Accessing Chime Reports with Power BI

Introduction

In this article we will provide an example using Microsoft's Power BI to retrieve data from the Chime reporting API. Chime provides a reporting API, with token level access, in order to assist developers with creating external portals, dashboards, and custom reports.

The Power BI integration will use:

• Chime reporting APIs to retrieve CSV based reports from Chime

• Power Bi Desktop Application to configure data queries and reports

• Reports may be requested at the Chime queue level or from global Chime reports (data across queues)

Before Getting Started

First you will want to have a decent understanding of the chime reporting API and how to generate credentials and charts. You can find information on the chime reporting API here: https://teaminstant.visualstudio.com/Chime-Public/_wiki/wikis/Chime-Public.wiki/181/Chime-Reporting-API.

Background on Microsoft Power BI Desktop App

You will want a basic understanding of Power BI and an installation of Microsoft Power BI Desktop.

In order to configure advanced data connectors, please download the Microsoft Power BI desktop application here: https://powerbi.microsoft.com/en-us/downloads/ 

How to retrieve data from Chime

First, after installing Microsoft Power BI Desktop, You will want to start a new project by selecting File > New from the starting page.

Method 1: Blank Query & Advanced Editor

Select "Get Data" followed by "Blank Query" and then Advanced Editor.

Simply paste this cURL example:

let
    Source = Web.Contents(
        "https://ch-teams-net1.imchime.com/Chime/Report/InboundVsAnsweredChats?queueId=1&start=2020-11-01T04:00:00.000Z&stop=2020-12-01T04:59:59.999Z&csv=true", 
     [
        Headers = [
            #"Method"="GET",
            #"Authorization"="Bearer cec5c7ab-c8db-4774-b6d0-6a792833dde1"
            ]
            ])
in
    Source

Select "Done" on the following page and you will generate an inbound vs answered report with the specified date ranges.

Method 2: Web Query & Advanced Editor

To create a connector to chime, select "Get Data" and then "Web"

You will need a URL to the report you would like to generate. Refer to the chime reporting API documentation here to learn how to construct this URL: https://teaminstant.visualstudio.com/Chime-Public/_wiki/wikis/Chime-Public.wiki/181/Chime-Reporting-API.

We will use the following values in the advanced tab.

• Chime report URL: https://ch-teams-net1.imchime.com/Chime/Report/InboundVsAnsweredChats?queueId=1&start=2020-11-01T04:00:00.000Z&stop=2020-12-01T04:59:59.999Z&csv=true

We will add Headers for:

• Method / get

• Authorization / Bearer cec5c7ab-c8db-4774-b6d0-6a792833dde1

Configure the next window like this:

In this example we have generated the Inbound vs. Answered chart.

Using DAX to get data using dynamic ranges

Once we have generated the report in Power BI We will do a bit of extra coding in the DAX language to add the ability to get data based a dynamic range of dates. In this case we will set the date range from yesterday to now. This way when you check the report it will update with recent data.

To do this we will select "Transform Data" and then "Advanced Editor" in the following window.

We will enter the following DAX query on the advanced editor page:

let
    #"todaystring" = Date.ToText(DateTime.Date(DateTime.LocalNow())),  
    #"daybeforestring" = Date.ToText(DateTime.Date(Date.AddDays(( DateTime.LocalNow()),-1))),
    #"ChimeURL" = "https://ch-teams-net1.imchime.com/Chime",
    #"ReportExtension" = "/Report/InboundVsAnsweredChats",
    #"QueueID" = "?queueId=1",
    #"SourceUrlString" = #"ChimeURL" &#"ReportExtension" &#"QueueID" &"&start=" &#"daybeforestring" &"&stop=" &#"todaystring" &"&csv=true",
    Source = Csv.Document(Web.Contents(#"SourceUrlString", [Headers=[Method="get", Authorization="Bearer cec5c7ab-c8db-4774-b6d0-6a792833dde1"]]),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date UTC", type datetime}, {"Inbound Chats", Int64.Type}, {"Answered Chats", Int64.Type}, {"Queue Unavailable Chats", Int64.Type}, {"Deflected Chats", Int64.Type}})
in
    #"Changed Type"

In the query above we are using the following variables to construct our cURL call to the chime reports api.

• todaystring (will get todays date)

• daybeforestring (will get yesterdays date)

• ChimeURL (The URL of the chime server we are hitting)

• ReportExtension (The report we would like to fetch)

• QueueID (the ID of the queue we are hitting)

Once the string is concatenated we are adding the required get and authorization headers.

When you select "Done" your report will now update dynamically.

DAX (Power BI's supported language) is a robust language and you can read more about different things you can do here: https://docs.microsoft.com/en-us/dax/#:~:text=Data Analysis Expressions (DAX) is,Pivot in Excel data models .

Once you log in to the Power BI Windows application, you can publish the report up to your Power BI account / workspace.