OBIEE Skipping Rows when performing binary join

All,

We had a strange issue with OBIEE in one of the reports that we built recently. Hope this solution will help you guys.

The report has two week selections and two scenario selections say forecast and call and based on the selections user makes, the report should display the measures from a fact table and the measure variance between the two selections. Also there is a column selector by which user can view the data by Region, Sub Region, Country, Key Account.

When the data is being viewed by Region, everything looks fine. The problem occurs when the data is being viewed by Country. OBIEE deliberately skips one of the rows in the tabular view result set. But the grand total value includes the measure value for the skipped row. Debugging further lead us to a strange path.

OBIEE has written one logical query and 3 physical queries to retrieve the result set from the DB and performing a binary join of that data before displaying on the tabular view. One of the physical queries was only to calculate the grand total. Before performing the binary join, OBIEE sorts the data retrieved from the two physical queries. After the sort has been done one of the rows was skipped because of the case mismatch.

Solution:

There is a parameter in the NQSConfig.ini file to set the case sensitive comparison on/off. In our file, the property was set to OFF hence while sorting and joining the results from two physical queries, one of the rows was skipped. After setting that property to ON, OBIEE started displaying the missing row in the tabular view.

Exact Parameter:

Change CASE_SENSITIVE_CHARACTER_COMPARISON=OFF

to CASE_SENSITIVE_CHARACTER_COMPARISON=ON

Impact:

We need to make sure that the back end database also has this property set to ON if we are changing it in the config file. If we have multiple data sources in the physical layer, then the decision to change this property depends on the usage of each data source. If the back end database has this property OFF and we set it to ON in the config file, this may give wrong results too.

If one of the data sources in the physical layer has this property ON and the other as OFF, then we may need to create multiple RPD files and put them on different instances and set this property to ON/OFF as per the data source requirement.

Hope this helps...!!

Siva Pratap

Increase Maximum Drop Down Values in OBIEE

All,

We will be wondering how to increase the number of values in the drop down type prompt. With the Default Installation of OBIEE - Drop down type prompt will only show first 250 values and shows the message (Too Many Values)

Solution:
We need to add a Parameter in the Instanceconfig.xml

1. Add the <Prompts><MaxDropDownValues>XXXXX</MaxDropDownValues></Prompts> where XXXXX is the number of values.
2. Restart the Presentation Services.


Impact:

Please note that providing huge values like 1000, 10000 will have performance impact to the Dashboard.

Best Regards,
Siva Pratap