Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SteveCarter1
Advocate II
Advocate II

Anyone using Power BI to pull 8x8 CRM data?

Can't find much in the way of Power BI use. I'm looking at 8x8 developer api guides for case and contact management.

8x8 Case and Contact Management API - 8x8 Support

They provide an example but can't find much more.

Yes, I will be seeing what I can gleam from 8x8 support, but was hoping if anyone here has already hooked into this, specifically the 

8x8.com/api/ccm/

section, any tips or even list of sub-categories under ccm - the above only mentions /attachments/ - would be received greatfully.

 

Oh and if anyone wants to know how to pull 8x8 interaction data from the virtual contact center api then feel free to reach out. I've worked out how to use a combination of a list, parameters and converting PBI queries to functions the run the function over the list to get this. The biggest trick is they only return 50 records at a time so you have to 'page' through their data to get more.

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@SteveCarter1 

Very small topic, I have not seen any thing related to 8x8 with power bi. Is it possible to get help from the 8x8 support?

Vpazhenmsft_0-1626834258216.png

 

 

Paul Zheng

View solution in original post

22 REPLIES 22
GaryRailton
Frequent Visitor

Hello all,

 

We've ended up using an Azure Function App to connect to 8x8 then connect to Azure using a PBI dataflow. It seems to work well for us. 

 

This is the dev who set it up for us: Lokesh S. - Senior Data Engineer - Upwork Freelancer from Bengaluru, India

Tariqaliqau
Frequent Visitor

Hello everyone,

 

Also struggling with same,anyone please share helping material.

Thanks

I have created the email subscriptions of the 8x8 dashboard and then with power automate; I have the file uploaded in sharePoint folder. From sharePoint folder source, connect the data to PBI. This worked for me. 

Let me know if you need further assistance. 

If this works for you, please marked as Solutions. 

Thanks,

PT

The material I posted previously in this thread is what I have.

 

I too would welcome any further insights or improvements from anyone else in the community.

 

In particular in automating a way to pull back the most recent X VCC records as opposed to having a paramatised starting record number and also automating pulling CRM response data.

 

I am interested in what you have built on this matter, please send me the details at pthapa@qcsupply.com

thanks,

PT

lukegulley85
New Member

Hi Steve,

 

I'm also very interested in what you have worked out pulling data from the VCC.

 

My email is lgulley@asthma.org.au

 

Thanks,

Luke

Part1/2

URL's relate to Australia. copy/paste of the notes I have, minus screenshots and anything specific to my org.

 

Oh and if anyone from 8x8 sees this and goes hmmmm, please reach out. I'm happy to change the way I do this, but without easy to follow full documentation and an easy to sign up for forum access, I was on my own to do this. If you don't want customers doing this then publish a paper that we can all follow for your best practice, specifically for use with Power BI.

GOOD LUCK! 

Virtual Contact Centre (VCC) Data

VCC data includes all communication interactions and 8x8 offer a web based API to access call centre statistics using simple GET based URI’s.

Developer documentation on using this API is available here: https://support.8x8.com/cloud-contact-center/virtual-contact-center/developers/8x8-contact-center-st...

The base URL to use is as follows however there is nothing at this level. You need to go directly to one of the multiple sub-levels to return any data: https://vcc-au1.8x8.com/api/stats/

Instructions on how to use the API are here: https://support.8x8.com/cloud-contact-center/virtual-contact-center/developers/8x8-contact-center-st...

Access Tokens (how to log in to the API)

To access this information we need to use a user name and a password, with the latter known as the API token.

We have access to 2 types of tokens.

The Data Access token allows us to read and access data on our system.

The Action Request token allows us to manipulate the system programmatically. We do not use this in Power BI.

As we are using this data to build reports, we only use the Data Access token.

Testing Data Access

To test you can connect successfully you can type in the following URL in a web browser which should return a short list of groups configured on our system: https://vcc-au1.8x8.com/api/stats/group.csv

If you are not prompted for a username/password and are shown an error page then there is most likely a network level security restriction in place.

Getting the data into Power BI

In Power BI, to get this data you use the ‘Web’ connector. You will be accessing sub-level URLs such as https://vcc-au1.8x8.com/api/stats/group

 

If you put a .csv at the end of many of the group urls then you will get a CSV formatted data file returned. Without a .CSV extension it will return an XML formatted file. You can use either however we use XML data only.

When prompted for credentials you use the ‘Basic’ authentication type and enter the user name for the username and the data request token for the password.


The API will return an XML document that Power BI will partially recognise and perform a couple of initial data transformation steps for you, including navigating to the table it finds and apply type settings to the columns found.

