Tuesday, April 15, 2008

Can we install DAC client and DAC Server without installing Analytics application?

Sometime customer do not want to install the 7.9.x application but just need the DAC client or DAC Server. In order to setup the DAC client or DAC Server, you do not have to run the Analytics application installer. That is the beauty of DAC. If you have the DAC client or DAC Server setup on one machine, just zipped up the DAC folder and then unzip it on the target machine. After the unzip, if the directory structure is different on source and target, modify the DAC config files config.bat/config.sh and dac_env.bat/dac_env.sh and update the path.

Since DAC client/DAC server a JAVA based application and platform independent, there is no separate DAC server installer available on Unix platform. You need to setup the DAC on the Windows machine and the move the DAC folder to Unix machine in order to run the DAC Server on Unix. After you move the DAC folder to Unix, please make sure to use dos2unix utility on all the .sh files to remove the carriage return/line feed characters. If you do not run this utility and try to source the environement variables using the .sh file, it will not be set correctly.

DAC Task synchronize results in Error

When you try to synchronize the task in DAC, it might results in the following error.

MESSAGE:::Failed to create folder/workflow/session cache fileEXCEPTION CLASS::: com.siebel.analytics.etl.infa.interaction.PmrepUtilsException
com.siebel.analytics.etl.infa.interaction.PmrepUtils.exportListWorkflow(PmrepUtils.java:659)com.siebel.analytics.etl.infa.fileParsing.TaskSync.sync(TaskSync.java:147)com.siebel.analytics.etl.client.action.TaskSynchronizationAction.doOperation(TaskSynchronizationAction.java:123)com.siebel.etl.gui.view.dialogs.WaitDialog.doOperation(WaitDialog.java:53)com.siebel.etl.gui.view.dialogs.WaitDialog$WorkerThread.run(WaitDialog.java:85)
::: CAUSE :::EXCEPTION CLASS::: java.lang.NullPointerException
com.siebel.analytics.etl.infa.interaction.PmrepUtils.getRelatedObjects(PmrepUtils.java:520)com.siebel.analytics.etl.infa.interaction.PmrepUtils.getWorkflowObjects(PmrepUtils.java:490)com.siebel.analytics.etl.infa.interaction.PmrepUtils.readSessions(PmrepUtils.java:447)com.siebel.analytics.etl.infa.interaction.PmrepUtils.exportListWorkflow(PmrepUtils.java:650)com.siebel.analytics.etl.infa.fileParsing.TaskSync.sync(TaskSync.java:147)com.siebel.analytics.etl.client.action.TaskSynchronizationAction.doOperation(TaskSynchronizationAction.java:123)com.siebel.etl.gui.view.dialogs.WaitDialog.doOperation(WaitDialog.java:53)com.siebel.etl.gui.view.dialogs.WaitDialog$WorkerThread.run(WaitDialog.java:85)

This might be because of the DAC client Windows machine setup. DAC client will use the pmrep.exe utility to connect to the Informatica repository server but if the PATH environment variable is not pointing to the Informatica client installation on the Windows machine, DAC task synchronize will results in the above error.

Please add the Informatica client path to PATH environment variable, and re-start the DAC client and then try to synchronize the task again.

Saturday, April 12, 2008

DBG_21045 ERROR: Can't create files in the directory

While running the ETL, you may encounter the following error

TRANSF_1_1_1> PMF_15004 Unable to open file.
PMF_15004 [/oraclebi/infrmatica/714/server/Cache/PMLKUP9442_262173.dat]
TRANSF_1_1_1> DBG_21045 ERROR: Can't create files in the directory "/oraclebi/infrmatica/714/server/Cache"

TRANSF_1_1_1> DBG_21052 Check file system permission and free space for possible failure

This error usually occur when the Informatica try to create a huge Cache file but no space available on the mount point. Check the space available using the df -h command when this error occur and add more disk space to the mount point in question.

ORA-00600: internal error code, arguments: [kxfqupp_bad_cvl]

When running the ETL job, you may encounter the following error

Failed : CREATE BITMAP INDEX
W_SLS_CC_LN_F_F34
ON
W_SALES_CYCLE_LINE_F
(
PROFIT_CENTER_WID ASC
)
NOLOGGING PARALLEL TABLESPACE OLAP_REP_INDEX With error message : java.sql.SQLException: ORA-00600: internal error code, arguments: [kxfqupp_bad_cvl], [8863], [6], [0], [], [], [], []

After 10 attempts.

This error will occur when creating index with parallel option on huge tables. If the table size is small to medium, this error does not error while creating the index with parallel option.

There are two ways to resolve this issue. Either create the index serially, which might take lot of time or fix the Oracle DB. There is a bug in the Oracle DB (to be more specific 10.2.0.3) and a patch is available for this. Apply the patch 4695511 to fix this issue.

DAC Schedule impacted by Daylight Saving time (DST)

DAC Schedule may be impacted by the Daylight Saving Time (DST) if the JRE used is not DST patched. DAC Server uses the JRE and if the JRE used does not have the DST fix, DAC schedule will be impacted when the time change.

