Power BI is a suite of Microsoft business analytics tools which are very effective for visualising and sharing data. Similarly to connecting Microsoft Excel to Smartabase using a live report, Smartabase 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 Smartabase. The resulting visualisations 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 Smartabase 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 Smartabase can be very extensive, attempting to retrieve a large dataset using Power BI can increase the load on the server that your Smartabase 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.

EXAMPLE: POWER BI WELLNESS 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.

HOW TO: CREATE A DATASET IN POWER BI
  1. Open a new report within Power BI
  2. From the Home tab select the Get Data button and choose the From Web option
  3. 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 Smartabase 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 capitalised 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 question 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.

TIPformName, startTime and finishTime need to be capitalised 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 ( & ).

  1. On completion of the URL request string, select the OK button
  2. When prompted by Power BI, enter your Smartabase credentials (this is only necessary on your first log in)
  3. 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