Solved

full outer join in FM

Posted on 2008-10-27
3
1,457 Views
Last Modified: 2012-05-05
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
Comment
Question by:muhammadaqeel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Expert Comment

by:RWrigley
ID: 22815792
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
 

Author Comment

by:muhammadaqeel
ID: 22816139
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
 
LVL 12

Accepted Solution

by:
RWrigley earned 500 total points
ID: 22816271
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question