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