Ledger and Default Dimension combination in Single view

Dimension View Project XPO

SQL Query:

Default dimension:
SELECT
     T1.DefaultDimension AS DefaultDimension,
     T2.DISPLAYVALUE AS Employee,
     T3.DISPLAYVALUE AS CostCenter,
     T4.DISPLAYVALUE AS Division,
     T5.DISPLAYVALUE as Department,
     'Default' as DimType
FROM
     DefaultDimensionVIEW T1
LEFT OUTER JOIN DefaultDimensionVIEW T2
ON T1.DefaultDimension=T2.DefaultDimension
and T2.Name = 'Employee'
LEFT OUTER JOIN DefaultDimensionVIEW T3
ON T1.DefaultDimension=T3.DefaultDimension
and T3.Name = 'CostCenter'
LEFT OUTER JOIN DefaultDimensionVIEW T4
ON T1.DefaultDimension=T4.DefaultDimension
and T4.Name = 'Division'
LEFT OUTER JOIN DefaultDimensionVIEW T5
ON T1.DefaultDimension=T5.DefaultDimension
and T5.Name = 'Department'
GROUP BY T1.DefaultDimension,
T2.DISPLAYVALUE,
T3.DISPLAYVALUE,
 T4.DISPLAYVALUE,
T5.DISPLAYVALUE


Ledger Dimension:

//View 1 staging

CREATE VIEW [dbo].[vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag]   

As  

SELECT 

T1.DIMENSIONATTRIBUTEVALUECOMBINATION AS VALUECOMBINATIONRECID,  

 T1.ORDINAL AS GROUPORDINAL, 

 T2.DISPLAYVALUE AS DISPLAYVALUE,  

 T2.RECID AS LEVELVALUERECID,  

 T2.ORDINAL AS VALUEORDINAL,  

 T2.DIMENSIONATTRIBUTEVALUEGROUP AS DIMENSIONATTRIBUTEVALUEGROUP,  

 T3.DIMENSIONATTRIBUTE AS DIMENSIONATTRIBUTE,  

 T3.RECID AS ATTRIBUTEVALUERECID,  

 T3.ENTITYINSTANCE AS ENTITYINSTANCE,  

 T3.HASHKEY AS ATTRIBUTEVALUEHASHKEY,  

 T4.Name as DimensionName,

 1010 AS RECID 

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 T2.DIMENSIONATTRIBUTEVALUE=T3.RECID) 

  

GROUP BY T1.DIMENSIONATTRIBUTEVALUECOMBINATION,T1.ORDINAL,T2.DISPLAYVALUE,T2.RECID,T3.RECID,

T2.ORDINAL,T2.DIMENSIONATTRIBUTEVALUEGROUP, T3.DIMENSIONATTRIBUTE,T3.ENTITYINSTANCE,T3.HASHKEY,T4.NAME

GO

//view 2

CREATE VIEW [dbo].[vDIMENSIONATTRIBUTELEVELVALUEALLVIEW]   

As  

SELECT

     T1.VALUECOMBINATIONRECID   as LedgerDimension,

     T2.DISPLAYVALUE as AccountNum,

     T3.DISPLAYVALUE as BusinessUnit,

     T4.DISPLAYVALUE as CostCenter,

     T5.DISPLAYVALUE as Project,

T6.DISPLAYVALUE

as OnsiteOffshore,

T7.DISPLAYVALUE

as Worker,

T8.DISPLAYVALUE

as Geo,

'LedgerDimension'

as Type

FROM

     DIMENSIONATTRIBUTELEVELVALUEALLVIEW T1

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T2 

ON T1.VALUECOMBINATIONRECID=T2.VALUECOMBINATIONRECID

and T2.DimensionName = 'MainAccount' 

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T3 

ON T1.VALUECOMBINATIONRECID=T3.VALUECOMBINATIONRECID

and T3.DimensionName = 'BusinessUnit' 

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T4 

ON T1.VALUECOMBINATIONRECID=T4.VALUECOMBINATIONRECID

and T4.DimensionName = 'CostCenter'

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T5 

ON T1.VALUECOMBINATIONRECID=T5.VALUECOMBINATIONRECID

and T5.DimensionName = 'Project'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T6 

ON T1.VALUECOMBINATIONRECID=T6.VALUECOMBINATIONRECID

and T6.DimensionName = 'OnsiteOffshore'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T7 

ON T1.VALUECOMBINATIONRECID=T7.VALUECOMBINATIONRECID

and T7.DimensionName = 'Worker'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T8 

ON T1.VALUECOMBINATIONRECID=T8.VALUECOMBINATIONRECID

and T8.DimensionName = 'Geo'

GROUP BY T1.VALUECOMBINATIONRECID, T2.DISPLAYVALUE,

T3.DISPLAYVALUE,

T4.DISPLAYVALUE,

T5.DISPLAYVALUE, T6.DISPLAYVALUE,

T7.DISPLAYVALUE,

T8.DISPLAYVALUE

GO