Caching NULLS in OBIEE

Question

Hi everyone

We have a requirement where we want to report on all combinations of some dimensions, whether there is data in our fact tables or not.

We initially enabled both the following options in our pivots: Include rows with only Null values  and  Include columns with only Null values.

We have also enabled the Include Null Values options in the OBIEE analyses' criteria section.

This all works fine but response times are slow.

We therefore enabled caching and we can see the queries run off cache. We however noticed that the cache files remain small, even after increasing the size in the NQSconfig file. It seems that OBIEE is not caching the non existent (NULL) values that are being returned to the client. It merges the values returned from the database with the null values in the BIServer. This is taking up a lot of processing time to a point where caching is not a viable solution as there is little to no increase in performance.

We have also tried outer joins to generate the NULL values, but with no success. We also loaded the NULL values in the database but the data volumes are too large.

Do you perhaps know if there is a way to force OBIEE to cache the NULL values generated by outer joins?

Thank you.

Johan

Jerry Casey · Answer

@Johan (South Africa)

An alternative to caching NULLs is to use a union query in which the second leg of the union simply returns all possible combinations of the necessary dimensions, with null or zero in the measures column.

Then, combine the rows in a pivot or add a results column that combines the values.

Joel Acha · Answer

The Cache results are based on actual data from the underlying data source. The "NULLS" don't exist in the data and the BI Server does the manipulation after the data is returned and before it is displayed in your analyses.The only way to get the "NULLS" cached is to store the NULLS as facts in your database.