How do I get the location for a row in dbo.CUSTTRA

Question

I am building a small AR data mart for a client which is using Dynamics AX (2012). The data from the mart will feed into a Power BI report. I know almost nothing about AX.

There is a column in the dbo.CUSTTRANS table named DEFAULTDIMENSION. I was told, by the client, that this value can be used to retrieve a location description for the transaction row using the following query:

SELECT 
    T.DEFAULTDIMENSION
    ,T3.Name AS Location
FROM dbo.CUSTTRANS T
    INNER JOIN dbo.DEFAULTDIMENSIONVIEW T2 ON T2.DEFAULTDIMENSION = T.DEFAULTDIMENSION
    INNER JOIN dbo.DIMATTRIBUTEOMBUSINESSUNIT T3 ON T3.RECID = T2.ENTITYINSTANCE
WHERE T.DEFAULTDIMENSION = 5637168346

However, for some values of DEFAULTDIMENSION, like the one above, multiple rows are returned with different descriptions. Is my query incorrect? Or is their data bad? BTW, I would ask the client but the person who would know this won't available until next week.

Answer 1

I think the query is incorrect.

The field DefaultDimension in table CustTrans (as well as others) is used to store a reference to a set of financial dimension and their values (it's a rather complex data structure and logic, which is why I won't go into more detail, but Implementing the Account and Financial Dimensions Framework (White paper) might serve as starting point for more research).

It seems your client interprets the business unit financial dimension as "location" and wants you to report the value of that financial dimension. Note that the term "location" is usually associated with inventory dimensions in Dynamics AX, not financial dimensions.

By filtering T.DEFAULTDIMENSION = 5637168346, you are selecting all CustTrans records that share the exact same set of financial dimensions and values.

By joining to dbo.DimAttributeOMBusinessUnit, each financial dimension in that set is considered the business unit financial dimension.

For DefaultDimension values that reference a set of financial dimensions which only consists of the business unit dimension, the query would only return one (correct) value. But for DefaultDimension values that reference a set with multiple dimensions, the query interprets each dimension as business unit dimension, which produces incorrect results.

If my earlier assumption is correct that your client wants to report only values of the business unit dimension, you need to add an additional filter to the query. It would filter dbo.DefaultDimensionView.BackingEntityType to a value that would correspond with the table id of table dbo.DimAttributeOMBusinessUnit.

I'd like to add that AX already comes with a data mart for BI purposes and contains several BI cubes out of the box, including one for AR. Management Reporter is another solution that can be used to report on financial data and dimensions. A custom built data mart may not be the best fit here.