# 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](https://aka.ms/pbidesktopstore) and the [PostgreSQL ODBC driver](https://www.postgresql.org/ftp/odbc/releases/REL-17_00_0006/). 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.

      ![The User DSN tab of the ODBC Data Source Administrator with the Add button](/images/pbx/add-user-dsn.png)
   3. Choose **PostgreSQL Unicode(x64)** and click **Finish**.

      ![Driver selection dialog listing the PostgreSQL Unicode driver](/images/pbx/create-postgresql.png)
   4. Enter the connection details described below, then click **Save**.

      ![PostgreSQL ODBC connection settings dialog](/images/pbx/postgresql-odbc-setting.png)

      | 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.

      ![The saved data source name listed in the ODBC administrator](/images/pbx/postgresql-dsn-name.png)
2. Open Power BI Desktop.
3. Click **Open** and select the PostgreSQL template file.

   ![Power BI Desktop Open dialog with the report template selected](/images/pbx/powerbi-open-template.png)
4. Repoint the template at your database and supply credentials:
   1. On the toolbar, click **Transform data** and choose **Data source settings**.

      ![Transform data menu expanded to show Data source settings](/images/pbx/power-bi-transform-data.png)
   2. Update the source:
   1. Select the default data source and click **Change Source**.

         ![Data source settings window with Change Source highlighted](/images/pbx/postgre-power-bi-change-datasource.png)
   2. From the **Data source name (DSN)** list, pick the DSN you created and click **OK**.

         ![DSN drop-down showing the PostgreSQL data source](/images/pbx/postgresql-select-dsn.png)
   3. Set the access credentials:
   1. Click **Edit Permissions**.

         ![Data source settings with the Edit Permissions button](/images/pbx/postgre-power-bi-edit-permission.png)
   2. In the dialog, click **Edit**.

         ![Permissions dialog with the Edit option](/images/pbx/postgre-power-bi-edit-permission2.png)
   3. Select **Database**, enter the credentials below, and click **Save**.

         ![Credential entry form with database authentication fields](/images/pbx/postgre-power-bi-edit-mysql-credential.png)

         | 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.

   ![The Refresh button on the Power BI toolbar](/images/pbx/power-bi-run-queries.png)

:::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.

![A Power BI report populated with call data](/images/pbx/power-bi-report.png)

## 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](https://aka.ms/pbidesktopstore) and [MySQL Connector/NET](https://dev.mysql.com/downloads/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](https://cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem).
  2. Add it to the Trusted Root Certification Authorities store:
   1. Run `certmgr.msc` to open Certificate Manager.

        ![Certificate Manager open on a Windows desktop](/images/pbx/powerbi-certmgr.png)
   2. Right-click **Trusted Root Certification Authorities**, then choose **All Tasks** > **Import**.

        ![Trusted Root Certification Authorities context menu with the Import command](/images/pbx/powerbi-import-certificate.png)
   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.

   ![Power BI Desktop Open dialog with the report template selected](/images/pbx/powerbi-open-template.png)
3. Repoint the template at your database and supply credentials:
   1. On the toolbar, click **Transform data** and choose **Data source settings**.

      ![Transform data menu expanded to show Data source settings](/images/pbx/power-bi-transform-data.png)
   2. Update the source:
   1. Select the default data source and click **Change Source**.

         ![Data source settings window with Change Source highlighted](/images/pbx/power-bi-change-datasource.png)
   2. Enter the connection details below and click **OK**.

         ![MySQL database connection dialog](/images/pbx/power-bi-database-info.png)

         | 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**.

         ![Data source settings with the Edit Permissions button](/images/pbx/power-bi-edit-permission.png)
   2. In the dialog, click **Edit**.

         ![Permissions dialog with the Edit option](/images/pbx/power-bi-edit-permission2.png)
   3. Select **Database**, enter the credentials below, and click **Connect**.

         ![Credential entry form with database authentication fields](/images/pbx/power-bi-edit-mysql-credential.png)

         | 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.

   ![The Refresh button on the Power BI toolbar](/images/pbx/power-bi-run-queries.png)

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

![A Power BI report populated with call data](/images/pbx/power-bi-report.png)

## 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](https://aka.ms/pbidesktopstore).
- If your Microsoft SQL server uses SSL, install its certificate first:
  1. Download the [SSL public certificate](https://cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem).
  2. Add it to the Trusted Root Certification Authorities store:
   1. Run `certmgr.msc` to open Certificate Manager.

        ![Certificate Manager open on a Windows desktop](/images/pbx/powerbi-certmgr.png)
   2. Right-click **Trusted Root Certification Authorities**, then choose **All Tasks** > **Import**.

        ![Trusted Root Certification Authorities context menu with the Import command](/images/pbx/powerbi-import-certificate.png)
   3. Complete the import wizard and click **OK**.

### Steps

1. Open Power BI Desktop.
2. Click **Open** and select the Microsoft SQL template file.

   ![Power BI Desktop Open dialog with the report template selected](/images/pbx/powerbi-open-template.png)
3. Repoint the template at your database and supply credentials:
   1. On the toolbar, click **Transform data** and choose **Data source settings**.

      ![Transform data menu expanded to show Data source settings](/images/pbx/power-bi-transform-data.png)
   2. Update the source:
   1. Select the default data source and click **Change Source**.

         ![Data source settings window with Change Source highlighted](/images/pbx/power-bi-change-datasource-mssql.png)
   2. Enter the connection details below and click **OK**.

         ![Microsoft SQL database connection dialog](/images/pbx/power-bi-database-info-mssql.png)

         | 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**.

         ![Data source settings with the Edit Permissions button](/images/pbx/power-bi-edit-permission-mssql.png)
   2. In the dialog, click **Edit**.

         ![Permissions dialog with the Edit option](/images/pbx/power-bi-edit-permission2-mssql.png)
   3. Select **Database**, enter the credentials below, and click **Save**.

         ![Credential entry form with database authentication fields](/images/pbx/power-bi-edit-mysql-credential-mssql.png)

         | 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.

   ![The Refresh button on the Power BI toolbar](/images/pbx/power-bi-run-queries.png)

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

![A Power BI report populated with call data](/images/pbx/power-bi-report.png)

## 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](/pbx/integrations/data-connector/call-report-calculations-with-multiple-sql-queries/).