Get Ledger & Default Dimension - All Dimension Combination in Single view (AX 2012, DAX 365 FO, SQL)

Note: Change Dimension Names in Below query according to your requirement.

//AX

Dimension:
Ledger Dimension and Default Dimension

In Standard
Resulted Output;

DimensionRecId	DisplayValue	Dimension Name
5637191105	99999	MainAccount
5637191105	BU1	BusinessUnit
5637191105	CC1	Cost Center

(Note: Multiple lines in View)

In Customize view
Resulted Output:

DimensionRecId	BusinessUnit	CostCenter	MainAccount	Project
5637191105	BU!	CC1	99999	Admin_1
5637206943	BU1	CC2	99998	Admin_1
5637241279	BU2	CC3	99997	Admin_1

(Note: Single lines for each unique dimension combination in View)

Project XPO Download 
Use Below link
Ledger and Default Dimension View

//SQL 
//LedgerDimension

//Create New View

CREATE VIEW AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW 
As
SELECT T1.DIMENSIONATTRIBUTEVALUECOMBINATION AS VALUECOMBINATIONRECID,
 T1.ORDINAL AS GROUPORDINAL,
 T1.PARTITION AS PARTITION,
 1010 AS RECID,
 MAX(T2.RECID) AS MAXOFRECID,
 T2.PARTITION AS PARTITION#2,
 T2.DISPLAYVALUE AS DISPLAYVALUE,
 T2.RECID AS LEVELVALUERECID,
 T2.ORDINAL AS VALUEORDINAL,
 T2.DIMENSIONATTRIBUTEVALUEGROUP AS DIMENSIONATTRIBUTEVALUEGROUP,
 T3.PARTITION AS PARTITION#3,
 T3.DIMENSIONATTRIBUTE AS DIMENSIONATTRIBUTE,
 T3.RECID AS ATTRIBUTEVALUERECID,
 T3.ENTITYINSTANCE AS ENTITYINSTANCE,
 T3.HASHKEY AS ATTRIBUTEVALUEHASHKEY,
 T4.Name as DimensionName
FROM DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION T1 

CROSS JOIN DIMENSIONATTRIBUTELEVELVALUE T2 
CROSS JOIN DIMENSIONATTRIBUTEVALUE T3 
CROSS JOIN DIMENSIONATTRIBUTE T4 

WHERE (T1.DIMENSIONATTRIBUTEVALUEGROUP=T2.DIMENSIONATTRIBUTEVALUEGROUP 
 AND t4.RecId =t3.DIMENSIONATTRIBUTE
 AND (T1.PARTITION = T2.PARTITION)) 
 AND (T2.DIMENSIONATTRIBUTEVALUE=T3.RECID 
 AND (T2.PARTITION = T3.PARTITION)) 

GROUP BY T1.DIMENSIONATTRIBUTEVALUECOMBINATION,T1.ORDINAL,

T1.PARTITION,T2.PARTITION,T2.DISPLAYVALUE,T2.RECID,T2.ORDINAL,T2.DIMENSIONATTRIBUTEVALUEGROUP,
T3.PARTITION,T3.DIMENSIONATTRIBUTE,T3.RECID,T3.ENTITYINSTANCE,T3.HASHKEY,T4.NAME

//Select query for ledger Dimension
//New view for all Dimension Value in column based on ledgerDimension (Distinct)

CREATE VIEW AX_DIMENSIONATTRIBUTELEVELVALUEALLVIEW 
As
SELECT
     T1.VALUECOMBINATIONRECID AS LedgerDimension,
     T2.DISPLAYVALUE AS AccountNum,
     T3.DISPLAYVALUE AS BusinessUnit,
     T4.DISPLAYVALUE AS CostCenter,
     T5.DISPLAYVALUE as Project
FROM
     DIMENSIONATTRIBUTELEVELVALUEALLVIEW T1
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T2
ON T1.VALUECOMBINATIONRECID=T2.VALUECOMBINATIONRECID and T2.DimensionName = 'MainAccount'
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T3
ON T1.VALUECOMBINATIONRECID=T3.VALUECOMBINATIONRECID and T3.DimensionName = 'BusinessUnit'
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T4
ON T1.VALUECOMBINATIONRECID=T4.VALUECOMBINATIONRECID and T4.DimensionName = 'CostCenter'
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T5
ON T1.VALUECOMBINATIONRECID=T5.VALUECOMBINATIONRECID and T5.DimensionName = 'Project'

GROUP BY T1.VALUECOMBINATIONRECID, T2.DISPLAYVALUE, T3.DISPLAYVALUE,
T4.DISPLAYVALUE, T5.DISPLAYVALUE


//Output
//Now use this view AX_DIMENSIONATTRIBUTELEVELVALUEALLVIEW,
for to get all LedgerDimension dimension value with all Dimension combination in single line


//Default dimension - Dimension Default

