Two fact tables with different grain levels wrong results in Query/Report studs

[bladeless]

I have two fact tables with different grain levels: F1 and F2 they are connected in the following way:
F1 (1..1) – Key_id1 – (1..1) Dim1 (1..1) – Key_id1– (1..n) F2 (1..n) – Key_id2 – (1..1) Dim2
Determinants:
F1 – Unique: Key_id1
   Dim1 – Unique: Key_id1
   F2 – Group by: Key_id1

In report studio, I have created a report with one list on report page to test the package and I got wrong results in the list - Fact1 column shows wrong result (the same value in all rows), but Fact2 column shows correct results...
In the list, I have added the following columns:
Dim2.A - F1.Fact1 – F2.Fact2

Could you help me with:
Is it possible to modify Framework model to get correct results in Fact1 column? How to do it?

Generated Native SQL:
select "T1"."C0" AS A
   , "T1"."C1" AS Key_id1
   , "T1"."C2" AS Fact1
   , "T0"."C1" AS Fact2
from (
      select Dim2.A AS "C0"
         , sum(F2.Fact2) AS "C1"
       from Dim2
         , F1
         , F2
         , Dim1
       where F2.Key_id2 = Dim2.Key_id2
         and F2.Key_id1 = Dim1.Key_id1
         and Dim1.Key_id1 = F1.Key_id1
       group by Dim2.A
   ) "T0"
   , (
      select Dim2.A AS "C0"
         , F1.Key_id1 AS "C1"
         , F1.Fact1 AS "C2"
      from Dim2
         , F1
         , F2
         , Dim1
      where F2.Key_id2 = Dim2.Key_id2
         and F2.Key_id1 = Dim1.Key_id1
         and Dim1.Key_id1 = F1.Key_id1
   ) "T1"
where "T1"."C0" = "T0"."C0"
   or "T1"."C0" is null
   and "T0"."C0" is null
order by 2 asc


Generated IBM Cognos SQL:
select
       Dim2.A as A,
       XSUM(F1.Fact1 at F1.Keyid1 )  as  Fact1
       XSUM(F2.Fact2 for Dim2.A)  as  Fact2
from
       Dim2,
       F1,
       F2,
       Dim1
where
       (F2.Key_id2 = Dim2.Key_id2) and
       (F2.Key_id1 = Dim1.Key_id1) and
       (Dim1.Key_id1 = F1.Key_id1)
group by
       Dim2.A

[bus_pass_man]

If I've understood correctly, you've set your relationships to be this.  Are you sure that the relationship between dim1 and f1 is 1.1?

Dim1  1.1 F1
Dim1  1.n F2
Dim 2 1.n F2

What is the relationship between dim 2 and f1?  You seem to be using dim2 in the query so it would need a relationship between it and f1.

It looks like you've defined the determinants in the fact tables.  They need to be in the dimension tables, actually.

If you look at the sample great outdoors warehouse model you will see examples of determinants in use in the time and products dimensions.  The grains of these dimensions are at a lower level of detail than the grains of the product forecast and sales target fact tables.  (Month for both and product line for one and product type for the other ( or something along those lines))

Hopefully that can help you.

[bladeless]

Thank you for your reply, bus_pass_man!

If I've understood correctly, you've set your relationships to be this.  Are you sure that the relationship between dim1 and f1 is 1.1?

Dim1  1.1 F1
Dim1  1.n F2
Dim 2 1.n F2

Yes, it is correct relationships.
Yes, I am sure. In the case - F1 table has two unique keys F1_key_id and key1_id.

What is the relationship between dim 2 and f1?  You seem to be using dim2 in the query so it would need a relationship between it and f1.

There is no relationship between dim 2 and f1. Dim2 is non-conformed dimension. There is only one conformed dimension - Dim1.

[MFGF]

Yes, it is correct relationships.
Yes, I am sure. In the case - F1 table has two unique keys F1_key_id and key1_id.

There is no relationship between dim 2 and f1. Dim2 is non-conformed dimension. There is only one conformed dimension - Dim1.

Hi,

For multi-fact queries to generate appropriate SQL, you need to understand how cardinality affects the assumptions the query engine makes. Whenever it encounters a report that spans more than one query subject, the query engine makes a determination about whether each query subject is a Fact or a Dimension. This isn't based on the usages you define for your query items, it's entirely based on the cardinalities of the relationships. In simple terms, these are the rules it uses:

