Set up the Power BI dashboard

The Microsoft Power BI dashboard provides a holistic overview with visualizations and insights into the resources of your tenant: environments, apps, Power Automate flows, connectors, connection references, makers, and audit logs. Telemetry from the audit log is stored when you set up the Center of Excellence (CoE) Starter Kit, so you can identify trends over time.

Screenshot of the CoE Starter Kit Power BI dashboard.

Watch a walk-through (video) on how to set up the Power BI dashboard.

Which dashboard should I use?

You can get the CoE Power BI dashboard by downloading the CoE Starter Kit compressed file (aka.ms/CoeStarterKitDownload).

This file contains some Power BI template files:

  • Production_CoEDashboard_MMMYY.pbit - Use this file if your Power Platform inventory data source is from cloud flows.
  • BYODL_CoEDashboard_MMMYY.pbit - Use this file if your Power Platform inventory data source is from Data Export.
  • PowerPlatformGovernance_CoEDashboard_MMMYY.pbit - Use this file in addition to the above dashboards to gain further actionable governance and compliance insights into your adoption.

Prerequisites

Get the environment URL

You need the URL of the Power Platform environment where the CoE Starter Kit is installed. Power BI connects to Dataverse tables in that environment.

  1. Go to the Power Platform admin center.

  2. Select Environments, then choose your environment where your CoE solution is installed.

  3. Copy the organization URL in the details window, including the https:// prefix and the ending slash /.

    Screenshot of the Power Platform admin center with the environment URL highlighted.

    If the URL is truncated, you can see the full URL by selecting See all > Environment URL.

    Screenshot showing the Environment settings available in the Power Platform admin center.

Configure the Production and Governance Power BI dashboard

You can configure and modify the Power BI dashboard by working directly with the Power BI pbit file and Power BI Desktop. The Power BI Desktop allows you to customize your dashboard to your own branding and select only the pages or visuals you want to see.

  1. Download and install Microsoft Power BI Desktop.

  2. In Power BI Desktop, open the pbit file found in the CoE Starter Kit you downloaded from aka.ms/CoeStarterKitDownload.

  3. Enter the URL of your environment instance. Include the https:// prefix for OrgUrl. The Production_CoEDashboard_MMMYY.pbit has a Tenant Type parameter. If you're a regular commercial tenant, leave the dropdown as the default Commercial, otherwise pick your sovereign cloud.

  4. If prompted, sign in to Power BI Desktop with your organization account, containing the CoE Starter Kit environment.

    Screenshot that shows where you enter your OrgUrl to configure Power BI dashboard.

  5. Save the dashboard locally or select Publish, and choose the workspace where you want to publish your report.

    Tip

    If you're publishing and wish to retain the URL after each upgrade, select a name to copy over each month. For example, use Contoso CoE Governance.

  6. Configure scheduled refresh for your Power BI Dataset to update the report daily.

    You can find the report later by going to app.powerbi.com.

Configure the BYODL Power BI dashboard

Copy Azure Storage account URL

  1. Navigate to the Azure portal.
  2. Search for or select the storage account configured to receive Data Export data.
  3. Select Endpoints.
  4. Copy the Data Lake Storage URL.
  5. Append /powerplatform to the URL.

Import Power BI dataflows

