> For the complete documentation index, see [llms.txt](https://atlas.anywhere.now/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://atlas.anywhere.now/platform-administration/reporting-administration/reporting/set-up-power-bi-template.md). # Set up the Power BI Template Set up, update, and troubleshoot the Power BI Template for deployments that still use a customer-hosted CDR database. The Power BI Template connects Microsoft Power BI Desktop to historical Call Detail Records (CDR) data for deployments that still use a customer-hosted CDR database. {% hint style="info" %} This page applies to existing or transitional deployments that still use a customer-hosted CDR database. New Dialogue Cloud Neo customers do not receive a customer CDR database and use Dialogue Insights instead. {% endhint %} Use this procedure when you need to prepare the SQL views used by the template, open the `.pbit` template in Power BI Desktop, set the template parameters, or update an existing template package. After publishing the report to the Microsoft Power BI Service, workspace access, subscriptions, scheduled refresh, and report customization are Microsoft Power BI administration tasks. Do not use Atlas as a procedure source for support-supplied CDR archive, move, or retention scripts. ## Prerequisites Before you start, make sure you have: * The Power BI Template package supplied for the customer environment. * Microsoft Power BI Desktop installed on the workstation that will open the template. * Microsoft SQL Server Management Studio (SSMS) or Azure Data Studio. * Network access from the workstation to the SQL Server or Azure SQL endpoint that hosts the CDR database. * An account for SQL view setup that can create or modify views in the CDR database. * The database name and server or instance name for the SQL CDR database. * Power BI Desktop credentials that are allowed to read the data loaded by the template. If the template must load agent presence data, the source UCC must write presence data to the CDR database. The legacy setting `EnablePresenceRecording` controls this behavior; default `True`. {% hint style="warning" %} Keep CDR database access restricted. Allow only the approved addresses and ports required for the customer environment. For Dialogue Cloud CDR connectivity, use only the regional NAT gateway IP addresses supplied for that environment. {% endhint %} {% hint style="info" %} Do not confuse Power BI Desktop credential choices with CDR database provisioning requirements. Power BI Desktop may offer Windows, database, or Microsoft account sign-in options. CDR provisioning guidance can require a SQL account with `db_owner` on the CDR database, depending on the environment and provisioning step. {% endhint %} ## Create or update the SQL views Run the SQL views file from the Power BI Template package against the CDR database before opening the template in Power BI Desktop. 1. Extract the Power BI Template package. 2. Open SSMS or Azure Data Studio. 3. Connect to the SQL Server or Azure SQL endpoint that hosts the CDR database. 4. Open the CDR database. 5. Create a new query. 6. Paste the contents of the SQL views file from the package. The SQL views file is in the same package folder as the `.pbit` template file. Some packages identify this file with a name such as `Golive Views - Vx.x.sql`; treat that as a package file name, not as product terminology. 7. Execute the query. 8. Review the **Messages** output to confirm that the views were created or updated. ### Expected messages for first-time setup The setup script reports the view and index operations in the query messages. A first-time setup can include messages similar to: ``` Creating new PowerBI_Dialogue index Done creating new PowerBI_Dialogue index ------- Creating new PowerBI_Dialogue_vx Done creating PowerBI_Dialogue_vx ------- Creating new PowerBI_Hunt_vx Done creating PowerBI_Hunt_vx ------ Creating new PowerBI_Presence_vx Done creating PowerBI_Presence_vx ------ Creating new PowerBI_Classification_vx Done creating PowerBI_Classification_vx ------ Creating new PowerBI_CallFlow_vx Done creating PowerBI_CallFlow_vx ------ Creating new PowerBI_ChatTranscript_vx Done creating PowerBI_ChatTranscript_vx ------ Creating new PowerBI_Discharge_vx Done creating PowerBI_Discharge_vx ------ Creating new PowerBI_QM_vx Done creating PowerBI_QM_vx ``` {% hint style="info" %} `PowerBI_ChatTranscript_vx` is a legacy Skype for Business WebChat transcript view. It does not mean that WebAgent text-session transcripts are included in the Power BI Template. {% endhint %} ### Expected messages for an update When updating an existing package, the script can remove and recreate current-version views: ``` Deleting old PowerBI_Dialogue index Creating new PowerBI_Dialogue index Done creating new PowerBI_Dialogue index ------- Deleting old PowerBI_Dialogue_vx Creating new PowerBI_Dialogue_vx Done creating PowerBI_Dialogue_vx ------- Deleting old PowerBI_Hunt_vx Creating new PowerBI_Hunt_vx Done creating PowerBI_Hunt_vx ------ Deleting old PowerBI_Presence_vx Creating new PowerBI_Presence_vx Done creating PowerBI_Presence_vx ------ Deleting old PowerBI_Classification_vx Creating new PowerBI_Classification_vx Done creating PowerBI_Classification_vx ------ Deleting old PowerBI_CallFlow_vx Creating new PowerBI_CallFlow_vx Done creating PowerBI_CallFlow_vx ------ Deleting old PowerBI_ChatTranscript_vx Creating new PowerBI_ChatTranscript_vx Done creating PowerBI_ChatTranscript_vx ------ Deleting old PowerBI_Discharge_vx Creating new PowerBI_Discharge_vx Done creating PowerBI_Discharge_vx ------ Deleting old PowerBI_QM_vx Creating new PowerBI_QM_vx Done creating PowerBI_QM_vx ------ ``` For major view updates, for example from V2 to V3, old views are retained so existing custom Power BI reports can continue to work. ## Open the template in Power BI Desktop 1. In Power BI Desktop, open the `.pbit` template file from the package. The package template file can use a name such as `PowerBI vx.x.xxx.pbit`. 2. When Power BI Desktop prompts for parameters, enter the values for the customer environment. 3. When Power BI Desktop asks for credentials, choose the sign-in option that applies to the database connection and account you are using: | Power BI Desktop option | Use when | | ----------------------- | ---------------------------------------------------------------------------------------- | | Windows | The signed-in Windows account has access to the database. | | Database | You are using a SQL account for the database connection. | | Microsoft account | You are using a Microsoft account flow presented by Power BI Desktop for the connection. | 4. Load the data. 5. Save the report file. ## Template parameters Use the parameter prompt in Power BI Desktop during first load. To change parameters later, use **Home** > **Transform data** > **Edit parameters**. | Parameter | Required value or behavior | | --------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `SQL Server \ Instance` | SQL Server or instance name for the CDR database endpoint. | | `Database name` | Name of the CDR database. | | `Filter Method` | Use **No Filter (all data)** to load all available data, or **Previous Days** to limit the period. The default is **No Filter (all data)**. | | `Previous Days` | Number of previous days to load when **Filter Method** is **Previous Days**. The value cannot be `0`, even when **Filter Method** remains **No Filter (all data)**. | | `Load Call Details` | Set to `False` only when you accept that individual call-detail data will not be loaded. Disabling it can speed up loading. | | `Load Presence Data` | Set to `False` only when you accept that individual agent-presence data will not be loaded. Presence data can also depend on the UCC setting `EnablePresenceRecording`. | | `Anonymize Data` | Options are `No`, `Agents`, `Callers`, and `Agents & Callers`. `No` is the default. When anonymization is enabled, URI values are replaced with an anonymous ID format. | | `Service Level Agreement` | Numeric threshold, in seconds, used for the main SLA calculation. The documented default is 30 seconds. | | `Service Level Agreement 2` | Numeric threshold used for SLA2 calculations. Confirm the current value in the customer package before relying on this threshold. | | `Quickdrop` | Numeric threshold, in seconds, used to identify quickdrops. The documented default is 5 seconds. Quickdrops are excluded from SLA calculations. | | `Frequent Contacts` | Numeric threshold used to classify frequent contacts. Confirm the current value in the customer package before relying on this threshold. | | `Frequent Transfers` | Numeric threshold used to classify frequent transferees. The documented default is 5 transfers. | {% hint style="warning" %} Exact SLA2, SLA3, and Frequent Contacts defaults are package-dependent and must be confirmed from the current template package or by product/support before they are used for operational reporting decisions. {% endhint %} ## Validate the report After the data loads: 1. Confirm that the expected UCCs, skills, agents, and contacts are visible. 2. Confirm that the loaded period matches the selected filter parameters. 3. If **Load Call Details** is enabled, open a report area that uses call-detail data and confirm that rows are available. 4. If **Load Presence Data** is enabled, confirm that presence visuals contain data for the expected agents and period. 5. Validate a small sample of report totals against known CDR data before distributing the report. {% hint style="info" %} By default, only presence for the last 30 days is loaded, regardless of the **Previous Days** parameter. {% endhint %} ## Update behavior Updating the Power BI Template creates a new report file. If an existing Power BI report contains custom pages, visuals, or calculations, reapply those custom changes to the new file. Use this update sequence: 1. Back up the existing report file and any custom report assets. 2. Run the SQL views file from the new package against the CDR database. 3. Open the new `.pbit` file in Power BI Desktop. 4. Enter the same environment-specific parameters unless the new package instructions require a change. 5. Load and validate the data. 6. Reapply any custom report changes. 7. Save and distribute the updated report. ## Troubleshooting | Symptom or message | Checks | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Power BI Desktop cannot connect to SQL Server. | Confirm the server or instance name. Use the fully qualified domain name when required. Confirm that the workstation can reach the SQL endpoint and that the database engine accepts remote connections. | | `Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server... The wait operation timed out.` | Check firewall rules for the SQL Server instance and port. Confirm that SQL Server services are running and that TCP/IP is enabled for the SQL Server instance. | | `Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake... An existing connection was forcibly closed by the remote host.` | Check the configured TCP port and UDP port 1434 where those ports are used by the environment. Confirm the SQL Server network configuration. | | `Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0... Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)` | Try starting Power BI Desktop with elevated permissions. If the error persists, repair or reinstall the relevant Microsoft .NET Framework components on the workstation. | ## Limitations * The Power BI Template uses historical CDR data and reports on handled sessions. Conversations that are still waiting or not yet picked up are not included until they are handled. * The setup applies only to deployments that still have a customer-hosted CDR database. * WebAgent text-session transcripts are not included by the legacy transcript view. * Custom report design and custom calculation work are outside the standard service offering. Work with a Microsoft Power BI expert or partner for custom report development. ## Related pages * [Power BI Template](/use/monitor-and-report/power-bi-template.md) * [Power BI Template measurements and flows](/use/monitor-and-report/power-bi-template/power-bi-template-measurements.md) * [Power BI Template data reference](/product-reference/reporting-and-power-bi/power-bi-template-data-reference.md) --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://atlas.anywhere.now/platform-administration/reporting-administration/reporting/set-up-power-bi-template.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.