Dynamics 365 Finance Keeps Every Dimension Behind One Integer. The Semantic Model Decodes It.

Query Dynamics 365 Finance data with AI against a replicated general ledger and the first question Finance asks, posted amount by department, comes back as a tidy list of codes with amounts beside them. Some of the codes are departments. Some are cost centers. The posted line carries no dimension column at all, only one integer, and the readable string that integer points at is documented by Microsoft as not a key.

A controller asks what each department posted last quarter.

Point an AI agent at the landed schema and it finds the ledger in seconds. generaljournalaccountentry is the posted line. It joins to generaljournalentry for the accounting date, and its ledgerdimension column joins to dimensionattributevaluecombination, which has a displayvalue that looks like 606100-022-001. Split it on the hyphen, take the second segment, group, sum.

The query compiles. The breakdown comes back, one row per code, amounts in the right ballpark, and a department named A that the controller has never heard of.

Because under the account structure that governs some of those main accounts, A is a cost center.

Before you start

• Dynamics 365 Finance already landed in a lake. The tables leave through Azure Synapse Link for Dataverse, or Link to Fabric, into your own Azure Data Lake Storage in Common Data Model format, and a warehouse connector reads that container. Fivetran's Microsoft Dynamics 365 Finance and Operations connector is explicit that it is the second hop: "Instead of syncing your data from the Dynamics 365 Finance and Operations database, we sync from the Azure Data Lake Storage that contains your exported data." Microsoft's prerequisite for the first hop is "a finance and operations sandbox (Tier-2) or higher environment."
• A free trial exists, behind a tenant administrator. Microsoft's unified admin trials are "free subscription-based trial environments for finance and operations apps," and "a tenant administrator can get a trial license in the Microsoft 365 admin center." The gotcha: "Only tenant admins can create a trial (subscription-based) environment," it "lasts as long as the subscription is active," and admins get "a single extension." A personal email address has no route to it. Everything below runs against your own lake either way.
• Casing varies by extract tool. Microsoft's lake-side examples are lowercase, Fivetran offers "Fivetran naming" or "Source naming" at setup, and two renames apply everywhere: "ID fields from finance and operations tables are renamed to FnO_Id" and reserved words get "a trailing character. Ex. Level becomes Level_." SQL below uses lowercase, unbroken names; adjust to whatever your destination did.
• Select the ledger tables rather than syncing the schema. generaljournalaccountentry, generaljournalentry, ledger, mainaccount, dimensionattributevaluecombination, dimensionattributevaluegroupcombination, dimensionattributevaluegroup, dimensionattributelevelvalue, dimensionattributevalue, and dimensionattribute answer the question in this post. Not Business Central and not Dynamics 365 Sales; the three share a vendor and nothing below the UI.

The question

"What did each department post last quarter?"

Trial balance by dimension is the report every close runs on. Actual against budget by cost center, spend by business unit, one department against another. Finance has been pulling it out of Dynamics 365 Finance for as long as the company has run it, and the controller has last quarter's trial balance open in another window to compare.

What breaks

Here is the query, and it is the first thing anyone writes:

select split_part(davc.displayvalue, '-', 2)  as department,
       sum(gjae.accountingcurrencyamount)     as posted_amount
from   generaljournalaccountentry gjae
join   generaljournalentry gje
  on   gje.recid = gjae.generaljournalentry
join   dimensionattributevaluecombination davc
  on   davc.recid = gjae.ledgerdimension
where  gje.accountingdate >= date_trunc('quarter', current_date - interval '3 months')
  and  gje.accountingdate <  date_trunc('quarter', current_date)
group  by 1
order  by 2 desc;

Nothing about it is careless. The posted line has no department column, so the agent went looking for one. It found displayvalue on the combination table, saw strings like 606100-022, and did the obvious thing with a delimited string. The join on ledgerdimension is the right join. The date filter is on the header, where Microsoft puts it. The query returns one clean row per code.

The labels are wrong for some share of the rows, and nothing in the result says which share.

split_part and date_trunc are shared by Snowflake, Databricks, and PostgreSQL; a Synapse serverless reader needs the T-SQL equivalents. The mislabelling is the same in every dialect.

