How to set up x-hoppers integration with Power BI

This guide provides step-by-step instructions for configuring x-hoppers integration with Power BI and importing call adoption and performance metrics.

Created: October 2024

Permalink: https://x-hoppers.atlassian.net/wiki/x/IwABB

1 Introduction
- 1.1 Requirements
2 Setup
- 2.1 Adoption data
  - 2.1.1 Method 1. PowerBI direct import
  - 2.1.2 Method 2. Data export via API
- 2.2 Performance metrics
  - 2.2.1 CSV/XLSX import

Introduction

x-hoppers integration with Power BI (PBI) enables you to download x-hoppers analytical data and import it to Power BI for advanced reporting and visualization. The following data types are available for import:

Adoption: information about who joined a broadcast, when and for how long (import via a script)
Performance metrics: Agents and Stores stats, including wait and talk time, headsets battery status and more; Help points stats, such as QR code scans and tablet help points usage (import via CSV/ XLSX)

Requirements

x-hoppers system on WMS 6
Power BI
Node.js (required for specific scenarios)
x-hoppers SuperHopper+AI license to download performance metrics

Note: Steps describe in this Guide are also applicable to other platform for data analysis, such as Looker, Tableau, Excel and Google Sheets.

Setup

Adoption data

You can import adoption data into Power BI using one the following method.

Method 1. PowerBI direct import

This method involves uploading a DAX (Microsoft’s formula expression language) script to Power BI:

Open Power Query Editor in Power BI, select New Source and choose Blank Query

Open Advanced Editor for the blank query and paste the following script:

let
    pbxUri = "https://{pbxDomain}.wildixin.com/",
    user = "admin",
    pass = "{pass}",
    aud = "cds-ca",
    scope = "get",
    dateFrom = "2024-08-26",
    dateTo = "2024-08-30",

    baseAuth = "Basic " & Binary.ToText(Text.ToBinary(user & ":" & pass), BinaryEncoding.Base64),
    
    personalTokenResponse = Web.Contents(pbxUri & "api/v1/personal/token/?service=auth.wildix.com", 
        [
            Headers = [
                Authorization=baseAuth, 
                #"User-Agent"="WildixAuthUtils/1"
            ]
        ]
    ),
    personalTokenJson = Json.Document(personalTokenResponse),
    personalToken = personalTokenJson[result][token],

    authAccessTokenResponse = Web.Contents("https://auth.wildix.com/api/v2/Token/", [Headers=[Authorization="Bearer "& personalToken]]),
    authAccessUserTokenJson = Json.Document(authAccessTokenResponse),
    authAccessUserToken = authAccessUserTokenJson[result][accessToken],

    authOpenIdTokenResponse = Web.Contents("https://auth.wildix.com/api/v2/OpenId/service/", 
        [Content = Json.FromValue([aud = "cds-ca", scope = "scope"]), 
            Headers = [
                Authorization = "Bearer " & authAccessUserToken, 
                #"User-Agent"="WildixAuthUtils/1", 
                #"Content-Type"="application/json"
            ]
        ]
    ),
    authOpenIdTokenJson = Json.Document(authOpenIdTokenResponse),
    openIdToken = authOpenIdTokenJson[result][id_token],

   targetServiceResponse = Web.Contents("https://cds-ca.wildix.com/v1/calls",
        [
            Headers = [
                Accept = "application/json",
                Authorization = "Bearer " & openIdToken
            ],
            Query = [
                dateFrom = dateFrom,
                dateTo = dateTo
            ]
        ]
    ),
    targetServiceData = Json.Document(targetServiceResponse),
    items = targetServiceData[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"callCalleeCompany", "callCalleeDevice", "callCalleeEmail", "callCalleeGroupId", "callCalleeGroupName", "callCalleeMos", "callCalleeName", "callCalleePhone", "callCalleeType", "callCalleeUserExtension", "callCalleeUserId", "callCallerCompany", "callCallerDevice", "callCallerEmail", "callCallerGroupId", "callCallerGroupName", "callCallerMos", "callCallerName", "callCallerPhone", "callCallerType", "callCallerUserExtension", "callCallerUserId", "callDestination", "callEndBy", "callEndCause", "callEndCauseStr", "callGroupId", "callGroupName", "callMergeWith", "callRecordings", "callRemoteCountryCode", "callRemoteCountryCodeStr", "callRemoteLocation", "callRemotePhone", "callSplitReason", "callSplitTransferType", "callTrunkDirection", "callTrunkName", "conferenceId", "conferenceSubject", "connectTime", "connectTimeMs", "direction", "duration", "durationMs", "flags", "id", "part", "pbx", "service", "serviceNumber", "sessionId", "startTime", "status", "tags", "talkTime", "talkTimeMs", "time", "type", "waitTime", "waitTimeMs"}, {"callCalleeCompany", "callCalleeDevice", "callCalleeEmail", "callCalleeGroupId", "callCalleeGroupName", "callCalleeMos", "callCalleeName", "callCalleePhone", "callCalleeType", "callCalleeUserExtension", "callCalleeUserId", "callCallerCompany", "callCallerDevice", "callCallerEmail", "callCallerGroupId", "callCallerGroupName", "callCallerMos", "callCallerName", "callCallerPhone", "callCallerType", "callCallerUserExtension", "callCallerUserId", "callDestination", "callEndBy", "callEndCause", "callEndCauseStr", "callGroupId", "callGroupName", "callMergeWith", "callRecordings", "callRemoteCountryCode", "callRemoteCountryCodeStr", "callRemoteLocation", "callRemotePhone", "callSplitReason", "callSplitTransferType", "callTrunkDirection", "callTrunkName", "conferenceId", "conferenceSubject", "connectTime", "connectTimeMs", "direction", "duration", "durationMs", "flags", "id", "part", "pbx", "service", "serviceNumber", "sessionId", "startTime", "status", "tags", "talkTime", "talkTimeMs", "time", "type", "waitTime", "waitTimeMs"})
in
    #"Expanded Column1"

Replace the fields pbxURI, pass, dateFrom, dateTo with your own data, then click Done

Click Edit Credentials in the top ribbon, select Anonymous in the new window and click Connect to apply the setting to

In the next message, tick off Ignore Privacy levels checks…, then click Save

Your data should now appear in Power Query. Click Close & Apply to import it into the Power BI data model

Method 2. Data export via API

This method uses API calls to fetch data from CDS and integrate it with Power BI or any other platform for data analysis. For detailed API instructions, refer to this guide: https://docs.wildix.com/guides/2024/08/24/cds-ca/.

Performance metrics

CSV/XLSX import

To import the performance metrics into Power BI, follow these steps:

Navigate to x-hoppers Analytics and select the desired dataset, Agent Stats or HelpPoints

Hover over the report and click More -> Download

Choose the file format: XLSX or CSV
Click Download to save the file

Once you have the file, you can import it into Power BI:

In Power BI, click Home -> Get Data -> Text/CSV (or Excel, depending on the format)
Select the CSV or XLSX file that you downloaded
Click Load to import the data into Power BI.

You can also automate the process by scheduling x-hoppers reports to be sent via email and setting up an automatic import of the data into Power BI.