none
How to Update Columns in Dimtable if it is return from multipl tables

    Question

  • Hi All,

           I created SSIS package that loads data into Dimension table, the source for this dimension table having 2 Lacs Records I'm inserting all this records in to My dimension table.

    For Populating this dimension table I need to join with Minimum of 10 Tables.the complete resukt data I stored in DimItemMaster table. As Per my requirement if any record is updated in any of 10 tables Those updated will happened in My Main Dimension table.

    For this one I created SSIS package like

    1. Source --> Select required columns from First table

    2. Lookup--> Compare on ID columns using Lookup transformation. if record exists Update those column in Dimension table.

    3. Other wise insert into table.

    In Next DFT

    1. Source --> Select required columns from Secound table.

    2. Lookup--> Compare on ID columns using Lookup transformation. if record exists Update those column in Dimension table.

    Like this I'm created 10 DFT's and check based on ID's if not update that record in Dimtable. For updating this I use OLEDB command.

    In first Table the data is loading in 10 min when coming to secound DFT we are updating records.

    Here execution process is very slow for updating 16k Records it takes 1 hour.

    The process that I did is correct or not?because for updating 4 column in Dimension table it takes this much of time If I want to update all coulmn in dimension table it takes lot of time.

    Update Statement:

    Update DimItemMaster 

    Set

    InvID=?,

    InvName=?,

    InvType=?

    where

    CODEID=?

    This is Update Statement I written in Oledb Command


    Regards, Praveen

    Saturday, September 28, 2013 1:21 PM

All replies

  • If it works, it's okay.

    But instead of doing this by lookup/update, you can try using SQL join/update query in Execute SQL Task.

    Update T2
    SET T2.InvID = T1.InvID
    , T2.InvName = T1.InvName
    , T2.InvType = T1.InvType (and so on for the rest of your update columns)
    FROM Table2 T2 INNER JOIN Table1 T1
    ON T2.CODEID= T1.CODEID (can use multiple columns to join).

    The above query basically does what the lookup/oledbupdate does....but will be much faster (make sure you have index on CODEID column).

    An alternative is a Merge query.




    • Edited by B3nt3n Saturday, September 28, 2013 7:17 PM
    • Proposed as answer by Dean Savović Sunday, September 29, 2013 12:02 PM
    Saturday, September 28, 2013 7:13 PM
  • How about creating a view instead of creating Dimension Table. Create a view and read the data as required. The Dimension table you are taking about does not seems like a true Dimension table but it sounds like a table carved out of multiple columns of multiple tables.


    Vikash Kumar Singh || www.singhvikash.in

    Sunday, September 29, 2013 3:01 AM
  •   I created SSIS package that loads data into Dimension table, the source for this dimension table having 2 Lacs Records I'm inserting all this records in to My dimension table.

    For Populating this dimension table I need to join with Minimum of 10 Tables.the complete resukt data I stored in DimItemMaster table. As Per my requirement if any record is updated in any of 10 tables Those updated will happened in My Main Dimension table.

    I would say you should use the T-SQL MERGE statement to perform dimension load. If the source table is result set of 10 table join, it is achieveable in MERGE. The other imporatnt fact is the performace which is excelllent using T-SQL MERGE.


    Thanks, hsbal

    Thursday, October 03, 2013 7:43 PM
  • Thank you Hsbal,


    But in my case I have to implement this requirement in SSIS package level. not in Sql server level.Because of this reason I created like this.

    But perfomance is not that much of expectation.


    Regards, Praveen

    Saturday, October 05, 2013 11:00 AM
  • Even though you want to performt this task as SSIS package, T-SQL MERGE can still be used in exec SQL task. You need to test the MERGE query in SSMS and then paste that in exec SQL task. So, this will be done in package level...Finally when you will schedule the package as SQL agent job, dim will be updated based on pkg execution..

    Thanks, hsbal

    Monday, October 07, 2013 4:00 PM