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

image

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”

clip_image002

·         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.

clip_image003

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

clip_image005

·         Click Connect

clip_image007

·         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.

clip_image009

·         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:

clip_image010

·         Simply tick all needed tables then click load

clip_image012

clip_image013

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

clip_image015

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

clip_image017

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

clip_image019

·         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

clip_image021

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

clip_image023

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

clip_image025

·         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.

clip_image027

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

clip_image029

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

       image

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

       image

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.

2 thoughts on “Build Your First Report in Microsoft Power BI Designer Part 1, Basics”

Leave a Reply