Ledger Financial Dimension Entity Instance

Ledger Financial Dimension Entity Instance

Submitted by Nathan Clouse on 08/29/15.

A view that gives a list of all financial dimensions plus all the usage and hierarchy info behind that entity. This isn't particularly useful on its own but when used with other views is very powerful.

AX Version: 

Module: 

CREATE VIEW [dbo].[vw_ledgerBaseDimensionView] 
AS 
  SELECT davsi.displayvalue, 
         davsi.dimensionattributevalueset, 
         davsi.partition, 
         davsi.recid, 
         dav.entityinstance, 
         da.reportcolumnname, 
         'DimensionAttributeId' = da.recid, 
         da.backingentitytype, 
         da.keyattribute, 
         da.nameattribute, 
         da.NAME 
  FROM   dbo.dimensionattributevaluesetitem davsi 
         CROSS JOIN dbo.dimensionattributevalue dav 
         CROSS JOIN dbo.dimensionattribute da 
  WHERE  ( davsi.dimensionattributevalue = dav.recid 
           AND ( davsi.partition = dav.partition ) ) 
         AND ( dav.dimensionattribute = da.recid 
               AND ( dav.partition = da.partition ) ) 

go