Dimensional filtering techniques vs. relational filtering techniques for cubes

[abcuser]

Hi,
using Cognos 10.2.1 fixpack 6 accessing Cognos TM1 cube 10.1.1 fixpack 2. Cognos BI package type is DQM.

I have seen a lot of information on web to use "dimensional filtering techniques" vs. "relational filtering techniques" and there is recommendation to use "dimensional" approach for cubes. I wonder is this statement still valid or is this an "old info" valid for older versions of Cognos BI product. I did a test and can't see the difference.

TEST 1: Dimensional filtering technique
1. Create new crosstab.
2. Columns: [Quantity]
3. Rows: Query Calculation | Other expression and in Expression Definition type in:
filter([CubeName].[TimeDimension].[TimeHierarchy].[TimeYearLevel],[Quantity]>100)
4. Run report (I created a print screen and paste it in image editor to not lose the result).
5. Tools | Show generated SQL/MDX. Copy the native MDX to text editor.

TEST 2: Relational filtering technique
1. Create new crosstab.
2. Columns: [Quantity]
3. Rows: TimeYearLevel
4. Click somewhere in crosstab and Data | Filters | Edit Filters.
5. On default Detail filters click on Add button.
6. Advanced.
7. In Expression Definition type in: [Quantity]>100
8. Run report and compare the values with "TEST 1" step 4 result. The results are identical.
9. Tools | Show generated SQL/MDX and compare the MDX with "TEST 1" step 5 MDX. The results are identical.

Now my question. It looks to me that this two technique have the same result. Is this: "Use dimensional filtering technique for cubes" recommendation based on created MDX still valid argument for never versions of Cognos BI or is it irrelevant?
Regards

[MFGF]

Hi,
using Cognos 10.2.1 fixpack 6 accessing Cognos TM1 cube 10.1.1 fixpack 2. Cognos BI package type is DQM.

I have seen a lot of information on web to use "dimensional filtering techniques" vs. "relational filtering techniques" and there is recommendation to use "dimensional" approach for cubes. I wonder is this statement still valid or is this an "old info" valid for older versions of Cognos BI product. I did a test and can't see the difference.

TEST 1: Dimensional filtering technique
1. Create new crosstab.
2. Columns: [Quantity]
3. Rows: Query Calculation | Other expression and in Expression Definition type in:
filter([CubeName].[TimeDimension].[TimeHierarchy].[TimeYearLevel],[Quantity]>100)
4. Run report (I created a print screen and paste it in image editor to not lose the result).
5. Tools | Show generated SQL/MDX. Copy the native MDX to text editor.

TEST 2: Relational filtering technique
1. Create new crosstab.
2. Columns: [Quantity]
3. Rows: TimeYearLevel
4. Click somewhere in crosstab and Data | Filters | Edit Filters.
5. On default Detail filters click on Add button.
6. Advanced.
7. In Expression Definition type in: [Quantity]>100
8. Run report and compare the values with "TEST 1" step 4 result. The results are identical.
9. Tools | Show generated SQL/MDX and compare the MDX with "TEST 1" step 5 MDX. The results are identical.

Now my question. It looks to me that this two technique have the same result. Is this: "Use dimensional filtering technique for cubes" recommendation based on created MDX still valid argument for never versions of Cognos BI or is it irrelevant?
Regards

Hi,

In super-simple situations, such as your test case above, you might find that detail filters will give you the right answer. Be under no illusion, though, as soon as you try to extend this and do more, you're in DEEP trouble using detail filters with your OLAP source. To see why, I have great pleasure in pointing you to the witty, informative words of Paul - one of the all-time Cognos BI rockstars...

http://www.cognoise.com/index.php/topic,18290.msg53585.html#msg53585
http://cognospaul.com/2012/01/29/why-detail-filters-should-never-be-used-in-olap/

Having read his post above and his blog, hopefully you can see that dimensional reporting should not be done using relational techniques :)

Cheers!

MF.

[bdbits]

The legendary, definitive post on the subject. I for one have had it bookmarked for some time.  8)

I wonder if we should link to it as part of the FAQs?

[Lynn]

The legendary, definitive post on the subject. I for one have had it bookmarked for some time.  8)

I wonder if we should link to it as part of the FAQs?

Excellent suggestion!