Multiple conditions in filter expression vs slicers?

[AndrewYoung]

Hi,

I'm very new to Cognos 8 Report Studio and am building my first report off an OLAP data source.  The cube has a very large number of dimensions and a large number of members in most of those dimensions, resulting in over 1billion cross-joined rows.

My report is a detail level report that includes about 15 of these dimensions at the lowest level of detail (one row in the report for each Activity).  If it helps, in the datatable that feeds the cube the Activity Id is the unique primary key / index.

After some initial heartache (my first attempt was to try detail filters to restrict my data) I have since learned a bit about how the OLAP data source works, especially when it comes to drawing together levels from various dimensions.  The most critical thing I've gained is an understanding that the detail / summary filters are only applied AFTER all the data is returned to the report and are therefore not suitable for OLAP data sources.  So now I've moved on to using filter expressions on data items to reduce the amount of data I'm initially pulling into the report.

My report essentially has two prompts which generate the following parameters:
?param_Activity_Status? : Closed or On Hand
?param_Activity_Team?: Team A, Team B, Team C etc

The cube has a measure called "No. of Acty" and therefore a "real" line of data in the cube is one where No. of Acty > 0.

Each item in my query is a data item with a filter expression, e.g.:
Activity Id: filter([CubeDatasource].[Activity].[Activity].[Acty_Id],[No. of Acty] > 0)
Activity Team: filter([CubeDatasource].[Activity].[Activity].[Acty_Owner_Team],[No. of Acty] > 0)
Product: filter([CubeDatasource].[Classification].[Classification].[Case Product],[No. of Acty] > 0)

I currently have two slicers on the query too:
Slicer1: [CubeDatasource].[Status].[Status].[Status Category] = ?param_Activity_Status?
Slicer2: [CubeDatasource].[Activity].[Activity].[Acty_Owner_Team] -> ?param_Activity_Team?

The query runs successfully.

My three questions are:

• Can I move the additional conditions from the slicer to the relevant data items?
• If yes to above, what is the correct syntax for having multiple test conditions within the filter expression?
• Would I actually gain anything (speed wise) from moving the test from the slicer to the data item, or is the processing essentially the same?

I'm sorry if I've included too much detail here (or not enough).  I'm still trying to wrap my head around the system and am unsure exactly how much information might be required to answer these questions.

Thanks in advance for any advice you might be able to provide.

Andrew.

[Bark]

Hi,

When you say "No. of acty" is 0, is it 0 in the database or it appears after the generation because there is no join in that particular cell? if you build the report without the filters and then supress zeros in the table, does it give you the expected results?

Why is your first slicer using "=" instead of "->"? filtering on an attribute is slower than on a memeber, solving that might improve performance.

Regards,

Bark

[AndrewYoung]

Thanks Bark.

When you say "No. of acty" is 0, is it 0 in the database or it appears after the generation because there is no join in that particular cell? if you build the report without the filters and then supress zeros in the table, does it give you the expected results?

The 0's appear after generation, there are no 0 rows in the database table.  I can't actually build the report without the filters because it's too big (complex?) for the report to actually run.  It just goes into processing mode and hangs there indefinitely.  Well, I suppose it's not indefinitely but I've canned it after an hour and my run time needs to be measured in seconds.  Having said that, if I cut the number of dimensions used in the report down to only 3 or 4, and then run with suppress zeros, it runs.  It's worth noting that the report currently runs in the format I have it, I was just look to see if I could make it more efficient.

Why is your first slicer using "=" instead of "->"?

Good question to which I have no answer other than "due to my own stupidity."  Thanks for pointing that out.

What I'd wanted to be able to do (if possible) was create data items that were something like this:
filter([CubeDatasource].[Activity].[Activity].[Acty_Owner_Team],[No. of Acty] > 0 and [CubeDatasource].[Activity].[Activity].[Acty_Owner_Team] -> ?param_Activity_Team?)

Obviously that doesn't work but is there a proper way of writing that to achieve that kind of result?  It would be great if there was a way of including both prompts on every filtered data item so that I knew I was only pulling back to the report the smallest relevant dataset, rather than bringing back a large dataset and suppressing the unnecessary rows in the report.  Something like this:
filter([CubeDatasource].[Classification].[Classification].[Case Product],[No. of Acty] > 0 and [CubeDatasource].[Activity].[Activity].[Acty_Owner_Team] -> ?param_Activity_Team? and [CubeDatasource].[Status].[Status].[Status Category] = ?param_Activity_Status?)

Perhaps that's over-complicating the situation though?  I suppose my goal is to do as much of the filtering at the source rather than in the rendering of the report.

Thanks again for your help.

Andrew.

[RKMI]

Hi Andrew,

I would recommend to the keep the slicers as is sicne it doesn't make a difference to trying to write a complex syntax to acheive the same end result. Instead combine the filters into one
filter([CubeDatasource].[Activity].[Activity].[Acty_Id],[No. of Acty] > 0) and filter([CubeDatasource].[Activity].[Activity].[Acty_Owner_Team],[No. of Acty] > 0) and filter([CubeDatasource].[Classification].[Classification].[Case Product],[No. of Acty] > 0)

But, if you are still interested in a syntax, it would look something like this,

tuple ( filter(tuple([CubeDatasource].[Activity].[Activity].[Acty_Owner_Team],[No. of Acty]) > 0), [Acty_Owner_Team], [Status Category])

also add 2 dataitems to maintain the prompt values for
1. Acty_Owner_Team:   [CubeDatasource].[Activity].[Activity].[Acty_Owner_Team] -> ?param_Activity_Team?
2. Status Category: [CubeDatasource].[Status].[Status].[Status Category] = ?param_Activity_Status?

Thanks,
RK