How to remove default FLOOR function in obiee 12c

I faced a strange issue in obiee report today. Col 1 and Col2 were having some values in each row but doing (Col1/Col2)*100, was returning ZERO for all rows.

I tried to set Column Properties > Data Format to 2 decimal places (shown in pic) but didn’t work.

Then, I applied ROUND on formula to round-off data upto 2 decimal places but again it didn’t work.

Finally, I extracted the physical query (Administration > Manage Sessions > View Log (of your report) ) (shown in pic)

Here i found the reason. OBIEE was applying FLOOR function to the formula where i was dividing an INTEGER by another INTEGER. (shown in pic)

Solution 1:

Use CAST function to convert INT to DOUBLE. and then you can use ROUND need basis. If you don’t want result in decimals then apply ROUND (expr , 0).

Solution 2: Go to Rpd > Physical layer > Database Properties

Find FLOOR and uncheck the box under ‘Value’ column (shown in pic) and then SAVE rpd.

Please note before making any changes take a back-up of rpd. Don’t make changes directly to PROD environment. Try in lower environments first.

I hope this will solve the issue.

Please comment for any doubts/help.

Thanks

Akash Garg

OBIEE12C: Scheduler Service (obisch1) not started/remains shutdown

I was restarting obiee12c through script (stop.sh and start.sh). Except Scheduler Service ([OBISCH] ) did not started and it was in SHUTDOWN state.