The same display string, two meanings. Microsoft's own example: under one account structure 145-A puts a department in the second segment; under another, a cost center. A split on the hyphen labels both the same.

Why it breaks

Microsoft states the storage model directly, in the developer article Ledger account combinations:

"To save a single segment in a new combination, insert at least one record into each of these four tables. For each additional segment that you enter, insert an additional record into the DimensionAttributeLevelValue table. Abstractly, these four tables are referred to as a Ledger Dimension. A Ledger Dimension is expressed as a foreign key that references the RecId value in the DimensionAttributeValueCombination table."

Read that as a schema and three things follow.

The posted line carries no values, only a surrogate. generaljournalaccountentry has, per its published Common Data Model definition, three amount columns, a mainaccount RecId, and ledgerdimension. That is the whole dimensional content of a posted line: one int64. Group by it and you have grouped by an opaque integer. Join it to dimensionattributevaluecombination and you have the combination table, which the same article describes as storing "a full multisegment account combination, together with some denormalized information about the combination. For example, it stores the concatenated segments as a single string, a foreign key reference to the account structure, and a foreign key to the main account."

The readable column is not a key, and Microsoft says what goes wrong if you treat it as one. From the section titled "Apparent duplicate combinations":

"The DisplayValue strings are stored on the records to improve performance for some scenarios, but they aren't used to uniquely identify the record."

"For example, an account structure has MainAccount-Department in one company and another account structure has MainAccount-CostCenter in a different company. In this scenario, the DisplayValue string of two combinations, one for each account structure, can appear as 145-A. For the first account structure, A represents a department in the first company. However, for the second account structure, it represents a cost center in the second company."

The segment order is set by whichever account structure governs that main account, and "financial dimensions can be different for each main account or group of accounts." Advanced rules add trailing segments only when their filter matches. And blank segments are displayed but not stored: the entry control shows "three segments in the ledger account combination, even if one of those segments is left blank," while in storage "one record exists for each segment that is entered. If a segment is empty, no values are stored for it." So position two is a department on rows under one structure, a cost center on rows under another, and something else again on rows where a rule fired. The query returns a tidy list of codes, and every one of the labels is wrong for some of the rows.

The columns that would fix it exist only in your tenant. Two sentences from the same article:

"In Microsoft Dynamics 365 finance and operations, the dimension framework expands to allow up to about 50 dimensions, due to SQL database limits on total column counts in tables."

"The DimensionAttributeValueCombination and DimensionAttributeValueSet tables contain 'unpivoted' columns and 50-100+ supporting indexes that were added in Dynamics 365 version 7.0. These columns store denormalized dimension values directly on the combination or set record, replacing the need for complex multitable joins when querying by dimension value."

The cap on dimensions is a cap on column count, because activating a dimension adds columns to the combination table. The activation troubleshooting page confirms it from the failure side. Change Data Capture on that table blocks activation because "this condition prevents the schema changes that dimension activation requires." Renaming a dimension without activating is blocked because "the old name still exists as a column in the dimension tables." And the CDC error names the column literally: "Column name 'SYSTEMGENERATEDATTRIBUTE<DimensionAttribute>' in table 'cdc.dbo_DIMENSIONATTRIBUTEVALUECOMBINATION_CT' is specified more than once."

The names of those columns are stored as data. dimensionattribute carries dimensionkeycolumnname and dimensionvaluecolumnname in its published definition, and Microsoft's own extension sample for exposing a dimension on an entity reads the column name out of that row at runtime: strFmt('SELECT TOP 1 T1.%1 ', dimensionAttribute.DimensionValueColumnName).

Nine published columns, and none of them is yours

The published schema of dimensionattributevaluecombination lists nine columns, counting the key: RecId, AccountStructure, DisplayValue, Hash, LedgerDimensionType, DataAreaForCreation, ImpliedDataAreaId, MainAccount, and MainAccountValue. One is readable. None is a dimension you defined.

Left, the nine columns Microsoft publishes for the combination table. Right, what a tenant's lake copy carries after activation: the same nine plus a pair per dimension, named after the dimension. The names on the right are examples; dimensionattribute holds the real ones.

