OAC CSV download limit

OAC is external PaaS Cloud application from Oracle.  It does allows the customer to change some of the configuration parameters similar to on-premise OBIEE application.

But good thing is that OAC automated provisioning process itself take care of setting the correct configuration parameters based on the shape of the OAC Service instance.  There should not be a need for customer to change those parameters post provisioning.

For example,  CSV download parameter in the instanceconfig.xml are set based on the OAC shape.

Based on the Shape,

OC3M - CSV download limit is 65K

OC4M - CSV download limit is 500K

OC5M - CSV download limit is 1M

These values get set during provisioning and there should not be a need to change it further unless really needed.

OAC have two separate configuration settings for limiting excel and csv downloads,  DefaultRowsDisplayedInDownload and DefaultRowsDisplayedInDownloadCSV respectively.

BICS report execution result in "the user request exceeded the maximum query governing execution time"

Some of the reports in BICS results in following error but same report in on-premise OBIEE run successfully against the same Datawarehouse.

Odbc driver returned an error (SQLExecDirectW).
 Error Details
Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
Please have your System Administrator look at the log for more details on this error. (HY000)
State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS.
Please have your System Administrator look at the log for more details on this error. (HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed:
Please have your System Administrator look at the log for more details on this error. (HY000)
State: HY000. Code: 60009. [nQSError: 60009] The user request exceeded the maximum query governing execution time.
Please have your System Administrator look at the log for more details on this error. (HY000)
Please have your System Administrator look at the log for more details on this error. 

This is due to the Query governor set in BICS.  Given that BICS is a cloud service and in cloud,  user query need to be controlled in order to make sure environment is stable and can scale.  This is done by introducing the query governors.  Query execution timeout is set to 600 seconds and this is hard limit.  Customer cannot request to change this hard limit for their BICS instance so report need to be re-design so that DB query runs within 600 seconds.

This limitation will not be there in OAC since OAC is external PaaS and customer will be able to change the limit as per their own requirement. 

DAC 11.1.1.6.4 intermittent Crash




It has been notice DAC 11.1.1.6.4 Server crash intermittently and it need to be restarted.  Crash is following.




# A fatal error has been detected by the Java Runtime Environment:


# # SIGSEGV (0xb) at pc=0x00007fcf752b21b0, pid=112882, tid=140528723429696
# # JRE version: Java(TM) SE Runtime Environment (7.0_80-b15) (build 1.7.0_80-b15)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.80-b11 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# V [libjvm.so+0x6541b0] jni_GetLongArrayRegion+0xd0
# # Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
# # An error report file with more information is saved as: # /oraclebi/DAC11G/dac/hs_err_pid112882.log
# # If you would like to submit a bug report, please visit:
# http://bugreport.java.com/bugreport/crash.jsp
# ORA-24550: signal received: [si_signo=6] [si_errno=0] [si_code=-6] [si_int=0] [si_ptr=(nil)] [si_addr=0x4e220001b8f2] kpedbg_dmp_stack()+362

Reference:

 

ORA-24550 Signal Received Error From Client Based Application (Doc ID 1284564.1)

 

CAUSE

 

Diagnosability features which are turned on by default on Oracle 11g Release 2 causes the problem.

Parameters are:

DIAG_ADR_ENABLED

DIAG_SIGHANDLER_ENABLED

DIAG_DDE_ENABLED

 

SOLUTION

 

1. Such errors can occur if the "Diagnosability" features are 'ON'.

To turn off the OCI signal handler and re-enable standard Operating System failure processing, place the following parameter setting in sqlnet.ora:

DIAG_SIGHANDLER_ENABLED=FALSE

By default this parameter is set to true.

If you want to see the old behavior you can simply turn off the feature by placing the following parameter setting in your client side sqlnet.ora file under $ORACLE_HOME/network/admin directory.

2. In addition, please turn off other Diagnostics parameters as well by placing the following entries in the client side or server sqlnet.ora file. i.e., from where the application is run.

DIAG_ADR_ENABLED=OFF

DIAG_SIGHANDLER_ENABLED=FALSE

DIAG_DDE_ENABLED=FALSE

 

Note:

Changes to client side SQLNET.ORA requires only application restart.

Where as changes to SQLNET.ORA on Database side requires only restart of the listener.

 

FDM Application Migration - Page Not Found

When you migrate FDM application from one environment to another, one thing you need to remember to use the exactly same UNC path for the application. If the UNC path is different on the Target application, then you may see "Page Not found" error while performing the Import data file operation.

If the source application have UNC \\HostName\Apps\FDMapp, then Target application should be \\Hostname\Apps\FDMapp

Testing Essbase or Planning Connection from Oracle Data Integrator (ODI) results in error

When testing Essbase or Planning Data Server connection from ODI results in "Driver Must be Specificed" error and below is the call stack in the error.

ava.sql.SQLException: Driver must be specified

at com.sunopsis.sql.SnpsConnection.a(SnpsConnection.java)

at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java)

