Import Phone System Data into Power BI

Once Cloud Voice is synchronizing your phone system data to an external database, you can register that database as a data source in Power BI and load it into the ready-made Cloud Voice report template. Power BI is Microsoft’s business intelligence tool: it turns raw table data into charts and dashboards, so this is how you visualize call activity outside of Cloud Voice. The template ships with variants for each supported database engine, so pick the one that matches where your data lives. This page walks through the setup for PostgreSQL, MySQL, and Microsoft SQL Server.

Note

This procedure assumes the data synchronization is already running and the database is receiving Cloud Voice data. If the database is still empty, set up the synchronization first, otherwise Power BI will connect but find no records to display.

Import data from PostgreSQL

Before you begin

• Download and extract the Cloud Voice Power BI template package. It contains a separate template file for each database type; use the PostgreSQL one.
• Install Power BI Desktop and the PostgreSQL ODBC driver. ODBC (Open Database Connectivity) is the standard interface Windows uses to talk to a database.

Steps

Caution

Open the 64-bit ODBC tool and choose the x64 PostgreSQL driver. Power BI Desktop is a 64-bit application, so a DSN created with the 32-bit ODBC administrator will not appear when you connect from Power BI.

1. Create an ODBC connection to the database:

   1. Open ODBC Data Sources (64-bit).

   2. On the User DSN tab, click Add. A DSN (Data Source Name) is the saved connection profile that Power BI will point to.

      

   3. Choose PostgreSQL Unicode(x64) and click Finish.

      

   4. Enter the connection details described below, then click Save.

      

      Field	What to enter
      Database	The name of the database.
      Server	The IP address or domain name of the host running the database.
      Port	The port the database listens on.
      User Name	The account used to connect to the database.
      Password	The password for that account.

   5. Take note of the DSN name shown in the list, you will select it in a later step.

      

2. Open Power BI Desktop.

3. Click Open and select the PostgreSQL template file.

   

4. Repoint the template at your database and supply credentials:

   1. On the toolbar, click Transform data and choose Data source settings.

      

   2. Update the source:

      1. Select the default data source and click Change Source.

         

      2. From the Data source name (DSN) list, pick the DSN you created and click OK.

         

   3. Set the access credentials:

      1. Click Edit Permissions.

         

      2. In the dialog, click Edit.

         

      3. Select Database, enter the credentials below, and click Save.

         

         Field	What to enter
         User name	The account used to connect to the database.
         Password	The password for that account.

   4. Save your changes.

   Tip

   Power BI stores the credentials you enter here. Point it at a database account limited to read-only access so the report can read your data but never change it.

5. Click Refresh to run every SQL query in the template. SQL (Structured Query Language) is the language the template uses to pull each report from the database.

   

Note

Refresh re-runs every query in the template, so run it again after any change to the data source or credentials. On a large database the first load can take a while.

Your phone system data loads and appears on the report pages.

Import data from MySQL

Before you begin

• Download and extract the Cloud Voice Power BI template package and select the MySQL template.
• Install Power BI Desktop and MySQL Connector/NET.
• If your MySQL server uses SSL (Secure Sockets Layer, the encryption for the database connection), install its certificate first:
  1. Download the SSL public certificate.
  2. Add it to the Trusted Root Certification Authorities store:

     1. Run certmgr.msc to open Certificate Manager.

        

     2. Right-click Trusted Root Certification Authorities, then choose All Tasks > Import.

        

     3. Complete the import wizard and click OK.

Caution

The certificate must go into the Trusted Root Certification Authorities store specifically. If it lands anywhere else, the SSL connection to MySQL will fail. Skip this step entirely if your MySQL server does not use SSL.

Steps

1. Open Power BI Desktop.

2. Click Open and select the MySQL template file.

   

3. Repoint the template at your database and supply credentials:

   1. On the toolbar, click Transform data and choose Data source settings.

      

   2. Update the source:

      1. Select the default data source and click Change Source.

         

      2. Enter the connection details below and click OK.

         

         Field	What to enter
         Server	The IP address or domain name of the host running the database, along with the database port.
         Database	The name of the database.

   3. Set the access credentials:

      1. Click Edit Permissions.

         

      2. In the dialog, click Edit.

         

      3. Select Database, enter the credentials below, and click Connect.

         

         Field	What to enter
         User name	The account used to connect to the database.
         Password	The password for that account.
         Select which level to apply these settings to	The database the account is allowed to access.

   4. Save your changes.

4. Click Refresh to run every SQL query in the template.

   

Your phone system data loads and appears on the report pages.

Import data from Microsoft SQL Server

Before you begin

• Download and extract the Cloud Voice Power BI template package and select the Microsoft SQL template.
• Install Power BI Desktop.
• If your Microsoft SQL server uses SSL, install its certificate first:
  1. Download the SSL public certificate.
  2. Add it to the Trusted Root Certification Authorities store:

     1. Run certmgr.msc to open Certificate Manager.

        

     2. Right-click Trusted Root Certification Authorities, then choose All Tasks > Import.

        

     3. Complete the import wizard and click OK.

Steps

1. Open Power BI Desktop.

2. Click Open and select the Microsoft SQL template file.

   

3. Repoint the template at your database and supply credentials:

   1. On the toolbar, click Transform data and choose Data source settings.

      

   2. Update the source:

      1. Select the default data source and click Change Source.

         

      2. Enter the connection details below and click OK.

         

         Field	What to enter
         Server	The IP address or domain name of the host running the database, along with the database port.
         Database	The name of the database.

   3. Set the access credentials:

      1. Click Edit Permissions.

         

      2. In the dialog, click Edit.

         

      3. Select Database, enter the credentials below, and click Save.

         

         Field	What to enter
         User name	The account used to connect to the database.
         Password	The password for that account.

   4. Save your changes.

4. Click Refresh to run every SQL query in the template.

   

Your phone system data loads and appears on the report pages.

Customize the reports

You can add or edit SQL queries to control which data the template displays.

Note

Because Power BI cannot combine multiple queries from a single source, the template shows only these call reports out of the box:

• Extension Call Accounting
• Extension Call Accounting Details
• AI Receptionist Call Activity
• Agent Missed Call Activity
• Queue Callback Activity
• Satisfaction Survey
• Satisfaction Survey Details
• IVR Report
• DID/Outbound Caller ID Activity

In these names, IVR (Interactive Voice Response) is the automated phone menu, and DID (Direct Inward Dialing) is a phone number that routes straight to an extension.

To surface other call reports, add the SQL queries you need. For the underlying data logic, see Call Report Calculations with Multiple SQL Queries.