Power BI Connection via the API Web Call

Power BI is a Microsoft product that allows you to take tables of data from various sources in order to collate the information together in a Report with a series of tables, charts and cards.

 

These instructions will enable you to take data from a Softools Application, use Power BI to build Reports and then display the Reports back in Softools.

1.    Getting Data from Softools into Power BI

 The data from a Softools Application can be used in Power BI by making an API Web Call to receive the collection of Records from the App.

First open the Power BI desktop App and start a new working model. Click on the Get Data drop down and then select Web to get data from a web source. In the option that opens, select Advanced and then fill in the following properties to make the data call.

Properties to fill in:

• URL Parts
• HTTP Request Header Parameters
  • apikey
  • tenant

See below instruction for each property:

URL Parts - This is the call to get data. It is in the form:

https://api-gateway.softools.net/Api/Apps/{AppID}/Data

e.g. https://api-gateway.softools.net/Api/Apps/KPIScorecard/Data

Tip: If the App that the data is coming from contains a lot of Fields and Records the size of data returned can be reduced by building a List Report and then using the ID of the Report from App Studio.

This would be in the form:

https://api-gateway.softools.net/Api/Apps/{AppID}/Data/Report/{ReportID}

e.g. https://api-gateway.softools.net/Api/Apps/KPIScorecard/Data/Report/162617

The data can further be reduced if needed by adding a filter so that only Records meeting certain Field criteria are returned.  

e.g. To only return Records from the KPI Scorecard App where Field with ID ‘KPIType’ is value ‘People and Change’ you could use the following. The quickest way to get the syntax is by applying the filter in the UI and copying this from the URL. Please note that a $ symbol will need to be added between the ? and the word filter.

e.g. https://api-gateway.softools.net/Api/Apps/KPIScorecard/Data/Report/162617?$filter=%5BKPIType%5D%20eq%20%27People%20and%20Change%27

HTTP Request Header Parameters

There are two Headers that are needed to authenticate the call for data. The credentials here the API Key for the User that the call will be made from and the Tenant that the data is being returned from. The User Account needs to have visibility of the records that need sending to Power BI. This would normally mean belonging to a Team that has access to the App and Record Read Only or Full access permissions.

apikey: “abcde123- xxxx-…”

The API Key can be found in the User Profile. Click on the Cog menu and select My Profile. Then scroll down to API Key where you can set. Please Note if you refresh the API Key it will cause any calls authenticated against the previous API Key to fail until updated to the new key.

Note: the API Key can only be accessed via the owner of the User Account.

tenant: e.g. “azuuza”

The Tenant is usually the first part of the URL for your site. In this case the URL for the azuuza platform is https://azuuza.on.softools.net in the form https://{tenant}.on.softools.net. If an alias has been used for the URL then it will be necessary to contact your system administrator for your tenant. 

Having made this call you should result in a table of data in Power BI that you can then use to construct Reports. Multiple Web Get Data sources can be added to give data from multiple Apps as well as in conjunction with data sources from other Applications. You can then use table manipulation in Power BI including Convert to Table, Expand Columns, Rename Columns and Setting Data Types. For numeric data ensure the data set has been set to numeric in case cumulative values are used in the Power BI Reports.

2.    Publish to Web

There are two steps to getting the Power BI Report constructed in the Desktop App published back into Softools or to another Web location. First Publish the Power BI model to the Power BI Server and then get a weblink that can be used to access the Report.

1. Click on Publish to upload the Power BI Model to the Power BI Server.

 

2. Once this is complete go to https://powerbi.microsoft.com and Sign In.

 

3. From the Workspace select the Report that you have published to the Power BI Server

 

4. Under File, there is the option to Publish to Web. Select this and it will give you the iframe code that you can embed into a Web Page in order to share the Report with groups of Users.

 

If you would like to display the content of the Report in Softools then there are two options. One is as a homepage and one as a Dashboard Report for an App using a Template.

3.    Publishing Power BI Reports to Softools

• Publish as a Homepage:

Add a new Homepage and go into the Enhanced Builder. In here paste the iframe code and it will display the Power BI Dashboard as a homepage on Softools

 

• Publish a Dashboard Report for an App using a Template:

Add a new Template and go into the Enhanced Builder. In here paste the iframe code into the HTML and save the template.

 

Then add a new Report and select the Template that you have added to display as the template for the Report.

 

4.    Updating the Web Call to allow for Schedule Refresh of the Data

In Power BI there is the option to use Schedule Refresh for a Web Source in order to pull in the data from the source up to eight times a day. To take advantage of this feature we need to edit the API Call for data in order to authenticate properly against https://api-gateway.softools.net.

In the Power BI desktop App open the data model:

1. Click on Edit Queries.

 

2. Right-click on the data source and select Advanced Editor

 

Update the call from:

Source = Json.Document(Web.Contents("https://api-gateway.softools.net/Api/Apps/Dashboard/Data", Headers=[apikey="abcde123-xxxx-….", tenant="azuuza"]))

to:

Source = Json.Document(Web.Contents("https://api-gateway.softools.net", [Headers=[apikey="abcde123-xxxx-….", tenant="azuuza"], RelativePath="/Api/Apps/Dashboard/Data"])),

By adding the Relative Path it means the Schedule Refresh will be granted successfully authenticating against the root https://api.on.softools.net domain.

Note: this action is required for each data set that requires a Schedule refresh.

4. Publish your changes
5. Open the Power BI Server and go to My Workspace / Datasets / select your data Set, select Schedule refresh and switch this feature on to update the data as often as Power BI will allow.