Build Your First Report in Microsoft Power BI Designer Part 1, Basics

First of all I would like to briefly explain Microsoft Power BI Designer. Then we’ll see how easy we can create a report using designer. I will use Adventure Works DW database as the source database.

Microsoft Power BI Designer is basically an integration of Power Query and Power View. Saying that the tool is still a preview version and it’s NOT actually a released product we’ll expect to see more features when it’s released. Some features like PowerPivot models expected to be available to the release version. At the moment PowerPivot models are not available in the designer, but, hopefully Microsoft will add it to the tool. So I added an idea into BI in SQL vNext as I believe it would be great to have PowerPivot functionalities included in the release version of the product. We’ll see what happens.

Let’s have a look at the tool. At the first look, I would like to say it’s an amazing tool integrating lots of awesome features all together with ease of use. It’s so fun to use the tool to create very effective and flashy reports in a short amount of time. First of all you need to download the designer from here.  Install the designer and open it. I’ll use AdventureWorksDW2012 as the source database.

Open the Microsoft Power BI Designer Preview. If you want to get more familiar with the tool click on the videos on the startup screen.

·         To connect to SQL Server click on “Get Data” or “New Source”

·         As you can see there are lots of other data sources available to connect to. In our sample we need to connect to SQL Server. So, click on “SQL Server Database” from the list. Then click connect.

·         Key in the server name and database name and click OK

·         Click Connect

·         Now you should see a list of tables and views in the navigator. If you can’t, make sure you have access to browse the database and select the objects.

·         In this sample we want to create some reports for “Internet sales”, so we select all related objects. Whatever you can see in the following database diagram should be selected from the navigator list:

·         Simply tick all needed tables then click load

·         Drag “SalesAmount” from fields list and drop it in the middle of the designer

·         You’ll have the “SalesAmount” diagram in the middle of the report

·         Now drag and drop “SalesTerritoryCountry” from the “DimSalesTerritory” on the middle of the created diagram

·         Now you have a diagram showing SalesAmount by SalesTerritoryCountry

·         As you can see SalesAmount automatically moved to Value section and SalesTerritoryCountry moved to Axis section

·         Click on the “Change visualisation type” and click on the map to see the SalesAmount by SalesTerritoryCountry on the map

·         When you hover over a country you can see the sales amount for that specific country

·         Now we want to create a new diagram to show yearly sales amount. To do so you just need to drag and drop “SalesAmount” from FactInternetSales and “CalendarYear” from DimDate just like what we did befor.

·         Now we want to add a new page to the report. To do so, just click on the “New Page” button

·         Now we can drag and drop “SalesAmount” from FactInternetSales and “EnglishProductCategoryName” to the page to create another diagram.

• From File menu you can save the report. The BI Power Designer format is *.pbix.

As you see it is really easy to use the tool to create lots of visualisations in a short time.

In the next articles of this series I’ll explain how to use the tool to create more professional reports.