If a query subject is at the <n> end of *ALL* relationships used in the query, it is treated as a Fact
If a query subject is at the <1> end of *ANY* relationship used in the query, it is treated as a Dimension

In your case, F2 will be identified as a Fact, but F1 will not be because you have defined it's cardinality as 1.1 (ie at the <1> end of a relationship) with Dim1. This means F1 will be identified as a Dimension, and you're going to get entirely inappropriate SQL generated as a result.

What should you do to fix this? Simple. Change the relationship with Dim1 to be Dim1 1.n F1

Secondly, determinants go on the dimensions, not the facts. Remove the determinants from your fact tables. If F1 and F2 each join to Dim1 using the same granular key, you probably don't need determinants in this situation.

Cheers!

MF.

[bladeless]

Thank you, MFGF!

I have done as you told, but I still get wrong results. Here is Generated IBM Cognos SQL:

Code Select Expand

with
D4 as
    (select
           XSUM(F2.Fact2  for Dim2.A )  as  Fact2,
           Dim2.A  as  A
     from
           F2,
           Dim2
     where
           (F2.Key_id2 = Dim2. Key_id2)
     group by
           Dim2.A
    ),
D5 as
    (select distinct
           XSUM(F1.Fact1 )  as  Fact1
     from
           F1
    )
select
       D4.Fact2  as  Fact2,
       D5. Fact1 as  Fact1,
       D4.A as A
from
       D4,
       D5