Fixing unhandled XML parsing (URL results returned as a ‘Table’ element)

Power BI does not always get it right when needing to guess a data type when reading results especially if there are a lot of empty or blank entries. You will see this as a number of XML elements returned from a web query will show up as a ‘Table’.

To fix this we need to add a custom transformation step into the query to read what is inside the element and return that instead.

All results from these 8x8 queries should come back as single text elements. None have nested table results that I’ve found.

On those columns if you try to expand them you might get lucky and find PBI discovered a text element and allow you to expand that. In many cases you will not have any options.

Even if a element is found, expanding that column can produce error results further down the processing stack. An example would be if your function returns a table for a column in one call but text for another call in which case you end up with mixed data types in a single column which doesn’t work.

The fix is to open the Advanced Editor and to add a custom column transformation step. In the one step you read each of the columns that is listed as a table, check if you can read the first property from that ‘table’ and if you can then return that value. If you cannot read the first value of the table element then just return whatever it is.

Logic is: Try to read the first property of this table. If I can then I know this is a table and instead return just the text answer in that first property. If I cannot then I know that either it is not a table (i.e. just text) or it is empty but either way return whatever is in there.

let

    Source = Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/",

        [

            RelativePath=#"group-id" & "/activities",

            Query=[n=offset]

        ]

        )),

    Table0 = Source{0}[Table],

    Transformed = Table.TransformColumns(Table0, {

        {"trans-transfer-from", each if _ is table then Table.FirstValue(_) else _},

        {"trans-conference-from", each if _ is table then Table.FirstValue(_) else _},

        {"case-id", each if _ is table then Table.FirstValue(_) else _},

        {"tcl-list-id", each if _ is table then Table.FirstValue(_) else _},

        {"tcl-item-id", each if _ is table then Table.FirstValue(_) else _},

        {"recording-filename", each if _ is table then Table.FirstValue(_) else _}

        }),

    #"Changed Type" = Table.TransformColumnTypes(Transformed,{{"agent-id", type text}, {"agent-name", type text}, {"group-id", Int64.Type}, {"group-name", type text}, {"media-type", type text}, {"channel-id", Int64.Type}, {"channel-obj-id", type text}, {"queue-obj-id", type text}, {"queue-name", type text}, {"transaction-id", Int64.Type}, {"origination", type text}, {"customer-name", type text}, {"trans-accept-time", type datetimezone}, {"trans-proc-time", Int64.Type}, {"trans-post-proc-time", Int64.Type}, {"trans-total-time", Int64.Type}, {"tcl-item-shortcode-text", type text}, {"tcl-item-report-text", type text}, {"interaction-id", Int64.Type}, {"trans-rec-num", Int64.Type}})

in

    #"Changed Type"

 

Using parameters and functions to get data from dynamic URLs

**Using Power BI parameters as described here will work for Power BI Desktop but when uploaded to the Power BI Service you will not be able to refresh the dataset without an additional ‘RelativePath’ option which is described under Workaround for refreshing the data in Power BI Service

A lot of data we collect comes from individual queries against each subset.

For example to get a list of groups we just call https://vcc-au1.8x8.com/api/stats/groups however to get a list of all activities for each group we need to insert the group id into the URL such as https://vcc-au1.8x8.com/api/stats/groups/100/activities , https://vcc-au1.8x8.com/api/stats/groups/137/activities and so on.

Although we only have a few groups and could do this manually by creating a separate URL containing each group id then combine the results, this creates a static set of groups that would need manual updating every time there was a change to the groups and some process put in place for us to know when we needed to make a change.

For the agent sub-level data – a.k.a. each individual phone line, this becomes a lot more problematic as we have dozens and it is just not possible to deal with in such a manual way.

To get this data dynamically, we use Power BI parameters and functions.

As an overview we create a custom column that uses a function we create that calls the URL we want and uses a parameter to insert the id or whatever it is we need to call but we replace the parameter with a column reference instead.

  • Create a text parameter with a ‘safe’ default value (one that should always exist)
  • Create a new Web query to get the data, using ‘Advanced’ mode and specify the parameter in the web part where needed
  • Right-click the new query and select ‘Create Function’.
    This will group the query and parameter and new function together
  • Create a new query that gets the list of parameters we need
  • Add a new column as a special function and set the parameter to the column that contains the list we want to run through
  • Lastly expand the custom column

Following a worked example, if we want to see activity for all groups:

The URL for activity of a single group if the group id is 100 is: https://vcc-au1.8x8.com/api/stats/groups/100/activities

 

Create a parameter to hold the group id. Call it group-id or something obvious

Set it to Text and set the ‘Current Value’ to a group id that exists, like 100.