Power BI dataflows can transform data from the Azure Storage account into tables that get used by the dashboard. But first, you must configure the Power BI dataflows.

  1. Navigate to app.powerbi.com.

  2. Select Workspaces > Create a workspace.

  3. Provide a name and description, select Advanced, and choose Premium per user or Premium per capacity depending on what’s available in your tenant.

  4. Select Large dataset storage format. You can also use an existing premium workspace, if available.

    Screenshot that shows the Create a workspace pane you see when creating a new premium workspace.

    Note

    A Power BI Premium trial is sufficient if you are only testing the integration of the Data Export feature with the CoE Starter Kit.

  5. Select + New > Dataflow. If prompted, select No, create dataflow instead of a datamart. Screenshot that shows how to create a new dataflow workspace.

  6. Select Import Model and upload the PowerPlatformAdminAnalytics-DF.json file, which can be found in the CoE Starter Kit you downloaded. Screenshot that shows the Import model option highlighted.

  7. From the workspace, select Datasets + Dataflows to see your imported dataflow.

  8. Edit the dataflow. Screenshot that shows where the Edit menu option is located for your dataset.

  9. Select Edit tables. Screenshot that shows where the Edit tables option is located from the menu bar. This option allows you to edit the tables of the imported dataflow.

  10. Select the Datalake URL parameter. Screenshot that shows where you can update the Datalake URL parameter to point to your Azure Storage account URL.

  11. Paste the Azure Storage account URL and make sure it ends with /powerplatform (no trailing slash /).

  12. From the Queries view, select one table after the other to configure connections and sign in with your account. If creating the connection fails, try selecting Source under Applied steps to retry configuring the connection. Screenshot that shows how to configure a connection.

  13. Select Continue when you see a notification about connecting data from multiple sources.

  14. Select Save and Close and wait for the validation to finish.

  15. Select Close to close the Edit tables view.

  16. Select the overflow menu > Settings on the dataflow.

  17. Configure a daily schedule refresh. A good time to refresh your dataflow is just after files write to your storage account by the Data Export feature. Therefore, your dataflow runs after data is exported to your storage account. Screenshot that shows where to schedule a refresh for your dataflow.

  18. Refresh the dataflow manually. The initial refresh might take one to two hours to run, depending on the size of your inventory. Check the Refresh history to see when the refresh completes.

  19. Copy the Power BI workspace and dataflow ID contained in the URL. Parts of the ID are removed for security purposes in the example images, but you should see a full ID number in your URL.

    1. Select the dataflow and copy the Workspace ID by looking at the URL and copying the GUID after /groups/. Screenshot that shows where you copy the workspace ID in the URL.
    2. Select the dataflow and copy the Dataflow ID by looking at the URL and copying the GUID after /dataflows/. Screenshot that shows where you copy the dataflow ID in the URL.

Configure the Power BI dashboard

  1. In Power BI Desktop, open the BYODL_CoEDashboard_MMMYY.pbit file found in the CoE Starter Kit you downloaded.

  2. Enter your URL and ID.

    1. Enter the URL of your environment instance, including the https:// prefix for OrgUrl.
    2. Enter the ID to your Power BI Workspace for Power BI Workspace ID.
    3. Enter the ID to your Power BI Dataflow for Power BI Dataflow ID.
    4. If you're a regular commercial tenant, leave the Tenant Type dropdown to default, otherwise pick your sovereign cloud.
  3. If prompted, sign in to Power BI Desktop with your organization account that has access to your CoE Starter Kit environment.

  4. Save the dashboard locally or select Publish and choose the workspace where you want to publish your report.

  5. Configure scheduled refresh for your Power BI Dataset to update the report daily.

You can find the report later by going to app.powerbi.com.

Configure embedded apps in the CoE dashboard

The Production_CoEDashboard_MMMYY.pbit and BYODL_CoEDashboard_MMMYY.pbit dashboards can be configured to use embedded apps to enable you to drive action based on insights you find. With the embedded apps, you can grant yourself access to resources, delete apps and flows, and reach out to the maker through email. You must configure the Power Apps visuals in the Power BI dashboard before you can use them.

Prerequisites to configure embedded apps

  • You must be an owner or co-owner of the Admin – Access this app and Admin – Access this flow apps to be able to embed them.
  • Because this app uses Microsoft Dataverse, end-users must have a Power Apps Per User license, the app must be assigned a Power Apps Per App license, or the environment must be covered by pay-as-you-go.
  • The user must be assigned the Power Platform Service Admin role or have admin permissions, since the app is for admins only.

