Level of Difficulty: Intermediate – Senior.
UiPath Data Service has become a sophisticated way to encapsulate data and information used in automation solutions within the UiPath platform without needing to invest in additional infrastructure. The power that data enables which ties everything together is the ability to integrate and visualise the available data. For this, UiPath relies heavily on the UiPath Data Service activity and the UiPath Data Service API.
UiPath has a product referred to as UiPath Insights in the product suite which is aimed at building visualisations from data within the environment (currently specific to UiPath Automation Hub and UiPath Orchestrator). UiPath Insights is a good tool to use for visualisations for data that is not living in Data Service. Unfortunately there is not an integration between Data Service and Insights just yet.
Since visualisation is an important layer used to understand the underlying data and UiPath have not yet catered for the capability, other tools and options should be considered as a ‘stop gap’. The industry leading tool in this space is Microsoft’s Power BI. Power BI Desktop is available for download at no cost and is a very user friendly platform used to develop reports and dashboards.
Lots of technologies mentioned, lots of information given but now what… How do these come together?
In this post we’ll be covering how to use a Power Query Script to connect to the UiPath Data Service API, packaged as a custom connector that can be ingested into Power BI Desktop as a data source.
Some things you may want to consider before getting started:
- Install Visual Studio 2016, 2017 or 2019. 2022 does not seem to support what we will be trying to do
- Download and install the Power Query SDK
- Download the following Power Query code sample
- Build the project and copy the generated UiPathDataService.mez file into your Custom Connectors directory (Documents\Power BI Desktop\Custom Connectors)
Alternatively, you can download the custom connector from this GitHub repo and move it to your Power BI Custom Connectors directory.
Create a UiPath External Application
Navigate to the UiPath Automation Cloud where you will need to create an External Application. To do that, go to the ‘Admin’ tab and navigate to ‘External Applications’.
Click ‘Add Application’ and insert the following information:
- Type: Non-Confidential application
- Scopes: DataService.Schema.Read, DataService.Data.Read
- Redirect URL: https://oauth.powerbi.com/views/oauthredirect.html
Configuring Security Settings in Power BI
When you open Power BI Desktop, you may be prompted to update your security settings before you can use the custom connector.
To do this, go to File > Options > Security > Web Preview Level > Data Extensions. Check the ‘Allow any extension to load without validation or warning’ option and restart Power BI Desktop.
Pulling the UiPath Data Service Data into Power BI
Select ‘Get Data’. Search for the ‘UiPath Data Service’ data source, which should appear as a custom connector:
You’ll be prompted to provide the organisation name, the tenant name and the client ID (app ID of the external app created in UiPath Automation Cloud)
You should now be able to select the entities that you would like to use as data sources from UiPath Data Service:
Now you can go on to create cool visualisations from the data in Power BI:
Did this work for you? Feel free to leave a comment below.