Slowly Changing Dimension (SSIS)
In SSIS Slowly Changing Dimension (or) SCD is categorized in to 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 a particular Columns Values
Are saved as new records and Previous values are Saved as expired records.
- Fixed Attribute (Type III in terms of Data Ware housing)
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 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
At TekSlate, we offer resources that help you in learning various IT courses. We avail both writtenmaterial and demo video tutorials. To gain in-depth knowledge and be on par with practical
experience, then explore MSBI Training Videos.
Click Next ---------->
Make sure that Fail the transformation, if changes are detected in the Fixed attribute check box 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 source table for a fixed
Attribute (up date [Human Resource].[Employee] ) set Gender = ‘M’
Where employee ID = 1 ---------->
Execute package
Row Sampling Transformation:
It takes fixed number of rows from 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 the 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 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 editor it and Specify, ---------->
Percentage of row - 10 ---------->
Click ok
Drag and Drop OLEDB destination and Configure it.
Learn more about MSBI Interview Questions in this blog post.
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 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 copy of 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 destination and provide destination connection manager ---------->
Click new to create destination table and rename it as Audit data ---------->
Click ok twice
For an Indepth knowledge on MSBI click on: