OBIEE Events Calendar

 
First of all Kudos to Hitesh for laying the ground work: http://hiteshbiblog.blogspot.com/2010/04/obiee-showing-data-on-calendar.html
First you have to go to the MooTools site and download the basics:
The mootools core: http://mootools.net/download => choose the uncompressed version, it makes debugging easier.
Next get the More building blocks Date, Scroller, Tips: http://mootools.net/more
Finally get the calendar control: http://dansnetwork.com/mootools/events-calendar/download/
Put everything in a subfolder of the Res folder (if you are using OC4J as webserver, you have to sync both RES folders):

Let’s get some base data to work with: Startdate, Enddate, dayofweek, brand, revenue:

Now we add a narrative view:

In the prefix part we first select the size of the calendar control:

<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECalLarge.css">
or
<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECal.css">
or
<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECalSmall.css">

Next we add the references to the javscript:

<script language="javascript" src="./res/mooTools/mootools-1.2.4-core-nc.js"></script>
<script language="javascript" src="./res/mooTools/mootools-1.2.4.4-more.js"></script>
<script language="javascript" src="./res/mooTools/mooECal.js"></script>

as div tag to hold the body:

<div id="calBody"></div>

and a function to set the background and font color:
<script language="javascript">

function getDiv(holFlag)
{
if(holFlag=='1' || holFlag=='7')
{
return '<div style="background-color:#990000;color:#ffffff;">';
}
else
{
return '<div style="background-color:#999900;color:#ffffff;">';
}
}

finally the header of the control:

