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

Manoj Dixit

Thanks Thomas and Christian. I did go through the contents of the earlier post you had suggested, Thomas. Unfortunately, it did not help in generating the correct Left Outer SQL.

Below are more details about the issue.

Oracle Table Data

OBI EE Analysis

SQL Generated

Full SQL is

WITH SAWITH0 AS
 (SELECT SUM(T61718.UNIT_COST) AS c1,
 SUM(T61839.AMOUNT_SOLD) AS c2,
 T61799.TIME_ID AS c3,
 concat(concat(concat(T61725.PROD_NAME, ' ( '), CAST(T61725.PROD_ID AS VARCHAR ( 10 ) )), ' )') AS c4
 FROM CHANNELS T61710    /* DIM_CHANNEL */
 ,  TIMES T61799    /* DIM_DATE */
 LEFT OUTER JOIN ( PRODUCTS T61725 /* DIM_PRODUCT */
 LEFT OUTER JOIN COSTS T61718    /* FACT_COSTS */
 ON T61718.PROD_ID = T61725.PROD_ID)
 ON T61718.TIME_ID = T61799.TIME_ID
 ,    SALES T61839    /* FACT_SALES */
 WHERE ( T61710.CHANNEL_ID                                                                                = T61839.CHANNEL_ID
 AND T61718.TIME_ID = TO_DATE('1998-01-01' , 'YYYY-MM-DD')
 AND T61725.PROD_ID = T61839.PROD_ID
 AND T61799.TIME_ID = T61839.TIME_ID
 AND T61799.TIME_ID                                                                                       = TO_DATE('1998-01-01' , 'YYYY-MM-DD')
 AND concat(concat(concat(T61710.CHANNEL_DESC, ' ( '), CAST(T61710.CHANNEL_ID AS VARCHAR ( 10 ) )), ' )') = 'Internet ( 4 )'
 AND concat(concat(concat(T61725.PROD_NAME, ' ( '), CAST(T61725.PROD_ID AS       VARCHAR ( 10 ) )), ' )') = 'OraMusic CD-R, Pack of 10 ( 118 )' )
 GROUP BY T61799.TIME_ID, concat(concat(concat(T61725.PROD_NAME, ' ( '), CAST(T61725.PROD_ID AS VARCHAR ( 10 ) )), ' )')
 )
SELECT D1.c1 AS c1, D1.c2      AS c2, D1.c3      AS c3,  D1.c4 AS c4,  D1.c5      AS c5
FROM
 ( SELECT DISTINCT 0 AS c1, D1.c3             AS c2,    D1.c4 AS c3,    D1.c2             AS c4,    D1.c1             AS c5  FROM SAWITH0 D1
 ORDER BY c2,    c3  ) D1
WHERE rownum <= 65001

LTS is configured as

The Outer Joins are as under.

I tried to configure the Levels for the LTS and the Measurements, but that did not help in producing the expected query and results (still No Results).

Have I left out something obvious? Why would Oracle BI create joins like,

