Ledger account combinations - Part 1 (Dimensions)

Introduction

In Dynamics AX 2009, dimensions were limited to a minimum of three and a maximum of ten, and entered in a set order that required code customizations and database synchronization for each dimension added. In Dynamics AX 2012, the dimension framework was expanded to allow unlimited dimensions which can be dynamically created by the user, and entered in any order. The unlimited nature of the new model, coupled with taking advantage of relational database design as well as optimizing for performance requirements has led to a more complex data model than existed in the past.  In this series of blog posts, we will discuss the various areas of the dimension framework, and how they work together to give a better understanding of “What happens when I create a ledger account combination?”

The model below in figure 1 shows the various areas within the dimension framework.

Figure 1: Dimensions in framework

This initial blog post covers the Dimensions, Dimension Values, Categorizations and Backing Entities regions highlighted in figure 1 above in pale yellow.

Subsequent blog posts will cover:

• Dimension Enumerations and Default Dimensions
• Structures and Constraints
• Advanced Rules
• LedgerDimensions
• Advanced topics including hash keys, hashes and other key data model elements

Dimension Attributes

A dimension attribute, which will be referred to as a dimension, simply represents an additional piece of classifying information that a user would like to associate with a ledger account combination. It represents classes of things, not specific instances. Examples of things that can be used to create a dimension are Department, Cost Center, Expense Purpose, Customer, Vendor, Item – which are all classes of entities that already exist in the system; or custom entities that are specific to a particular installation such as license plate number or event name or ticket number.

When a dimension is created, the user chooses to use values for it from either an existing entity in the system such as Customers or Departments, or to create a custom list. The dimension framework keeps track of a reference for this dimension to a table in the system.  For existing entities such as Customers, a reference to the CustTable table is used.  For custom entities that are defined by the user, a reference to the DimensionFinancialTag table is used.  This metadata about what a dimension represents is stored in the DimensionAttribute table for each dimension defined. 

The following form shows an example of two dimensions, one to represent customers that already exist in the application, and another that represents a new custom list.

Figure 2: Financial dimensions form

The data is stored in the DimensionAttribute table. The SQL query below in figure 3 shows some of the basic information associated with each dimension.

Figure 3: DimensionAttribute storage query results

The Type determines whether the dimension is backed by an existing entity in the system or a custom list.  It is also important to note that the dimension framework does not directly reference the existing entity backing table such as CustTable. Instead, a custom view is created to make an entity available in the system for use in the dimension framework. As of Dynamics AX 2012 R2, 36 existing entities have been enabled to be used as dimensions in the system.  

It is possible for a user to create more than one dimension based on the same entity. There may be instances where an entity in the system is used for multiple different purposes when classifying transaction activity in the system.  In this case, multiple dimensions can be defined for it, one for each of its purposes.  A common example would be a cost center backing entity used to represent the primary cost center (e.g. selling) and the cost center the transaction is being traded against (e.g. purchasing).

Internally, special dimensions exist that are automatically created to support key functionality of the dimension framework. A primary example is the Main Account dimension. This allows a main account to be treated as a dimension by the dimension framework, but also prevents it from being used by a user to create a dimension. The other types of special dimensions are system generated ones that are used by the dimension framework for internal purposes.

Dimension Attribute Values

A dimension attribute value is a specific instance of a dimension used within the dimension framework. The values for a dimension are determined by the ViewName specified on the DimensionAttribute record. In the case of an existing entity, such as CustTable, values consist of the records in that table. In the case of a custom list, it is a specific set of records within the DimensionFinancialTag table.  Values that are available for a particular dimension are viewable by clicking the “Financial dimension values” button on the Dimension details form as shown in figure 2 above. When the list is provided by an existing entity, such as CustTable, it is not editable from this form. To create a new dimension value for Customer, the user would go directly to the Customer form and create a new customer.  Once created, the new customer will become available for use in the dimension framework. When the list is provided by the user as a custom list, the user will be able to modify the list directly on this form.

Example of a list of values provided by CustTable (with no values stored in the dimension framework):

Figure 4: Financial dimensions values form (existing list)

 Example when provided by a custom list (with values stored in the dimension framework):

  Figure 5: Financial dimensions form (custom list)

Figure 6: Dimension setup tables query results

In both of these cases, the Financial dimension values form is displaying what values exist for the entity, not what values have actually been used within the dimension framework.  The dimension framework representation of these values is not created until it is used within the framework requiring it to hold a reference to it. This allows for the ability for values to be deleted that have not yet been used, and for storage size and performance optimization.

Once a dimension value is referenced requiring it to be saved by the dimension framework, it is stored in the DimensionAttributeValue table.  This table is the link between the DimensionAttribute and the specific RecId of the record in the ViewName view or table referenced on the DimensionAttribute. Both the DimensionAttribute and DimensionAttributeValue records are needed to navigate back to the originating value that the user has entered.

In a system where nothing has been referenced by the dimension framework, there will be no records in the DimensionAttributeValue table.

In the next blog post, the storage of dimensions as dimension enumerations and of dimension values as default dimensions will be explained.