Power BI

You can bring BotCity Insights data into Power BI using the BotCity Insights API. This way, the task, automation, and Runner data reported to Insights becomes available in the reports and dashboards you already build in Power BI.

On this page, you see how to:

• Get the API access token with a query in Power BI.
• Consume an API route as a Web data source.
• Make authentication dynamic in the Advanced Editor.

Introductory examples

The examples on this page cover a basic integration flow with Power BI. To learn about all available routes, parameters, and responses, see the BotCity Insights API reference.

Authenticate with the API

The first step to use the BotCity Insights API is authentication. The API uses an access token, sent in the header of all requests.

To get the access token through Power BI:

1. Open Power BI Desktop and create a new report.
2. In the Get data menu, select the Blank Query option.
3. Open the created query in the Advanced Editor to enter the code in Power Query M.

The code in this step makes a POST request to the API login route, sending the login and key values in the request body.

Where to find login and key

The login and key values are on the Dev. Environment page of your workspace in the BotCity Orchestrator.

Login

() =>
    let
        // Login route
        url = "https://your-company.botcity.dev/api/v2/workspace/login",
        headers = [#"Content-Type" = "application/json"],

        // Passing the workspace information in the body of the request (Dev. Environment)
        postdata = Json.FromValue(
            [
                login = "<WORKSPACE_LOGIN>",
                key = "<WORKSPACE_KEY>"
            ]
        ),
        // Getting the response
        response = Web.Contents(url, [Headers = headers, Content = postdata]),
        Data = Json.Document(response),

        access_token = Data[accessToken]
    in 
        // Returning the obtained access token
        access_token

Replace the login route URL

The URL https://your-company.botcity.dev in the code is only an example. Replace it with your company's dedicated server URL, the same one you use to access the Orchestrator, keeping the /api/v2/workspace/login route.

If you use the Community version, the base URL is https://developers.botcity.dev.

When you run this query, the API returns an access token.

With the function that returns the access token, you can use it as a parameter in the other requests to consume report data.

Consume report data

Use the endpoints below to consume BotCity Insights report data:

BASE URL

https://your-company.botcity.dev

Replace the base URL

The URL https://your-company.botcity.dev is only an example. Replace it with your company's dedicated server URL, the same one you use to access the Orchestrator.

If you use the Community version, the base URL is https://developers.botcity.dev.

Type	Route	Description
GET	/api/v1/insights/tasks	Lists the data of executed tasks
GET	/api/v1/insights/runners	Lists the data of the workspace Runners
GET	/api/v1/insights/automation	Lists the data of automations

In the same report, follow the steps below to consume data from the BotCity Insights API:

1. In the New Source menu, select the Web source to create a new query.

2. In the window that opens, check the Advanced option and enter the base URL of the BotCity Insights API.

   

3. In the second URL parts field, enter the route you want to use to consume the data.

4. In the HTTP request header parameters field, enter the token obtained in the authentication step and the organization of your workspace. All BotCity Insights API requests require these two values in the header.

   In the end, the configuration should look similar to this:

   

Value of organization

The organization value is the same as the login field, available on the Dev. Environment page of your workspace in the Orchestrator.

When you run the request, the API response appears in the data source configuration window. From there, you can apply the transformations needed to display the data the way you want.

Use the API in an advanced way

Make Power BI requests dynamic

When you create the query in the format above, Power BI automatically generates Power Query M code with the data you filled in.

In the example above, the access token was passed manually. With a small change, you can make the authentication step dynamic.

To do this, edit the query in the Advanced Editor and use the function created to get the token.

Token generated on every refresh

The function that gets the access token, created in the authentication step, can be passed as a parameter in the next requests.

This way, a new token is generated whenever the query is refreshed.