Available only on the web.
Power BI is a suite of Microsoft business analytics tools which are very effective for visualizing and sharing data. Similarly to connecting Microsoft Excel to Teamworks AMS using a live report, AMS can also be connected to Microsoft’s Power BI desktop application. Power BI does not require the creation of a saved report first, however.
Once the dataset is created in Power BI using a specially formatted request URL, Power BI then allows you to create reports and dashboards within Power BI which are based on live data stored in AMS. The resulting visualizations can then be shared amongst other Power BI users with appropriate permissions to view the data the report or dashboard is based upon.
Note that because the data is extracted from AMS in CSV format (the same format that is used when selecting the export to Excel option from the Reports tool), it is necessary to adjust the format of any date columns you wish to use in Power BI. For those who use the dd-mm-yyyy date notation style (Australia, New Zealand, UK, for example), the text format of the CSV can easily be converted to date format by right-clicking the date column, choosing Change Type and selecting the Using Locale option. For people needing to format dates as mm-dd-yyyy, there is a second step to this process: once the date text has been converted to date format, the Modelling menu can be used to select the correct date/time format.
Because datasets created and stored in AMS can be very extensive, attempting to retrieve a large dataset using Power BI can increase the load on the server that your AMS site is hosted by. If this happens, your Power BI data request can impact the performance of the site for other users. To avoid this, use suitable filters and date ranges in your request URL to ensure that you only retrieve the data that is necessary for your report.
This example report was created in Power BI using data from a wellness form. We can use the athlete selector to compare an athlete’s wellness variables over the past week to the same data from 30 days prior. We can also view each athlete’s average daily heart rate and training stress balance for the report period.
Steps to create a dataset in Power BI
- Open a new report within Power BI.
- From the Home tab, select the Get Data button and choose the From Web option.
- In the pop-up box for the URL, create a request URL using the format below:
https://SERVERNAME.smartabase.com/SITENAME/externalcsvreports?app=SITENAME&formName=FORMNAME&filter=FIELDNAME|VARIABLENAME&filter=FIELDNAME|VARIABLENAME&startTime=STARTDATE&finishTime=FINISHDATE
Where:
- SERVERNAME is the name of the server your AMS site is located on.
- SITENAME is the name of your site.
- FORMNAME is the exact title of the event form you want to fetch a dataset from. This must be capitalized in the same way as the event form name. If there are spaces in the event form name, you must replace them with the + symbol, so if the form name was Training Load you would type formName=Training+Load.
- FIELDNAME is the exact name of a field or calculation within the event form, while VARIABLENAME is the exact text or number of a possible result for that field. These can be used in combination with the filter parameter to restrict the dataset to specific information. For example, if we wanted to fetch a dataset from the Training Load form but only for cases where the type of training (field name = Session type) was skills training (variable name = Skill), we could type filter=Session+type|Skill.
- STARTDATE and FINISHDATE are the beginning and end dates of the records you want to include in your dataset formatted as ddmmyyy, so 14 May 2017 would become14052017 and 2 December 2016 would become 02122016.
formName, startTime and finishTime need to be capitalized as they are here. Using filters and start and end dates are optional; if these are not required your URL request would end with the form name. There should be no spaces in your URL request, field names and variable names should be separated by a vertical bar ( | ) and parameters should be added using an ampersand ( & ).
- On completion of the URL request string, select the OK button.
- When prompted by Power BI, enter your AMS credentials (this is only necessary on your first log in).
- Power BI should then populate your dataset according to the parameters specified in your URL request and you can format your report in Power BI based on this dataset.
Automatically refresh the data in your Power BI report
When you publish a Power BI report to Power BI Service, the dataset that is published, along with the report, is static. This means that if new records are entered in AMS, these records won’t automatically appear in your Power BI report by default.
If you want your Power BI report to display the most up-to-date data from your AMS application, you need to configure Scheduled Refresh for that specific report in Power BI Service. When Scheduled Refresh is enabled, Power BI Service runs the data queries included in your report, at preset times, to update the underlying dataset that feeds your report.
When you are using the Web source option to pull data from AMS into Power BI, Power BI prompts you to enter your AMS credentials and, when provided, these are stored in your local installation of Power BI (Data Source Settings).
When you publish a Power BI report, the credentials, to run the data queries included in your report, are encrypted and stored in Power BI Service. If Scheduled Refresh enabled for a specific report, Power BI Service would try to run the data queries included in your report to update the underlying dataset but, since the credentials are encrypted, it is unable to run the data queries. To solve this problem, you need to install an On-premises Data Gateway.
An On-premises Data Gateway acts as a bridge to provide quick and secure data transfer between on-premises data (secured data sources) and several Microsoft cloud services. When enabled, the On-premises Data Gateway handles data query requests and sends the resulting datasets to Power BI Service where these get stored. Click here to read more information about On-premises Data Gateway architecture.