SELECT
     T1.DefaultDimension AS DefaultDimension,
     T2.DISPLAYVALUE AS Company,
     T3.DISPLAYVALUE AS BusinessUnit,
     T4.DISPLAYVALUE AS CostCenter,
     T5.DISPLAYVALUE as Project
FROM
     DefaultDimensionVIEW T1
     LEFT OUTER JOIN DefaultDimensionVIEW T2
ON T1.DefaultDimension=T2.DefaultDimension and T2.Name = 'Company'
     LEFT OUTER JOIN DefaultDimensionVIEW T3
ON T1.DefaultDimension=T3.DefaultDimension and T3.Name = 'BusinessUnit'
     LEFT OUTER JOIN DefaultDimensionVIEW T4
ON T1.DefaultDimension=T4.DefaultDimension and T4.Name = 'CostCenter'
     LEFT OUTER JOIN DefaultDimensionVIEW T5
ON T1.DefaultDimension=T5.DefaultDimension and T5.Name = 'Project'

GROUP BY T1.DefaultDimension, T2.DISPLAYVALUE, T3.DISPLAYVALUE,
T4.DISPLAYVALUE, T5.DISPLAYVALUE

//Create New View

CREATE VIEW AX_DefaultDimensionVIEW 
As
SELECT
     T1.DefaultDimension AS DefaultDimension,
     T2.DISPLAYVALUE AS Company,
     T3.DISPLAYVALUE AS BusinessUnit,
     T4.DISPLAYVALUE AS CostCenter,
     T5.DISPLAYVALUE as Project
FROM
     DefaultDimensionVIEW T1
     LEFT OUTER JOIN DefaultDimensionVIEW T2
ON T1.DefaultDimension=T2.DefaultDimension and T2.Name = 'Company'
     LEFT OUTER JOIN DefaultDimensionVIEW T3
ON T1.DefaultDimension=T3.DefaultDimension and T3.Name = 'BusinessUnit'
     LEFT OUTER JOIN DefaultDimensionVIEW T4
ON T1.DefaultDimension=T4.DefaultDimension and T4.Name = 'CostCenter'
     LEFT OUTER JOIN DefaultDimensionVIEW T5
ON T1.DefaultDimension=T5.DefaultDimension and T5.Name = 'Project'

GROUP BY T1.DefaultDimension, T2.DISPLAYVALUE, T3.DISPLAYVALUE,
T4.DISPLAYVALUE, T5.DISPLAYVALUE

//Output
//Now use this view  AX_DefaultDimensionVIEW   , for to get all Default dimension value with all Dimension combination in single line

//Union  - View for Both Dimension
//Create View, For both dimension in to single view

CREATE VIEW AX_DimensionVIEW
AS
SELECT
     T1.DefaultDimension as DefaultDimension,
     'NUll' as AccountNum,
     T3.DISPLAYVALUE as BusinessUnit,
     T4.DISPLAYVALUE as CostCenter,
     T5.DISPLAYVALUE  as Project,
     'DefaultDimension as Type
FROM
     DefaultDimensionVIEW T1
     LEFT OUTER JOIN DefaultDimensionVIEW T3
ON T1.DefaultDimension=T3.DefaultDimension and T3.Name = 'BusinessUnit'
     LEFT OUTER JOIN DefaultDimensionVIEW T4
ON T1.DefaultDimension=T4.DefaultDimension and T4.Name = 'CostCenter'
     LEFT OUTER JOIN DefaultDimensionVIEW T5
ON T1.DefaultDimension=T5.DefaultDimension and T5.Name = 'Project'

GROUP BY T1.DefaultDimension, T3.DISPLAYVALUE,
T4.DISPLAYVALUE, T5.DISPLAYVALUE

union ALL

SELECT
     T1.VALUECOMBINATIONRECID   as LedgerDimension,
     T2.DISPLAYVALUE as AccountNum,
     T3.DISPLAYVALUE as BusinessUnit,
     T4.DISPLAYVALUE as CostCenter,
     T5.DISPLAYVALUE as Project,
     'LedgerDimension' as Type
FROM
     DIMENSIONATTRIBUTELEVELVALUEALLVIEW T1
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T2
ON T1.VALUECOMBINATIONRECID=T2.VALUECOMBINATIONRECID and T2.DimensionName = 'MainAccount'
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T3
ON T1.VALUECOMBINATIONRECID=T3.VALUECOMBINATIONRECID and T3.DimensionName = 'BusinessUnit'
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T4
ON T1.VALUECOMBINATIONRECID=T4.VALUECOMBINATIONRECID and T4.DimensionName = 'CostCenter'
     LEFT OUTER JOIN AXStag_DIMENSIONATTRIBUTELEVELVALUEALLVIEW T5
ON T1.VALUECOMBINATIONRECID=T5.VALUECOMBINATIONRECID and T5.DimensionName = 'Project'

GROUP BY T1.VALUECOMBINATIONRECID, T2.DISPLAYVALUE, T3.DISPLAYVALUE,
T4.DISPLAYVALUE, T5.DISPLAYVALUE