Categories
How to specify left outer join when fetching data from two fact tables.
Hi,
I am using OBI EE 11.1.1.7.0.
I have to build an Analysis where I need to display measurement values from two fact tables. SQL Query generated by Oracle BI uses a full outer join. Instead of this, I want a left outer join.
For example, Fact1 table has 10 rows. I want to display Fact1.MeasA. Fact2 table has 5 rows. I want to display Fact2.MeasB.
Now, Oracle BI is forcing a full outer join and fetching more than 50 rows.
What I want is fetch 10 rows from Fact1 and use the same dimension values to fetch data from Fact2 table. If no dimension value is found in Fact2, then Fact2.MeasB should display a null value. Ultimately, the Analysis should display only 10 rows rather than more than 10 rows (due to a full outer join).
Note: I am using conformed dimensions to start with.
Could you please suggest a way to achive this?
Thank you.
Regards,
Manoj.
Edit: Now, Oracle BI is forcing a full outer join and fetching 50 rows.
Answers
-
Hi,
Did you add your dimension column on this analysis too or just fact columns?
Try that:
Just drag and drop for your criteria tab
1 - Dimension Column (to summarize your measures) / 2 - FACT1 Column (ensure that is linked to dimension called early) / 3 - FACT2 Column (ensure that is linked to dimension called early)
It must work according you want
Felipe Idalgo
0 -
Use the Physical layer joins to specify columns
Use the logical layer joins to specify inner/outer, cardinality, driving table, etc.
0 -
Hello Felipe,
Yes, there is a conformed dimension. For example,
CDim.Col1, Fact1.MeasA and Fact2.MeasB.
When used with a non-conformed dimension, it fetches data only from Fact1, for example,
NCDim.Col1, Fact1.MeasA.
Fact2.MeasB is automatically set to NULL by Oracle BI.
Regards,
Manoj.
0 -
This might help you: https://obibb.wordpress.com/2010/05/31/multiple-fact-reporting-on-non-conforming-dimensions/
Approach is the same despite this being a 10g related blog post.
0 -
Thanks Thomas.
I shall try out the driving table. Other things have been tried out.
Regards,
Manoj.
0 -
check out that blog post before you go after the driving table ...
Driving tables are for cross-database joins ...
0 -
Ok, thanks.
0 -
Hi,
What I would like to know is how do I implement / configure the below SQL Query in OBI Repository (RPD) esp., in the BMM Layer.
SELECT
sf.DAY_KEY
, sf.prod_key
, sf.customer_key
, sf.channel_key
, sf.promotion_key
, sf.amount_sold
, sf.quantity_sold
, cf.unit_cost
, cf.unit_price
FROM
sales sf
LEFT OUTER JOIN costs cf
ON
sf.day_key = cf.day_key
AND sf.prod_key = cf.prod_key;
Once this is modelled, I would like to build an Analysis with the following columns.
Date.Day
, Product.ProductName
, Sales.AmountSold
, Costs.UnitPrice
At the moment, when the Analysis is run, the Analysis is doing a full outer join but I would like a left outer join (as above).
Any suggestions please?
Thank you.
Regards,
Manoj.
0 -
The suggestion is the solution and it's simply what Thomas told you:
Use the Physical layer joins to specify columns Use the logical layer joins to specify inner/outer, cardinality, driving table, etc.
In your BM you just double-click the join in question and change it to a left outer join. Bob's your uncle.
0 -
If this approach isn't working for you (not sure why), you could try using the lookup dense/sparse as a way to achieve the same results. For every sale look up the cost based on the keys ...
Oracle BI EE 11g – Sparse Lookups & Left Outer Joins - Rittman Mead Consulting
0