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
Solved

full outer join in FM

Posted on 2008-10-27
3
1,422 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
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with running SSRS Reports thorugh ISA2006 2 214
Tableau dashboard drill-though 2 3,937
SSRS and Visual Studio Setup 7 89
SSRS generating a table of contents 6 239
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

790 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