The world’s leading publication for data science, AI, and ML professionals.

Minimum Viable Streaming Dashboard in Power BI: Part 3

Featuring report development in Power BI Desktop and publishing reports to Power BI Service

Final Power BI Report using Push dataset. Image by author.
Final Power BI Report using Push dataset. Image by author.

Welcome back to the final part of the Minimum Viable Streaming Dashboard series.

In this post we’ll cover how to build a Power BI report, using the Push dataset we created back in Part 2.

To refresh your memory, we currently have an Azure Stream Analytics (ASA) job running in Azure cloud that is writing events to a Power BI Push dataset.

Application architecture diagram. Image by author.
Application architecture diagram. Image by author.

Now, all we have left to do is to develop our Power BI report by connecting to that dataset.

Build a report in Power BI Desktop

Before we start, go ahead and download Power BI Desktop from Microsoft’s website.

Download the 64-bit version shown below.

Download Power BI Desktop. Image by author.
Download Power BI Desktop. Image by author.

Once you’ve finished the installation, search for Power BI Desktop in the Windows search bar, right-click on the app icon, and pin the app shortcut to your taskbar.

You should see a yellow chart icon on your taskbar like the one below:

Pin Power BI Desktop app shortcut to taskbar. Image by author.
Pin Power BI Desktop app shortcut to taskbar. Image by author.

Connect to the Push dataset

Open Power BI Desktop by clicking on the app icon you pinned on the taskbar.

Open Power BI Desktop app. Image by author.
Open Power BI Desktop app. Image by author.

Click on Get Data, and log in using your Power BI account.

In the search bar for the various connectors, search for Power BI datasets.

Power BI datasets connector. Image by author.
Power BI datasets connector. Image by author.

Click on the yellow Connect button in the bottom right.

Search for the name of the Push dataset you created back in Part 2. Mine is called event-hub-dataset.

Connect to Push dataset created in Part 2. Image by author.
Connect to Push dataset created in Part 2. Image by author.

Click the Create button to establish a live connection to the dataset.

In the Fields pane in the far right, you will see all the columns created by the ASA job.

And near the bottom of the page, you will see a message that says you are connected live to the Power BI Push dataset.

Establish live connection to Power BI Push dataset. Image by author.
Establish live connection to Power BI Push dataset. Image by author.

Introduction to Power BI Desktop

I will take a minute here to explain the elements we’ll be using on the main Report tab.

Main Report tab for report development. Image by author.
Main Report tab for report development. Image by author.

This is the Report tab, where you can design visualizations, add interactive filters, and configure your report for the end user.

The big whitespace in the middle is termed the Canvas, and is the main interface your users will use to interact with the published report.

On the far right is the Fields pane mentioned earlier. This is where all the tables and their respective columns show up.

You can see that for some of the columns, there is a Summation symbol beside the column name.

Summation symbol indicating default summarization of values. Image by author.
Summation symbol indicating default summarization of values. Image by author.

Whenever you see this, Power BI is applying default summarization on that column whenever it is used as a value in a visual.

To better control how the column is summarized, it is considered best practice in Power BI to create explicit measures that define how we want to summarize the column.

We can do so, by clicking the ellipsis beside the _event_count_ column name, and clicking on New Measure.

Create new explicit measure in Power BI. Image by author.
Create new explicit measure in Power BI. Image by author.

Enter the following DAX expression into the formula bar that pops up at the top of the report canvas:

# of Page Views = SUM('<dataset-name>'[event_count])

Press the Enter key on your keyboard.

Now, whenever we use the # of Page Views measure in one of our visuals, Power BI knows to give us the total number of page views.

With # of Page Views measure selected, change the Format option in the Measure Tools Ribbon to Whole number.

Also, proceed to select the Comma icon in the Formatting section.

This will format our page view metrics to be more pleasing to the human eye.

Format measures in Power BI Measure Tools ribbon. Image by author.
Format measures in Power BI Measure Tools ribbon. Image by author.

Design the Visuals

Let’s get started creating some visuals for our users to interact with.

We are going to create a summary page that shows the overall number of events that happened on our website.

And we are also going to allow users to drill down into the details of each event.

1. Summary page

In the Visualizations pane, find and select the Card visual.

Tick the checkbox next to the # of Page Views measure we created to add the explicit measure to the card.

