Microsoft Dynamics 365: Changes to Financial Dimensions Structure

Monday, April 30, 2018

Simplified dimension value fetch

After the release of Dynamics 365 for Operations [Enterprise Edition] dimension structure and business logic has slightly changed. In this blog post, I will introduce you to what’s new for dimensions in the new version of Dynamics AX.

Building dimension-based select statements in Dynamics 365 for Operation [Enterprise Edition] is much easier than in Dynamics AX 2012. Instead of writing a huge select statement to find a record with a particular dimension value, now you will need significantly less code lines than you would in previous versions. It’s because now dimension values are moved directly to DimensionAttributeValueSet (for default dimensions) and DimensionAttributeValueCombination (for ledger accounts) tables. New dimension fields are added when a dimension is activated. If you’re interested, you can find the code that does that in DimensionSchemaAndDataSynchronizationUtility / createDimensionColumnsForList method.

For example, if you would activate a CustomerSegment dimension, the system would create a new CustomerSegment and CustomerSegmentValue columns in backing SQL table (these fields will not be visible AOT in Visual Studio) for both DimensionAttributeValueSet and DimensionAttributeValueCombination tables.

CUSTOMERSEGMENT	CUSTOMERSEGMENTVALUE
5637144684	100
5637144684	100
5637144680	4000

Example 1: CustomerSegment and CustomerSegmentValue dimension

In this example, CustomerSegment field is the Record of the dimension’s backing entity (in this case CustTable) and CustomerSegmentValue is CustomerSegment display value.

To select a specific dimension value directly from DimensionAttributeValueCombination (or DimensionAttributeValueSet) table you will need to specify its fieldID instead of its name. For that purpose, you can use the DimensionAttributeValueCombination/getDimensionValueFieldId method.

To see how dimension-based select statements can be shortened in Dynamics 365 let’s look at an example which selects projects that have a specific BusinessUnit dimension display value:

static void CF1_GetProjectsWithCustomerSegment(Args _args)
{
    #define.BUSINESSUNIT('BusinessUnit')

    ProjTable                       projTable;

    DimensionAttribute              da;
    DimensionAttributeValue         dav;
    DimensionAttributeValueSet      davs;
    DimensionAttributeValueSetItem  davsi;

    while select ProjId from projTable
        exists join davs
            where davs.RecId == projTable.DefaultDimension
        exists join davsi
            where davsi.DimensionAttributeValueSet == davs.RecId &&
                  davsi.DisplayValue               == '001'
        exists join dav
            where dav.RecId == davsi.DimensionAttributeValue
        exists join da
            where da.RecId == dav.DimensionAttribute &&
                  da.Name  == #CUSTOMERSEGMENT
    {
        info(projTable.ProjId);
    }
}

Example 2: Financial dimension-based select statements in Dynamics AX 2012

class CF1_GetProjectsWithBusinessUnit
{        
    public static void main(Args _args)
    {        
        Const Name BUSINESSUNIT = 'BusinessUnit';

        FieldId                     businessUnitId;
        ProjTable                   projTable;
        DimensionAttributeValueSet  davs;

        businessUnitId = DimensionAttributeValueCombination::getDimensionValueFieldId(BUSINESSUNIT);

        while select ProjId from projTable
            exists join davs
                where davs.RecId          == projTable.DefaultDimension &&
                    davs.(businessUnitId) == '20'
        {
            info(projTable.ProjId);
        }
    }
}

Example 3: Financial dimension-based select statements in Dynamics 365

As you can see, select statements are now much shorter, which in turn increases solution performance. The same applies to queries in Dynamics 365.

class CF1_GetProjectsWithBusinessUnit
{        
    public static void main(Args _args)
    {        
        Const Name BUSINESSUNIT = 'BusinessUnit';

        FieldId         businessUnitId;
        ProjTable       projTable;

        businessUnitId = DimensionAttributeValueCombination::getDimensionValueFieldId(BUSINESSUNIT);

        Query                   q;
        QueryRun                qr;
        QueryBuildDataSource    qbdProjTable, qbdDimensionAttributeValueSet;

        q = new Query();

        qbdProjTable = q.addDataSource(tableNum(ProjTable));

        qbdDimensionAttributeValueSet = qbdProjTable.addDataSource(tableNum(DimensionAttributeValueSet));
        qbdDimensionAttributeValueSet.relations(true);

        SysQuery::findOrCreateRange(qbdDimensionAttributeValueSet, businessUnitId).value('20');

        qr = new QueryRun(q);

        while (qr.Next())
        {
            projTable = qr.get(tableNum(ProjTable));
        }
    }
}

Example 4: Financial dimension-based queries in Dynamics 365

Changes to DimensionStorage class in Dynamics 365

In Dynamics 365, quite a few methods from DimensionStorage class (which is commonly used in development projects) were moved to other classes. Here is a list of those methods:

AX 2012 DimensionStorage method	Dynamics 365 DimensionStorage method
getMainAccountIdFromLedgerDimension	LedgerDimensionFacade/ getMainAccountRecIdFromLedgerDimension
getMainAccountFromLedgerDimension	LedgerDimensionFacade/getMainAccountFromLedgerDimension
getDefaultAccount	LedgerDefaultAccountHelper/getDefaultAccountFromMainAccountRecId
getDefaultAccountForMainAccountNum	LedgerDefaultAccountHelper/getDefaultAccountFromMainAccountId
accountNum2LedgerDimension And getDynamicAccount	LedgerDynamicAccountHelper/getDynamicAccountFromAccountNumber
ledgerDimension2AccountNum	LedgerDynamicAccountHelper/ getAccountNumberFromDynamicAccount
getDefaultDimensionFromLedgerDimension	LedgerDimensionFacade/getDefaultDimensionFromLedgerDimension

