Oracle Business Intelligence

Products Banner

Null Values are not displayed in Filter values.

Received Response
411
Views
9
Comments

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.

pastedImage_0.png

Thanks.

Answers

  • 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
    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
    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
    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

  • 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.

    pastedImage_1.png

  • SonPat99
    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.

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

  • Robert Angel
    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
    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:

    pastedImage_0.png

    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?