EDIT: This is a known issue that MSFT is actively working on addressing
You are here
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