I am a bit lost when it comes to the new Dimensions data-model in Ax2012. I created the query below to get the Dimensions as applied to a CUSTTRANS record.
SELECT
CT.RECID,
DAVS.RECID,
DAVSI.RECID,
DISPLAYVALUE
FROM
dbo.CUSTTRANS AS CT
LEFT OUTER JOIN
dbo.DIMENSIONATTRIBUTEVALUESET AS DAVS ON DAVS.RECID = CT.DEFAULTDIMENSION
LEFT OUTER JOIN
dbo.DIMENSIONATTRIBUTEVALUESETITEM AS DAVSI ON DAVS.RECID = DAVSI.DIMENSIONATTRIBUTEVALUESET
The query above does not tell me what type of Dimension I am looking at. It’s nice to have it’s value but knowing the type as well is quite preferable.
Furthermore:
As I understood it is since Ax2012 possible to create unlimited dimensions and the order of the dimensions applied to an CUSTTRANS or VENDTRANS record isn’t consequent. At one time a Cost centre can be the first dimension and a few records on it could be the second dimension and again a few records on it could be the 99th dimension.
Is there a table in which I can see which Dimensions have been used? Is this year specific?
As shown above I JOIN on DEFAULTDIMENSION, this functions in CUSTTRANS and VENDTRANS. I was told that the LEDGERTRANS has been replaced by the GENERALJOURNALENTRY table. How do I join this table to it’s dimensions? It does not have a DEFAULTDIMENSION field.
Coming back here 'cos I found out something but do definitely lack certainty.
Each trans record (CUSTTRANS, VENDTRANS, LEDGERJOURNALTRANS) that has a value in DEFAULTDIMENSION has a DISPLAYVALUE which has so far been accepted by me as being the dimension I need to represent. However, the table DIMENSIONATTRIBUTE has a few more interesting columns in there as well: BACKINGENTITYTABLENAME and BACKINGENTITYFIELDNAME.
In some of the cases BACKINGENTITYTABLENAME points to a table named DimensionFinancialTag. When I do a ‘SELECT *’ over this table I see records that more look like dimensions as I used to know them.
The same goes for OMOperatingUnit ( if you join it with the DIRPARTYTABLE that is).
If I query the following … SELECT BACKINGENTITYTABLENAME FROM dbo.DIMENSIONATTRIBUTE GROUP BY BACKINGENTITYTABLENAME ORDER BY BACKINGENTITYTABLENAME
… I end up with:
Are this all the possible DimensionTypes? Why is number 10 referencing OmOperatingUnit and not OmOperatingUnitView?
Querying the query below … SELECT * FROM ( SELECT DA.REPORTCOLUMNNAME AS ID, DA.NAME AS Description, DA.BACKINGENTITYTABLENAME AS RefTable, CT.DATAAREAID FROM dbo.CUSTTRANS AS CT LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUESET AS DAVS ON DAVS.RECID = CT.DEFAULTDIMENSION LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUESETITEM AS DAVSI ON DAVS.RECID = DAVSI.DIMENSIONATTRIBUTEVALUESET LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVSI.DIMENSIONATTRIBUTEVALUE LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTE AS DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE UNION ALL SELECT DA.REPORTCOLUMNNAME AS ID, DA.NAME AS Description, DA.BACKINGENTITYTABLENAME AS RefTable, CT.DATAAREAID FROM dbo.VENDTRANS AS CT LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUESET AS DAVS ON DAVS.RECID = CT.DEFAULTDIMENSION LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUESETITEM AS DAVSI ON DAVS.RECID = DAVSI.DIMENSIONATTRIBUTEVALUESET LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVSI.DIMENSIONATTRIBUTEVALUE LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTE AS DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE UNION ALL SELECT DA.REPORTCOLUMNNAME AS ID, DA.NAME AS Description, DA.BACKINGENTITYTABLENAME AS RefTable, CT.DATAAREAID FROM dbo.LEDGERJOURNALTRANS AS CT LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUESET AS DAVS ON DAVS.RECID = CT.DEFAULTDIMENSION LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUESETITEM AS DAVSI ON DAVS.RECID = DAVSI.DIMENSIONATTRIBUTEVALUESET LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVSI.DIMENSIONATTRIBUTEVALUE LEFT OUTER JOIN dbo.DIMENSIONATTRIBUTE AS DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE ) AS Dimensions WHERE DATAAREAID IN (‘ceu’) AND COALESCE(Dimensions.Description, ‘’) <> ‘’ GROUP BY Dimensions.ID, Dimensions.Description, Dimensions.RefTable, Dimensions.DATAAREAID ORDER BY Dimensions.Description
… delivers me …
What are these? Dimension types? And the actual possible dimensions are to be found in the table as shown in the column named RefTable? Is that the way it all works in Ax2012?
I desperately need a forrester 'cos I am not out of the woods yet!
It will help you if you understand what happens in AX, instead of just looking at some data in database. You know the difference between data and information, don’t you?
AX 2012 supports two types of financial dimensions:
System dimensions, i.e. dimensions based on data existing in other tables, e.g. projects. This is clearly what you’re looking at right now.
Custom dimensions, for which you define values manually.
Try it yourself. Go to General ledger > Setup > Financial dimensions > Financial dimensions and create a new dimension. In Use values from field, you can see all supported entities or you can choose < Custom dimension >.
Of course it helps if I would\could understand what happens in Ax but I don’t have an environment. I’ll just have to make do with the database, that’s the whole problem.
I’ll read the white paper, maybe this time I will get to the end of it before dozing off.