You need to check if the JRE used has the DST fix. Sun Microsystem provide the Timezone updater tool (tzupdater.jar) to update the Timezone data in JDK/JRE installed on the Server. More information regarding the Timezone updater tool can be found at http://java.sun.com/javase/tzupdater_README.html. Download the Timezone updater from http://java.sun.com/javase/downloads/index.jsp#timezone and run it to update the timezone data.

The usage of the TZUpdater tool is as follows

java -jar tzupdater.jar options

Where options are

-t - Run verification test. Use this option to test if the JDK/JRE is up to date with the Timezone data

-u - Update the timezone data. If the verification failed when option -t used, use -u to update the JDK/JRE.

Start the DAC Server on Unix in background

There is startserver.sh script available in /DAC folder to start the DAC Server process. But if you just start the DAC Server with startserver.sh script, DAC Server process will die after the user who start it, close the SSH session. You need to start the DAC Server process using nohup so that SIGHUP signal can be ignored and start it in the background.

$pwd
/oraclebi/product/OracleBI/DAC
$nohup ./startserver.sh >> dacserver.log 2>&1 &

Change Ports for SAW, SAS and Scheduler component

Sometime there is requirement to change the default ports for Oracle Analytics Web server, Analytics Server and Scheduler component. Some company even has a security policy not to use the default ports for the application.

In order to change the default port for Oracle Analytics Server (SAS), open the NQSConfig.INI file from /oraclebi/product/OracleBI/server/Config in vi or any other editor. Change the value of RPC_SERVICE_OR_PORT parameter. Default value is 9703. For example

RPC_SERVICE_OR_PORT = 10073;

Once you change the SAS Server port, make sure to modify the AnalyticWeb ODBC connection in odbc.ini file in /oraclebi/product/OracleBI/setup folder. This ODBC connection is used by SAW Server so it also need to be changed to reflect the correct port.

[AnalyticsWeb]
Driver=/oraclebi/product/OracleBI/server/Bin/libnqsodbc.so
Description=Oracle BI Server
ServerMachine=local
Repository=
Catalog=
UID=
PWD=
Port=10073


In order to change the default port for Oracle Analytics Web Server (SAW), open the instanceconfig.xml file from /oraclebi/product/OracleBIData/web/config in vi or any other editor. Add the name-value pair Listener, attribute port within ServerInstance tag. Default value is 9710. For example

<Listener port="10076"/>

Once you change the SAW Server port, also make sure to change the configuration file of SAW Plug-in. SAW plug-in need to know where the SAW Server is running. In case of Oracle Application J2EE Server, update the web.xml file of the Analytics application in the ias/product/asmt_10131/j2ee/bianalytics/applications/analytics/analytics/WEB-INF folder.

<servlet>
<servlet-name>SAWBridge</servlet-name>
<servlet-class>com.siebel.analytics.web.SAWBridge</servlet-class>
<init-param>
<param-name>oracle.bi.presentation.sawserver.Host</param-name>
<param-value>localhost</param-value>
</init-param>
<init-param>
<param-name>oracle.bi.presentation.sawserver.Port</param-name>
<param-value>10076</param-value>
</init-param>
</servlet>


In order to change the default port for Oracle Analytics Scheduler component, you need to configure the Scheduler configuration using schconfig utility on Unix platform or via Job Manager on Windows. Launch the schconfig utility

$pwd
/oraclebi/product/OracleBI/setup
$. ./user.sh
$. ./sa-init.sh
$schconfig

Select Option 1- Configure Scheduler and then Option 2-General. Change the port number in option 4. Default port is 9705.

Friday, April 11, 2008

Setup OBIEE Scheduler on Linux

Several of my friends ask me how to setup the OBIEE scheduler on Linux. Here are the detailed steps of setting up the OBIEE scheduler on Linux in 10.1.3.3.2. Please note that this steps may be different in earlier version of Analytics (7.8.4) as introduction of cryptotools.

These are the steps to setup the Scheduler



  1. Setup the DB schema for Scheduler that is used to store the runtime data


  2. Configure the scheduler on Linux


  3. Configure the presentation service in order for it to connect to Scheduler



  4. Start the Scheduler Service



1. Setup the DB schema for Scheduler that is used to store the runtime data


The very first step in setting up the scheduler is to setup the DB schema for scheduler. The DB tables are in the SQL script
$INSTALLDIR/OracleBI/server/Schema/SAJOBS.Oracle.sql. Please select the DB platform specific .sql file. This blog post assume Oracle Database as your DB platform.



SQL>create user S_NQ_SCHED identified by S_NQ_SCHED;
SQL>grant connect,resource to S_NQ_SCHED;
SQL>alter user S_NQ_SCHED default tablespace OLAP;
SQL>alter user S_NQ_SCHED temporary tablespace TEMP;
SQL>alter user S_NQ_SCHED quota unlimited on OLAP;
SQL>connect S_NQ_SCHED/S_NQ_SCHED;
SQL>@$INSTALLDIR/OracleBI/server/Schema/SAJOBS.Oracle.sql;


This will setup scheduler tables S_NQ_JOB, S_NQ_JOB_PARAM, S_NQ_INSTANCE and S_NQ_ERR_MSG under schema S_NQ_SCHED.



