Overview
The dimension structure in AX 2012 and later has changed significantly from AX 2009 and earlier. The dimension structure now follows a dimension set methodology and key dimensions are no longer posted into the transaction tables as they were previously, but rather there are lookup tables that contain the dimension information and the transaction table now just contains a single key. This article describes a few methods to bring in the dimensions to a project using the Jet Data Manager.
Process
Dimensions are handled in a few similar, but slightly different, ways in AX 2012. Some transaction tables will contain the financial dimensions in the form of a LEDGERDIMENSION field while others will contain the dimensions in the form of the DEFAULTDIMENSION field. We will discuss adding financial dimensions first.
-
Financial dimensions are stored in a number of tables but we have simplified the process by adding a Dimensions view to the staging database.
The three fields in this view represent:
LEDGERDIMENSION: This is the dimension ID and will be used to join the dimension values to the transaction tables
DIMENSIONNAME: This is the name given to the dimension by the organization and will represent things like "Department", "CostCenter", etc.
VALUE: This is the actual value for the dimension and will represent the actual Department Code associated with the transaction
In this example we will add both the Department and CostCenter dimensions to the GENERALJOURNALACCOUNTENTRY table. For each dimension you want to add you will drag the VALUE field from the Dimensions view and drop it on the GENERALJOURNALACCOUNTENTRY table. You can then rename the conditional lookup fields to represent the actual dimensions be pulled as represented below.
-
You will then right-click the Join node, click Add Join, and set a join where LEDGERDIMENSION = LEDGERDIMENSION and click OK
-
You will then add another join to specify which dimension you want to bring in. To do this you will change the Lookup Field dropdown to be DIMENSIONNAME, click the Fixed radio button, and then type in the dimension name in the Fixed Value field.
In this example the first dimension is the Department dimension.
The finished result after both dimensions have had the joins added will look like this:
-
You can then deploy and execute the table to commit the changes and have the values populated into the transaction table.
-
You can now add these fields to the desired table in the data warehouse for the end users.
Other tables in AX may use the DEFAULTDIMENSION field to store the dimension value.
There is a DEFAULTDIMENSIONVIEW object that can be added as a new table in the staging database that will contain the necessary values to pull in data using the same steps described above. The fields that should be pulled in from this object are:
DEFAULTDIMENSION: This is the dimension ID that will be used to join the dimension to the transaction table
NAME: This is the dimension name, such as Department, CostCenter, etc.
DISPLAYVALUE: This is the actual value for the dimension and will represent the actual Department Code associated with the transaction
You will bring the DISPLAYVALUE field from the DEFAULTDIMENSIONVIEW table and drop it on the desired transaction table and then follow similar steps above to join on the DEFAULTDIMENSION field in both tables and then apply a fixed filter to bring in the desired dimension. Below is a screenshot of bringing the Department dimension to the CUSTTRANS table.
These steps can then be repeated for all desired dimensions.
Comments