D365 F&O Integration

How Konsolidat extracts data from Dynamics 365 Finance & Operations via Airbyte and OData.

Azure AD App Registration

Step-by-Step

1. Go to Azure Portal → App Registrations
2. Click New registration
   • Name: Konsolidat Airbyte
   • Supported account types: Single tenant
   • Redirect URI: Leave blank
3. Note the Application (client) ID and Directory (tenant) ID
4. Go to Certificates & secrets → New client secret
   • Description: Konsolidat production
   • Expiry: 24 months
   • Copy the secret value immediately (shown only once)
5. Go to API permissions → Add a permission
   • Select Dynamics ERP (Dynamics 365 Finance and Operations)
   • Choose Application permissions → check the access role (e.g. Connector.FullAccess / AX.FullAccess as exposed by your tenant)
   • Click Grant admin consent

The connector authenticates with the OAuth2 client-credentials flow (grant_type=client_credentials), so the app registration must be granted application permissions — there is no signed-in user. The token is requested from https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token with the scope {environment_url}/.default, which means the service principal must also be registered as an integrating application inside D365.

6. In D365, go to System administration → Setup → Microsoft Entra ID applications (formerly Azure Active Directory applications) and register the Client ID with a user account that has the required data-access permissions. Without this step the token will be issued but OData calls return 401.

Required Values

Value	Where to Find	Used By
Tenant ID	App registration → Overview	Airbyte source config
Client ID	App registration → Overview	Airbyte source config
Client Secret	Certificates & secrets	Airbyte source config
Environment URL	D365 → System administration	Airbyte source config

The environment URL typically looks like: https://yourorg.operations.dynamics.com

OData Entities

The connector ships 14 D365 OData streams. All use cross-company=true by default to pull data across every legal entity in a single request (this can be disabled per-source via the cross_company setting).

General Ledger

The GL streams read the BI entity views (...BiEntities), which expose D365's internal SourceKey (RecId) as a monotonic surrogate primary key. Both sync incrementally on SourceKey.

OData Entity	Purpose	Primary Key / Cursor	Key Fields
GeneralJournalAccountEntryBiEntities	GL line items (postings)	SourceKey (incremental)	SourceKey, GeneralJournalEntry, LedgerAccount, AccountingCurrencyAmount, IsCredit, PostingType, AccountingDate
GeneralJournalEntryBiEntities	Journal headers	SourceKey (incremental)	SourceKey, JournalNumber, AccountingDate, DocumentNumber, PostingLayer, FiscalCalendarYear, FiscalCalendarPeriod

Chart of Accounts

OData Entity	Purpose	Primary Key	Key Fields
MainAccounts	Account master	MainAccountId	MainAccountId, Name, MainAccountType, MainAccountCategory, ChartOfAccounts
MainAccountCategories	Account category mapping	ReferenceId	ReferenceId, MainAccountCategory, Description, MainAccountType

Organization

OData Entity	Purpose	Primary Key	Key Fields
LegalEntities	Company master	LegalEntityId	LegalEntityId, Name, PartyNumber, AddressCountryRegionId
Ledgers	Ledger / currency configuration per legal entity	LegalEntityId	LegalEntityId, AccountingCurrency, ReportingCurrency, Name, ChartOfAccountsId

Fiscal Calendar

OData Entity	Purpose	Primary Key	Key Fields
FiscalCalendarYears	Fiscal year definitions	(none)	Calendar, FiscalYear, Description, StartDate, EndDate

Financial Dimensions

OData Entity	Purpose	Primary Key	Key Fields
DimensionAttributes	Dimension definitions	DimensionName	DimensionName, UseValuesFrom, ReportColumnName
FinancialDimensionValues	Dimension value master	(none)	FinancialDimension, DimensionValue, Description, IsSuspended, ActiveFrom, ActiveTo

Exchange Rates

OData Entity	Purpose	Primary Key / Cursor	Key Fields
ExchangeRates	Currency rate data	cursor StartDate (incremental)	FromCurrency, ToCurrency, StartDate, EndDate, Rate, ConversionFactor, RateTypeName
ExchangeRateTypes	Rate type definitions	Name	Name, Description

Budget

