Strange Fact Rollup Issues and Generated SQL

[grifter102]

So I have another bizarre issue that I can't seem to figure out.  I have a query using 3 tables.  The first table is a list of PO items with amounts and quantities, the second table is a reference table linking POs to the source request details and the third table contains sourcing details such as the purchased job details and target rate.  I have the relationships set up like this:

PO Item [PO] (1..1) ---- (0..1) Source Ref [SR] (1..n) ---- (0..1) Sourcing Details [SD]

Not all POs were created from the source system I'm accessing and the sourcing details contains one line for the job purchased while the PO will have lines for straight-time, overtime, etc.

Now, if I create a query subject like this:

[PO].[PO Number],
[PO].[PO Item],
[PO].[PO Amount] **measure agg=sum,
[SR].[Req ID],
[SD].[Job Desc],
[SD].[Market Rate] **measure agg=avg

and I pull all the non-measures into RS or QS, then it will generate this SQL:

Code Select Expand

select "PO"."PO_Number" "PO Number", "PO"."PO_Item" "PO Item", "SR"."Req_ID" "Req ID", "SD"."Job_Desc" "Job Desc"
from DB.PO_Item "PO"
left outer join DB.Source_Ref "SR" on PO.<join fields> = SR.<join fields>
left outer join DB.Sourcing_Details "SD" on SR.<join fields>=SD.<join fields>

Very simple and clean and it works fine.  If I pull in one of the measures (say, PO Amount), I get this:

Code Select Expand

select "PO"."PO_Number" "PO Number", "PO"."PO_Item" "PO Item", "SR"."Req_ID" "Req ID", "SD"."Job_Desc" "Job Desc", sum("PO"."PO Amount") "PO Amount"
from DB.PO_Item "PO"
left outer join DB.Source_Ref "SR" on PO.<join fields> = SR.<join fields>
left outer join DB.Sourcing_Details "SD" on SR.<join fields>=SD.<join fields>
group by "PO"."PO_Number", "PO"."PO_Item", "SR"."Req_ID", "SD"."Job_Desc"

Same thing happens if I pull in just the one other measure.  The problem happens when I pull in measures from both PO and SD.  Then it does something like this:

Code Select Expand

select T1.C0, T1.C1, T1.C2, T1.C3, T1.C4, T0.C4 "C5"
from
(select "PO"."PO_Number" "C0", "PO"."PO_Item" "C1", "SR"."Req_ID" "C2", "SD"."Job_Desc" "C3", sum("PO"."PO Amount") "C4"
from DB.PO_Item "PO"
left outer join DB.Source_Ref "SR" on PO.<join fields> = SR.<join fields>
left outer join DB.Sourcing_Details "SD" on SR.<join fields>=SD.<join fields>
group by "PO"."PO_Number", "PO"."PO_Item", "SR"."Req_ID", "SD"."Job_Desc") T0,
(select "PO"."PO_Number" "C0", "PO"."PO_Item" "C1", "SR"."Req_ID" "C2", "SD"."Job_Desc" "C3", sum("SD"."Market Rate") "C4", "SD"."Key Field 1" "C5", "SD"."Key Field 2" "C6"
from DB.PO_Item "PO"
left outer join DB.Source_Ref "SR" on PO.<join fields> = SR.<join fields>
left outer join DB.Sourcing_Details "SD" on SR.<join fields>=SD.<join fields>
group by "PO"."PO_Number", "PO"."PO_Item", "SR"."Req_ID", "SD"."Job_Desc", "SD"."Key Field 1""SD"."Key Field 2") T1,
where T1.C0 = T2.C0 AND T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T1.C3 = T2.C3

Now... that query seems overly complicated and it seems to be adding in extra fields to the second portion of the query corresponding to SD's key fields.  I would also think that, however complicated, this query would return the right answer... but it doesn't.  Every single value of SD.Market_Rate is the exact same for every single line in the results.  I'm not even sure what that number corresponds to... because it doesn't seem to be an overall average.  So even though the generated SQL looks like it should work (however awkwardly), it's not what Cognos is showing me as results.

I don't have any determinants set up on any of the tables and I've triple-checked all of the relationships.  I've stripped out all of the other fields from my query subject and all that I can determine is that when I bring in a measure from both the PO and SD tables the SD result will always be the same for every single row while the PO results are fine.  Does anyone have any ideas what is going on here?

[blom0344]

With measures coming from 2 different query subjects you will need determinants. Without them Cognos will attempt to recombine sets over the all the non aggregates (in some fashion).

From your example it looks like fact and non-fact query subjects are switched.  I would try to recombine PO and SR into one new query subjects and consider this as fact against SD. you then would need a unique determinent on SD to prevent overcounting that side

There is also a slight chance that the actual SQL executed is different from the one Cognos pictures, so I would suggest running a trace file to capture SQL executed

[grifter102]

I changed the relationship between the SR and SD tables to be 1..1 and 0..1.  Even though it's not exactly accurate, it's giving me the results that I need.  I did try a determinant and it still gave me the incorrect data repeated across every single row.  I'm not sure exactly what is happening there... but at least I have a workaround.

I also can't run any traces or anything to find out exactly what Cognos is running.  DBAs refuse to support those requests.

[norkos]

It seems to me a usual cardinality issue, because cardinalities are not properly set in your model.

In Cognos the fact and dimension table detection is determined by cardinalities of the relationships.
In your model the measure items exist in PO and SD tables, which will determined as dimensions by the Cognos query engine.
To get proper results from CQE, the measure items should be in fact tables instead in dimensions.

I recommend to check your relations once more with a special caution to cardinalities.

There are two detailed sections in this topic in FWM user guide called "Guidelines for Modeling Metadata
" and "The SQL Generated by IBM Cognos Software", which can may help you.