?
Solved

full outer join in FM

Posted on 2008-10-27
3
Medium Priority
?
1,490 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 1500 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 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