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?
Learn how to deploy an AI Data Platform agent, register it to the AI Registry, and introduce it to business users through Agent Hub. This session covers how to prepare an agent for deployment, configure endpoint access and authentication, invoke a deployed agent, and monitor it after deployment. The session also introduces…
Can you please help resolve this error. WebLogic version is 12c. OAS is 6.4
So I have an action passing a value to another workbook. On the other workbook, I have an expression filter and a parameter I created and it does not seem to work. Any ideas. See the images
We know you can directly export workbooks and folders from within OAC, and we know you can migrate OAC content using the FDI Content bundle. However, it's unclear what settings or steps are required in order to ensure any calculations in "Shared Objects" still carry over. When making a .dva artifact export from OAC for…
The user icon in the upper-right corner of OAC (DV) is displaying more menu options for our non-admin users than we wish to display. We would like to restrict these to only "Profile" and "Sign Out" for regular users. Is there a way to customize these menu items? Especially if we can control this via security, that would be…
What is wrong with this code
