Problem

In order to explain this issue in detail, we create a sample below.

C3 is only used for FACT1. The M2 in FACT2, or denominator is based on C1 and C2 only. The challenge is how to make Cognos FM model be flexible enough to allow report authors to generate report by simply drag and drop, using relational model or DMR model.

Analysis

It looks like there are a few options to try**Option1: Using UNION to bring both datasets together**

Data looks like below

This is a problem, as data cannot be aligned in report.

**Option2: Using JOIN to bring both dataset together**

Report

Even we apply semi aggregate function, we still cannot generate the current report.

**Option3: Using STICTCH QUARY to bring both dataset together**

Cognos has two core concepts, stitched query and determinant. However, this sample is not a typical sample, as C3 is not shared dimension. To resolve this problem, we can “FAKE” an extra column for FACT2 with value “total”. We can also “FAKE” an extra column for C3 with value “total”. Doing this way will bring all three dimensions as confirmed and shared dimensions, where the relationship between C3 and FACT2 is connect at “Total” level. This relationship can be further adjusted by determinant to avoid the double count.

Solution

Below is the FM design screenshot, where we get 3 dimensions are designed and turn the design as multiple fact tables with different granularities.

Create a faked column as C3_Total in FACT2

[Data Source Layer].[ FACT2]: select C1, C2, M2, 'Total' as C3_Total from [DATASOURCE1].FACT2

Create a view as V_C3 with a fake column Total, the reason we create this view is because only table or view can be supported for Cognos determinant.

[Data Source Layer].[ C3]: select * from [DATASOURCE1].V_C3

CREATE OR REPLACE VIEW SCHEMEX.V_C3 AS SELECT 'Total' AS TOTAL, SCHEMAX.C3.ID FROM C3

Result analysis

**Case 1:**

Report:

SQL:

**select**

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2

from

(

select

C3.ID ID ,

sum(FACT1.M1) M1

from SCHEMAX.C3 C3, SCHEMAX.FACT1 FACT1

where FACT1.C3 = C3.ID group by C3.ID

) D2,

(

select

sum(M2) M2

from SCHEMAX.FACT2

having count(*) > 0

) D3

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2

from

(

select

C3.ID ID ,

sum(FACT1.M1) M1

from SCHEMAX.C3 C3, SCHEMAX.FACT1 FACT1

where FACT1.C3 = C3.ID group by C3.ID

) D2,

(

select

sum(M2) M2

from SCHEMAX.FACT2

having count(*) > 0

) D3

Data:

**Case 2:**Report:

SQL:

**select**

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2 ,

(D2.rc * 1.0e0) / nullif(D3.rc, 0) C____M1__M2_ ,

min(D2.Summary_M1_) over () Summary_M1_ ,

min(D3.Summary_M2_) over () Summary_M2_ ,

(min(D2.rc5) over () * 1.0e0) / nullif(min(D3.rc) over (), 0) Summary____M1__M2__

from (

select

T0.C0 ID ,

T0.C1 M1 ,

T0.C1 rc

, sum(T0.C1) over () Summary_M1_ ,

sum(T0.C1) over () rc5

from (

select C3.ID C0 , sum(FACT1.M1) C1

from SCHEMAX.C3 C3, SCHEMAX.FACT1 FACT1

where FACT1.C3 = C3.ID group by C3.ID

) T0

) D2,

(

select

distinct T0.C0 M2 ,

T0.C0 rc ,

T0.C0 Summary_M2_

from

(

select sum(M2) C0 from SCHEMAX.FACT2

) T0

) D3

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2 ,

(D2.rc * 1.0e0) / nullif(D3.rc, 0) C____M1__M2_ ,

min(D2.Summary_M1_) over () Summary_M1_ ,

min(D3.Summary_M2_) over () Summary_M2_ ,

(min(D2.rc5) over () * 1.0e0) / nullif(min(D3.rc) over (), 0) Summary____M1__M2__

from (

select

T0.C0 ID ,

T0.C1 M1 ,

T0.C1 rc

, sum(T0.C1) over () Summary_M1_ ,

sum(T0.C1) over () rc5

from (

select C3.ID C0 , sum(FACT1.M1) C1

from SCHEMAX.C3 C3, SCHEMAX.FACT1 FACT1

where FACT1.C3 = C3.ID group by C3.ID

) T0

) D2,

(

select

distinct T0.C0 M2 ,

T0.C0 rc ,

T0.C0 Summary_M2_

from

(

select sum(M2) C0 from SCHEMAX.FACT2

) T0

) D3

Data:

**Case 3:**Report:

SQL:

**select**

(coalesce(D2.ID1, D3.ID1)) ID1 ,

(coalesce(D2.ID2, D3.ID2)) ID2 ,

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2

from

