There is a good article on the Canrock Solutions blog that covers the basics of time dimensions. There the author boils the choice down to discreet vs. continuous, concluding:
Overall, I favor continuous approach for planning and forecasting applications. In my opinion, it better supports rolling forecast, multiple fiscal calendars and relative time calculations. It makes rules creation and report building significantly easier.
Discrete approach will be more advantageous for reporting applications with heavy ad-hoc year-over-year analysis requirements.
But in reality, aren't there many more options and factors to consider? Shouldn't we prioritise low maintenance, usability and flexibility over development ease? Is a dimension that expands endlessly over time ever a good idea when there's an alternative?
My default time-dimension structure is fairly simple and involves three time dimensions:
Year - contains simple 4-digit years ("2014", "2015", "2016", etc) with a total. This is the year in which the budget or forecast was prepared, or the year the actual was recorded.
Month - simple 3-character months with opening balance element and roll-ups for YTD, YTG and any other accumulations required. To support a different fiscal year start month, I name the elements "M01", "M02", etc, and use an alias for the month name. With a few offset attributes this works well.
Forecast Period - this is the future year. "Y01", "Y02", "Y03", etc. "Y01" is usually named "Current Year" via alias. Actuals and Budget use only this element, and projections and forecasts use the future years.
I'm sure you've all seen similar set ups. This requires a date lookup cube for relative time calculations, but this is a standard cube that I built several years ago that has never had to change.
As indicated in the article above, there are some limitations around cross-year aggregation and rolling forecasts, but I've never found it difficult to overcome those.
Even given the priorities that led to the conclusions in the article, I would probably still use a hybrid approach. In some cases, you could combine the Month and Forecast Period dimensions. This would give you element names like "Y01 M01", "Y01 M02", etc, with "Y01" as a consolidation.
This would enable cross-year aggregation for forecasts without resulting in a dimension that expands linearly over time.
I know it's a relatively fundamental topic and we all have our own set ways. Would be interested to hear thoughts from the community on their go-to structure and the motivations behind it.
Flow OLAP -- We think outside the cube!