In last blog I have tried to explain various modeling options by which we can implement the Outer Join can be implemented for OBIEE 11G, in addition I have written about how to achieve data densification in successive blogs.
In OBIEE 11g always suppress null at the analysis level. With the latest Release 184.108.40.206 of OBIEE 11g offers an option for table or pivot table view called as ‘Null Values’. This option is very useful instead of putting the outer join and provides data densification without any repository configuration.
Options Available for Null Values for Table and Pivot
Null Values check box is used to override null suppression set at the analysis level. The following two options one for row and columns for table and pivot are as follows.
Include rows with only Null Values — this check box is used to include null values in a table or pivot table view when the entire row contains all nulls. When selected, null values will not be suppressed for rows for the table or pivot table.
Include columns with only Null Values — this check box is available only for pivot table views. This check box offers to include null values in pivot table view when the entire column contains all nulls. When selected this option, null values will not be suppressed for columns in the pivot table.
Views without selecting Null Option
I have designed a simple report with two dimensions (Year and Product Brand) and two measures (Revenue and Billed Quantity).
A table and pivot view based on analysis out is shown below.
Table View with Null Values Not Suppressed
Parameter setting for table view and table output is shown below. The report now includes all rows with null values.
Pivot View with Null Values Not Suppressed
Parameter setting for pivot view and pivot output is shown below. The report now includes all rows with null values.
The option made the outer join and data densification simpler, easy to implement. I hope you find this option useful.