Joins are created in the Data Model. This reduces the maintenance overheads for the model.
The cardinality for the joins are specified in accordance with the database design logic for the various entities, for example, An Order Header may not always have a Carrier Service Code. Therefore, the cardinality for the Order Header join with Organization Carrier is 0:1 on the Carrier side.
Additionally, to allow flexibility in reporting, some of the joins have optional cardinality (0:1 or 0:n). This is done so that, in a query, the main entity's data set is not restricted by the lack of data in the views it is joined with. Typically, the transactional views do not have optional joins, however, the master views have them.
The Data Model allows changing of the optional cardinality to a direct join at an individual report level.
The Item and Item Alias entities have a 1:1-0:n join in the business layer. When the Cognos® 10.1.1 report containing these two entities is created, it is as follows:
select T1."MANUFACTURER_NAME" as c1,
T2."ALIAS_NAME" as c2
from ("YNA_ITEM_VW" T1 left outer join "YNA_ITEM_ALIAS_VW" T2 on T2."ITEM_KEY" =
T1."ITEM_KEY")
--If we now uncheck the Report->Query->Access->”Include the missing table join
--values (outer join)” check box, the query changes to
select T1."MANUFACTURER_NAME" as c1,
T2."ALIAS_NAME" as c2
from "YNA_ITEM_ALIAS_VW" T2,
YNA_ITEM_VW" T1
where (T2."ITEM_KEY" = T1."ITEM_KEY")
However, if the join in the business layer was 1:1-1:n instead, the flexibility described here would not have been possible to achieve.