Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using Lookup in SSIS for scd Type 1 change

Posted on 2013-01-11
6
Medium Priority
?
631 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 Patade
  • 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 Patade
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 27

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Sonali Patade
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 750 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 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 750 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

971 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