Dynamics Community Forum Thread Details

Query default financial dimensions for Fixed Assets

I am working on a BI solution and am looking to query the default financial dimensions for fixed assets (eventually will need them in other areas as well). I need to pull in all 3 fields of the dimension, as shown in the screen shot below. I have a query that seems to work, but it doesn't seem like the most elegant solution possible. I am wondering if I have the query correct and/or if there are standard AX views that I can use to get this information as well? My query is below the screen shot. Thanks in advance for the help.

SELECT abk.ASSETID
,abk.BOOKID
,da.NAME
,davsi.DISPLAYVALUE
,coalesce(dft.DESCRIPTION,bu.NAME,dept.NAME) AS Name2
FROM dbo.ASSETBOOK abk
INNER JOIN dbo.DIMENSIONATTRIBUTEVALUESET davs
ON davs.RECID = abk.DEFAULTDIMENSION
INNER JOIN dbo.DIMENSIONATTRIBUTEVALUESETITEM davsi
ON davsi.DIMENSIONATTRIBUTEVALUESET = davs.RECID
INNER JOIN dbo.DIMENSIONATTRIBUTEVALUE dav
ON dav.RECID = davsi.DIMENSIONATTRIBUTEVALUE
INNER JOIN dbo.DIMENSIONATTRIBUTE da
ON da.RECID = dav.DIMENSIONATTRIBUTE
LEFT OUTER JOIN dbo.DIMENSIONFINANCIALTAG dft
ON davsi.DISPLAYVALUE = dft.VALUE
AND da.VIEWNAME = 'DimensionFinancialTag'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMDEPARTMENT dept
ON davsi.DISPLAYVALUE = dept.VALUE
AND da.VIEWNAME = 'DimAttributeOMDepartment'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMBUSINESSUNIT bu
ON davsi.DISPLAYVALUE = bu.VALUE
AND da.VIEWNAME = 'DimAttributeOMBusinessUnit'