ODI – view FDMEE Scheduled tasks


Follow the below steps to view FDMEE tasks.

1.) Open ODI Studio and click Connect To Repository.
2.) From the login screen, click the green Plus sign.
3.) Enter a name for the Login (can be anything).
4.) ODI Connection User is SUPERVISOR.
5.) Enter ODI Connection Password.
6.) Master Repository User is the FDMEE database username.
7.) Choose Microsoft SQL Server Datadirect Driver from the Drive List dropdown.
8.) Enter the URL: jdbc:weblogic:sqlserver://<db host>;databaseName=<database name>.
9.) Select Work Repository radio button and click the magnifying icon, then select desired FDMEE repository.
10.) Click the Test button (if you do not see “Successful Connection” the information provided is incorrect). Click OK, and then OK one more time.
11.) The log in screen should be visible again at this point. Select the Login Name created and press OK to log in to your FDMEE Work Repository.
12.) Click on the Operator tab in ODI Studio.
13.) Expand the Scheduling selection in the Operator tab.
14.) Expand the All Schedules selection to view scheduled tasks in ODI.
15.) Expand Scheduling under your scheduled batch.
16.) Right click OracleDIAgent and click Open.
17.) This screen will show the scheduled times/dates of which it should run.

Thanks,

~KKT~

How To Clear The Cache For ODI Studio


It is necessary to purge Journal/Operator entries which are older than ten days.

Steps to implement the solution:

  1. First define an ODI Variable to retrieve the targeted date in Java format:
    1. Create a Project (or Global) Variable V, of Alphanumeric datatype.
    2. On the Variable Refresh tab, select a database schema, and run the SELECT command to assign a value.

      For example:

      • On Oracle schema, use SQL command:
        SELECT to_char(sysdate-10, ‘YYYY/MM/DD HH:MI:SS’) FROM dual
        SELECT to_char(sysdate-10, ‘YYYY/MM/DD HH24:MI:SS’) FROM dual /* format to include the 24 hours */

        To get the complete list of Oracle format models which may be used for Date/Time conversions, see:

        http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#i34924

      • On Microsoft SQLServer schema, use SQL command:
        SELECT convert (char(11), getdate()-10, 111) + convert (char(8), getdate()-10, 8)

        To get the complete list of Microsoft SQLServer format models which may be used for Date/Time conversions, see:

        http://msdn.microsoft.com/en-us/library/ms187928.aspx

      The statements above must return the date in “yyyy/MM/dd/ hh:mm:ss” format, elsewhere either:

      – An error message would be signaled, as indicated in Note 423654.1 – “Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff” Message When Using The ODI OdiPurgeLog Tool, or

      -The OdiPurgeLog would treat it as a wrong date (for example, “2013/25/05”), but instead of giving an error, it would delete all the Sessions regardless of the -TODATE parameter. Note an easy way for getting the dates in the right format without using “convert”, “to_date”, etc conversion functions would be to force the language of ODI GUI / Agent to English, as explained in Note 1317507.1 – How To Define Java Options (Such As The Limits Of Memory Heap, The Location Of Non-Java Libraries, etc) In ODI

  2. Next, purge the ODI log using the above Variable, and the OdiPurgeLog tool.

    For this, create a two steps Package:

    1. Drag the Variable into the Package, and set the step to “Refresh Variable” type.
    2. Click on OdiPurgeLog icon to add this step into the Package.

      Change “End Date” to #<Project Code>.Variable_name (where <Project Code> is the code of Project if the Variable has been defined in a Project, or GLOBAL if the Variable has been defined as Global).

    The above will generate the OdiPurgeLog command, with -TODATE property set to the Variable value:

    OdiPurgeLog “-TODATE=#<Project Code>.V”

    If it is necessary to delete Session Logs as well as Scenario Logs, add the PURGE_REPORTS parameter to the tool:

    OdiPurgeLog “-TODATE=#<Project Code>.V” “-PURGE_REPORTS=1”

    If this is not set then only Session execution logs will be purged.

    We strongly recommend you always set “-PURGE_REPORT=1” to delete the Scenario reports along with the Sessions, and therefor avoid the accumulation of **orphan** records in the SNP_STEP_REPORT table.
  3. To automatize the purging of Logs, you can generate a Scenario from above specific Procedure, and schedule for regular execution on the desired ODI Agent.
  4. It is recommended that you regularly use the OdiExportLog tool to backup your logs to a zip file before using the OdiPurgeLog to remove them from the ODI Repository.

