In doing some analysis work on performance, a consultant recently asked for an extract of all of our AX2012’s COA validation rules.
The below SQL is an attempt at regenerating the rules based off the data in the various related tables.
NB: I’m not 100% certain that this gives all rules as they’d appear in the application, but a few initial tests prove promising; so hopefully this SQL can be reused by others with similar requirements.
Here’s a small sample of our validation rules from AX:
Here’s the same rules, as produced by the below SQL:
Full SQL:
set transaction isolation level read uncommitted go ;with c1 as ( select DimensionConstraintNode , STUFF ( ( SELECT ';' + case when WILDCARDSTRING > '' then WILDCARDSTRING when RangeFrom=RangeTo then RangeFrom else concat(RangeFrom,'..',RangeTo) end FROM DimensionConstraintNodeCriteria inside where inside.DimensionConstraintNode = outside.DimensionConstraintNode ORDER BY ORDINAL, RECID FOR XML PATH (''),TYPE ).value('.','nvarchar(max)') ,1,1,'' ) AS Rules from DimensionConstraintNodeCriteria outside group by DimensionConstraintNode ) , ActiveDCN as ( select dct.dimensionhierarchy , dcn.DIMENSIONCONSTRAINTTREE , dhl.level_ , dhl.dimensionattribute , da.name AttributeName , dcn.dimensionhierarchylevel , dcn.PARENTCONSTRAINTNODE , dcn.RecId , c1.Rules from DimensionConstraintTree dct inner join DIMENSIONHIERARCHYLEVEL dhl on dhl.dimensionhierarchy = dct.dimensionhierarchy inner join DIMENSIONATTRIBUTE da on da.recid = dhl.dimensionattribute inner join DimensionConstraintNode dcn on dcn.DIMENSIONCONSTRAINTTREE = dct.recid and dcn.dimensionhierarchylevel = dhl.RECID left outer join c1 on c1.DimensionConstraintNode = dcn.recid where (dcn.activeto = '1900-01-01 00:00' or dcn.activeto >= getutcdate()) and (dcn.activefrom = '1900-01-01 00:00' or dcn.activefrom <= getutcdate()) ) select dh.name HierarchyName , concat(dcn1.AttributeName,': ',coalesce(dcn1.Rules,'*')) R1 , concat(dcn2.AttributeName,': ',coalesce(dcn2.Rules,'*')) R2 , concat(dcn3.AttributeName,': ',coalesce(dcn3.Rules,'*')) R3 , concat(dcn4.AttributeName,': ',coalesce(dcn4.Rules,'*')) R4 from DIMENSIONHIERARCHY dh inner join ActiveDCN dcn1 on dcn1.dimensionhierarchy = dh.recid and dcn1.level_ = 1 --not sure if level’s required; it does affect results; keeping in for now as improves performance (though also reduces result set by 75%) left outer join ActiveDCN dcn2 on dcn2.ParentConstraintNode = dcn1.Recid --and dcn2.level_ = 2 left outer join ActiveDCN dcn3 on dcn3.ParentConstraintNode = dcn2.Recid --and dcn2.level_ = 3 left outer join ActiveDCN dcn4 on dcn4.ParentConstraintNode = dcn3.Recid --and dcn2.level_ = 4 --where dcn1.Rules like '%640103%' --to produce screenshot order by dh.name, dcn1.DIMENSIONCONSTRAINTTREE