There may be cases when you need to retrieve a ledger or default dimensions from display values. Doing that has changed a bit in Dynamics 365 and I will give you a few solutions options.

To get the ledger dimension from display values you will need to set each segment that has value using DimensionStorageSegment class and then use the ledger AccountDimensionResolver resolve method to retrieve ledger dimension. Here’s an example of how that could be achieved:

//Dimension segement constant List
const Name MAINACCOUNT     = 'MainAccount';
const Name BUSINESSUNIT    = 'BusinessUnit';
const Name CUSTOMERSEGMENT = 'CustomerSegment';
const Name PROJECT         = 'Project';
const Name PRODUCT         = 'Product';
const Name GROUPCODE       = 'GroupCode';
const Name TARGET          = 'Target';

public DimensionDynamicAccount getLedgerDimensionFromDisplayValues (CF1_DisplayValueTable  _displayValueTable)
{
    LedgerAccountDimensionResolver  ledgerAccountDimensionResolver;
    DimensionStorage                dimensionStorage;
    DimensionHierarchy              dimensionHierarchy = DimensionHierarchy::find(DimensionHierarchy::getAccountStructure(MainAccount::findByMainAccountId(_displayValueTable.Account).RecId, Ledger::current()));

    dimensionStorage = DimensionStorage::construct();
    dimensionStorage.addAccountStructure(dimensionHierarchy.RecId);

    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Account, MAINACCOUNT);
    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Object1, BUSINESSUNIT);
    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Object2, CUSTOMERSEGMENT);
    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Object3, PROJECT);
    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Object4, PRODUCT);
    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Object5, GROUPCODE);
    this.setSegments(dimensionStorage, dimensionHierarchy.RecId, _displayValueTable.Object6, TARGET);
 
    ledgerAccountDimensionResolver = LedgerAccountDimensionResolver::newResolver(dimensionStorage.getComboDisplayValue(), dimensionHierarchy.Name);
    ledgerAccountDimensionResolver.parmDimensionFormat(DimensionHierarchyView::findByHierarchy(dimensionHierarchy.RecId).Segments);

    return ledgerAccountDimensionResolver.resolve();
}

public  void setSegments(DimensionStorage _dimensionStorage, DimensionHierarchyId _dimensionHierarchyId, DimensionValue _dimensionValue, Name _dimensionName)
{
    DimensionAttribute             dimensionAttribute 	   = DimensionAttribute::findByName(_dimensionName);
    DimensionAttributeValue        dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute, _dimensionValue);

    if (_dimensionValue && dimensionAttributeValue && _dimensionHierarchyId && dimensionAttribute)
    {
        DimensionStorageSegment		dimStorageSegment;
 
        dimStorageSegment = DimensionStorageSegment::construct(
            _dimensionValue,
            dimensionAttributeValue.RecId,
            dimensionAttributeValue.HashKey);

       _dimensionStorage.setSegment(DimensionHierarchyLevel::findByDimensionHierarchyAndDimAttribute(_dimensionHierarchyId, dimensionAttribute.RecId).Level, dimStorageSegment);
    }
}

Example 5: Financial dimensions from display values in Dynamics 365

To retrieve a default dimension from the dimension segment display values you will need to use DimensionAttributeValueSetStorage class. Here’s an example of how this can be implemented:

public static DimensionDefault getDefaultDimensionFromDisplayValues(CF1_DisplayValueTable  _displayValueTable)
{
    //Dimension segement constant List
    const Name BUSINESSUNIT    = 'BusinessUnit';
    const Name CUSTOMERSEGMENT = 'CustomerSegment';
    const Name PROJECT         = 'Project';
    const Name PRODUCT         = 'Product';
    const Name GROUPCODE       = 'GroupCode';
    const Name TARGET          = 'Target';

    DimensionAttributeValueSetStorage   invoiceDAVSStorage;

    void addItem(DimensionAttributeValue _dav)
    {
        if (_dav)
        invoiceDAVSStorage.addItem(_dav);
    }

    invoiceDAVSStorage = new DimensionAttributeValueSetStorage();

    addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::findByName(BUSINESSUNIT), _displayValueTable.Object1));
    addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::findByName(CUSTOMERSEGMENT), _displayValueTable.Object2));
    addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::findByName(PROJECT), _displayValueTable.Object3));
    addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::findByName(PRODUCT), _displayValueTable.Object4));
    addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::findByName(GROUPCODE), _displayValueTable.Object5));
    addItem(DimensionAttributeValue::findByDimensionAttributeAndValue(DimensionAttribute::findByName(TARGET), _displayValueTable.Object6));

    return invoiceDAVSStorage.save();
}

Figure 6: Retrieve Default Dimension in Dynamics 365

I hope this has given you some insight into the differences between dimension structure in Dynamics 365 for Operations [Enterprise Edition] versus Dynamics AX 2012. Stay tuned for more information and insights from 1ClickFactory on Dynamics 365 for Operations [Enterprise Edition]!