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
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