As you can see there is no join on Key_id1 so the query shows wrong result  :(
I think I missed something in the model, didn't I?

[MFGF]

Thank you, MFGF!

I have done as you told, but I still get wrong results. Here is Generated IBM Cognos SQL:

Code Select Expand

with
D4 as
    (select
           XSUM(F2.Fact2  for Dim2.A )  as  Fact2,
           Dim2.A  as  A
     from
           F2,
           Dim2
     where
           (F2.Key_id2 = Dim2. Key_id2)
     group by
           Dim2.A
    ),
D5 as
    (select distinct
           XSUM(F1.Fact1 )  as  Fact1
     from
           F1
    )
select
       D4.Fact2  as  Fact2,
       D5. Fact1 as  Fact1,
       D4.A as A
from
       D4,
       D5


As you can see there is no join on Key_id1 so the query shows wrong result  :(
I think I missed something in the model, didn't I?

Hi,

There must be more going on here, because with a multi-fact query you should be getting a stitch query. You can recognise a stitch query in the Cognos SQL quite easily - you will see a Full Outer Join between the fact queries, and coalesce() functions for the common dimensional items. I'm seeing neither in the SQL you posted.

Can you confirm the relationships are defined as follows:

Dim1  1..1 <--> 1..n  F1
Dim1  1..1 <--> 1..n  F2
Dim2  1..1 <--> 1..n  F2

ie the cardinality at the dimension end of each relationship is 1..1 and at the fact end of each relationship is 1..n?

MF.

[bladeless]

Hi,

I posted SQL from Report Studio ("Generated SQL" propery option).
Yes, I confirm that the relationships are defined as mentioned.

Here is Generated SQL from Framework Manager:

Code Select Expand

[b]Cognos SQL [/b]
with
D as
    (select
           F2.Fact2  as  Fact2,
           Dim2.A  as  A,
           RSUM(1  order by F2.Fact2 asc  local)  as  sc
     from
           F2,
           Dim2
     where
           (F2.Key_id2 = Dim2.Key_id2)
     order by
           Fact2 asc
    ),
D3 as
    (select
           F1.Fact1  as  Fact1,
           RSUM(1  order by F1.Fact1 asc  local)  as  sc
     from
           F1
     order by
           Fact1 asc
    )
select
       D.Fact2  as  Fact2,
       D3.Fact1  as  Fact1,
       D.A  as  A
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)

[b]Native SQL [/b]
select "F2"."Fact2" AS "C0"
, "Dim2"."A" AS "C1"
from "F2", "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
order by 1 asc

select "F1"."Fact1" AS "C0"
from "F1"
order by 1 asc 


BL.

[MFGF]

Hi,

I posted SQL from Report Studio ("Generated SQL" propery option).
Yes, I confirm that the relationships are defined as mentioned.

Here is Generated SQL from Framework Manager:

Code Select Expand

[b]Cognos SQL [/b]
with
D as
    (select
           F2.Fact2  as  Fact2,
           Dim2.A  as  A,
           RSUM(1  order by F2.Fact2 asc  local)  as  sc
     from
           F2,
           Dim2
     where
           (F2.Key_id2 = Dim2.Key_id2)
     order by
           Fact2 asc
    ),
D3 as
    (select
           F1.Fact1  as  Fact1,
           RSUM(1  order by F1.Fact1 asc  local)  as  sc
     from
           F1
     order by
           Fact1 asc
    )
select
       D.Fact2  as  Fact2,
       D3.Fact1  as  Fact1,
       D.A  as  A
from
       D
        full outer join
       D3
        on (D.sc = D3.sc)

[b]Native SQL [/b]
select "F2"."Fact2" AS "C0"
, "Dim2"."A" AS "C1"
from "F2", "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
order by 1 asc

select "F1"."Fact1" AS "C0"
from "F1"
order by 1 asc 


BL.

Hi,

Just to clarify... Are you selecting an item from the conformed dimension? It looks like you're only bringing in items from F1 and F2 and the non-conformed dimension? Did you check the Auto Sum checkbox in the top right corner when testing in FM?

MF.

[bladeless]

Are you selecting an item from the conformed dimension?

No, I am not.

It looks like you're only bringing in items from F1 and F2 and the non-conformed dimension?

Yes, you are right.

Did you check the Auto Sum checkbox in the top right corner when testing in FM?

No, I didn't.

Here is generated queries with selected an item(Dim1.B) from the conformed dimension and checked "Auto Sum" checkbox:

Code Select Expand

Cognos SQL
with
D as
    (select
           Dim2.A  as  A,
           Dim1.B  as  B,
           XSUM(F2.Fact2  for Dim2.A,Dim1.B )  as  Fact2
     from
           Dim2,
           Dim1,
           F2
     where
           (F2.Key_id2= Dim2.Key_id2) and
           (F2.Key_id1 = Dim1.Key_id1)
     group by
           Dim2.A,
           Dim1.B
    ),
D3 as
    (select
           Dim1.B  as  B,
           XSUM(F1.Fact1  for Dim1.B )  as  Fact1
     from
           Dim1,
           F1
     where
           (Dim1.Key_id1 = F1.Key_id1)
     group by
           Dim1.B
    )
select
       D.Fact2  as  Fact2,
       D3.Fact1  as  Fact1,
       D.A  as  A,
       coalesce(D.B,D3.B)  as  B
from
       D
        full outer join
       D3
        on (D.B = D3.B)

Native SQL
select "D"."Fact2" AS "Fact2"
, "D3"."Fact1" AS "Fact1"
, "D"."A" AS "A"
, (coalesce("D"."B", "D3"."B")) AS "B"
from (
select "Dim2"."A" AS "A"
, "Dim1"."B" AS "B"
, sum("F2"."Fact2") AS "Fact2"
from "Dim2"
, "Dim1"
, "F2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
and "F2"."Key_id1" = "Dim1"."Key_id1"
group by "Dim2"."A", "Dim1"."B") "D"
FULL OUTER JOIN (
select "Dim1"."B" AS "B"
, sum("F1"."Fact1") AS "Fact1"
from "Dim1"
,"F1"
where "Dim1"."Key_id1" = "F1"."Key_id1"
group by "Dim1"."B"
) "D3"
on "D"."B" = "D3"."B"


But I still get wrong results if I don't select an item from the conformed dimension - So, For example, users who use Query Studio could get wrong results.
Is there any chance to avoid the situation?

[MFGF]

No, I am not.
Yes, you are right.
No, I didn't.

Here is generated queries with selected an item(Dim1.B) from the conformed dimension and checked "Auto Sum" checkbox:

Code Select Expand

Cognos SQL
with
D as
    (select
           Dim2.A  as  A,
           Dim1.B  as  B,
           XSUM(F2.Fact2  for Dim2.A,Dim1.B )  as  Fact2
     from
           Dim2,
           Dim1,
           F2
     where
           (F2.Key_id2= Dim2.Key_id2) and
           (F2.Key_id1 = Dim1.Key_id1)
     group by
           Dim2.A,
           Dim1.B
    ),
D3 as
    (select
           Dim1.B  as  B,
           XSUM(F1.Fact1  for Dim1.B )  as  Fact1
     from
           Dim1,
           F1
     where
           (Dim1.Key_id1 = F1.Key_id1)
     group by
           Dim1.B
    )
select
       D.Fact2  as  Fact2,
       D3.Fact1  as  Fact1,
       D.A  as  A,
       coalesce(D.B,D3.B)  as  B
from
       D
        full outer join
       D3
        on (D.B = D3.B)

Native SQL
select "D"."Fact2" AS "Fact2"
, "D3"."Fact1" AS "Fact1"
, "D"."A" AS "A"
, (coalesce("D"."B", "D3"."B")) AS "B"
from (
select "Dim2"."A" AS "A"
, "Dim1"."B" AS "B"
, sum("F2"."Fact2") AS "Fact2"
from "Dim2"
, "Dim1"
, "F2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
and "F2"."Key_id1" = "Dim1"."Key_id1"
group by "Dim2"."A", "Dim1"."B") "D"
FULL OUTER JOIN (
select "Dim1"."B" AS "B"
, sum("F1"."Fact1") AS "Fact1"
from "Dim1"
,"F1"
where "Dim1"."Key_id1" = "F1"."Key_id1"
group by "Dim1"."B"
) "D3"
on "D"."B" = "D3"."B"


But I still get wrong results if I don't select an item from the conformed dimension - So, For example, users who use Query Studio could get wrong results.
Is there any chance to avoid the situation?

Hi,

The query you posted above looks perfect to me. Are you saying it returns incorrect results? If so, how are they incorrect?

If you don't select an item from the conformed dimension, what results and SQL do you see (with Auto Sum checked so the query emulates something in Query Studio/Report Studio)?

MF.

[bladeless]

The query you posted above looks perfect to me. Are you saying it returns incorrect results? If so, how are they incorrect?

No, it returns correct results while there is an item from the conformed dimension.

If you don't select an item from the conformed dimension, what results and SQL do you see ... ?

Code Select Expand

Cognos SQL
with
D as
    (select
           XSUM(F2.Fact2  for Dim2.A )  as  Fact2,
           Dim2.A  as  A
     from
           F2,
           Dim2
     where
           (F2.Key_id2 = Dim2.Key_id2)
     group by
           Dim2.A
    ),
D3 as
    (select distinct
           XSUM(F1.Fact1 )  as  Fact1
     from F1
    )
select
       D.Fact2  as  Fact2,
       D3.Fact1  as  Fact1,
       D.A  as  A
from
       D,
       D3

Native SQL
select "D"."Fact2" AS "Fact2"
, "D3"."Fact1" AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
, "Dim2"."A" AS "A"
from "F2"
, "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
group by "Dim2"."A"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
from "F1"
having count(*) > 0
) "D3"


