Dimension Tables

Step 1: Lets take a simple scenario of creating 2 dimensions or Attributes.
  1. D1_Location
  2. D2_Department


Step 2: These attributes would then have values
    1.1  DXB
    1.2  IND

    2.1  SALES
    2.2  OPS
    2.3  ADMIN

Step 3: These dimensions can be combined to create attribute sets. A set would decide the dimensions involved and their sequence 
    3.1  SET1: In this set D1_Location dimension is first and D2_Department dimension is second in sequence.
          3.1.1  D1_Location
          3.1.2  D2_Department

    3.2   SET2: In this set the D2_Department dimension is first and D1_Location is second in sequence. 
          3.2.1  D2_Department
          3.2.2  D1_Location


Step 4: Based on the sets defined above a combination of attribute values could be created
  4.1    SET1
     4.1.1   DXB+SALES
     4.1.2   DXB+OPS
     4.1.3   DXB+ADMIN


  4.2  SET2
    4.2.1    IND+SALES
    4.2.2    IND+OPS
    4.2.3    IND+ADMIN

Firstly dimensions are of two types: -

Lookup Dimenions: These are lookup to an existing master in AX. The dimensions are stored in two tables namely, DimensionAttribute (for dimension name) and DimensionAttributeValue (for dimension values, There is a EntityInstance field in this table. That’s the relation to the value original table.) 

Custom : These are custom defined values and do not exist elsewhere within AX. These are stores in two custom tables FinancialTagCategory (for dimension name) and DimensionFinancialTag (for dimension values)

When the above structure has to be stored in AX tables, it is divided in two parts. The part 1 takes care of storing the schema and the part 2 takes care of storing the values.

Part 1 : the details about the dimensions are stored in

1. DimensionAttribute = this tables is the dimension master (D1_Location and D2_Department). Each dimension has 1 record in this table. (Step 1)
2. DimensionAttributeSet = this table maintains the dimension set. (Step 3)
3. DimensionAttributeSetItem = this table is the child table for DimensionAttributeSet and stores the the individual attributes in a set (Step 3.1.1 to 3.2.2)

 Part 2 : The *Value counterparts for the above dimensions are :-

1. DimensionAttributeValue : The individual values DXB, IND, SALES, OPS, ADMIN ( Step 1.1 to 2.3). If the values are looked up from another table, there is a EntityInstance field in this table which stores the RecID of the actual value from the original table when the dimensions created.
2. DimensionAttributeValueSet : The values corresponding to each set. There is a hash value generated for each combination of values. (a hash is a numeric equivalent of a string).  
3. DimensionAttributeValueSetItem : The individual values for each of the attribute of the set.
4. FinancialTagCategory: This table stores record of custom financial dimension.
5. DimensionFinancialTag: this table stores custom financial dimensions value.

The combination of Ledger Account with the DimensionAttributes is stored in a new set of tables referred as ValueGroup Tables. The nomenclature is justified as value group is a group that is created to store values (amounts).

1. DimensionAttributeValueCombination: Stores combination of Ledger and DimensionAttributes
2. DimensionAttributeValueGroup: Stores dimension group
3. DimensionAttributeValueGroupCombination: Store relation of DimensionAttributeValueGroup and DimensionAttributeValueCombination
4. DimensionAttributeLevelValue: Stores dimension value of ledger dimension

Consider the following SQL statement, will return each CombinationID, AttributeName, AttributeValue:

select DAVSI.DimensionAttributeValueSet, DA.Name, DAVSI.DisplayValue

from DimensionAttributeValueSetItem DAVSI

inner join DimensionAttributeValue DAV

    on DAV.RecID = DAVSI.DimensionAttributeValue

inner join DimensionAttribute DA

    on DA.RecID = DAV.DimensionAttribute

Consider the below SQL statement, will return each CombinationID for LedgerDimension, AttributeName,  AttributeValue:

select DAVGI.DimensionAttributeValueCombination, DA.Name, DALV.DisplayValue

from dimensionAttributeValueGroupCombination DAVGI

inner join dimensionAttributeLevelValue  DALV

    on DALV.DimensionAttributeValueGroup = DAVGI.DimensionAttributeValueGroup

inner join dimensionAttributeValue DAV

    on DAV.RECID = DALV.DimensionAttributeValue

inner join DimensionAttribute DA

    on DA.RecID = DAV.DimensionAttribute

order by DAVGI.DimensionAttributeValueCombination, DALV.Ordinal