All posts
erp6 min read

Dynamics 365 Financial Data Extraction for Due Diligence

Dynamics 365 data extraction for due diligence covers F&O and Business Central. Learn how TS teams extract and normalize financial data from Dynamics.

Datapack Team

Dynamics 365 Financial Data Extraction for Due Diligence

Microsoft Dynamics 365 encompasses two distinct financial products: Dynamics 365 Finance & Operations (F&O), used by larger organizations, and Dynamics 365 Business Central, common among mid-market companies. Both appear regularly in due diligence engagements, particularly in manufacturing, distribution, and professional services.

For TS teams, Dynamics presents a middle ground between the complexity of SAP and the simplicity of QuickBooks. The data is well-structured but requires understanding the system's specific architecture and extraction methods. Teams that develop Dynamics-specific extraction competency handle these engagements more efficiently and produce more reliable analysis.

Dynamics 365 Finance & Operations

F&O is Microsoft's enterprise-grade ERP system. Its financial module stores data in a structured relational database with clear table relationships.

Data Architecture

Legal entities. F&O uses "legal entities" as its primary organizational unit, equivalent to company codes in SAP. Each legal entity has its own chart of accounts, fiscal calendar, and currency settings.

Chart of accounts structure. F&O uses a shared chart of accounts model where a single chart of accounts can be shared across multiple legal entities. This simplifies extraction because account numbers and descriptions are consistent across entities. However, not all accounts may be active in all entities.

Financial dimensions. F&O uses "financial dimensions" (Department, Cost Center, Business Unit, Project, and custom dimensions) for segment reporting. These dimensions are attached to transactions and provide the analytical detail that TS teams use for revenue and expense analysis.

Fiscal calendars. F&O supports multiple fiscal calendars with configurable period types. A standard implementation uses 12 monthly periods plus a closing period. Understanding the fiscal calendar configuration is essential for accurate period-based extraction.

Extraction Methods

Data entities and OData. F&O provides data entities that expose financial data through standardized interfaces. The General Journal Entry data entity, Trial Balance data entity, and Chart of Accounts data entity provide the core financial data needed for due diligence.

Management Reporter / Financial Reporting. F&O's financial reporting tool generates trial balances, income statements, and balance sheets that can be exported to Excel. These are useful for quick overviews but may not provide the GL-level detail needed for adjustment analysis.

Direct database access. For targets hosted on-premise, direct SQL queries against the underlying database provide the most flexible extraction method. Key tables include GeneralJournalEntry, GeneralJournalAccountEntry, MainAccount, and LedgerJournalTable.

Azure Data Lake. Targets using F&O in the cloud may have data replicated to Azure Data Lake, which can be queried with standard SQL tools. This method handles large data volumes well and avoids performance concerns about querying the production system directly.

Essential Exports

TS teams working with F&O targets need:

  1. Trial balance by period and legal entity. Generated from the Trial Balance data entity or Financial Reporting. Include all posting layers and all periods including closing periods.
  2. General ledger detail. Transaction-level data from GeneralJournalEntry and GeneralJournalAccountEntry tables. Include posting date, voucher number, account, amount, description, financial dimensions, and posting layer.
  3. Chart of accounts. MainAccount table with account numbers, names, main account types, and main account categories.
  4. Sub-ledger data. AR aging from CustTrans, AP aging from VendTrans, and fixed asset data from AssetBook and AssetTrans.

Dynamics 365 Business Central

Business Central is the more common Dynamics product in mid-market due diligence. It is simpler than F&O but has its own extraction requirements.

Data Architecture

Companies. Business Central uses "companies" within a tenant. Each company has its own chart of accounts and data. Multi-entity targets may have multiple companies within a single Business Central tenant.

G/L Account structure. Business Central uses a flat or hierarchical chart of accounts with account numbers and names. Account categories and subcategories provide additional classification that supports chart of accounts mapping.

Dimensions. Business Central supports two "global dimensions" and up to six "shortcut dimensions" for transaction tagging. These are simpler than F&O's financial dimensions but serve a similar analytical purpose.

Extraction Methods

Page exports. Business Central pages (Chart of Accounts, General Ledger Entries, Trial Balance) can be exported directly to Excel or CSV. This is the simplest method and works for most mid-market engagements.

API access. Business Central provides REST APIs for programmatic data access. The APIs cover most financial entities: GL entries, accounts, customers, vendors, and items.

Configuration packages. Business Central's RapidStart configuration packages can export data from multiple tables in a structured format. This is useful for comprehensive extractions that cover the full chart of accounts, transaction history, and master data.

Essential Exports

  1. Trial balance. The Trial Balance page filtered by period. Export to Excel for each fiscal year in scope.
  2. General Ledger entries. The General Ledger Entries page with all fields visible. Filter by posting date range.
  3. Chart of accounts. The Chart of Accounts page with account number, name, account type, account category, and account subcategory.
  4. Sub-ledger data. Customer Ledger Entries, Vendor Ledger Entries, and FA Ledger Entries for sub-ledger analysis.

Common Challenges

Data volume in F&O. Large F&O implementations may have millions of GL entries. Extracting all detail can time out or produce unmanageable file sizes. Filtering by period or account range and extracting in batches is necessary.

Dimension inconsistency. Targets may use financial dimensions inconsistently over time. A dimension that was rigorously applied in recent periods may be missing from older transactions. This affects segment-level analysis across the full analysis period.

Posting layers. F&O supports multiple posting layers (Current, Operations, Tax). The TS team must specify which posting layer to include. Extracting the wrong layer produces a trial balance that does not reconcile to reported financials.

Business Central version differences. Older Business Central versions (NAV) have different data structures and extraction methods. Confirming the exact version before preparing the data request avoids specification errors.

Standardizing Dynamics Extraction

Teams that encounter Dynamics targets regularly should maintain version-specific data request templates. The template should specify:

  • The Dynamics product and version (F&O or Business Central, with version number)
  • Required legal entities or companies
  • Fiscal periods in scope
  • Currency and posting layer specifications
  • Financial dimensions to include
  • Preferred export format and encoding

This standardization reduces the back-and-forth with the target's IT team and ensures consistent data quality across engagements. Combined with automated ERP data extraction tools that handle Dynamics-specific data structures, the path from raw Dynamics data to analysis-ready datasets becomes a repeatable, efficient process.

For teams focused on scaling deal throughput, building competency across all major ERP systems, including Dynamics, eliminates a common source of delays and ensures that the ERP system used by the target does not determine the engagement timeline.