What would you do: creating year, month, day dimension separately in the cube or create them in one dimension

[yyround]

Hi All,

I use year, month, day as an example but in real case, it is for a business structure where the "Month" equivalent level has 20-30 possibilities and for the "day" equivalent level, the number of members is increased when a new item is added by the user.

Our external developer put them all in one dimension, i can see some pros and cons with that approach but would like to hear from you gurus that in terms of optimization of the processing speed in this cube. Would you opt for one dimension or 3? and why?

Thank you for your time in advance.
YY

[bkkbasher]

If you are just starting out, it would seem a single dimension with hierarchies for the days, months, quarters and years would be the way to go. We currently have Month and Year dimensions and are now running into issues with PAW, whereby we cannot create a fairly rudimentary time series line chart. With the single dimension and hierarchies you should get both the performance and functionality.

Hoping to find a solution to my time series issue in PAW, i stumbled across this, which might help:

https://senturus.com/blog/solving-time- ... analytics/

Of note, this assumes you are using PAx and PAW, as the hierarchies are not recognised in Perspectives.

[paulsimon]

Hi

For a month level dimension I would definitely combine year and month into a single dimension. By using named hierarchies you can still get a Year and Month cross tab if appropriate, but with all in one dimension you can do calculations like a 12 month rolling average just by consolidation that you cannot do in two separate dimensions.

If you search the forum, you will find many past arguments about which is best. However, many of those are before named hierarchies came along.

Of course, classic dimensions with alternate hierarchies still have a role as tools like TM1 Web don't support named hierarchies, and many existing systems were built before named hierarchies.

However, if you are going down to day level, then I am not so sure that putting all three levels into a single dimension is a good idea. If you use alternate hierarchies in a classic dimension as well as named hierarchies then you might run into MUN issues with that many elements.

It will all depend on why you want to have day level data and what you want to do with it.

In my own GL cube I use a Year-Month dimension combined with a Day dimension 1-31. My main functionality is monthly, but I need to be able to record journals at day level.

However, if you are working in retail where weeks and weekends are important, you might be better with a Year dimension and a 366 day Day dimension.

So unfortunately, the answer is, it depends on your particular requirements. I don''t believe that there is a one size fits all answer.

Regards

Paul