How to Create Data Warehouse and Integrate with Power BI

From Structure Data to Data Warehouse and Combining Power BI:

Starting from the process of structuring data, we know that the Data will not always be in the structured format, a structured format is a myth. The first step for diving into the data either for Data Analysis or Machine Learning the fundamental step is “Data Engineering”. The data can be in different formats either XML, JSON or Raw Data. We need to convert it into the structure or format we want.

In the coming lectures we will also be dealing with unstructured data, for getting into the Data Warehouse we are just getting started with the Adventure Works Data Base. We have downloaded the Full Data Warehouse Backup Version 2019. For Implementing any sort of Warehouse, you need SQL SERVER 2019 Professional or Enterprise Edition, Visual Studio 2019 with Microsoft Analysis Services Extension of Visual Studio. These are some of the basic stuff which required to start the project or get into the Data Warehouse. The Data Tools Should be Installed in Visual Studio 2019, the actual name is “SSDT” which is SQL SERVER Data Tools.

Initially After Downloading the Adventure Works 2019 Full Backup Database Which is .bak format I started creating the folder in the C Drive so that I could access the folder While importing this, Copy & Paste the bak file into any folder inside the C Drive. In my example I just renamed the folder to DW.After Jumping into this we need to import that file into the SQL SERVER , for importing we used SSMS which is the SQL Server Management Studio 2018.

The process of Importing the bak file is as follows:

1. Right Click on the Database
2. Restore Database
3. Click on Devices
4. And then Click Add

• After Clicking Add Give the path of the Adventure Works 2019 Full Backup .bak file, in my case I have uploaded to C drive inside the DW folder.
• Add the file Click OK

The Database of Adventure Works is uploaded, now we have that Structured Data, the main goal is to model that data into the warehouse, this is the most critical & important process. Once the Tables are imported. We need to add the tables for Warehouse.

This is the most critical part where we need to analyze what are the dimensions and fact tables for the Data warehouse. Either we only have the dimensions for the Data Warehouse? In this case, we need to create the Fact table with the help of dimension tables.

Creating the Data Warehouse in Visual Studio 2019 using Tabular Model.

Let’s take an example where we have the Database of Adventure Works and we do have the fact and dimension table available for creating the Data warehouse we need to have Visual Studio Tabular Model Services Extension installed in any Visual Studio Edition. I am starting with the Visual Studio 2019 Community Edition Where I am creating an empty project using Visual Studio Tabular Model Extension.

How to create an Analysis Services Tabular Project we need to follow the following steps

• Search the Tabular Project from the Search Bar
• Select the Analysis Services Model
• Click Next
• Set the Location to your Drive, in my case I have stored the Analysis Services Model under the D Drive
• Click Create

After Creating the Empty Project of Analysis Services Tabular Model, we are now diving into the designing of the Data warehouse.

Here in this scenario, we are going for designing a Star Schema (Type of Data warehouse) using a wizard base. Here we have the empty applications which is mentioned in the figure below.

The picture we have above is the Model.bim file here we have now and we are about to create a Data Warehouse in this.

The process to import the dimension and fact tables are as follows:

• First, we need to import the Data Sources for this purpose we need to follow the Tabular Model Explorer on the right-hand side in the Visual Studio
• Right Click on the Data Sources and Click on Import New Data Sources.
• Click SQL SERVER if the source is SQL SERVER, in my case SQL Server is the source of the Data.

• Click Next, After Clicking Next Add the “. “ In the server name because in this case, we are targeting the localhost , If the source or the Database is inside your PC then you need to add “?.?“dot as server name.

• Click Advanced
• Select Service Account Which is checked below in the Image

• Select Next
• Select Any of the Fact Tables and Click Select Related Tables

In this case, we have selected the Select Related Table Feature which automatically created the Data Warehouse on the basis of Wizard but in any case, if you want it to be done by yourself just select the fact table and the dimension table by yourself and click Finish.

• In Our Case, We have selected the FactInternetSales and We have the Star Schema Available for the Data Warehouse.
• We have the Star Schema ready as a Data Warehouse Now we have to integrate the Data Warehouse with any of the reporting tools we are now selecting Microsoft Power BI for integrating the Data Warehouse with the report.
• This type of architecture is safer and handier than any other approach.

Deployment of Data Warehouse into Analysis Services Model

In this case, we are going for the live connection between the Analysis Services Model and the Power BI. The Power BI does not support more than one live connection so we need to be careful before integrating the Data Warehouse with our model because in this case if the Data Warehouse does not have any requirement which has to be shown on the reported side then it might be very costly operation for anyone to go back and make the changes again in the Data Warehouse and Deploy again then integrate with the Power BI report.

The process of deploying the Data Warehouse into the analysis services is quite easy and handy. We just need to provide the Connection String if we are working on the localhost then we just need to mention it.

Follow the steps to deploy the data warehouse.

• Click on the Solution Explorer
• Right Click on the Solution Name and Click Properties
• Rename the Database to any of your choices, in our case we are selecting the Name "StarSchema-DW".

• Here In this scenario, we have changed the name of the Database and Click OK
• After Clicking ok Right Click on the solution name and Select Deploy then the solution will be deployed into the Microsoft SQL Server Analysis Services which is shown in the image.
• The Data Warehouse Star Schema is successfully is deployed into the analysis services which is shown above in the image.
• We can select any of the queries using DAX or MDX.
• Throughout the series, we will be following the DAX query standards which are handier and easier for diving through any data model.

Integrating the Data Warehouse (Star Schema) With Power BI

Now the last step is to integrate the Data Warehouse with Power BI Report. In this case, we are working very basic Power BI report because our purpose is to integrate the Data warehouse with the Power BI.

The process of integrating the Data Warehouse with the Power BI is very simple in this example we are creating a New & Empty Report.

• Open Power BI
• Click Get Data
• Select SQL SERVER
• SQL Server Analysis Database.
• Click Get Data

• Click OK Now we have successfully integrated the Data Warehouse with the Dashboard and Now we are creating a very simple dashboard.