D365 F&O: Accelerate DirectQuery with Databricks ADBC
Directly querying high-volume Dynamics 365 Finance and Operations transactional tables such as GeneralJournalAccountEntry and InventTrans often creates severe report latency and gateway throttling. Traditional Open Database Connectivity (ODBC) connectors convert columnar lakehouse data into row-oriented memory structures, creating huge CPU and serialization overhead across Power BI semantic models. By adopting the Generally Available Databricks Arrow Database Connectivity (ADBC) connector in Power BI and Microsoft Fabric, you can bypass row serialization entirely and unlock native columnar data transfer speeds for enterprise financial and operational reporting.
At a glance
- 🔹 Applies to: Dynamics 365 Finance, Dynamics 365 Supply Chain Management (version 10.0.38 and later), Power BI Desktop, Power BI Service, Microsoft Fabric, Azure Databricks.
- 🔹 Prerequisites: D365 F&O data synchronized to Azure Data Lake Storage Gen2 or Microsoft OneLake via Azure Synapse Link for Dataverse; active Databricks SQL Warehouse (Serverless or Pro); Fabric or Power BI Pro/PPU workspace licensing.
- 🔹 Effort level: Medium. Configuration requires workspace or semantic model connection updates, M-query refactoring, and gateway driver validation.
- 🔹 Outcome: Substantial reduction in DirectQuery execution times on high-cardinality D365 F&O ledger and inventory tables without maintaining complex composite partition cache pipelines.
How it works
Enterprise analytics architectures for D365 F&O frequently export core transactional tables to Azure Data Lake Storage Gen2 or OneLake using Azure Synapse Link for Dataverse. Within Azure Databricks, data engineers curate these raw Delta tables into dimensional star schemas managed under Unity Catalog. When business analysts query these curated models using Power BI DirectQuery, the client application sends DAX queries that are translated into Databricks SQL. Historically, the transmission of this query result back to the Power BI mashup engine relied on standard ODBC or Thrift protocols.
Standard ODBC drivers enforce a row-based format. Even though Databricks executes queries in a columnar Apache Spark/Arrow format and Power BI consumes data into the columnar VertiPaq/Mashup engine, ODBC forces the server to serialize Arrow vectors into row-by-row tuples. Once the network packets arrive at the Power BI gateway or mashup engine, the data must be deserialized back into columnar arrays. This double serialization wastes compute cycles, increases memory pressure, and introduces high visual latency on enterprise dashboards.
The transition to ADBC is generally available for Power BI and Microsoft Fabric. The migration path is non-disruptive and opt-in: existing connections across semantic models, dataflows, and paginated reports continue operating on ODBC drivers until upgraded per connection or data source.
| Capability / Behavior | Legacy Databricks ODBC | Databricks ADBC (GA) |
|---|---|---|
| Data Wire Protocol | Row-oriented Thrift / ODBC serialization | Native Apache Arrow columnar streaming |
| Client-side CPU Overhead | High (row-to-column deserialization) | Minimal (direct zero-copy buffer loading) |
| DirectQuery Latency | High for >100k rows (gateway bottleneck) | Substantially lower (columnar streaming) |
| Memory Footprint | High memory allocation on gateway/mashup | Optimized vectorized memory buffers |
| Rollout Strategy | Default legacy driver | Opt-in per connection or data source |
Step-by-step solution
Transitioning your D365 F&O DirectQuery semantic models to the new ADBC connector requires coordinated updates across Dataverse synchronization, Databricks Unity Catalog, and Power BI semantic models.
Phase 1: Configure Lakehouse Data Ingestion
- Validate Azure Synapse Link for Dataverse tables: In the Power Apps maker portal (make.powerapps.com), navigate to Azure Synapse Link and ensure core D365 F&O entities—specifically
GeneralJournalAccountEntry,GeneralJournalEntry,MainAccount, andDimensionAttributeValueCombination—are actively replicating in Delta format with row version change tracking enabled. - Curate views in Databricks Unity Catalog: In your Azure Databricks workspace, create gold dimensional views exposing these D365 F&O Delta tables. Avoid complex subqueries in views intended for DirectQuery; instead, materialize common dimensional joins into governed Delta tables with automated Liquid Clustering or Z-Ordering applied to
LedgerDimensionandPostingDate.
Phase 2: Enable the ADBC Connector in Power BI
- Update Power BI Desktop and local mashup runtimes: Download the latest Power BI Desktop release. The ADBC driver binaries for Databricks are packaged natively inside the modern Mashup container, eliminating separate third-party driver installations on authoring workstations.
- Configure the connection string in Power Query: Open Power BI Desktop, open Transform Data > Data Source Settings, and inspect your Databricks connection. If authoring an M query manually, select the native
Databricks.Catalogsconnector. Specify the catalog and database parameters:
let
Source = Databricks.Catalogs(
"adb-1234567890123456.7.azuredatabricks.net",
"/sql/1.0/endpoints/a1b2c3d4e5f6g7h8",
[Catalog = "d365_gold", Database = "finance"]
),
FinanceView = Source{[Name="d365_gold"]}[Data]{[Name="finance"]}[Data]{[Name="Fact_GeneralLedger"]}[Data]
in
FinanceViewmodel.tmdl so that CI/CD deployments maintain consistent data source definitions during promotion across development, test, and production workspaces.- Configure cloud connections in Microsoft Fabric: In the Power BI service or Fabric workspace, open semantic model settings, expand Gateway and cloud connections, and ensure the cloud connection mapped to your Databricks SQL endpoint is properly configured.
- Update On-Premises or VNet Data Gateway: If your semantic models query Databricks through a Microsoft On-Premises Data Gateway or Virtual Network (VNet) Data Gateway, ensure the gateway is updated to the latest monthly release. Older gateway builds will silently fall back to ODBC drivers when evaluating Databricks queries.
Worked example
Contoso Global Manufacturing runs Dynamics 365 Finance across 18 legal entities. Their corporate reporting team built an executive General Ledger analysis report consuming 42 million transaction rows from GeneralJournalAccountEntry joined to dimension combinations and financial period calendars. The report runs in DirectQuery mode to provide near-real-time visibility during month-end closing.
Under the legacy Databricks ODBC driver, the primary matrix visual—displaying balance sheet accounts broken down by department, business unit, and fiscal period—suffered from an average render latency of 14.8 seconds. During month-end close when 50 concurrent financial controllers accessed the dashboard, the queries regularly exceeded the DirectQuery gateway timeout threshold (120 seconds), generating visual errors.
Contoso implemented the Databricks ADBC driver in their production Fabric workspace and pointed their semantic model to a Databricks SQL Serverless warehouse. Because ADBC streams data as raw Apache Arrow record batches, network deserialization time dropped drastically. Visual render time on the complex GL matrix fell from 14.8 seconds to 3.2 seconds. Gateway CPU utilization during concurrency peaks decreased by 58%, and Power BI mashup engine memory consumption was reduced by 62%.
// Optimized M Query using Databricks ADBC endpoint for D365 GL Entries
let
ServerEndpoint = "contoso-lakehouse.azuredatabricks.net",
HttpPath = "/sql/1.0/endpoints/3f82a912bcde4567",
Source = Databricks.Catalogs(
ServerEndpoint,
HttpPath,
[
Catalog = "contoso_lakehouse",
Database = "d365_general_ledger",
EnableADBC = true
]
),
GLTable = Source{[Name="contoso_lakehouse"]}[Data]
{[Name="d365_general_ledger"]}[Data]
{[Name="v_GeneralJournalAccountEntryExpanded"]}[Data],
FilteredRows = Table.SelectRows(GLTable, each [FiscalYear] >= 2024)
in
FilteredRows
Automation and AI opportunities
Upgrading your data access layer to ADBC establishes a high-performance foundation for automated modeling and Copilot integrations.
Model Authoring Automation with MCP Servers
Semantic model engineers can leverage the Power BI Authoring MCP Server (hosted at https://api.fabric.microsoft.com/v1/mcp/powerbi/authoring or locally via stdio) to automate the deployment of ADBC connection strings, DAX measures, and calculation groups. Using Model Context Protocol (MCP) clients such as Visual Studio Code, AI agents can inspect TMDL metadata within your Power BI Project (PBIP) repositories, refactor connection parameters to enable ADBC, and validate model schema conformance without opening Power BI Desktop.
Grounding AI with Fabric IQ
Once your DirectQuery model is running on ADBC with sub-second backend response times, you can ground conversational assistants using Fabric IQ. The Fabric IQ MCP endpoint (https://fabriciq.svc.cloud.microsoft/v1/mcp/FabricIQ) enables Microsoft Copilot Studio agents and Microsoft 365 Copilot to answer natural language financial questions grounded in your governed D365 F&O semantic models while honoring underlying row-level security (RLS) and object-level security (OLS).
Common pitfalls and troubleshooting
- Query folding failure in custom M functions: Inserting non-foldable M transformations (such as
Table.Bufferor custom text parsers) breaks DirectQuery folding. When folding breaks, Power BI attempts to fall back on in-memory evaluation, which forces unexpected data conversions. Fix: Audit query steps using View Native Query; push all transformations, splits, and calculations into the Databricks SQL view. - Gateway fallback to legacy ODBC: Running an out-of-date On-Premises Data Gateway causes the runtime to silently downgrade ADBC connections to legacy ODBC. Fix: Export the gateway logs from the On-premises data gateway app under Diagnostics > Export logs and inspect the mashup engine traces to confirm the modern driver initialized.
- Databricks SQL concurrency queuing: DirectQuery visual rendering triggers multiple parallel queries against Databricks. If the target SQL Warehouse has small cluster scaling limits, queries queue during peak dashboard loading. Fix: Enable auto-scaling on the Databricks SQL Warehouse (minimum 2, maximum 10 clusters) and verify serverless execution is enabled.
- Workspace permission mismatches: Users cannot view or build reports against the upgraded semantic model. Fix: Ensure report creators possess at least the Contributor role in the workspace hosting the semantic model to obtain automatic Build permissions, or grant explicit Build permissions on the model if authoring reports across workspaces.
- Precision truncation on 128-bit decimals: D365 F&O amounts stored as high-precision decimals can trigger casting discrepancies between Spark DecimalType and Power BI fixed currency types. Fix: Explicitly cast D365 currency values to
DECIMAL(18,4)in the Unity Catalog gold views to ensure complete compatibility with Apache Arrow wire specifications.
Best-practice checklist
- ✅ Enable Azure Synapse Link for Dataverse with Delta Lake format and row version change tracking enabled.
- ✅ Cluster or partition Databricks Delta tables on
LedgerDimension,DataAreaId, andPostingDate. - ✅ Ensure all visuals on DirectQuery report pages fold back to single SQL queries against Databricks.
- ✅ Validate semantic models with ADBC in staging environments prior to production rollout.
- ✅ Keep On-Premises or VNet Gateways updated to ensure the modern Mashup container with Arrow support is active.
- ✅ Implement Fabric workspace roles (Admin, Member, Contributor) systematically to govern Build permissions.
- ✅ Separate semantic model authoring (using Power BI Authoring MCP server) from consumption (Fabric IQ).
- ✅ Configure Databricks SQL Serverless warehouses to avoid cold-start latencies on interactive dashboards.
FAQ
Does opting into the Databricks ADBC connector invalidate existing DAX measures?
No. The ADBC driver operates at the transport and connectivity layer between the Power Query engine and Databricks. Semantic model definitions, relationships, DAX measures, calculation groups, and row-level security (RLS) remain unchanged.
Can I use the ADBC connector with Power BI Import mode, or is it DirectQuery only?
The ADBC connector accelerates both Import and DirectQuery modes. In Import mode, scheduled refreshes ingest data significantly faster due to the native Arrow memory transfer. However, the performance benefits are most visible in DirectQuery mode, where every user interaction initiates a live data roundtrip.
What workspace role is required to modify semantic model connections to ADBC?
You must have the Admin, Member, or Contributor role in the Power BI or Fabric workspace where the semantic model resides. Users with the Viewer role cannot alter connection settings, edit credentials, or change driver properties.
Key takeaways
- 🎯 The Generally Available Databricks ADBC connector replaces row-based ODBC translation with native, end-to-end Apache Arrow columnar streaming.
- 🎯 DirectQuery models over high-volume D365 F&O tables experience substantial latency reductions, preventing visual timeouts during peak close periods.
- 🎯 The upgrade is non-breaking and applied per connection, making it safe to pilot in targeted finance models.
- 🎯 Pairing ADBC semantic models with Fabric IQ and Power BI Authoring MCP servers establishes an enterprise-ready foundation for AI agent integration.
Further reading
- Databricks ADBC connector for Power BI and Microsoft Fabric (Generally Available)
- Overview of the MCP Servers - Power BI
- Roles in workspaces in Power BI
- Tutorial: Get Started Creating in the Power BI Service
Note: written with AI assistance and reviewed by Gaurav Jadaun. Always validate in a sandbox environment before applying to production.
Comments
Post a Comment