How to handle bridge table in framework manager (for unbalanced hierarchy and multi-valued dimensions) (re# Cognos release 10.2.2 with Bridge Query Subjects)

Check list of all posts

Introduction

There is an article to handle bridge table from IBM best practice: Bridge Tables and IBM Cognos 8. However, this solution can not cover some generic user cases, including unbalanced hierarchy and multi-value dimensions. (We don’t discuss about unbalanced hierarchy and multi -valued dimensions here). This article is intended to demonstrate how to bring these data model into framework manager. The bottom line is that we can NOT simply bring these tables into framework manager with existing ERD, as Cognos may produce unexpected result.

Sample

Database

Please check ERD below, there is 1 fact table with 3 dimensions. D1 and D2 dimensions have relationship with fact via two bridge tables G1_Bridge and G2_Bridge, respectively. From pure database point of view, there is clear many to many relationship for bridge table, as it describes the mapping between Group and dimension.

Framework manager

We can create a framework manager based on database table structure above.

Bring all database tables as below without creating any relationships

The dimension view is a clear normalized STAR scheme as below.

The key concept is that we should build a relationship for bridge table at business layer. There are ways to handle it.

Change cardinality for bridge table

We cannot use the same relationship as described ERD in database. If use database ERD, Cognos will consider two bridge tables as Fact table. Then full outer join will be used, and then report will be wrong. The reasonable requirement is to use inner join all bridge tables.

The solution is trick Cognos and makes relationship as below, please note the circled relationship is exactly opposite to database ERD.

The generated SQL is as anticipated below.

Directly connect fact table with bridge table

The other way is to get rid of group table, directly to connect with fact table. The key here is to use one to many relationship to grantee use inner join

The generated SQL is as anticipated below.

 

Further update based on new Cognos release 10.2.2 with Bridge Query Subjects

Previously when a bridge table was used in a Cognos Framework Model, it could only be modeled using a many to many relationship. Since many to many relationships are not supported in DQM, bridge table use was limited to CQM. Also, many to many relationships used in this context were often found to produce incorrect or unexpected results. The new Bridge Query Subject in Framework Manager allows the modeler to now simply set the usage property of a query subject to ‘Bridge’ while maintaining a one to many relationship and Cognos will identify this as a bridge table rather than a fact table.