This will display the total number of page views to date.

Card visual displaying total number of Page Views in Millions. Image by author.
Card visual displaying total number of Page Views in Millions. Image by author.

With the Card visual selected, go over to the Format tab (paint roller icon) in the Visualizations pane.

Expand the Data label dropdown, and change Display units to None.

Change display units of Card visual to show total number of Page Views. Image by author.
Change display units of Card visual to show total number of Page Views. Image by author.

Next, select the Map visual from the Visualizations pane, drag _geo_country to the Location field box, and then drag geo_region_name below geo_country_.

This will effectively create a hierarchy that allows you to drill down from the country level to the regional level.

Add # of Page Views to the Size field box, and you will see the global spread of where our visitors are coming from.

Map visual with geo hierarchy showing global spread of website visitors. Image by author.
Map visual with geo hierarchy showing global spread of website visitors. Image by author.

Select the Click to turn on Drill down arrow to activate Drill down mode for the Map visual.

Activate Drill down mode for Map visual. Image by author.
Activate Drill down mode for Map visual. Image by author.

Then, click the US bubble near the North America section of the map.

This will allow you to drill down to the regional level and be able to see the different regions of the US.

Drill down to US regional level on Map visual. Image by author.
Drill down to US regional level on Map visual. Image by author.

The region names won’t totally make sense here as our events are programmatically generated.

Notice how the Card visual metric changes as we interact with the Map visual on the same canvas.

Power BI enables the visuals on the same canvas page to interact with each other as a default setting.

This is really convenient, because it saves the report developer an additional step of configuring the interactions themselves.

Click the Drill up arrow to go back up one level to the country level, and deactivate Drill down mode on the Map visual.

Deactivate Drill down mode on the Map visual. Image by author.
Deactivate Drill down mode on the Map visual. Image by author.

Now that we know where most of our traffic is coming from, we can also analyze how users ended up on our website.

To do so, add a Donut chart visual to the canvas, and check # of Page Views and _utm_source_ column.

Make sure # of Page Views is added to the Values field box, and _utm_source_ is in the Legend field box.

Donut chart visual showing ad platform performance. Image by author.
Donut chart visual showing ad platform performance. Image by author.

Now we can see the number of Page Views by the website source. This tells us which ad platforms our marketing campaigns are performing better.

With the Donut chart visual selected, go over to the Format tab in the Visualizations pane.

Expand the Detail labels dropdown, and change Display units to Thousands.

Change display units of Donut chart visual to show total number of Page Views in Thousands. Image by author.
Change display units of Donut chart visual to show total number of Page Views in Thousands. Image by author.

Click the US bubble in the map chart, and then Ctrl+Click on the google section of the Donut chart visual.

Filter Card visual using multiple visuals on the same canvas. Image by author.
Filter Card visual using multiple visuals on the same canvas. Image by author.

The number in the Card visual shows the total number of users in the US who reached our website from marketing campaigns we launched on Google.

Finally, let’s add a date slicer to the canvas.

Click on the Slicer visual, and check _event_timestamp_ from the Fields pane.

You should see a slicer appear like the one below, that allows users to view information across a specific time period.

Date slicer that allows users to specify the time period filter. Image by author.
Date slicer that allows users to specify the time period filter. Image by author.

With the Slicer visual selected, go over to the Format tab in the Visualizations pane.

Turn off the Slicer header.

Next, expand the Date inputs dropdown, and change Text size to 12.

Arrange your visuals as shown below:

Final dashboard design layout with Date slicer text size formatted. Image by author.
Final dashboard design layout with Date slicer text size formatted. Image by author.

Rename this page to Campaign Overview.

2. Detail Page

Add a new blank report page, and rename it to Campaign Details.

Create new Campaign Details report page. Image by author.
Create new Campaign Details report page. Image by author.

Insert a Table visual into the canvas, and add these columns to the table in the following order:

  • event_timestamp
  • of Page Views

  • utm_source
  • utm_campaign
  • utm_content
  • user_custom_id
  • geo_country
  • geo_region_name
Table visual displaying campaign details. Image by author.
Table visual displaying campaign details. Image by author.

With the Table visual selected, go over to the Format tab in the Visualizations pane, and expand the Style section.

Change the style of the visual to Minimal for better readability.

Change Table visual style for better readability. Image by author.
Change Table visual style for better readability. Image by author.

