Creating an Asset Dashboard in PowerBI using ODATA

You have a few options when it comes to finding the asset you want to report on in ARDI.

However, the two simplest are either directly linking to the ODATA page, or by using the search function.

To search for an asset, you'll need to know something unique about it - ideally something that isn't going to change over time. While the assets name is OK, you're better to use something even more permanent.

If your organisation uses Equipment Reference Numbers (sometimes called 'Tag Numbers') or Function Equipment Location Codes, this is the ideal thing to search for as it will remain consistent even if users rename the asset.

In Excel, PowerBI or any Power Query-application, you can go to Get Data and choose OData feed

The application will then prompt you for the address of this feed. You can enter yourservername/odata/webservice/

This will then open the list of endpoints for your OData service.

Each endpoint reflects a different kind of data you can browse through, or different function you can call to interact with ARDI.

To search for an asset, choose the AssetSearch option.

Enter your search term in the 'Query' section on the top-right, and then hit 'Apply'.

You should get at least one (and ideally only one) search result.

Press Transform Data to continue, then see the last section on Formatting Your Data for more information.

Every asset in ARDI has it's own internal ARDI ID, which is usually a number.

The ID number for an asset is part of the URL used on its dashboard - for example, the temperature sensor on stove #1 in our Blast Furnace demo is located at http://demo.optrix.com.au/s/bf/dashboard/58 - the ID number for this asset is '58'.

You can directly access the information for an individual asset by simply going to the /Assets(idnumber) endpoint in the OData server.

So for the above asset, you can get its OData information from http://demo.optrix.com.au/s/bf/odata/webservice/Assets(58)?format=json.

The 'format' parameter we added ensures that the data is in JSON format, which PowerQuery can read. When you create a JSON data source, you'll be prompted to choose a file. Don't worry - you're actually be able to paste in a web URL without any issues.

No matter which way you've loaded your data, you'll usually end up with this…

This is a list of all of the properties on the asset you've selected. Now follow the steps below…

1) Convert this list to a table with the Into Table button shown at the top left of the image above.

2) Press Transpose in the Transform tab to change your rows into columns.

3) Press Use First Rows as Headers in the Home tab to name your columns properly

4) Press Detect Data Type on the Transform tab (PowerBI won't take guesses at what data types are in each column - you need to specifically mark columns as decimal numbers if you want them treated that way).

5) Apply any other changes, filters or replacements that you'd like, then pick Close and Apply from the Home tab.

Your data will now be available - you'll be able to choose from any of the properties of your asset in PowerBI, or you'll be able to import data directly into Excel.

If you'd like to create complex queries, or request history on your data points, you can also use AQL queries through the OData addin. You should be able to go through very similar steps to format the AQL data.