Tanubhrt.This.Side

When using Cognos for making reports, more often we require joining of two or more query subjects. But the place where most students get confused is when to use inner join and when to go for outer join.

Well, for starters, you need to realize that if you are using multi dimensional model for creating reports, their is an automatic inner join between two dimensions using the fact attributes. So, this means that if there is a query A and a query B, if you pick few columns from query A and few from query B, the resultant output will be the inner join between A and B using the common attribute present in the fact table.

Now, let us understand what an inner join is, i.e. the basic inner join using the SET concept of mathematics.

INNER JOIN

When an inner join is done between the two queries the resultant query has the attributes that are common in both of them.

So, here in the above two, the resultant query will have 1,4 and 6.

OUTER JOIN

Outer join differentiates from inner join in such a way, that you have the choice to include all the columns from a query irrespective of the fact that the columns are matching or not. So, for example if I do an outer join between query A and query B on query B, the resultant query will have 1,3,4,6,8,9 but if the outer join is done on query A, the resultant query will have, 1,3,4,6 only.

Let us now see what COGNOS by default does.

By default COGNOS does INNER JOIN.

Given below are two queries(tables). When you do an inner join between the two queries i.e. when you simply drag and drop columns from the two queries, what happens is that COGNOS finds out columns from both the tables present in the fact table and does an inner join. With inner join, we mean that only the columns that are common to both will come in the final table.

When simply dragging and dropping in Cognos make sure that the columns you are searching for in the dimension table are also present in the Fact table, otherwise you may get some stupid results and you won’t have anyone to blame.

Happy Learning 🙂