Edit

Access audit data with Azure Synapse Link for Dataverse and Power BI

Note

Azure Synapse Link branding will retire soon. In Power Apps, open Link data to access this experience. The UX uses Link to Data lake, while this article continues to use Azure Synapse Link so existing customers can recognize the feature.

Customers who use Azure Synapse Link for Dataverse can export audit logs to meet the external and internal auditing, compliance, security, and governance policies that are common to many enterprises.

Microsoft Dataverse auditing supports all custom and most customizable tables and columns. Dataverse stores audit logs and consumes log storage capacity. If you have Azure resources in the same region as the Dataverse environment, you can export audit logs to an Azure storage account with Azure Synapse Link for Dataverse and query the audit logs by using Azure Synapse Analytics and Power BI Desktop.

Prerequisites

Considerations

Important

  • This procedure requires the Delta Lake (Parquet) export option in Azure Synapse Link, which is deprecated. Beginning October 15, 2026, the option won't be available to new customers. Eligibility is evaluated at the Dataverse organization ID level, not at the tenant level. A new environment with a new organization ID isn't eligible, even if another organization in the same tenant is eligible. If you unlink an eligible existing organization, you can create the link again for that same organization ID. The opt-in URL doesn't override eligibility or extend the deprecation deadlines.
  • Existing customers must transition to CSV output in Azure Synapse Link or Link to Microsoft Fabric by December 2027, when the Delta Lake (Parquet) export option will no longer be available. Audit table export is supported only with Delta Lake profiles today. Link to Microsoft Fabric will support audit tables soon. Plan to use Link to Microsoft Fabric for this functionality when audit-table support becomes available. More information: Export Dataverse data in Delta Lake format.
  • Azure Synapse Link doesn't support synchronizing audit tables that contain more than 100 million records.

Tasks to complete

Here are the high-level steps for creating the storage and workspace in Azure, then the Azure Synapse Link for Dataverse so you can create reports in Power BI:

  1. Create an Azure Synapse Link
    1. Create a resource group.
    2. Create an Azure Data Lake Storage Gen2 account with hierarchical directory enabled.
    3. Create a Synapse workspace.
    4. Create an Apache Spark pool for the Synapse workspace.
    5. Grant storage account and Synapse workspace permissions to the Power Platform maker who creates the Azure Synapse Link for Dataverse.
  2. Connect Dataverse audit table to Synapse workspace
    1. Verify the auditing table’s sync status.
  3. Create reports and dashboards using Power BI
  1. Sign into the Azure portal.
  2. Create a resource group. More information: Create resource groups
  3. Create a storage account. More information: Create a storage account
    Under the Project details:

    1. Select the Subscription where the resource group was created.

    2. Select the Resource group that you created earlier.

    3. Enter a Storage account name.

    4. Select the Advanced tab and enable the Hierarchical Namespace option.

      Create an Azure storage account

  4. Grant Storage account access to the Power Apps admin who is connecting the Dataverse tables to this storage account.
    1. On the left pane, select Access control (IAM).
    2. Select Role assignments > + Add > Add role assignments.
    3. Under Add role assignment, select the Members tab.
    4. Select the Power Platform admin user, and then select the Privileged administrator roles tab.
    5. Enter owner in the Search box.
    6. Select the Storage Account contributor role.
    7. Enable Allow user to assign all roles (highly privileged) in What user can do.
  5. Repeat the previous steps for these roles:
    • Storage Blob Data contributor
    • Storage Blob Data owner
  6. Create a Synapse workspace.
    Under the Project details:
    1. Select the Subscription where the Resource group was created.
    2. Select the 'Resource group' that you created previously.
    3. Workspace name. Choose any globally unique name.
    4. Region. Select the region where your Dataverse environment is located.
    5. Select the Storage account name that you created earlier.
    6. Select Create new under the File system name.
    7. Enter a Name for the Data Lake Storage Gen2 file system.
  7. Create a Spark pool for the Synapse workspace.
    1. Go to the resource group of the Synapse workspace that was created earlier.
    2. The storage account and the Synapse workspace are listed under Resources. Select the Synapse workspace you created in the above step.
    3. Select + New Apache Spark pool to create a spark pool.
    4. On the new Apache Spark pool page:
    • Enter an Apache spark pool name.
    • Enter 5 in the Number of nodes.
    • Select the Additional settings tab, and then enter 5 for the Number of minutes idle.