FROM CHANNELS T61710    /* DIM_CHANNEL */
 ,  TIMES T61799    /* DIM_DATE */
 LEFT OUTER JOIN ( PRODUCTS T61725 /* DIM_PRODUCT */
 LEFT OUTER JOIN COSTS T61718    /* FACT_COSTS */
 ON T61718.PROD_ID = T61725.PROD_ID)
 ON T61718.TIME_ID = T61799.TIME_ID
 ,    SALES T61839    /* FACT_SALES */
 WHERE ( T61710.CHANNEL_ID                                                                                = T61839.CHANNEL_ID
 AND T61718.TIME_ID = TO_DATE('1998-01-01' , 'YYYY-MM-DD')
 AND T61725.PROD_ID = T61839.PROD_ID
 AND T61799.TIME_ID = T61839.TIME_ID
 AND T61799.TIME_ID                                                                                       = TO_DATE('1998-01-01' , 'YYYY-MM-DD')

Any suggestions, please.

Thank you.

Regards,

Manoj.

Thomas Dodds

Right off the bat ... don't join 2 fact tables directly ... star schema is such that dimensional attributes are used in a conformed way to related facts, in your case the facts of sales and costs are related through the commonality found in the channel and time dimensions.

Manoj Dixit

Thanks for the reply, Thomas.

Yes, I would like to avoid the above single LTS configuration. There are three more Presentation Tables in the subject area - 'Fact - Costs', 'Fact - Costs and Sales' and 'Fact - Sales'. The 'Fact - Costs and Sales' has got two LTSs. When I use any of these, OBI EE generates a full outer join. It gives the correct results.

But my DBA has a question, why can't OBI EE generate a left outer join query to join two fact tables based on conformed dimensions. The DBA "thinks" that OBI EE should be able to create a left outer join query (like in SQL Developer SQL Query) and should solve the requirement via the concerned Analysis / RPD.

I shall try out the lookup too sometime this week.

Regards,

Manoj.

Thomas Dodds

Quite frankly ... a properly formed warehouse has inner joins as any fact row that doesn't have an appropriate dimensional key has the -1 / 'Unassigned' key, so the star is made up of optimized indexed inner joins ...

Furthermore, given the inner and join specs you've got in your LTS, it leads me to believe that your model could be much simplified.  Presentation tables have no effect on the BMM; so presenting multiple facts should not determine your BMM (logical structure) ... you should have a distinct logical table for each dimension and each fact ... if you have a fact table with multple LTS; then you are probably dealing with an aggregate (for which you should also have aggregate dimensional LTS as well) ... keep your modeling as simple as you can.  

To boot, prompting on LEFT/RIGHT OUTER joins can cause the BI server to behave differently as well ...

It is best to stick to STAR schema principles -- first move is to get rid of fact to fact joins; make your logical facts a collection of like sources for like factual data (sales is a fact; costs is another)

Manoj Dixit

Thanks for your reply, Thomas, to which I agree.

It's sometimes bit confusing for me to explain to a colleague what OBI EE can do and can not. For example, if I were to model the BMM Layer based on an OLTP schema, how the joins can be configured. But as a true Star Schema follower, I agree 100% to the above.

Regards,

Manoj.

Thomas Dodds

You can model OLTP in the BMM layer ... you just need to be much more careful not to build circular joins and such ... most DWs have a little bit of snowflaking so it's a very common practice to have multiple LTS per logical table as well.  I see red flags when I see an LTS of a fact joined to another fact -- in EVERY CASE OLAP/OLTP the BMM (to work right) needs to be a star schema approach.

Here's a decent write up of an approach you might be able to use to 'reduce' your OTLP into a BMM STAR ...  https://mtalavera.wordpress.com/2013/06/04/obiee-reporting-from-transactional-databases/

[Deleted User]

It hasn't really gotten anything to do with "what OBI EE can do and can not".

But my DBA has a question, why can't OBI EE generate a left outer join query to join two fact tables based on conformed dimensions. The DBA "thinks" that OBI EE should be able to create a left outer join query (like in SQL Developer SQL Query) and should solve the requirement via the concerned Analysis / RPD. 

OBI is about models, not SQL queries. Multi-star queries across non-conformed dimensions work as they should over the LTS and content levels (for non-conformed dimensonalities obviously) because the models are not necessarily - and here's the point where SQL queries just cr*p out - sourced from a single or just homogeneous data sources! Assuming that OBI only ever reads from one database at a time or one single database technology at a time is basically disregarding core capabilities of the tool.

Long story short: If your DBAs want to write SQL queries tell them to use SQL Developer. It's free. Use OBI for adding proper value.

Manoj Dixit

Thomas and Christian, thanks once again.

Christian, the DBA spotted a full outer join in an OBI EE generated Query, hence his questions about why and how. He asked whether or not an equi join could be used but the business needs a left outer join as described using the SH schema.

I guess the only option is using a database view?

Regards,

Manoj.

Edit: I haven't yet studied the contents of the article mentioned by Thomas, which may resolve my specific issue.

Manoj Dixit

Hi,

Just came across this blog where it seems that instead of full outer joins, left outer joins are used. I am not sure about whether or not an upgrade to a higher version from 11.1.1.7.0 would help and even if the higher versions use the left outer joins, the scenario described using the SH schema (based on the actual business requirement) can be taken care of.

Cross Drilling: Where did the Full Outer Join Go!!!! | Frank Davis' Blog: OBIEE ONE

A question related to my main question: If a higher version really supports a left outer join, why was this changed?

Regards,

Manoj.