OData Entity	Purpose	Primary Key / Cursor	Key Fields
BudgetRegisterEntries	Budget register entries	EntryNumber, cursor Date (incremental)	EntryNumber, BudgetModelId, BudgetCode, Status, Date, AccountingCurrencyAmount, DimensionDisplayValue

Consolidation

OData Entity	Purpose	Primary Key	Key Fields
ConsolidateAccountGroups	Group mapping	ConsolidationAccountGroup	ConsolidationAccountGroup, ConsolidationAccountGroupName

Trial Balance Snapshot

OData Entity	Purpose	Primary Key	Key Fields
TrialBalanceFiscalYearSnapshots	Pre-built TB snapshot	(none)	LedgerName, DimensionValue1, YearName, PeriodStartDate, OpeningBalance, AmountDebit, AmountCredit, EndingBalance

Airbyte Configuration

Source: Dynamics 365 Finance & Operations

Setting	Value
Source type	Dynamics 365 F&O (OData)
Tenant ID	From Azure AD app registration
Client ID	From Azure AD app registration
Client Secret	From Azure AD app registration
Environment URL	https://yourorg.operations.dynamics.com

Destination: ClickHouse

Setting	Value
Host	host.docker.internal (Docker) or ClickHouse hostname
Port	8123
Database	epm_bronze
User	default (or dedicated write user)
Password	From .env

Stream Configuration

Enable all 14 streams listed above. For each stream:

• Sync mode: Full Refresh (overwrite) for reference data; Incremental for the four streams that declare a cursor (GeneralJournalAccountEntryBiEntities and GeneralJournalEntryBiEntities on SourceKey, ExchangeRates on StartDate, BudgetRegisterEntries on Date)
• Cursor field: as declared per stream above (not all streams support incremental)
• Primary key: as declared per stream above — for example SourceKey for the GL BI entities, LegalEntityId for LegalEntities and Ledgers; several reference streams declare no primary key

Cross-Company Setting

By default all entity requests include cross-company=true in the OData query to retrieve data across all legal entities in the D365 instance. This is controlled by the cross_company source setting and can be disabled to restrict a sync to the service principal's default company.

Entity-to-Model Mapping

How D365 entities flow through the dbt layers:

OData Entity	→ Bronze	→ Silver	→ Gold
GeneralJournalAccountEntryBiEntities	bronze_general_journal_account_entry_bi_entities	silver_gl_entries	gold_trial_balance
GeneralJournalEntryBiEntities	bronze_general_journal_entry_bi_entities	silver_gl_entries (joined)	—
MainAccounts	bronze_main_accounts	silver_main_accounts	—
MainAccountCategories	bronze_main_account_categories	silver_main_accounts (joined)	—
LegalEntities	bronze_legal_entities	silver_legal_entities	—
Ledgers	bronze_ledgers	silver_legal_entities (joined)	—
FiscalCalendarYears	bronze_fiscal_calendar_years	silver_fiscal_periods	gold_period_hierarchy
ExchangeRates	bronze_exchange_rates	silver_exchange_rates	gold_consolidated_trial_balance
BudgetRegisterEntries	bronze_budget_register_entries	silver_budget_entries	gold_spread_budget

Sync Schedule

Approach	Frequency	Best For
Manual (via task pane or API)	On-demand	Development, ad-hoc
Airbyte scheduler	Daily at off-peak hours	Standard production
Event-driven	On D365 posting	Real-time requirements

Troubleshooting

Issue	Cause	Fix
401 Unauthorized from D365	Expired client secret, or the service principal is not registered in System administration → Setup → Microsoft Entra ID applications	Rotate the secret; verify the Client ID is registered as an integrating app with a permitted access role in D365
Empty sync results	cross-company disabled (only the default company is returned)	Re-enable the cross_company source setting
Missing entities	Entity not enabled in D365	Contact D365 admin to expose the data entity
Timeout on large syncs	Too many records in one batch	Increase Airbyte timeout, use incremental sync
Rate limiting (429)	D365 throttling	Reduce batch size, add retry logic

Next Steps

• Setup Guide — Full installation including Airbyte
• Configuration Reference — EPM Settings fields
• Bronze Models — What the raw data looks like