DQM - Bridge Table - Two facts tables

[Bohor2Gannes]

Dear all, ang Cognos guru Guys...

How I could resolve this modelisation case with Cognos Framework  DQM.
In my business case... article car have several Markets... Marketing collegues create market with a list of articles...

so the bridge table working very well with one fact table but with 2 ... measures are computed but the amount are not good

here an extract of the Cognos Query

Code Select Expand

            INNER JOIN BRIDGE1
            ON DS1.CD_ARTICLE = BRIDGE1.CD_ARTICLE
                INNER JOIN DS2
                ON BRIDGE1.CD_MARKET= DS2.CD_MARKET
                    INNER JOIN DS3
                    ON BRIDGE1.CD_ARTICLE = DS3.CD_ARTICLE


the problem is if one article is not present in the fact2.... this impacts fact1 due to the "inner join" clause

how I can resolve this case

[Lynn]

Your attachment is tiny at 120x101 pixels so it is practically unreadable.

I've not done too much with bridge tables but this Kimball article might be an interesting place to start. If I had to make a really wild guess I'd say alias your bridge table so you join each fact table to a separate copy of it, then join to your conformed dimension.

http://www.kimballgroup.com/2014/05/design-tip-166-potential-bridge-table-detours/

[Bohor2Gannes]

Hi Lynn,
I have tried this test without success!
cf my attachment !

[Bohor2Gannes]

test 3 & 4

[Bohor2Gannes]

an my final test...
two many to many with specific subject query..but not working..

[Lynn]

Kudos for trying many variations  :D

If you take a look at sections in the FM user guide about cardinality you'll find that the query engine identifies facts as those that are on the "n" side of all relationships. You are making your bridge table the fact by having it as the only table on the "n" side of relationships.

I would suggest you set fact1 and fact2 as the "n" side of the 1..n join to the bridge. All your other joins perhaps should be 1..1.

Again, not having done much with bridge tables I'm just guessing here, but for sure Cognos looks for the "n" on all relationships to a particular query subject to determine if it is a fact or not.

[bus_pass_man]

but for sure Cognos looks for the "n" on all relationships to a particular query subject to determine if it is a fact or not.

That's correct.  The problem is that's also how bridge tables' relationships need to be modeled. With 10.2.1 onward the chore of modeling for bridge tables is a bit easier as query subjects have a property which can identify the query subject as a bridge table.

Just out of curiosity, when you looked at the log files what information did you discover?  Where the messages different each attempt?

so the bridge table working very well with one fact table but with 2 ... measures are computed but the amount are not good

When you say its working with one fact table do you mean, one particular fact table or when only one of your two fact tables are in the query? 

Could you elaborate about the nature of the problem with the amounts?  Facts usually are on the many end of a relationship.  You might want to look at that.   If the cardinality to your facts is many, do you get the results you are seeking?

[Lynn]

With 10.2.1 onward the chore of modeling for bridge tables is a bit easier as query subjects have a property which can identify the query subject as a bridge table.

Oooh! I didn't know that. I see the DQM Usage property has Bridge as one of the options. Good to know. Thanks!