Documentation
Documentation
Start typing to search…

OData API: Power BI Report with ABB Ability™ History

Step by step guide to create Power BI report using ABB Ability™ History OData API.

  • Create a new Power BI report file ex: PUMP.pbix
  • Go to Get Data menu item in Power BI Desktop application and select Web
  • Select Basic radio button
  • Enter the oData query as referenced in the OData API documentation and click on OK button.
Connection string should be added to start with odata queries.

Connection string should be added to start with odata queries.

  • OData Sample Query:
http://localhost/odata/FetchGraphData(startx='2023-10-27T00:00:00',classname='EquipmentHistory',endx='2023-10-27T00:30:00Z',wherestring='Path=%3F%20AND%20Property=%3F',whereargs=['Cars.FuelType.Petrol.Maruti',%20'RPM'],filter='AVG1min')
  • Select the authentication method as 'Basic' and provide the credentials to connect to the RTDB data source.
  • Click on Connect button to execute the oData Query on executing the OData query loads the Data Transformation window as shown below. The query result is converted as Power BI Table automatically.
  • Now, transform the data source as per steps below:

  1. Rename the data source as 'Parent', but this can be named as wished. All the JSON nodes will be shown as column names in the table.

  2. Convert the Data Source to Table

  3. Expand Value column as Record

  4. Expand the Record in Value column

  5. Removed the unnecessary columns, ex: '@odata.context', '[email protected]', '[email protected]', '[email protected]', 'value.Statuses'

  6. Now we can see only 'value.XValues' and 'value.YValues' columns as List

  7. Extract the data in the XValues and YValues columns as it's separated by a ','

  • Now we can create another table called XValues from the 'Parent' table. This may be done with a few steps described below. Also shown on screenshot.
  1. Choose Source ('Parent' table)
  2. Remove YValues column
  3. Split XValues column by delimiter
  4. Change type
  5. Add a new index column
  6. Reorder columns
Applied settings for creating a 'XValues' table

Applied settings for creating a 'YValues' table

  • Then we create another table from 'Parent' for YValues and we will call 'YValues'. We apply similar steps here as with the XValues above. Steps shown below.
  1. Define Source
  2. Remove XValues column
  3. Split YValues column by delimiter
  4. Change the type
  5. Add a new Index column
  6. Reorder the columns
Applied setting for creating 'YValues' table

Applied setting for creating 'YValues' table

  • Finally we create a table called 'Pressure' by joining XValues and YValues table based on Index column. The data in this table is used to create the Power BI reports. The screenshot below will show further information including applied steps.
  • Now we can navigate to Report view in the Power BI file we created in step 1 and create reports based on Pressure table.

Updated 5 months ago