You can get a list of groups and their id’s by calling the query https://vcc-au1.8x8.com/api/stats/groups

Part 2/2:

Create a new query which we will use to create the function

Get data using the Web connector. At the URL screen select ‘Advanced’.

Enter the URL up to the part where the group id needs to be entered: https://vcc-au1.8x8.com/api/stats/groups

At that point select the next URL part as ‘parameter’ and select the group-id parameter you created earlier
Now complete the URL by adding the final third part of the URL query /activities

Now click OK.

At the Navigator screen, select the activity table then load the query into the data model. This returns the list of activities for the group-id we manually assigned to the parameter when we created it.

Right-click your new query and select ‘Create Function’

Give it a descriptive name then click OK.

The result is a new group that contains the original query, parameter and newly created function

 

Create a new base query to get a list of groups – it’s this list we want to run our function over

Get data using the Web connector. At the URL screen type in: https://vcc-au1.8x8.com/api/stats/groups
then click OK.

Select the table then load that into the data model

Add a new column using our function

On the ‘Add Column’ ribbon select ‘Invoke Custom Function’.

Select your query from the Function query drop down list.
For the parameter make sure it shows the ‘column’ icon and that it has the group-id column selected then click OK.

You will now get a new column with a table as the element

Expand your new column.

The result is you now get a table of activities for each of the groups listed in the group-id column.

If a group is removed or new group added, the query will automatically pick this up and refresh accordingly.

Workaround for refreshing the data in Power BI Service

At this point the dataset can be refreshed in PBI Desktop successfully.

However it is not possible to refresh it once uploaded to the PBI Service.

 

The problem is due to using a parameter, which we need to do, in the Web.Contents() M query function and the code that the Desktop application generates when we compile the Get Data web URL parts.

Working through the same Group Activities example, the issue is specifically in the query in the function GetActivities we created earlier:

Source = Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/" & #"group-id" & "/activities"))

 

This can be resolved using a Web.Contents option called RelativePath.

Update the query to read as:

Source = Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/", [RelativePath=#"group-id" & "/activities"]))

 

Now you should be able to upload the report to the PBI Service and schedule refreshes.

If this is the first time you have used the data source in the PBI Service you will also be prompted to provide login credentials, at which point you should select ‘Basic’ authentication, enter the account information and set Privacy Level to ‘Organizational’.

VCC API 50 record limit (pagination)

The API will only return 50 records at a time.

You can see this in Power Query by looking at the results our function returns (within the function, not the GroupActivities query) and you’ll see in the bottom left corner it says 50 ROWS.

 

To pull back more than 50 records you need to call the URL multiple times, requesting the offset record number using the URL option ‘?n=x’ where x is the offset record number.

By default, n is 0 so you will always only get the first 50 records that exist.

For our group activities function, the following 2 calls return the same first 50 records:

Source = Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/", [RelativePath=#"group-id" & "/activities"])),

 

Source = Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/",
        [
            RelativePath=#"group-id" & "/activities",
            Query=[n="0"]
        ]
        ))

 

To get more than 50 records using the above modified function code we create a list of numbers counting in steps of 50 – which we call offset, convert that to a table, then call this modified function using the offset as an input to the function.

 

Create a new parameter called ‘offset’, set type to ‘Text’ and set ‘Current Value’ to 0.

Change your function query to read:

Source = Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/",
        [
            RelativePath=#"group-id" & "/activities",
            Query=[n=offset]
        ]
        ))

 

So now the query will call the parameter called ‘offset’ which is set to 0. All good.

But now we will have another problem.

Requesting a record offset that does not exist

If we call for a record offset that does not exist, 8x8 will not return a blank XML table. It only returns the ending table element which causes problems for Power BI when trying to parse the results.

For example if I manually type this URL into my web browser:

https://vcc-au1.8x8.com/api/stats/groups/100/activities?n=999999

 

It does not include the opening XML tag “<activities>”

This results in an error when trying to parse incomplete tables.

The fix is to test if the call returns an error, and if it does then return a blank table with the opening and closing headers.

We do this using the try..otherwise error handling expression feature of M Query.

Replace the Source query above with this (changes in red):

Source = try Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/",
        [
            RelativePath=#"group-id" & "/activities",
            Query=[n=offset]
        ]
        )) otherwise “<activities></activities>”

Now to extend this to call multiple offsets.

Create a new Blank query then enter the following code:

= List.Numbers(0,500,50)

 

You’ll now have a list of numbers counting from 0 in steps of 50, 500 times.

 

Rename this query to GroupActivitiesOffset (the expected total record count for group activities compared to other queries won’t be anywhere similar so no point using the same x thousand offsets if we are only expecting a few dozen).

