question

SrikanthA avatar image
SrikanthA asked

Updating dimension tables in a data warehouse

Hello guys, The question I'm going to ask might be very silly. I need a solution for the question which I could not find by googling. Question is: I have a staging database which receives all the new records on daily basis from the OLTP server. From staging I need to populate the dimension tables. When I use a Lookup Transform to find the matched and unmatched records, I am putting an OLEDB Destination for newly created data for the dimension table, For the records which already exist, I need to update them in dimension table. Initially I used OLEDBCommand and a stored procedure to update the records to dimension table but when I googled, I read that OLEDBCommand will operate in row-by-row basis and it takes huge time for millions of records. It was also suggested that SCD (Slowly Changing Dimension) is also done on a row-by-row basis and will kill time. When I tried to do the OLEDBDestination and SQL Command, and with the stored procedure, it threw an error saying the mapping columns are not available. I know why... it's because it is a stored procedure which only updates but does not give any output. So again, how can I update the dimension tables after Lookup Transform? Am I going about this the wrong way? Please guys help me out with a solution. Thanks in advance.
ssisdata-warehouseslowly-changing-dimension
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
The question is not silly at all. It's a challenge for many to perform something like a T-SQL Merge in SSIS. There are some options to choose from: - Create a view looking just like your destination and have an INSTEAD OF TRIGGER on the view, which performs the MERGE operation. That way, you'll get bulk insert to the view and if you write the INSTEAD OF TRIGGER in a clever way, you will have set based operations all the way. - Create a table type, create a stored proc which gets a table valued parameter of that type, and in SSIS use an ADO.NET source and a Script Component as target ( more here: http://voluntarydba.com/post/2012/12/05/Using-table-valued-parameters-in-SSIS.aspx ) - Move the operation to the Control Flow, and simple use an Execute SQL task perform the MERGE operation for you. There are pros and cons with all Three solutions. If staging and destination table exist in the same database or at least same server instance, I would go for the third solution.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wish I could upvote more than once!
0 Likes 0 ·
Thanks Magnus for giving me multiple solutions. I will go through and check which one is feasible. Thanks Srikanth
0 Likes 0 ·
opt 3 sounds good :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.