Using Lookup in SSIS for scd Type 1 change

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.
Sonali PDatabase AdministratorAsked:
Who is Participating?
 
Ryan McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
Sonali PDatabase AdministratorAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris LuttrellSenior Database ArchitectCommented:
Are you going to reload all the facts every time?  What about the historical records already loaded into it?
0
 
Sonali PDatabase AdministratorAuthor Commented:
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
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
All Courses

From novice to tech pro — start learning today.