Monday, November 19, 2012

Financial Dimension Lookup for AX 2012

If you just took the dive into AX 2012, you may have noticed quite a change in how financial dimensions are stored.  I'm here to bring light to how to access the dimension values with the new structure so that you may use them as you did in the 4.0/2009 system.

As a review: In AX 4.0 and 2009, a business was limited to the number of financial dimensions they wanted, I believe the default amount of dimensions was 3, but additional dimensions could be purchased and was regulated by the license file provided by Microsoft when AX is purchased.  The various tables that did utilize these financial dimensions simply had a column for each dimension, namely Dimension[1], Dimension[2], Dimension[3], etc... (in SQL: Dimension, Dimension2_, Dimension3_, etc...) depending on how many dimensions your business purchased, of course.  Each one of these fields stored the actual value of the dimension right there in that table.  If you wanted to reference the name or description of that Dimension, it was easy.  AX had a Dimensions table where you could just do a Dimensions::find() and provide the dimension value stored in the above table and your DimensionCode enum type (to specify which dimension you are supplying).  Presto, upon returning the Dimensions record, you reference the Description field and you have the dimension description/name.

AX 2012 handles this quite differently...

Instead of there being a limit on the number of financial dimensions, the team at Microsoft found a way to lift that restriction and now allow for infinite dimensions.  Yahoo!  But it doesn't come for free (don't think monetarily).  The price you pay is the complexity of it.  Instead of storing each of the dimensions on a given table, their solution is to store a reference (to the related table's RecId) of a record in a centralized dimension table in a column called DefaultDimension.  This is the value that will eventually get you to your dimensions.

If you don't care about a technical data structure oversight and just want a simple solution, you can skip down to the solution below, otherwise continue reading (this is starting to feel like a Goosebumps "Give Yourself" book).

The centralized dimesion table I was referring to is DimensionAttributeValueSetItem.  The DimensionAttributeValueSet field in this table is going to be the field that DefaultDimension relates to.  For each financial dimension that is specified for a record, there will be that many records in the DimensionAttributeValueSetItem table.  There is a field called DisplayValue in this table, this is the value of the dimension!  Yahoo!  Don't get too excited yet, there are no values in this table that will tell us which financial dimensions are which.  We will need to join to DimensionAttributeValue and again to DimensionAttribute first.  From here it is tricky if you're looking to find the human-readable description in addition, depending on what kind of dimension it is, it will link to a different table.  I suggest only linking to the tables you need to.  If you have custom dimensions, you will outer join to DimensionAttributeDirCategory and DimensionFinancialTag (as shown below).  Any other types of dimensions link to various Views with a "DimAttribute" prefix but also must be linked to the current company that is selected.  I've included an example of linking to Customer and Project dimensions.  Below you will find a SQL query which will define the join criteria.
SELECT davsi.DisplayValue, da.Name, dft.Description, dact.Name, dapt.Name
FROM DimensionAttributeValueSetItem davsi
JOIN DimensionAttributeValue dav
    ON dav.RecId = davsi.DimensionAttributeValue
JOIN DimensionAttribute da
    ON da.RecId = dav.DimensionAttribute
--Custom dimensions
LEFT JOIN DimensionAttributeDirCategory dadc
    ON dadc.DimensionAttribute = da.RecId
LEFT JOIN DimensionFinancialTag dft
    ON dft.FinancialTagCategory = dadc.DirCategory
AND dft.RecId = dav.EntityInstance
--Customer dimension
LEFT JOIN DimAttributeCustTable dact
    ON dact.Value = davsi.DisplayValue
    AND dact.RecId = dav.EntityInstance
    AND dact.DataAreaId = 'dat'
--Project dimension
LEFT JOIN DimAttributeProjTable dapt
    ON dapt.Value = davsi.DisplayValue
    AND dapt.RecId = dav.EntityInstance
    AND dapt.DataAreaId = 'dat'
WHERE davsi.DimensionAttributeValueSet = '5637144584'
--where '5637144584' is the DefaultDimension reference value
The interesting part to note about the above query is the da.Name field.  This is the AX 2012 equivalent to the DimensionCode Enum, only AX 2012 doesn't use Enums for dimensions (because Enums cannot (and shouldn't) be created dynamically by your end users, but financial dimensions now can be and are meant to be defined by your end users).  So, when coding, you will either need to hard code these dimension names, or create your own Enum or Macro that you must manage separately.  I don't like it either but it is what it is.

