Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1528
  • Last Modified:

full outer join in FM

hey when i try to combine 2 dims with fact, both have outer join......i ended up having full outer join ...tht i don't need...wht is the solution for this. i was think to join 3 dims data in fact to facilate report authors...but when i see query subject  test data it shows correct but test query contains full outer join.....how can i remove full outer join
0
muhammadaqeel
Asked:
muhammadaqeel
  • 2
1 Solution
 
RWrigleyCommented:
Anytime you try to report off of two fact tables through a conformed dimension, Cognos will create a "stitch" query (a full outer join with a coelsce).  It does this in order to ensure that the values returned are aggregated correctly on each fact table.  This behavior is documented in the Framework Manager Users Guide:

The SQL generated by Cognos 8, known as a stitched query, is often misunderstood. A stitched query uses multiple subqueries, one for each star, brought together by a full outer join on the common keys. The goal is to preserve all dimensional members occurring on either side of the query.

What Is the Coalesce Statement?
A coalesce statement is simply an efficient means of dealing with query items from conformed dimensions. It is used to accept the first non-null value returned from either subquery that is built for each detected fact table. This statement allows a full list of keys with no repetitions when doing a full outer join.

Why Is There a Full Outer Join?
A full outer join is necessary to ensure that all the data from each fact table is retrieved. An inner join gives results only if an item in inventory was sold. A right outer join gives all the sales where the items were in inventory. A left outer join gives all the items in inventory that had sales. A full outer join is the only way to learn what was in inventory and what was sold.
0
 
muhammadaqeelAuthor Commented:
im not getting data from two facts...but its the same fact and multiple dimensions...each has outer join.0..n....wht happen is tht when I select different fieds from diff dims have o..n relationship....FM does show me the date but in query it shows full outer join. I was pulling data from 3 dims and 1 fact.....
0
 
RWrigleyCommented:
What is the full cardinality between each of the query subjects?  I'll point out that outer joins are a really bad idea in terms of a data warehouse...every FK in the fact table should have a corresonding entry in the Dim table.  If there's a not a match, you should have a "No Entry" entry in the dimension, and use that.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now