Note

  • There are no costs incurred with the creation of Spark pools. Charges are only incurred once a Spark job is executed on the target Spark pool and the Spark instance is instantiated on demand. These costs are related to the usage of Azure Synapse workspace Spark and are billed monthly. The cost of conducting Spark computing mainly depends on the time interval for incremental update and the data volumes. More information: Azure Synapse Analytics pricing
  • It's important to take these additional costs into consideration when deciding to use this feature as they're not optional and must be paid in order to continue using this feature.

Connect Dataverse audit table to Synapse workspace

To connect your Dataverse audit table to a Synapse workspace, use the Azure Synapse Link that you created earlier in this article.

  1. Sign in to Power Apps with the Dataverse system administrator security role and select the environment you want.

  2. Open Link data, and then add enableSynapseParquet=true to the URL. Keep your usual Maker Portal host and environment ID. For example:

    https://make.powerapps.com/environments/<environment-id>/linkdata?tab=other&enableSynapseParquet=true

    Use the exact parameter spelling and lowercase true, and include the parameter only once. If the URL already contains ?, append &enableSynapseParquet=true. Otherwise, append ?enableSynapseParquet=true.

  3. Allow the page to finish loading, and then select Create link to data lake. Don't select Create a new Fabric link for this procedure.

    Link data page with Create link to data lake selected.

  4. In Create Link to Data lake, find the Get more from your data with Link to Fabric card, and then select Get Parquet format in a Synapse workspace anyway.

    Create Link to Data lake panel with the existing-customer Parquet option highlighted.

    Note

    The URL doesn't select Parquet. The creation panel starts with CSV, and the Parquet action appears only after the URL opt-in and your organization's eligibility are confirmed. If the action isn't available, don't try other feature flags.

  5. Select the Azure Subscription, Resource group, and Synapse workspace.

  6. Select Use Spark pool for Delta Lake data conversion job, and then select the Spark pool and Storage account.

    Create Link to Data lake panel with Spark enabled for the Delta Lake conversion job.

  7. Select Next.

  8. Expand the Advanced tab and enter 480 minutes in the Time interval field. Later, you can change how frequently you want to refresh the Dataverse tables in the Synapse workspace.

  9. Under the list of tables, select the Auditing and User tables.

The tables you select must have change tracking enabled. More information: Enable change tracking to control data synchronization.

It takes a while for the Dataverse tables to get exported to the Synapse workspace. After the export completes, you can manage the table data to the Synapse workspace. More information: Manage table data to the Synapse workspace

Verify auditing table sync status

  1. In Power Apps, select Link data, and then select Data lake links.
  2. Open the link that you created and verify the auditing table’s sync status. It should show as Active.

Create reports and dashboards using Power BI

Access the audit data using your Azure Synapse Analytics workspace with Power BI desktop. If you don't already have Power BI Desktop, download and install Power BI Desktop it. It's a free application that runs on your local computer.

Create reports using the Azure Synapse Analytics workspace connection

Use the Azure Synapse Analytics workspace connection for smaller volumes of data to build reports with Power BI.

  1. Start Power BI Desktop, and then in a new Power BI session, select Get data > More....
  2. In the left pane of the Get Data dialog pane, select Azure and then in the right pane select Azure Synapse Analytics workspace (Beta).
  3. Select Connect.
  4. Select Sign in, and enter or select your Organizational account, the account that has access to the Azure Synapse workspace that you created. Select Connect.
  5. Once signed in, select the workspace under the Navigator dialog pane.
  6. Expand the workspace and locate the Dataverse environment’s workspace. A list of Dataverse tables are displayed.
  7. Select the audit and the systemuser tables, and then select Load. Select the audit and systemuser tables in Power BI Desktop
  8. With the tables selected, you can build Power BI visualizations.

Create reports using the Azure Synapse Analytics SQL connection

Use the Azure Synapse Analytics SQL connection for larger volumes of data to build reports with Power BI.

  1. Start Power BI Desktop, and then in a new Power BI session, select Get data > More....
  2. In the left pane of the Get Data dialog pane, select Azure, and then select Azure Synapse Analytics SQL.
  3. Select Connect.
  4. Enter the Server and Database names.
    • The server name can be found in the Azure portal. Enter the workspace name in the Search bar, and then select the workspace. On the Overview page, copy the Serverless SQL endpoint and return to the Power BI SQL Server database page and paste it to the Server field.
    • To find the database name, go to Power Apps, select Link data > Data lake links, and then select the Azure Synapse Link. Select Go to Azure Synapse Analytics workspace on the command bar. You find your database name under the Lake database folder on the Data page.
  5. Select OK.

See also

System Settings Auditing tab

  • Last updated on