Thanks,

~KKT~

How To Encrypt a Password Using ODI


Every major version of ODI – 12.2, 12.1.3, 12.1.2, 11g, 10g – uses a different algorithm to encrypt passwords, and there is no compatibility whatsoever between the respective algorithms.

Hence passwords must be encoded using the encryption function provided with the version of ODI in which password will be used.

For example:
– Do not use ODI 12.2 encoding for a password which will be used in ODI 12.1.3 (and older).
– Do not use ODI 11g encoding for passwords used in ODI 12c.
– etc

Encrypt the password for ODI 12.2 and 12.1.3

The script for encoding passwords is called encode.cmd (.sh) and is found in the standalone/colocated Agent Domain “bin” folder.

  1. At the command prompt, navigate to your ODI <DOMAIN_HOME>/bin
  2. Set the JAVA_HOME environment variable pointing to your JDK home.
  3. Issue the encode command per below syntax:
    encode.cmd(sh) “-INSTANCE=<instance_name>” <password>

    where “-INSTANCE=<instance_name>” refers to your ODI Agent name (for example, OracleDIAgent1)

    If you have multiple Agent definitions, to get a correct value of -INSTANCE:
    (a). Start ODI Studio. navigate to the Topology, and check the name(s) of Physical Agent(s).
    (b). Under ODI <DOMAIN_HOME>/config/fmwconfig/components/ODI folder, see if a sub-directory with same name exists.
    (c). Then set -INSTANCE to that name.

ODI1213_encode

ODI 12.2 and 12.1.3 encryption requires Master Repository connection to perform encryption.

The encryption can be done in either:

  • ODI Studio directly (ODI Studio has ability to encrypt strings and does so as part of ODI Tools password parameter encryption)… or
  • Using the encode.cmd|sh script available once ODI standalone or colocated Agent is configured and deployed in a Domain. Please consult Note 1984054.1 for details.

    Note the Agent configuration provides the necessary connectivity to ODI Repository for the encode.sh and other tools to use.

There exists encode.sh script in ODI installation under <OH>/odi/agent/internal/bin. Scripts here are not executable directly. They are used in turn by the scripts laid down in a agent Domain. You need to use the scripts created in the ODI standalone/colocated agent bin directory to perform actions that need ODI Repository connectivity.

Encrypt the password for ODI 12.1.2

The script for encoding passwords is called encode.cmd (.sh) and is found in the standalone Agent “odi/agent/bin” folder.

  1. At the command prompt, navigate to your ODI <ORACLE_HOME>/odi/agent/bin
  2. Set the JAVA_HOME environment variable pointing to your JDK home.
  3. Set a variable ODI_HOME to your ODI <ORACLE_HOME>\odi\agent.
    The following error will be raised if ODI_HOME is not set:

    The system cannot find the path specified.
    Exception in thread “main” java.lang.NoClassDefFoundError: oracle/odi/Encode
    Caused by: java.lang.ClassNotFoundException: oracle.odi.Encode
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
    Could not find the main class: oracle.odi.Encode.  Program will exit.
  4. Issue the encode command per below syntax:
    encode.cmd(sh) <password>

ODI1212_encode

Encrypt the password for ODI 11g