Results:

Fact2	Fact1	A
-1402	511181	A1
-7436	511181	A2
0	511181	A3
0	511181	A4

[MFGF]

No, it returns correct results while there is an item from the conformed dimension.

Code Select Expand

Cognos SQL
with
D as
    (select
           XSUM(F2.Fact2  for Dim2.A )  as  Fact2,
           Dim2.A  as  A
     from
           F2,
           Dim2
     where
           (F2.Key_id2 = Dim2.Key_id2)
     group by
           Dim2.A
    ),
D3 as
    (select distinct
           XSUM(F1.Fact1 )  as  Fact1
     from F1
    )
select
       D.Fact2  as  Fact2,
       D3.Fact1  as  Fact1,
       D.A  as  A
from
       D,
       D3

Native SQL
select "D"."Fact2" AS "Fact2"
, "D3"."Fact1" AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
, "Dim2"."A" AS "A"
from "F2"
, "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
group by "Dim2"."A"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
from "F1"
having count(*) > 0
) "D3"


Results:

Fact2	Fact1	A
-1402	511181	A1
-7436	511181	A2
0	511181	A3
0	511181	A4

Hi,

I'm not seeing anything wrong with this. Isn't the result exactly what you would expect? There is no connection between the non-conformed dimension and F1, so you get an overall total of the entire table. What else would you expect it to do? There's no way to break down F1 numbers for each value in Dim2, since Dim2 doesn't connect or relate to F1...

MF.

[bladeless]

I'm not seeing anything wrong with this. Isn't the result exactly what you would expect?

Yes, as cognos developer, I expect the result and I can deal with this situation during report developing.
But, Query Studio user doesn't know that  it is supposed to add an item from the conformed dimension.

What else would you expect it to do?

Code Select Expand

select sum("D"."Fact2") AS "Fact2"
, sum("D3"."Fact1") AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
, "Dim2"."A" AS "A"
,"F2"."Key_id1"
from "F2"
, "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
group by "Dim2"."A"
, "F2"."Key_id1"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
, "F1"."Key_id1"
from "F1"
group by "F1"."Key_id1"
having count(*) > 0
) "D3"
where
d."Key_id1" = d3."Key_id1"
group by "D"."A" AS "A"


