Fernitudela.dev

This post/video is going to focus in the D365FO dimensions tables structures and how to get the descriptions for all your dimensions in a generic way, without having to go to the specific table in each case. In a previous post we have seen also a little bit of this topic and how to show the dimension values in a grid.

The method to get the description of the dimensions

Here you are the code to get the description, this is a piece of code you can include in your repository and use it in many of your implementations. I hope it is useful for you, however, before copying and using it, my recommendation is to watch the video above or to keep reading this post so you can understand how it works internally and to know more about the dimensions.

    /// <summary>
    /// Returns the dimension description of a specific dimension on a combination.
    /// </summary>
    /// <param name = "_dimensionDefault">RecId of the DimensionAttributeValueSet</param>
    /// <param name = "_dimensionName">Dimension name in the DimensionAttribute</param>
    /// <returns>return</returns>
    public static str getDimensionDescription(DimensionDefault _dimensionDefault, DimensionRefFieldName _dimensionName)
    {
        DimensionAttribute          dimensionAttribute;
        DimensionAttributeValueSet	dimensionAttributeValueSet;
        DictTable					dimAttrViewDictTable;
        str							dimensionDescription;
        Common						dimAttrViewGeneric;
        FieldId						keyColumnFieldId;
        ;
        select firstonly dimensionAttributeValueSet
            where dimensionAttributeValueSet.RecId		== _dimensionDefault;
        dimensionAttribute			= DimensionAttribute::findByName(_dimensionName);
     
        dimAttrViewDictTable	    = new DictTable(dimensionAttribute.BackingEntityType);
        dimAttrViewGeneric		    = dimAttrViewDictTable.makeRecord();
        keyColumnFieldId			= fieldName2Id(tableNum(DimensionAttributeValueSet), dimensionAttribute.DimensionKeyColumnName);
        select firstonly dimAttrViewGeneric
            where dimAttrViewGeneric.(dimensionAttribute.KeyAttribute) == dimensionAttributeValueSet.(keyColumnFieldId);
        dimensionDescription		= dimAttrViewGeneric.(dimensionAttribute.NameAttribute);
        return dimensionDescription;
    }

Dimensions setup in DimensionAttribute

If we go to the DimensionAttribute table, we can find there all the setup for our dimensions, we are going to need here 4 fields: DimensionKeyColumnName, BackingEntityType, KeyAttribute and NameAttribute.

In our case we will use the DimensionAttributeValueSet to locate the Financial Dimension value in the first case using the DefaultDimension and the DimensionName, therefore we are going to use the DimensionKeyColumnName, as we did to create the ComputedColumns in the previous Post how to show the dimension values in a grid, to get the Value directly from the DimensionAttributeValueSet table fields that are non accessible from VS and that contains the values of each specific dimension. Check the previous post if you don’t know what I am talking about.

The BackingEntityType, stores the table Id of the table where our dimension values and descriptions are stored. There are 2 types of dimensions, the ones based in custom values that use the table DimensionFinancialTag, and the ones based on actual OOB tables, that use one of the DimAttribute… views. We can find as many DimAttribute… views as eligible tables to be used as dimensions.

In consequence, we know from which table we can get our dimension value and description for each specific dimension, and then we can use the other 2 fields: KeyAttribute and NameAttribute. Those fields store basically the Ids of the fields that are the key (RecId) and the name (description value) respectively in the afore mentioned table (defined by the BackingEntityType Field). We are going to use the KeyAttribute to get the record using the value from the DimensionAttributeValueSet and will get the description afterward, Using DictTable and Common.(FieldId), check this post where it is explained in further detail.

Visualization of the DimensionAttribute table data used in our method

I have done this SQL Query to illustrate this setup, using the views SysTableIdViews and SysTableFieldIdView:

select DimensionAttribute.Name as DimensionName, 
		BackingEntityType, 
		KeyAttribute, 
		NameAttribute, 
		SysTableIdView.Name as DimensionTableName, 
		SYSTABLEFIELDIDVIEW.Name as DimIdFieldName, 
		DescriptionField.Name as DimDescFieldName
from DimensionAttribute
inner join SysTableIdView on backingEntityType = SysTableIdView.Id
inner join SYSTABLEFIELDIDVIEW on SYSTABLEFIELDIDVIEW.Id = SysTableIdView.Id and SYSTABLEFIELDIDVIEW.FIELDID = KEYATTRIBUTE
inner join SYSTABLEFIELDIDVIEW DescriptionField on DescriptionField.Id = SysTableIdView.Id and DescriptionField.FIELDID = NAMEATTRIBUTE
where DimensionAttribute.NAME in ('CostCenter',
								   'Department',
								   'RetailChannel',
								   'Program',
								   'Customer')

Conclusion

So, knowing and understanding the way the Dimensions are structured is vital for any D365FO developer that eventually will need to deal with them. Using the DimensionAttribute table is vital to be able to get the setup information for any dimension and for doing your code generic and easily maintainable, as we did here to create the method which gets the dimension descriptions for any given dimension that was setup in your project. Thanks for reading it until the end and hope it was useful for you, see you next time!