Modelling Discrepencies? Inner joins outputted as full outer joins.

[nitro001]

I am running into an error that I am stumped on.  I have a model for an Advancement group, their base table is Constituent that has a number of fields.  Off of this are a number of tables, 3 tables have me stumped.  The base table Constituent is joined to a memo table by a 1..1 -> 1..n relationship, constituent is also joined to a gifts table also with a 1..1 -> 1..n relationship.  Ok simple so far.  There are 2 more tables, one is Pledges it is joined to Constituent with 1..1 -> 1..n.  It is also joined to Gifts 0..n -> 1..1.  Lastly, Direct Mailing, joined to Constituent by 1..1 -> 1..n and to Gifts by 1..1 and 0..n.

Just looking at the first three tables, If I choose a couple query items from constituent and memo and perform a test and look at the query response, I get what I would expect to see as the SQL being executed:

with
Gifts_Memo as
    (select
           KSUODS_GIFTS_MEMO.MEMO_UID  as  MEMO_UID,
           KSUODS_GIFTS_MEMO.GIFT_NUMBER  as  Memo_Gift_Number
     from
           ODS..KSUODS.KSUODS_ETL_GIFTS_MEMO KSUODS_GIFTS_MEMO
    ),
Constituent as
    (select
           KSUODS_CONSTITUENT.PERSON_UID  as  PERSON_UID,
           KSUODS_CONSTITUENT.CONTACT_NAME  as  Contact_Name
     from
           ODS..KSUODS.KSUODS_CONSTITUENT KSUODS_CONSTITUENT
    )
select
       Gifts_Memo.MEMO_UID  as  MEMO_UID,
       Gifts_Memo.Memo_Gift_Number  as  Memo_Gift_Number,
       Constituent.PERSON_UID  as  PERSON_UID,
       Constituent.Contact_Name  as  Contact_Name
from
       Gifts_Memo
        join
       Constituent
        on (Constituent.PERSON_UID = Gifts_Memo.MEMO_UID)

Ok so far,

Now doing the same thing with Constituent and Gifts, I see what I would expect (without the Direct Mailing or Pledge tables interfering):

with
Constituent as
    (select
           KSUODS_CONSTITUENT.PERSON_UID  as  PERSON_UID,
           KSUODS_CONSTITUENT.CONTACT_NAME  as  Contact_Name
     from
           ODS..KSUODS.KSUODS_CONSTITUENT KSUODS_CONSTITUENT
    ),
Gifts as
    (select
           KSUODS_GIFTS.ENTITY_UID  as  ENTITY_UID,
           KSUODS_GIFTS.CAMPAIGN  as  Gift_Campaign
     from
           ODS..KSUODS.KSUODS_ETL_GIFTS KSUODS_GIFTS
    )
select
       Constituent.PERSON_UID  as  PERSON_UID,
       Constituent.Contact_Name  as  Contact_Name,
       Gifts.ENTITY_UID  as  ENTITY_UID,
       Gifts.Gift_Campaign  as  Gift_Campaign
from
       Constituent
        join
       Gifts
        on (Constituent.PERSON_UID = Gifts.ENTITY_UID)


Now why is it, when I try to select fields from all three tables, Constituent, Gifts, and Memo's I get the following?:

<message severity="information"title="QE-DEF-0469 QE Message"type="joinResolution">RQP-DEF-0433 Shared Dimensions:
    [KSU Advancement].[Constituent]

</message>
<message severity="information"title="QE-DEF-0469 QE Message"type="cognosSQL">with
Constituent as
    (select
           KSUODS_CONSTITUENT.PERSON_UID  as  PERSON_UID,
           KSUODS_CONSTITUENT.CONTACT_NAME  as  Contact_Name
     from
           ODS..KSUODS.KSUODS_CONSTITUENT KSUODS_CONSTITUENT
    ),
Gifts_Memo as
    (select
           KSUODS_GIFTS_MEMO.MEMO_UID  as  MEMO_UID,
           KSUODS_GIFTS_MEMO.GIFT_NUMBER  as  Memo_Gift_Number
     from
           ODS..KSUODS.KSUODS_ETL_GIFTS_MEMO KSUODS_GIFTS_MEMO
    ),