SOLUTION

I have developed a couple of helper methods that simply the retrieval of dimension data.  These can be included in a helper class and referenced to as static methods are normally, but I recommend including these in your Global class, then you can just call the method directly.  I have 2 methods: dimValue() and dimDesc(), they return the value and description, respectively.  Code and usage is below:
//Usage for dimValue() and dimDesc()
static void JobDimensionUsage(Args _args)
{
    SalesLine       sl;

    DimensionValue  value;
    Description     desc;
    ;
    select firstonly sl;

    value = dimValue(sl.DefaultDimension, 'Department');
    desc = dimDesc(sl.DefaultDimension, 'Department');

    info(strfmt("%1: %2", value, desc));
}
static DimensionValue dimValue(RefRecId _defaultDimension, Name _name)
{
    DimensionAttributeValueSetItemView  davsi;
    DimensionAttribute                  da;
    ;
    select DisplayValue from davsi
    where davsi.DimensionAttributeValueSet == _defaultDimension
    join RecId from da
    where da.RecId == davsi.DimensionAttribute
        && da.Name == _name;
 
    return davsi.DisplayValue;
}

static Description dimDesc(RefRecId _defaultDimension, Name _name)
{
    DimensionAttributeValueSetItemView  davsi;
    DimensionAttribute                  da;
    DimensionFinancialTag               dft;
    DimensionAttributeDirCategory       dadc;
    DimAttributeCustTable               dact;
    DimAttributeProjTable               dapt;
    ;
    select DimensionAttributeValueSet from davsi
    where davsi.DimensionAttributeValueSet == _defaultDimension
    join RecId from da
    where da.RecId == davsi.DimensionAttribute
        && da.Name == _name
    outer join Name from dadc
    where dadc.DimensionAttribute == da.RecId;
    outer join Description from dft
    where dft.RecId == dav.EntityInstance
        && dft.FinancialTagCategory == dadc.DirCategory
    outer join Name from dact
    where dact.RecId == dav.EntityInstance
        && dact.Value == davsi.DisplayValue
    outer join Name from dapt
    where dapt.RecId == dav.EntityInstance
        && dapt.Value == davsi.DisplayValue;
 
    return (dft.Description ? dft.Description : (dact.Name ? dact.Name : (dapt.Name ? dapt.Name : "")));
}

