Post#23: GL – Advanced Rules via SQL Query

Tracking all the Accounting Structures and advanced rules created across all entities in AX is a daunting task especially if you have a lot of entities. A lot of users try to do this manually but there is a better way to document and verify the same via SQL.

The following tables hold the required info you need, understanding how they work is key:

• DimensionHierarchy: Different Structure and Dimensions sets are stored here (e.g Account Structure, Advanced Rules, Journal Control, Dimension sets, built in dimensions all differentiated based on structure type field). MSDN link

• DimensionHierarchyLevel: Defines no. of levels of the dimension hierarchy. MSDN link

• DimensionAttribute: Name of the dimension attribute (e.g dimensions + ledger). MSDN link

• DimensionRuleAppliedHierarchy: Maps Rules to the Hierarchy

• Dimension Rule: Stores the Advanced Rules. MSDN link

• DimensionRuleCriteria: Stores the filters on the Advanced Rule Structures. MSDN link

• DimensionConstraintNode: Defines constraints on the node eg. Allow blanks. MSDN link

I have mapped the same visually to understand better:

Based on this I proceeded with the SQL creation, kindly see the below. Keep in mind that since the DimensionHierarchy contains details for each type it is easier to use separate queries by type rather than one query for all. The below query is for Advanced rules but has a matrix to view the associated accounting structure as well as the advanced rule structure.

--Advanced rules structure by Ashwin Padmanabhan
select Structure_Type=
case
when dh.STRUCTURETYPE=0 then 'Account Structure'
when dh.STRUCTURETYPE=1 then 'Advanced rule Structure'
when dh.STRUCTURETYPE=2 then 'Journal Control Structure'
when dh.STRUCTURETYPE=6 then 'Dimension Set'
when dh.STRUCTURETYPE=7 then 'Customer'
when dh.STRUCTURETYPE=8 then 'Vendor'
when dh.STRUCTURETYPE=9 then 'Project'
when dh.STRUCTURETYPE=10 then 'Fixed Asset'
when dh.STRUCTURETYPE=11 then 'Bank Account'
when dh.STRUCTURETYPE=12 then 'Employee'
when dh.STRUCTURETYPE=13 then 'Item'
when dh.STRUCTURETYPE=14 then 'Single Dimension attribute Rule Structure'
when dh.STRUCTURETYPE=16 then 'Default Account'
when dh.STRUCTURETYPE=16 then 'All dimension attribute '
end,
Accounting_Structure=dh2.name,
Advanced_Rule=dr.NAME,
Advanced_Rule_Structure_ID=dh.name,
Advanced_Rule_Structure_Name=dh.DESCRIPTION,
dh.ISDRAFT,
dhl.LEVEL_,
da.NAME,
Filter_on=isnull(da2.NAME,''),
Filter_on_range_from=isnull(drc.rangefrom,''),
Filter_on_range_to=isnull(drc.RANGETO,'')
,dcn.ISOPTIONAL
from DIMENSIONHIERARCHY dh with(nolock) --store advanced rule structures info
left outer join DIMENSIONHIERARCHYLEVEL dhl with(nolock) on dhl.DIMENSIONHIERARCHY=dh.RECID
left outer join DIMENSIONATTRIBUTE da with(nolock) on da.RECID=dhl.DIMENSIONATTRIBUTE
left outer join DIMENSIONRULEAPPLIEDHIERARCHY drah with(nolock) on drah.DIMENSIONHIERARCHY= dh.RECID
left outer join dimensionrule dr with(nolock) on dr.RECID= drah.DIMENSIONRULE
left outer join DIMENSIONHIERARCHY dh2 with(nolock) on dh2.RECID=dr.ACCOUNTSTRUCTURE
left outer join dimensionrulecriteria drc with(nolock) on drc.DIMENSIONRULE=dr.RECID
left outer join DIMENSIONATTRIBUTE da2 with(nolock) on da2.RECID=drc.DIMENSIONATTRIBUTE
left outer join DIMENSIONCONSTRAINTNODE dcn with(nolock) on dcn.DIMENSIONHIERARCHYLEVEL=dhl.RECID
Where dh.STRUCTURETYPE=1 --only advanced rules
order by dh2.name,dr.NAME,dh.name,dhl.LEVEL_

I hope this helps you. If it does please like or comment.

Thank for for taking the time to read this.

Happy daxing ppl.