How to: Default Date Prompt to NULL in OBIEE Answers

Consider the following scenario:

• You have an accumulating periodic fact table that tracks an item as it moves through its distribution life cycle from warehouse to the point of sale at a store.

If applying Kimball Design Principles (which you should always follow in a denormalized dimensional model), your accumulating periodic fact table would be sparsely populated and contain NULL values for stages the item has not yet encountered. Take the fact table below as an example.

This fact table is what Kimball classifies as a 'periodic accumulating snapshot' because:

1) each record represents the life cycle of 1 item
2) each record is updated as it reaches a key date, as opposed to a new record being added
3) each record has a definite beginning and end

Assume you had a requirement to create a report that identified all of the products that arrived at the store but have yet to be sold.

To translate this technically, you're looking for Sales Dates that are NULL.

You cannot simply create a filter that says 'Sales Date <> NULL' because NULL is not a valid DATE type. NULL in DATE is represented as a '01-01-1900'. You could then be tempted to create a filter as 'Sales Date <> '01-01-1900' but that is not a valid DATE either, since by default Dates are stored as DATETIME stamps.

An easy way to exclude Dates that are stored as NULL, is to do it in answers as follows:


Step 1: Create a Dashboard Prompt for your Date that Defaults to SQL Values:

and populate it with:

SELECT

case when 1=0 then Time."Fiscal Date" else Cast(NULL as Date)  END

FROM "ENTER LOGICAL FACT TABLE HERE"

The 'Enter logical fact table here' should be replaced with the name of the logical fact table of that subject area in the BMM.

Step 2: Edit Column Formula to Cast as Date

Date dimensions default to DATETIME in the repository so you will need to cast your column as a Date.

This will generate the following result:

Note that although the default value looks 'blank', a filter of NULL is being applied in the physical query: