Faking relationships for correct results

[prikala]

This is an simplified example of a real life problem.

Database and FM model structure:
-----------------------------
   FactA ("main fact")
      CommonKey   (pk)
      ValueA
   SubFactA1
      CommonKey   (pk)
      A1Key      (pk)
      ValueA1
   SubFactA2
      CommonKey   (pk)
      A2Key      (pk)
      ValueA2
   "(pk)"==primary key column


   Relations:
      FactA 1:n SubFactA1
      FactA 1:n SubFactA2

   Model has a querysubject/table and each querysubject
   has determinant for the primary key.
   In addition subFact-tables have a determinant for CommonKey column.


   Data:
   factA
      COMMONKEY  VALUEA
      ---------- ----------
      1          1
      2          1
      3          1
      4          1
      5          1

   subFactA1

      COMMONKEY  A1KEY         VALUEA1
      ---------- ---------- ----------
      1          A1A                 1
      1          A1B                 1
      1          A1C                 1
      2          A1C                 1
      3          A1A                 1
      4          A1B                 1


   subFactA2

      COMMONKEY  A2KEY         VALUEA2
      ---------- ---------- ----------
      1          A2A                 1
      2          A2A                 2
      3          A2A                 3
      4          A2A                 4
      5          A2A                 5

-----------------------------


The report I am trying to produce is equal to this query:
   -- what is the sum of SubFactA2.valueA2 for those rows
   -- having CommonKey that exists in SubFactA1 with the
   -- given values of A1Key
   select A1.A1Key, sum(A2.ValueA2)
   from FactA A, SubFactA1 A1, SubFactA2 A2
   where
      A.CommonKey = A1.CommonKey
      and   A.CommonKey = A2.CommonKey
      and A1.A1Key in ('A1A','A1C')
      and A2.A2Key = 'A2A'
   group by A1.A1Key;

   Expected result:
   A1KEY      SUM(A2.VALUEA2)
   ---------- ---------------
   A1A                      4
   A1C                      3
   


With the 1-n relationships, same report in querystudio gives me:
   A1KEY   A2KEY   VALUEA2
   A1A   A2A   15
   A1C   

This is of course not the result that I want.


I quess this is an example of a query that splits (as in document "Guidelines
for Modeling Metadata"). This problem might also be related to this thread:
http://www.cognoise.com/community/index.php?topic=1074.0


If i fake the relationsips and convert them to 1:1, querystudio report
gives me the same results as sql query.
Now I am a bit suspicious: I had thought that metadata should always reflect the reality.

My questions are:
- What pitfalls are there if I fake the relationships?
- is there any other way to solve this problem

Edit 10 Oct 2007: Attached ddl and dml for creating testset (oracle).

[bpeterson]

Try creating the same query in Report Studio and look at the SQL generated by cognos. I have a feeling that the automatic aggregation rules that occur when importing metadata into the model are causing the issue. In addition, when using a 1:n relationship many times things are double counted or even total incorrectly. I have found when a 1:n relationships that determinants are need to avoid double counting or incorrect data being returned. I highly suggest testing this in FM before publishing, this can be done by creating a new model query subject based on the query subjects you are joining together.