Integrating Usage Intelligence Reporting APIs with Power BI

Yes

This article explains how to use Usage Intelligence Reporting APIs to create reports using Microsoft Power BI.

NOTE: Information on Usage Intelligence Reporting APIs can be found at Usage Intelligence Reporting API v3.0.0

Authentication

Before being able to request any data, you need to authenticate with the Usage Intelligence API.

The Usage Intelligence Authentication APIs cannot be integrated with Power BI directly, as they do not provide basic cookie handling functionality as of now, so you need to authenticate using Postman or another similar tool.

Refer to the Authenticate section of the Usage Intelligence Reporting API v3.0.0 documentation for more details.

The following is an example from Postman:

NOTE: In the response headers of this API, look for Set-Cookie with a session token and copy the value (you will need it later).

Create a Query in Power BI

Perform the following steps to create a query in Power BI.

To create a query in Power BI:

Open the Power BI Desktop.
Go to Home > Transform Data > Transform Data to open the Power Query Editor.
In the Power Query Editor, select New Source > Blank Query.
In the Advanced Editor, replace the existing code with the Power Query M code example provided here:

Example Code

The following is example code:

let
// session cookie token got from earlier step
preObtainedCookie = "RUI-Session-Token=xx; HttpOnly; Max-Age=39600; Path=/; SameSite=Strict; Secure",

// Common headers for the API call
commonHeaders = [
#"Content-Type" = "application/json",
#"RUI-Client" = "PowerBI",
#"RUI-Client-Version" = "1.0",
#"Origin" = "https://analytics.revenera.com",
#"Cookie" = preObtainedCookie
],

// Reporting API URL
apiUrl = "https://analytics-api.revenera.com/v3/reporting/eventTracking/lifetime/dataTable/2376158762",

// JSON payload content as required by the specific API used here, for other APIs, refer the documentation, make sure it is formatted as shown below
reportPayload = "{
""startDate"": ""2024-07-07"",
""stopDate"": ""2024-09-04"",
""globalFilters"": {},
""events"": null,
""sorting"": {
""events"": ""uniqueUsersUsedAtLeastOnce"",
""eventsDirection"": ""desc""
},
""categorizeEvents"": false,
""paging"": {
""startAt"": 0,
""limit"": 25
},
""groupBy"": ""clientId""
}",

// Convert report payload to binary
reportContent = Text.ToBinary(reportPayload),

// Make the API call and handle the response, change ManualStatusHandling as required
reportResponse = Web.Contents(
apiUrl,
[
Headers = commonHeaders,
Content = reportContent,
ManualStatusHandling = {200}
]
),

// Parse the JSON response
finalResult = Json.Document(reportResponse),
result = finalResult[result]
in
result

The example provided above is for POST API. For GET API, just omit the following steps:

Providing JSON payload content.
Convert report payload to binary.
Passing JSON content while calling API.

You will see the response as shown below:

The response from API is always in JSON format. Parse it as required by expanding the nested data, flatten hierarchies, pivot/unpivot data, and then load into Power BI to visualize the data.