Wednesday, 28 September 2011

Default dimension storage in Ax 2012

The Chart of accounts structure in Ax2012 has changed significantly from previous versions. In Ax2009, any table that stored dimensions would have a field called 'Dimension' (an n-element array). This applied to master data (customers, items, etc), as well as transactions, like entries in the GL (LedgerTrans). Typically, dimension values would flow from the master data all the way through the GL postings. eg. Values specified on a customer record would get copied onto a sales order header, to the sales lines, to the invoice/lines, then through to the GL.

In 2012, this concept still remains however the implementation is very different. We now have the concept of 'Default dimensions', which are stored on the master data, but not on the transactions. The default dimensions are similar to the dimension values propagated to the GL, but they are stored in a different structure.

Table structure


Let's have a look at a quick code sample that displays the default dimensions attached to a supplier. (This is working off the Ax2012 demo data).

static void ShowVendDefaultDimensions(Args _args)
{
    VendTable                       vendTable;
    DimensionAttributeValueSet      dimAttrValueSet;
    DimensionAttributeValueSetItem  dimAttrValueSetItem;
    DimensionAttributeValue         dimAttrValue;
    DimensionAttribute              dimAttr;
    Common                          dimensionValueEntity;
    ;
    
    // Find our supplier
    vendTable = VendTable::find('3008');
    
    // Find the dimension value set that the vendor points to (for specifying the 
    // 'default' dimensions). This table is used as a sort of 'header' that the 
    // value set items (DimensionAttributeValueSetItem) records belong to.
    dimAttrValueSet = DimensionAttributeValueSet::find(vendTable.DefaultDimension);

    // Find all of the 'value set items' linked against the 'value set'  
    while select dimAttrValueSetItem
        where   dimAttrValueSetItem.DimensionAttributeValueSet   == dimAttrValueSet.RecId
    {
        // Find the dimension 'value' (DimensionAttributeValue) that the set item points to.        
        dimAttrValue        = DimensionAttributeValue::find(dimAttrValueSetItem.DimensionAttributeValue);
        
        // Find the underlying attribute.
        dimAttr             = DimensionAttribute::find(dimAttrValue.DimensionAttribute);

        // Use the helper class to obtain a reference to the underlying entity (can be anything)              
        dimensionValueEntity = DimensionDefaultingControllerBase::findBackingEntityInstance(
            curext(),
            dimAttr,
            dimAttrValue.EntityInstance);
        
        info(dimAttr.Name + ' ' + dimAttrValue.getValue());               
    }
}
X++ code to retrieve default dimensions (via individual selects)



Obviously this isn't a particularly efficient approach - it's expanded out like this for the sake of demonstration. In picture-form it may look similar to the following. Note the main tables involved, and the relationships between them:

Tables for default dimensions


That's a lot of tables! Whereas before we would just reference the elements of the Dimension array, we now have to go through multiple joins to get the same information. The reason for this is the way dimensions are defined and structured in Ax2012. Previously we had a fixed number of dimensions, and a fixed source (the dimension code table), but now we can define an attribute that points to pretty much anything (customers, item groups, warehouses, etc).

I'll be interested in seeing how this affects reporting that works off direct SQL queries or cubes, as we now have to dynamically link tables based on the underlying source table (identified by DimensionAttribute.BackingEntityType). It could make things a bit tricky, and I suspect we'll have to rely more on generating datasets from within Ax, using the new data provider framework for SSRS. 
So an overview of the main tables involved is:

Table Description
DimensionAttributeValueSet A unique combination of values used for default dimensions. This acts as a
container for a list of DimensionAttributeValueSetItem records, which link off
to the specific attribute and attribute value records.

This is similar in concept to the InventDim table in Ax2009, which stores unique combination of inventory dimension values. It uses a field called Hash, which stores a hash-code for all of the attached values. This is used by Ax when checking whether it needs to create a new entry, or use an existing one. (NB the dimension controllers rely heavily on server-side caching - If you're doing any investigation into the code it may help to disable this via code. Just make sure it's left as-is for production and testing environments).
DimensionAttributeValueSetItem This stores the individual attribute items (I would describe them more as the 'segments'), that make up a value set. This relates to the RecID of the
DimensionAttributeValueSet via the field of the same name.

Note that this table doesn't store the actual value. It points to an instance of DimensionAttributeValue (see below), which in-turn links back to the dimension value entitiy (eg Customer table).
DimensionAttributeValue This is a link between an attribute and a value.


The field EntityInstance points to the RecID of the underlying table/view. NB
the structure of this is normally that you create a view pointing to the table or tables you want to use for the dimension values. The view can be structured as normal with joins, relations, etc, but will typically only return three fields:

  • Key - RecID of primary table
  • Value - 'Code', such as customer account, item number, etc.
  • Name - The description/name, eg The name on the customer address book entry.
The convention is that any table used for dimension values is exposed as a view (prefixed with "DimAttribute"). Have a look at the existing DimAttributexxx views in the standard application for plenty of examples.
DimensionAttribute The main attribute table. This will have an entry for 'department', 'cost centre', 'purpose', etc, as well as any other dimensions you define. Each DimensionAttribute points to a 'backing entity' type, which is the table/view id of the underlying data-source.

For 'custom value' dimensions (ie those that don't point to an existing table), this points indirectly to table DimensionFinancialTag.

Table overview

* There's a slight caveat here. If the dimension points to a table like CustTable, how does Ax make sure that there is a corresponding entry in DimensionAttributeValue? The answer is that whenever the dimension value is referenced (for example by selecting it on a form), the system checks whether the entry exists, and if not, it's created. This occurs at:




\Data Dictionary\Tables\DimensionAttributeValue\Methods\insert

5

\Data Dictionary\Tables\DimensionAttributeValue\Methods\findByDimensionAttributeAndEntityInst

50

\Forms\DimensionDefaultingLookup\Methods\closeSelect

17

And in addition, what if we're referencing the customer dimension, but the underlying customer record is deleted? If you look at CustTable.delete, you'll see a call to DimensionAttributeValue::updateForEntityValueDelete. This goes through any existing references to the corresponding DimensionAttributeValue and clears them. I suspect (at least I'd hope), that if any GL postings have already been made, you won't be able to remove the underlying record.

Forms

The class DimensionDefaultingController is used throughout the application to handle the display of default dimensions on master records (customer, suppliers, etc). If you look at the code in the following stack-trace, you'll see query logic similar to the sample at the beginning of this post.


Stack trace for the update of default dimension controls on a form

The DimensionDefaultingController is created on the form, accepting the datasource and field (which in most cases will be DimensionDefault). On the datasource 'active' event, the controller iterates through the relevant dimension value set, and updates the controls. There's a lot more to cover with respect to how dimensions are displayed/updated from the UI - Look out for a future post.

3 comments:

  1. It's very difficult to see without this help changes of dimension from AX3 to AX2012.

    Thanks for this post!!!

    ReplyDelete
  2. More better then MS official document, thanks for your wonderful post!!!

    ReplyDelete
  3. HI ,
    I have just opposite scenario. I have the default FinancialTag Values (for 3 main dim which are cost center, dept. and purpose) from external system, but how can i set those values for example in inventJournaltrans item ? Those 3 values should give me a rec id where i can put into the defaultDimension property in inventjournatrans item.

    Thanks !

    ReplyDelete