FWIW, my queries are very inefficient. PowerBI self taught and pet project!
If someone would like to assist in optimizing before releasing template online please DM me for a collab. I am sure 8x8 would not appreciate their APIs getting hammered by inefficient queries :)
Original Message:
Sent: 04-24-2024 17:57
From: Ben Fischer
Subject: PowerBI / PowerQuery with 8x8 APIs
Sample...
Surfaced post call survey transscriptions
Before I share, I am fixing;
- variables for incremental refresh
- auto fall back for report granularity (if you request week and that won't work, it errors, so trying to incorporate a let &granularity&, then none, day, week, month)
- ticked logged for broken https://api.8x8.com/qm/ API. Not returning all records.
- trying to get detailed interactions report loaded
- data types so that they can be reported on correctly
Before using, you will need to
- Update all reports and APIs for your region (just an edit per query)
- Update your API and KEY for the GetBearer functions
Big thanks to Scott for assistance along the way!
------------------------------
Ben Fischer
Original Message:
Sent: 04-24-2024 16:01
From: Ben Fischer
Subject: PowerBI / PowerQuery with 8x8 APIs
I have had much progress and luck. There is two reports I need to get working then I will share my entire workspace with the group, And hope it can be improved. Expect it next week!
------------------------------
Ben Fischer
Original Message:
Sent: 04-24-2024 12:55
From: Justin Mangano
Subject: PowerBI / PowerQuery with 8x8 APIs
Hi @Ben Fischer - did you have any luck with this? I am in the same boat currently.
------------------------------
Justin Mangano
Original Message:
Sent: 12-17-2023 13:01
From: Ben Fischer
Subject: PowerBI / PowerQuery with 8x8 APIs
That's a brilliant start, thanks David!!
Yes, I have the PCS Api Key. I am signing up for a 2 week Precog trial today, they have connectors for 8x8 <> PowerBI out of the box... apparently. I'll let you know how it goes!
------------------------------
Ben Fischer
Original Message:
Sent: 12-15-2023 01:11
From: David Maxelon
Subject: PowerBI / PowerQuery with 8x8 APIs
I only have a rough POC in PBI but happy to show my progress so far;
lbbdtoken:
let
url = "https://api.8x8.com/oauth/v2/token",
clientId = "client id",
secret = "secret",
base64Encoded = Binary.ToText(Text.ToBinary(clientId & ":" & secret), BinaryEncoding.Base64),
headers = [
#"Content-Type" = "application/x-www-form-urlencoded",
#"Authorization" = "Basic " & base64Encoded
],
body = "grant_type=client_credentials",
options = [
Headers = headers,
Content = Text.ToBinary(body),
ManualStatusHandling = {400, 401, 403, 404, 500}
],
response = Web.Contents(url, options),
jsonResponse = Json.Document(response),
#"Converted to Table" = Record.ToTable(jsonResponse),
Value1 = #"Converted to Table"{0}[Value]
in
Value1
-----------------------------------------------------------------------------------------------------------------------------------
Example realtime report:
let
testToken = lbbdtoken,
dataUrl = "https://api.8x8.com/eu/analytics/cc/v5/realtime-metrics/queues",
dataOptions = [
Headers = [
Authorization = "Bearer "& testToken
]
],
source = Web.Contents(dataUrl, dataOptions),
#"Imported JSON" = Json.Document(source,65001),
#"Converted to Table" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "metrics"}, {"Column1.id", "Column1.name", "Column1.metrics"}),
#"Expanded Column1.metrics" = Table.ExpandListColumn(#"Expanded Column1", "Column1.metrics"),
#"Expanded Column1.metrics1" = Table.ExpandRecordColumn(#"Expanded Column1.metrics", "Column1.metrics", {"key", "value"}, {"Column1.metrics.key", "Column1.metrics.value"})
in
#"Expanded Column1.metrics1"
---------------------------------------------------------------------------------------------------------------------------------------
This works successfully; but really what I want is a few million rows from Historical interaction details and I only do this as a hobby so the pagination restrictions kill me.
For post call data - are you aware you need to request the additional token from 8x8 helpdesk? Not quite sure how that fits in as we ended up paying a supplier to do the reporting on our PCS and they built the API's into their system.
------------------------------
David
Original Message:
Sent: 12-14-2023 14:53
From: Ben Fischer
Subject: PowerBI / PowerQuery with 8x8 APIs
Howdy,
I have tried to add the PostCallSurvey API request into PowerBI and Query without success. I get the following error message. The same credentials work fine in Postman.
"we couldn't authenticate with the credentials provided"
"A web API key can only be specified when a web API key name is provided"
I also get the same above errors replicating this API call in PowerBI/Query but not in Postman. https://support.8x8.com/contact-center/8x8-contact-center/developers/8x8-contact-center-statistics-reporting-api#List_all_agents
Has anyone successfully obtained data via PBI/PQ? If so was there a trick to authenticate?
Cheers
------------------------------
Ben Fischer
------------------------------