AX 2012 dimension sql code snippet

To fetch an AX 2012 dimension into a sql report it is sometimes a good choice to prepare a CTE for each dimension and then use the cte in the main query below:

cteRegion as (
select distinct v.partition, v.valuecombinationrecid, b.value,
from DimAttributeOMBusinessUnit b
left join dimensionattributelevelvalueallview v on
v.dimensionattribute = (select top 1 recid from dimensionattribute WHERE DIMENSIONATTRIBUTE.NAME = ‘Region’)
and v.entityinstance = b.recid)