[Solved] Management of time in Cubes

[Marcelv]

Hi all,
I'm an ex Applix TM1 developer and am in the throes of learning my new friend powerplay and providing a spec for the cubes.Ã,  The catch? I'm not getting training until Cognos 8 :)

In the examples generally available Time seems to be treated as one dimension so we look at sales over different periods and analyse between cost centres.

This is fine for people who work monthly but my situation is that I want to compare YTD performance this year with last year.Ã,  I'd do this by breaking up Year and month into two seperate dimensions to enable, for example, splitting out Dec YTD as the filter option and running Years along the columns, giving me a nice analysis of year performance as at Dec YTD.Ã,  I can even chuck quarters (held in the month dimension) along the rows and see quarterly analysis of sales by years.

This is my current strategy but I'm interested to know other approaches and benefits of treating time as either one dimension or even other implementations and their pro's and con's.Ã,  Am I going to miss out on date/time functionality in the cubes if I do this?

[MFGF]

The problem with having multiple time dimensions is where you start applying filters.  Just because you filter on 2003 in one time dimension does not imply that the same filter is applied to the other, so things could get very confusing for users if they start to mix members from both time dimensions in their reports.

The usual recommendation is to have a single time dimension, if necessary using alternate drill paths and adding extra relative time categories to handle things like different periods and YTD last year.

Incidentally, even with Cognos 8, you will still be using Transformer to define and build Cognos Powercubes, so if you need any training in this area, you might as well do it sooner rather than later.

Best regards,

MF.

[Marcelv]

Good point on Transformer.Ã,  Will look into it.

I see what you mean by having multiple years in a cube would be a pain when filtering.
Nice point about multiple drill paths in the dimension that seems like a logical solution.

I'm going to put this to solved but feel free to continue contributing to the discussion people.

[dwood09]

Quite a number of our cubes have a "Time Aspect" dimension with categories "Month", YTD" and "Full Yr". 

Using these with a standard Time dimension provides a great deal more analytical flexibility ... change management and user acceptance / understanding was the hardest part, but it's become somewhat of a standard ... well worth the initial pain...  mind the rollup strategies though!!!

With 7.3 you can declare the dimension as a "Scenario", which means the values of the categories do not roll up to a total "Time Aspect", which of course in nonsense.

cheers,
DW

[Marcelv]

That idea certainly does extend the analytical ability of time based reporting, while avoiding some of the issues raised in the second post to do with time.

Also good to know about scenario setting - I see the benefits of that and I havent even got to building powerplay cubes yet :)

Time is one thing thats difficult to model in any way as its such an abstract concept rather than Male or Female which is a little more straight forward :)