Tip: DATE and DATETIME in OBIEE

I must admit that I am new to the OBIEE world and I really wish I can help people that are starting with this Oracle product to save some precious time when dealing with Calendar dimensions.

I have created a Calendar dimension in my database with a field "DIA" to be DATE. This dimension is meant to replace one that was already in place, same content, but more fields. I have imported the table in the physical layer, mapped it to the logical layer, but when I run the dashboard I kept on seeing date fields with DD/MM/YYYY hh:mm in the calendar prompts.

I spent quite a lot of time (call me dumb!) to find out that the answer was as simple as replacing the DATETIME field with DATE.

Tip: How to get Today Date using Sunopsis Memory Engine

Today I needed to retrieve today's date in 'YYYYMMDD' using SUNOPSIS Memory Engine.
That would have been much easier to do the following:

SELECT TO_CHAR(sysdate,'YYYYMMDD')
FROM dual

but I wanted to be able to get a date in a string format without relying on any Oracle database schema. To get today's date using In-Memory Engine is quite tricky.
First, you have to create a procedure and make sure you add the following steps:

0 - Drop_Dual_Table
Simply drops a table named dual (which does not exist by default in the SUNOPSIS database)



10 - Create DUAL table



20 - Insert Values



Then you can assign today's date to a variable (and you can format it as you wish) and use it in your interfaces/packages.



P.D. Originally I wanted to get yesterday's date directly in SUNOPSIS MEMORY ENGINE, but I could not find a way to assign -1 offset to CURDATE(). Any ideas?