Overview

Power BI is an interactive data visualization software product developed by Microsoft, focusing primarily on business intelligence. It is part of the Microsoft Power Platform. The existing dashboards of WaterSight can be extended with custom Power BI dashboards to achieve utility-specific insights. Main steps include:

• Download PowerBI connector from WaterSight administration page
• Open PowerBI desktop and connect to WaterSight data using the downloaded connector (OpenFlows Connector)
• Build your own reports using PowerBI desktop capabilities
• Publish the reports into WaterSight

It is also possible for the water utility to include in WaterSight any previous reports created in PowerBI, even if were not build based on the WaterSight data. In that case it is just required to publish the reports in WaterSight, see section below "Publishing Reports into WaterSight".

 

Power BI Licences

The Free license gives access to use the Power BI Desktop product only. The reports are built using Power Bi Desktop app which don't need a license to run, but the user needs a license to publish to cloud for sharing.  

For users that will either publish PowerBI reports into WaterSight or will consult those from WaterSight, a PowerBI licence might be required as described below:

• The user who publishes PowerBI reports in WaterSight will require either a PowerBI Pro or PowerBI Premium Capacity licence;
• If the user publishing PowerBI reports in WaterSight has the PowerBI Pro licence, those other users that will access the reports also need to have a PowerBI Pro licence;
• If the user publishes the PowerBI report in a Workspace within a Premium Capacity, the viewers don´t need a license to access shared reports

Please note that reports needs to be published to the power BI cloud in order to be embedded inside WaterSight. 

More information can be found here:

Power BI enterprise

PowerBI pricing

 

Download PowerBI Conector

WaterSight's Power BI Connector must be in the right place for Power BI to recognize and make it available in the desktop application. If the Power BI Connector is not downloaded, please download it. If the downloaded content is a zip file, extract it. Please make sure the file extension of the connector file is ".mez".

The Power BI desktop, by default, does not allow loading custom connectors. Follow the steps below to enable loading custom connectors.

• Open PowerBI desktop
• File > Options and settings > Options
• From the Options dialog, Security > Data Exensions, select second radio option. You may need to close and reopen the Power BI Desktop again so that changes are reflected

Follow the steps below to load the Power BI connector that was downloaded from WaterSight. The default location for the custom Power BI connector is "C:\Users\<UserName> \Documents\Power BI Desktop\Custom Connectors\".

• Close all Power BI sessions (windows)
• Copy the connector file, mez, to this location. [C:\Users\<UserName> \Documents\Power BI Desktop\Custom Connectors\]
• Open the Power BI desktop application
• Navigate to Home > Get data > More

Make sure under the Get Data dialog, Other > OpenFlowsConnector  (Beta) is visible

 

Pull data from WaterSight

• Open the Power BI desktop application
• Navigate to Home > Get data > More
• From the Get Data dialog, Other > OpenFlowsConnector (Beta)
• Click on Connect button

 

After clicking Connect on the Get Data dialog, a Navigator dialog is loaded.

• Expand the Digital Twins section under the OpenFlowsConnector group
• Another dialog box "OpenFlowsConnector" will show up. Click on the Sign in as different user button.  

• Enter the credentials to log in to Bentley's Connect account.
• Click Allow button with all the preselected options

• Click on Connect button

Predefined Tables (example)

The OpenFlows connector comes already with predefined tables and data that can be easily used to create the PowerBI reports:

• Expand OpenFlowsConnector
• Expand Digital Twins
• Expand the Digital Twin of interest
• Expand Realtime Data
  • Select OF_Signals
• Expand Time Series (Fifteen)
  • Select one of items
• Click Load

Customized Tables (Custom Get)

The OpenFlows connector comes already with predefined tables and data (as mentioned above) however not all the data that is in WaterSight is listed there. 

The user has the ability to connect to any specific data that exists in WaterSight and build reports from that, even if not listed in the predefined tables. To do this:

• Expand OpenFlowsConnector
• Expand Digital Twins
• Click on the Custom Get function

• In the Parameters:
  • In the first field, insert the WaterSight endpoint for which you want to get data. Example: 
    • api/v1/GeographicFeatures/DataFeaturesBulk?digitalTwinId=XXX&dataType=YYY
    • Where: XXX = Digital Twin ID | YYY: Name of GIS layer in WaterSight (Pipes, Zones, etc)
  • Region (optional): leave blank for projects done in Azure based in US-EST. For European projects put weu
• Click Invoke

• A new Query is loaded in the left panel (Invoked Function)

For more information about the available endpoints, reach to your Bentley point of contact. The article below contains a detailed example on how to use the Custom Get function to pull GIS data from WaterSight into PowerBI:

Integrating GIS data from WaterSight to PowerBI

 

Building Reports

Once the data are loaded, they appear as a table under Fields panel. Depending on the Digital Twin connected, the name of the tables may differ.

 

Create Time Series Chart

• From Visualizations panel, click on Line Chart to add to the report page
• Drag and drop, Name to the Legend field
• Drag and drop, TimeStamp to the Axis field
  • Make sure TimeStamp is selected instead of Date Hierarchy
• Drag and drop, Value to Values field

 

Publishing Reports into WaterSight

After concluding the construction of the Dashboard, in order to make the report available in WaterSight it's necessary to Publish the dashboard to Power BI's web capacity of your company.

• To do so, select File > Publish > Publish to Power BI thru Power BI Desktop application

• Click in Save if Prompted

• Select the Workspace you want to save to

• Click in Select
• Once done, click to Open the Dashboard in Power Bi Web

• It opens the Published Dashboard in your default internet browser
• Select File > Embed report > Website or portal

• Copy the URL in the first box:

• Load your Digital Twin in WaterSight
• Open its Administrator page
• Click in Edit under the Power BI Tile

• Click in Add button (+)
• Paste the URL copied in step 7 in the field "Embed URL"
• Set a name for the Report and Select the most appropriate WaterSight page to put it available using the Drop down menu under "Menu Entry"

 

To access Power BI reports, user will use the Power BI credentials and not WaterSight's. And the creator of the report should give access permission to users thru Power BI's tools. For more information about Power BI licensing and access control please consult Power BI's documentation.

 

Automatically updating reports

The user can keep the PowerBI reports up to date, by pressing the Refresh button inside PowerBI desktop. For automatically updates of the PowerBI reports, the user needs to install the PowerBI On-Premise Gateway. More information here: Automatically update of PowerBI reports into WaterSight

 

See Also

Automatically update of PowerBI reports into WaterSight

Accessing PowerBI reports in WaterSight

PowerBI Administration

OpenFlows WaterSight TechNotes and FAQ's

 WaterSight - Learning Resources Guide