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

At which layer should the relationship be created in Cognos Framework manager?

Check list of all posts

There are three choice, the test result is listed below:
The best one: Only at database layer
The good one: Only at business layer
The bad one: at both database layer and 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);

The best done: Only at database 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 DIM3.ID = FACT1.DIM3_ID
GROUP BY DIM3.ID

The good one: Only 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 DIM3.ID = FACT1.DIM3_ID
GROUP BY DIM3.ID
It looks like the SQL is same as SQL generated by having relationship at database layer, however, the problem is that this model does not support derived columns and filters. Please refer to other topics in this blog for detail.

The bad one: at both database layer and business Layer

case without combined Model subject
Cognos FM model

 

 

RQP-DEF-0103 Cross joins (between query subjects: [Data Source Layer].[DIM1], [Business Layer].[FACT1]) are not permitted for the user who has the identity '*'.
RQP-DEF-0103 Cross joins (between query subjects: [Data Source Layer].[DIM2], [Business Layer].[FACT1]) are not permitted for the user who has the identity '*'.
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 DIM3.ID = FACT1.DIM3_ID
GROUP BY DIM3.ID

case with combined Model subject
Cognos FM model

 

Select DIM3.ID and FACT1.MESURE1 – NOT efficient SQL generated, as the join with DIM1 and DIM2 is applied, even both tables are not involved.
select
       DIM3.ID  as  ID,
       SUM(FACT.MEASURE1)  as  MEASURE1
 from
       SCHEMEX.DIM3 DIM3,
       (select
              FACT1.DIM3_ID  as  DIM3_ID,
              FACT1.MEASURE1  as  MEASURE1
        from
              SCHEMEX.FACT1 FACT1,
              SCHEMEX.DIM1 DIM1,
              SCHEMEX.DIM2 DIM2
        where
              (DIM1.ID = FACT1.DIM1_ID) and
              (DIM2.ID = FACT1.DIM2_ID)
       ) FACT
 where
       (FACT.DIM3_ID = DIM3.ID)
 group by
       DIM3.ID