Filtering DMR for last 7 Days

[bberryhill]

I have created a set using the following expression:
filter(
[Dimensions].[Date].[Date].[Day],
roleValue('_businessKey', currentMember( [Dimensions].[Date].[Date] )) <= #
sq(timestampMask(_add_days($current_timestamp,-1),'yyyy-mm-dd') + ' 00:00:00.000') #
and
roleValue('_businessKey', currentMember( [Dimensions].[Date].[Date] )) >= #
sq(timestampMask(_add_days($current_timestamp,-8),'yyyy-mm-dd') + ' 00:00:00.000') #
)

The result is a set containing the last 7 days from my date dimension.  However, the performance of this set is dramatically slower than an explicit set containing the last 7 days.  I really noticed the problem when I used my calculated set in a cross tab and summarized the set.  Without the summary I was getting results in about 30 seconds, with the summary - 10 minutes, ouch!  When I drag date members into the cross tab (creating an explicit set) the report runs in 5-10 seconds.

Any thoughts on why the filtered set performs so much slower than an explicit set?  Thoughts on how to create a set containing the last days?  Ultimately the cross tab should have the last 7 days and other rows such as MTD and YTD.  I can create these by using item() to get a member from my set and then using ancestor but I am concerned about the performance of this filtered set.

[CognosPaul]

Are you using DQM? If not, try enabling that.

Can you give me an example of the MUN of one of the dates? It may be possible to construct the mun using macro expressions.

Assuming it is possible, can you try running:

