Power BI Dashboard for Sales Data
Business Intelligence tools are getting more popular every day. With heaps of data available it’s quick, easy, and understandable presentation is what companies want next. Power BI is Microsoft based Business Intelligence tool that is easy to understand and implement. In this article, I will be sharing some basic tips and tricks to make a Power Bi dashboard for sales data. I will be using Adventureworks 2012 database which is sample data set provided by Microsoft. The main purpose of this article is to develop a basic understanding of Power BI.
Step 1. Installation and Setting up the environment
This step is for people who are not familiar with Power BI and setting up this tool for the very first time. If you are already familiar with Power Bi and it is installed on your computer you can skip this step.
Download link for Power BI
Download Power BI
Easy and simple installation of Power Bi just like any other Microsoft product. Follow the steps and it's done. Currently Power Bi is only available for Windows so if you are mac user, you can use AWS free tier to get a windows machine. I will write an article later on about how to set up a windows server using free tier AWS.
Once Power BI is installed you need to sign up with your work/university email address. Power Bi does not allow us to sign up with consumer email services or telecommunication providers. This includes outlook.com, hotmail.com, gmail.com, and others. If you don't have an appropriate email refer to the below link to find alternate ways to signup.
Alternate ways to sign up
Step 2. Download SQL server and AdventureworksDW 2012 database
If you already have SQL server installed you can skip SQL server installation and just download the Adventure works DW 2012. Make sure to download the DW2012 version.
Download SQL server 2017 express edition this is completely free.
After downloading install front end SQL Server Management Studio.
Once the download will be completed, open it and you will see a window like below
Click connect and you have connected your front end SSMS with your SQL server. Now you need a Database. Follow the below steps for downloading and loading it to SSMS.
1. For Database download Adventure Works sample database. Make sure to download the DW2012 version. It is a sample database provided by Microsoft.
2. When the database is downloaded, copy the database file to the below location.
C Drive → Program files →Microsoft SQL Server →MSSQL 14 SQL Express →MS SQL →Backup
3. Open SSMS on side pannel click on restore. On the restore window click the device and go to exact same path mentioned above where you have copied the database file. Your Database will be loaded and you can see all the tables. Explore all the tables to understand this database.
Step 3. Connecting Database to Power BI
Now your Power BI, SQL Server, and SSMS are installed. The database is loaded in your SSMS. The next step is to connect your Database with your Power BI.
Open Power BI click on get data, Select SQL server. Type in your server name which will be your computer name/SQLExpress, if you are following along this is the same name I have mentioned earlier in Fig ____. Click import.
You will be able to see all the tables of the database in the window.
For this article I will be using three tables from the database, so you can just import these three tables mentioned below.
1. DimCustomer
2. FactInternetSales
3. DimGeography
Select one table and you can preview it inside the window. Select all three tables and click load. It will take some time to load all three, be patient. After loading will be completed you will be able to view all three tables on your Power BI right-side panel under fields.
Step 4. Managing Relationships between tables
Now we have three tables but they are not joined together. So we have to manage the relationship between these tables. On Power BI desktop left side you will see the manage relationship button click it.
Power BI will automatically make relationships between tables, but we need to edit these relationships accordingly.
Number 1. Double click on the already made relationship between DimCustomers and FactInternetSales table and the edit relationship window will open.
Number 2. You will see two tables are joined by CustomerKey column.
Number 3. Cardinality (one to many) because one customer can have many rows in sales tables.
Number 4. Cross filter direction select BOTH.
Number 5. Click OK.
Repeat these steps to edit the relationship between DimCustomers and DimGeography. These two tables will be joined on Geography_key. Cardinality will be (many to one) because many customers can have same geographical data. Set cross filter direction to both and click OK.
Step 5. Plotting Visualization
Now our all three tables are connected and we are ready to plot our first visualization.
to be continued..