bridge table help

[abailey]

I have a fact table that represents one order line of an order.  As a business we are now able to add multiple coupons to one order line.  I have my order_line fact table which I joined to a dimension which is then joined to the coupon fact table.  So it's   1..n ->  1.1.  ->  1.n. 

I publish my package and then I create a small query to look at order line and then coupon discount.  I look at the generated sql in tabular sql and I'm expecting to see the two facts joined in the native sql (sybase).  But I don't see that.  I see two separate queries.  So my result is wrong.  I don't want to create a new package for our users.  Any advice would be helpful.

I looked at my joins in Framework Manager, and it all looks good.  I have to be missing something somewhere.

[COGNOiSe administrator]

Are both facts in the same schema, meaning do they come from the same database connection definition?

[abailey]

Yes.  Thanks.

[MFGF]

You have modelled your metadata to contain two separate "Fact" query subjects, linked via a common dimension.  The net result of this is that the Query Engine will generate a stitch query.  If you look at the Cognos SQL within your report, I'm willing to bet that you will see Coalesce functions on the common dimensional values, and a Full Outer Join in the middle of the query.

This is expected behaviour where multiple facts are encountered in the same query.  If you want to avoid this from happening, consider merging the two "Fact" query subjects into a single model query subject in Framework Manager, and defining a relationship from the dimension to the combined fact as 1..1 -> 1..n  This should change the query structure to use a single native query in your reports.

Hope that helps,

MF.

[sir_jeroen]

I totally agree with MF....
What you'll have to do is to create a new dimension that contains the "joining" item. Eg.
in your first FACT table there's "Order num" and in the second fact you have "Order num","Coupon" then you'll have to create a Confirmed dimension with "Order num". If you do this then both facts can be joined using the "Order num" confirmed dimension.

Btw.. are you using JD Edwards?

[abailey]

I'm not using JDEdwards...and when I look at the cognos sql I don't see coalesce.

In FM I started off in my business view and created a star schema of my order table and stuck it in my business view.  I added the shortcut to my coupon fact table.  I created the joining dimention and used order_line_key to bridge to my order fact.  I then used my new dimension  and had order_line_key to connect to my coupon fact.  I  then looked at the object diagram.  I had to create a shortcut to the relationship beetween the order_line_key dimension and the coupon fact table. I republished the package macking sure I have my order_line dimension and my fact tables.  When I create a report in reportnet I don't see the coalesce.  I have to be missing something. 

Any advice on things to check next? 

[abailey]

Just figured it out... I needed to readd the join between my order fact table and my new order_line_key dimension.  When I brought the shortcut up to my business view I didn't readd the join.  I didn't think I had too.  I never did see coalesce, but my results are now correct.

Thanks for the help