Business Intelligence is the process of transforming technical data into meaningful information, so the end users can analyze and gain insights to make proper decisions in an organization. In this blog, we talk about the importance of Microsoft Business Intelligence and how it can be used to auto build and deploy SSRS and SSIS projects.

What is Microsoft Business Intelligence?

Microsoft Business Intelligence (MSBI) is a comprehensive set of tools to help businesses to make better solutions in Business Intelligence and Data Mining Queries. MSBI leverage the power of Visual Studio and SQL servers to provide users access to accurate and up-to-date information by optimizing and improving their decision-making process.

The transformation of raw data to information involves a very important step called Analysis.

The analysis comes with a lot of complexity. If you look at the data side, data can be in different formats (XML, CSV, txt etc.) and to go and run the analysis algorithm individually on each one of these data sources is a very time consuming, difficult and illogical task. Hence, the best approach would be to take this data and dump it into a central database. The central database is termed as Data Warehouse. On this central data database, we run analysis algorithm, and from that outcome of the analysis, meaningful information is generated.

The conversion process of technical data to the data warehouse involves a very complex process. As mentioned before, the data is available in different data sources like XML, CSV, txt, SQL etc. and before we go and bring all of these different data types into the central database. We have to do three major processes.

The first process of having an extraction of these different types of data sources and dump them into the data warehouse.

The second process involves data transformation. Where we bring different data types to a common data warehouse and ensure that the data has transformed into the common format.

The last step involves loading this transformed data into data warehouse. So in short, the path of technical data to data warehouse involves Extraction, Transformation, and Loading. This complete process in MSBI world is termed as ETL ( Extract, Transformation and Load).

Once the data has been analyzed from the data warehouse, we need to store this analysis to some kind of data store. This data store structure is termed as CUBE. From the CUBE, we display the data to the end user as information.

In MSBI, there are 3 parts: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS).

When we go and create an MSBI project, we are provided with different templates.

Each one of these templates serves some section of MSBI life cycle. For example, the ETL section, the journey from data to data warehouse is done by Integration services template. The analysis part, where we run analysis and create a cube is done by the Analysis services template. The reporting party, where we take the analyzed data and show it to the end user is done by the reporting server template. So all of these 3 templates (Integration services template, Analysis services template and reporting server template) helps to get full Business Intelligence Automation.

The integration service is also termed as SSIS (SQL Server Integration Services). The analysis service is termed as SSAS (SQL Server Analysis Services) and the reporting service is termed as SSRS (SQL Server Reporting Services). So, the SSIS do the ETL, SSAS do the analysis and the SSRS display the analysis to the end user.

So, now that we know that what is the roles of SSIS, SSAS, and SSRS.

We can go and start executing a sample project. Before starting our project we need to have all the necessary tools with us. So we need to download SQL server enterprise edition (MSBI does not come with express edition, you need only Enterprise edition to get MSBI) and SQL server data tools (SSDT). The SQL server data tool is to create MSBI projects.

Example Project using SSIS and SSRS

Our aim is to fetch the below data from Customer Information, Product information and Sales information (CSV format) and transfer it to a database’s (data warehouse) corresponding tables (tblcustomer, tblproduct, and tblsales).

When we transfer the data to the database, we need to change the Customers and products names as an uppercase letter. So, we have to perform the ETL (Extract the data from the text files, Transform the lowercase letters to uppercase, and Load it to the database).

We can use SQL Server Integration Service (SSIS) to perform the ETL and SQL Server Reporting Service (SSRS) to display the report to the end user.

Step 1. Create a new project

Open SQL server data tools >> New project. Select integration services project. Specify a name and choose a directory to save the project. Click OK.

It will open up SSIS designer window which will help you to create and manage Integration service packages. It looks as follows:

Here you would see an SSIS toolbox, solution explorer and a taskbar having Control flow and data flow.

Step 2. Add control flow and data flow

Control Flow controls the flow of execution of SSIS package, while data flow task operates the most of ETL operations and is the main part of SSIS. Control flow also acts as a container as all data flow tasks will be created under the control flow.

2.1 Drag and drop the data flow task from SSIS toolbox to the control flow window and give a name.

2.2. Double-click on the data flow, now you can see the control flow tab switched to the data flow tab automatically. Here we can add data flow tasks.

The SSIS toolbox changes when it goes from control flow to data flow. Now. we can add the ETL code into this data flow tab.

2.3 To fetch data from above-mentioned files, drag and drop the flat file sources from Other sources tools and name it.

