• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Slowly Changing Dimension (SCD)

Slowly Changing Dimension (SSIS)

In SSIS Slowly Changing Dimension (or) SCD is categorized in to 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

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

  1. 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:

Summary
Review Date
Reviewed Item
Slowly Changing Dimension (SCD)
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Slowly Changing Dimension (SCD)"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.