Aptem is Microsoft Azure hosted and each MWS client has its own separate database. Microsoft Power BI is a powerful visual reporting tool and is a part of the Office 365 suite. This article describes how you can use Power BI to create your own Aptem dashboards.
Connecting your data to Power BI Desktop
Open Power BI Desktop and select Get Data.
Power BI will then request that you select the type of data you need. Select Other and then OData Feed. Click Connect.
Enter the following URL but replace the section highlighted with the URL from your own Aptem account.
This can be found in the address bar as shown when connected to your account.
You will then have created an initial connection between Power BI and your Aptem data. At this stage we have still not approved the transit of data. You must complete some more steps to authorise the process.
Select the tables from your data that you wish to connect to Power BI.
In this example we have selected all the tables available, but you may select as few or as many as you need.
A warning message will appear in the right hand pane; ignore this as we still haven’t completed all the steps required.
Click on Load once you have chosen your tables.
An additional error message will appear. Again, you can ignore this and click Close.
From the Power BI ribbon, select Edit Queries.
The Queries window will now open. Click on any table from the left-hand column. If you are importing multiple tables, select the first one.
Click on Advanced Editor in the Power BI ribbon. Alternatively, you can right-click on a table and select the same option from the context menu.
The advanced editor window will now appear.
At this stage you have still not authorised the connection between Power BI and your live Aptem data.
To do this we need to insert an API key.
We need to replace the section of the text current called Null with the API key that can ONLY be generated by a member of the MWS / Aptem Admin team (such as your implementation consultant or a member of the support team). An API token will be generated for a named user in your organisation. This user must have administrator-level privileges assigned to them.
A long string API will be sent to the named user when requested. This will be in a plain text format and will need to be copied precisely to ensure all parentheses and commas are included. You can copy the key to a text editor like Notepad, You must add [#"X-API-Token before the key, and add "] after the API key.
Whilst still within the Advanced Editor screen from the steps above carefully replace the previous Null value with the long API key that has been issued to you.
You will know the connection has been successful when you can see the contents of that table displayed in the query editor screen and the yellow warning symbol has disappeared.
You now need to repeat this process for any other tables you have imported, but you can shorten the steps required as follows:
Click on the recently activated table and then re-open the Advanced Editor window from the Power BI ribbon.
You will now see the enhanced key information that you entered earlier.
Highlight the entire line starting with the word Source, ensuring you pick-up all the values and symbols contained within that entire line. Everything from, and including, the word Source up to, and including the words [Implementation="2.0"]), should be selected. Copy the selected text.
Now select the next table in the list on the right hand side, the first one that still has a yellow warning triangle and then click on Advanced Editor again.
Highlight the line starting Source and paste in its place, the line previously copied in the step above.
Click Done once finished and the table contents should now be visible as before.
Repeat these steps for each table that has the yellow warning symbol.
Once you have repeated the steps above for each table in Power BI then you should see all yellow warning symbols disappear and all table contents will be visible and will be able to be used within Power BI desktop.
Adding new tables and fields
As part of ongoing development, new tables and data fields may be added to the system. To add more tables or fields, navigate to the Home ribbon, select Get data > OData feed.
You must then repeat the steps shown in this article.
The data you have connected in the steps above will be your organisation’s live data. Data in Power BI is read-only and will not be written back to your Aptem system. However, for security reasons, caution should be exercised as to who has access to the tables in Power BI. If any data appears to be missing/incomplete it will be because this data has not been sufficiently entered into your Aptem system.