(DQM) Usage - Bridge table

[en1]

Hi! :D

"
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.
"
I cant understand. This property only for DQM?
What best practice for modeling bridge tables in CQM (DMR)

[bdbits]

I have always understood the definition of a bridge table as an intermediary table between two tables that have a many-to-many relationship. Thus:
Many-to-Many: Class <<-->> Student
With Bridge Table: Class <<-> {Student/Class bridge table} <->> Student

This is a straightforward thing to model in FM. The quote appears to be self-contradictory and does not make much sense to me.

Also where is this query subject usage property? I thought I knew FM moderately well but I cannot find it anywhere even if I create a new project and set up a bridge table scenario.

Maybe it is just Monday morning and I need a little more caffeine.  ;D

[cognostechie]

I have heard about bridge tables and have always wondered as to what is it that I am missing because I have always resolved this with a 1-to-many join? May I haven't come across a proper example yet? In this case I would create a Student Key and a Class key in the fact table and join the Fact to Student Dim and Class Dim . maybe I need another example where a many-to-many is required?

[bus_pass_man]

The bridge table property is a property of a query subject.  I can't find anything about it in the docs (didn't look very long though) but the name of the property certainly suggests that it is a dqm only feature.

The usual example of many-to-many is a bank account can have many owners.  Each owner can have many bank accounts.  The balance of any one account is the same for each owner, is the total value for the account (not divided among the owners) and is not aggregate-able.  It's far easier just to be able to say, this is a bridge table than to futz around the old way.

Here's a good overview explaining a lot of everyone's questions in this presentation
https://www-304.ibm.com/connections/blogs/cdd50800-96e4-45ec-b4bf-f99bb71ccb95/entry/bridge_query_subjects?lang=en_us

Here's a workshop about bridge tables.
Smarter Support Hands On Workshop: Cognos Framework Manager 10.2.2

https://www-304.ibm.com/connections/blogs/basupportlink/entry/smarter_support_hands_on_workshop_cognos_framework_manager_10_2_2?lang=en_us

You can do it in cqm.  There's a PDF about bridge tables in cqm on the IBM site.  I tried to include the link to the pdf for bridge tables and cqm but it wouldn't work very well.  Search for bridge tables and cognos 8 and download the PDF.

Now, I've muttered 'from my cold dead hands' about many things but never about compatible mode especially w.r.t. dqm.   I extend my condolences to you for needing to stick with cqm. 

[en1]

thanks for your replys.

bridge table used for resolve many-to-many between fact table and dimmension table (multivalued dimmension)

I have read document  about bridge tables and Cognos 8.
And if i understand this document about relational model.

"Exclusions and Exceptions
When working with a bridge table the dimensional modelling techniques using
conformed dimensions will not be applicable. Additional query paths resulting
from conformed dimensions may cause the bridge table to be left out of a
query when it is necessary to provide the appropriate data matching between
the two subject areas. The additional dimensions must be created as distinct
model query subjects so that a direct connection does not exist through these
dimensions and allow the bridge table to be removed from the query.

Further, the techniques outlined in this document apply only to relational
modelling and cannot be extended to dimensionally modelled relational
packages."


So, what about CQM and dimensional model?

[bdbits]

Bridge tables in a purely dimensional discussion are not relevant because they are neither a fact or a dimension. But they can certainly be in a model upon with a dimensional layer has been built (e.g. DMR). Even in a relational package, chances are you would not be exposing the bridge table itself to the user. They are there to enable Cognos to build correct SQL at runtime.

CQM and DQM are query engines. The use of bridge tables is not tied to either one.

I might take issue with whether bridge tables can be used with conformed dimensions. There are too many other factors - mostly subject matter dependent - to make a blanket statement like that. It does require more thought to go into model development, and you may indeed want/need to create query subjects as a layer above the bridge table participants. It just depends.

Is this just a theoretical discussion, or do you have an actual problem you are wanting to solve? If you just want to learn about this, I would recommend creating some sample tables and building some models and packages. Then test and look at the generated SQL for yourself.

[cognostechie]

Completely agree with bdbits. In the past, I have resolved the many-to-many with other methods instead of bridge tables while in some cases bridge table will be neccesary so it all depends on the specific scenario and requirement. 

[en1]

I try to find best practices to resolve bridge table. Everything i can find that in version 10.2.2 new property Usage for DQM. It ok, but my version is 10.2.1. Also in this document i have read in versions before 10.2.2 many-to-many relations not supported. Ок, if so i cant use DQM in my version. And what about CQM? I find document with best practices to resolve bridge tables for Cognos 8. But techniques outlined in this document apply only to relational modelling and cannot be extended to dimensionally modelled relational packages. I think bridge table not supported at all in my case. Of course i can resolve this with other methods, but it mean that bridge tables just not suported.

[cognostechie]

I found a document which was published from Cognos Insight 2014 and this is what it says:

---
Bridge Query Subjects – Now, obtaining correct results with bridge tables is simple.
This can be achieved by setting the (DQM) Usage property of the Query Subject that
represents the bridge to "Bridge". This will let the IBM Cognos server know that the
associated table is a bridge table, not a fact table like the many side of a join
normally implies, so that it can plan queries accordingly for correct results. This
setting ensures no double counting and no missing entries. When a user applies a
filter to a bridge in a multi-fact query, both fact tables will be filtered accordingly.
---

This implies that in the earlier versions, even if you had a bridge table in FM, the results were
probably not correct. This might mean that correct results will appear only from 10.2.2. 

For the confusion regarding the DQM property - Yes, that property will only be visible when you
create a Data Source using JDBC connection. Once it is set in the relational layer, it will also apply to DMR
because the DMR gets the data from the relational layer. The DMR is not a physical dimensional model but only a
virtual one. The data is retrieved from the DB using SQL which will be generated according to the DQM setting
and then it will be converted into an OLAP format for re-structuring the data according to the hierarchies .

[MFGF]

This implies that in the earlier versions, even if you had a bridge table in FM, the results were
probably not correct. This might mean that correct results will appear only from 10.2.2. 

Hi,

No - not really. It just says that using bridge tables in your model is now easier. The "gotcha" to watch out for prior to this was that bridge tables would be mistakenly recognised as fact tables if you defined the relationship cardinalities the way they should be (ie it was at the many end of all relationships to it). There were several techniques to work around this that would still deliver correct results - but you obviously had to do more work in the model to implement them. Now all you need to do is to mark it as a bridge table and it will no longer be recognised as a fact table.

Cheers!

MF.

[cognostechie]

Yup ! That's exactly what I meant that if the modeler would have modeled it like shown in the diagram then it would have treated the bridge as a fact giving improper results but of course, some people who know how to do things themselves instead of going by the book would figure out the right way to do it.

[amplizz]

Sorry to bump this thread, but I have a question about the new bridge functionality in FM 10.2.2: Is it now possible to use a bridge table in addition to conformed dimensions without getting unpredictable results? From IBM's paper about bridge tables in Cognos 8 I quote one of the exclusions: "The additional dimensions must be created as distinct model query subjects so that a direct connection does not exist through these dimensions and allow the bridge table to be removed from the query." I'm hoping there is a way around this.

The case is this: two fact with different granularity, and there is a many to many relationship between them. For example a query could include a measure from Fact A (GL) and Dim F (suppliers) from Fact B (AP), or it could be a factless query (e.g. Dim C and Dim F). We have many conformed dimensions and would like to avoid using aliases, but instead "force" the query to use the bridge table. Is this supported with the new bridge functionality (or is it supported already)?

                                               -----------------------------------(1..1)Conform Dim A(1..1) -----------------------------------
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                              |(1..N)                                                                                                                                 (1..N)|
Dim C(1..1)<---->(1..N)Fact A(1..N)<--->(1..1)Dim D(1..1)<--->(1..N)Bridge(1..N)<---->(1..1)Dim E<---->(1..N)Fact B(1..N)<--->(1..1)Dim F
                                              |(1..N)                                                                                                                                 (1..N)|
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                               -----------------------------------(1..1)Conform Dim B(1..1) -----------------------------------

We are now using FM 10.2, but are considering to upgrade.

[bdbits]

I am still on 10.2.1 but from the comments about 10.2.2 FM, it should be a bit simpler to use bridge tables without running into code generation problems which would arise due to Cognos misinterpreting the bridge table as a fact table. The easiest way to see for yourself is to create a model and run some tests; shouldn't take too terribly long.

[amplizz]

I am still on 10.2.1 but from the comments about 10.2.2 FM, it should be a bit simpler to use bridge tables without running into code generation problems which would arise due to Cognos misinterpreting the bridge table as a fact table. The easiest way to see for yourself is to create a model and run some tests; shouldn't take too terribly long.

Good point, Bdbits. But unfortunately I'm still on version 10.2, so I can't run these test with the new bridge functionality that is on versjon 10.2.2. This specific case would be one of the main reasons to upgrade, but I was hoping to confirm this first.

[MFGF]

Good point, Bdbits. But unfortunately I'm still on version 10.2, so I can't run these test with the new bridge functionality that is on versjon 10.2.2. This specific case would be one of the main reasons to upgrade, but I was hoping to confirm this first.

Confirmed. Bear in mind it's only for models/packages using Dynamic Query Mode though.



Cheers!

MF.

[amplizz]

Confirmed. Bear in mind it's only for models/packages using Dynamic Query Mode though.

Thanks for confirming the bridge functionality! Can also confirm if this type of bridge can be used together with conformed dimensions between the same two facts, and that in queries joins through the bridge always will be prioritized over joins using the conformed dimensions?

[cognostechie]

Hi !

Please try to understand how the bridge functionality will work. In a conformed dimension join, two Fact Query Subjects are joined to a Dimension Query subject so the Dimension is common between the Facts whereas in bridge join strategy, the bridge QS is joined to two Dimension QS and those two Dimension QS will be joined to their respective Fact QS. This means that if you use the bridge join then you know that there is no way you could have joined two Facts to a common dimension ( there are no keys which will allow that) so the possibility of conformed and bridge joins working together does not exist. It's either/or. If it was at all possible to make a conformed dimension join then there was no need for creating a bridge join.

[tinocash]

Amplizz, Or anyone else

Have you found a solution to the problem you stated below. We've upgraded to Cognos 10.2.2 but are facing the same issue with the usage of our bridge table.  When selecting data from two fact tables the user has to select an attribute from both dimensions that are connected to the bridge table or Cognos won't know to use it.  Is there a way to force Cognos to go through the bridge table?

Thanks in advanced

Sorry to bump this thread, but I have a question about the new bridge functionality in FM 10.2.2: Is it now possible to use a bridge table in addition to conformed dimensions without getting unpredictable results? From IBM's paper about bridge tables in Cognos 8 I quote one of the exclusions: "The additional dimensions must be created as distinct model query subjects so that a direct connection does not exist through these dimensions and allow the bridge table to be removed from the query." I'm hoping there is a way around this.

The case is this: two fact with different granularity, and there is a many to many relationship between them. For example a query could include a measure from Fact A (GL) and Dim F (suppliers) from Fact B (AP), or it could be a factless query (e.g. Dim C and Dim F). We have many conformed dimensions and would like to avoid using aliases, but instead "force" the query to use the bridge table. Is this supported with the new bridge functionality (or is it supported already)?

                                               -----------------------------------(1..1)Conform Dim A(1..1) -----------------------------------
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                              |(1..N)                                                                                                                                 (1..N)|
Dim C(1..1)<---->(1..N)Fact A(1..N)<--->(1..1)Dim D(1..1)<--->(1..N)Bridge(1..N)<---->(1..1)Dim E<---->(1..N)Fact B(1..N)<--->(1..1)Dim F
                                              |(1..N)                                                                                                                                 (1..N)|
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                              |                                                                                                                                                    |
                                               -----------------------------------(1..1)Conform Dim B(1..1) -----------------------------------

We are now using FM 10.2, but are considering to upgrade.