The script for encoding passwords is called encode.bat(cmd|sh) and is found in the standalone Agent “oracledi/agent/bin” folder.

  1. At the command prompt, navigate to your ODI <ORACLE_HOME>/oracledi/agent/bin
  2. Set the JAVA_HOME environment variable pointing to your JDK home.
  3. Set a variable ODI_HOME to your ODI Agent home folder.

    For example (on Windows):

    set ODI_HOME=C:\Oracle\ODI12C\odi\agent
  4. Issue the encode command per below syntax:
    encode.bat(cmd|sh) <password>

    For example on Windows:

    encode myString

    For example on Unix:

    ./encode.sh myString

    where myString is the string used for the password to be encrypted.

    The result of this operation is the encrypted string.

    For information, the “encode.cmd(.bat)(.sh)” command script refers to to the “oracle.odi.Encode” method, and requires the following Java libraries from the Agent “lib” directory:

    • commons-lang-2.2.jar
    • odi-core.jar
    • odi-standalone-agent.jar
    • spring-core.jar
ODI 11g has new mechanism and script to encode the passwords, the old values encoded using the agent encode script in ODI 10g will not work with ODI 11g and onwards.

Therefore, ODI 10g users, even if the passwords have not changed, make sure to re-encode the passwords using the encode script and enter the new value in the Agent’s odiparams file.

Otherwise, you may run into error messages described in Note 563284.1 “Execution Of ODI Scenario With “startscen” Command Does Not Start Due To IndexOutOfBoundsException Or NullPointerException Or NoClassDefFoundError Or ODI-1218 Or ODI-1414 Or ODI-1418 Messages”.

Encrypt the password for ODI 10g and legacy Sunopsis

Launch the Oracle Data Integrator (ODI) Agent with the encode parameter.

For example, open a command window, set your cursor to the ‘/oracledi/bin’ (‘/sunopsis/bin’) folder and run the following command:

  • On Microsoft Windows operating systems

    agent.bat encode myString
  • On Unix-like operating systems

    ./agent.sh encode myString

where myString is the password to be encrypted.

The result of this operation is the encrypted string.

In all versions of ODI, even if the target password is null (an empty string), you are obliged to encrypt it, by using the appropriate command and an empty (blank) myString value.

Thanks,

~KKT~

CALC ALL, CALC DIM and AGG in Essbase


AGG :
It consolidates the database values ignoring all member formulas.
It works on the sparse dimensions.
It is faster than CALC for less than 6 consolidation levels.

Syntax: AGG (Dimension list);

CALC ALL:
It calculates and aggregates entire outline database based on Outline.
Order of the calculation depends upon the characteristics of the dimensions in the outline.

Syntax: CALC ALL;

CALC DIM:
It calculates and aggregate values for all members in the specified dimensions.
Order of the calculation based on the Sparse and Dense types.
Ex : CALC DIM(dense1,sparse2,dense3,sparse4) then the order of the calculation is dense1,dense3,sparse2,sparse4.
If at all you want to specify your own order use separate function for each dimension.
CALC DIM(dense1);
CALC DIM(sparse2);
CALC DIM(dense3);
CALC DIM(sparse4);

Syntax : CALC DIM(dimension);

Essbase – Maximum number of rows processed [250000] exceeded […] – ver 11.1.2.4


Many times we face this issue while connecting to Smartview and we get error maximum number of row exceeded. The reason being

SSPROCROWLIMIT is not working correctly in Essbase Server 11.1.2.4.000

In Essbase 11.1.2.4.000, this setting does not work for values > 500,000.

Essbase always returns the error “Maximum number of rows processed [250000] exceeded [……]”.

If the value of SSPROCROWLIMIT is > 500000, Essbase seems to go back to the default of 250000.

The documentation for 11.1.2.4 says that SSPROCROWLIMIT still can be set to > 500000, although it is discouraged for performance reasons.

The issue has been addressed in Essbase 11.1..24.004 PSU as can be seen in Patch Readme:

FIXED DEFECT LIST OF ESSBASE 11.1.2.4.004PSU
21764969 MERGE PATCH FOR BUG 21556292

Action Plan :
=========

  • Apply Essbase Server Patch 11.1.2.4.004 PSU or higher to fix this issue.

Find the latest available patch set for Essbase via KM Doc ID 1396084.1

Thanks,

~KKT~

Essbase Kill a ghost ESSSVR Process


Sometime we can see some of the sessions in Terminating state and even if we try to kill from EAS console or from MaxL we can not get rid of them or some of the process that are running in backend and we can’t see those in EAS console. so How to kill a runaway or ghost ESSSVR process.

To kill a runaway or ghost ESSSVR process:

Windows:

1. CTL/ALT/DEL and select Task Manager
2. In the processes tab, select the ESSSVR process and do an “End Process”. If you have multiple ESSSVR processes, you can search through the essbase.log file for the process ID that started the application.

UNIX:
1. From the UNIX prompt, type: ps -ef | grep ESSSVR
2. Find the runaway/ghost ESSSVR process ID and use the kill -15 command to kill that pid. If after a few minutes the ESSSVR process does not stop, you will have to use the kill -9 command.

NOTE:  The -9 argument to kill should never be used on Unix systems, except as a very last resort. The KILL signal does not allow a process to run any cleanup code, which means blasting away with kill -9 may leave child processes of a parent orphaned, temporary files open, shared memory segments active, and sockets busy. This leaves the system in a messy state, and could lead to unanticipated and hard to debug problems.

Properly written programs will respond to a -15 by cleaning up anything they need to do before dying. Understand that this is not a matter of priorities, or the system giving more time with a -15; it’s simply that a program can catch the -15, do what it needs to do and then voluntarily exit. It could also choose to ignore the -15 all together. If it hasn’t made any provisions at all, the -15 works exactly like the -9: the process dies immediately.

A “kill -9” just causes the process to die; it gets no chance to do any cleanup. Therefore, if you don’t know how a program was written, you should try the -15 first, in case it does need to clean up files, flush logs or whatever. If the -15 doesn’t work, then use the -9.

When using a kill -9 if processes do become orphaned they can attach themselves to the init process.

Hyperion Planning Application Owner – View or Change


How can we determine which user is the owner of a Planning application? How can we change which user owns the application?

Only one user can be the owner of a Planning application. That user has all the ordinary rights of an administrator user, plus the additional rights to delete the application.

The user who created the application originally (or deployed it from EPMA originally) is the initial owner. Only that user can hand over ownership to another administrator user. To hand over the ownership (assuming you know who the owner is):

  1. Log into the Planning application with the application owner
  2. Administration > Application > Settings
  3. At the bottom of this page is a menu that allows the owner to hand over the ownership to any other provisioned administrator for this application. Only the owner can see this menu.

If you do not know who the owner is, the easiest way to determine this is by looking in the Planning application database schema. You can also change ownership directly in the tables, although we recommend that it is changed via the user interface, as above.

  1. Before making any manual changes to the relational repository please ensure that you have a working backup of the database.
  2. Look at the contents of the HSP_USERS table. The owner has a ROLE value of 3. Ordinary administrators have a ROLE of 0.
  3. Make a note of the USER_ID of the user with a ROLE of 3.
  4. Look in the HSP_OBJECT table for a record with an OBJECT_ID that matches the USER_ID recorded in the previous step.
  5. This record’s OBJECT_NAME is the user name of your application owner.

If you want to reset ownership you can change the ROLE of an existing administrator to 3, and set the current owner’s role to 0. As above, you will need to look at the HSP_OBJECT table to work out which user corresponds to which USER_ID.

Restart the Planning service after modifying the database in order for the changes to take effect.

Thanks,

~KKT~

Essbase – Location of Jar Files


Trying to connect to Essbase from an application using the Essbase Java API.  Where are the following supporting .jar files located:

import oracle.wsm.security.util.SecurityConstants;
import weblogic.wsee.jws.jaxws.owsm.SecurityPolicyFeature;

and also:

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;

Supporting files are located in:

import oracle.wsm.security.util.SecurityConstants;

<MIDDLEWARE_HOME>/oracle_common/modules/oracle.wsm.common_11.1.1/wsm-secpol.jar

import weblogic.wsee.jws.jaxws.owsm.SecurityPolicyFeature;

<MIDDLEWARE_HOME>/oracle_common/modules/ws.api.jar

The supporting files for:

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.*;
import com.essbase.api.domain.*;
import com.essbase.api.metadata.*;

are found in a location similar to this on a Windows machine:

\Oracle\Middleware\EPMSystem11R1\common\EssbaseJavaAPI\11.1.2.0\lib in the ess_japi.jar file.

Thanks,

~KKT~

OBIEE 12C – uploadrpd and downloadrpd – Log file


Commands to Deploy RPD and Download RPD – Log file location for uploadrpd and downloadrpd command utilities.

How to Deploy Repository in OBIEE 12c?

Repository is uploaded using data-model-cmd script with uploadrpd option

Check Oracle link.

<Oracle_Home>user_projects/domains/<domain name>/bitools/bin/data-model-cmd.sh

./data-model-cmd.sh uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]

Where

I specifies the name of the repository that you want to upload.

W is the repository’s password. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.

SI specifies the name of the service instance.

U specifies a valid user’s name to be used for Oracle BI EE authentication.

P specifies the password corresponding to the user’s name that you specified for U. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.

S specifies the Oracle BI EE host name. Only include this option when you are running the command from a client installation.

N specifies the Oracle BI EE port number. Only include this option when you are running the command from a client installation.

SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when you are running the command from a client installation.

H displays the usage information and exits the command.

For example: ./data-model-cmd.sh uploadrpd -I obiee12samp.rpd -W Admin123 -SI ssi -U weblogic -P Welcome99

Note: Options S, N,SSL are needed when command is executed on client machines. Use -H for help.

 

How to Download Repository in OBIEE 12c?

Repository is downloaded using data-model-cmd script with downloadrpd option

Check Oracle link.

<Oracle_Home>user_projects/domains/<domain name>/bitools/bin/data-model-cmd.sh

 

./data-model-cmd.sh downloadrpd -O RPDname [-W RPDpwd] -SI service_instance -U cred_username [-P cred_password] [-S hostname] [-N port_number] [-SSL] [-H]

Where

O specifies the name of the repository that you want to download.

W specifies the password for the repository. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.

SI specifies the name of the service instance.

U specifies a valid user’s name to be used for Oracle BI EE authentication.

P specifies the password corresponding to the user’s name that you specified for U. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.

S specifies the Oracle BI EE host name. Only include this option when you are running the command from a client installation.

N specifies the Oracle BI EE port number. Only include this option when you are running the command from a client installation.

SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when you are running the command from a client installation.

H displays the usage information and exits the command.

For example: ./data-model-cmd.sh downloadrpd -O obieesamp.rpd -W Admin123 -SI ssi -U weblogic -P Welcome99

Note: Options S, N,SSL are needed when command is executed on client machines. Use -H for help.

What is the Log file name and it’s location for verifying uploadrpd and downloadrpd command utilities?

Check  the file bi-lcm-rest.log.0 located under <Oracle_Home>/user_projects/domains/<domain name>/servers/bi_server1/logs folder

Also check bi-server1-diagnostics.log file under <Oracle_Home>/user_projects/domains/<domain name>/servers/bi_server1/logs folder

Smartview Tab Disappears From Excel


Hi,

I came across this issue many times and address many times to my users how to fix this issue. Just thought of documenting for everyone.

Goal – How to enable the Smart View tab if it disappears from Excel

To fix:

  1. Open Excel as a System Administrator.
  2. Go to Excel Options ->Select Add-ins
  3. Under the Drop Down selection for “Manage” ->Select COM-ADDINS and Click on Go
  4. Check if the “Load Behavior” of the Oracle Smart view for Office is “Load at Startup”.
  5. If the status is unloaded, remove the existing COM Addin andrRe-add the HsAddin.dll from <Drive>:\Oracle\SmartView\bin directory.

Step by step Video for reference. Smartview Excel Fix

Thanks,

~KKT~