Columns replicating data 49k times in report.

[VonBlitzk]

IBM Cognos Analytics 12.0.3

I have inherited a bunch of reports from a previous colleague. I am also very new to the BI/COGNOS world.

An issue has come to light where with some prompt inputs, the table is displaying with many thousands of replicated lines.

The report queries are structed as such:

A
B
C
D

Query2 Join of C and D (The joins are PO_ID - PO_ID)
Query3 Join of Query2 and B (The joins are PO_ID - PO_ID and POITEM_NUM - POITEM_NUM)
Final data Join of Query3 and A (The joins are PO_ID - PO_ID and INV_ID - INV_ID)

So all 3 joined queries share the PO_ID join.

The offending columns being managed by query A and Query3.

Having spoken to the Dev team, they are convinced the issue is "the way Cognos is grouping data". But in my limited experience, I can't find an issue with how this report is set up and believe the core queries are the issue.

I apologies for the lack of information, I am trying to share as much as I can without compromising anything sensitive.

Does any known issue or common mistake jump out at you that could cause such excessive replication of results?

[dougp]

Not enough detail there to work with.  I'll assume all of your source queries are connecting to SQL databases.

Write the correct query in SQL, then export the SQL from the Cognos report and compare.

[bus_pass_man]

What leaps out to me is that I'd say let's try to rule out double counting.  Double counting usually happens when a query is projected below the fact grain of a dimension.

Why is this sort of modelling via queries happening in a report?  Why isn't modelled?  What is it that this munging supposed to model? i.e. what is the business entity that is being modelled?

POITEM_NUM  = Purchase order item number?
PO_ID = purchase order?
What's INV_ID?

The munging together of queries B, C, and D is presumably attempting to create a dimension.  This sort of thing is what modelling is for.  Also, the composition of queries A, B, C, and D might be of significance.

What's the cardinality of the joins?  In Query2, the join of C and D, which is on the many end?   Is that a true reflection of reality?  In Query3, the join of Query2 and B, which is on the many end? In the join of Query3 and A which is on the many end?  I'm guessing that query2 is higher level than query3. 

Query A is where the facts are?  What is the grain of dimensional detail of the facts? Is the level of the facts above the lowest level of the dimension?. Are the facts at the same level? 

Can you explain the choice of keys. If entities are distinguished by a composite key of po_id and poitem_num why is the join to the (presumably) fact table using different keys?

Could you explain to another person what each query is supposed to represent in the real world?


If you are new why are they throwing this problem at you? 

Apropos of nothing, have you encountered the saying 'no bad soldiers, only bad officers'?

Another saying which is apropos of nothing is 'it's a poor workman who blames his tools'.