Now

  • disable loading of the list

 

  • convert it to a table

 

  • rename the column to ‘offset’

 

Tranform the column type to Text

Now we need to inject this list into the GroupActivities query so we can call our function against each group id and offset to pull back all records for each group.

Go to the GroupActivities query, select the ‘Changed Type’ step then add a new Custom Column. Call it offset and in the formula field type the name of the offset table we just created.

This now gives us a table showing us all groups we have with a list of offset numbers for each group.

Now expand the column.

If the stars are aligned, you should now be able to click on the last step in the query and get all records for each group. You will know this is the case if you have more than (number of groups) x 50 records returned.
Lastly you might hit a PBI service timeout limit if there's too much to pull back, in which case you could play around with parameters and the starting offset in the List.Numbers function - or even better would be to somehow determine how many records there are first, then minus however many records from that number that you know you can successfully get reliably then dynamically set the starting number in the List.Numbers function to that. For that you're on your own because I just manually bump it up every couple of months instead (got other fires to put out that are more important).

Anonymous
Not applicable

I have been trying to parse this for over a day, and I don't understand where some of the changes go.


When adding the "otherwise “<activities></activities>”, does that go in the function, the query the function is based on (activity), or the query that uses the function (group)?

 

I also would like to know the same for the transformation listed in part 1.

 

Thanks!

Andrew

Hi
The otherwise is part of the try function but formatting might be messing things up. When I copy/paste from above, the double-quotes around the code in the otherwise part were a different style than stright double-quotes. Hopefully with this in a code block it should be more clear and work for you. In a nutshell I am returning an empty XML table that PBI can parse without producing an error.

Source = try Xml.Tables(Web.Contents("https://vcc-au1.8x8.com/api/stats/groups/",
        [
            RelativePath=#"group-id" & "/activities",
            Query=[n=offset]
        ]
        )) otherwise "<activities></activities>"

 

Anonymous
Not applicable

I get that, I just am not sure if these are edits in the function (GetActivities) , the group query, or the activity query. I am just unclear where these edits go. For example, the transformation edit in part 1, I have no clue where that goes, so I have tried the function and the group query, and both. at the same time. When I add the offset step at the end, I just end up copying the same query of 50 items over and over again and am rather lost.

It's inside the function. For example for me:

SteveCarter1_0-1670363844264.png

 

GaryRailton
Frequent Visitor

Hello Steve,

 

I'm interested in what you have worked out in regards to data from the VCC please. 

 

KR

Gary

Hey.  If you PM me with your e-mail address I'll send through what I have.  The message function here in PBI community seems to be quite limited so I'm struggling to copy/paste the word doc info I have and send it.

Hi ,

I'm building a report and I need to pull the number of calls from 8x8 to PBI .
I will appreciate if you could send me the steps to follow;

fouad.choaibi@theworkperk.com 

Thanks!

Hello,

Here is the basic steps that I followed to get the 8x8 call data in Power bi.

How to get the 8x8 call data in PBI?

Please follow the steps below:

  1. Log in to apps.8x8.com
  2. Go to Analytics for 8x8 Work
  3. To the right of the screen, click the drop down on your name > Select schedule emails.
  4. New
  5. Select the report you want to send to your desired email address. Fill in other required info.
  6. Once it done, you need to create a power automate flow to grab this email from inbox and drop it in a Sharepoint file. Make sure to create the file first in sharepoint.
  7. Test the flow.
  8. Create the connection for Power Bi from the share point file you just created above.
  9. Build the desired report in PBI.

Be grateful if you could send to me also?  Many Thanks.  keith@dbfb.co.uk  

Hello,

Here is the basic steps that I followed to get the 8x8 call data in Power bi.

How to get the 8x8 call data in PBI?

Please follow the steps below:

  1. Log in to apps.8x8.com
  2. Go to Analytics for 8x8 Work
  3. To the right of the screen, click the drop down on your name > Select schedule emails.
  4. New
  5. Select the report you want to send to your desired email address. Fill in other required info.
  6. Once it done, you need to create a power automate flow to grab this email from inbox and drop it in a Sharepoint file. Make sure to create the file first in sharepoint.
  7. Test the flow.
  8. Create the connection for Power Bi from the share point file you just created above.
  9. Build the desired report in PBI.
V-pazhen-msft
Community Support
Community Support

@SteveCarter1 

Very small topic, I have not seen any thing related to 8x8 with power bi. Is it possible to get help from the 8x8 support?

Vpazhenmsft_0-1626834258216.png

 

 

Paul Zheng

Please follow the channel, I have provided the workaround that I did for my 8x8 call report.

See the thread above.

Thanks,

Pthapa

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors