Why connect PowerBI to Orchestrator?
Many of us developing robotic process automation solutions used to design so called Robot Reports or Robot Activity Logs. Those were usually Excel reports where all of the transactions were tracked from the business perspective (status, timestamp, relevant attributes etc.) and business users could monitor the success rate of the robot. Back in the days where we used Studio + Attended robot this was a fair solution.
Today when most of the companies sufficiently developed infrastructure and have their orchestrators in place, Orchestrator data and PowerBI together can yield powerful and re-usable reporting and monitoring mechanisms.
Orchestrator itself comes with a variety of prebuild reports, however often this is not enough. It is true that with the combination of Orchestrator and Kibana you may achieve similar results. On the other hand, if your organization leverages PowerBI as a preferred solution or your employees are more skilled in PowerBI than Kibana, you should continue reading this blogpost.
While it is possible to directly connect PowerBI to Orchestrator SQL database, usually in production environments your database admins will give you a dirty look if you mention the idea of obtaining direct access to database. For this reasons, we will show you how to connect PowerBI to Orchestrator API, and this is possible with having access only to Orchestrator web interface.
Prerequisites
To make useful reports with the combination of Orchestrator and PowerBI, it is advisable that you adhere to the following principles:
Apply dispatcher-performer pattern and utilize Orchestrator Queues
Have access to your Orchestrator (proper credentials)
Have PowerBI Pro licenses for users who monitor robot reports
Although there are many examples of how you can use Orchestrator data in situations where you do not have above three requirements, to exploit the full potential you should have this in place. Lets look at how a basic Orchestrator Queue looks.
.jpg)
All of this data, as well as fully customizable additional data you can add into queue items allow you for almost unlimited ways of how you can design your reports. Most importantly, this helps on standardizing your robot transactional monitoring. Separate post will cover the importance of Orchestrator Queues and Dispatcher-Performer pattern, and now let’s do some work.
Connect your PowerBI to Orchestrator
Getting data from the Orchestrator is performed in 2 Orchestrator API calls:
1. Authentication call
2. Data Request Call
To perform authentication, you need credentials. It is advisable to dedicate a read-only account to make API calls since I use hard-coded password in the Power Query.
Open Power Query
To perform Authentication, you will need Credentials, so firstly you need to make a Blank Query and call it Credentials:
Then, you should make your API request. API Request will differ whether you have on-premise orchestrator, cloud or community version. Below is showed a version where you have your on-premise Orchestrator and separate Tennant.
To easily navigate in API, you can refer to the swagger documentation site. Usually it is placed under the:
YOURORCHESTRATORURL.com/swagger/index , or
YOURORCHESTRATORURL.com/swagger/ui/index
But wait, where can I find my OrganizationUnitId? Let’s see…
Identify OrganizationUnitId using Orchestrator API UI
Of course, by calling another API. Alternatively, you can go directly to the swagger page (as I did) and retrieve manually your organization unit by calling GET on Folders to receive the folder Id.
Once you hit get on the Folders you should receive something like this:
The Id of the folder is the OrganizationUnitId you need to use when calling the Orchestrator API to get Queue Items.
So, in our case the Default folder was 31 so our final code (except for you base Url) should look like this
If the PowerQuery complaints then go to “Edit Credentials” and choose Anonymous Access. Only Anonymous Access is allowed when using token authentication.
And Voila! You are connected to Orchestrator API. Next post will explain how to make robot reports to monitor transaction statuses and robot performance.
