sql server - What is the best way to update a calculated derived column during Slowly dimension update? -

I am working on an SSIS project to create a datawarehouse.

Here is my simple SSIS process. The location table created to load one dimension table (location) is a parent / hair structure based on nested set (left, right, level field). Therefore, along with the process involved, the update process also works.

However, I have a parent relationship There is a need to fill a calculated column (PARENT_ID) in order to store. Therefore, for every line I need to use the SQL statement (left, right and level column) to find the value of the PARENT_ID.) < / P>

This is the table structure of the source table:

< P> then the loading process What is the best way to populate my parentadid column during RIA? Should I create a dedicated data flow in this process?

Can I use a SQL statement in a derived column component to find the parent_id value?

Edit : Here's my SQL query to update the structure of my parent / child (parent area).

  Update L1 SET L1. [PARENT_ID] = (top 1 L 2. ID FOR [DW]. [DM]. [TREE_LEFT_VALUE] & lt; L1. [TREE_LEFT_VALUE] and L 2. [TREE_RIGHT_VALUE] & gt; L1. [TREE_RIGHT_VALUE] ORDER BY L2. [TREE_RIGHT_VALUE] -L1. [TREE_RIGHT_VALUE] ASC) [DW]. [Thumb position] L1 WHERE [TREE_LEFT_VALUE] & gt; 1  

Edit 2 : A diagram representing a nested set structure and how it is represented in the database.

Therefore, each node has TREE_LEFT_VALUE and TREE_RIGHT_VALUE values ​​which are respectively their child's TREE_LEFT_VALUE / TREE_RIGHT_VALUE value.

This is a common query designed to find all the children in a specified node. For example, if I want all children in the "Route 1" node, then I have to query all the nodes from which TREE_LEFT_VALUE is greater than 2 and TREE_RIGHT_VALUE 5 Less than

Because I need to use the parent_id structure in my datawirehouse (SSAS project for the creation of OLP cube), I need to update a PARENT_ID field by adding the parent node ID, so I use the update code The parent node ID needs to be queryed by using the TREE_LEFT_VALUE and the TREE_RIGHT_VALUE field.

It works fine, but is there a more efficient way to do this?

Thank you,

If the column is empty, After the flow. It will guarantee insurance that you can overwhelm all the original parental rows before you try to find a parent - instead of trying to do it with a lookup in a dataflow task, the process will be determined based.