The pair that is published, MainAccount and MainAccountValue, is the pattern. It exists for the one dimension every tenant has, because Microsoft's framework "lets the dimension framework treat a main account as a dimension." A tenant with BusinessUnit, Department, and CostCenter has a pair for each on its combination table; the published definition of that table has none of them; and a tenant that named its dimension "Dept" has a different column from one that named it "Department." An agent reading the published schema cannot know the columns exist. An agent reading the landed schema cannot know which business concept each one encodes without dimensionattribute.

So the wrong query does not error. It succeeds against displayvalue, which every tenant has, and returns a breakdown that is right for the rows that share the structure the reader had in mind.

What Dynamics 365 Finance did for you

Inside the application, nobody has ever split that string.

On the way in, the segmented entry control resolved it. The same article walks through it: the control shows the possible segments from the account structure, adds a third when the typed values match an advanced rule ("when you type a hyphen after the second segment, the control adds a third segment and receives the focus"), and then "the dimension framework saves the combination and then validates it, based on the constraints." The user typed 145-Q-AAA 111 and the platform knew position two was Customer and position three was LicensePlate because it read the structure while the user was typing. The string in the lake is the output of that resolution with the resolution removed.

On the way out, dimension sets summarised the ledger by dimension. From Financial dimension sets: "A dimension set is an ordered list of financial dimensions that you can use to summarize General ledger data in a user-defined way. A primary use of dimension sets is to define a trial balance." Balances "are summarized from the General ledger data to help improve performance when they're retrieved," a set can hold "up to eleven dimension attributes," and "display of the trial balance forces an update." So inside Dynamics 365 Finance, trial balance by Department never touched displayvalue and never walked four tables per row. Replicate the ledger and the combination table and you have the raw material the balances were summarised from, and none of the summarisation, the set definitions, or the structure that told the entry control what each segment meant.

And the application's own answer to "make dimension N a column" is to alter the table. That is what activation is: a schema change, blocked by CDC, blocked by a leftover column name, documented as such. Replication copies the table after the alteration and carries no record that the column was tenant-made. That is why the published schema and the landed schema disagree by exactly your dimension count.

This is the third Microsoft-adjacent ledger in this series and the third storage choice for the same idea. Business Central keeps two dimensions as physical columns on the ledger row and computes six more on read. Sage Intacct's dimensions don't land as tables at all. Dynamics 365 Finance keeps none on the row. One shared cause: the application resolved the structure, and replication carried the values without it.

The fix

None of this is a query problem. Changing split_part to a join on the tenant's column fixes one query for one person on one afternoon, and the next person to ask about spend starts from the same schema, finds the same readable string, and reaches the same wrong labels by the same correct route.

It belongs in the semantic model, where the join is declared once with its citation, the dimension is bound once to the column that decodes it, and every posted-amount question inherits both.

The joins go in first. Every one of them is published as a Relationship_* attribute in the child table's CDM definition, and not one is a constraint in the lake: Synapse Link writes Delta files, and Fivetran's schema note for this connector is one sentence, "All the tables have an id column, which represents a unique row ID." An introspection tool that infers joins by name will find dimensionattributevaluecombination referenced from a column called ledgerdimension only if it is told that a Ledger Dimension is a combination RecId, which is a sentence in Microsoft's documentation and not a property of the data.

