power-bi

Power BI Setup Instructions

Contents

Introduction

Realtime Data using Azure Stream Analytics (Power BI Service)

Historical Analysis using Cosmos DB (Power BI Desktop)

Introduction

Power BI is Microsoft's interactive data visualisation tool. This template provides two methods of visualising data from sensor devices - realtime analysis using Stream Analytics (requires a Power BI Free subscription or higher), and historical analysis of data stored in Cosmos DB (no subscription required unless you wish to publish/share the dashboard).

These reports are provided as a starting point for specialised analysis and do not contain any sensor-specific visualisations.

Realtime Data using Azure Stream Analytics (Power BI Service)

If you have deployed the template using the 'Deploy to Azure' button, click on the 'Manage your resources' link once all resources have successfully deployed:

If you deployed from the command line, open up a browser and navigate to your newly created resource group in the Azure Portal.

Once in the resource group blade, select the Stream Analytics Job:

Then select the Power BI output:

Click on 'Renew authorization' to sign in:

Once this has completed, choose the Power BI Group Workspace you wish to use for the dashboard from the dropdown at the top of the blade. This must have been created previously in the Power BI portal. Once you are happy with the workspace/dataset details, Click 'Save'.

Now click 'Start' to start the job:

Once this is complete, a pale blue banner with the text 'Running' will appear at the top of the Stream Analytics blade where it previously read 'Created'.

Now the job is running. You won’t see the dataset in the portal until it has started receiving data from Stream Analytics, at which point it will appear under the ‘Datasets’ menu in the Power BI portal as shown below:

To create visuals using this streaming data, do the following:

1. Select a dashboard from the Dashboards menu on the left (or create a new one).

2. Add a tile from the menu at the top of the page:

3. Select 'Custom Streaming Data' as the source:

4. Select your dataset (created by Stream Analytics):

5. Customise tile as required, click 'Apply' once satisfied (this can be edited later):

6. The final result will look something like the below. If there is no data being streamed to the tile currently it will appear as shown on the left. Otherwise, data will be displayed in near realtime as it is pushed from the Stream Analytics job (as seen on the right):

Historical Analysis using Cosmos DB (Power BI Desktop)

This section requires Power BI Desktop to be installed. This is available from Microsoft for free here.

Once installed, open up the Power BI Template IoTDashboard.pbit provided with this repo.

You will be prompted to enter your Inactive Devices API URL. This value can be found in the Azure Portal by navigating to the Function App deployed previously, selecting the 'GetInactiveDevices' Function and clicking on '</> Get function URL' in the top right corner as shown:

Copy this value into the prompt and append '&minutes=60' to the end of the URL - replace 60 with your preferred period of time before a device is considered 'inactive'. The result should look like the following:

https://iotdeploymenttestfunction.azurewebsites.net/api/GetInactiveDevices?code=<yourkey>&minutes=60

Click 'Load' to pull data from the API. At this point it will attempt to load data from the two sources specified in the template (Cosmos DB and the Inactive Devices API) - this will fail because we have not yet set up the Cosmos DB connection. Click 'Cancel' on the popup window to skip loading the data until the connection is configured:

Alternatively, wait for the load to fail and then click 'Close'. If prompted for a Cosmos DB account key, select 'Cancel':

Next, open Query Editor from the Home tab in the ribbon:

You will see the following screen:

Scroll to the top of the 'Applied Steps' pane until you see 'Source'. Click on the cog symbol to its right:

Edit the source settings to reflect the URL for your newly created Cosmos DB and click 'OK' (Cosmos DB will be initialised for you by the deployment script with default database 'db' and collection 'sensordatacollection', which have been auto-populated for you):

Enter the primary key for the Cosmos DB when prompted. This can be found in the Azure portal:

Repeat this process for the 'Device Data (Latest Readings Only)' query, found in the Queries pane on the left:

NOTE: This is a temporary workaround to be used until the Cosmos DB Power BI connector is out of preview and parameters are enabled for this connection type (after which the experience will be similar to that for the Inactive Devices API).

Click ‘Close and Apply’ in the top left of the Query Editor window. You will then be prompted for authentication for the inactive devices API – choose Anonymous and click 'Connect'.

Now that all the connections are correctly set up and authenticated, the visualisations should populate with your data and look similar to the below. If they don't, manally refresh the data by clicking on the refresh button in the 'Home' tab of the ribbon.

To publish this report to the Power BI Service, click the 'Publish' button at the right of the 'Home' tab in the ribbon. Choose your preferred destination Group Workspace and click 'Select' to deploy to the Power BI portal:

It will then appear under the 'Reports' menu in the portal: