Slowly Changing Dimension (SSIS)
In SSIS Slowly Changing Dimension (or) SCD is categorized into 3 parts.
- Change the Attribute (Type I - in terms of data ware Housing)
Select this type when Changed Values should overwrite With existing values
- Historical Attribute (Type II)
Select this type When Changes in particular Columns Values Are saved as new records and Previous values are Saved as expired records.
- Fixed Attribute (Type III in terms of Data Warehousing)
Select this type when the value in a column Should not change. Changes are treated as errors.
Steps to Configure Slowly Changing Dimension:
Open Business Intelligence Development Studio ---------->
Create a new package and rename it as an scd. Dtsx
In Control flow drag and drop Data Flow Task and rename it as DFTSCD ---------->
Open SQL Server Management Studio and run the following SQL Statements ---------->
To create temporary tables for source and destination ---------->
Table for Source Select _ into Human Resources. Employee Address from Human Resources. Employee ---------->
In Data flow drag and drop OLEDB source ---------->
Double click on OLEDB Source to configure it ---------->
Provide Connection Manager if exists ---------->
Select Human Resources. Employee Address from the drop-down list ---------->
Drop and Drop SCD and make a connection from OLEDB Source to SCD ---------->
Double click on SCD to configure it ---------->
In welcome to the slowly changing Dimension wizard, click next ---------->
Provide destination connection manager and select Human Resources. Employee destination and set,
Login ID – Business Key ---------->
Click Next ---------->
Birth Date - Fixed Attribute
Gender - Fixed Attribute
Contact ID - Fixed Attribute
Hire Date - Fixed Attribute
Marital Status - Historical Attribute
Sick Leave Hours - Historical Attribute
Title - change Attribute
Inclined to build a profession as MSBI Developer? Then here is the blog post on, explore MSBI Training
Click Next ---------->
Make sure that Fail the transformation, if changes are detected in the Fixed attribute checkbox is checked ---------->
Click Next ---------->
Use a single column to show Current and Expired records radio button ---------->
Column to indicate current Record Select Nation ID Number ---------->
Value When Current - Select Current Expiration value - Expired ---------->
Click Next ---------->
Click finish
Note:
For Cross – Testing SCD, Make some changes in the source table for a fixed
Attribute (update [Human Resource].[Employee] ) set Gender = ‘M’ Where employee ID = 1 ---------->
Execute package
Row Sampling Transformation:
It takes a fixed number of rows from the Source data set.
It splits the data set into 2 sets, Sample and UN sampled output.
Note:
The main usage of this transformation would be to select a fixed size subset Of data. This subset can be used to test the Packages with a limited data set.
For example:
If you are running a package against a multimillion rows, you could Just run the package with.
Steps to Configure Row Sampling
Open Business Intelligence Development Studio ---------->
Create a New package and rename it as Row Sampling. dtsx ---------->
In Control flow drag and drop data flow task ---------->
In Data Flow drag and drop OLEDB Source ---------->
Double click on OLEDB Source to configure it ---------->
Provide Connection Manager if exists ---------->
Select Human Resources. Employee Address from the drop-down list ---------->
Select Columns and click ok ---------->
Drag and Drop Row Sampling transformation and make a connection from ---------->
Source to Row Sampling. ---------->
Double click on Row sampling to configure it and set, ---------->
Click ok ---------->
Drag and Drop OLEDB destination and make a connection from Row Sampling to Destination ---------->
Select Sampling Selected output in Input-output Selection Editor ---------->
Click ok ---------->
Double click on OLEDB destination to configure it ---------->
Provide destination connection manager and click new to create destination table ---------->
And rename it as Row Sample ---------->
Click ok twice
Note:
If you want to test your package with the sample data set, use Random seed.
Percentage Sample:
percentage Sampling Splits a data set by read-only directing Rows to one of two outputs. We need to set the percentage of rows splits in to the 2 outputs (Sampling and Un sampling)
Steps to Configure percentage Sampling
Open Business Intelligence Development Studio ---------->
Create a new package and rename it as Percentage Sampling .dtsx ---------->
In Data Flow drag and drop the OLEDB source and configure it. ---------->
Drag and Drop percentage Sampling and make a connection from Source to percentage Sampling ---------->
Double click on percentage sampling to the editor it and Specify, ---------->
Percentage of row - 10 ---------->
Click ok
Drag and Drop the OLEDB destination and Configure it.
Audit Transformation:
Audit Transformation is used to display Audit information such as package Name, Execution Start time, Task Name, User Name, Machine Name, Task ID, etc.,
Copy Column:
Copy column transformation is used to copy input columns.
Example: Gender - Copy of Gender
Character Map:
Character Map Transformation is used to apply string operations in the selected data set.
Steps to Configure Audit, Copy column, and Character Map Transformation:
Create a New package ---------->
In Data Flow drag and drop OLEDB Source ---------->
Double click on OLEDB Source to configure it ---------->
Provide connection Manager if exits and Select Human Resources. Employee From the drop-down list ---------->
Drag and drop Audit transformation and make a connection from the source to audit. ---------->
Double click on audit and Select the following ---------->
Audit Type,
Package Name
Task Name
Machine Name
User Name
Click ok ---------->
Drag and Drop copy column and make a connection from Audit to copy Column ---------->
Select Gender from available input columns and click ok ---------->
Drag and drop Character Map transformation and make a column to Character Map ---------->
Double click on Character Map and check the copy of the Gender available input column and set, ---------->
Operation - Lower Case ---------->
Click ok
Drag and Drop OLEDB destination and make a connection from Character Map to destination ---------->
Double click on the destination and provide destination connection manager ---------->
Click new to create destination table and rename it as Audit data ---------->
Click ok twice
For in-depth knowledge on MSBI click on: