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: Reverse Engineering in Oracle Data Modeler

I am currently building a Data Warehouse using OWB as integration tool. I have almost finished and now comes what I like the less about my job... documentation!
During the development process, you usually create a number of staging tables. Oracle Data Modeler allows you to import tables from a schema. This way it is much easier writing down your documentation and you also have a visual presentation ready to use.
Let's go through the reverse engineering process:
1) Go to File -> Import -> Data Dictionary

2) Add a new connection if you haven't set up any yet

3) Select your Schema/Database



Personally I find the filter option pretty useful for retrieving quickly the Schema you are looking for.

4) Select the objects you want to import

5) Click on Finish

If everything goes ok, you have your Model imported in Oracle Data Modeler and you can export images of your diagrams or generate your DDL code to attach to your documentation.  

Oracle Tip: Find locked objects in Oracle Database

Tipically I end up locking objects by executing an Oracle Warehouse Builder (OWB) mapping and then stopping it because I realize I have forgotten a join condition. If you abruptly kill the session, the object may stay locked until the rollback is completed or endlessy if the session is killed before any action against the target table is taken.
In order to find out which object has been locked (and beg your DBA to kill the associated session or free the locks), run the following query:

 SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#,
                 b.status, b.osuser, b.machine
 FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

The output will look like this:

In case you are the DBA (and you have DBA privileges), then get the SID and the SERIAL# and run the following:

 alter system kill session 'SID,SERIAL#';