Tuesday, April 07, 2020

Dimension Tables

Step 1: Lets take a simple scenario of creating 2 dimensions or Attributes.
  1. D1
  2. D2


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 attributes can be combined to create attribute sets. A set would have the individual dimensions and the sequence in which they are used as details
    3.1  SET1
          3.1.1  D1
          3.1.2  D2

    3.2   SET2
          3.2.1  D2
          3.2.2  D1


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


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. Each dimension has 1 record in this table. (Step 1)
  2. DimensionAttributeSet = this table maintains the dimension set. (Step 3)
  3. DimensionAttributeSetItem = this table store the details of individual attributes in each set (Step 3.1.1 to 3.2.2)

 Part 2 : The *Value counterparts are :-
  1. DimensionAttributeValue : The individual values ( Step 1.1 to 2.3). There is a EntityInstance field in this table. That’s the relation to the value original table when the dimensions are not custom values.
  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. As the name suggests the 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

No comments: