How to create a growth column or a share column in an SQL Server 2008 Cube Development?

Question

I have a huge amount of sales data starting from 2000 till 2009. I want to calculate growth on 2009 sales versus 2008 sales.

I also want to calculate share of each product in any particular year or month.

Best Regards

Wajih

Answer 1

If you are talking straight MDX you could do something like:

Code Snippet (

Sum( MTD( [Date].[Calendar].[Date].[21.10.2008] ), [Measures].[Sales] )

-

Sum( MTD( ParallelPeriod( [Date].[Calendar].[Year], 1, [Date].[Calendar].[Date].[21.10.2008] ) ), [Measures].[Sales] )

)

/ Sum( MTD( ParallelPeriod( [Date].[Calendar].[Year], 1, [Date].[Calendar].[Date].[21.10.2008] ) ), [Measures].[Sales] )

You could also make it a KPI value using the KPI tab in VS Business Intelligence Dev Studio. It all depends on how you want to reference it (i.e., in Excel 2007 or through some third-party reporting tool).