Slowly Changing Dimension (SCD)

Slowly Changing Dimension (SSIS)

In SSIS Slowly Changing Dimension (or) SCD is categorized into 3 parts.

1. Change the Attribute (Type I - in terms of data ware Housing)

Select this type when Changed Values should overwrite   With existing values

2. 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.

3. 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.

MSBI Interview Questions

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:

• SQL Server Analysis Services
• Report Server
• Transfer SQL server Objects Task
• Flat file source