Solved

full outer join in FM

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now