Dyota's blog

Dynamics F&O map

Introduction

This is a map of Dynamics 365 Finance and Operations.

I imagine the landscape as several distinct islands (which are groups of tables) that are related to each other.

This will be based on a business that sells people's time (as opposed to units of material).

The islands:

Conventions

Financial dimensions

There are two different tables:

DimensionAttributeValueCombination

davc relates to ledgerdimension. If you see a ledgerdimension reference on a table (in the general ledger for example (GeneralJournalAccountEntry), it points to davc.

davc will always have a mainaccountvalue. This relates to the table MainAccount. This table has a column type, which is a n enum that describes what type of account it is on the Chart of Accounts. Join onto GlobalOptionSetMetadata (gosm) for the full description. The types are below:

main account types 

1: Revenue
2: Expense
3: 
4: Asset
5: Liability
6: Equity
7: 
8: Total

The other financial dimensions that are defined in the system will have columns of their own. This will differ with each implementation. Each of these is enumerated in OMOperatingUnit, where their code is in the omoperatingunitnumber column. To get the long names of these, join onto DirPartyTable

SQL: OMOperatingUnit 

select 
    ou.omoperatingunitnumber 
    , d.name as locationname -- the long name lives in dirpartytable
    , d.namealias
from omoperatingunit as ou
    left join dirpartytable as d on d.recid = ou.recid
where ou.omoperatingunittype = 4 -- must choose a value for this to narrow down

Notably, dataareaid is not a financial dimension that will come off of davc. It is best to connect dataareaid directly to the fact table.

ERD 

Table mainaccount {
	mainaccountid string 
    name string
    type int64
}

Table exampledimension {
omoperatingunitnumber
name string
type int64
}


Table dimensionattributevaluecombination as davc {
recid int64
exampledimensionvalue string [ref: > exampledimension.omoperatingunitnumber]
mainaccountvalue string [ref: > mainaccount.mainaccountid]
}


Table dataarea {
fno_id string
}


Table generaljournalaccountentry {
ledgerdimension int64 [ref: > davc.recid]
dataareaid string [ref: > dataarea.fno_id]
}

DimensionAttributeValueCombination

This is the other dimension set, and it corresponds to columns called defaultdimension. Notably, it connects to projpostedtranstable.

It will also have columns that correspond to OMOperatingUnit.

General Ledger

Actuals

The main table to pick up is GeneralJournalAccountEntry (gjae). This table represents individual transactions.

There is another table called GeneralJournalEntry (gje) which represents a group of transactions, as a single journal entry. Many lines in gjae correspond to one line in gje.

However, in terms of reporting, there is not need to have both. There are two bits of information in gje that are absent in gjae, and these could be joined onto gjae:

SQL: GeneralJournalAccountEntry 

select 
    gje.subledgervoucherdataareaid as dataareaid
    , base.recid
    , gje.accountingdate
    , gje.journalcategory
    , journalcategory.LocalizedLabel as journalcategoryname
    , base.postingtype as postingtypecode
    , postingtype.LocalizedLabel as postingtype
    , base.transactioncurrencyamount
    , base.transactioncurrencycode
    , base.accountingcurrencyamount
    , base.reportingcurrencyamount
    , base.generaljournalentry
    , base.ledgerdimension
    , base.quantity
    , base.text
    , base.mainaccount
    , base.iscorrection
    , base.iscredit
    , 'actual' as source -- will combine with budgettransactionline
from dbo.generaljournalaccountentry as base
    left join dbo.generaljournalentry as gje on base.generaljournalentry = gje.recid
        left join GlobalOptionsetMetadata as journalcategory on gje.journalcategory = journalcategory.[Option] and journalcategory.EntityName = 'generaljournalentry' and journalcategory.OptionSetName = 'journalcategory'
    left join GlobalOptionsetMetadata as postingtype on base.postingtype = postingtype.[Option] and postingtype.EntityName = 'generaljournalaccountentry' and postingtype.OptionSetName = 'postingtype'

In the SQL query above, I have an extra column, called source which only has one value: actual. This is in case this table is to be appended to the budget table, depending on how the BI model is built.

Budget

The table that contains the budget for each main account is BudgetTransactionLine. It has a ledgerdimension column that relates to davc.

This will need to be joined onto BudgetTransactionHeader to fill out. The company (budgetmodeldataareaid) and the budget model (budgetmodelid) will come from the this header table.

SQL: BudgetTransactionLine 

select 
    header.budgetmodeldataareaid as dataareaid
    , base.recid
    , budgetmodeltype.LocalizedLabel as budgetmodeltype
    , header.budgetmodelid
    , base.date
    , base.ledgerdimension
    , base.accountingcurrencyamount
    , base.transactioncurrencyamount
    , base.budgettype as budgettypecode
    , budgettype.LocalizedLabel as budgettype
    , base.budgettransactionheader
    , 'budget' as source -- will combine with generaljournalaccountentry
from dbo.budgettransactionline as base
    left join GlobalOptionsetMetadata as budgettype on base.budgettype = budgettype.[Option] and budgettype.EntityName = 'budgettransactionline' and budgettype.OptionSetName = 'budgettype'
    left join dbo.budgettransactionheader as header on base.budgettransactionheader = header.recid
        left join GlobalOptionsetMetadata as budgetmodeltype on header.budgetmodeltype = budgetmodeltype.[Option] and budgetmodeltype.EntityName = 'budgettransactionheader' and budgetmodeltype.OptionSetName = 'budgetmodeltype'
where 1=1
    and base.recid is not null
    and base.transactioncurrencyamount <> 0

Similar to gjae, in the SQL query above, I have an extra column, called source which only has one value: budget.

Measures

These measures assumes that gjae is already connected to davc to access all of the ledger dimensions, especially the main accounts.

DAX Measures: GeneralJournalAccountEntry 

DEFINE
MEASURE _measures[GL Actual Revenue] = 
CALCULATE(
    [GL Actual Base],
    FILTER(
        mainaccount,
        [type] = 1 // Revenue
    )
)
MEASURE _measure[GL Actual Expense] = 
CALCULATE(
    [GL Actual Base],
    FILTER(
        mainaccount,
        [type] = 2 // Expense
    )
)
MEASURE _measures[GL Actual Profit] = 
[GL Actual Revenue] - [GL Actual Expense]

#database #dbml #erd #f&o