# subject_areas/general_ledger/relationships.yaml
relationships:
  - from_table: generaljournalaccountentry
    from_column: ledgerdimension
    to_table: dimensionattributevaluecombination
    to_column: recid
    relationship: many_to_one
    confidence: confirmed
    review_state: approved
    description: >
      "A Ledger Dimension is expressed as a foreign key that references the
      RecId value in the DimensionAttributeValueCombination table." The
      posted line's main account and every financial dimension sit behind
      this one int64. No constraint exists in the lake; the join is declared
      because it is documented, not because it is discoverable.
    source: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/ledgeraccountcombinations

  - from_table: generaljournalaccountentry
    from_column: generaljournalentry
    to_table: generaljournalentry
    to_column: recid
    relationship: many_to_one
    confidence: confirmed
    review_state: approved
    description: >
      Posted line to its voucher header. Accounting date, ledger (legal
      entity), and posting layer live on the header rather than the line.

  - from_table: generaljournalentry
    from_column: ledger
    to_table: ledger
    to_column: recid
    relationship: many_to_one
    confidence: confirmed
    review_state: approved
    description: >
      "Each legal entity can have only one ledger." ledger.accountingcurrency
      is the currency of every accountingcurrencyamount under it.
    source: https://learn.microsoft.com/en-us/dynamics365/finance/general-ledger/ledger-subledger

  - from_table: dimensionattributevaluegroupcombination
    from_column: dimensionattributevaluecombination
    to_table: dimensionattributevaluecombination
    to_column: recid
    relationship: many_to_one
    confidence: confirmed
    review_state: approved
    description: >
      One row per structure used by the combination. "Two records are stored
      in the DimensionAttributeValueGroupCombination and
      DimensionAttributeValueGroup tables. Each record represents a structure
      that is used." A combination that matched an advanced rule has two or
      more; aggregating through this edge without constraining the dimension
      fans by structure count.

  - from_table: dimensionattributelevelvalue
    from_column: dimensionattributevaluegroup
    to_table: dimensionattributevaluegroup
    to_column: recid
    relationship: many_to_one
    confidence: confirmed
    review_state: approved
    description: >
      One row per non-blank segment. "If a segment is empty, no values are
      stored for it." A left join is required to keep postings that lack the
      dimension.

  - from_table: dimensionattributevalue
    from_column: dimensionattribute
    to_table: dimensionattribute
    to_column: recid
    relationship: many_to_one
    confidence: confirmed
    review_state: approved
    description: >
      dimensionattribute.name is the tenant's name for the dimension and the
      only place the meaning of a segment is recorded.

Then the part that carries the weight. The schema can't say which column is Department, so the model has to:

entities:
  - name: fo_financial_dimension
    description: >
      A financial dimension on a posted Dynamics 365 Finance ledger line. The
      line carries no dimension column; it carries ledgerdimension, a RecId
      into dimensionattributevaluecombination. Resolve either through the
      tenant's unpivoted column on that table (fast path, name held in
      dimensionattribute.dimensionvaluecolumnname) or through the documented
      four-table walk filtered on dimensionattribute.name (portable path).
      Never by splitting dimensionattributevaluecombination.displayvalue:
      Microsoft documents that the same string means a department under one
      account structure and a cost center under another.
    resolves_to:
      fast_path:
        table: dimensionattributevaluecombination
        value_column: "<dimensionattribute.dimensionvaluecolumnname for this dimension>"
        key_column: "<dimensionattribute.dimensionkeycolumnname for this dimension>"
      portable_path:
        table: dimensionattributelevelvalue
        value_column: displayvalue
        key_filter: "dimensionattribute.name = '<tenant dimension name>'"
    caveats:
      - >
        The fast-path columns exist only after the tenant activated the
        dimension and only in tenants that did. The published schema of
        dimensionattributevaluecombination lists nine columns and none of
        them. Confirm the pair is in the lake copy (model.json) before binding.
      - >
        Blank segments are not stored. Decide whether postings without this
        dimension appear under "(none)" or are dropped, and record it.
      - >
        A combination that matched an advanced rule has one
        dimensionattributevaluegroupcombination row per structure. The
        portable path must filter on dimensionattribute.name or it fans.
      - >
        The combination table has no data area ("SaveDataPerCompany = No").
        Resolve the legal entity through generaljournalentry.ledger, never
        through the dimension.
    source: https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/financial/ledgeraccountcombinations

And the metric binds to the entity rather than to the string, with the two filters the application applied silently as required:

