For the sake of an example, let's consider the following LedgerDimension value:
1200120-INR2001
The above display value has multiple parts, each seperated by a (-) hyphen. Lets call each part a Segment and the value of a segment as the segment value.
When we look at the display value of a dimension then only the segmentValues are visible, however the segment names are not known. The segment names are derived from the account structure that has been enabled on the concerned account.
As it is possible to change the account structure in AX, it is also possible that the segment names currently active are different from the ones which were active when a particular LedgerDimension value was entered.
The details below will help us understand this concept better.
Firstly dimensions are of two types: -
As we know in AX the dimension combinations are uniquely created and assigned a RecID which is then reused on records with the same dimension value. The dimension combinations are of two types
select DAVGI.DimensionAttributeValueCombination, DA.Name, DALV.DisplayValue
from dimensionAttributeValueGroupCombination DAVGI
inner join dimensionAttributeLevelValue DALV
on DALV.DimensionAttributeValueGroup = DAVGI.DimensionAttributeValueGroup
inner join dimensionAttributeValue DAV
on DAV.RECID = DALV.DimensionAttributeValue
inner join DimensionAttribute DA
on DA.RecID = DAV.DimensionAttribute
order by DAVGI.DimensionAttributeValueCombination, DALV.Ordinal
the above statement will return each CombinationID for LedgerDimension, segmentName, segmentValue
1200120-INR2001
The above display value has multiple parts, each seperated by a (-) hyphen. Lets call each part a Segment and the value of a segment as the segment value.
When we look at the display value of a dimension then only the segmentValues are visible, however the segment names are not known. The segment names are derived from the account structure that has been enabled on the concerned account.
As it is possible to change the account structure in AX, it is also possible that the segment names currently active are different from the ones which were active when a particular LedgerDimension value was entered.
The details below will help us understand this concept better.
Firstly dimensions are of two types: -
- Lookup Dimenions: These are lookup to an existing master in AX. The dimensions are stored in two tables namely, DimensionAttribute (for dimension name) and DimensionAttributeValue (for dimension values, There is a EntityInstance field in this table. That’s the relation to the value original table.)
- Custom : These are custom defined values and do not exist elsewhere within AX. These are stores in two custom tables FinancialTagCategory (for dimension name) and DimensionFinancialTag (for dimension values)
As we know in AX the dimension combinations are uniquely created and assigned a RecID which is then reused on records with the same dimension value. The dimension combinations are of two types
- DefaultDimension : Default dimension is stored in
- DimensionAttributeValueSet : A grouping of unique values denoting a combination.
- DimensionAttributeValueSetItem : individual items in a combination
select DAVSI.DimensionAttributeValueSet, DA.Name, DAVSI.DisplayValue
from DimensionAttributeValueSetItem DAVSI
inner join DimensionAttributeValue DAV
on DAV.RecID = DAVSI.DimensionAttributeValue
inner join DimensionAttribute DA
on DA.RecID = DAV.DimensionAttribute
the above statement will return each CombinationID, SegmentName, SegmentValue
from DimensionAttributeValueSetItem DAVSI
inner join DimensionAttributeValue DAV
on DAV.RecID = DAVSI.DimensionAttributeValue
inner join DimensionAttribute DA
on DA.RecID = DAV.DimensionAttribute
the above statement will return each CombinationID, SegmentName, SegmentValue
- LedgerDimension: Ledger dimension is stored in
- DimensionAttributeValueCombination: Stores a combination of unique values denoting a ledger dimension.
- DimensionAttributeValueGroup: Stores dimension group
- DimensionAttributeValueGroupCombination : Store relation of DimensionAttributeValueGroup and DimensionAttributeValueCombination
- DimensionAttributeLevelValue: Stores dimension value of ledger dimension
select DAVGI.DimensionAttributeValueCombination, DA.Name, DALV.DisplayValue
from dimensionAttributeValueGroupCombination DAVGI
inner join dimensionAttributeLevelValue DALV
on DALV.DimensionAttributeValueGroup = DAVGI.DimensionAttributeValueGroup
inner join dimensionAttributeValue DAV
on DAV.RECID = DALV.DimensionAttributeValue
inner join DimensionAttribute DA
on DA.RecID = DAV.DimensionAttribute
order by DAVGI.DimensionAttributeValueCombination, DALV.Ordinal
the above statement will return each CombinationID for LedgerDimension, segmentName, segmentValue