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:

Date Calculations example


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.

Date calculations 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.