metrics:
  - name: posted_amount_by_dimension
    calculation: >
      Sum of generaljournalaccountentry.accountingcurrencyamount grouped by
      one named financial dimension, resolved through fo_financial_dimension,
      for one ledger (one legal entity, one accounting currency) and one
      posting layer.
    requires_entity: fo_financial_dimension
    required_filters: [generaljournalentry.ledger, generaljournalentry.postinglayer]
    source_tables: [generaljournalaccountentry, generaljournalentry, ledger, dimensionattributevaluecombination]
    primary_table: generaljournalaccountentry
    other_names: [GL by department, posted by cost center, trial balance by dimension]
    citation: >
      Microsoft, Ledger account combinations, and Ledger, subledger overview
      ("up to 10 posting layers can be used to track the deltas for
      additional accounting standards"). The entity supplies the column,
      which is the part the schema cannot express.

requires_entity: fo_financial_dimension is the load-bearing line. It says that reaching a dimension through displayvalue isn't a stylistic choice; it is a path the model has already declared invalid. The two required filters are the ones the trial balance page applied for you: a ledger, because each legal entity has one and each ledger has its own accounting currency, and a posting layer, because there are ten and a query without the filter adds every book to the primary one.

Six decisions in here aren't inferable from the schema, and a person makes them once.

Which dimension answers which question. dimensionattribute.name says "Department." Whether Finance means that dimension or "CostCenter" when they ask for spend by team is a person's answer, recorded in other_names.

Which account structures exist and which main accounts each governs. dimensionhierarchy holds the definitions; which structure's view of a line is "the" view for a report is a policy.

The posting layer that means "the books." Current is the conventional answer. A tenant with statutory books on a custom layer wants a different one for a statutory report. The code is an enum and lands as an integer; decode it from globaloptionsmetadata rather than hard-coding it.

Which legal entities to include, and whether to translate. Each ledger has its own accounting currency. A group view needs reportingcurrencyamount, if a reporting currency is configured, or an explicit translation policy.

The "(none)" policy for lines posted under structures that don't carry the dimension.

Whether the lake copy of the combination table is current with activation. Activation is a schema change. A Synapse Link profile created before it may need the table re-added, and someone has to check model.json against dimensionattribute.

Reproduce it yourself

This runs against your own lake, which is the normal route. If your tenant admin will click through the trial, the same steps run there; check whether the Contoso USMF legal entity is present after deployment before assuming the demo ledger came with it.

1. Run the discovery query. It tells you what dimensions your tenant has and what activation named their columns, before you touch a single amount.
2. Confirm the column pair is in the lake. model.json in the Synapse Link container lists the exported columns per table.
3. Run the wrong query from the top of this post. Keep the output.
4. Run both corrected queries and compare them to each other, then to the Trial balance page for one legal entity with a dimension set on the same dimension.
5. Declare the relationships, the entity, and the metric, then ask the question in plain English in the assistant your team already opens, and compare against the number the controller reports.

The discovery query. Run this first. It is your own version of the schema fact this post can't state for you: the published schema of the combination table has nine columns, and this returns how many more yours has.

select name,
       type,
       viewname,
       dimensionkeycolumnname,
       dimensionvaluecolumnname
from   dimensionattribute
order  by name;
-- Every row is a column pair that exists on dimensionattributevaluecombination
-- in this tenant and in no published schema of that table.

Corrected, form A. The tenant's unpivoted column, once discovery has named it. Fastest, and the form Microsoft built the columns for. departmentvalue below is an example name; substitute the value from dimensionvaluecolumnname. USMF is Microsoft's Contoso demo entity; substitute yours.

select davc.departmentvalue                    as department,   -- from dimensionvaluecolumnname
       sum(gjae.accountingcurrencyamount)      as posted_amount
from   generaljournalaccountentry gjae
join   generaljournalentry gje
  on   gje.recid = gjae.generaljournalentry
join   dimensionattributevaluecombination davc
  on   davc.recid = gjae.ledgerdimension
join   ledger l
  on   l.recid = gje.ledger
where  l.name = 'USMF'                          -- one legal entity, one accounting currency
  and  gje.postinglayer = :current_layer        -- decode Current from globaloptionsmetadata
  and  gje.accountingdate >= date_trunc('quarter', current_date - interval '3 months')
  and  gje.accountingdate <  date_trunc('quarter', current_date)
group  by 1
order  by 2 desc;

Corrected, form B. The documented four-table walk. It needs no tenant-specific column, so it is the one to use if the lake copy of the combination table predates a dimension's activation. The filter on da.name is what keeps it from fanning.

select dalv.displayvalue                       as department,
       sum(gjae.accountingcurrencyamount)      as posted_amount
from   generaljournalaccountentry gjae
join   generaljournalentry gje
  on   gje.recid = gjae.generaljournalentry
join   ledger l
  on   l.recid = gje.ledger
join   dimensionattributevaluegroupcombination davgc
  on   davgc.dimensionattributevaluecombination = gjae.ledgerdimension
join   dimensionattributelevelvalue dalv
  on   dalv.dimensionattributevaluegroup = davgc.dimensionattributevaluegroup
join   dimensionattributevalue dav
  on   dav.recid = dalv.dimensionattributevalue
join   dimensionattribute da
  on   da.recid = dav.dimensionattribute
where  da.name = 'Department'
  and  l.name = 'USMF'
  and  gje.postinglayer = :current_layer
  and  gje.accountingdate >= date_trunc('quarter', current_date - interval '3 months')
  and  gje.accountingdate <  date_trunc('quarter', current_date)
group  by 1
order  by 2 desc;

Form A and form B must agree. A disagreement means the lake copy of the combination table is stale relative to activation, which is a real and documented event. And before trusting form B on a tenant with advanced rules, run the guard: count dimensionattributelevelvalue rows per ledgerdimension and da.name, and any count above one is a combination where a rule repeated a segment the structure already had, which Microsoft's own guidance says not to do ("Don't use rules to replicate segments that already exist in the account structure").

Two things to confirm before you paste. The tenant column names belong to your tenant, so read them from the discovery query rather than from this page. And the inner join in both forms drops postings that don't carry the dimension at all; switch to a left join from generaljournalaccountentry if the controller's trial balance shows a "(none)" line.

What this looks like in Agami

Everything above holds whoever builds the model. Here is what it is in our product, in the terms this post has used.

The ledgerdimension join is declared with a source, not inferred from a constraint. Introspection reads the tables, the columns, the keys, and how tables join from the lake itself. On a Synapse Link export that finds id on every table and stops there, because nothing in the lake says that an int64 called ledgerdimension is a combination RecId. The relationship goes in as readable YAML in your repo with Microsoft's article as its citation, and a validator blocks any write that would break the model.

The dimension is drafted as an entity, and a person approves it. Descriptions, entities, and metric definitions are drafted from the schema, then your team approves them, reversibly. fo_financial_dimension above is that step: the fast path bound to the column dimensionattribute names, the portable path filtered on dimensionattribute.name, and displayvalue marked as never a key. requires_entity means no posted-amount metric can reach a dimension through the string.

Posted amounts ship once they match the trial balance. Reconciliation takes a screenshot of the Trial balance page for one dimension set, a CSV export, or numbers pasted into chat, and compares at a one percent tolerance by default. A mismatch opens the SQL so you can see why. On this schema the mismatch is the rows whose structure put something other than Department in position two, and finding them is the useful output rather than the failure.

Every answer returns with its SQL beside it. Whether the dimension was resolved through the tenant column, walked through the four tables, or split out of displayvalue is visible on the answer, so a reviewer who knows the account structures can see a string split before the controller does.

A validated question becomes a golden test. Once posted amount by department agrees with the trial balance for one legal entity, that question and its answer are saved, and a change to the model that breaks it is not promoted.

And the number we don't have. We haven't run this on a Dynamics 365 Finance lake, so there is no count of dimensions a tenant typically activates, no size of the mislabelling on a real ledger, and no figure of ours in this post. The discovery query returns yours. And the model can only bind the dimension a person named: it records that "spend by team" means Department, it does not discover it, and it cannot tell whether your lake copy of the combination table is current with activation until someone reads model.json.

Frequently asked questions

What is the LedgerDimension column on GeneralJournalAccountEntry in Dynamics 365 Finance?

An int64 foreign key into DimensionAttributeValueCombination. Microsoft's developer documentation: "A Ledger Dimension is expressed as a foreign key that references the RecId value in the DimensionAttributeValueCombination table." The combination row holds the main account and every financial dimension of the posted line, stored across four tables and rendered once as a display string. The posted line itself carries no dimension column.

Why can't I split DisplayValue on the hyphen to get the department?

Because the segment order is set per account structure, and Microsoft documents that the strings "aren't used to uniquely identify the record." Its own example: 145-A is a department under a MainAccount-Department structure and a cost center under a MainAccount-CostCenter structure. Advanced rules add trailing segments only when they match, and blank segments are shown in the control but not stored, so positions shift between rows that look alike.

Where are the department and cost center columns in a Dynamics 365 Finance warehouse?

On DimensionAttributeValueCombination, as "unpivoted" columns that activating a dimension adds to the table, named after the tenant's own dimensions. The published Common Data Model definition of that table lists nine columns, counting the key, and none of them. DimensionAttribute records each dimension's column names in DimensionKeyColumnName and DimensionValueColumnName; query it to find yours, then confirm the columns are in your lake copy.

Why does joining the Dynamics 365 Finance dimension tables double my posted amounts?

Because DimensionAttributeValueGroupCombination holds one row per account structure used by a combination, and a combination that matched an advanced rule has two or more. Microsoft: "Two records are stored in the DimensionAttributeValueGroupCombination and DimensionAttributeValueGroup tables. Each record represents a structure that is used." Walking from the posted line through that table without filtering on DimensionAttribute.Name returns one row per segment per structure, and the sum multiplies.

Doesn't Microsoft's own analytics already answer posted amount by dimension?

In the tenant, yes. Business performance analytics is "included as part of the Dynamics 365 Finance license," runs on a curated dimensional model, and Microsoft documents that customers "can currently report on up to eight quarters of data" with "two data refreshes per day." Its ERP Analytics MCP server lets AI agents query that model in natural language. None of it runs on the raw tables in your lake beside your other systems, which is the reader this post is for.

References

1. Ledger account combinations, Microsoft Learn (Dynamics 365 finance and operations developer documentation). The four-table storage model, the foreign-key sentence, "Apparent duplicate combinations" with the 145-A example, the "about 50 dimensions" sentence, the unpivoted-columns paragraph, immutability, and the entry-control walkthrough. The primary source for this post.
2. DimensionAttributeValueCombination, Common Data Model entity reference, Microsoft Learn. The nine published columns and two relationship attributes.
3. microsoft/CDM on GitHub, schemaDocuments/core/operationsCommon/Tables/Finance. The .cdm.json definitions of GeneralJournalAccountEntry, GeneralJournalEntry, DimensionAttribute, and the four ledger-dimension tables, including DimensionKeyColumnName, DimensionValueColumnName, and every Relationship_* attribute cited above.
4. Create read-only entities that expose financial dimensions, Microsoft Learn. The SELECT TOP 1 sample keyed on DimensionValueColumnName.
5. Fix financial dimension activation errors, Microsoft Learn. Activation as a schema change, and the SYSTEMGENERATEDATTRIBUTE column in the CDC error.
6. Financial dimension sets, Microsoft Learn. The in-application summarisation by dimension.
7. Ledger, subledger, and subledger journal accounting entries overview, Microsoft Learn. One ledger per legal entity, ten posting layers, structures per main account.
8. Choose finance and operations data in Azure Synapse Link for Dataverse, Microsoft Learn. The Tier-2 prerequisite, the FnO_Id and reserved-word renames, GlobalOptionsMetadata, and the export limitations.
9. Unified admin trials and About trial environments, Microsoft Learn. The tenant-admin gate and the subscription clock.
10. What is Business performance analytics?, Microsoft Learn. The eight-quarter and twice-daily limits, and the ERP Analytics MCP server.
11. Microsoft Dynamics 365 Finance and Operations connector, Fivetran. The two-hop route and the one-sentence schema note.
12. agami-core on GitHub

Make your Dynamics 365 Finance data answerable

Agami is the semantic layer between your AI assistant and your lake. It declares which column decodes a dimension and which ledger and posting layer a posted amount means, so an agent returns a governed breakdown or says why it cannot.

Start a free trial or talk to us →