Get financial dimension value and description

We were posed with the following task the other day, get all financial dimension values and descriptions

But those fields can come from different tables depending on the type of the dimension.
For example, custom dimensions are stored in the DimensionFinancialTag, while Departments will be stored in the DirPartyTable.

To overcome that issue Microsoft introduced several views that will assist developers correctly retrieve financial dimensions.

The DimensionAttribute table relates the attribute to its source view. The field BackingEntityType holds the TableId of the view and the field NameAttribute holds the FieldId of the description field on the underlining view.

You can run the following query to find all views behind financial dimension values and descriptions:

SELECT
     BACKINGENTITYTYPE AS TABLEID,
     NAMEATTRIBUTE AS FIELDID,
     (SELECT TOP 1 NAME FROM SQLDICTIONARY WHERE TABLEID = BACKINGENTITYTYPE AND FIELDID = 0 AND SQLNAME <> '') AS TABLENAME,
     (SELECT TOP 1 NAME FROM SQLDICTIONARY WHERE TABLEID = BACKINGENTITYTYPE AND FIELDID = NAMEATTRIBUTE AND SQLNAME <> '') AS FIELDNAME
 FROM DIMENSIONATTRIBUTE
 GROUP BY BACKINGENTITYTYPE, NAMEATTRIBUTE

Even with this views, getting values from different dimensions would require a good effort, so MS also created the FinancialDimensionValueEntityView that does a union of all the views behind all dimensions.