at com.sunopsis.sql.SnpsConnection.testConnection(SnpsConnection.java)

at com.sunopsis.graphical.l.pm.o(pm.java)

at com.sunopsis.graphical.l.pm.r(pm.java)

at com.sunopsis.graphical.l.pm.g(pm.java)

at com.sunopsis.graphical.l.pm.a(pm.java)

at com.sunopsis.graphical.l.pm.a(pm.java)

at com.sunopsis.graphical.l.iz.actionPerformed(iz.java)

at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)

at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.setPressed(Unknown Source)

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)

at java.awt.Component.processMouseEvent(Unknown Source)

at java.awt.Component.processEvent(Unknown Source)

at java.awt.Container.processEvent(Unknown Source)

at java.awt.Component.dispatchEventImpl(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Window.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.EventQueue.dispatchEvent(Unknown Source)

at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

at java.awt.Dialog$1.run(Unknown Source)

at java.awt.Dialog.show(Unknown Source)

at java.awt.Component.show(Unknown Source)

at java.awt.Component.setVisible(Unknown Source)

at com.sunopsis.graphical.l.pm.q(pm.java)

at com.sunopsis.graphical.l.pm.(pm.java)

at com.sunopsis.graphical.frame.b.jh.bx(jh.java)

at com.sunopsis.graphical.frame.bo.w(bo.java)

at com.sunopsis.graphical.frame.bo.d(bo.java)

at com.sunopsis.graphical.frame.w.actionPerformed(w.java)

at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)

at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.setPressed(Unknown Source)

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)

at java.awt.Component.processMouseEvent(Unknown Source)

at java.awt.Component.processEvent(Unknown Source)

at java.awt.Container.processEvent(Unknown Source)