OR

Code Select Expand

...
inner join
...
on d."Key_id1" = d3."Key_id1"
...

Result:

Fact2	Fact1	A
0	8030	A9
-1402	117586	A1
-7436	561076	A2
-3987	276297	A10
-2075	1381929	A11

There's no way to break down F1 numbers for each value in Dim2, since Dim2 doesn't connect or relate to F1...

I would like to know an answer on this question - is there any way to break down F1 numbers for each value in Dim2?

[MFGF]

Yes, as cognos developer, I expect the result and I can deal with this situation during report developing.
But, Query Studio user doesn't know that  it is supposed to add an item from the conformed dimension.

Code Select Expand

select sum("D"."Fact2") AS "Fact2"
, sum("D3"."Fact1") AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
, "Dim2"."A" AS "A"
,"F2"."Key_id1"
from "F2"
, "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
group by "Dim2"."A"
, "F2"."Key_id1"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
, "F1"."Key_id1"
from "F1"
group by "F1"."Key_id1"
having count(*) > 0
) "D3"
where
d."Key_id1" = d3."Key_id1"
group by "D"."A" AS "A"


OR

Code Select Expand

...
inner join
...
on d."Key_id1" = d3."Key_id1"
...

Result:

Fact2	Fact1	A
0	8030	A9
-1402	117586	A1
-7436	561076	A2
-3987	276297	A10
-2075	1381929	A11

I would like to know an answer on this question - is there any way to break down F1 numbers for each value in Dim2?

Hi,

Can you explain what logic should dictate this breakdown? Here's an analogy of what you are asking...

I have a dimension table of Vehicle Details for my fleet of trucks - all descriptive

I have a fact table of expenses for maintaining the vending machines in our head office.

There is no join or correlation between them.

How can I get a report that shows vending machine expenses broken down by vehicle type?

As you can see, it's a nonsensical request. If there is no relationship defined between them, you can't do it. This isn't a product defect, it's a fundamental design issue with your model. You're giving your users the ability to see unrelated values in a single report. Can you explain how you'd like to see your F1 numbers split by Dim2 values when they don't relate to each other? What is the logical breakdown? If there is one, then add the relevant relationship in your model...

MF.

[bladeless]

Can you explain what logic should dictate this breakdown? ... There is no join or correlation between them.

I thought if the model has the following join logic:
Dim1  1.n F1
Dim1  1.n F2
Dim 2 1.n F2
and when we add into QS/RS columns from F1 and Dim2 (without adding any columns from F2) Cognos knows that to get accurate result it has to use information about all "on the way" joins from Dim1 to F2.
In other words Dim2 is joined to F1 since Dim2 is joined with F2, F2 is joined with Dim1, and as end point of our way Dim1 is joined to F1.

[Lynn]

I thought if the model has the following join logic:
Dim1  1.1 F1
Dim1  1.n F2
Dim 2 1.n F2
and when we add into QS/RS columns from F1 and Dim2 (without adding any columns from F2) Cognos knows that to get accurate result it has to use information about all "on the way" joins from Dim1 to F2.
In other words Dim2 is joined to F1 since Dim2 is joined with F2, F2 is joined with Dim1, and as end point of our way Dim1 is joined to F1.

F1 is not considered as a fact for the relationships as you've defined here. The muppet explained this in his earlier post.

If a query subject is at the <n> end of *ALL* relationships used in the query, it is treated as a Fact
If a query subject is at the <1> end of *ANY* relationship used in the query, it is treated as a Dimension

Is F1 a fact or is it a dimension? It might help if you can provide a more realistic representation of your data (not just F1, Dim1, etc.). An example of how a value from F1 should relate to Dim2 might help.

This article provides a basic overview about multi-fact queries with conformed and non-conformed dimensions:
https://www.ibm.com/support/knowledgecenter/SSEP7J_11.0.0/com.ibm.swg.ba.cognos.ug_cr_rptstd.doc/c_cr_rptstd_multiple_fact_queries.html

Assuming they really are both facts and the conformity is correct as you've described, make the F1 join as 1..n on the fact side just as the muppet explained previously.

If you don't want your users to ask nonsensical questions of your package, you can publish two packages for your authors: one that includes F1 and Dim1 and another package that includes F2, Dim1, and Dim2.

[bladeless]

Oh, sorry. I copied the logic from 2nd post with mistake.
Here is correct relationships:

