OBIEE 11.1.1.7 - Include Null Values

In OBIEE 11.1.1.7 there is a change of behavior regarding Rows with Null Values. What is the difference between new features and change of behavior? It means that things that used to work in previous releases work differently.

Lets start with new features: In most relevant presentation object properties (table, pivot, chart...) there is a new flags regarding "Include Rows with only Null Values" or in Pivot, 2 new flags: "Include Rows with only Null Values" and "Include Columns with only Null Values".

This a result with this option turned off:

 

 Here is the same with optioned turned on:

You can see that now I have an Null Amount_Sold for Catalog Channel, I didn't have in the previous result.

In the past to achieve this functionality you had to do an right outer join in the Business Layer. Most DBAs I know hate outer joins because of performance reasons.

What happened here?

The regular (first query) did the following select:

WITH
SAWITH0 AS (select sum(T43863.AMOUNT_SOLD) as c1,
     T43790.CHANNEL_DESC as c2,
     T43790.CHANNEL_ID as c3
from
     CHANNELS T43790,
     SALES T43863
where  ( T43790.CHANNEL_ID = T43863.CHANNEL_ID )
group by T43790.CHANNEL_DESC, T43790.CHANNEL_ID)
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select distinct 0 as c1,
     D1.c2 as c2,
     D1.c3 as c3,
     D1.c1 as c4
from
     SAWITH0 D1
order by c2, c3 ) D1 where rownum <= 65001

The important part of the select statement is:
select sum(T43863.AMOUNT_SOLD) as c1,
     T43790.CHANNEL_DESC as c2,
     T43790.CHANNEL_ID as c3
from
     CHANNELS T43790,
     SALES T43863
where  ( T43790.CHANNEL_ID = T43863.CHANNEL_ID )
group by T43790.CHANNEL_DESC, T43790.CHANNEL_ID)

Now lets see the code for the "Include Rows with only Null Values" turned on:

We have 2 queries!

Q1:
select T43790.CHANNEL_DESC as c1,
     T43790.CHANNEL_ID as c2
from
     CHANNELS T43790
order by c1, c2
Q2:
select T43790.CHANNEL_DESC as c1,
     T43790.CHANNEL_ID as c2,
     sum(T43863.AMOUNT_SOLD) as c3
from
     CHANNELS T43790,
     SALES T43863
where  ( T43790.CHANNEL_ID = T43863.CHANNEL_ID )
group by T43790.CHANNEL_DESC, T43790.CHANNEL_ID
order by c1, c2

So what can I do to show the Null lines?

Option 1:

In the Criteria, Analysis properties, at Data Tab, mark the "Include Null Values":

Option 2:

Do it for each Visualization in the Analysis as shown before.

Great! No outer join, just a quick select on the dimension.

So why I was blabbering about "change of behavior"? This is just great new functionality!

Lets see what happens with outer joins...

I duplicated the Channels Dimension with an Alias and in the Business Layer have set a right outer join between the new Channels and Sales Fact.

I created an Analysis:

I expected to see 5 lines of results, but I have only 4!

 This is different from previous releases.

The SQL has outer join on Channels:

select T45370.CHANNEL_DESC as c1,
     sum(T43863.AMOUNT_SOLD) as c2
from
          SALES T43863 left outer join
          CHANNELS T45370 /* Channels_outer */  On T43863.CHANNEL_ID = T45370.CHANNEL_ID
group by T45370.CHANNEL_DESC
order by c1

 But the result doesn't show the null lines.

When applying the previously shown options it works well and I see the Null lines as well. I just have to want them explicitly!

This is a change of behavior.

P.S.
Thanks to Eira and Rakefet from Oracle ACS for their enthusiastic remarks about this feature.