How to model Cognos Framework Manager to generate most efficient SQL query (Part2)

At which layer should filter(s) be added in Cognos Framework manager?

Check list of all posts

There are 5 choices, the test result is listed below:
1. The best one: Relationship at database layer and  filter at business layer
2. The good one: Relationship at database layer and  filter in SQL at database layer
3. The bad one: Relationship at database layer and  filter at database layer
4. The bad one: Relationship at business layer and  filter at Business layer
5. The bad one: Relationship at business layer and  filter at database layer

Sample data

DROP TABLE SCHEMEX.DIM1 IF EXISTS;
CREATE TABLE SCHEMEX.DIM1 ( ID INTEGER, ATTR1 CHARACTER VARYING(50), ATTR2 CHARACTER VARYING(50));
insert into SCHEMEX.DIM1 VALUES (1,'ATTR1_1','ATTR2_1');
insert into SCHEMEX.DIM1 VALUES (2,'ATTR1_2','ATTR2_2');
DROP TABLE SCHEMEX.DIM2 IF EXISTS;
CREATE TABLE SCHEMEX.DIM2( ID INTEGER, ATTR1 CHARACTER VARYING(50), ATTR2 CHARACTER VARYING(50));
insert into SCHEMEX.DIM2 VALUES (1,'ATTR1_1','ATTR2_1');
insert into SCHEMEX.DIM2 VALUES (2,'ATTR1_2','ATTR2_2');
DROP TABLE SCHEMEX.DIM3 IF EXISTS;
CREATE TABLE SCHEMEX.DIM3( ID INTEGER, ATTR1 CHARACTER VARYING(50), ATTR2 CHARACTER VARYING(50));
insert into SCHEMEX.DIM3 VALUES (1,'ATTR1_1','ATTR2_1');
insert into SCHEMEX.DIM3 VALUES (2,'ATTR1_2','ATTR2_2');
DROP TABLESCHEMEX.FACT1 IF EXISTS;
CREATE TABLE SCHEMEX.FACT1( DIM1_ID INTEGER, DIM2_ID INTEGER, DIM3_ID INTEGER,  MEASURE1 INTEGER, MEASURE2 INTEGER);
insert into SCHEMEX.FACT1 VALUES (1,1,1,1,1);
insert into SCHEMEX.FACT1 VALUES (2,2,2,2,2);

1. The best one: Relationship at database layer and  filter at business layer
Cognos FM model

Select DIM3.ID and FACT1.MESURE1 – efficient SQL generated
SELECT
 DIM3.ID ID ,
 SUM(FACT1.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM3, SCHEMEX.FACT1 FACT1
WHERE FACT1.DIM1_ID = 1
 AND FACT1.DIM3_ID = DIM3.ID
GROUP BY DIM3.ID

2. The good one: Relationship at database layer and  filter in SQL at database layer
Cognos FM model

Select DIM3.ID and FACT1.MESURE1 – not efficient SQL generated, however, this solution is to get the base query filtered, also this filter can be leveraged  with prompt, such as date range selected from report.

WITH FACT12 AS
(
 SELECT *
 FROM SCHEMEX.FACT1
 WHERE DIM1_ID = 1
)
SELECT
 DIM3.ID ID ,
 SUM(FACT12.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM3, FACT12
WHERE FACT12.DIM3_ID = DIM3.ID
GROUP BY DIM3.ID

3. The bad one: Relationship at database layer and  filter at database layer
Cognos FM model

Select DIM3.ID and FACT1.MESURE1  - not efficient SQL generated
WITH FACT13 AS
(
 SELECT
  FACT1.DIM3_ID DIM3_ID ,
  FACT1.MEASURE1 MEASURE1
 FROM SCHEMEX.FACT1 FACT1
 WHERE FACT1.DIM1_ID = 1
)
SELECT
 DIM3.ID ID ,
 SUM(FACT13.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM3, FACT13
WHERE FACT13.DIM3_ID = DIM3.ID
GROUP BY DIM3.ID;

4. The bad one: Relationship at business layer and  filter at Business layer
Cognos FM model

Select DIM3.ID and FACT1.MESURE1  - not efficient SQL generated
WITH FACT15 AS
(
 SELECT
  FACT1.DIM3_ID DIM3_ID ,
  FACT1.MEASURE1 MEASURE1
 FROM SCHEMEX.FACT1 FACT1
 WHERE FACT1.DIM1_ID = 1
)
SELECT
 DIM34.ID ID ,
 SUM(FACT15.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM34, FACT15
WHERE FACT15.DIM3_ID = DIM34.ID
GROUP BY DIM34.ID;

5. The bad one: Relationship at business layer and  filter at database layer
Cognos FM model

Select DIM3.ID and FACT1.MESURE1 -- not efficient SQL generated, this is probably the worst one when comparing with last case, as the base query selects all columns regardless whether they are used or not.
WITH FACT15 AS
(
 SELECT
  FACT1.DIM1_ID DIM1_ID ,
  FACT1.DIM2_ID DIM2_ID ,
  FACT1.DIM3_ID DIM3_ID ,
  FACT1.MEASURE1 MEASURE1 ,
  FACT1.MEASURE2 MEASURE2
 FROM SCHEMEX.FACT1 FACT1
 WHERE FACT1.DIM1_ID = 1
)
SELECT
 DIM36.ID ID ,
 SUM(FACT17.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM36, FACT15 FACT17
WHERE FACT17.DIM3_ID = DIM36.ID
GROUP BY DIM36.ID;