Linking to Power BI

Before looking into how to integrate Power BI and Smartabase we suggest you read the following links...

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#real-time-dashboards

https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-troubleshooting-refresh-scenarios/

Also, if you have switched from the desktop version to the web version, you will need to update your login credentials as they will not transfer over...

To import Form data into Microsoft Power BI in CSV format follow the step below:

NOTE: This is ONLY to be used on small data sets. If you load large amounts of data you can risk overloading the server, just as with live reports. Please use responsibly.

Inside Power BI select Get Data (from the Home tab) and Web

Inside Power BI select Get Data (from the Home tab) and Web

Clicking on Web will open up a pop-up window

Clicking on Web will bring up a pop-up window

Clicking on Web will bring up a pop-up window

You will need to create a request URL that looks similar to the following:

     https://{1}/{2}/externalcsvreports?app={2}formName={3}&filter1={4}|{5}&filter2={4}|{5}&startTime={6}&finishTime={7}

            NOTE: The numbers inside {these} brackets will need to be filled as per the instructions in the section titled: Instructions for creating your request URL, also see the section at the bottom of this page: Request URL Examples.

 A pop up window that looks like the image will appear. Ensure you select the Basic tab (as shown in the image above) and enter your Smartabase login credentials.

  A pop up window that looks like the image will appear. Ensure you select the Basic tab (as shown in the image above) and enter your Smartabase login credentials.

. Click Connect then you should see another pop up that looks similar to the image directly below.

. Click Connect then you should see another pop up that looks similar to the image directly below.

CRITICAL: You MUST know how to accurately create a correct URL Request

https://{1}/{2}/externalcsvreports?app={2}formName={3}&filter1={4}|{5}&filter2={4}|{5}&startTime={6}&finishTime={7}

Make sure to use https:// (as opposed to http://)

{1} is the url you enter when visiting your Smartabase website

{2} is the name of your application

{3} the name of the form you want to import. NOTE that white space is not supported in a URL, you will have to replace any spaces in form and filter names with the + symbol. For example, the form named Training Session would become Training+Session. Also note that the URLs you are creating ARE case-sensitive and will break if you use capitalisation that is different from that which is used in your applications form and filter names, also note that formName must include the capital N for name.

{4} is the name of the filter you wish to apply to your form import, the name of the filter must correspond exactly to a member of the form being requested.

{5} the value you insert here will cause the CSV returned to only return values which are EQUAL TO this parameter value, as above make sure to be aware of case sensitivity.

You must include this character: | (the vertical bar) between the filter name and the filter parameter.

You can use multiple filters, but must append an incremented filter name, for example, with one filter filter=... with two filters filter1=..., filter2=...

{6} allows you to create the beginning of a time range, for example, to start on the 1st of January 2016 the value would be 01012016.

{7} as above.

Once you have created your URL as described above click the OK button.

You will be only prompted to enter your user name and password (note that this is your Smartabase username and password, not your Power BI details) on your first connection to the servlet, you should be aware of the possible security issues this may cause if using shared computers.

Power BI will then return either a CSV file which conforms to the filters provided or an error message which will help you to correct any mistakes in your request URL.

Request URL examples

 

Example URL 1: Blood Test

Let's say your web url is https://test.smartabase.com/exampleapp

that the form you want to request is named: Blood Test Results

and you want to apply two filters named: Osmolarity and Passes Test

where the Osmolarity results returned are equal to the value: 4.0 and the Passes Test results returned are equal to No

and where the data you want returned is from between the dates 01 Feb 2015 and 09 Jun 2016

your Power BI request URL will be as follows:

https://test.smartabase.com/exampleapp/externalcsvreports?app=exampleapp&formName=Blood+Test&filter1=Osmolarity|4.0&filter2=Passes+Test|No&startTime=01022015&finishTime=09062016

Example URL 2: Fitness Training Session

Let's say your web url is https://xyz.smartabase.com/xyzathletes

that the form you want to request is named: Fitness Training Session Data

and you want to apply one filters named: Athlete Name

where the Athlete Name results returned are equal to the value: John Smith

and where the data you want returned is from between the dates 24 Feb 2015 and 28 Jul 2016

your Power BI request URL will be as follows:

https://xyz.smartabase.com/xyzathletes/externalcsvreports?app=xyzathletes&formName=Fitness+Training+Session+Data&filter=Athlete+Name|John+Smith&startTime=24022015&finishTime=28072016

 

Note, one user uncovered the following. Please feedback on the success you have using the filter numbers.

This did not work: https://xyz.smartabase.com/ademo/externalcsvreports?app={ademo}&formName=Daily+Monitor&filter1=Muscle+Soreness|3&filter2=Fatigue+Levels|3.55}&startTime=01011900&finishTime=30022017. Yet, this worked OK: https://test.smartabase.com/ademo/externalcsvreports?app={ademo}&formName=Daily+Monitor&filter=Muscle+Soreness|3&filter=Fatigue+Levels|3.55}&startTime=01011900&finishTime=30022017

0 Comments

Add your comment

E-Mail me when someone replies to this comment