Solved

Using Lookup in SSIS for scd Type 1 change

Posted on 2013-01-11
6
545 Views
Last Modified: 2016-02-10
Hi,

Please advise how to use a Lookup component in SSIS for SCD Type 1 change.

I am able to to insert and update changes to the target table making use of lookup
and conditional split.

However i am not sure how to delete a row in the target table
if that happens in source table.

Also is it necessary to have a surrogate key in my dimensions and facts and can that be an
identity column?
also can i make use of multiple columns which are pk in my oltp tables as business key in olap tables.
Also is it necessary to have a start and end date column in each dim and fact table.
0
Comment
Question by:isonali
  • 2
  • 2
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
Comment Utility
Wouldn't you leave the row intact if it's deleted from the source system, since you'll still have historic data in your fact tables that corresponds to the (now deleted) SCD row?

Also, your SCD does generally have an identity column as the surrogate key, since that's what links your fact tables to the dimension row. While not strictly required if you have a single column in your dimension that uniquely identifies the row, I still prefer it as it abstracts your source data from the warehoused data and allows for more flexibility. Also, if you decide to change the dimension value in the future and want to keep record of the previous value (for historic reporting reasons), a surrogate key allows you to do that.
0
 

Author Comment

by:isonali
Comment Utility
Can we have multiple columns in the dimension tables which uniquely identity a row
in the dimension tables and not make use of a surrogate key.
I do not intend to save historic data.in the dimensions and facts on the target server , it just needs to be a replica of the source db. (SCD Type 1 change)
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
Are you going to reload all the facts every time?  What about the historical records already loaded into it?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:isonali
Comment Utility
I would not like to retain the historic record in facts or dimensions and keep just the current records.
Still wld you require a surrogate key ?  my concern was can multiple columns from source be used to uniquely identify rows in dimension and fact tables and not use surrogate key at all
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 250 total points
Comment Utility
If you can uniquely identify rows in your dimension table, and you don't have a need for historic reporting (and it sounds like you don't), then you don't necessarily need a surrogate key. Just remember that you need to associate rows in your fact table with the dimensions, and I've found surrogate keys the easiest way to do that, but if you want to do it with native business keys, there's nothing wrong with that as long as it's manageable.
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 250 total points
Comment Utility
Yes, I agree you don't have to have the surrogate key, assuming from your statement that you want to "keep just the current records".  
The best and probably fastest way to do that will be clearing and rebuilding your Fact and Dimension tables every time. If truely only  keeping "current" records this will be a much faster operation than any Merge or the Lookup Conditional split operations.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now