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

At which layer should a query column be derived in Cognos Framework manager?

Check list of all posts

There are 3 choices, the test result is listed below:
1. The best one: Relationship at database layer and  Derived Column at business layer
2. The bad one: Relationship at database layer and  Derived Column in SQL at database layer
3. The bad one: Relationship at business layer and  Derived Column at Business 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 TABLE SCHEMEX.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 Derived Column at business layer
Cognos FM model

Select DIM3.ID,  FACT1. DerivedColumn1 and FACT1.MESURE1  - not efficient SQL generated

SELECT
 DIM3.ID ID ,
 CASE WHEN FACT1.DIM1_ID = 1 THEN 'DIM 1 ID IS #1'
 ELSE 'DIM 1 ID IS NOT #1' END DERIVEDCOLUMN1 ,
 SUM(FACT1.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM3, SCHEMEX.FACT1 FACT1
WHERE FACT1.DIM3_ID = DIM3.ID
GROUP BY DIM3.ID, CASE WHEN FACT1.DIM1_ID = 1 THEN 'DIM 1 ID IS #1'
 ELSE 'DIM 1 ID IS NOT #1' END

2.The bad one: Relationship at database layer and  Derived Column  in SQL at database layer
Cognos FM model

Select DIM3.ID,  FACT1. DerivedColumn1 and FACT1.MESURE1  - not efficient SQL generated

WITH FACT14 AS
(
 SELECT
  DIM1_ID,
  DIM2_ID,
  DIM3_ID,
  MEASURE1,
  MEASURE2,
  CASE WHEN DIM1_ID = 1 THEN 'DIM 1 ID IS #1'
   ELSE 'DIM 1 ID IS NOT #1' END DERVIEDCOLUMN1
 FROM SCHEMEX.FACT1
)
SELECT
 DIM35.ID ID ,
 FACT16.DERVIEDCOLUMN1 DERVIEDCOLUMN1 ,
 SUM(FACT16.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM35, FACT14 FACT16
WHERE FACT16.DIM3_ID = DIM35.ID
GROUP BY DIM35.ID, FACT16.DERVIEDCOLUMN1;


3.The bad one: Relationship at business layer and  Derived Column at Business layer

Cognos FM model
 

Select DIM3.ID,  FACT1. DerivedColumn1 and FACT1.MESURE1  - not efficient SQL generated

WITH FACT15 AS
(
 SELECT FACT1.DIM3_ID DIM3_ID , FACT1.MEASURE1 MEASURE1 ,
 CASE
 WHEN FACT1.DIM1_ID = 1 THEN 'DIM 1 ID IS #1' ELSE 'DIM 1 ID IS NOT #1' END DERIVEDCOLUMN1
 FROM SCHEMEX.FACT1 FACT1
)
SELECT
 DIM34.ID ID ,
 FACT15.DERIVEDCOLUMN1 DERIVEDCOLUMN1 ,
 SUM(FACT15.MEASURE1) MEASURE1
FROM SCHEMEX.DIM3 DIM34, FACT15
WHERE FACT15.DIM3_ID = DIM34.ID
GROUP BY DIM34.ID, FACT15.DERIVEDCOLUMN1;