Solved

Using Lookup in SSIS for scd Type 1 change

Posted on 2013-01-11
6
597 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:Sonali Paradkar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 38768922
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:Sonali Paradkar
ID: 38768961
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
ID: 38768976
Are you going to reload all the facts every time?  What about the historical records already loaded into it?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Sonali Paradkar
ID: 38769021
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
ID: 38769028
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
ID: 38769055
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan not deleting backup files as anticipated 4 32
Help with SQL pivot 11 49
What is needed to become a DBA? 7 56
Search Text in Views 2 28
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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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