ATMS API and Power BI

In this guide we will create a Power BI visualisation (often shortened to ‘visual’) that uses ATMS API to display all the ATMS Actions for an ATMS project, in a pie chart with each slice representing a different action status.

For this we need several things:

  1. Power BI desktop.
  2. ATMS API authentication token.
  3. ATMS Project Id.
  4. ATMS API Actions URL.

 

Install Power Bi Desktop

Download and install Power BI Desktop from the following URL:

https://powerbi.microsoft.com/en-us/desktop/

(Power BI is not free, although there is a free trial.)

Power BI can combine data from multiple sources (APIs, databases, spreadsheets…) into dashboards and reports.

 

Getting an ATMS API Authentication Token.

The ATMS API may be used as a Power BI data source, allowing the manipulation and presentation of ATMS project and action data.

Access to ATMS API data is only available to authenticated users and services.

ATMS API Authentication.

Power BI can retrieve ATMS data by authenticating with ATMSAPI using a valid JSON Web Token (JWT).

Note: A valid ATMS account with API access is required for Power BI access to ATMS data.

Obtaining a Token.

Navigate to ATMS API web page where you can obtain a token: https://atmsapi.pisys.co.uk/help/index.html

  • Scroll to the Auth section.
  • Click the green row named [POST] /api/v1/Auth/AccessToken to expand the section.

  • Click on the Try it Out button on the right hand side.

  • Update the JSON object - replacing the string and 0 values, and update the expiry date:

UserName and Password are the user’s ATMS username and password.

CustomerId is the user’s customer id.

OptionalExpiryDateUTC allows the user to specify a longer lifetime for the normally short-lived access token.

Note: TokenName can be any text (e.g. “PowerBI Pi Chart Token”), but UserName, Password, and CustomerId are used for authentication. A generated token shall be rejected as invalid after its ExpiryDate.

  • Click the blue Execute button to send the JSON object to the ATMS API.
  • Scroll down a little to locate the Server Response section. Successful authentication produces output that looks something like this:

  • The 200 under Code on the left is the HTTP response code for success.
  • The token we need for Power BI is highlighted in white.
  • Copy the token text (excluding the double-quotes and the date) and paste into Notepad or another editor. In the example above copy the text eyJhbG.....Eo1A_hA.
  • Success! You have a token that can be used by Power BI for authenticated access to the ATMS API.

Note: A token should be protected more carefully than a password. A token grants access to whoever has it, for the lifetime of the token.

If you suspect a token is being misused, or has been leaked, the only way to stop it being used is for it to be blacklisted in ATMSAPI. Your ATMS System administrator can help with this.

If the number under Code is not 200 then check the following:

  1. Ensure that only the string, 0, and date values of the JSON object were modified.
  2. Double-check the UserName and Password.
    (UserName may not be your email address, the password is case-sensitive.)
  3. Examine the “message” in the Response body.

Getting ATMS Project ID for ATMS API.

The ATMS API contains a URL that returns all the actions for an ATMS Project by specifying the ATMS Project Id.

If you are a system or action administrator you can get the project Id for an ATMS Project from the Project details page under ATMS/Administration/Projects.

If you are not an action or system administrator you can ask one to provide you with a Project Id for your project.

The Project Id for the HAZOP 001 project above is 57.

Make a note of the ATMS Project Id for the ATMS Project you want to use.

Note: If Project Id is not visible, then check that ATMS API access is enabled for your user.

Your ATMS System Administrator can check this.

Getting Actions Data into Power BI.

We will use ATMS API to get Action data into Power BI.

ATMS API provides ATMS data based on a specific URL used.

For our example, we will use the following ATMS API URL: https://atmsapi.pisys.co.uk/help/index.html and a test project.

All you have to do is replace the test project with the ID of the Project you want to use.

How to get Actions Data into Power BI.

  • Open Power BI desktop, and select Get data from the Start screen.

If the Start screen is not visible, it can be displayed by selecting Get started from the File menu.

  • Select Other on the left, Web on the right, then click on the Connect button.

  • Select the Advanced option.

  • Complete the form using your Project Id in the following URL and your JSON web token:

  • Click the OK button. This can take a few minutes to complete, and you may see a connecting progress popup window. Once the connection is successful, you will see the Power Query Editor window.

 

Working with ATMS Action Data in Power BI.

  • Click the List link next to data.

  • We need to prepare the data before we can visualize it. We do that by converting into, or transforming to a table. Click the To Table button.

  • Click OK on the To Table dialog.

  • Check that Applied Steps section updates with Converted to Table.

  • Now click the small double-arrow button to the right of Column1.

  • Clear all column checkboxes except Status, and uncheck Use original column name as prefix, then click OK. 

  • Note that we can now see all the action status values in a table.

  • Click Close & Apply.

  • This will take you back to the Power BI Desktop window.

Note Status is now listed under Fields on the right-hand side. This is our project’s action data that we’re going to visualize.

Creating the Pie Chart in Power BI.

  • Click the pie chart icon (highlighted) in Visualizations.

  • You should see a grey pie chart appear in the blank area on the left-hand side. Once you Click on it, some inputs appear under Visualizations.

  • We need to wire-up our Action status data to the pie chart. We do this by dragging it from Fields, into Values.

  • Now we have a pie chart containing actual ATMS data, but it’s not fulfilling the promise of adding value to our data. At best we can tell that the chart represents 23 status values.

  • The chart is displaying exactly what it is supposed to – the Count of Status – but it would be more informative to see the split between the different action status values.
  • We do this by dragging Status from Fields to Legend (as we did before with Values).

Success! We’ve used ATMS API to import ATMS action data for a project and produced a Power BI visual displaying the action status.

We can see at a glance that over 80% of actions in the project are Open – something that would have been more difficult to discern from the action list screen of ATMS.
This project only has 23 actions but imagine a project with hundreds or thousands of actions.

This only scratches the surface of how you can use Power BI to reveal hidden details, and extract more value from your data.

POC is dead - long live POV & MVP