nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to specify left outer join when fetching data from two fact tables.

Manoj Dixit

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.

Find more posts tagged with

Accepted answers

All comments

Felipe_Idalgo

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

Thomas Dodds

Use the Physical layer joins to specify columns

Use the logical layer joins to specify inner/outer, cardinality, driving table, etc.

Manoj Dixit

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.

Thomas Dodds

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.

Manoj Dixit

Thanks Thomas.

I shall try out the driving table. Other things have been tried out.

Regards,

Manoj.

Thomas Dodds

check out that blog post before you go after the driving table ...

Driving tables are for cross-database joins ...

Manoj Dixit

Ok, thanks.

Manoj Dixit

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

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.

[Deleted User]

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.

Thomas Dodds

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