1. tried to restart the service from em (enterprise manager) but failed again.
2. Checked the log file nqscheduler.log (path:  /OBIEE_HOME/user_projects/domains/bi/servers/obisch1/logs) and found following error:  [nQSError: 16027] ODBC error state: 28000 code: 1017 message: [Oracle DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01017: invalid username/password; logon denied.. Errors were encountered while initializing Scheduler’s backend database, check database configuration.
3. changed the password for BIPLATFORM schema. to do this goto: em > weblogic domain > Security > Credentials > oracle.bi.system > datasource.biplatform and edit it, save it and try to restart the service. I was successful 🙂

Till next time..

Akash (twitter:akashdgarg)

reference: proficientbi.com/obiee12c-faq-how-to-update-obiee-12c-data-sources-when-repository-schema-passwords-are-changed-in-the-rcu-database/

Edit Presentation Column and table names in OBIEE 12c RPD

By default, in Obiee12c , editing of presentation column names and table names is restricted and you would wonder it was happening till 11g (11…..0.7 version).

It is simple to get back to it.

Go to RPD > Tools > Options > General and

click on check box ‘Edit Presentation Names’ and Done.. 🙂

 

Thanks

Akash

OBIEE 11g Log file names and path

Log files:
OBIEE server log (RPD related, OPMN services start/stop related log):
$OBIEE_HOME$/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqserver.log

OBIEE Query Log (OBIEE report’s query related logs):
$OBIEE_HOME$/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqquery.log

OBIEE Scheduler Log (OBIEE scheduler related logs):
$OBIEE_HOME$/instances/instance1/diagnostics/logs/OracleBISchedulerComponent/coreapplication_obisch1/nqscheduler.log

Managed server log:
$OBIEE_HOME$/user_projects/domains/bifoundation_domain/servers/bi_server1/logs

Admin Server Log:
$OBIEE_HOME$/user_projects/domains/bifoundation_domain/servers/AdminServer/logs

 

Thanks

Akash Garg (twitter: @akashdgarg)

Oracle DB useful queries

To Kill Session:

SELECT ‘alter system kill session ‘||””||B.SID||’,’||
b.serial#||””||’;’,
b.status,
b.osuser,
b.machine from v$session b where B.OSUSER in (‘AK851270’);

To Check Lock on Tables:

select a.session_id,a.oracle_username, a.os_user_name, b.owner “OBJECT OWNER”, b.object_name,b.object_type,a.locked_mode from

(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,

(select OBJECT_ID, OWNER, OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS) B

where a.OBJECT_ID=B.OBJECT_ID;

To check tablespace datafiles/tempfiles:

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 256
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN tablespace FORMAT a18 HEADING ‘Tablespace Name’
COLUMN filename FORMAT a75 HEADING ‘Filename’
COLUMN filesize_in_MB FORMAT 9,999,999,999,999 HEADING ‘File Size’
COLUMN used_in_MB FORMAT 9,999,999,999,999 HEADING ‘Used (in bytes)’
COLUMN pct_used FORMAT 999 HEADING ‘Pct. Used’

BREAK ON report

COMPUTE sum OF filesize ON report
COMPUTE sum OF used ON report
COMPUTE avg OF pct_used ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.file_id file_id
, d.bytes/1024/1024 filesize_in_MB
, NVL((d.bytes – s.bytes), d.bytes)/1024/1024 used_in_MB
, TRUNC(((NVL((d.bytes – s.bytes) , d.bytes)) / d.bytes) * 100) pct_used
FROM
sys.dba_data_files d
, v$datafile v
, ( select file_id, SUM(bytes) bytes
from sys.dba_free_space
GROUP BY file_id) s
WHERE
(s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
UNION
SELECT
d.tablespace_name tablespace
, d.file_name filename
, d.file_id file_id
, d.bytes/1024/1024 filesize
, NVL(t.bytes_cached, 0)/1024/1024 used
, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d
, v$temp_extent_pool t
, v$tempfile v
WHERE
(t.file_id (+)= d.file_id)
AND (d.file_id = v.file#)
/

To create schema:
create tablespace GST_01 datafile ‘path/GST_01.dbf’ size 10G;

create user GST identified by “password” default tablespace GST_01;

grant connect,resource to gst;

To Impersonate in OBIEE:

http://IPAddress:9704/analytics/saw.dll?BIEEHome&startPage=1&NQUser=weblogic&NQPassword=weblogic&Impersonate=abc@sample.com

*IPAddress or Hostname of obiee.

OBIEE11g: Error:[nQSError: 43126] Authentication failed: invalid user/password. error when Logging into Answers/analytics

After restarting all services (Admin server, Node manager, Managed server and OPMN), users were not bale to login to Presentation Services (commonly known as answers/analysis). It was showing invalid user/password error.

Log file:  /u01/APP/OBIEE/instances/instance1/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1/nqserver.log

error: [2017-08-02T21:53:52.000+05:30] [OracleBIServerComponent] [ERROR:]1] [] [] [ecid: 0000LqZjaJG4qmmpsoL6iX1PWVhs000002,0:26:1:6] [tid: eaed6700] oracle.webservices.provider.ProviderException: java.lang.RuntimeException: oracle.bi.security.service.SecurityServiceException: SecurityService::authenticateAccess is denied for the specified credentials/identity.
[2017-08-02T21:53:52.000+05:30] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 0000LqZjaJG4qmmpsoL6iX1PWVhs000002,0:26:1:6] [tid: eaed6700] [nQSError: 43126] Authentication failed: invalid user/password.

Root Cause: The BISystemUser account has become locked.

Solution:

1. Log in to the WebLogic Server Administration Console.
2. Navigate to Security Realms > myrealm > Users and Groups > Users > BISystemUser > Password
3. Reset the password to same as of old password (i.e. you just need to reset to same password) and save.
4. Login to Enterprise Manager.
5. Navigate to WebLogic Domain  and right click on bifoundation_domain.
6. Click on Security > Credentials
7. click on oracle.bi.system and then system.user
8. Then click on edit on top of this table and reset the password same as you set for BISystemUser in WebLogic Server Administration Console.
9. Restart OPMN services and wait (i did so) for 10 minutes before login into Answers.
10. It should work.

 

Thank You

Akash (@akashdgarg).

path to set images for fmap: function in presentation services/Reports/dashboards in obiee

You might need to show custom image with title, logo of company in header or any such manner where you can show images by just using fmap: of OBIEE. with this function you need not to mention absolute path of an image.

But do you wonder how obiee identifies the path with above said function?

by default images need to be placed at following paths (same image at below two paths):

1. $OBIEE_HOME/Oracle_BI1/bifoundation/web/appv2/res/s_blafp/images
2. OBIEE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/s_blafp/images

where $OBIEE_HOME is the obiee home path where obiee is installed.

 

Thanks till next time

Akash (@akashdgarg)

 

OBIEE11g: Managed server services Stuck: java.lang.OutOfMemoryError: GC overhead limit exceeded error

One fine day, if you suddenly face an issue of OBIEE being not working, managed server services in ‘Hang’ or ‘STUCK’ state, try to restart the services but no luck etc. Reason behind all this scenarios could be the following:

java.lang.OutOfMemoryError: GC overhead limit exceeded.

you will find above error in bi_server.log (bi_server is the name of your managed server) at below path:

/$OBIEE_HOME$/user_projects/domains/bifoundation_domain/servers/bi_server1/logs/bi_server1.log.

Error: looks like this:

####<Jun 12, 2017 8:22:37 PM IST> <Error> <HTTP> <etdcrpt02> <bi_server1> 
<[STUCK] ExecuteThread: '11' for queue: 
'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> 
<005KKckpyjA4qmmpsoL6iX0001CH00001^> <1497279157635> <BEA-101017> 
<[ServletContext@1533767336[app:bisecurity module:bisecurity path:
/bisecurity spec-version:2.5 version:11.1.1]] 
Root cause of ServletException.
java.lang.OutOfMemoryError: GC overhead limit exceeded

Cause of error:

 The detail message "GC overhead limit exceeded" indicates that the 
garbage collector is running all the time and Java program is making very 
slow progress. After a garbage collection, if the Java process is spending 
more than approximately 98% of its time doing garbage collection and if it 
is recovering less than 2% of the heap and has been doing so far the last 
5 (compile time constant) consecutive garbage collections, 
then a java.lang.OutOfMemoryError is thrown. This exception is typically 
thrown because the amount of live data barely fits into the Java heap 
having little free space for new allocations.

Solution:

Instant: restart all services (admin server, node manager, managed server and OPMN).

while restarting managed server, first you need to stop/force stop (to force stop use kill -9 PID ) managed server.

Permanent fix: 

Increase the heap size. The java.lang.OutOfMemoryError exception for 
GC Overhead limit exceeded can be turned off with the command line 
flag -XX:-UseGCOverheadLimit.

 

 

 

Thanks till next time

Akash (@akashdgarg)

 

 

‘Catalog’ not visible despite of having access in OBIEE11g.

One of my user raised an issue of ‘Catalog’ menu being not visible on homepage along with other menu items like Dashboard, Open, New etc.

My initial investigation was to check roles, group of the user. He belongs to ‘AuthenticatedUser’ group, no explicit denial of access to catalog folder. He was able to open reports by going an alternative approach(using Open menu). Then why is he not able to see catalog as a menu button on home screen?

Solution: 1. Log in to user’s account (impersonation/ or by user’s credentials), go to my Account, check the Accessibility mode to ‘OFF’ (earlier it was set to ‘ON’).

press ‘OK’ and refresh the page:

Catalog is visible now.

OBIEE Version: 11.1.1.7.141014.

 

Thanks till next time.

Error while exporting/printing large data to PDF/Excel2007 in OBIEE11g

Today, my business user sends me an error screenshot saying he was not able to take a printout of detailed report which had approx. 7k rows. I provided an ‘Export‘ link at the bottom of the report and asked the user to download first and print. But, interestingly, he was able to download in excel 2003 but neither in excel2007 nor pdf. It triggered me to investigate the issue and provide a solution.

Issue: trying to export an analysis result in to pdf or printing to pdf and gives following error:

A fatal error occurred while processing the request. The server responded with: com.siebel.analytics.utils.InputStreamWithLimit$ReadOverTheLimitException.

Solution: edit the config.xml file located at /$OBIEEHOME$/instances/instance1/config/OracleBIJavaHostComponent/coreapplication_obijh1/config.xml

Note: take a backup of config.xml file in case you need to rollback the changes.

and edit the following entries:

in my case value in <InputStreamLimitInKB> was 32768 and i changed it to 0 (zero)

<XMLP>
<InputStreamLimitInKB>0</InputStreamLimitInKB>
<ReadRequestBeforeProcessing>true</ReadRequestBeforeProcessing>
</XMLP>

0 means deactivating the limit.

and restart the services.

But , would you like to limit the maximum input size (in kilobytes) for requests that are sent to the JavaHost service, then increase the parameter’s value until you are able to resolve the issue.

Note: everytime you make the changes in config.xml file, you need to restart the services.

You can also try changing the following tag in same file:

Thanks

AKASH (@akashdgarg)