Detail Filter make report slow down

[joecog]

Hi,

I had add in a detail filter  [Ori].[Chain].[Chain].[Sub_Chain]not in('New','Old','Normal','OTHER') into a report but after add in, and try run the report, it takes very long time to show out.
If I take out the filter it will run very fast.

Possible to have the detail filter and at the same time make this report runs faster?

Joe

[Lynn]

Is this a relational data source?

If so, I suggest you take a look at the SQL being executed and speak with your DBA about it. Maybe there is an indexing issue.

[CognosPaul]

A relational reference has three parts, [Namespace].

.[Field].  Joe's problem statement has four parts, [Cube].[Dimension].[Hierarchy].[Level]. Thus it must be a dimensional model. Either DMR or a proper OLAP cube. So the question then becomes is this DRM or a cube? The next question is sub_chain (or the descendants) being displayed in the report, or should this only effect the measure? If it's DMR then a detail filter is acceptable, but you should be using one of the attributes of the field. [Ori].[Chain].[Chain].[Sub_Chain].[Long Name] not in ('New','Old','Normal','OTHER'). The SQL generated, while completely unreadable because of the DMR, will have the correct filter in the where section. If it's a proper OLAP cube you'll need to use set functions and a slicer to achieve your goal. My own rule of thumb is "Never ever ever, on the pain of pain, are you ever allowed to use detail filters on an OLAP cube". I ensure that my students or workers follow that rule when building reports. That being said, Cognos can occasionally work with detail filters, but your problem is not one of them. When using a detail filter Cognos will extract all of the data relevant to your query into a local dataset, and query that with SQL to achieve your goal. This occasionally can result in fairly large (2G+) local files clogging up your server. First I'll make a few assumptions on your cube. Assumption 1: Sub_Chain is not the second level, with Chain being above it. Assumption 2: The members New, Old, Normal and OTHER may appear in more than one chain. Based on those two assumptions you might try putting the following in the slicer. except([Ori].[Chain].[Chain].[Sub_Chain],filter([Ori].[Chain].[Chain].[Sub_Chain],[Ori].[Chain].[Chain].[Sub_Chain].[Long Name] in ('New','Old','Normal','OTHER'))) This would generate a set of all the members in the sub_chain level that match those names, then it would generate a set of all the members in that level minus that first set.

[joecog]

Hi Paul,

This is a DMR and I had put the  [Ori].[Chain].[Chain].[Sub_Chain].[Sub_Chain - Category Code]not in('New','Old','Normal','OTHER') as suggested but the report still runs very slow.

Any idea?

Joe

[CognosPaul]

DMR makes it slightly more difficult to track down issues like these. Try filtering off the field from the original table.

What happens if you filter the opposite way? [Ori].[Chain].[Chain].[Sub_Chain].[Sub_Chain - Category Code]  in('123','abc')

[joecog]

Hi Paul,

I had try the opposite way and the report still run very slow.

Joe

[AussiePete2011]

Hi there,

As this is a DMR, what you need to now provide is information on the joins between tables that make up this heirarchy.  I'd suggest that the issue may very well be found in a join in the relationships.

Because this is a DMR, there may also be a delay in the MDD layer of Cognos but that would only be available if you turned on performance tracing.  There's an existing forum thread on this already.

It may be better if you created a tuple expression rather than using a filter.  A tuple expression will be more dynamic than a filter in this particular case.
Links of interest - http://unofficialcognostraining.blogspot.com/2008/03/filter-vs-tuple.html

links of interest
http://www.ibm.com/developerworks/forums/thread.jspa?messageID=14201185
http://www.ibm.com/developerworks/data/library/cognos/modeling/design/page491.html

Food for thought
Cheers
Peter

[joecog]

Hi AussiePete2011,

As this is my first time using tuple, just want to double check with you,
Let says my crosstab report is as below :-

Sales         Year
Subchain   100
Total          100

Do you mean that instead of using detail filter, I create a new data item that replace the current Subchain where the data item content is as below :-

tuple(Sales, [Ori].[Chain].[Chain].[Sub_Chain]not in('New','Old','Normal','OTHER') )

Joe