OBIEE Understanding Outerjoins Part 2

In a previous article I showed the basics of working with OBIEE Outerjoins: (http://obiee101.blogspot.com/2008/11/obiee-understanding-outerjoins-part-1.html).

This works fine until you add a filter :

From the log:

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
having 440000 < sum(T31.F_FACT_VAL)
order by c1

This "kills" the outerjoin.... I hear think why not add "OR IS NULL". Let's do that:

 

From the log:

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c2
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
having 440000 < sum(T31.F_FACT_VAL) or sum(T31.F_FACT_VAL) is null
order by c1

We still  are missing a couple off years.

Let try an in view filter: change F_FACT_VAL to:

CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end

This give us all the years back:

From the log:

SET VARIABLE QUERY_SRC_CD='Report';SELECT DIM_YEAR_MONTH_DAY.D_YEAR saw_0, CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end saw_1 FROM BM_OUTER_JOIN ORDER BY saw_0

select T26.D_YEAR as c1,
     sum(T31.F_FACT_VAL) as c4
from
          DIM_YEAR_MONTH_DAY T26 left outer join
          F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
order by c1

As you can see OBIEE did the case when logic internally.

Till Next Time

This article is orignally written for the ciber knowledge blog:

OBIEE Understanding Outerjoins Part 1

First of all in an "ideal" DWH you wouldn't have a need for outerjoins, but sometimes you need to make data visible which you don't have.

Outerjoins in OBIEE can be a bit tricky and don't always give you the result you would expect. In this article I will get trough the basics, in a next trough some pittfalls....

Creating an outerjoin

OBIEE How To Replace Multiple Go Button By One Go Button In Prompts

Sunil wrote a good one:

http://sranka.wordpress.com/2008/11/09/how-to-replace-multi-go-button-prompt-by-one/

Till Next Time

OBIEE Outerjoin workaround?

(Direct answer: No :-( )

Have a look at this table:

What I'm looking for is this:

But I don't want to use outerjoins, since obiee messes them up anyway when you try do filtering later on.

Here is a small Q&D trick

Add an extra fact column and multiply it with a random number:

Next hide the column:

What is happening?

If we look in the log we see that OBIEE isn't executing the RAND() function on the Database side but internally. For that it first gets only the dimension columns from the database:

select T26.D_YEAR as c2,
T26.D_YEAR_MONTH as c3
from
DIM_YEAR_MONTH_DAY T26
where ( T26.D_YEAR = '2013' )

Next it will get the facts:

select D2.c2 as c1,
D2.c3 as c2,
D2.c1 as c3
from
(select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
from
(select sum(T31.F_FACT_VAL) as c1,
T26.D_YEAR as c2,
T26.D_YEAR_MONTH as c3,
ROW_NUMBER() OVER (PARTITION BY T26.D_YEAR_MONTH ORDER BY T26.D_YEAR_MONTH ASC) as c4
from
DIM_YEAR_MONTH_DAY T26,
F_FACTS T31
where ( T26.D_YEAR = '2013' and T26.D_YEAR_MONTH_DAY = T31.D_DATE )
group by T26.D_YEAR, T26.D_YEAR_MONTH
) D1
where ( D1.c4 = 1 )
) D2
order by c2

After that it stitches the query's together.

Till Next Time

OBIEE Presentation Services Cache Settings

The cache is accessed when users make requests in Answers. This is not the same cache that is accessed by the Oracle BI Server. You can change the internal defaults by modifying the Oracle BI Presentation Services configuration file (instanceconfig.xml) to add the cache entries.

First of all try to have your presentation Cache on the fastest disk available (consider a "RAM drive").

Specifying the Maximum Number of Oracle BI Presentation Services Open Record Sets

From the guide:
You can override the maximum number of open record sets that Oracle BI Presentation Services keeps open at any one time. The internal default is 10. The minimum value is 3. For systems under
significant loads, you can increase this value to 500 or 1000.
The following entry is an example:
<CacheMaxEntries>100</CacheMaxEntries>

Rule of thumb: Number of concurrent users * 5.

There are a couple of more setting, but unless you have a lot of concurrent users (1000+) the "gain" of these settings is minimal.

Specifying the Maximum Amount of Time an Entry Can Exist in the Oracle BI Presentation Services Cache

From the guide:

You can override the maximum amount of time, in minutes, that an entry in the cache can exist before it is removed. The internal default is 60 (one hour). Depending on (the number of requests being run, an entry may be removed before the time limit expires.
The following entry is an example:
<CacheMaxExpireMinutes>60</CacheMaxExpireMinutes>

Rule of thumb: Don't make this any longer then 10 % of your ETL refresh rate to avoid the risk of user seeing "old" data inadvertently. Be aware that CacheMaxExpireMinutes also influenced by CacheMinUserExpireMinutes. This means that in a 24Hour ETL cycle this shouldn't be any longer then 150 minutes.

Specifying the Least Amount of Time an Entry Can Exist in the Oracle BI Presentation Services Cache

From the guide:

You can override the minimum amount of time, in minutes, that an entry in the cache can exist before it is removed. The internal default is 10.
The following entry is an example:
<CacheMinExpireMinutes>10</CacheMinExpireMinutes>

Rule of thumb: 20% of CacheMaxExpireMinutes

Specifying the Least Amount of Time an Entry Can Exist in the Oracle BI Presentation Services Cache After Use

From the Guide:

You can override the minimum amount of time, in minutes, that an entry in the cache can exist after it has been viewed by a user. The internal default is 10.
For example, if CacheMaxExpireMinutes is set to 60 minutes and a user views the entry during the 59th minute, the entry exists for that user for an additional 10 minutes. The user can continue paging through the data without requiring a new request to be run.
The following entry is an example:
<CacheMinUserExpireMinutes>10</CacheMinUserExpireMinutes>

Rule of thumb: same as CacheMinExpireMinutes

Till Next Time

This article was orginally written for the Ciber knowledge blog: http://knowledge.ciber.nl/weblog/?p=150

OBIEE Default colour Title border

Good a quick question on how to set the default Titleborder colour in OBIEE to red.

Go to your view.css directory in ..s_MyCompagny\b_mozilla_4

Locate this part and change the colour:

/* ***** */
/* Title */
/* ***** */

.TitleTable {
    font-family: arial, helvetica, sans-serif;
    border-width: 0 0 4px 0;
    border-style: solid;
    border-color: #FF0000;
}

If you are on OC4J don't forget it to copy to applications directory: ...oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_MyCompagny\b_mozilla_4

Till Next Time