Implementing Ledger Dimensions Flattened View for your Financial Dimensions

This post outlines the Ledger Dimensions Flattened for easier consumption for Contoso. This view needs to be created for a specific set of financial dimensions and updated whenever a new financial dimension is added. This is one way to get financial dimensions into columns so you can filter and report more fluidly and quickly. First, let's start with the base view.

CREATE VIEW [dbo].[vw_ledgerLedgerDimensionsFlat] 
AS
  SELECT dv.partition, 
         dv.dimensionhierarchy, 
         'AccountStructure' = dv.dimensionhierarchy, 
         dv.dimensionattributevaluecombination, 
         'LedgerDimension' = 
            dv.dimensionattributevaluecombination, 
	-- dimension start

	--dimension end
  FROM   (SELECT davc.partition, 
                 'DimensionHierarchy' = 
                 davc.accountstructure, 
                 DAVGC.dimensionattributevaluecombination, 
                 dalv.displayvalue, 
                 DALV.ordinal, 
                 'DimensionAttributeName' = da.NAME
          FROM   dimensionattributevaluecombination davc 
                 JOIN
         dimensionattributevaluegroupcombination 
         davgc 
                   ON
         davc.partition = davgc.partition 
         AND davc.recid = 
             davgc.dimensionattributevaluecombination 
                 JOIN dimensionattributevaluegroup davg 
                   ON davg.partition = davgc.partition 
                      AND davg.recid = 
                          davgc.dimensionattributevaluegroup 
                      AND davg.dimensionhierarchy = 
                          davc.accountstructure 
                 JOIN dimensionattributelevelvalue dALV 
                   ON davg.partition = dALV.partition 
                      AND davg.recid = 
                          dALV.dimensionattributevaluegroup 
                 JOIN dimensionattributevalue dav 
                   ON
         dav.recid = dalv.dimensionattributevalue 
                 JOIN dimensionattribute da 
                   ON da.partition = dav.partition 
                      AND da.recid = dav.dimensionattribute 
          GROUP  BY davc.partition, 
                    davc.accountstructure, 
davgc.dimensionattributevaluecombination, 
DALV.displayvalue, 
dalv.ordinal, 
da.NAME) dv 
GROUP  BY dv.partition, 
dv.dimensionhierarchy, 
dv.dimensionattributevaluecombination 
 
go 

That can be copy/pasted into SSMS. Next, we need to create a column for each financial dimension name. This is fairly easy to do, just a bit time consuming. Here is the basic template for each Financial Dimension with [x] in place of the Financial Dimension name.

'[x]' = Max(CASE dv.dimensionattributename 
                               WHEN '[x]' THEN
                               dv.displayvalue 
                               ELSE ''
                             END), 

 

Now, for each Financial dimension, you can find/replace [x] with its name the copy/paste that into the SQL above between the "-- Dimension Start" and "-- Dimension end" comments. As an example, if you had 3 financial dimensions of Site, Department and CostCenter, your specific implementation of this view would be

CREATE VIEW [dbo].[vw_ledgerLedgerDimensionsFlat] 
AS
  SELECT dv.partition, 
         dv.dimensionhierarchy, 
         'AccountStructure' = dv.dimensionhierarchy, 
         dv.dimensionattributevaluecombination, 
         'LedgerDimension' = 
            dv.dimensionattributevaluecombination, 
         'Site' = Max(CASE dv.dimensionattributename 
                               WHEN 'Site' THEN
                               dv.displayvalue 
                               ELSE ''
                             END), 
         'Department' = Max(CASE dv.dimensionattributename 
                             WHEN 'Department' THEN
                             dv.displayvalue 
                             ELSE ''
                           END), 
         'CostCenter' = Max(CASE dv.dimensionattributename 
                                WHEN 'CostCenter' THEN
                                dv.displayvalue 
                                ELSE ''
                              END)
  FROM   (SELECT davc.partition, 
                 'DimensionHierarchy' = 
                 davc.accountstructure, 
                 DAVGC.dimensionattributevaluecombination, 
                 dalv.displayvalue, 
                 DALV.ordinal, 
                 'DimensionAttributeName' = da.NAME
          FROM   dimensionattributevaluecombination davc 
                 JOIN
         dimensionattributevaluegroupcombination 
         davgc 
                   ON
         davc.partition = davgc.partition 
         AND davc.recid = 
             davgc.dimensionattributevaluecombination 
                 JOIN dimensionattributevaluegroup davg 
                   ON davg.partition = davgc.partition 
                      AND davg.recid = 
                          davgc.dimensionattributevaluegroup 
                      AND davg.dimensionhierarchy = 
                          davc.accountstructure 
                 JOIN dimensionattributelevelvalue dALV 
                   ON davg.partition = dALV.partition 
                      AND davg.recid = 
                          dALV.dimensionattributevaluegroup 
                 JOIN dimensionattributevalue dav 
                   ON
         dav.recid = dalv.dimensionattributevalue 
                 JOIN dimensionattribute da 
                   ON da.partition = dav.partition 
                      AND da.recid = dav.dimensionattribute 
          GROUP  BY davc.partition, 
                    davc.accountstructure, 
davgc.dimensionattributevaluecombination, 
DALV.displayvalue, 
dalv.ordinal, 
da.NAME) dv 
GROUP  BY dv.partition, 
dv.dimensionhierarchy, 
dv.dimensionattributevaluecombination 
 
go 

 

 

To use this, you can simply consume by table.LedgerDimesion, like so:

select top 100 *
from LEDGERJOURNALTRANS ljTrans
join vw_ledgerLedgerDimensionsFlat df 
 on df.dimensionattributevaluecombination = ljTrans.LedgerDimension
and df.partition = ljTrans.partition