Connecting your organisation's live data to Power BI via an API
Overview
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.
Security and GDPR warning
This method allows you to create a file which provides direct read-only access to the live data in your Aptem system. The data in your Aptem system cannot be altered.
Anyone with access to the Power BI file will have access to personal data regardless of which computer they use. The Power BI file you create must only be shared with those who are authorised to use the data in your Aptem system. Not following secure procedures could result in a breach of GDPR regulations.
How to connect to your data
Create a new Power BI file. If you see an orange/black start-up dialog, close it by clicking the 'x' icon on your top-right.
You will then see the Power BI Desktop ‘Home’ ribbon. Click Transform Data.
The Power Query editor will open. It will say Power Query Editor in the title bar.
Click New Source > Blank Query.
A new query - Query1 will be seen in the ‘Queries’ pane, on your left.
Click the Advanced Editor icon in the query section of the ribbon – the Query editor box will be displayed.
Select the text in the box, and replace it with one of the sample queries at the end of this document.
You will need to edit the sample query and (1) replace the text ‘YOURTENANT’ with web address of your Aptem account (2) replace the text YOURAPIKEY with the API key provided to you.
The API key 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.
Next, click Done on the Query editor.
Click Edit Credentials.
Click Connect on the pop up screen. If this fails then the key and/or tenant is incorrect.
On the query properties pane on your right, rename the query to some more useful.
By default only the first ten columns are loaded. You need to manually select the columns you need. This step is particularly necessary for Users and ILRAims tables where more than ten columns are needed.
To manually select the columns you need, click ‘Choose Columns’ on the top panel.
The Choose Columns popup screen is displayed. Select/unselect the fields as per your requirement. Click OK.
The first query is completed. In the query editor, click Refresh Preview to confirm data loads.
How to add additional data tables
For each table you wish to add, right-click on the existing query in the Queries section.
Then click Duplicate option to copy it. A new query will be shown in the list.
Under the Properties pane ‘on the right, double click Navigation.
This will display a list of tables available to your OData feed.
A table will appear – simply click on the link text ‘table’ next to the required data table.
In earlier versions of Power BI, the screen looks like the one below – click the required table, then click OK.
You will then need to select relevant columns. Click Choose Columns to select the columns.
Note that if you see a “The column ## of the table wasn’t found” error, then in the Query Settings- ‘Properties’ pane, click on the cog icon to next to the ‘Removed Other columns’ line to select the relevant columns.
The Choose Columns screen will be displayed. Click OK after selecting the required column and the error will be resolved.
Repeat the steps for any additional tables.
Loading Queries into Power BI
Click Close & Apply and the data will load-ready for visualisation and analysis.
Remember to save the Power BI file before creating report pages.
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. If any data appears to be missing/incomplete, it means that this data has not been sufficiently entered into your Aptem system.
Connecting data tables within Power BI
As with other database software (e.g SQL Server) you will need to connect the imported data tables together.
In the main Power BI interface, click the ‘Model’ icon. This will show the data tables and allow you to manage the relationships between the tables.
Warning: Incorrectly connecting the tables will result in incorrect data visualisation and analysis results.
We do not recommend the use of Power BI’s auto-detect relationships feature. It will result in incorrect links across your data. Each table has an Id field- the ‘auto-detect feature’ will link all the ‘Id’ fields together.
The process for manually linking data tables can be found here.
The primary key is [Id] in the Users table. Users[Id] should only be manually connected to [LearnerId] on other tables e.g IlrAims[LearnerId] and ComplianceDocuments[LearnerId].
Sample queries
Users Table
|
let myAPI = "YOUR_API_KEY", myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
Users_table = Source{[Name="Users",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Users_table,{"Id", "RefNumber", "FirstName", "LastName", "FullName", "SubscriptionStatus", "Type", "UserGroups_GroupLevel0", "UserGroups_GroupLevel1", "UserGroups_GroupLevel2" , "UserILRSummary_ProgrammeStartDate", "UserILRSummary_PlannedEndDate", "UserILRSummary_ActualEndDate", "UserILRSummary_RestartDate" }),
// temporarily rename the date columns
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"UserILRSummary_ProgrammeStartDate", "ILRProgrammeStartDate"}, {"UserILRSummary_PlannedEndDate", "ILRPlannedEndDate"}, {"UserILRSummary_ActualEndDate", "ILRActualEndDate"}, {"UserILRSummary_RestartDate", "ILRRestartDate"}}),
// change date column types to text format
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"ILRProgrammeStartDate", type text}, {"ILRPlannedEndDate", type text}, {"ILRActualEndDate", type text}, {"ILRRestartDate", type text}}),
// convert temporary date columns from text date back to date - fixes daylight saving time glitch
#"Date1" = Table.AddColumn(#"Changed Type", "UserILRSummary_ProgrammeStartDate", each
Date.From(Text.Middle([ILRProgrammeStartDate], 6,4) & Text.Middle([ILRProgrammeStartDate], 3, 2) & Text.Start([ILRProgrammeStartDate], 2)), type date),
#"Date2" = Table.AddColumn(#"Date1", "UserILRSummary_PlannedEndDate", each
Date.From(Text.Middle([ILRPlannedEndDate], 6,4) & Text.Middle([ILRPlannedEndDate], 3, 2) & Text.Start([ILRPlannedEndDate], 2)), type date),
#"Date3" = Table.AddColumn(#"Date2", "UserILRSummary_ActualEndDate", each
Date.From(Text.Middle([ILRActualEndDate], 6,4) & Text.Middle([ILRActualEndDate], 3, 2) & Text.Start([ILRActualEndDate], 2)), type date),
#"Date4" = Table.AddColumn(#"Date3", "UserILRSummary_RestartDate", each
Date.From(Text.Middle([ILRRestartDate], 6,4) & Text.Middle([ILRRestartDate], 3, 2) & Text.Start([ILRRestartDate], 2)), type date),
// Now remove tmp columns
#"Removed Columns" = Table.RemoveColumns(Date4,{"ILRProgrammeStartDate", "ILRPlannedEndDate", "ILRActualEndDate", "ILRRestartDate"})
in
#"Removed Columns" |
Compliance Table
|
let myAPI = "YOUR_API_KEY", myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
ComplianceDocuments_table = Source{[Name="ComplianceDocuments",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(ComplianceDocuments_table,{"Id", "Name", "LearnerId", "ProgramName", "Status", "Date", "LearnerSignedDate", "FullySignedDate"})
in
#"Removed Other Columns" |
ILR Aims table
|
let myAPI = "YOUR_API_KEY", myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
IlrAims_table = Source{[Name="IlrAims",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(IlrAims_table,{"Id", "ReferenceNumber", "Name", "Level", "LearnerId", "ProgramId", "ProgramName", "AimType", "StartDate", "OriginalStartDate", "PlannedEndDate", "ActualEndDate", "ProgType", "FundModel", "FworkCode", "PwayCode", "StandardCode", "PlannedHours", "EpaCode", "Status", "Outcome", "AchievementDate", "Sof", "Res", "Act", "TotalTrainingPrice", "TotalAssessmentPrice"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"StartDate", "tmpStartDate"}, {"OriginalStartDate", "tmpOriginalStartDate"}, {"PlannedEndDate", "tmpPlannedEndDate"}, {"ActualEndDate", "tmpActualEndDate"} , {"AchievementDate", "tmpAchievementDate"}}),
// convert text date back to date - fixes daylight saving time glitch
#"Date1" = Table.AddColumn(#"Renamed Columns", "StartDate", each Date.From(Text.Middle([tmpStartDate], 6,4) & Text.Middle([tmpStartDate], 3, 2) & Text.Start([tmpStartDate], 2)), type date),
#"Date2" = Table.AddColumn(#"Date1", "OriginalStartDate", each Date.From(Text.Middle([tmpOriginalStartDate], 6,4) & Text.Middle([tmpOriginalStartDate], 3, 2) & Text.Start([tmpOriginalStartDate], 2)), type date),
#"Date3" = Table.AddColumn(#"Date2", "PlannedEndDate", each Date.From(Text.Middle([tmpPlannedEndDate], 6,4) & Text.Middle([tmpPlannedEndDate], 3, 2) & Text.Start([tmpPlannedEndDate], 2)), type date),
#"Date4" = Table.AddColumn(#"Date3", "ActualEndDate", each Date.From(Text.Middle([tmpActualEndDate], 6,4) & Text.Middle([tmpActualEndDate], 3, 2) & Text.Start([tmpActualEndDate], 2)), type date),
#"Date5" = Table.AddColumn(#"Date4", "AchievementDate", each Date.From(Text.Middle([tmpAchievementDate], 6,4) & Text.Middle([tmpAchievementDate], 3, 2) & Text.Start([tmpAchievementDate], 2)), type date),
// Now remove temporary columns #"Removed Columns" = Table.RemoveColumns(Date5,{ "tmpStartDate", "tmpOriginalStartDate", "tmpPlannedEndDate", "tmpActualEndDate","tmpAchievementDate" })
in
#"Removed Columns" |
FAQs
Some columns in my imported data are empty. Why?
By default only the first ten columns of an OData feed are imported. Although the column headers are visible, no data will be imported. You need to use the ‘Select Columns’ icon to edit the query for each table and select (tick) the columns which you wish to use. Click Refresh Preview’ to confirm that the data has been imported. Next, click Close and Apply.
Note that some fields or columns will be empty depending on the information your organisation collects.
Some dates from the ILR aims table are shown as a day earlier when imported into Excel/PowerBI during the summer months. Why?
When daylight saving is active on your local computer the server providing the OData feed can be in a different time-zone to the current time zone on your local computer. The result is that date-time value set at midnight can be reinterpreted as minus one hour resulting in the previous day being recorded in your Excel/Power BI file. This behaviour occurs automatically in Excel and Power BI – and we have no control over it. The sample queries (Users table, ILR aims table) above overcome this issue by converting the date-time fields to text format and then returning just the correct date.
Why am I unable to read data from OData feeds?
This may be caused if the request URL length exceeds 8060 characters. Currently, the maximum supported URL length is 8060 characters.
You can modify your request to accommodate this limitation and try again.