new Calendar({calContainer:'calBody', newDate:'1/3/2007',
cEvents:new Array(

The date is the date on which the control wil be opened.
The total prefix should look like this:

<link rel="stylesheet" type"text/css" href="./res/mooTools/mooECalLarge.css">
<script language="javascript" src="./res/mooTools/mootools-1.2.4-core-nc.js"></script>
<script language="javascript" src="./res/mooTools/mootools-1.2.4.4-more.js"></script>
<script language="javascript" src="./res/mooTools/mooECal.js"></script>
<div id="calBody"></div>
<script language="javascript">
function getDiv(holFlag)
{
if(holFlag=='1' || holFlag=='7')
{
return '<div style="background-color:#990000;color:#ffffff;">';
}
else
{
return '<div style="background-color:#999900;color:#ffffff;">';
}
}
new Calendar({calContainer:'calBody', newDate:'1/3/2007',
cEvents:new Array(

In the narrative part we fill the array:

{
title: getDiv('@3') +'@4: '+ '@5 </div>',
start: '@1',
end: '@2',
location: ''
}

In the postfix we close the array:

)
}); </script>

Set the separator to “,”
Warning if you try to save this from the narrative view you will get the ‘opaque’ save screen….

switch to the criteria view first and then save!
Add the narrative to your compound lay-out:

Till Next Time

a generic calendar dimension

Finally had the time to activate on of my other blogs. This one will contain my more generic articles. Here is the first one: http://bizintell101.blogspot.com/2009/10/bi-101-generic-calendar-dimension.html

Feel free to comment.

Till Next Time

OBIEE date prompt to day month year

A Obiee prompt from a calendar to a presentationvariable is cast as date. One way of extracting the day, month or year can be done based on the locate function:

day: substring( '@{Date1}{01/01/1900}' from (locate('/','@{Date1}{01/01/1900}',1) +1)for (locate('/','@{Date1}{01/01/1900}' ,4) - locate('/','@{Date1}{01/01/1900}',1)-1))

OBIEE Date, TimeStamp and ORACLE Indexes

Mark Rittman triggered meto investigate this: If you are Filtering against a date column in OBIEE you will see in the log that a timestamp conversion is added to your query:

select distinct T2826.DAYTIME as c1
from
     DAYTIME2 T2826
where  ( T2826.DAYTIME = TIMESTAMP '1968-03-14 00:00:00' )
order by c1

This timestamp conversion implies that a index on the DATE column is skipped, resulting in a full tablescan. How can we force the usage of an index? Here is an alternative:

1: Create a FUNCTIONAL INDEX on your date column Fi:

create index dt3 on daytime2(cast ("DAYTIME" as timestamp));

2: Create a view on your table casting the date column as TIMESTAMP:

create view v_daytime2
    as
     (select cast(daytime as timestamp)  as daytime from daytime2)

Map this view to your RPD and you will, check the explain plan, see that Oracle uses your index.

If you have any better / other solutions please let me know!

Till Next Time

OBIEE Between Dates Prompt

One the questions you see regularly on the forums is how to create a 'between dates' dashboard prompt if the date column is not a part of a date dimension. If more important with the use of the calendar control.

Step 1 create a new prompt based on a date dimension:`

select 'Calendar' from the control dropdown box, if you do it  later on it won't be available (undocumented feature ;-) ).

Step 2: Alter the column function:

CASE WHEN 1=0 THEN CALENDAR.D_DATE ELSE TIMESTAMP '1900-01-01 00:00:00' END

The CASE WHEN part guarantees validation of the column.

Step 3: Set the presentation variable and label

Step 4: repeat step1

Step 5:  Alter the column function:

CASE WHEN 1=0 THEN CALENDAR.D_DATE ELSE TIMESTAMP '2999-12-31 00:00:00' END

This has to be different from the one used in step 2 since OBIEE uses the default outcome of the formula as internal name for the column.

Step 6: Set the presentation variable and label

Step 7: reference the variable in your report:

Step 8 : Create the dashboard:

Till Next Time

OBIEE Reporting on dateranges

When your data is stored in ranges instead of individual events:

Graphical representation over a period becomes quit tricky. Best solution: Have the ETL people create a valid entry for each day. Good alternative: Create an entry for each day in the repository.

First create an alias for the calendar table:

Join this with a COMPLEX join to your fact table

Don't forget to restrict the open ended data!

DIM_DATE.DATEDATE >= AWOL.FROM_DATE AND DIM_DATE.DATEDATE <=  CASE  WHEN AWOL.TO_DATE IS NULL  THEN CURRENT_DATE ELSE AWOL.TO_DATE END

Join this with your regular calendar dimension and set it to outerjoin in the Business model.

Check the report:

Till Next Time

OBIEE Making a distinct date prompt

One of the question I get asked on regurlar basis is: "If have a timestamp column, how do I convert this in a date only prompt".

 

One of the possible solution is this:

First Create as truncated date column in your RPD:

using :

CAST( "BM_CP_TEST"."DIM_DATE_F_FACTS"."D_DATE_RANDOM"  as DATE)

Next create a prompt:

on the show dropdown select SQL results.

Next edit the SQL to:

SELECT DISTINCT DIM_DATE_F_FACTS.DATE_TRUNC FROM BM_CP_TEST
ORDER BY DIM_DATE_F_FACTS.DATE_TRUNC

(Don't forget the order by!)

EDIT:

Till Next Time

OBIEE Leap Year Challenge

Have a look at these tables of February 2008 and 2009: (remember 2008 was a "leap" year)

If in February 2009 we use the ago function of OBIEE:

AGO(BM_AGO_TEST.F_FACTS.F_FACT_VAL, BM_AGO_TEST.DIM_DATE_TESTDim."YEAR", 1)

On a month level we get:

which is correct. But if we do it on a day by day level we get:

Which is not quite correct since it is missing the "AGO" data for FEB 29 2008 (1232). Now you could argue that this is correct since there is no FEB 29 2009. Most accounting systems agree that in such a case the data for FEB 29 should be added to the data of FEB 28. One way of solving this is adding a Year Ago DATE_ID to your date dimensions with a double entry for FEB 28 2009:

If connect an alias of our fact table to the DT_ID_YAGO we get:

 

We see that the data for FEB 28 and FEB 29 2008 is now summarized for FEB 28 2009.

An other advance for working with fixed ago column is your time dimension is that the query costs are about 80 % percent lower then using the OBIEE AGO function. Is this example 187 versus 853 on as Oracle 10Gr2 database.

Till Next Time

OBIEE First / Last day previous week

First day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY,-6, (TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)))

Last day of the previous week:

TIMESTAMPADD(SQL_TSI_DAY, DAYOFWEEK(CURRENT_DATE) *-1,CURRENT_DATE)

Till Next Time

OBIEE First / Last of month

OBIEE doesn't have a built in function for the first and or last of a month. This can be easily achieved by the follwing functions:

First of month:

TIMESTAMPADD(SQL_TSI_DAY, ( DAYOFMONTH(CURRENT_DATE) * -1) + 1, CURRENT_DATE)

Last of month:

TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(CURRENT_DATE) * -1 , TIMESTAMPADD(SQL_TSI_MONTH, 1, CURRENT_DATE))

Till Next Time

OBIEE Handling SCF data / PIT Reporting

Due to more and more compliance rules a lot of DWH's these days have there data stored based on the SCF principal (Slowly Chancing Fact). A typical example of SCF data is the state of an order:

If you want to do PIT reporting (Point In Time) you need to use the SCF columns:

One possible solution track is using creating a SCF_IS_PIT column in the repository based on a repository variable.

First let's create a PIT_DATE session variable:

Next create the column:

Our data will now look like this (Current date = 03-DEC-2008"):

If we add a filter to our report :

we only get "CURRENT" data:

For most users this is the information they are looking for. For those who need to go "back in time" with the same report, you simple add a prompt to the dashboard allowing them to set the PIT date:

Till Next Time

OBIEE AGO and TODATE Series

Have a look a these two tables:

Let's create a MONTH_YEAR_AGO Column:

Have a look at the result:

....some thing is not right here....

Let's investigated the date dimension:

Looks alright? Have a look again without year and month:

We have a sorting issue here, so let's add a sorting column:

OK that's fixed, let's go back to our report:

Still a problem there, let's sort the chronological key:

That really fixed the problem!

Till Next Time

OBIEE Sort Order Column property

A customer had the following problem with his dates dimension:

The month year part of the date dimension wasn't sorted in the right way. He forgot to assign a sort column (format ('YYYYMM')):

The preferred result:

Till Next Time