Solved

Using Lookup in SSIS for scd Type 1 change

Posted on 2013-01-11
6
611 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 27

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

630 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