Embed the apps

  1. Open the CoE Power BI dashboard in Power BI Desktop.

  2. Go to the Manage App Access page.

  3. Select the existing Power Apps visual, then choose Format visual and select Reset to default.

  4. Close the sharing notification. Screenshot that shows how to close the sharing notification.

  5. Select Choose app.

  6. Select the environment of your CoE, where you imported the apps. Screenshot that shows where to select Choose app.

  7. Search for and select Admin – Access this App [works embedded in Power BI only]. Screenshot that shows where to add the app access.

    Tip

    The app doesn't work when browsing directly to the page, but only works when an app is sent in via a drill through.

  8. Go to the Manage Flow Access tab and repeat the previous steps, but selecting the Admin - Access this Flow [works embedded in Power BI only] app this time.

Republish the dashboard, and view it under app.powerbi.com.

Troubleshooting

Unable to connect (provider Named Pipes Provider, error: 40 – Couldn't open a connection to SQL Server)

This error message means the connector failed to connect to the tabular data stream (TDS) endpoint. This error can occur when the URL used with the connector includes https:// and/or the ending slash /. Remove the https:// and ending slash / so the URL is in the form orgname.crm.dynamics.com.

Screenshot that shows the error message: Unable to connect.

A connection was successfully established with the server, but an error occurred during the handshake before you sign in

This error message means the connector failed to connect to the TDS endpoint. This error can occur if the ports, the TDS endpoint uses, are blocked. For more information, see Ports required for using SQL to query data.

Screenshot that shows the second type of error message that happens after you connect.

Unable to open document: The queries were authored with a newer version of Power BI Desktop and might not work with your version

This error message means you're on the current version of Power BI Desktop, select Close to continue, and set up the latest version.

Screenshot that shows the Unable to open document error message.

Sign-in issues

When you see sign-in issues, your data source settings might be cached to the wrong user or tenant. Here are a few examples of what that might look like:

Screenshot that shows the remote name not resolved error. Screenshot that shows the authorization not specified error.

The solution in this case is to clear the permissions:

  1. Open Power BI Desktop.
  2. Select File > Options and settings > Data source settings.
  3. Select the data source for the connection, for example https://mycoe.crm.dynamics.com, then select Clear Permissions.
  4. Try to open the Power BI template file again.

Not prompted for parameters

If you're not prompted for the input parameters like OrgURL when you open the pbit files, clear the cache:

  1. Open the pbit file and select discard changes. Screenshot that shows where to select Discard changes.

  2. Reopen the pbit file.

    You're prompted for the input parameters.

Data source error: Information is needed in order to combine data

This error message might appear when you're using the BYODL Dashboard. The privacy settings likely aren't configured correctly. As a result, Power BI can't combine data from the Azure Storage account and Dataverse.

Change the privacy level and update the credentials to resolve the issue:

  1. Open Power BI Desktop.

  2. Select File > Options and settings > Options.

  3. Select Global > Privacy and set the privacy level to Always combine data according to your Privacy Level settings for each source. Screenshot that shows where to set the privacy level in Options.

  4. Select OK, and then select File > Options and settings > Options again.

  5. Select Current File > Privacy and set the privacy level to Combine data according to your Privacy Level settings for each source. Screenshot that shows where to set the privacy level in Options for the current file.

  6. Select OK.

  7. Select File > Options and settings > Data source settings.

  8. Select Edit Permissions and set the privacy level to Organizational for both data sources. Screenshot that shows where to set the privacy level in Edit permissions section of your Data source settings.

  9. Save and refresh the report in Power BI Desktop.

  10. Select Publish.

  11. Navigate to app.powerbi.com and select your workspace.

  12. Select Datasets + dataflows.

  13. Select Settings on the dataset. Screenshot that shows where to find Settings in the overflow menu.

  14. Select Data Source Credentials.

  15. Select Edit credentials, set the privacy level to Organizational for both data sources, and select Sign in for each data source. Screenshot that shows where to configure privacy settings.

I found a bug with the CoE Starter Kit. Where should I go?

To file a bug against the solution, go to aka.ms/coe-starter-kit-issues.