data-en

Why a D365 Finance Analytics Project Is Harder Than Your BI Team Expects

Alex at FitGap

24 Mar 2026 — 8 min read

Behind every ERP dashboard lies a complex transactional data model that BI teams must first decode.

And What to Do About It

There is a moment that happens in almost every Dynamics 365 Finance analytics project.

The BI team has assessed the scope. The timeline looks reasonable. The tools are chosen. Someone says "we just need to connect to the database and build the model."

Three months later, the dashboards aren't live. The data doesn't match the ERP. Numbers that look correct turn out to be wrong in specific scenarios. Fields that appear on screen don't exist in any table. The data model that was supposed to take a sprint has consumed the entire project.

This is not a failure of skill or effort. It is a failure of expectations — specifically, the expectation that an ERP database behaves like a conventional data source.

It doesn't.

This article explains why — and what to do about it.

The Core Problem: ERP Databases Are Not Designed for Analytics

Relational databases built for transactional processing and databases designed for analytics have fundamentally different structures.

A data warehouse is designed for queries. Tables are wide, denormalized, and optimized for aggregation. A single business event is typically a single row.

An ERP database is designed for transactions. It is normalized for integrity, not for readability. A single business event — a vendor invoice, a customer payment, a journal entry — is distributed across multiple tables, each capturing a different aspect of the transaction.

D365 Finance is no exception.

Understanding this gap is the starting point for any realistic analytics project scope.

1. A Single Business Event Lives Across Many Tables

When a vendor invoice is posted in D365 Finance, it doesn't create one record. It creates entries across multiple tables simultaneously — each serving a different purpose in the system's internal logic.

The vendor transaction itself is recorded in VendTrans, which represents the supplier-side entry and its lifecycle.
The accounting impact of that transaction is then reflected in the posted general ledger structures — GeneralJournalEntry (voucher level) and GeneralJournalAccountEntry (line level).
Other elements, such as taxes and financial dimensions, are not stored directly on a single record but are handled through separate tables and dimension reference structures.

An analytics team that queries only one of these tables to report on vendor invoices will get a partial picture at best, and subtly wrong numbers at worst. The join logic required to reconstruct a complete picture of a single business event is non-trivial — and it varies depending on the transaction type.

This is not a D365-specific problem. It is a fundamental characteristic of ERP data architecture. But D365 Finance's data model is particularly complex, with hundreds of tables involved in the financial transaction flow alone.

The practical implication: the first step in any D365 analytics project should be a data model mapping exercise — not a sprint to build dashboards.

→ For context on how subledgers relate to the general ledger in D365 Finance, see Ledger vs Subledger in D365 Finance. https://www.fitgapfinance.com/ledger-vs-subledger-d365-finance/

2. There Is No Such Thing as a Generic D365 Data Model

This is the most underestimated complexity in D365 Finance analytics projects — and the one most likely to invalidate assumptions made during scoping.

The D365 Finance data model is not fixed. It changes shape depending on how the system was configured.

Financial dimensions are the clearest example. An organization that uses three financial dimensions — business unit, department, and cost center — produces a fundamentally different ledger data structure than one that uses six. The dimension combination is stored as a foreign key reference to a dimension attribute table, not as separate columns. Queries that work correctly for one client's instance may return incorrect or incomplete results for another with a different dimension configuration.

Inventory configuration produces similar variation. Whether items are tracked as stocked or non-stocked, whether catch-weight is enabled, whether multiple warehouses are active — each of these choices changes which tables are populated and how.

Project accounting configuration creates its own data model variation. Organizations using project modules populate tables that simply don't exist in meaningful form for organizations that don't.

The implication for analytics projects is significant: data models, queries, and reports built on one D365 instance cannot be assumed to transfer to another. There is no universal D365 Finance data model to design against. The model must be reverse-engineered from the specific configured instance.

This is why a greenfield analytics architecture — built theoretically before engaging with the actual system — almost always requires rework.

3. What You See on Screen Is Not Always What Is Stored in the Database

This is the insight that most consistently surprises analytics teams encountering D365 Finance for the first time.

Many values that appear on screen are computed dynamically at display time. They are not stored in any table. They exist only as the result of business logic the application layer executes when rendering the interface.

Common examples in D365 Finance include certain net amount calculations on transaction lines, aging bucket assignments in accounts receivable and payable, some tax-derived fields, calculated balance fields on vendor and customer summaries, and certain fields on financial statements rendered through the application.

An analytics team that identifies a field in the user interface and assumes it corresponds to a database column will sometimes be right — and sometimes discover, after significant effort, that the field they need to reproduce requires reverse-engineering application logic that was never designed to be queried directly.

The practical approach is to validate every field needed in the analytics output against the actual database schema before committing to a reporting design. This step is rarely included in standard BI project methodologies — and its absence is a common source of schedule overruns.

