What is a dummy date dimension?
Adding a "dummy" date dimension can provide real analytical power to your cube. The additional dimension means that all of your date based calculations will work "across all measures". Here is an example:
The traditional, or even tabluar, way of creating date calculations is on the mesures dimension. If you had a measure called "Sales Amount" then you could create various date based calculations.
- Sales Amount Pr Yr
- Sales Amount YTD
- Sales Amount Pr Yr Variance
- Sales Amount Pr Yr Variance %
This is fine if you only have a few measures. But what if you have 10 measures and 20 date calculations. (WTD, MTD, QTD, etc)
Then you would suddenly have 200 measures!
With an additional date calculations dimension, you are able to create the date calculation once, like YTD, and it will work across all measures and measure groups. (You can limit it if you want)
This dimension is especially useful for "daily" dashboard type reports. A report using a date calculations dimension could look something like this:
Also handy for graphs
The additional date dimension is also handy when graphing because it will dynamically calculate the "prior year" for each member in the graph.
There are numerous other types of calculations that can be put on this dimension. Here are the most common ones:
Prior Year with variances
Prior Period with variances
Week to Date with variances
Month to Date with variances
Quarter to Date with variances
Year to Date with variances
SUM rolling 6 months with variances
AVG rolling 6 months with variances
SUM rolling 12 months with variances
AVG rolling 12 months with variances
We can help you implent a global date calculations dimension in your cube. This can easily be done remotely.