Dim1  1.n F1
Dim1  1.n F2
Dim 2 1.n F2

[bladeless]

An example of how a value from F1 should relate to Dim2 might help.

Code Select Expand

select sum("D"."Fact2") AS "Fact2"
, sum("D3"."Fact1") AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
, "Dim2"."A" AS "A"
,"F2"."Key_id1"
from "F2"
, "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
group by "Dim2"."A"
, "F2"."Key_id1"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
, "F1"."Key_id1"
from "F1"
group by "F1"."Key_id1"
having count(*) > 0
) "D3"
where
d."Key_id1" = d3."Key_id1"
group by "D"."A" AS "A"

[Lynn]

What I meant to suggest was a practical example of your data, not the SQL you are expecting to have generated.

The SQL you've shown is not a multi-fact query and violates basic dimensional modeling concepts in that we never join facts to facts. We join facts to dimensions. The conformed dimensions that exist between multiple facts allow for proper stitch queries. If you've not read any of the Kimball books (e.g., data warehouse toolkit) I'd strongly recommend those as worthwhile. The FM user guide also has some good information on modeling scenarios.

Looking at the SQL you provided I reverse engineered a data example that makes the point about non-conformed dimensions. Imagine you've got stores that sell things. You've got a dimension to identify the stores. You've got another dimension to identify the products. One fact gives you the overhead costs per store. Another fact gives you material costs by store and by product.

In the sample data below, how would we properly attribute the Paramus store overhead to the product cost for buttons vs. bows? We can get total overhead for the store alongside total material cost for the store, but our overhead costs have no relationship to the products sold within the store so I cannot say how much of my overhead should be attributed to one product vs another.

If you substitute vending machines and fleet of vehicles as the muppet's example described previously you can see the dilemma. Based on what you've described it doesn't seem sensible to do what you are describing. I suspect that I don't fully understand your situation.

Code Select Expand


-- Dim1
-- Key_id1   Dim1_Store
-- 1       Paramus

-- Dim2
-- Key_id2  Dim2_Product
-- 2          Bows
-- 5          Buttons

-- F1
-- Key_id1  Fact1_StoreOverhead
-- 1        250

-- F2
-- Key_id1  Key_id2  Fact2_Material_Cost
-- 1        2        100
-- 1        5        50


[bladeless]

Thank you, Lynn!

The model that you provided is the same as I described.
Why if I chose Dim2_Product, Fact1_StoreOverhead and Fact2_Material_Cost in FM Cognos generates the following SQL query:

Code Select Expand

select "D"."Fact2" AS "Fact2"
, "D3"."Fact1" AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
, "Dim2"."A" AS "A"
from "F2"
, "Dim2"
where "F2"."Key_id2" = "Dim2"."Key_id2"
group by "Dim2"."A"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
from "F1"
having count(*) > 0
) "D3"

Why Cognos SQL generator doesn't use information that we have 2 joins between Fact1 and Fact2 tables: F1-Dim1 and Dim1-F2?

In this case I would expect(using basic dimensional modeling concepts) something like that:

Code Select Expand

select sum("D"."Fact2") AS "Fact2"
, sum("D3"."Fact1") AS "Fact1"
, "D"."A" AS "A"
from (
select sum("F2"."Fact2") AS "Fact2"
,"Dim2"."A" AS "A"
,"Dim1"."Key_id1"
from "F2"
, "Dim2"
, "Dim1"
where "F2"."Key_id2" = "Dim2"."Key_id2"
and "F2"."Key_id1" = "Dim1"."Key_id1"
group by "Dim2"."A"
,"Dim1"."Key_id1"
) "D"
, (
select sum("F1"."Fact1") AS "Fact1"
,"Dim1"."Key_id1"
from "F1"
            , "Dim1"
where "F1"."Key_id1" = "Dim1"."Key_id1"
group by "Dim1"."Key_id1"
having count(*) > 0
) "D3"
where
d."Key_id1" = d3."Key_id1"
group by "D"."A" AS "A"

[tjohnson3050]

The way Cognos builds a stitch query follows the Kimball Drill-Across concept of querying two fact tables with a conformed dimension. 

This concept is for a separate sub query for each fact table and dimension(s) combination, and joining these two sub queries together with an outer join on the conformed dimension(s).  Here is a link to the Kimball concept, and an IBM proven practice article on Cognos Stitch queries.

http://www.kimballgroup.com/2003/04/the-soul-of-the-data-warehouse-part-two-drilling-across/

https://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html