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.

• Getting Data from Softools into PowerBI
• Adding Image Fields into a PowerBI Table Report
• Publishing from PowerBI to Web
• Publishing Power BI Reports to Softools
• Updating the Web Call for Schedule Refresh of the Data

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.- For a video on how to do this, please click here.

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 View Report and then using the Identifier of the View Report from App Studio.

This would be in the form:

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

e.g. https://api-gateway.softools.net/Api/Apps/Risk/View/ListofRisks

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 = Risk
• View Report Identifier = ListofRisks
• Filter Criteria = Where [Status] is Red

e.g. https://api-gateway.softools.net/Api/Apps/Risk/View/ListofRisks?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.

 

Adding Image Fields into a PowerBI Table Report

To give your Power BI report a more aesthetically pleasing feel, you can also add images to your PowerBI Matrix elements. In the example below we have list of Key Stakeholders. To boost engagement it is nice to be able to put a name to a face, building team connections. In our Softools App, we have a Field to upload a picture of the Stakeholder.

 

We will need to add this Image Field along with the other data that we need in our PowerBI model via a View. This will enable Softools to generate all of the correct backing Fields when the data is parsed to PowerBI via the API. 

This would be in the form:

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

e.g. https://api-gateway.softools.net/Api/Apps/Stakeholder/View/StakholderView

 

When the data is pulled into your PowerBI Data Table, Image Fields will appear with two backing Fields meaning that you have three columns of data in PowerBI for each Image Field.

 

• Field (Stakeholder) This is the Softools database ID for your image
• Field_Link (Stakeholder_Link) This is a publicly accessible link that will display the image in a PowerBI report.
• Field_LinkExpiry (Stakeholder_LinkExpiry) When the image link is generated, it will be available to use for a period of three months. To ensure that the images do not disappear from your PowerBI Reports after this time enable Scheduled Refresh of Data.

To ensure that the Image is displayed in your PowerBI Report and not just the URL, you need to explicitly state the data type for that cell in your Data Table to be Image URL. By doing this PowerBI then knows that you are expecting it to display an image and not text.

 

Build your graphic and use the [Field_Link] property with data category as Image URL and you will now have images from Softools in your PowerBI model. Here we can see the thumbnail images of our Stakeholders alongside their key credentials.

 

Other common scenarios where this feature will add value to your process are

• Documentation & Reporting: Add screenshots or visual evidence in QA, Audit and Issue Log reporting.
• Asset / Product Registers: It helps to have an image of the items that are owned or part of service offerings.

 

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.

 

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.

 

Updating the Web Call 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-gateway.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.