Power BI

Content by Charles Sterling

Dashboard in Day Afternoon Demo

(in process)

The new Dashboard in a Day training will cut the On-premises Gateway section as this is typically done by administrators  not the analysts  and to reduce the duration of the training.  This demo is designed to accompany the Dashboard in a day kick off Demo  but the afternoon demo will focus on strategies for keeping the report data current.

While this training isn’t designed as in-depth coverage of the strategies for keeping report data current  there are three basically “modes” Power Bi runs in:

  1. Importing the data into Power BI and using the On-Premises Gateway
  2. Live Connections to Analysis Server
  3. Direct Query to various

As this training focuses on the most common Power BI “mode”- importing your data into a Power BI model and keeping it updated with the On-premises this first demo is to show how to install and configure the Gateway.

Power BI Service – Refreshing data on the Dashboard

Once the dashboard has been shared with the members of the team, one of the key benefits of Power BI is the ability to setup automated data refresh. This enables the dashboard to be live and operational for the organization. If your data is being retrieved from a cloud data source such as SQL Azure, then you can click on the Dataset … and schedule the refresh as per your organization needs. In this section, you will learn to setup On-Premises Gateway that allows the author of the report and dashboard to refresh the content in the data model from an on-premises data source.

Installing the Gateway

Go to https://powerbi.microsoft.com/en-us/gateway and select download

After the Gateway is downloaded install the Gateway…including the screen shots in case you want to just create slides.

Next step is to configure the gateway on the Power BI Service by adding data sources that can be accessed by the gateway and the users who have

Login to http://app.powerbi.com; from the menu on the top right, click on the gear and select Manage gateways.

You will be navigated to Gateways screen. Notice the gateway you created is available. On the right panel of the screen you can add more information regarding the gateway using Gateway Settings screen. On the right panel of the screen there is also an option to add Administrators for the gateway. Let’s add data sources that are used in our model so we can set up a refresh schedule.

Click on the ellipsis next to the gateway name and select ADD DATA SOURCE.

We need to configure 3 data sources that are used in the model:

  1. bi_dimensions.xlsx
  2. sales.csv
  3. InternationalSales

In the Data Source Settings page enter the name of the data as Dimensions.

From the Data Source Type drop down select File.

Enter following for the Full Path C:\DIAD\Data\USSales\bi_dimensions.xlsx (if your file is in a different location change the path accordingly).

Enter the Windows username and password (this is typically the username and password you use to login to your machine).

Select Add.  Once the data source is added, notice you can add Users who can access this data source.

Let’s add the Sales.csv data source file. Click on the ellipsis next to the gateway name and select ADD DATA SOURCE. In the Data Source Settings page enter the name of the data as US Sales.

From the Data Source Type drop down select File.

Enter following for the Full Path C:\DIAD\Data\USSales\sales.csv (if your file is in a different location change the path accordingly).  Enter the Windows username and password.

Let’s add the International Sales folder data source.

Click on the ellipsis next to the gateway name and select ADD DATA SOURCE.

In the Data Source Settings page enter the name of the data as International Sales.

From the Data Source Type drop down select Folder.

Enter following for the Full Path C:\DIAD\Data\InternationalSales (if your file is in a different location change the path accordingly).

Enter the Windows username and password (this is typically the username and password you use to login to your machine).

Setting up the Refresh Schedule

Now let’s look at how refresh is going to work.

If the left panel is collapsed, expand it by clicking on the three bars below PowerBI.

Navigate to Datasets section on the left panel and locate the dataset called DIADReportFinal. Click on the ellipses and click on Schedule Refresh as shown in the Figure.

 

Worth pointing out that DIAD Workspace is running in a shared capacity therefore limited to nine refresh schedules/day changing this to a premium capacity ups this limit to 48 refreshes/day.

Suggested Demo Duration 10 minutes

 

Real time Dashboards

In the kick off demo it was demonstrated how real time tiles can add to the dashboard experience.  Traditionally real time data sets required development skills.  In this section it will be demonstrated how easy real time data sets can be created in Power BI and populated with Microsoft Flow.

In the creation dialog point out the fact that real time datasets are limited to: Text, Number and Date data types.

Rather than creating your own dataset and connecting it up to a dashboard it is probably easier dismissing the creation dialog and using existing the dataset “TweetsOut”.

An interesting demo side track is to show how you can use Power Shell to update the dataset.  Select the Tweetsout dataset and select the “i” glyph.  Navigate to the Power Shell Tab, select the Power Shell script and paste into a Power Shell command prompt.

Navigate to the Power Shell Tab, select the Power Shell script copy it and paste into a Power Shell command prompt.  Viewing the DIAD Dashboard you will see the Power shell has updated the dashboard!

Now to use Microsoft Flow to update the Power BI Dataset

Go to http://Flow.microsoft.com and log in using the DIAD Instructor credentials.  Navigate to “My flows”.

In the next page select the option: “Search hundreds of connectors and triggers

Suggested Demo Duration 10 minutes