Setting up the Power BI Connector to Query Your Reliability Data


This article explains how to use the Bentley custom connector for Microsoft Power BI and AssetWise Reliability oData API to query your Reliability (APM) data. You can then build visualizations and reports in Power BI Desktop.

Note:    If you wish to establish scheduled queries that run without user interaction, contact Bentley Support.

Before you begin:   Download and install the Microsoft Power BI Desktop at powerbi.microsoft.com. 

To Set up the Custom Connector

  1. Download the latest Bentley connector for Power BI.
  2. Extract the .mez file from the zip file. Copy the .mez file to your local folder [Documents]\Power BI Desktop\Custom Connector. If the folder does not exist, create it.
  3. Adjust the data extension security settings in Power BI Desktop:
    1. Select the File menu, Options and Settings, Options, and Security.
    2. Under Data Extensions, click (Not Recommended) Allow any extension to load without validation or warning.
    3. Select OK and then restart Power BI Desktop.
  4. In Power BI Desktop, click Get data from another source.

  1. In the Get Data dialog that appears, enter “bentley”:

  1. Select the Bentley AssetWise Reliability Service Connector and click Connect.
  2. Read the warning message that appears and click Continue. The tenant dialog appears:

  1. Fill in the following with credentials provided by Bentley Support Services:
    1. Clientid
    2. Tenantid
    3. BaseUrl
  2. In QueryURL, enter your initial query, for example, “fnd.sites” or “mnt.assets”. This step is not optional. In Power BI Desktop, you will be able to create and run additional queries.
  3. Enter a page size to limit the amount of data displayed at one time.

Tip: Loading large datasets into Power BI can take a long time. To limit the amount of data that is fetched, use $filter or $top in the query. For example, “mnt.assets?$top=1000” gets the first 1000 results from the database.

  1. Click OK. You might have to navigate Bentley connection and Login screens. For example:

Note: The authentication must be renewed every hour because the Access token has a 60-minute expiration period.

  1. Click Connect. You will see a window like this:

  

  1. Click Load. The Power BI Desktop appears. In this example, Query 1 (mnt.assets) in the Data column has been expanded to list the data:

  1. Next to “Query 1”, right-click “More options” and click Edit Query. In the Power Query Editor, expand the formula box to see the query:

 

You can change or duplicate the query and run it as needed.

Visit the AssetWise Reliability OData API Swagger page for information about building basic queries, including syntax and authentication. You can use the “try out” functionality on the Swagger page to see queries at work.