at java.awt.Component.dispatchEventImpl(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Window.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.EventQueue.dispatchEvent(Unknown Source)

at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

at java.awt.EventDispatchThread.run(Unknown Source)


This is the expected error. You can only test the JDBC connection, not Essbase,Planning and HFM connection.

It is ok to ingnore this error and move forward inserting the Physical Schema.

How to setup Hyperion BIPlus with Custom Ports

When you deploy Hyperion application, there may be a need to deploy Hyperion BIPlus where there is firewall between Server and Client. In this case, you cannot deploy BIPlus with Default ports as BIPlus uses dynamic ports and due to Security reasons, you cannot open tons of ports through the Firewall.

In such case, it make more sense to deploy BIPlus with Static Ports, that can be opened on Firewall.

To Deploy Hyperion BIPLus with Static Ports

- Set the RMI Server port in fr_global.properties

RMIPort=

- Set the ports for FR Server in fr_repserver.properties

HRRepSvrPort1=

HRRepSvrPort2=

- Run the Configuration Utility for BIPlus and set the static ports for

GSM Port
Core Service Port
Name Service Port
Service Broker Port
Job Service Port
Event Service Port
Repository Service Port

- Set the Planning ADM RMI Server port in ADM.properties file under \common\ADM\9.3.1\lib

ADM_RMI_PORT=
ADM_RMI_SERVER_PORT=

ADM_RMI_PORT will be same as RMI port in fr_global.properties file.

Informatica 8.1.1 install fail on Windows

Informatica 8.1.1 install on Windows 2003 may results in following error. This error occur when installer try to configure the domain.

STDOUT: Installing the service '"Informatica Services 8.1.1"' on node ''...

Using CURRENT_DIR: D:\Hyperion\Informatica\PowerCenter8.1.1\server\tomcat\bin

Using INFA_HOME: D:\Hyperion\Informatica\PowerCenter8.1.1

The service '"Informatica Services 8.1.1"' has been installed.

The Informatica Services 8.1.1 service is starting.



STDERR: '"D:\Hyperion\Informatica\PowerCenter8.1.1\server\bin\infacmd.bat" getNodeName' is not recognized as an internal or external command,

operable program or batch file.

The Informatica Services 8.1.1 service could not be started.

The service did not report an error.


More help is available by typing NET HELPMSG 3534.

This may occur due to COMSPEC environment variable on Windows Server. If this variable is not pointing to correct CMD.exe executable, this issue may arise. Check this environment variable. It should be c:\windows\system32\cmd.exe.

How do I upgrade Informatica Hyperion Repository from 7.1.4 to 8.1.1

During the Hyperion upgrade from System 9 to Hyperion 11.1.1.1, there may be a need to upgrade the existing Hyperion DIM repository from 7.1.4 to 8.1.1 so that it can be used with Informatica 8.1.1.

Lets assume that you are setting up the new Hyperion 11.1.1.1 environment with DIM 8.1.1. and now you need to use the old Hyperion DIM repository that exists in System 9 environment.

- Export the DIM Schema from system 9 Database and import it into Target 11.1.1.1 environment
- Create a new Repository Service into Target in Admin Console and associate it with imported schema from System 9
- If you try to start the Repository Service, it will not start as the Repository Version is different from that shipped with Informaitca 8.1.1.
- You need to upgrade the Repository first, before starting it. To upgrade the repository, click on Action -> Upgrade Content in the Admin console for the Repository Service. You need to provide the Repository UserId/PWD Administrator/Administrator to initiate the upgrade process. Once the upgrade is successful, you should be able to start the repository.

Note: Before importing the DIM Schema from System 9 environment, DISABLE the Authentication Module. With Authentication Module turned on in the System 9 environment, you will not be able to associate the Repository Service with the DIM repository database imported from System 9 environment. You can disable the Authentication plug-in using the Repository Admin Console.

Importing DIM Repository with Japanese Code Page

When you import a DIM Hyperion repository from Japanese Code Page environment to a new 8.1 ENU environment, you will see the below error.


[[REP_61082] AdminConsole's code page (MS Windows Latin 1 (ANSI), superset of Latin1) is not one-way compatible to repository Hyperion's code page (MS Windows Japanese, superset of Shift-JIS). Failed to connect to repository service [Hyperion].]

This error indicate that Hyperion repository is with Shift-JIS code page, but the Gateway/Node running with Latin 1 code page.

If you need to import and run the Hyperion repository with Shift-JIS code page, you need to set the PM_CODEPAGENAME environment variable in the Target environment to MS932. This environment variable indicate the code page of the Gateway/Node. After this, you can import the Hyperion repository with Shift-JIS code page into 8.1 target environment.

Excel Add-in crash with Excel 2007 on Win XP SP3

We oberverved one issue with Excel Add-in and Excel 2007 on XP SP3.

1. Open Excel and connect to the Essbase server.
2. Do Retrieve
3. Double click on any member. Closes the whole excel.
4. Appears the following message:

" The Microsoft Office Excel found a problem and need be closed. We regret the incovenience"

Issue specific to Windows XP SP3.

Patch 7317818 and 7035861 resolve this issue. The fix is available in 9.3.1.2.6.

Essbase crash on Linux

We noticed several crash of Essbase process (ESSSRVR) on Linux, specially when users are doing restructuring and running calculations. Call Stack in the exception log look like this.


Process Type: Application
Application Name: XXXXX
Database Name: XXXXX
Exception Log File: /essbase/Hyperion/AnalyticServices/app/XXXX/XXXX/log00001.xcp
Current Thread Id: -1499931744
Signal Number: 0x11=Segmentation Violation
Signal Code: 0x-168009176=Unknown



----- Stack Trace -----



[0] /essbase/Hyperion/AnalyticServices/bin/libessutlu.so(adXcptWriteStackInfo+0x3e) [0xf73ad7de]
[1] /essbase/Hyperion/AnalyticServices/bin/libessutlu.so(adXcptCreateRpt+0x224) [0xf73ac8cc]
[2] /essbase/Hyperion/AnalyticServices/bin/libessutlu.so(adXcptHandlerDiag+0x83) [0xf73ac62b]
[3] /essbase/Hyperion/common/JRE/Sun/1.5.0/lib/i386/server/libjvm.so [0xf5e2ae4d]
[4] /essbase/Hyperion/common/JRE/Sun/1.5.0/lib/i386/server/libjvm.so [0xf5e28c7f]
[5] /essbase/Hyperion/common/JRE/Sun/1.5.0/lib/i386/server/libjvm.so(JVM_handle_linux_signal+0x1d8) [0xf5e2b6b8]
[6] /essbase/Hyperion/common/JRE/Sun/1.5.0/lib/i386/server/libjvm.so [0xf5e28ba4]
[7] [0xffffe600]
[8] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(adCalmExec+0x64e) [0xf7bf4a5a]
[9] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(adCalcCSCR+0x5fe) [0xf7bcb4b6]
[10] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(adCalcCSCR+0x2040) [0xf7bccef8]
[11] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(adCalcCSCR+0x6eb) [0xf7bcb5a3]
[12] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(adCalcScrCalc+0xf5) [0xf7bcad01]
[13] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(EpXSvrCalcExec+0x484) [0xf7d987dc]
[14] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so [0xf7d9ce47]
[15] /essbase/Hyperion/AnalyticServices/bin/libesssvrd.so(EpXSvrDispatch+0xf8) [0xf7d98350]

We noticed that the VM for the ESSSRVR process was very high. Even for Sample application, VM was around 1.5G. That pretty high. Since Essbase is 32-bit application, it can only address 2GB of memory. Since restructuring/calc need more memory, chances are that process can go beyond 2GB, hence crash.

We changed one of the parameter in Essbase.cfg file. Set the JvmModuleLocation to blank.

; JvmModuleLocation /essbase/Hyperion/common/JRE/Sun/1.5.0/lib/i386/server/libjvm.so
JvmModuleLocation

After this, ESSSRVR process will not load the JVM, hence the VM for the process will be low. After this change, VM for sample application was around 350M as compare to 1.5G. This will give sufficient room for the process to grow when users are doing restructuring/calc.

After this change, we did not notice the similar crash.

Wish we had Essbase 64-bit on Linux available so that we will not hit this limitation.

BI Publisher Logging fail in 10.1.3.2

In out of the box OBIEE/BI Publisher configuration, logging to BI Publisher fail with default user Administrator/Administrator.

In default configuration, it was observed that the authentication mechanism was set to OBIEE Server, instead of BI Publisher. Configuration file of BI Publisher is /oraclebi/product/OracleBI/xmlp/XMLP/Admin/Configuration. tag in this file determines the authentication mechanism for BI Publisher. Default value is BI_SERVER.

In order to use BI Publisher authentication mechanism, set SECURITY_MODEL tag value to XDO.

Planning Apps not visible in Workspace

When we launched the Workspace and went to Navigate -> Applications, no planning application visible. But when we use the EAS console, we can see all the application under the Essbase and they are running.

It might happen if you have re-started the Essbase server.

Please make sure to re-start the Planning application if you re-started the Essbase for some reasons. If you re-start Essbase, but not planning, you may see some weired behaviors.

Start/Stop Essbase server on Linux

Source the Essbase environment variables.

HYPERION_HOME=/essbase/Hyperion;export HYPERION_HOME
ARBORPATH=/essbase/Hyperion/AnalyticServices;export ARBORPATH
ESSLANG=English_UnitedStates.Latin1@Binary;export ESSLANG
#LD_LIBRARY_PATH=$HYPERION_HOME/common/JRE/Sun/1.5.0/lib/i386/server:$HYPERION_HOME/common/JRE/Sun/1.5.0/lib/i386:$HYPERION_HOME/common/JRE/Sun/1.5.0/lib/i386/server:$HYPERION_HOME/common/JRE/Sun/1.5.0/lib/i386:$HYPERION_HOME/common/ODBC/Merant/5.2/lib:$ARBORPATH/bin:$LD_LIBRARY_PATH
#Use following if your deployment does not require CDF,CDM,Data mining,Single Sign-on or Triggers
LD_LIBRARY_PATH=$HYPERION_HOME/common/JRE/Sun/1.5.0/lib/i386/server:$HYPERION_HOME/common/JRE/Sun/1.5.0/lib/i386:$HYPERION_HOME/common/ODBC/Merant/5.2/lib:$ARBORPATH/bin:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
PATH=$ARBORPATH/bin:$PATH;export PATH
ODBCINI=$HYPERION_HOME/common/ODBC/Merant/5.2/odbc.ini

To start the Essbase server

$./startEssbase.sh -b &

To Stop the Essbase server

$./essmsh -l asadmin
MAXL> alter system shutdown;

PLP_GLRevnGroupAccount_A_Stage_Full Informatica ETL Mapping Slow

In one of the project, we have seen that out of the box mapping PLP_GLRevnGroupAccount_A_Stage_Full in Informaitca was taking really long time. In full ETL, this mapping took almost 30 hours.

After reviewing the Session log, it was found that most of the time was taken in the Reader thread. Since the issue was with the source SQL, the same SQL was run from the SQLPlus and it run in 1 hour only.

After reviewing the informatica configuration, it was found that Informatica server was forced to use some pre-sql to change the Database session settings.

alter session set optimizer_index_cost_adj=1;
alter session set optimizer_index_caching=10;

These session pre-sql change the explain plan for the optimizer and optimizer was not performing well with the Source Query. But it perform well for other mapping.

It was advised to remove these pre-sql at the global level but use at the mapping level if they perform well with these pre-sqls.

DAC and serverSetupPrompt.sh

There is no DAC Server setup on Unix platform. You need to setup the Analytics client on the Windows Machine first and then move the DAC folder to the Unix platform. After the DAC folder is moved and if you try to run any of the .sh script, you may see some wired behavior. For Example, when you try to run the serverSetupPrompt.sh, you may see the following error.

Exception in thread "main" java.lang.NoClassDefFoundError: com/siebel/etl/gui/login/TextModeServerSetup

If you try to run other .sh file, you may notice that environment variables are not setup correctly.

When you move the DAC folder to Unix, you need to make sure to run the dos2unix utility to remove CR+LF characters from all the .sh files. If you do not run dos2unix utility, then none of the .sh file will work on the Unix platform as expected. You can download the dos2unix utility from http://www.download3k.com/Install-Dos2Unix.html

Qserver is already bound to the port

Sometime when you start the DAC Server on Unix, you will notice the error in the DAC Server log.

Qserver is already bound to the port 3141.

It was observed that when the DAC Server was shutdown on the Linux using the stopserver.sh script, QServer JAVA Process was not went down and it was still running.

When you shutdown the DAC Server, you need to make sure that JAVA Process QServer also went down. If not, then you have to kill the JAVA Process before starting the DAC server again.

MESSAGE:::Cannot run program "pmcmd": CreateProcess error=2, The system cannot find the file specified

When you attempt to register the Informatica servers in DAC and click the "Test" button, the test may fail with the below error.

SEVERE Thu Jan 11 18:52:41 EDT 2008

MESSAGE:::Cannot run program "pmcmd": CreateProcess error=2, The system cannot find the file specified

In order to troubleshoot this error, try to run the pmcmd and pmrep command from the command window and see if you can run those. If not, then it means that PATH environment is not set correctly.

ORA-01652: unable to extend temp segment by 32 in tablespace TEMP while running ETL

You may receive the error ORA-01652: unable to extend temp segment by 32 in tablespace TEMP while running the ETL, specially when tables are huge. If the SELECT statement is running against the tables which have 70-80 million rows, and the optimizer is doing the hash join, it will make use of TEMP and hash-area size to build the hash. If the TEMP tablespace is not big enough, then you may receive this error. There is no one value fit all the sizes, but in our case, with 100GB of space for TEMP tablespace resolve this error. You may have to set this to higher value if you have more than 100 million of rows in the tables.

Do not change the explain plan to make use of NESTED LOOP, because initially optimizer will take more space and time to build the hash but once the hash is built, query results will be much faster and it will perform better than NESTED_LOOP.

Client Commands are Currently Not Being Accepted

If you receive the error "Client Commands are Currently Not Being Accepted" while performing some operation in the Essbase Database, it seems that some restructuring is happing for the Database. It is possible that some user has started some Calc script, which is forcing the restructure of database, then you cannot run any commands on the database until restructuring completes.

You can replicate this error with the following sequence of command.

Force the Essbase Database Restructure

MAXL> alter database Sample.Basic force restructure;

And then immediately run some command against the database like ESSCMD GETDBINFO or GETDBSTATS and you will see the followinng error.

localhost:Sample:basic:asadmin[1]->getdbinfo
GetDbInfo:


[Sat May 17 12:37:31 2008]localhost/Sample/basic/asadmin/Error(1013204)
Client Commands are Currently Not Being Accepted

Command Failed. Return Status = 1013204