lastPeriods(7,[Today's Date])?

I've occasionally seen very poor performance when using DMR. Since the introduction of DQM is has been better. Usually slowdowns are caused by mixing relational and dimensional models or reporting styles.

With the runtime you're seeing, I suspect that Cognos running the time calculation and fact aggregation separately, and joining them after the fact has been run. Can you run a db trace to confirm?

[bberryhill]

Thanks for your reply.

I am not using DQM - will work on that.

I think the date MUN will be hard to construct.  The date dimension contains year, quarter, month, week and day levels.  An example MUN is
[All].[2013].[1255].[1367].[2095].[2013-12-02 00:00:00.00]

I think the quarter, month and week levels add some complexity to constructing the MUN.

[Today's Date] is the real crux of the problem.  My date dimension is populated with dates into the future.  I can use one of the filtering techniques to determine the current day.  Similar to the one I posted earlier - make a timestamp and filter the date dimension or I can filter the date dimension on a tuple that returns a measure value -- last day where the quantity is not null approach).

It feels like the problem is the filtered approach - it seems like the query is executed using all of the rows in the date dimension joined to the fact table(s), then the results are filtered so that only the current day or the last 7 days are displayed.  What needs to happen is the rows in the date dimension are determined first, then the fact table is queried based on the dimension rows.  That seems to be what happens if I manually put members into the crosstab or use a tree-prompt and allow the user to select the members to be shown in the crosstab row.

[bberryhill]

Amendment to previous reply ...
The Date MUN looks to be composed of the unique identifiers for each level - epoch numbers for the most part.  These are carried in the dimension as attributes so, either in framework manager or in RS I could create the MUN for the current day. 
I saw an earlier post referring to creating the MUN in fm along with a parameter map.  If that is a possible solution, please explain that approach in simpler terms - I'm not familiar with parameter maps in fm.  A set expression using "paulsmagicallookup" sounds promising.

[CognosPaul]

It shouldn't be too difficult. As you said, a parameter map will be needed.

Create a new query in FM. Drag in the items from your time dimension, and filter the date to today. Create a calculated item that recreates your MUN:
'[All].['+cast([Namespace].[Time Dim].[Year],varchar(4)) +'].['+cast([Namespace].[Time Dim].[QuarterKey],varchar(4)) +'].['+cast([Namespace].[Time Dim].[Month],varchar(4)) +'].['+cast([Namespace].[Time Dim].[DateyKey],varchar(5)) +']'

Create another calculated item that has a static 1.

Now create a parameter map, called PaulsMagicalLookup, based on that query. Set the static 1 as the key and the MUN as the value. Publish the model and test the param map by doing lastPeriods($PaulsMagicalLookup{1},7) in a query.

[bberryhill]

I humbly return for more advice ...  :-[  If I sound like a newbie, well to multi-dimensional authoring I am.  I am working through the multi-dimensional authoring course and I'm determined to not mix relational style techniques.

I got the parameter map worked out in FM. 
In RS I added a query calculation to my cross tab as a set expression on my Date hierarchy with the following expression:
  [Dimensions].[Date].[Date].[Day] -> #$CurrentDayLookup{1}#

I got the expected results so I added a second query calculation to my cross tab as a set expression on my Date hierarchy with the following expression:
  lastPeriods (7, [CurrentDate] )

Got the expected results again.  This appears to be working as I expected and I can navigate the date dimension.  Thank you for your guidance. 
Now, I want to prompt the user to select a date from a list or select a static choice "Current Date" (Previous Day, Current Month, Prior Month will be coming - and I'm trying to work within my date dimension so I can navigate it rather than using my relative dimension).

So, I create a prompt page and a value prompt with the parameter name "pSelectedDate", the prompt is based on a query that returns the day level: [Dimensions].[Date].[Date].[Day]
Then I go back to my cross tab and edit my query calculation to use the prompt:
   [Dimensions].[Date].[Date].[Day] -> ?pSelectedDate?  Works fine.
Then I add the static choice to my prompt.  For use, I use my lookup:  #$CurrentDayLookup{1} and I give it a display value 'Current Day'.
When I run the report and choose 'Current Day' I get an error: The query contains a reference to at least one object '[all].[2013].[2013Q4].[2013-10].[2013-10-40].[2013-10-04]' that does not exist  Apparently the prompt executed the macro and sent a string back to the set expression.

So now I head down the path of using a prompt macro instead of ?pSelectedDate?.  The first issue I run into is that the prompt macro and the ?pSelectedDate? work differently.  I thought [Dimensions].[Date].[Date].[Day] -> #prompt('pSelectedDate','mun')# should work fine, but when I run the report and choose a date, I get a parsing error: Parsing text: [Dimensions].[Date].[Date].[Day] -> [Dimensions].[Date].[Date].[Day]->[all].[2013].[2013Q3].[2013-09].[2013-09-39].[2013-09-28]  It is doubling up on my level info -- don't understand why ?prompt? and #prompt... are behaving differently especially with this very basic implementation of #prompt?

On "google's" advice, the expression is changed to: #prompt('pSelectedDate', 'mun')# ... that works when I select a date but when I select the static choice, I get a cognos generated prompt page looking for pSelectedDate with my macro ($CurrentDayLookup{1}#) in the field.  I tried changing the datatype from mun, to token and to string and moved the ball around but got no closer to the goal.

I assume the expression has to get resolved to a level and a MUN since it is a set expression.
I don't understand pretext and posttext in the prompt macro - can I use if or case constructs to test the content of the parameter and affect the results of the user's input? 
Thanks in advance for your assistance.

[CognosPaul]

Advice is free  8)

The -> shorthand for prompts always annoys me. I recommend discontinuing its use and focusing on macros.

A Member Unique Name will always have the level information attached to it. So the reason you're getting that parsing error is because you're doubling up on the mun. There is a solution for the static choices though.

The first thing to remember is that Cognos will not attempt to use data from the static choices as macro functions. So setting #$CurrentDayLookup{1}# won't work. However you can cheat by putting the lookup into it's own data item and referencing that in the static value.

So prompt has:
Display            |  Use
Current Date   | [Current Date]
Current Month | [Current Month]

Since token prompts will accept muns, you can set that prompt object to use the date level as well.

The pre and post texts are ways of programmatically adjusting the values that the user selects.

Take the following expression:
#promptmany('Dates','mun','[Cube].[Time Dim].[Time Hier].[Years]','set(','[Cube].[Time Dim].[Time Hier]',')')#

That is creating the Dates parameter, which is the memberUniqueName data type. By default if nothing is selected it will use the years level. If the user does select something, it will add set( before the selection and ) after. If Cognos generates a prompt for this, if you run it without a prompt page for instance, it will generate a tree prompt based on the Time Hier hierarchy.

As of version 10.something, you can use case statements inside macro expressions if you are using the DQM. CQM does not support macro case. You can use a case statement outside of the macro, but I don't recommend it on a dimensional model.