Let’s change some other Format settings in the Table visual.

Expand Column headers section, set Font family to Segoe UI Semibold, and Text size to 11.

For Values, leave Font family as Segoe UI, and set Text size to 11.

Your final table result should look like this:

Final Table visual after changing font options. Image by author.
Final Table visual after changing font options. Image by author.

Now go back to the Fields tab under the Visualizations pane.

Scroll down till you see a box that says Add Drill through fields here.

Drag the following fields to the section:

  • geo_country
  • geo_region_name
  • utm_source

This will tell Power BI to listen for any users who want to drill through into the detail page from other pages in the report using the fields you added.

Add Drill through fields to Campaign Details page. Image by author.
Add Drill through fields to Campaign Details page. Image by author.

If you go back to the Campaign Overview page now, and right-click on US in the Map visual, you should see an option titled Drill through.

Drill through into details page from Campaign Overview page. Image by author.
Drill through into details page from Campaign Overview page. Image by author.

Mouse over that option, and click on Campaign Details to drill into that detail page.

Notice how all the filters present in the Campaign Overview page will carry over to the detail page i.e. only US page views are displayed to the user.

Campaign Details page with filters applied from overview page. Image by author.
Campaign Details page with filters applied from overview page. Image by author.

Ctrl+Click the arrow in the top left to go back to the summary page.

Save your Power BI Report by clicking on the Save icon in the top left corner of the UI.

Publish your report to Power BI Service

Now we have our report, click on Publish in the Home ribbon, and publish the report to a workspace.

I’m just going to use the same one back in Part 2 where the event-hub-dataset is located.

Publish final report to Power BI Service. Image by author.
Publish final report to Power BI Service. Image by author.

Sign in to Power BI Service and take a look at your report in the workspace which you published it to.

Now functionality such as filtering, drill-throughs, and tooltips are available to us!

This is the benefit of using a Push dataset that can analyze historical data.

View published report in Power BI Service. Image by author.
View published report in Power BI Service. Image by author.

Send events to Event Hub

Let’s send some events to Event Hub again using the _send_events.py_ file we created back in Part 2.

Give some time for the ASA job to finish processing the events, and refresh the report.

You should see your metrics get updated instantly, albeit without the smooth animations that were previously seen in a Streaming dataset.

Test functionality of published report in Power BI Service. Gif by author.
Test functionality of published report in Power BI Service. Gif by author.

Power Bi Push datasets only store a maximum of 200k rows.

If you see your # of Page Views decrease, it is because you’ve already hit that limit, and the older rows in the dataset have been pushed out in a first-in-first-out (FIFO) fashion.

Conclusion

We’ve finally come to the end of the Minimum Viable Streaming Dashboard in Power BI series.

In this part we covered how to develop a Power BI report using a Push dataset, and published it to Power BI Service to expose curated data and insights back to our end users.

If you learned something new that was useful to you in these posts, feel free to follow me for more content about building modern data management platforms.

Stream Analytics Cost Model: Per Hour billing based on no. of Streaming Units

At $0.127/hr, with 3 Streaming units running 24/7 throughout the day, you only pay $283 per month.

There are no servers to manage, so you can just focus on the output of your code.

Clean up resources in Azure Portal

Since we are not sending events any more, you can go ahead and stop your ASA job in the portal.

Otherwise, unnecessary cost will be incurred.

Stop Azure Stream Analytics job. Image by author.
Stop Azure Stream Analytics job. Image by author.

You can leave the stopped ASA job, and the Event Hub if you think you might still need them in the future, as they incur no cost if the ASA job isn’t running / processing events, and the Event Hub isn’t ingesting any new events respectively.

Resources

Once again, I’ll include links to Part 1 and Part 2 if you came directly to this article without going through those first.

All Microsoft documentation used for research purposes are also linked below.

  1. Streaming Units in Azure Stream Analytics
  2. Azure Stream Analytics | Pricing
  3. Event Hubs | Pricing
  4. Minimum Viable Streaming Dashboard in Power BI: Part 1
  5. Minimum Viable Streaming Dashboard in Power BI: Part 2

All of the Python code snippets used today can be found in the Github repo:

GitHub – natworkeffects/power-bi-streaming

Thank you for reading through this series, and see you again in the next article.


Related Articles