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 written material 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:
0 Responses on Slowly Changing Dimension (SCD)"