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, then using Power BI, build Reports and display these 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.  

Example

• App ID = Risks
• Report Id = 186100
• Filter Criteria = Where [Status] is Red

e.g. https://api-gateway.softools.net/Api/Apps/Risks/Data/Report/186100?filter=[Status] eq 'Red'

Note: The filter is structured just like a normal filter string except for the $ which is replaced with a ? to indicate a query to the browser. Although this is in a decoded URL format any browser that supports Softools should have the capability of encoding this correctly. 

Please see here for how to construct filter strings.

 

HTTP Request Header Parameters

There are two Headers that are needed to authenticate the call for data. The credentials here are the API Key for the User, that the call will be made by and the Tenant that the data is being returned from. The User Account MUST 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.

Please see here for info on Record Security

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 and in order to do this they must have the API Permission.

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 the other as a Dashboard Report - wither way you first have to make a Dashboard.

 

3.    Publishing Power BI Reports to Softools

• Publish as a Dashboard:

Add a new or edit an existing Dashboard, regardless you will need to select one of the tiles to house your Power BI display.

Tip: Think about the size of your Power BI display, if large then dedicating a whole dashboard 1x1 (1 Col, 1 Row) will likely give the best results.

From here ensure the iFrame options is selected and copy the source in that you got from Power BI. If you wish for fixed applications, you can specify dimensions, otherwise Softools will automatically be reactive to the screen size

Finally pick your comparative alignment compared to the screen ensuring the background is Transparent and whether you want to show the title or not. Don't toggle Hide Cell? as this will result in it not displaying. Once the Dashboard is configured you will need to go into the App you want to display it in and set it up as a Report.

• Publish in a Homepage:

You will fist need to create the Dashboard like above but once this is done you can add it to a Homepage, where it can then be used at the main point of entry into your site.

Tip: To be able to use a Dashboard within a Homepage you must have toggled 'Is Global?' on in the dashboard configuration.

 

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 Web App open the data model:

1. Click on Transform Data and then Data Source Settings.

2. Select the appropriate data source and then click 'Change Source...'

In the advanced editor window that pops up, update the call from:

URL Parts = 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.