OData format in WEBCON BPS
Applies to version: 2022.1.4.47 and above; author: Grzegorz Straś
Introduction
Data from WEBCON BPS can now be loaded in OData format and used in Power BI Desktop and Online.
This greatly simplifies WEBCON integration with Power BI, instead of manually constructing REST API queries and dealing with token-based authentication, we are able to use one of two custom connectors to easily load data from WEBCON BPS into Power BI.
Handling this data is also less cumbersome, we are able to apply filtering to the data and select what we need at Power BI level.
Since this is a new feature, there are certain limitations. The OData format that we use has been tailored specifically to work with Power BI Desktop and Online. As time goes on, more systems that use OData format will be able to use WEBCON BPS data, but the goal for version 2022.1.4 was to make it available for Power BI queries.
There is also the issue of the nature of WEBCON BPS data – it is dynamic. Not only can the data on forms be constantly edited by users, but also the definition of WEBCON BPS processes can change. Specifically, after changing the process or form field names, it will be necessary to reload the data in Power BI. Otherwise, the Power BI reports will attempt to reference processes and form fields that no longer exist under the given name.
How to get WEBCON data into Power BI?
The two connectors that facilitate the integration can be found on our GitHub.
Each connector represents a context for obtaining data: User or Application
In this article, we will go over the latter method in detail. The list of steps to achieve this is as follows:
- Download the connectors and place them into the “Custom connectors” repository of the Power BI installation folder.
- Set up an API application in the Admin panel that uses the App context type and has App.Odata.Read.All permissions (example below).
- In all WEBCON BPS processes that you wish to load data from, add Metadata Access at Application level and Access all workflow instances (excluding attachments) at Process level for the user defined in the API application above (the Application’s login will be available as a user in the privilege settings).
- Launch Power BI and use the “Get Data” option to select the application context connector.
- Use the wizard to specify which process and which data you wish to load into Power BI. You will need to provide the Portal address, content database ID, as well as Client ID, and Client secret from the API application above.
- Enjoy!
Application context walkthrough
We will now go through a step-by-step guide on how to set up the aforementioned connection.
We can start with the obvious, download the connectors and place them into the “Custom connectors” repository of the Power BI installation folder. This is usually C:\Users\<user>\Documents\Power BI Desktop\Custom Connectors.
The connectors can be found on our GitHub.
Next, we will set up the API application in the Admin panel.
The important things here are:
- Application type: App context
- Name and Login: Creates a user in the context of which the Application will obtain data (we will assign privileges later).
- Client ID: Keep this! You will need it later.
- Client Secret: Generate and keep this! You will need it later.
- Application permissions (scopes): App.Odata.Read.All
To finish setting up the API Application, place the Login from its configuration into the privilege settings of every application and process you wish to access.
- Application privileges: Metadata Access
- Process Privileges: Access all workflow instances (excluding attachments)
With the connectors and API Application set up, we can go to Power BI Desktop.
NOTE: Depending on the version of the connectors and when you are downloading them, it might be necessary to allow any extensions to be loaded.
This can be found under File -> Options and Settings -> Options -> Security
Restart Power BI Desktop
Finally, we can load our data in using the Get Data function. Search for WEBCON connectors and choose the one corresponding to Application context.
Here you will need to provide your Portal address, Client ID and Client secret, as well as the Database ID which is the ID of your content database.
This last ID can be found in System Settings -> Global Parameters -> Databases.
Click OK and then Connect
Congratulations! You can now choose from which process (or processes) you wish to load data. This data will be available in the Fields section on the right.
You can notice that each process and form field has its ID appended to its name, please keep in mind that you may need to reload the data after you change one of these names in the process configuration.
+48 12 443 13 90
Thanks for this nice article. Finally OData has arrived in Webcon :-)
Is there any documentation out there of how to use this with REST API and filtering a view content?
Thanks in advance & best regards, Nik