relative dates within a DMR model

[kado]

DMR Gurus,

I am working through my first DMR model because we managed to get our hands on a Teradata box :-)

For performance testing and to satisfy one of our initial requirements I am transposing one of our Cubes (Transformer Models) into a DMR model and am wondering if anyone has any recommendations (or best practice) for integrating the Relative Dates baked into Transformer (i.e. Current Month, Previous Month, QTD, YTD, etc)?

I assume I will need to leverage a handful of Cognos functions and just want to make sure this is the best approach. Has anyone come across any good documentation for this? I haven't seen anything from IBM but may have missed it.

THANKS,
kado

[blom0344]

DMR related documentation seems very sparse. The YTD,Current Month and Previous month are relatively simple, but the QTD is more challenging since the proper functions are missing.

We have no need for these relative dates now, but in the past I 'simply' generated a special table through the ETL process for each and every relative date I could think of.

If you are on SQL server, then the following will  give a very nice 'ready-to-go' example of such a calender table:

http://www.sqlservercentral.com/scripts/Date/68389/

(registering required)

Pardon me, you are on teradata..

[kado]

Ahhhh ... interesting! That is helpful and an approach I wasn't really thinking about. Thanks for the feedback!!

[RobsWalker68]

Hi,

There was a recent thread on the IBM developerworks forum about roughly the same issue.  The response included a couple of links to relevant IBM documentation that should give you an idea

http://www.ibm.com/developerworks/forums/thread.jspa?threadID=345892&tstart=0

Kind Regards

Rob

[kado]

THANKS Rob!!!

[rockytopmark]

I have this in place in the model I am currently working with.  Here are basics of how I have accomplished it:

(Assumes there is a time dimension, and it contains certain expected keys for all levels of Time)

1. Physical Layer - import the Time Dimension table (DataSource query)
2. Development Layer - Build Time Dimensions (Model Query) for each role necessary
3. Development Layer - Using the Time Dim model query for each desired Role, create Model Filters for the desired Relative Dates. (As BLOM alluded, some are easier than others.  For Quarter, I simply used large CASE statement for determining correct 4 prior quarters)  The key to these filters to make it relative to today, using the Current_Date variable, in combination with the extract() function.
4. Dimensional Layer - Create Regular Dimensions for all desired Relative Time.  Use the appropriate Model Filter in the Regular Dimension's filters tab

Note: In modeling the regular dimensions for a single value, for like Month (MTD) or Year (YTD), your highest level is different than usual.  I will add the highest level with the same name as the Dimension, so like "Current YTD" and the source for its _memberCaption property is 'YTD - ' + [Development Layer].[Opened Date Dimension].[Opened_Date_Dim_Year_char] and the _businessKey is simply 'YTD'  (this way the MUN is always consistent)  This can be done for any Regular Dimension where a specific MUN may be desired.

(CAVEAT ALERT) These model filters will work only if the dimension is in the report object.  Context Filter only will not invoke use of the filter, so drag the "All" member into the report as an Outer Edge.

It just takes a little grunt-work, but in the end you will have decent replica of your relative time, and please note, they will not be alternate drill-downs in DMR, just separate dimensions.  You can give the allusion of Alternate drill paths by using folders, and grouping all "Open Date" regular dimensions in there.

HTH.... M

[blom0344]

I have this in place in the model I am currently working with.  Here are basics of how I have accomplished it:

(Assumes there is a time dimension, and it contains certain expected keys for all levels of Time)

1. Physical Layer - import the Time Dimension table (DataSource query)
2. Development Layer - Build Time Dimensions (Model Query) for each role necessary
3. Development Layer - Using the Time Dim model query for each desired Role, create Model Filters for the desired Relative Dates. (As BLOM alluded, some are easier than others.  For Quarter, I simply used large CASE statement for determining correct 4 prior quarters)  The key to these filters to make it relative to today, using the Current_Date variable, in combination with the extract() function.
4. Dimensional Layer - Create Regular Dimensions for all desired Relative Time.  Use the appropriate Model Filter in the Regular Dimension's filters tab

Note: In modeling the regular dimensions for a single value, for like Month (MTD) or Year (YTD), your highest level is different than usual.  I will add the highest level with the same name as the Dimension, so like "Current YTD" and the source for its _memberCaption property is 'YTD - ' + [Development Layer].[Opened Date Dimension].[Opened_Date_Dim_Year_char] and the _businessKey is simply 'YTD'  (this way the MUN is always consistent)  This can be done for any Regular Dimension where a specific MUN may be desired.

(CAVEAT ALERT) These model filters will work only if the dimension is in the report object.  Context Filter only will not invoke use of the filter, so drag the "All" member into the report as an Outer Edge.

It just takes a little grunt-work, but in the end you will have decent replica of your relative time, and please note, they will not be alternate drill-downs in DMR, just separate dimensions.  You can give the allusion of Alternate drill paths by using folders, and grouping all "Open Date" regular dimensions in there.

HTH.... M

You've got a PM..

[kado]

SOLID, great feedback, much appreciated!!!