DWH: SQL Server and Power BI-Part II

In the previous article we have seen how to create a DWH and now it’s time that we move to the next step and that is connecting Power BI to our DWH to see what we can achieve with that combination. 


Why would we want to use any BI tool?

Storing data in the database is necessary from the technical side, but analyzing these data to get insights on where your business is and/or where is it heading is also necessary, but this time from the business side.  You can always go with extracting data + Excel and that will work well until some point or until you have a limited number of well-structured data sources. In case you have many different data sources (databases, Excel files, CSV, etc.), you’ll need to somehow relate them and then perform your analysis. And if there are some changes, in a data structure or even in data, you’ll need to make modifications that would require some time. Most BI tools, including Power BI, come with the option to import data from different sources, relate them, create dashboards and refresh dashboards after data is refreshed. This way, you’ll spend less time on technicalities and have more time to analyze the numbers in your reports and go with conclusions.

DWH – a quick overview

Before we start with new “stuff” let’s quickly remind ourselves where we stopped last time.


data model


We’ve created a DWH data model in SqlDBM we could use to aggregate and store our sales or marketing data. The model itself consists of 5 dimension tables and 1 fact table. After that, we’ve imported our data model into MS SQL database.


SQL Server


The next thing we need to do is to populate this database with dummy data. After that, we’ll connect Power BI to our database and create a few reports.



Inserting dummy data

Before we can start analyzing anything we need to have some data in our DWH. I will “invent” some and insert them into our database. I won’t go into details about the commands used (basically this will be a series of insert commands) or data itself.


I’ve decided to insert a few distinct values in dimension tables: 5 in dim_time, 2 in dim_location, 3 in dim_product, 2 in dim_sales_category and 2 in dim_salesperson. Taking that into consideration, the maximum number of inserted rows in the fact table could be 120 (5*2*3*2*2). The reason for that is that the primary key of the fact table is composed of a combination of all 5 foreign keys from the dimension table (we don’t want to have the same combination more than once). In the fact_sales table, I’ve inserted 30 records, 6 for each date (6*5).


Installing Power BI and connecting to DWH

The first thing you’ll need to do is to download Microsoft Power BI. I went with the desktop version because it will be enough to show what we need. The installation process is pretty straightforward and mostly includes pressing the “Next” button.


Power BI


The screen on the picture above is the one you should see after successful installation. Now we need to connect to our data. To do it so, we’ll click on the “Get data”.


Power BI


We’ll choose “Database” and then “SQL Server database”.


Power BI


After typing “Server” and “Database” and clicking the “OK” button, we’ll finally see our database (picture below).


Power BI


I’ve selected all 6 tables and loaded them in the Power BI.


Creating a simple report

Now we’re ready to move to the fun stuff. The first thing I’ll do is creating one simple graph/report with only 1 value and 1 segmentation. I want to see our charges per city.


Power BI


As you could guess, this is pretty simple. We’ll select the graph type we want and columns from our tables which contains the data we need. These are the fact_sales.total_chraged and dim_location.city_name. The graph is generated automatically on a previously blank report canvas.


You can easily switch to different graph type in the “VISUALISATIONS” section. I’ll go with the pie chart graph type.


Power BI


Adding more reports

Having a graph is really cool, but creating the entire dashboard is way cooler. The same way we’ve created our first graph, we’ll create one more.


Power BI


This time I want to create a simple table, showing us a list of all our salesman and their results. To do it, I’ll select “Table” under “VISUALISATION”, dim_salesperson.first_name and dim_salesperson.last_name. For each of them, I was to show all 3 financial-related columns from the fact table: fact_table.number_sold, fact_table.total_charged and fact_table.total_cost. You can easily resize table (or graph), so all the data are visible.

Please notice that the way you select columns is important. The columns shall be displayed in the table in that same manner.


For the last table in our report, I’ll create one more table, but using one more option we have at our disposal.


Power BI


Everything is the same as in the previous example, except for the fact that I want to add filters. And once more, this is a pretty easy task. In order to add filters, you can easily drag and drop category you need in the “Report level filters” section. This will allow you to simply switch on and off the categories you want or don’t want to show in your dashboard.


Additional tools

We went with only a few basic graphs/tables here, but there is much more that Power BI offers. Describing everything would simply go beyond the scope of this article, but still, I want to mention that I find “R script visual” and “ArcGIS Maps for Power BI” very nice add-ons to Power BI. There is no better way to learn something and find do you need it or not, besides playing with it, so I strongly encourage you to explore Power BI.


Creating “sexy” reports from your data is not so complicated as you could think at first. Power BI enables you to use data, not only from your databases, but also from other sources, like Excel or CSV files. This way, you can combine data from various different sources and create nice and intuitive dashboards.

 I hope that this short series of 2 articles provided you with enough courage to create DWH and relate it to POWER BI. Who knows, maybe that’s a start of your career as a data analyst!?

Leave a Reply

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