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.
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.
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
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.
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 | Male2 | F | Female3 | 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.
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.
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.
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?
Hello Oracle Analytics Community, We are investigating a dashboard compatibility issue between Oracle Analytics Server (OAS) 7.6 and OAS 7.0. Some dashboards originally created in Oracle BI 12c can be opened in OAS 7.6 but fail to open after being archived and imported into OAS 7.0. During our investigation, we found the…
Hi all, We’re running into an issue with an on-premises Oracle Analytics Server 2026 integrated with Active Directory and hoping someone has seen this or knows if a patch is available. Environment: Oracle Analytics Server 2026 (on-premises) WebLogic 14.1.2.0.0, JDK 21, Oracle Linux DB-backed catalog (Oracle DB, BIPLATFORM…
We have a requirement Creating a Bar graph using Cost Center Tree hierarchy as prompt/filter. Created a Bar Graph using Cost Center Tree Hierarchy as a prompt. When I'm selecting values through selection steps from Cost Center Tree Hierarchy, the Bar Graph not refreshing as the selected values are not passing to Graph…
I have 2 tabs on the workbook that use the same dataset. I would rather not use 2 different datasets. Can I code an expression filter or something to remove those records?
Can you please help resolve this error. WebLogic version is 12c. OAS is 6.4