We need to configure each flat file sources and add connection manager.

2.4 Double-click on each flat file sources >> New >> Specify the connection manager name and file from where the data to be read and make sure to check the box “column names in the first data raw” then click OK.

2.5 Now we need to create a destination. We are using an SQL DB as a destination here and created DB with three tables (tblcustomer, tblproduct, and tblsales).

As, now we have extracted the data from the text files and ready to load the data to a data warehouse, we need to add a destination to load the data to a data warehouse. We will explain about transformation part later.

2.6 Drag and drop the ADO NET destination (since I am using SQL server database as a data warehouse) from Other destination folder and name it.

2.7 Drag and drop the blue line from flat file source to ADO NET destination to make a connection.

2.8 Double-click on ADO NET destination >> New >> New. Add the SQL server name and choose an authentication mode. And select a database where we need to load data. Click Ok.

2.9 Select ‘’tblcustomer table as I need to load the Customer information to the customer table and Click OK.

2.10 From mapping section, properly map the columns between source and destination columns. Drag the Available input columns and drop it to corresponding available destination columns if needed and click OK.

2.11 Now let’s add Transformation task to change the Customers and products names as an uppercase letter. For that, we need to delete the connection links between each flat file sources and ADO NET destinations.

2.12 Add Derived columns between each source and destinations. Then make the connection from flat file source to derive column, and from derived column to ADO NET destination.

Derived columns help us to do functions ranging from mathematical functions, string functions to date and time functions and more.

2.13 To configure it, double-click clicks on “Cloumns”, then a list of functions that can be seen achieved. Here we need to change the lower case letter to uppercase letter by using “String Functions”.

2.14 Find the function ‘UPPER( «character_expression» )’ under the string functions and drag and drop it to Expression section. Then drag and drop the DB column ( we have selected Customer name table as we need to change the names to the uppercase letter) to the inside of the brackets.

Change the derived column name and select ‘add as new column’ in derived column section. Click OK.

Now the Customer name and Product name will be transformed to the uppercase letter and stored into the newly created column (Uppercase Cname). So we need to change the column mapping as now we need to map newly created input column (Uppercase Cname) to the destination CustomerName column.

2.15 Edit the ADO NET destination >> click Mappings. Drag the newly created input column to the CustomerName output column. So the previous mapping will be deleted, and Click OK.

2.16 Now we are all set to run the SSIS project. Click on start button to execute the ETL process to Extract data from text files and Transform its names to the uppercase letter and then Load it to a database.

Now we can see the queries are running. If everything is running fine, results will be like:

Let’s check the database table to see the final result.

We can see all details have been successfully loaded to the corresponding tables and corresponding fields and we transformed the lower case letters to uppercase.

3. Display the results to the End User using SSRS

Our aim is to display the results of table ‘tblcustomer’ to end user through a browser by using SSRS.

3.1 Open SQL server data tools >> New project. Select Reporting services >> Report server project wizard. Specify a name and choose a directory to save the project. Click OK.

Here, we need to supply details of a data source. The data source is where our tblcustomer table resides. Currently, the tblcustomer resides on the database ‘data warehouse’.

3.2 In report server wizard, click Next >> specify the SQL server name, select authentication mode and select our database. Click OK.

3.3 Give a name for the data source and click Next.

3.4 In next window, click on Query builder to build a query to fetch details from the table ‘tblcustomer’.

3.5 Right click on Query designer >> Click “Add table”

3.6 Select the table ‘tblcustomer’ >> click “Add”.

3.7 Select the fields from where we need to display the result >> Click OK.

3.8 In next window, click Next >> select tabular or matrix report type >> Next

3.9 Drag and drop the fields to details panel.

3.10 Select any table style >> click Next

3.11 You can see the report server URL there and specify a deployment folder >> Click Next.

3.12 Give a name for the report >> Click Finish.

3.13 In Design window, we can do a final modification. We can also see a preview report in the Preview window.

3.14 If everything looks good to go, then right-click on the project >> Click Deploy.

We can see our project has been deployed to the report server. http://localhost/ReportServer to the subfolders /DisplayCustomer/DisplayCustomer.

We can use the URL http://localhost/ReportServer/DisplayCustomer/DisplayCustomer in a browser to view the results. We can see a decent report in the browser as follows.

Thanks for dropping by. Ready for the next blog?

Plesk Acquired SolusVM

The post Microsoft Business Intelligence (MSBI) – Build and Deploy SSRS and SSIS projects appeared first on Sysally.