4. Upstream Processes Determine Downstream Data Quality

Analytics teams often inherit data quality problems they didn't create and can't directly fix.

In D365 Finance, the quality of analytics output depends almost entirely on the discipline of the operational processes that produce the data. This is not unique to ERP — but ERP systems amplify the problem because their data is used for so many downstream purposes simultaneously.

The most common manifestations are: inconsistent posting behavior driven by user exceptions and manual overrides, incomplete dimension coding on transactions that makes cost allocation reporting unreliable, GR/IR balances that don't clear because three-way match processes aren't being followed, and intercompany transactions that are posted differently across entities.

These issues don't appear in the data model. They appear in the data.

An analytics architecture built on unstable operational processes produces reports that are technically correct but practically unreliable. Finance teams lose confidence in dashboards not because the dashboards are wrong, but because the underlying transactions reflect inconsistent practices.

The resolution is not a data engineering problem. It is a process governance problem — and it sits upstream of anything the analytics team can solve on their own.

→ For a detailed treatment of how upstream process discipline affects reporting quality, see The Hidden Cost of Faster Reporting in ERP. https://www.fitgapfinance.com/the-hidden-cost-of-early-reporting-in-erp/

5. Deleted Records Don't Always Mean What You Think

Data lifecycle management in D365 Finance is more complex than most analytics teams anticipate.

D365 does not handle all deletions the same way. Some transaction types are reversed rather than deleted — meaning a cancelled transaction creates a new offsetting entry rather than removing the original. Some records are soft-deleted through status flags and remain in the table. Others are hard-deleted and genuinely absent from the database.

An analytics model that doesn't account for this produces subtly wrong numbers. Counts of transactions include reversals. Balances include entries that have been effectively cancelled. Trend lines reflect noise from correction entries that aren't operationally meaningful.

The reversal logic in particular requires careful handling. A payment that was posted and then reversed creates two records — the original and the reversal — each with equal and opposite amounts. Including both in a sum produces the correct net result. Including only one produces an error. But the flag that identifies a reversal is not always obvious, and it varies by transaction type.

This is an area where engaging someone with deep D365 data model knowledge early in the project pays dividends. The patterns are learnable — but they are not documented in a way that makes them easy to discover independently.

6. Financial Dimensions Are Not Columns

For analytics teams expecting a clean star schema, the financial dimension structure in D365 Finance is one of the most disorienting discoveries.

Financial dimensions are not stored as separate columns on transaction tables. They are stored as a composite foreign key — typically a LedgerDimension or DefaultDimension value — that references a separate dimension attribute table where the actual dimension values are stored.

Retrieving the business unit, department, and cost center values for a set of transactions requires joining through this dimension structure, not simply selecting columns from the transaction table. The join logic is not straightforward and the performance implications of doing it incorrectly at scale are significant.

This architecture also means that adding or removing a financial dimension in the ERP configuration changes the dimension lookup logic without changing the transaction table itself. An analytics model that was built to extract three dimensions breaks silently — returning incomplete data rather than an error — when a fourth dimension is added in production.

Building resilient dimension extraction logic requires understanding this architecture explicitly, not discovering it during testing.

What To Do About It

These challenges are not reasons to avoid D365 Finance analytics projects. They are reasons to scope them correctly.

A few practical principles that apply across most implementations:

Invest in data model discovery before design. Before any dashboard or data model is designed, spend time mapping the specific tables relevant to each reporting use case in the configured instance. This is not optional groundwork — it is the actual work.

Include someone with D365 data model depth from day one. BI architects and data engineers are skilled professionals, but D365 Finance data model knowledge is specialized. A project that relies entirely on generic data engineering skills applied to ERP data will hit every one of the problems described above. Someone who has already navigated these patterns reduces the learning tax significantly.

Treat data quality as a process governance question, not a data engineering question. If the upstream operational processes are inconsistent, no amount of transformation logic fully compensates. The analytics team needs a clear escalation path to raise data quality issues as process issues — not absorb them as technical problems to work around.

Design for configuration variability. Assume the instance will change after the analytics model is built — because it will. Dimension additions, new legal entities, module expansions, and configuration changes are part of normal ERP evolution. An analytics architecture that assumes a static data model becomes fragile quickly.

Validate computed fields early. Before committing to any report design that includes a calculated value from the ERP interface, confirm whether that value is stored in the database or computed at display time. Doing this at the start of design is a one-hour task. Discovering it after a report has been built around a non-existent column is a week of rework.

Final Thought

The moment of discovery — when the BI team realizes the project is significantly more complex than scoped — is avoidable.

Not by doing less. By understanding what ERP analytics actually involves before the project starts.

D365 Finance contains extraordinarily rich data. The organizations that extract genuine analytical value from it are not the ones with the best BI tools. They are the ones that understood the data model, governed the upstream processes, and scoped the project against reality rather than assumption.