How to connect Power BI to Atria API

How to connect Power BI to Atria API


In this article, we are going to show you how to connect to Atria API via Power BI.  Power BI will be able to retrieve data to create visuals and reports based on your requirements. The following article will illustrate a step by step guide to getting data from Atria API. It is important to note that Atria billing reports fetch the data based on end date periods and we will show how to bring all periods in one report.
This document assumes that you have read the following document as it is required to have your own Token key to access Atria API: 
To view Atria External API using Swagger please have a look at the following link: 

Please note that the above link will change according to your API address.

Power BI Configuration

In order to access data via Atria API in Power BI Desktop please follow the steps below:
  1. Run Power BI.
  2. You can close the introduction page as we are going to explain the steps on the main page of the Power BI Desktop.
  3. On the Power BI Desktop, under the Home ribbon click on Get Data and select Web as follows:

  4. On the From Web page click on Advanced:

  5. Click on the first line of URL parts and enter the main Atria API address: 

  6. At this stage, you must have your Token received from “Generate API Access Token” in the Atria platform. The token should be entered as an Authorization header. Click on the HTTP request header parameters and enter Authorization in the header field:

  7. Click on the value field and enter the Bearer {token} as follows:

  8. From Web page should look like this to be able to retrieve data:

    1. Click on OK
  9. Once you click on OK, Power BI Desktop starts to connect to Atria External API. It is important to note that the data you receive via Atria API is in JSON format and in order to view JSON data in a table structure please follow the below steps:
  10. On the Power Query Editor page which will be opened automatically once you retrieve data from Atria API, under Convert Menu click on parameters Query on the left side of the page and then select RECORD next to the data field

  11. On the next page, you will see endDates and customers Lists. Click on the List next to endDates.
  12. Here we will have data shown in JSON format and to extract data and show it in a table structure, under Transform Menu click on Into Table icon on the top left side of the page:

  13. To Table page will be opened to define the way you want to create your table:

    1. Click on OK
  14. We need to extract columns from the list shown in Column 1, to extract columns click on  icon next to Column1 header:

    1. The column select page will be shown.
  15. On the Column Select page, select the columns you want to be shown. In this example, we just need to click on OK

  16. Once you click on OK, the parameters table will be shown and we can use it as a parameter to retrieve our required information.

  17. Now that we have the endDates specified, we can use it as a parameter to send to our External API and retrieve information for all periods of time. In order to create a parameter please follow the below steps.
  18. Under Home ribbon, click on Manage Parameters and select New Parameter:

  19. In Manage Parameters page, enter a Name for the parameter, select Text for Type and enter a Current Value as below:

    1. Click on OK
  20. Click on the New Source and select Web
  21. on the From Web page, click on Advanced and on the first line of the URL parts enter the following link:
  22. On the second line of the URL parts click on the  icon and from the list shown click on the parameter. endDate parameter that you created previously will be selected automatically.
  23. Enter the Authorization and Bearer token as a header parameter and click on OK:

    1. Here we have the data for one period and we are going to create a function that iterates through parameters table and invokes data from External API
  24. To create the function, select endDate (2) table, then right-click and select Create Function, enter a name for Function Name

    1. Click on OK
  25. Click on parameters table on the left-side and then under Add Column ribbon click on Invoke Custom Function

  26. Invoke Custom Function page will be shown. Enter a new column name and select your function (in this example GetSummary) from the drop-down box:

    1. endDate parameter will be shown to send it as a parameter to our function:

    2. Click OK to close the window.
  27. A warning message will be shown regarding data privacy. Click on Continue:

  28. in the Privacy Levels page, click on the "ignore privacy levels check for this file" and click on Save:

  29. Right now, our function uses the endDate table as a parameter and iterates through the values of the table and sends each value as a parameter to our External API. This may take a bit to send requests and receive the responses. You may just need to extract the columns and rows via the same process on steps 12 to 16.
  30. Once you finish the above steps, you will have all the information and you can start preparing data or creating visualizations or reports in your application. 

    • Related Articles

    • Atria API User Guide (CortexAPI)

      Overview The application programming interface (API) is a powerful interface that allows you to interact directly with Atria without using the ATRIA Web User Interface (UI).  The API grants a user, with some development knowledge, the ability to ...
    • Atria API

      Atria API Purpose The Atria API is a new Rest-based integration point that over time will replace the functionality of the old API, (known as the CortexAPI). If you have developed against The CortexAPI, don’t worry, it’s still working and supported. ...
    • Atria API Policy

      Purpose To provide direction and guidance to Atria customers and third parties integrating with Atria API’s. Atria API's API Name Type Purpose Cortex API XML based over HTTPS https://{hostname}/cortexapi Used by AD Sync, Customer, User, Service ...
    • How to connect to Atria External Billing API via Excel

      Atria External API via Excel This document illustrates how to use Atria's external API platform to retrieve billing invoice data via an Excel document. Applies to: Atria 12.11 or later Create Token Use the following KB as a step by step to receive ...
    • Billing with Atria

      Atria Billing Index Atria has a systematic approach to tracking services that it manages, this data can be exported or directly integrated via API into external PSA or billing systems. Background Information Quick video on billing and auditing How ...