All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Wednesday, June 29, 2011

'Date' defined by repository with timestamp information

It's been a while since the last post.

Today, I just want to talk about something we run into on our project related to defining 'date' using repository variable.

Defining a dynamic variable that returns yesterday's date is pretty easy by just adding the following statement in the initialization block:

select sysdate-1 from dual

The result shows that it also comes with hours and seconds after the date, like so:

Now changing the statement to select trunc(sysdate - 1) from dual, will remove the unwanted values at those timestamp positions following date, like so:

All these are fine and the value that the variable provides is also correct. However, we did overlook at one thing after moving forward with this setup.

Take the variable 'Yesterday_date' for instance, when we add that variable to the filter in the report, the SQL that's generated shows the following at the where clause:

T3266990.SNAPSHOTDATE = TIMESTAMP '2011-06-28 00:00:00'

Instead of having T3266990.SNAPSHOTDATE = TO_DATE('2011-06-28' , 'YYYY-MM-DD') when filtering without variable.

As far as the data is concerned, it is working correctly, however, it may affect the query's performance when it is using TIMESTAMP.

Depending on the setup of the database and it will affect the partitioning of the query if the passing parameter has timestamp in it, which isn't going to be a optimal thing to have.

The challenge now is how to use dynamic variables and still get To_Date in the SQL.

We have reached out to oracle support, basically we have been told that this is the way it is as:
"The init block returns a timestamp because OBI has no internal knowledge of what datatypes will be returned by the SQL statement. It only knows what the database tells it"

For further reading please go to:

The bottom-line is that in order to disassociate repository variable with timestamp, the datebase itself must contain date only data-type. If you are oracle DB, it automatically includes time & second in it so it will have to be changed completely, which isn't going to be easy.

Therefore the decision is up to the business as we know the factual behavior.

Looking forward to seeing whether the next release of 11G will have to way around this behavior.


Until next time.
Related Posts Plugin for WordPress, Blogger...