Filterung on DMR

[IceTea]

Hi all,

at many plances in the big Cognos world there are statements that you shouldn't do detail filters in queries on DMR Data.

But as i know, and please correct me if i'm wrong on one ore more things, this are the facts:

- Based on your query and the dimensional functions you use, Cognos generates a SQL statements to receive the data from the relational data source
- in an environment with classic Querymode, you can see the generated SQL directly from within the Report Studio, in an environment with DQM, you only will see MDX. But in the query enginge, SQL is also generated to to receive data.
- After receiving the data, Cognos builds a little temporary cube
- This temporary cube is accessed to to dimensional functions and calculations and after doing all the processing the reports shows the data
- You can limit the data which is retrieved for this temp cube by creating detail filters
- You can limit the data which is retrieved for this temp cube by creating Slicers
- Limiting the data should be the target to get a fast response from any function and calculation on the temp cube

Example:
- Inserting the member Jan/2014 to a crosstab doesn't affect query for filtering the data received for building up the temp cube. All data for all months is retrieved. Only Jan/2014 is shown
- Inserting  the member Jan/2014 to a crosstab AND adding a slicer or detail filter for Jan/2014  affects the retrieved data. Only data for Jan/2014 is inside the temp cube and is used for all the calculations.

So my actual strategy about this kind of reports is to implement as much slicers or detail filters to avoid unrelevant data in my temp cube.

Right or wrong?

[bdbits]

Beware the wrath of CognosPaul. You have been warned.  ;D

In what may be the mother of all posts on the subject, I humbly submit a link to a posting here at Cognoise. Says it better than I ever could hope to achieve.
http://www.cognoise.com/index.php/topic,18290.5.html

[navissar]

Right, let's start.

- Based on your query and the dimensional functions you use, Cognos generates a SQL statements to receive the data from the relational data source

Not quite. Based on your DMR structure (In framework) and your query Cognos generates SQL (This is only true for CQM, by the way).

- in an environment with classic Querymode, you can see the generated SQL directly from within the Report Studio, in an environment with DQM, you only will see MDX. But in the query enginge, SQL is also generated to to receive data.

This is, again, partially true. to pull data from a relational source eventually always SQL is manufactured. However, in DQM env. the data you query may already exist in cache, in which case SQL will not be created.

- After receiving the data, Cognos builds a little temporary cube

The process is slightly more complex than that, and depends on whether your on CQM or DQM.

- This temporary cube is accessed to to dimensional functions and calculations and after doing all the processing the reports shows the data

That's true for CQM and assuming you're using a crosstab or a chart and not a list. 

- You can limit the data which is retrieved for this temp cube by creating detail filters

Not if the filters are created in RS. This is DMR, so the data will be pulled into a server side cube and then filtered.  If you use detail filters on a DMR, you'll get in trouble. Not just because CognosPaul will unleash hell on you - though it is reason enough - but also because your data will be distorted. Making good decision based on bad data is near impossible, so let's try to keep the data good.

- You can limit the data which is retrieved for this temp cube by creating Slicers

Nope. Same reason as above.

- Limiting the data should be the target to get a fast response from any function and calculation on the temp cube

Not entirely accurate. I have a cube at work that's nearly 1TB in size. I have a dashboard on that cube that contains 6 charts, all of them are aggregating millions of rows over 3 and more dimensions, with complex tuples and slicers. The dashboard loads in 2 seconds flat. Then, for the same customer there's a cube that's hardly over 1GB in size, and a simple report over it can take up to 6 seconds to load. Why? Because OLAP performance is not really just a question of size. How complex are the dimensions? How deep do they go? How many measures are there, how do they aggregate? and so on and so forth, these are all questions that affect performance.
Let's also remember that the temp cube can be re-used, so that it doesn't need to be created on every run.

Example:
- Inserting the member Jan/2014 to a crosstab doesn't affect query for filtering the data received for building up the temp cube. All data for all months is retrieved. Only Jan/2014 is shown
- Inserting  the member Jan/2014 to a crosstab AND adding a slicer or detail filter for Jan/2014  affects the retrieved data. Only data for Jan/2014 is inside the temp cube and is used for all the calculations.

So my actual strategy about this kind of reports is to implement as much slicers or detail filters to avoid unrelevant data in my temp cube.

Let's take this example. Suppose you bring all months. Let's say you have 10 years' worth of data, that's 120 months. 120 members is nothing. Limiting this will have zero impact on your report. But also, you won't be limiting it at all - filters and slicers in RS will be applied after the basic data has been fetched. So basically, you'll be adding another complexity to your query with the slicer, for not much result. And keep in mind - temp cube is reusable, limiting it means recreating it for every scenario.

So, to sum up - I wouldn't say "Right"...

[IceTea]

Thanks a lot guys for this discussion.

Limiting this will have zero impact on your report. But also, you won't be limiting it at all - filters and slicers in RS will be applied after the basic data has been fetched.

You mentioned in your post that there is some difference between CQM and DQM. Maybe we can figure it out a bit deeper what's going on on DMR modelling with DQM compared to CQM.

I had experiences with DMR-Performancetuning on Cognos 8.4.1 and a Oracle Database. What we saw there was that described behaviour - now referring to my example - that WITHOUT a detailfilter or slicer on a single month (Jan/2014) the SQL generated WITHOUT a where-clause which effectivley results in pulling much more data than needed for the report (remember, the crosstab should only show data for Jan/2014). With the detailfilter we saw the where-clause in the SQL and report ran faster.

That is the date-reducing-effect of detail filters i mentioned above in my consideration.

Meanwhile, with the introduction of DQM, it sounds to me that this behaviour has been changed. Could you bring some more light in to this topic?

[navissar]

DMRs used to be a last resort because of performance issues. With DQM, they're a lot better, because DQM uses cache and hybrid queries and reduces significantly the run time.

[mrcool]

Thanks a lot guys for this discussion.

You mentioned in your post that there is some difference between CQM and DQM. Maybe we can figure it out a bit deeper what's going on on DMR modelling with DQM compared to CQM.

I had experiences with DMR-Performancetuning on Cognos 8.4.1 and a Oracle Database. What we saw there was that described behaviour - now referring to my example - that WITHOUT a detailfilter or slicer on a single month (Jan/2014) the SQL generated WITHOUT a where-clause which effectivley results in pulling much more data than needed for the report (remember, the crosstab should only show data for Jan/2014). With the detailfilter we saw the where-clause in the SQL and report ran faster.

That is the date-reducing-effect of detail filters i mentioned above in my consideration.

Meanwhile, with the introduction of DQM, it sounds to me that this behaviour has been changed. Could you bring some more light in to this topic?

Hi icetea,

What is the reason behind going with DMR instead of relational model?
We are in same situation as you and trying to find out best practices for developing reports using DMR. Even I observed that detail filters are working fine with DMR as I could see the filters passing through SQL.
What is your final go?Did you use detail filters?How's the performance?

Thanks,
mc