Links to other Labs of Learn MSBI Step by Step.

Learn MSBI Part 1:- Creating SQL tables, Practicals of SSIS-ETL(Extraction, Transformation & Loading)

Learn MSBI Part 2:- Performing Validation on Records

Learn MSBI Part 3:- SCD (Slow Changing Data)

Learn MSBI Part 4:- Fact and Dimension.

Learn MSBI Part 5:- CUBE Creation

Learn MSBI Part 6:- Displaying reports using SSRS

Learn MSBI Part 7:- DQS (Data Quality Services)

Learn MSBI Part 8:- Data Profiling Task

Learn MSBI Part 10:- Execute SQL Task component (SSIS)

Learn MSBI Part 11:- Send Mail Task & Script Task Component

Learn MSBI Part 12:- CDC Concept using SSIS

Learn MSBI Part 13:- Asynchronous, Synchronous, Full-blocking, Semi-blocking & No-blocking

Learn MSBI Part 14:- Understanding Page Split and maintaining SSIS Performance

Learn MSBI Part 15:- Debugging in deployment – Instrumentation using Data Taps

Learn MSBI Part 16:- Using Multi-threading and its performance in SSIS program

Starting with Pivot : With the help of Pivot transformation Rows can be done into Columns as shown in the following image. By using Pivot “DayOfWeek” Rows will be transformed into Columns as Destination.

In this lab practical demonstration will be done to transform rows into column using Pivot component and output will be displayed on SQL Server table.

Go to SSDT Visual Studio tool and create new SSIS project.

From SSIS Toolbox drag and drop Data Flow Task on Control Flow designer.

Do double click on Data Flow Task to go to Data Flow or directly click in Data Flow tab.

From SSIS Toolbox under “Other Sources” drag and drop “OLE DB Source” on Data Flow Designer as shown in the image below.

Next is to configure the component just by doing double click on component or do right click and Edit component.

It will open OLE DB Source Editor under it click on “Connection Manager” tab. Under this set OLE DB connection manager by giving server name in this field next is selecting table from the drop down [dbo].[PivotSource]

And finally just do a preview of it.

Next is drag and drop “Pivot” component from “Other Transforms” on the Data Flow Designer as shown in the image below.

2) And connect output of “OLE DB Source” component as input to Pivot component.

3) Now configure the Pivot component by clicking on Edit.

Once the Pivot configuration screen is open do the following settings: –

1. Set “Pivot Key” to DayOfWeek. This field “weekdays” will be converted into new column in Destination.
2. Set “Set Key” to Month. This field will be grouping input values of month into one output on Destination.
3. Set “Pivot Value” to Amount. It will give us back value of the Amount on the output column.

Next is to define output column as there is currently only one output column present i.e. Month. In order to confirm the output columns just do right on Pivot component and click on “Show Advanced Editor” which will open Advanced Editor for Pivot as shown in the image below and then click on “Input and Output Properties” and then expand Output Columns.

For new columns which will be created name it to weeks as [Sun],[Mon] and explicitly define it under “Generate pivot output columns from values” and then click on “Generate Columns Now” which will generate new columns named as “C_Sun_Amount”, “C_Mon_Amount” and get that displayed on the right side.

And finally click on OK to close the screen.

Once the Pivot component is configured next is to add the destination to show new output for that

1. Pull out “OLE DB Destination” from Other Destinations available under SSIS Toolbox.
2. Connect output of “Pivot” component as input to OLE DB Destination component.
3. Now configure the OLE DB Destination component by clicking on Edit.

Once the OLE DB Destination Editor screen is open click on Connection Manager tab available on the left side then select the server name as OLE DB connection manager.

After giving server name select the destination table name from dropdown as [dbo].[PivotDestination].

Now click on Mappings tab and do the mapping to set for input column & output column: –

And the finally click on OK to close the OLE DB Destination Editor.

After all components are configured successfully next it to execute and run the package by clicking on Start button available on the top.

It will show green arrow on the top right side of component as shown in the image below

Now go to the SQL Server Management Studio, on the database table and see whether the destination table created is filled with correct values. On the database at destination table input rows of week of days are successfully created as column as shown in the image below.

Unpivot

Unpivot is simply opposite of what we did in previous Pivot lab where we converted input rows to output columns. Under Unpivot conversion of input columns will be done into output rows.

For demo of this lab create new “Data Flow Task” on the Control Flow Designer. Next is to do double click on “Data Flow Task” component to go to Data Flow.

Now on Data Flow, drag and drop “OLE DB Source” from “Other Sources” available under SSIS Toolbox on Data Flow pane and then configure it.

Thereafter pull next component to do unpivot of the table by using readymade Unpivot component available under “Other Transforms” of SSIS Toolbox.

Drag output of OLE DB Source and connect it as input to Unpivot component and then Edit it to configure it

As the output generated from Unpivot component with column name “DayOfWeek1” will be nvarchar and on SQL Server the datatype is varchar so to avoid mismatch data conversion is required.

Under SSIS Toolbox under Common –> Data Conversion is available pull the same & drop on Data Flow.

Once the Data Conversion Transformation Editor is open select “DayOfWeek1” as Available Input Column.

Name output generated by Data Conversion as “VarcharDayOfWeek1” and then set its data type generated as string[DT_STR]

And then click on OK.

After configuring Data Conversion then pull “OLE DB Destination” from “Other Destination” under SSIS Toolbox and connect output of Data Conversion as input to OLE DB Destination and then click on Edit to configure it.

Once the Editor is open then click on Connection Manager tab and then select the destination table.

Then click on Mappings tab and select and map the new Input Column “VarCharDayOfWeek1” from dropdown which was created earlier as output of Data Conversion component.

And then click on OK to close the Editor screen.

Once all component on Data Flow are configured next is to execute and run the package in order to check working of each component. It will show green sign each component which means proper running of each component.

Next step is to go and check on database whether new table called “dbo.UnpivotDestination” is properly loaded.

On SQL Server Management go to Object Explorer and click on Tables then select destination table “dbo.UnpivotDestination” and do right click on it and click on Edit Top 200 Rows to view the table output available.

All the existing columns has been successfully converted into rows of the tables as shown in the image down below.

Aggregation

There are times when multiple entries are present within the table like for example as shown in the below snapshot where March month and Monday has two entries but with different Amount one is of 15 and other is of 20. So in order to mark it as a single entry in the table and summing up the Amount values.

Here is where Aggregation component is used which is available as “Aggregate” under Common under SSIS Toolbox.

Drag and drop the Aggregate component from SSIS Toolbox on Data Flow as between OLE DB Source and Pivot component. Remove existing connection between OLE DB Source and Pivot component then connect output of OLE DB Source as input to Aggregate component.

Then do right click on Aggregate component and click Edit to configure it.

It will open its Aggregate Transformation Editor where select Available Input Columns as “Month”, “DayOfWeek” and “Amount”.

Then on Amount column select operation as “Sum” from the dropdown list available.

And then click on OK.

Once Aggregate component is configured then connect its output to Pivot component as input followed by Pivot component connecting to OLE DB Destination as input to display the table in SQL.

After connecting and configuring all necessary component execute the package by clicking on Start or do ctrl+F5.

Below is the image shown which shows successful execution of the package with green arrow mark on the component.

Next is to go to SQL Server Management Studio and view the table of “dbo.PivotDestination”. After the table is open there can be seen two duplicates existing records are converted to single record and it “Amount” total has sum up as shown in the image below. So demonstrating of addition of records and removing duplicate records is successfully achieved by Aggregation component.

Also get to see following video on MSBI project series which start from the basic to start SSIS : -