December 15, 2006 at 11:37 am
I am a new DBA and obviously new to the SQL Server 2005 Integration Services. We are building our first Data Watehouse and one of the ETL requirement is to use SSIS.
Can anyone help me on how to put together the use of Lookup, Slowly changing Dimension, and Union All, in building my package. I have tried several times but I'm not getting a result.
The objective is simple:
1. populate the dimension table (which is initially empty)
2. check the dimension if the business key/natural key already exist
3. if not create the record, if it does, apply historical SCD type 2 change.
thanks for your help.
December 18, 2006 at 5:50 am
Hi,
When I first saw the SCD control I was excited and thought it would save me some time. After trying to use it I found the resulting package complicated to understand and the execution times of the package slow. Therefore I tend to write my slowy changing dimension by hand in Execute SQL tasks. This is fairly straight forward unless you wish to have many columns and conditions on your dimension.
If you break the package down into logic steps as you have already done in your post the sql is quite easy.
On a Type 2 dimension you need to include a couple of control fields to you dimension. I use DateFrom, DateTo, and CurrentStatus. You definately need the first 2 but the 3rd makes the SQL easier.
Here is a example I'll split over a few posts to give you the idea. This was a very simple dimension in the curretn warehouse I am building. I am building the Type2 dimension in the Presentation database moving data from the Detail database. Also for the CurrentStatus flag we have a status of 'C' for Current, 'U' for Updated and 'H' for Historic.
So this is how I've done it.
1. Check the dimension table exists. (not strictly required)
2. Check for current records that exist and have changed. Update the DateTo flag and set the CurrentStatus to updated.
3. Insert new and updated record from Detail to Presentation
4. Updated records in Presentation with and updated status to historic.
Check the next few posts for the SQL for each step.
Regards
Daniel
December 18, 2006 at 5:50 am
Use Presentation
If Not Exists(Select * From sys.tables Where name='DimWarehouseT2' And type='U')
Begin
Create Table dbo.DimWarehouseT2
(
WarehouseKey int Identity(0,1) Not Null,
WarehouseCode NVarchar(16) Collate database_default Null,
WarehouseDescription NVarchar(64) Collate database_default Null,
WarehouseName Nvarchar(96) Collate database_default Null,
ValidFrom datetime Null Constraint [DF_DimWarehouseT2_ValidFrom] Default('01 Jan 1900'),
ValidTo datetime Constraint [DF_DimWarehouseT2_ValidTo] Default('31 Dec 2099') Not Null,
CurrentStatus Char(1) Collate database_default Default('C') Null,
Constraint [PK_DimWarehouseT2] Primary Key Clustered
(
WarehouseKey Asc
) With (PAD_INDEX=OFF, IGNORE_DUP_KEY = OFF)
)
Insert Into dbo.DimWarehouseT2(WarehouseCode,WarehouseDescription,WarehouseName,SourceKey,ValidFrom,ValidTo,FileLoadNo,CurrentStatus)
Values('0', 'Unknown','0 Unknown',0,'01 Jan 1900','31 Dec 2099',0,'C')
End
December 18, 2006 at 5:51 am
Use Presentation
Update DimWarehouseT2
Set ValidTo = DateAdd(s,-1,Cast(Cast(GetDate() As Varchar(12)) As DateTime)),
CurrentStatus = 'H'
From
[CurrentDetail]..DimWarehouse As Detail
Inner Join DimWarehouseT2 As Presentation On
Detail.WarehouseCode = Presentation.WarehouseCode
Where
Presentation.CurrentStatus = 'C'
And (Detail.WarehouseDescription Presentation.WarehouseDescription
Or Detail.WarehouseName Presentation.WarehouseName)
December 18, 2006 at 5:52 am
Insert Into DimWarehouseT2
(
WarehouseCode,
WarehouseDescription,
WarehouseName,
ValidFrom,
CurrentStatus
)
Select
Detail.WarehouseCode,
Detail.WarehouseDescription,
Detail.WarehouseName,
Detail.ValidFrom,
'C' As CurrentStatus
From
[CurrentDetail]..DimWarehouse As Detail
Left Outer Join DimWarehouseT2 As Presentation On
Detail.WarehouseCode = Presentation.WarehouseCode
Where
Presentation.WarehouseCode Is Null
Or Presentation.CurrentStatus = 'U'
December 18, 2006 at 5:53 am
Update DimWarehouseT2
Set CurrentStatus = 'H'
Where
CurrentStatus = 'U'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply