Dataverse and Microsoft Fabric: Analytics on Dynamics 365 data without ETL

Analytics on Dataverse without building a pipeline

For years, reporting on Dynamics 365 Customer Engagement data meant one of three things: Power BI connected straight to Dataverse and hitting its limits, a nightly export job that someone had to babysit, or a data warehouse project that took longer than the CRM implementation itself. Link to Microsoft Fabric changes that equation. It makes your Dataverse tables available in OneLake, the data lake built into Fabric, without you building or running an ETL pipeline. Microsoft describes the feature in its Link to Microsoft Fabric overview.

I have now set up Fabric links for several CE environments, and the feature is genuinely useful. It is also easy to switch on without thinking through who will see the data, what it costs in Dataverse capacity and which downstream workloads it is actually suited for. This article covers how the link works, how it compares to Azure Synapse Link and the TDS endpoint, and the security and cost questions I ask before enabling it.

How Link to Fabric works

When you create a Fabric link, Dataverse maintains an optimized replica of the selected tables in Delta Parquet format, the native format of Fabric and OneLake. That replica is stored in Dataverse-managed storage, in the same region as your environment, so your operational workload is not affected by analytical queries. Fabric then sees the data through OneLake shortcuts: pointers to the data rather than another copy.

What the wizard creates for you

• A Fabric lakehouse: one lakehouse per link, with a shortcut for every linked Dataverse table.
• A SQL analytics endpoint: read-only T-SQL access to the same tables, usable from SQL Server Management Studio, Azure Data Studio or any SQL client that supports Microsoft Entra ID.
• A semantic model: a starting point for Power BI reports, which can use Direct Lake mode on top of the Delta tables.
• A data connection: the connection Fabric uses to talk to Dataverse, which you can switch to workspace identity or a service principal instead of a personal account.

Changes in Dataverse flow into the lakehouse continuously, but this is not real time. Microsoft documents that it can take up to an hour for changes to show up, and the initial sync of large tables takes longer. A low-latency sync engine is rolling out by region and is enabled automatically for new links; existing links stay on the old engine until you unlink and relink, which means a full resync. The new engine writes Delta Parquet directly, uses INT64 timestamps and deletion vectors, and drops the createdonpartition column from OptionsetMetadata. Check the documentation for the current state in your environment's region before you promise anyone near-real-time dashboards.

Setting up the link

The setup itself is a wizard in Power Apps (make.powerapps.com): select Link data in the left navigation, then + New link and the Fabric option. You can also start it from the Tables area via Analyze > Analyze in Fabric. The wizard is quick; the prerequisites are what usually slow a project down.

1. You need the System Administrator role in the Dataverse environment.
2. You need to be an administrator of the target Fabric (Power BI) workspace.
3. The workspace must sit on a Fabric capacity or Power BI Premium capacity; a trial capacity works for a proof of concept.
4. A Fabric administrator must allow item and workspace creation in the tenant settings, as well as access to OneLake data from apps outside Fabric.
5. Decide on authentication up front. Microsoft recommends workspace identity; it has to be added as an application user in Dataverse with an appropriate role.
6. Review the table selection. By default, every non-system table with Track changes enabled is selected. Clear what you don't need before you finish.

Every prerequisite and option is covered in Microsoft's step-by-step setup documentation.

Link to Fabric vs. Azure Synapse Link vs. the TDS endpoint

Link to Fabric is not the only way to get CE data into an analytics platform. In practice I weigh it against Azure Synapse Link for Dataverse (now labeled Link to Data lake in the Power Apps UI) and the read-only TDS (SQL) endpoint. They solve different problems.

My rule of thumb

• Choose Link to Fabric when the organization already uses (or is moving to) Fabric and you want CE data next to finance, web or product data without a pipeline to maintain.
• Choose Azure Synapse Link when there is an established Azure data platform, when data must land in a storage account the customer owns, or when downstream tools read files directly. Use the Delta/Parquet option; CSV profiles cannot be linked to Fabric later.
• Use the TDS endpoint for ad-hoc investigation and modest reports where respecting each user's Dataverse security matters more than volume. Queries are subject to a timeout and to service protection limits.
• Keep exports and integrations for operational data movement. Fabric is for analytics; if another system needs CE data to run a process, use a proper integration rather than reading from the lakehouse.

For the details of the alternatives, see the documentation on Azure Synapse Link for Dataverse and the TDS (SQL) endpoint.

Working with the data

Once the initial sync completes, the tables appear in the lakehouse with their Dataverse logical names (account, opportunity, incident and so on). Analysts can query them with T-SQL through the SQL analytics endpoint, with Spark in a notebook, or through the semantic model in Power BI.

T-SQL on the SQL analytics endpoint

Choice columns contain only the numeric value. The labels live in separate metadata tables (OptionsetMetadata for local choices, GlobalOptionsetMetadata for global ones, plus StateMetadata and StatusMetadata). The example below joins a custom local choice on account to its English label and summarizes the open pipeline per segment.

-- Open pipeline per customer segment (custom local choice crx_segment on account)
SELECT
    COALESCE(seg.LocalizedLabel, 'No segment') AS Segment,
    COUNT(o.opportunityid)                     AS OpenOpportunities,
    SUM(o.estimatedvalue)                      AS Pipeline
FROM opportunity AS o
INNER JOIN account AS a
    ON a.accountid = o.parentaccountid
LEFT JOIN OptionsetMetadata AS seg
    ON  seg.EntityName = 'account'
    AND seg.OptionSetName = 'crx_segment'
    AND seg.[Option] = a.crx_segment
    AND seg.LocalizedLabelLanguageCode = 1033
WHERE o.statecode = 0          -- Open
GROUP BY COALESCE(seg.LocalizedLabel, 'No segment')
ORDER BY Pipeline DESC;

PySpark in a Fabric notebook

For heavier transformations, attach a notebook to the lakehouse and work with the Delta tables directly. A typical pattern is to write curated results to a separate lakehouse you own, rather than modifying the one Dataverse manages.

# Fabric notebook with the Dataverse lakehouse attached as default
from pyspark.sql import functions as F

cases = spark.read.table("incident")

monthly = (
    cases
    .where(F.col("statecode") == 1)   # Resolved
    .withColumn("month", F.date_trunc("month", F.col("createdon")))
    .groupBy("month", "prioritycode")
    .agg(F.count("incidentid").alias("resolved_cases"))
)

# Write to your own curated lakehouse, not the one Dataverse manages.
# saveAsTable with a lakehouse name only works when that lakehouse is attached
# to this notebook; otherwise write to its abfss:// OneLake path with .save().
monthly.write.mode("overwrite").format("delta") \
    .saveAsTable("curated_lakehouse.case_volume_monthly")

Treat the linked lakehouse as a read-only source layer. Build your own curated layer next to it, so that a relink or a schema change in Dataverse does not break every report at once. The schema of the choice metadata tables is described in the choice labels documentation.

Security: Dataverse roles do not follow the data

This is the point I raise in every project. In Dataverse, what a user sees is decided by security roles, business units, team ownership, sharing and field-level security. The link itself reads Dataverse through the connection's identity, which typically holds the System Administrator role. In Fabric, access is governed by workspace roles and OneLake security, not by the user's Dataverse privileges.

How I approach it

• Restrict workspace roles: keep Admin, Member and Contributor roles for the data team. Report consumers should get access to reports or apps, not to the workspace.
• Use OneLake security for data access: where analysts need direct access, define roles on the lakehouse or specific tables instead of opening everything.
• Rebuild row-level security in the semantic model: if reports must respect ownership or business unit, model that explicitly in Power BI. It will not be inherited from Dataverse.
• Be selective with sensitive tables: columns protected by field-level security in Dataverse deserve the same scrutiny here. If a table is not needed for analytics, don't link it.
• Mind GDPR: the replica holds personal data. Include it in your record of processing activities and check how deletions and retention in Dataverse carry through to the curated layers you build downstream.

Fabric's own security model is explained in the OneLake security overview.

Cost considerations

Link to Fabric is marketed as 'no ETL', which is true, but it is not free. Two meters are involved.

• Dataverse database capacity: the Delta Parquet replica counts toward your Dataverse database capacity and is visible in the capacity reports of the Power Platform admin center. Large tables such as activities, audit-heavy custom tables or email can add up quickly.
• Fabric capacity: every query, notebook run, semantic model refresh and report interaction consumes capacity units. A busy Power BI workload on a small capacity will be throttled.
• Table selection: only linked tables consume storage. Because the wizard selects every table with change tracking by default, the cheapest optimization is simply clearing the tables nobody needs.
• Environment strategy: linking every sandbox doubles storage for little benefit. In most projects I link production and one non-production environment for report development.

Before enabling the link in production, check current database capacity usage, estimate the size of the tables you plan to link, and agree who pays for the Fabric capacity. That conversation is much easier before the storage alert arrives. Keep the documented limits in mind as well: at most 2,000 active tables per link and 200 MiB per record, which matters for email and activity tables.

Pitfalls I have run into

Most issues are not bugs but documented behavior that is easy to miss.

• Track changes not enabled: tables without change tracking can't be linked. Enable it on your custom tables as part of your solution, not by hand in production.
• Changing column data types: changing the type or precision of a synced column (for example whole number to decimal) breaks delta sync for that table, and the failure is permanent: deleting and recreating the column with the same name does not fix it. The workaround is removing and re-adding the table, which triggers a full resync.
• New columns not appearing: after adding columns, use Refresh Fabric tables on the link; the change appears with the next data change in that table.
• Unlinking with dependencies: unlinking deletes the lakehouse, and Fabric won't delete a lakehouse while pipelines or semantic models still reference it. Use lineage view to find dependent items first.
• Hard-coding the lakehouse name: Microsoft explicitly warns that the generated name format can change. Reference the workspace and lakehouse by ID.
• Locked-down workspaces: workspaces restricted to private links or selected networks for inbound traffic can't be used with Link to Fabric. Involve the network team early.
• Expecting real-time data: for operational dashboards that must reflect the last few minutes, query Dataverse directly or use the TDS endpoint.

Many of these limits are listed in the Link to Fabric FAQ.

Conclusion

Link to Fabric removes most of the plumbing that used to sit between Dynamics 365 Customer Engagement and a serious analytics platform. For organizations already invested in Fabric, it is now my default recommendation for CE reporting at scale.

Treat it as an architecture decision rather than a checkbox: choose the workspace deliberately, keep access tight because Dataverse security does not travel with the data, monitor Dataverse capacity, and build a curated layer of your own on top of the linked lakehouse. Do that, and you get CE analytics without a pipeline to maintain.