Implementing Your Default Dimensions View

This post contains a DefaultDimension view just for Contoso. This is how to create it just for your financial dimensions. Typically Financial Dimensions on things like customers or vendors are there for reporting purpose. Things like channel, region and site are some examples but with the ability to create custom financial dimensions and dimension structures, the sky is the limit. However, reporting on defaultDimensions in SQL can be difficult because they are distinctly different from LedgerDimensions although they contain very similar data. a DefaultDimension is a set of financial dimensions that does not contain a main account. A LedgerDimension is a set of financial dimensions that does contain a main account. To make DefaultDimension reporting significantly easier, you can implement a DefaultDimensions view.

First, lets start with this as a template:

 

CREATE VIEW [dbo].[vw_ledgerDefaultDimension] 
AS
  SELECT davsi.dimensionattributevalueset, 
         dav.dimensionattribute, 
         davsi.partition, 
         [ YOUR STUFF HERE ]
FROM   dbo.dimensionattributevaluesetitem davsi 
         INNER JOIN dbo.dimensionattributevalue dav 
                 ON dav.recid = 
                    davsi.dimensionattributevalue 
         INNER JOIN dbo.dimensionattribute da 
                 ON da.recid = dav.dimensionattributego 

So, pretty simple as far as queries go. Next, we will want to add our specific financial dimensions for our default dimensions. not all financial dimensions will apply but we will need a place holder for each that may be available.

'MyFinDim' = (SELECT displayvalue 
                     FROM   dbo.vw_ledgerbasedimensionview 
                     WHERE
         NAME = 'MyFinDim'
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset)

You can replace "MyFinDim" with your financial dimensions one at a time and place it in the template above. One note is that this view references another view I posted just for simplicity sake. You can find that view here.

Easy Way Out!

Here is some awful SQL that will fill in the template for you then you can use something like SQL Formatter to pretty it up. Just update the first part to INSERT your financial dimension names into #tmp and run it.

select cast('Site' as varchar(30)) as dim
into #tmp
 
insert into #tmp values('department')
--and so on
 
select template1 = '         ' + #tmp.dim +  ' = (SELECT displayvalue 
                     FROM   dbo.vw_ledgerbasedimensionview 
                     WHERE
         NAME = ''' + #tmp.dim +   '''
         AND dimensionattributevalueset = 
             davsi.dimensionattributevalueset)'
from #tmp
 
drop table #tmp