Monday, October 5, 2020

Fun with Microsoft Power BI - Part III - Advanced Ingest and Viewing

 By Tony Lee

Welcome to part III in this series of going from zero to hero using Power BI to ingest, process, and make amazing reports. If you have read some of our other articles you can probably tell by now that we enjoy making data actionable. Honestly, it doesn't matter what type of data or even where the data ends up. As long as we can make informed decisions using the data -- we love it. Following in this theme we are going to make BlackBerry (formerly known as Cylance) Protect Threat Data Report (TDR) CSVs actionable using Power BI and Power BI Desktop. You can use any data source to follow along in this series, but our example BlackBerry Protect report is shown below which we will happily share the Power BI file at the end of the series for you to load and analyze your own data, so stay tuned for that!

Figure 1:  Our Power BI report using BlackBerry Protect TDR data

In the first article, we covered:

  • Getting Started
  • Data Ingest
  • Adjusting Fields
  • Visualizations
  • Saving Your Work
In the second article, we covered:
  • Tabs
  • More Visualizations
    • Text box
    • Slicer
    • Table
    • Pie Charts
    • Treemap
  • Using Reports and Dashboards
  • Uploading Reports to Power BI Service (Online)
In this article, we will cover:
  • Question and Answer (Q&A) Feature
  • Power BI for Mobile
  • Changing the Data Source
  • Scheduling Data Refresh

Question and Answer (Q&A) Feature

Ever wish you could use natural language to ask questions and get answers about your data? In Power BI, you can do just that and even save the resulting visualizations into your report (if you have the necessary permissions). It may take a bit of trial and error, but the results can be both interesting and useful. In Power BI Desktop, this is activated by going to Insert menu > Q&A. In the Power BI Service online interface, click on the report you want and then the "Ask a question" button.

Figure 2: Question and Answer feature of Power BI

Power BI is nice enough to suggest a few questions to ask, however we will list some of our questions below as well.

Example searches include:
  • most recent threats data report
  • count of threats data reports by create time
  • show classification by sha 256, file name as matrix
Figure 3:  Example Q&A

Power BI for Mobile

One of the best features of Power BI is the platform flexibility. Once you build a report and have it available in your workspace, you can view it on nearly any mobile device: Apple, Android, and Microsoft.

We have an Android phone that we used to view the report and try out the filters. The app is available in the play store:

Figure 4:  Screenshot of our report displayed on a Galaxy Note 8

Changing the Data Source

In our first article, we wanted to keep things simple by uploading a static CSV file. This is great for building the initial report, but it becomes cumbersome to update the data. Fortunately for us, this CSV data came from a simple to use web API which Power BI can easily handle. In fact, Power BI supports a ton of data ingest options such as files, databases, cloud services (over 60 of them!), web APIs, and more. Now let's change our data source from static CSV file to web API, but first let's check the timestamp on the imported file. This can be achieved by hovering over the ThreatsDataReport field.

Figure 5:  Determining staleness and freshness of data

There is an option to refresh the data, but because the original data source is a static CSV, we would need to manually download it again. To change the data source, we need to Edit the query.

Figure 6:  Edit the query to change the source from static file to web query

This opens Power Query Editor. Next we click, Data source settings > Change Source... > Advanced > Then we use the file path parts to first enter the URL and then enter the token on the second line. This file will open the exact same as the static file.

Figure 7:  Changing the data source to using web API

Now that the source is changed to use the web API, we can refresh the data with a click of the button in Power BI using "Refresh data". After checking the timestamp on the new data, the update was successful!

Figure 8:  Refreshing data via the web API

Scheduling Data Refresh

Taking reporting one step beyond a manual refresh, we can also schedule a refresh of the data. In Power BI Service, expand the workspaces to see the Datasets. Click on the three dots next to the Web API data feed and select "Schedule refresh".

Figure 9:  Scheduling a refresh

Under the Schedule refresh menu, turn it on, enter the frequency and even the time to refresh. Another nice touch in this feature is email notification of failure.

Figure 10:  Settings for scheduling a daily data refresh


In this article, we looked at some interesting and more advanced features in Power BI to include the natural language Q&A feature, viewing reports on the Power BI mobile app, converting our static CSV file to update via web API, and finally scheduling the web API dataset to automatically update every day. Power BI is full of features and hopefully you learned something new today. Please feel free to leave feedback and your favorite Power BI features in the comments section below.

No comments:

Post a Comment