D9 as
    (select
           Constituent.PERSON_UID  as  PERSON_UID,
           Gifts_Memo.MEMO_UID  as  MEMO_UID,
           Gifts_Memo.Memo_Gift_Number  as  Memo_Gift_Number,
           Constituent.Contact_Name  as  Contact_Name,
           RSUM(1  for Constituent.PERSON_UID  order by Constituent.PERSON_UID asc  local)  as  sc
     from
           Constituent
            join
           Gifts_Memo
            on (Constituent.PERSON_UID = Gifts_Memo.MEMO_UID)
     order by
           PERSON_UID asc
    ),
Constituent13 as
    (select
           KSUODS_CONSTITUENT.PERSON_UID  as  PERSON_UID,
           KSUODS_CONSTITUENT.CONTACT_NAME  as  Contact_Name
     from
           ODS..KSUODS.KSUODS_CONSTITUENT KSUODS_CONSTITUENT
    ),
Gifts as
    (select
           KSUODS_GIFTS.ENTITY_UID  as  ENTITY_UID,
           KSUODS_GIFTS.PLEDGE_NUMBER  as  Pledge_Number
     from
           ODS..KSUODS.KSUODS_ETL_GIFTS KSUODS_GIFTS
    ),
D10 as
    (select
           Constituent13.PERSON_UID  as  PERSON_UID,
           Gifts.ENTITY_UID  as  ENTITY_UID,
           Gifts.Pledge_Number  as  Pledge_Number,
           Constituent13.Contact_Name  as  Contact_Name,
           RSUM(1  for Constituent13.PERSON_UID  order by Constituent13.PERSON_UID asc  local)  as  sc
     from
           Constituent13
            join
           Gifts
            on (Constituent13.PERSON_UID = Gifts.ENTITY_UID)
     order by
           PERSON_UID asc
    )
select
       D9.MEMO_UID  as  MEMO_UID,
       D9.Memo_Gift_Number  as  Memo_Gift_Number,
       D10.ENTITY_UID  as  ENTITY_UID,
       D10.Pledge_Number  as  Pledge_Number,
       coalesce(D9.PERSON_UID,D10.PERSON_UID)  as  PERSON_UID,
       coalesce(D9.Contact_Name,D10.Contact_Name)  as  Contact_Name
from
       D9
        full outer join
       D10
        on ((D9.PERSON_UID = D10.PERSON_UID) and (D9.sc = D10.sc))

</message>


Does anyone have any ideas why it would do a full outer join with these three tables instead of doing inner joins with all three tables since they are modeled as inner joins?

Thank you,
Nathan

[JGirl]

Its to do with the way that framework manager handles 'multifact queries'.  Any table that has a 1..n cardinality is assumed to be a fact table, and is handled by framework manager as such.

In a classic example, you would have 3 tables:
* SALES_CENTRE (A dimension containing a companie's offices structure)
* FACT_SALES_ACTUALS (A fact table containing actual sales)
* FACT_SALES_BUDGET (A fact table containing budgeted sales)

These would be joined as:
* SALES_CENTRE (1..1) <--> FACT_SALES_ACTUALS (1..n)
* SALES_CENTRE (1..1) <--> FACT_SALES_BUDGET (1..n)

But if you were to put all 3 tables together using inner joins, you wouldnt get records returned for sales centres that have an actual but no budget, nor for sales centres that have a budget with no actual, which isnt ideal.

In this example, cognos would execute 2 inner join statements (one between SALES_CENTRE and FACT_SALES_ACTUALS, and one between SALES_CENTRE and FACT_SALES_BUDGET), and put these together using a full outer join on the reportnet server.

This is known as a 'Stitch Query' in Cognos terms. 

Without knowing your data or reporting requirements, its difficult to suggest a 'quick fix' solution, but there's a document in the proven practices section of Cognos' support site called something like 'framework manager: modeling for predictable results' which might be worth taking a look at, as it explains the impacts of setting certain cardinalities and how to get around it.

J