Data Analytics Using SSIS And Power BI

Introduction

Data Analytics is one of the hottest topics of the 21st Century. The process of Data Analytics requires us to get data, clean it, transform it, then generate insights, patterns, or models for machine learning. All this through an automated mechanism. To perform data analytics we need data. This data should be in large amounts. The more it is, the more we can drill down to generate desired outcomes.

Few words about the Excel App. Excel is one of the data champions. It has been around even before data analytics became popular. It is an advanced mathematical tool with a lot of capabilities. It can still get a lot of things done that are part of business use cases for data analysis. We should explore it more, but not go out of context in this tutorial. Excel is great in the context of Microsoft Business Intelligence Tools. It cannot be replaced for the capabilities it provides.

Nowadays, many advanced data handling tools are available in the market. They are designed with the intent of making development easy and contain complete Tool Sets for getting things done from development to deployment. They support modern development Practices and much more. We are going to Discuss SSIS and Power BI.

SQL Server Integration Services (SSIS)

SSIS is a platform offered by Microsoft, it is used for building enterprise data integration and transformation solutions. It can extract data from various sources such as Excel, CSV, Databases, MPP Databases such as Hadoop, etc. It has a wide variety of connectors. It is an ETL Tool. It is used to create data workflows. It can automate many tasks. It can automate SQL Server Maintenance Tasks. It is widely used by companies that rely on Microsoft Technology Stack. It has a lot of built-in capabilities. It offers us a drag and drop interface for Workflow Development. It helps us easily work with databases so we can write SQL Queries or invoke Stored Procedures. It also provides us capabilities to Write C#/VB.NET code as part of the workflow.

Power BI

Power BI is a Business Analytics Tool, it is used to visualize data and can also perform data transformations. It is a Self-Service Analytics. It can connect with multiple sources. It can be extended with the Power Apps ecosystem which includes Power Automate, Power Virtual Agents. Also, with Office 365 Integration. We can publish reports and we can interact with Power BI API to change data. Power BI itself contains a lot of capabilities and can handle all data-related operations.

To perform advanced data analytics and automation there are many popular tools, some examples of ETL and Visualization Tools include:

Visualization	ETL
Tableau	Alteryx*
QlikSense	Talend
PowerBI	Informatica*
	SSIS
	Matillion
	Data Visualization Capabilities

What will be covered in this tutorial?

In this tutorial, we are going to implement full end-to-end flow for Data Analytics. We will clean (if required), transform, and then visualize the dataset. To make this possible we are going to use Microsoft Technology Stack, this will require us to use SQL Server Integration Services(SSIS) and Power BI. The purpose is to demonstrate the capabilities of both and not to show the most efficient solution. This is just an introduction use case. We will use the financial sample dataset.

Prerequisites

SSIS
Microsoft Power BI

Data Set

https://docs.microsoft.com/en-us/power-bi/create-reports/sample-financial-download

To-Do List

Take the excel file
Transform all the currency values to INR,
Filter data for the year 2014
Generate CSV File
Import CSV to Power BI
Create a pie chart and bar graph for countries with sales and profit

SSIS Workflow Steps

We will create a table in SQL Server.

CREATE TABLE "ADO NET Destination" (
"Segment" nvarchar(255),
"Country" nvarchar(255),
"Product" nvarchar(255),
"Discount Band" nvarchar(255),
"Units Sold" float,
"Manufacturing Price" money,
"Sale Price" money,
"Gross Sales" money,
"Discounts" money,
" Sales" money,
"COGS" money,
"Profit" money,
"Date" datetime,
"Month Number" float,
"Month Name" nvarchar(255),
"Year" nvarchar(255)
)

We will create a variable that will contain this exchange rate.
We will create a Workflow and configure it as per the below images.

About the Workflow

Our workflow will get an Excel file from source, convert to INR with the approximate exchange range from Dollar To INR and then insert data to the database. From the database, we will filter out data that will contain data for the year 2014. Then we will send data from SQL Server to the CSV file. This CSV file will be further used for visualization.

Power BI steps

Open Power BI
On Toolbar, click Get Data
On Get Data, click Text/CSV
Select File
Click Load

Now, to work with Power BI we drag one field over another to get the desired result. Let us drag the Gross Sales field and then drag the Segment Field over it and click on a donut chart. This will create a donut chart.

Similarly, we will create 3 more visuals for the report.

Drag Profit and Country, select bar chart if not selected.
Drag Gross sales and Country, select bar chart if not selected.
Drag Profit and Segment, select Pie Chart.

Overall, our Report will look like this in Power BI:

Now, you can save it with any name with .pibx format. That’s it. Congratulations on creating a full end-to-end flow!

Summary

Now, let us recap...

Create SSIS flow.

Create a Table in the Database.
Create a Script Task to Clean Table using Truncate because the same will run multiple times so the table must be empty.
Create a Variable for Currency Exchange.
Create a Data Flow task to read the Excel file.
In Data Flow, Create a task that will perform Currency Format.
In Data Flow, Create a task load Excel to a database table.
Create a SQL Script task to filter 2014 records from the database.
Create a Data Flow task to send data from SQL to a CSV file

Create a Power BI Report

Create 2 Bar Graphs
Create 1 Pie Chart
Create 1 Donut Chart

Thank you for reading until the end.If you have any questions, opinions or suggestions, please feel free to share in the comments.