(

select

C1.ID ID1 ,

C2.ID ID2 ,

C3.ID ID ,

sum(FACT1.M1) M1

from SCHEMAX

(coalesce(D2.ID1, D3.ID1)) ID1 ,

(coalesce(D2.ID2, D3.ID2)) ID2 ,

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2

from

(

select

C1.ID ID1 ,

C2.ID ID2 ,

C3.ID ID ,

sum(FACT1.M1) M1

from SCHEMAX

**.C1 C1,**

SCHEMAX.C2 C2,

SCHEMAX.V_C3 C3,

SCHEMAX.FACT1 FACT1

where FACT1.C1 = C1.ID

and FACT1.C2 = C2.ID

and FACT1.C3 = C3.ID

group by C3.ID, C1.ID, C2.ID

) D2

FULL OUTER JOIN

(

select C1.ID ID1 ,

C2.ID ID2 ,

sum(FACT2.M2) M2

from SCHEMAX.C1 C1,

SCHEMAX.C2 C2,

SCHEMAX.FACT2 FACT2,

(

select C3.TOTAL TOTAL

from SCHEMAX.V_C3 C3

group by C3.TOTAL

) C3

where C1.ID = FACT2.C1

and C2.ID = FACT2.C2

and C3.TOTAL = 'Total'

group by C1.ID, C2.ID

) D3

on D2.ID1 = D3.ID1 and D2.ID2 = D3.ID2;

SCHEMAX.C2 C2,

SCHEMAX.V_C3 C3,

SCHEMAX.FACT1 FACT1

where FACT1.C1 = C1.ID

and FACT1.C2 = C2.ID

and FACT1.C3 = C3.ID

group by C3.ID, C1.ID, C2.ID

) D2

FULL OUTER JOIN

(

select C1.ID ID1 ,

C2.ID ID2 ,

sum(FACT2.M2) M2

from SCHEMAX.C1 C1,

SCHEMAX.C2 C2,

SCHEMAX.FACT2 FACT2,

(

select C3.TOTAL TOTAL

from SCHEMAX.V_C3 C3

group by C3.TOTAL

) C3

where C1.ID = FACT2.C1

and C2.ID = FACT2.C2

and C3.TOTAL = 'Total'

group by C1.ID, C2.ID

) D3

on D2.ID1 = D3.ID1 and D2.ID2 = D3.ID2;

Data:

**Case 4:**Report:

SQL:

**select**

(coalesce(D2.ID1, D3.ID1)) ID1 ,

(coalesce(D2.ID2, D3.ID2)) ID2 ,

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2

from

(

select

C1.ID ID1 ,

C2.ID ID2 ,

C3.ID ID ,

sum(FACT1.M1) M1

from SCHEMAX.C1 C1,

SCHEMAX.C2 C2,

SCHEMAX.V_C3 C3,

SCHEMAX.FACT1 FACT1

where FACT1.C1 = C1.ID

and FACT1.C2 = C2.ID

and FACT1.C3 = C3.ID

group by C3.ID, C1.ID, C2.ID

) D2

FULL OUTER JOIN

(

select C1.ID ID1 ,

C2.ID ID2 ,

sum(FACT2.M2) M2

from SCHEMAX.C1 C1,

SCHEMAX.C2 C2,

SCHEMAX.FACT2 FACT2,

(

select C3.TOTAL TOTAL

from SCHEMAX.V_C3 C3

group by C3.TOTAL

) C3

where C1.ID = FACT2.C1

and C2.ID = FACT2.C2

and C3.TOTAL = 'Total'

group by C1.ID, C2.ID

) D3

on D2.ID1 = D3.ID1 and D2.ID2 = D3.ID2;

(coalesce(D2.ID1, D3.ID1)) ID1 ,

(coalesce(D2.ID2, D3.ID2)) ID2 ,

D2.ID ID ,

D2.M1 M1 ,

D3.M2 M2

from

(

select

C1.ID ID1 ,

C2.ID ID2 ,

C3.ID ID ,

sum(FACT1.M1) M1

from SCHEMAX.C1 C1,

SCHEMAX.C2 C2,

SCHEMAX.V_C3 C3,

SCHEMAX.FACT1 FACT1

where FACT1.C1 = C1.ID

and FACT1.C2 = C2.ID

and FACT1.C3 = C3.ID

group by C3.ID, C1.ID, C2.ID

) D2

FULL OUTER JOIN

(

select C1.ID ID1 ,

C2.ID ID2 ,

sum(FACT2.M2) M2

from SCHEMAX.C1 C1,

SCHEMAX.C2 C2,

SCHEMAX.FACT2 FACT2,

(

select C3.TOTAL TOTAL

from SCHEMAX.V_C3 C3

group by C3.TOTAL

) C3

where C1.ID = FACT2.C1

and C2.ID = FACT2.C2

and C3.TOTAL = 'Total'

group by C1.ID, C2.ID

) D3

on D2.ID1 = D3.ID1 and D2.ID2 = D3.ID2;

Data: