SQL View for Dimension

 The following SQL view can be used to extract dimension information using SQL 

1. DefaultDimensionView: for default dimension each dimension value is listed as a separate row. Hence, we  might need multiple joins for one each dimension to get values in a columnar format. 

2. DimensionAttributeValueCombination: For Ledger dimension. Each dimension would exist as a column in this view. 

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

Partially disable dimensionDefaultingController

The requirement being restricting the dimension selection on the dimension controller based on certain business rules.

I had a requirement where the default dimensions on the employee master had to be restricted to allow entry only for a subset of the total dimensions. As shown in the screen shot below the need was to restrict the selection of only D1_Division and D3_ConsGroup on the employee master and disable the rest for data entry

The code for the same has to be written on the Active method of the relevant DataSource on the form.


    DimensionAttributeSetStorage    dimAttrSetStorage;
    DimensionAttribute              dimAttribute;
    DimensionEnumeration            dimEnumeration;

    int ret;

    ret = super();

    dimensionDefaultingController.activated();    
    
    //The dimension controller to be locked to allow only certain dimensions to be entered.
    dimAttrSetStorage = new DimensionAttributeSetStorage();
    // D1_Division
    dimAttribute = DimensionAttribute::findByName('D1_Division');
    if(dimAttribute)
    {
        dimAttrSetStorage.addItem( dimAttribute.RecId, dimAttribute.HashKey, NoYes::Yes );
    }
    // D3_ConsGroup
    dimAttribute = DimensionAttribute::findByName('D3_ConsGroup');
    if(dimAttribute)
    {
        dimAttrSetStorage.addItem( dimAttribute.RecId, dimAttribute.HashKey, NoYes::Yes );
    }

    dimEnumeration = dimAttrSetStorage.save();
    dimensionDefaultingController.setEditability( true, dimEnumeration );

Read a given segment value for a given account from the GL

Assuming that one has to read all the values from a MainAccount '20151026' where the Employee dimension 'D6_Employee' = 'E0028'

Below is a sample job for the same 
static void Job10(Args _args)
{
    GeneralJournalAccountEntry          GJAE;
    GeneralJournalEntry                 GJE;
    FiscalCalendarPeriod                FCP;
    SubledgerVoucherGeneralJournalEntry SLVGJE;
   
    DimensionAttributeValueGroupCombination DAVGC;
    DimensionAttributeLevelValue            DALV;
    DimensionAttributeValue                 DAV;
    DimensionAttribute                      DA;
    SPYAmount                               claimAmount;
    HcmPersonnelNumberId                    personnelNumber = HcmWorker::find(5637146086).PersonnelNumber;
   
    while select *
    from GJAE
    join GJE   
    where GJE.RecId == GJAE.GeneralJournalEntry       
        && GJE.AccountingDate >= mkDate(1,1,2017)
        && GJE.AccountingDate <= mkDate(31,1,2017)
        && GJAE.MainAccount == MainAccount::findByMainAccountId('20151026').RecId
    join SLVGJE
        where SLVGJE.GeneralJournalEntry == GJE.RecId
        && SLVGJE.VoucherDataAreaId ==  curext()  
        //reading the individual segments of the entry
    join DAVGC
        where DAVGC.DimensionAttributeValueCombination == GJAE.ledgerDimension
    join DALV
        where DALV.DimensionAttributeValueGroup == DAVGC.DimensionAttributeValueGroup                   
        && DALV.DisplayValue == personnelNumber
    join DAV
        where DAV.RECID == DALV.DimensionAttributeValue
    join DA
        where DA.RecID == DAV.DimensionAttribute       
        && DA.Name == 'D6_Employee'        
    {       
        claimAmount += GJAE.AccountingCurrencyAmount;
    }
}