2. Configure the Scheduler on Linux


-bash-3.00$ pwd
/oraclebi/product/OracleBI/setup
-bash-3.00$ . ./user.sh
-bash-3.00$ . ./sa-init.sh
-bash-3.00$ schconfig





Select Option 1 and then Enter



Select Option 1 and then Enter



Select Option 3, Enter Data Source Name. This should match the data source in tnsnames.ora file on the Scheduler Server. Select Option 4, Enter S_NQ_SCHED and Select Option 5, Enter S_NQ_SCHED. Select 0 to Quit and Save the changes.



Select Option 2 - General. Specify the Scheduler Administrator user and password. It can be same as BI Server Administrator user or you can setup a new user in RPD with the Administrator group assigned to it. Select the option 4 if you want to use a different port for scheduler rather then default port of 9705. Select 0 to quit and save the changes. Again select 0 to Quit and come back to the main menu.



Select option 2 to configure the email. Select 1- General to specify the email SMTP Server details. Select option 3 and 4 to specify the SMTP host name and port. On Linux, If the SendMail application running, you can use same host name as SMTP Server and port as 25. If the SendMail or the SMTP Server you are using is configured with Authentication, select Option 8 - Authenticate against the SMTP Server and set it to TRUE and specify the Username and Password that Scheduler will use to authenticate against the SMTP Server. Select 0-Quit to come back to the main menu, and save the changes.



Select Option 3 to configure the scheduler to talk to Presentation server. Select Option 1 - SAW Machine Name if the SAW Server not running on the same host as Scheduler and using a non-default port.

Select 0-Quit to exit from the Scheduler Configuration and Save the changes.



3. Configure the presentation service in order for it to connect to Scheduler


Now you need to configure the Presentation Server to talk to Scheduler Server.



  • Open the instanceconfig.xml file from oraclebi/product/OracleBIData/web/config.

  • Check the Alerts tag and make sure it is pointing to the correct host and port for the Scheduler Server.

<alerts><scheduleserver>myhost.company.com:xxxx</scheduleserver></alerts>



  • Make sure that CrentialStore tag point to the credentialstore.xml file.

<credentialstore><credentialstorage type="file" path="/oraclebi/product/OracleBIData/web/config/credentialstore.xml" passphrase="xxxxx"></credentialstore>



  • Run the cryptotools utility to update the credentialstore.xml file


Bash$ . /oraclebi/product/OracleBI/setup/sysenvinit.sh
Bash$ cd /oraclebi/product/OracleBI/web/bin
Bash$ cryptotools credstore -add –infile /oraclebi/product/OracleBIData/web/config/credentialstore.xml



Credential Alias: admin
Username: Administrator
Password: xxxxxxx
Do you want to encrypt the password? y/n (y): y
Passphrase for encryption: xxxxxx
Do you want to write the passphrase to the xml? y/n (n): y


Please make sure that credentialstore.xml file is updated with the following section at the end of the file.


<sawcs:credential type="usernamePassword" alias="admin"> <sawcs:username>Administrator</sawcs:username> <sawcs:password passphrase="xxxxxx"> <xenc:encrypteddata> <xenc:encryptionmethod algorithm=" href=">http://www.rsasecurity.com/rsalabs/pkcs/schemas/pkcs-5#pbes2"> <pkcs-5:pbes2-params algorithm=" href="http://www.rsasecurity.com/rsalabs/pkcs/schemas/pkcs-5#pbkdf2"> <pkcs-5:keyderivationfunc> <pkcs-5:parameters> <pkcs-5:iterationcount>1024</pkcs-5:IterationCount> </pkcs-5:Parameters> </pkcs-5:KeyDerivationFunc> <pkcs-5:encryptionscheme algorithm=" href=">http://www.w3.org/2001/04/xmlenc#tripledes-cbc"/> </pkcs-5:PBES2-params> </xenc:EncryptionMethod> <xenc:cipherdata> <xenc:ciphervalue>3xUah7l969vR8NepkxxN4Q</xenc:CipherValue> </xenc:CipherData> </xenc:EncryptedData> </sawcs:password> </sawcs:credential>


passpharase should match with what you have specified while running the cryptotools utility. Please note that Credential Alias should be "admin" in order for Presentation Server talk to Scheduler. If you specify any other alias, presentation server will not be able to authenticate.


4. Start the Scheduler Service


Start the OBIEE Scheduler Component and also re-start the SAW Server if it is already running. If you change instanceconfig.xml file, always re-start the SAW server.



bash$ cd /oraclebi/product/OracleBI/setup
bash$ . ./user.sh
bash$ . ./sa-init.sh
bash$ ./run-sch.sh start


Please make sure Scheduler process started up.


bash-3.00$ ps -fe|grep sch
13140 1 0 Mar24 ? 00:00:22 /oraclebi/product/OracleBI/server/Bin/nqscheduler
bash-3.00$


You can also check the port of the scheduler to make sure it is running.


bash-3.00$ netstat -an|grep 9705
tcp 0 0 0.0.0.0:9705 0.0.0.0:* LISTEN
bash-3.00$