Friday, August 9, 2013

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)


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.



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.
New Bridge Query Subject Property in Framework Manager


  1. This comment has been removed by the author.

  2. I created a package based on the model you have above. I opened the package in Query Studio. When I grouped the data the values in my fact table were not aggregated correctly (double counting). I have the determinants set at the primary keys. Have you test this model in Query Studio? Did your fact values aggregate correctly?

    1. I've got exactly the same result with double counting in Query Studio. Any suggestions how to handle it?

    2. I was able to get the counting/aggregates to work correctly. If I recall, I did it by changing the Regular Aggregate property in FM (sorry I can't be more specific). After that the problem I ran into was that I got an error in QS when I tried to use dimension attributes that were link to a fact through two different bridge tables. In short I was only able to get QS to work correctly or a single many to many relationship. This was not a viable solution for me. If you have better luck please let me know.

    3. Thanks for comment and idea with aggregation options - I definitely will try it! I'm still investigating.
      At current moment I have 2 models one with bridge table directly connected to fact --> it causes double counting. And second with two fact tables (one connected to target dim and second NOT connected) plus a few other conformed dimensions --> in some queries this model return perfectly stitched result (!) that looks exactly like expected from bridge... but with fewer detalization result become meaningless. So I wonder is it possible to force cognos always go trough bridge and implement stitch logic from one side of query?..

  3. This comment has been removed by the author.