Accessing Chime Reports With Power BI
How to Retrieve Data from Chime Using a Blank Query
Introduction
In this article we will provide an example using Microsoft's Power BI to retrieve data from the Chime for Teams 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.
Set up blank query
Power BI Advanced Editor
Select the Advanced Editor option
Simply paste this cURL example:
let
Source = Web.Contents(
"{{baseUrl}}/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 0000000-0000-0000-0000-000000000000"
]
])
in
Source
Select ‘Done” on the following page and you will generate an inbound vs answered report with the specified date ranges.
Query Table Power BI
Method 2: Web Query & Advanced Editor
To create a connector to chime, select "Get Data" and then "Web"
Select your data source in Power BI
Select to import data from a web page
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: {{baseUrl}}/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 0000000-0000-0000-0000-000000000000
Configure the next window like this:
From Web Configuration Power BI
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.
Transform Data Power BI
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" = "{{baseUrl}}/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 0000000-0000-0000-0000-000000000000"]]),[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.