Introduction To Microsoft Power BI

Prerequisite

You will require a Power BI tool for desktop. You can download it from the official website here.

Introduction

Microsoft Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights.

Image source : https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-0-0-what-is-power-bi/

Power BI has three tools - a Windows desktop application called "Power BI Desktop" (which we are using in our example), an online SaaS (Software as a Service) service called "Power BI Service", and mobile "Power BI Apps" available on Windows phones and tablets, as well as for iOS and Android devices.

Image source : https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-0-0-what-is-power-bi/

Architecture of Power BI utility in our analysis solution will function like below.

Image source : https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-0-1-intro-using-power-bi/ 

There is one point of confusion in regards to Microsoft Excel which is that it already has the services Microsoft Power BI offers. So, what is the deal? Power BI is not a replacement of Microsoft Excel Services or limited to that. If you look at the first and third figures above, you will notice that Power BI is using Excel data as an input including other various data sources and then, it provides a powerful mechanism to customize you data representation in more consolidated manner. This question has been discussed on U.K community page. For more details, check this link.

Power BI Desktop Tool

I assume that by now, you have installed Power BI Desktop from prerequisite reference. So, let's get started to analyze a SQL Server sourced data in Power BI.

Step 1

Launch "Power BI for desktop".

Step 2

Click on "Get Data" and you will see the list of distinct sources to connect. Click on "SQL Server" and "Connect".

Step 3

Enter your Server name and database name and click OK (In my case, I am using local instance of SQL Server and AdventureWorks2012- sample database). If you are prompted to authenticate, please enter username and password to connect.

Step 4

Select the tables that you want to import data from. In my case, I have selected a total of seven tables from AdventureWorks2012 sample database. Up to this point, your Power BI tool should look like something below.



Let's spend some time in analyzing the Power BI tool. As you can see from the left side of the panel, there are three options - reports, data, and relations. At the top of it, we have a ribbon, similar to MS Office, available with multiple options of word processing and native tooling. At the right side of the panel, we have set of tools that are comparable with MS Excel Pivot tables, including features of different visualization. Last but not least to point here is the use of R. It is one of the very popular languages for data science. You can execute your R script in Power BI.

Let's click on relations from left panel. You can see all the relations between the tables that you can manipulate for your data analysis.



As we can see, Human Resource Employee has 1 to many relation with Purchasing Purchase Order Header which in turn, has 1 to many relation with Purchasing Purchase Order Detail, with approx 290, 4012, and 8845 records in my sample database.

Step 5

Let's go and click on reports. Then, draw a Treemap from visualization and check how many employees are Male or Female in the department. Click on Treemap and check or drag Gender (Employee table data) from Field in Group option. Then, drag marital status from same table and drop it to values in the Group tab option. You have first group of 206 Male and 84 Female employees on dashboard with their Marital status defined in data.



Let's try another similar example and take a fresh Linechart from Purchase Order Header - just check ShipDate and TaxAmt. You have thebelow result that can also be obtained from SQL Server with the following query 

 

SELECT Sum(TaxAmt) FROM Purchasing.PurchaseOrderHeader Where YEAR(ShipDate) = [Your Specific year e.g 2005]



Further, as our data has a specific date along with year, we can specifically develop the dashboard. For that, go to Axis --> Ship date and click on ellipsis button icon. Then, tick ship date and you will get more specific date wise details as in the below image.



Now, if we want to apply a filter , we just have to scroll a bit down. In the filter details, select Top N and enter 10 next to top Textbox as in the below image. Drag TaxAmt to By Value filter, then click on apply filter. You will have the result for top 10 tax amounts.



Conclusion 

In this article, I have introduced Microsoft Power BI and very basic dashboard creation using it. There are so many other data sources and utilities that we can customize in Power BI.

Reference

Power BI Official Website reference for architecture.

1. https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-0-0-what-is-power-bi/ 
2. https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-0-1-intro-using-power-bi/