If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

relative dates within a DMR model

Started by kado, 07 Sep 2010 12:09:25 PM

Previous topic - Next topic

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

#1
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


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

Quote from: rockytopmark on 09 Sep 2010 11:04:21 AM
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!!!  ;D