Appendix A: How To Import Data Into Power BI without using the Power BI Template

Before commencing, ensure you have received your BI Connector credentials:

• Server or Host URL

• Database name

• Username

• Password

Connecting to the PostgreSQL Database

Open Power BI Desktop, select Get data, navigate to PostgreSQL and select Connect:

Open

Enter the Server URL and database name into the popup window and select OK:

Open

Enter Username and Password and select Connect:

Select which tables to import (screenshot shows all three tables being imported) and select Transform Data.

Check the data type for each column in each table and make amendments where required.

For example, to change agentwordcountpercent from decimal to percentage, select the icon to the left of the column heading and select Percentage. Then select Close & Apply:

Converting the url field to a Web URL

During the Transform process above, it is not possible to convert the url column from Data Type “text” to Data Type “Web URL”.

To convert to Web URL, select the url column. Under Column tools, select Data category and then Web URL:

How to Extract Customized Metadata (clientdata Field) and App Scores (scorecard Field)

The clientdata and scorecard fields are imported into Power BI as lists. These lists are dynamic, insomuch as they will change from one customer to the next depending on:

• the metadata pushed to the platform; and

• the Apps created or updated from time to time.

Set out below is how to extract the data within the clientdata and scorecard fields. An alternative approach is to copy the Query Functions that have been included in the Power BI template that will significantly simplify the process outlined below.

Extracting Customized Metadata (clientdata Field)

Note: customers with a Genesys Cloud integration will already have some of their custom metadata fields extracted as separate columns (refer to Tables above).

The process has two steps:

1. Convert list to a record

2. Expand record into separate columns

To convert the list to a record, open the Power Query Editor by selecting Transform data on the Home tab:

Select the clientdata column and then select Parse > JSON (alternatively, right click on the clientdata header and select Transform > JSON):

Now select the icon to the right of the clientdata header to expand the record into separate columns:

Decide if you want to add “clientdata” as a prefix to the new column names for your custom metadata:

After selecting OK, your table should be expanded to show the custom metadata (your metadata may differ to the image below):

As before, you may want to change the data type before applying your changes.

To save your changes, select Close & Apply on the Home tab.

Extracting Workbench App Scores (scorecard Field)

If you were to follow the process above that is used for clientdata and expand the out-of-the-box Wordbench apps (Agent Scorecard, Call Drivers and Customer Experience), you would add an additional 130 columns to your table. This can make data management in Power BI unwieldy. It may be preferable to copy each Wordbench app to a separate table before expanding the columns.

First, open the Power Query Editor by selecting Transform data:

Select the table we want to duplicate (the one containing the scorecard field), then open the Advanced Editor. Copy the code in the popup window:

To create a new query, right click on the space below the tables and select New Query > Blank Query:

Open the advanced editor and paste the code into the window, then select Done.

We now have a new table. Transform the scorecard list to a record:

Expand the record:

We now want to remove all unwanted columns, retaining the insertid and Agent Performance columns.

Hold the Ctrl key and select the insertid and Agent Performance columns. Right click and select Remove Other Columns:

We now have a table with two columns. You can rename the table and expand the record to reveal the lower levels:

Note: when expanding some columns you will want to retain the column prefix so that you know that the score pertains to that category:

Eventually, you will attempt to expand a column where no further columns are found. You can remove this column:

Repeat the above process for each Wordbench app you need to expand.