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 records and performance metrics.

Created: October 2024

Permalink:

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:

Calls: 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)

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

Setup

Calls data

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

Method 1. Dedicated PBI script

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:

PBI 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

x-hoppers Power BI-Ignore privacy level.png

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

Method 2. JS script

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.