Cognos Query Join Relationship Cardinality Guidance Needed

Question

I have two queries 'Stock' and 'Dues' that I combined using a query join to create Stock_&_Dues as I want the data from both queries to populate in the same report page.

For some reason I'm getting all the data from 'Stock' and 'Dues' when both queries have data but if 'Stock' has no data and 'Dues' does have data then the 'Dues' data isn't being returned in the Stock_&_Dues report.

I'm using a 0..1 cardinality as I believe this is creating a left outer join but it's not working. I've tried pretty much all cardinality options now and the problem doesn't seem to sort itself out.

Any help would be greatly appreciated.

Answer 1

There are 2 parts that are used for Cognos expressing a join

The first part is either 0 or 1. 0 is outer, 1 for inner join

The second part is the type of relationship either 1 or n for many

You have 0.1 to 0.1 However the two tables I believe have data you want to treat as facts?

Facts are usually something you want to aggregate, typically total and because of this you want them on the many side of the relationship

This leads to the next issue, a many to many relationship also known as the cartesian product, something you do not want (no relationship between the two tables)

There are many ways to solve this type of join

1. This might be a good fit for a union. For the report, is all of the data the same? i.e. Unit ID, Unit Name, DMC. If so, you can combine them with a union. If you want to add some clarity, you could even create a make believe Data Item of type and hard code 'Stock' for the stock query and 'Dues' from the other query

Or

2. You can stitch two (or more) facts together by joining them to something in common. For example, there is probably a master file for the Unit and DMC. You would either use Data Modules or Framework Manager to design this relationship (you could do this at the report level however you would have to do this for each report which is not best practices).

For example,

Instead of Stocks 1.n joining to 1.n Dues (a many to many relationship)

We would want a stitch, like this

Fact 1 Stocks 1.n join to master table 1.1 DMC

Fact 2 Dues 1.n join to master table 1.1 DMC

and repeat again for other common master tables (maybe customer, or item, etc.)

This was you have full outer join with a coalesce that allows you to report over multi-fact content

The facts are on the many side of the relationship and the common master tables are on the 1 side of relationship

You will want to do this for the things that are in common: Maybe some context of time like the Year and Month

• The DMC master file
• The Unit master file

This allows you to bridge the relationship. Without those common tables to bridge two different facts, you run into a data trap known as a data chasm where you have a many to many relationship (you want to avoid that).