Relative time categories Implimetation in FM

[PRIT AMRIT]

Hi guru's,

I have a typical requirement and need your valuable input in order to give a go ahead to the customer.

My customer is using cubes now. And as we know it's easy to play with the time dimension such day, day-1, day-2, day-3, month, month -1, month -2, month -3, month -4, week-1, week-2, week-3 so on. And the customer is asking the same time categories to be implimented in the FM and do the Query/Report studio analysis.

FYI, we have around minimum 10 facts & 30 dimensions table. Customer wants to see all the measures for the above time categories. so that they can drag and drop on QS and do their analysis.

As per my experience, I believe the approch would be to create different time dimensions for each categories (i.e.  day, day-1, day-2, day-3, month, month -1, month -2, month -3, month -4, week-1, week-2, week-3).  And create shortcuts of all the 10 facts & 30 Dimensions. Arrange in the folder structure and define the relationship.

Day Folder
-------------
Day time dimension
10 facts
30 Dimensions

Day-1 Folder
----------------
Day-1 time dimension
10 facts
30 Dimensions

Day-2 Folder
----------------
Day-2 time dimension
10 facts
30 Dimensions
-----------
------------
so on.....

Then arrange all the measures in the presentation layer for reporting.

Please let me know if my approch is wrong. If yes, please guide me to design a better FM Model.

Looking forward to hear from you soon. Thanks!

Regards,
Prit

[wyconian]

Hi

That sounds like a lot of work :-(  First question could you're users use the cube in Report/Query studio?  That would give them the fucntionality they need and could use the cube's date functions.

If not, do you have access to the ETL?  (sorry I'm assuming you're using a warehouse).  If you do can you add the relative date attributes to your calendar dimension?  I've added relative day, relative month, relative year, working day etc attributes to my calendar.  You can just use the one calendar.

Good luck

[tjohnson3050]

There is an IBM Proven Practice Document for Modeling Relative Time in FM:

http://www.ibm.com/developerworks/data/library/cognos/page90.html