8 comments:

  1. Perfect................. thanks!!!!!!
    Sail

    ReplyDelete
  2. Hi
    I tried your method but I dont get values from dimDesc method.

    I have eg. one department number '1520' this is retrieved but the Name aint.

    Do you have a suggestion ?

    Otherwise this is a good method - thanks

    BR
    Jan

    ReplyDelete
    Replies
    1. I think you might have this backwards. The dimDesc is expecting the 'Name' as input, this is not what is being outputted.

      Delete
  3. dimdesc method in example works incorrectly. Should be :

    static Description dimDesc(RefRecId _defaultDimension, Name _name)
    {
    DimensionAttributeValueSetItemView davsi;
    DimensionAttribute da;
    DimensionFinancialTag dft;
    ;

    select DimensionAttribute from davsi
    where davsi.DimensionAttributeValueSet == _defaultDimension
    join RecId from da
    where da.RecId == davsi.DimensionAttribute
    && da.Name == _name
    join Description from dft
    where dft.RecId == davsi.DimensionAttribute;

    return dft.Description;
    }

    ReplyDelete
  4. hi, i had problems with the method dimDesc too. i'm using R2 CU7 and what i have is custom dimension not created by any templates.. i need to pass by the DimensionAttributeDirCategory table to get it works..

    static Description getDimAttDesc(RefRecId _defaultDimension, Name _name)
    {
    DimensionAttribute da;
    DimensionAttributeDirCategory dadc;
    DimensionAttributeValueSetItemView davsi;
    DimensionFinancialTag dft;
    ;
    select DimensionAttributeValueSet from davsi
    where davsi.DimensionAttributeValueSet == _defaultDimension
    join RecId from da
    where da.RecId == davsi.DimensionAttribute
    && da.Name == _name
    join RecId from dadc
    where dadc.DimensionAttribute == davsi.DimensionAttribute
    join Description from dft
    where dft.FinancialTagCategory == dadc.DirCategory
    && dft.Value == davsi.DisplayValue;

    return dft.Description;
    }

    ReplyDelete
    Replies
    1. hiii i want to change the look up for financial dimensions in all purhase order
      means eg: here lookup is created on analysis name but i want the lookup on beside sting edit in finacial dimensions
      please help me

      Delete
  5. Hi,

    For me there is a mistake in your method dimDesc

    static Description dimDesc(RefRecId _defaultDimension, Name _name)
    {
    DimensionAttributeValueSetItemView davsi;
    DimensionAttribute da;
    DimensionFinancialTag dft;
    ;
    select DimensionAttributeValueSet from davsi
    where davsi.DimensionAttributeValueSet == _defaultDimension
    join RecId from da
    where da.RecId == davsi.DimensionAttribute
    && da.Name == _name
    join Description from dft
    //where dft.RecId == davsi.AttributeValueRecId; //it's wrong
    where dft.recId == davsi.EntityInstance;

    return dft.Description;
    }


    Kind regards
    Dona

    ReplyDelete
  6. I've been confused on this issue for quite some time, why it works in some environments, but not others. I've finally had a chance to dig further into it. I'll update this blog post in the next few days to include my findings. The information in the blog above is true, if and only if you are using custom dimensions on all dimensions. There was the find by Andre above that Category should be linked in to complete the picture there. However, if you create a new dimension and choose any other value beside Custom dimension in the "use values from", depending on which one you choose, it will look at a different table for it's values. Since there are about 30-some options to choose from, the query required would need to join to 30 different tables.

    In actuality, AX uses various Views to reference the information, most commonly with the prefix "DimAttribute", each of which have a Key, Value, and Name column (although Key equals RecId, so we'll just use that) (and Name would be the Description as FinancialTag stores it). In addition, these DimAttribute Views all have a DataAreaId associated with it, so it will be important to filter it by the current company. Also, instead of joining to each of the 30, I think the best solution would be to individually choose which Views to link to depending on which type of financial dimensions your company is using. The SQL query I've come up with to demonstrate this is below, feel free to convert it to X++ from this:

    SELECT davsi.DisplayValue, da.Name, dft.Description, dapt.Name, dact.Name
    FROM DimensionAttributeValueSetItem davsi
    JOIN DimensionAttributeValue dav
    ON dav.RecId = davsi.DimensionAttributeValue
    JOIN DimensionAttribute da
    ON da.RecId = dav.DimensionAttribute
    --Custom dimension
    LEFT JOIN DimensionAttributeDirCategory dadc
    ON dadc.DimensionAttribute = dav.DimensionAttribute
    LEFT JOIN DimensionFinancialTag dft
    ON dft.FinancialTagCategory = dadc.DirCategory
    AND dft.RecId = dav.EntityInstance
    --Customer dimension
    LEFT JOIN DimAttributeCustTable dact
    ON dact.Value = davsi.DisplayValue
    AND dact.RecId = dav.EntityInstance
    AND dact.DataAreaId = 'dat'
    --Project dimension
    LEFT JOIN DimAttributeProjTable dapt
    ON dapt.Value = davsi.DisplayValue
    AND dapt.RecId = dav.EntityInstance
    AND dapt.DataAreaId = 'dat'

    Ideally, I think the better solution would be to create a Union Query to union all of these Views together and uniformly link to it to get all the dimension descriptions in the same column, that way it's also easier to report on from the SQL side. However, the concern I see is having RecId's from the various tables colliding with each other and AX not knowing which record to use. This would occur if you use the same financial dimension values in other dimensions. Perhaps there is a creative way to add a new column in this new View to dictate what kind of dimension it is; perhaps TableId or TableId of the relating table, but we'd somehow need it to relate to data that is only available in the DimensionAttribute tables.

    ReplyDelete