Relative Time Measures /Calculations in Framework Manager

[Sugs]

Can any help me with this? I am linking together several tables on primary keys one of which is obviously a calendar table with a date key. The fact table will be circa 5m records and I need to develop some relative time measures and dimensions so users can drag and drop or drill down etc.
Any help appreciated?
           

[Lynn]

The ideal approach would be to have a really robust date dimension in the database that is maintained as part of the ETL process for relative attributes. Even a database view that calculates everything might be better than doing it all in Cognos.

You could, however, do it in Cognos to prove out the specifics and let users review first. This way you can minimize grumbling from the database guys by giving them specific requirements and minimize changes they'll need to do.

As a simple example, let's say you want a column on the date dimension called RELATIVE_MONTH_TYPE. Based on the current date and the date on the row, you might want to populate this with the value "Current Month", "Prior Month", "Current Month Last Year", "Prior Month Last Year". Any other date rows that don't fit in any of those categories would just get populated with a string representing the month and year (e.g.., 2011-01).

Defining a column that encapsulates discrete scenarios and uses strings instead of multiple columns with Y/N flags lets a user filter in a very intuitive manner. Further, the "Relative Month Type" column can then be dragged into a report and when you crosstab it, the metrics plop into the appropriate buckets. You can also very easily create pre-defined filters in the Framework Model.

A really solid date dimension would have lots of these types of fields for all sorts of uses including xTD type logic (MTD, YTD, MTD Last Year, etc.) and rolling type logic (Rolling 12 months, etc.).

This requires nothing more to be done on the 5m records fact side of things. Just the join to your date dimension.

You didn't include a lot of specifics in your post, so hopefully I'm not too far off the mark here...

[blom0344]

Lynn,

great answer! Especially the first part on the database solution!

[Lynn]

Thanks blom

Your check is in the mail -- p.s. don't tell anyone I am not above paying for compliments :D

[MFGF]

Lynn,

You look like a million dollars! (all green and crinkly!)
Your teeth are like stars! (they come out at night!)

so...  do I get some dosh too? :D

MF.

[Sugs]

Thanks Lynn, I've gone for the database approach and so far so good, it seems really straightforward.

[Lynn]

Nice try muppet, but unfortunately it seems you have mistaken me for Paul :)

[MFGF]

Nice try muppet, but unfortunately it seems you have mistaken me for Paul :)

lol.  No hidden text in your reply?  You disappoint me, Mr Bond! :)

[CognosPaul]

Small, green and stuffed in a briefcase? Mouth full of flaming balls of gas? Lynn, really!

[Lynn]

Small, green and stuffed in a briefcase? Mouth full of flaming balls of gas? Lynn, really!

???
WTF?

[jive]

Hi ,
To add on what Lynn suggest add the working days, open-close days, holidays, days number and so so so ... I can tell you to build a good time dimension it's really important .

In the project I work now we build one with 80 columns and we have to go now 100 columns, most of them are the client way to express the date in text in the report, like 3 months period we have the full length yyyy-mm-dd to yyyy-mm-dd also mm-yyyy to mm-yyyy, also persiod number trimester 1 from mm-yy to mm-yy and so on. That way when the author do a report the standard for making report are always there, it's easy to validate standard before putting it in production. Think of all the calculation you can do on date and try to get everything in the time dimension it will save you a lots of work in the future. Just one recommendation don't lower then days, if it's not required for your business.

Regards Jacques