Welcome to an add-on episode of our MS Azure tutorial series. In the previous episodes, we presented tools of the Azure cloud such as data lake, key vault, Synapse, and data factory. We used these tools to set up a data warehouse for the Northwind test dataset. However, the aim of the whole data warehousing process, in general, is to enable an effective business analysis of the given data. In this tutorial episode, we show how to connect MS Power BI to the previously created Synapse DB and visualize the data.

1. Get the Data

As soon as you have installed your Power BI desktop, you can start it and get the data from your data source. Go to the Home tab and click the Get data button. Choose Azure - Azure SQL Data Warehouse, if you have your data warehouse up and running in Synapse.

Choose the Data SourceChoose the Data Source

On the next page, you are asked to type the server name and the connectivity method. In most of the scenarios, you would choose DirectQuery. All queries that you create in Power BI are pushed down to the actual source with this method. The import method on the other hand loads the data to local storage. This is only possible for small test scenarios or for data that is stored as files because operations such as joins are way faster computed on the source itself. On the next page, you enter the database user name and password.

Choose the Connectivity ModeChoose the Connectivity ModeProvide Login CredentialProvide Login Credential

Be aware that you have to enable public access in order to get the data from your Synapse DWH. Therefore, you go to the Synapse resource and add your client IP to the firewall white list.

White List the Client IP in the Firewall SettingsWhite List the Client IP in the Firewall Settings

Now you should be able to choose what data you want to use. Here we went for all the tables that are available. This completes the "get data"-process.

2. Create Reports

In order to create a proper report we first need to set the relationship between tables and prepare the data.

2.1. Table Relationships

We arranged our data warehouse in fact and dimension tables. Those are related via keys. We need to set this connection manually if Power BI has not recognized them automatically.

In order to do so, go to the "Modeling"-tab and choose "Manage relationships". If there are wrong references, you should delete them. For a first overview dashboard, we introduced the references between:

  • FactEmployee - DimEmployee

  • FactProduct - DimCategory

Manage the Table RelationshipsManage the Table Relationships

2.2. Data Preparation

In this short section, we want to make sure that Power BI understands the columns the way it should. In order to check that, you should go through the available fields that you find on the right side of your panel. Some fields may have a summation symbol aside. That means that Azure would interpret that field as to be aggregated upon. This also happens to fields such as numerical IDs or coordinates, which do not make sense at all. If you click on such a suspicious candidate, you can change these settings in the menu on the top. In that case, we changed the category_id to not be summarized. You can also change the data type if it was interpreted wrongly.

Data PreparationData Preparation

2.3. Creating Business Reports

You may have seen that the data warehouse that we constructed aimed to answer several very specific questions. In a usual data warehouse, you would probably have more general fact and dimension tables. However, the specific questions can be answered now in a very fast way. In Power BI, your report can have multiple pages.

Here we created only one overview page, that answers the following four questions:

  1. Which Employee was involved in how many orders?

  2. Which Employee was involved in how many product sales?

  3. Which category of a product was ordered how often?

  4. Where do the customers come from and how many orders are connected to their cities?

Furthermore, we enabled to pick only specific countries of customers in a slicer.

Affiliated Orders and Products per Employee Orders per Category and Total 0rders per Customers City Together with a Slicer on the Country and a MapAffiliated Orders and Products per Employee Orders per Category and Total 0rders per Customers City Together with a Slicer on the Country and a Map

We created the dashboard by choosing the following fields and settings.

1. and 2. We choose the clustered column chart visualization. We use the last_name column from DimEmployee as Axis and the num_orders_affiliated and num_products_affiliated, respectively, from the FactEmployee table as values.

3. Again, we choose the clustered column chart visualization. We use the category column from DimCategory as Axis and the times_ordered from the FactProducts table as value.

4. In order to show the customer's office location, we took the FactCustomer. Here we first create an overview map by choosing the map visual and the city column as the field of interest. Furthermore, we choose a stacked bar chart and add the city column as axis and total_orders as values. Often you might want to slice your data to focus the report on a few sub-selections. Therefore we choose the slicer visual and add the country column as a field. In the options of the slicer, we disabled the Multi-Select with Ctrl. and enabled the "Show all" option.

 Settings for the Order-Category Bar Plot  -  Settings for the Country Slicer Settings for the Order-Category Bar Plot - Settings for the Country Slicer

2.4. Publish the Report

Now that we have created a report answering some specific questions, we can also share these visualizations with our team members. In order to do that, go to the Power BI online landing page Power BI online landing page and register there. Inside your Power BI online, you have access to your personal workspace "My Workspace". You can also create new workspaces and give access to your colleagues. Going back to the Power BI desktop, you can see a button "publish" in your home tab. Pressing that button will allow publishing your report to a workspace that you have access to. Here we created a QimiaTest workspace.

Publish ReportPublish Report

This QimiaTest workspace and the Northwind report can now be accessed in the online version.

Access the ReportAccess the Report

You can also create a dashboard in your workspace. The dashboard is a one-page overview of several reports. You can also add some descriptive text or links to the dashboard.

Create a DashboardCreate a Dashboard

In order to include your visuals in such a dashboard, you have to enter that report. If you click on the figures that you want to present, you can choose the "pin visual" button. This makes the visual appearence in the dashboard you choose.

Pin VisualPin Visual

Be careful! Some of the options that you chose in the report previously might be reset in the dashboard. However, you can still edit details such as displaying titles or subtitles even on the dashboard level.

Wrapping It Up

This concludes our tutorial on data analytics with Power BI. We covered features such as managing table relationships, publishing reports, or using Azure Synapse as a data source with DirectQuery option enabled. Again, we emphasize that the main work of that reporting lies in the proper data warehouse. Once the data is easily accessible in a star schema, there is no need for time consuming analysis and algebra within an analytics tool such as Power BI. Instead, you can concentrate on creating nice visualizations with it. If you are interested in how to do such advanced data warehousing and its best practice, just check out our channel for new articles! We have a series on that in our pipeline!

Sources