Null Values are not displayed in Filter values.

User_IP06I

Hello all,

When I'm trying to Filter the Gender values, it's only Showing the values of F and M where as I also have some records which have Null values for gender. How Can I make them available for the users.

Thanks.

Shoeless Joe

Per ANSI SQL spec, nulls are treated differently from actual values, as the NULL "value" is technically undefined. You may be seeing an implication of that.

A dimensional modeling best practice that gets to the heart of this is to make every Fact reference to a Dimension be NOT NULL; Dimension Attributes should be NOT NULL as well, using values like "Not Specified", "Not Available", "Invalid", etc where the source data may be NULL.

SonPat99

Dear User,

You can define a CASE Statement for the "Gender" column. something like below:

CASE GENDER

WHEN 'M' THEN 'MALE'

WHEN 'F' THEN 'FEMALE'

ELSE

'NULL' <or whatever value you want>

END

Robert Angel

The best way of all to handle nulls in OBIEE is to get your ETL developers to turn them into something more useful.

'Unknown Gender' populated by the ETL for the null values will have less of a performance hit and will simplify reporting for everyone.

Thomas Dodds

along those lines ... NO DW dimension should ever have a NULL and NO fact should ever not have a corresponding key join ...

So if Gender dim is

key | code | descr

1 | M | Male
2 | F | Female
3 | U | Unspecified

then always add

-1 | <UNASSIGNED> | Missing <--  used in ETL when you get a null value

-2 | <FUTURE> | Future <-- used in ETL when you want to plan ahead

-3 | <NOTAPPLICABLE> | Not Applicable  <-- used in ETL when you co-locate facts of different grains in the same base table

-4 |<ERROR> | Error  <-- used in ETL when you get a non-conforming value; great for data quality initatives

Now your fact table never is without a key join to every conformed dimension

User_IP06I

Thank You for the response. CASE statement or IFNULL will solve the issue for this scenario. However, this issue isn't just confined to the Gender Column. I've many columns in different tables which have Null Values. So, It's won't be a good idea for me to ask the users to write a case statement when doing self service. That's the reason I'm looking for a way to Display the NULL Option without writing CASE and IFNULL Statements.

SonPat99

In that case, I will recommend you to use the approach suggested by @Thomas Dodds and @Robert Angel

There is no easy-way-out in this scenario.

Also, ideally, if your dim has NULLs, it should come as null.

Also, to filter out NULLs in the report, you should not "Equate" it with NULL, instead you have to use "is not null" as the Operator.

User_IP06I

Is There any alternative work around for now, as it takes time to change the ETL.

Robert Angel

On a temporary work around - it will not be as performant - you can create a calculated physical member based on your original in the rpd and replace your current measure with the calculated equivalent.

SonPat99

There is no simple way around this.

You will have to define a case statement in RPD for all such columns.

Can you give this a try:

You were trying to select values for NULL from the drop-down using "is equal to/is in". Can you please try to evaluate it using "is null" from the Operator drop-down list?