Calculated Percentage Column always showing result as 0% Zero Percentage in OBIEE 11g

You may also face similar issue while upgrading OBIEE to a newer version or if you upgrade some of your dashboards from OBIEE 10.1.3.4 to 11.1.1.5. The issue is that, some of the calculations under certain columns in the reports were showing zeros instead of the calculated values.  The requirement was to calculate a match rate % for some data based on another field that showed 'MATCH' or 'NO MATCH'. 

 The real-time scenario was

(SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END)/COUNT(Table1.Column1))*100

However, this was returning 0% as the result. With the help of some blogs and questions raised on OBIEE threads I came to know that this happens when you have an integer in a calculation. The solution which I used is very simple and was successful in achieving the requirement. The very easy fix to this issue is to multiply the calculation by 1.0.  The updated calculation which works is shown below and provides a meaningful result.

(don't use 1, as this will not work.  Use 1.0)

((1.0*SUM(CASE Table1.Column1 WHEN 'MATCH' THEN 1 ELSE 0 END))/(1.0*COUNT(Table1.Column1)))*100

OBIEE nQSError: 37005 Transactional Update Failed

This issue was there since version 11.1.1.3.x to Oracle BI Server version 11.1.1.5.x.  It will appear if you edit an OBIEE repository in online mode.The administrator tool for editing the RPD is unable sometimes to acquire an exclusive lock on the objects, that in turn causes the error message.Checking in the changes on a regular basis will reduce the occurrence of this error.

As a temporary solution, the following workaround can be used.

1. After you click on the Check-in button, do Consistency Check.
2. Click ‘Save’ button.It will display the ‘nQSError: 37005 Transactional Update Failed’ error.
3. Click ‘Save’ one more time despite the error.
4. Click the ‘Close’ button.You will get a prompt, Do you want to check in the changes?
5. Select ‘Yes’, you will get the error again.
6. Click the ‘Close’ button again.Then you will get a prompt, Do you want to check in the changes?
7. Select ‘No’.Then the RPD will close.
8. If the RPD says ‘your changes will get saved’ then it means that your changes will be there when you re-open the repository.

To permanently resolve this issue, apply patch number 12909840 to

OBIEE : Unable to Log In : Error Codes: WH4KCFW6:OPR4ONWY:U9IM8TAC

Error connecting to the Oracle BI Server: Could not connect to the Oracle BI Server because it is not running or is inaccessible. Please contact your system administrator.
Error Codes: WH4KCFW6:OPR4ONWY:U9IM8TAC

Common Causes

"Oracle BI Server" windows service is not running. Starting the service could resolve the issue.

In case the BI Server service is running and still the error occurrs, check the port the service is listening to is matching with the port specified in the ODBC DSN for the analytic repository. In NQSConfig.ini file look for RPC_SERVICE_OR_PORT. The default value is 9703. The ODBC DSN name can be found in instanceconfig.xml

• "Oracle BI Server" windows service is not running. Starting the service could resolve the issue.
• In case the BI Server service is running and still the error occurrs, check the port the service is listening to is matching with the port specified in the ODBC DSN for the analytic repository. In NQSConfig.ini file look for RPC_SERVICE_OR_PORT. The default value is 9703. The ODBC DSN name can be found in instanceconfig.xml