Tuesday, August 12, 2008

OBIEE Configuring Configuring the scheduler on Windows2003/Oracle

As with many configurations on OBIEE, it's in the documentation only distributed on many PDF's.
Here is how install the OBIEE scheduler on Windows2003 and an Oracle DB.
Basically you have to go trough 5 steps:
1 Create the DB User and Tables.
2 Check the instanceconfig.xml
3 Add the Scheduler Administrator to the credential store
4 Configure the Job Manager
5 Run A test

1 CREATE THE DB USER AND TABLES.
The table scripts can be found in ..\OracleBI\server\Schema\SAJOBS.Oracle.sql, but you have create the user yourself. Or you can use this script:
/*****************************************************************************/
/* Setup OBIEE scheduler on ORACLE */
/* READ THE SCRIPT BEFORE YOU RUN IT !!!!!!!!!! */
/* Run the script as SYSTEM */
/*****************************************************************************/
/*****************************************************************************/
/* Create the S_NQ_SCHED user */
/* You can change the password NOT the user name */
/*****************************************************************************/
DROP USER S_NQ_SCHED;
CREATE USER S_NQ_SCHED IDENTIFIED BY "S_NQ_SCHED_PASSWORD"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE DEFAULT
QUOTA UNLIMITED ON "USERS";
GRANT "CONNECT" TO S_NQ_SCHED;
GRANT "RESOURCE" TO S_NQ_SCHED;
GRANT CREATE SESSION TO S_NQ_SCHED;
GRANT CREATE TABLE TO S_NQ_SCHED;
GRANT CREATE VIEW TO S_NQ_SCHED;
ALTER USER S_NQ_SCHED DEFAULT ROLE NONE;

2 Check the instanceconfig.xml
In ..\OracleBIData\web\config you will find the instanceConfig.xml

Check if the entry between Alerts Tags has a ScheduleServer entry. If you run it on an other port then 9705 add it in the form ServerName:PortNumber (MyServer:1234).

3 Add the Scheduler Administrator to the credential store
Open a command line box:
cryptotools credstore -add -infile e:/OracleBIData/web/config/credentialstore.xml
Credential Alias: admin
Username: SchedulerAdmin
Password: SchedulerAdmin
Do you want to encrypt the password? y/n (y): Y
Passphrase for encryption: secret
Do you want to write the passphrase to the xml? y/n (n): Y
File "OracleBIData_HOME/web/config/credentialstore.xml" exists. Do you want to overwrite it? y/n (y): Y



4 Configure the Job Manager
From the file menu select Configuration Options




On the Database tab enter the connection pool date. Always use the native call interface if possible!.




On the General tab enter the administrator name and credentials
Start the scheduler service


You can find the log in: ..\OracleBI\server\Log\
5 Run A test
Create a simple report and press Save And Schedule
Click on the destinations tab an select Oracle BI Server Cache
Save the IBot.
Open a connection with the jobmanager:



Enter the login credentials


Press the refresh button and have a look at the entry:


This article was original written for the Ciber knowledge Blog: http://knowledge.ciber.nl/weblog/?p=124

Till Next Time

8 comments:

Kala said...

these steps really useful... and very much thankful for nice topics.. :)

Anonymous said...

the steps dont explain about setiing up mail server

John Minkjan said...

@Anonymous

have a look at this:
http://obiee101.blogspot.com/2008/09/setting-up-mailserver-in-vmware.html

Regards John

Bill said...

What is the meaning of the username 'SchedulerAdmin' since I don't have a BI user with this name. When I try to submit a job it fails when I specify the userid as SchedulerAdmin:
[NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43001] Authentication failed for SchedulerAdmin in repository Star: invalid user/password.
Connect open failed

John Minkjan said...

@Bill,

It's just a user to seperate functionality, you can use any user you want.

regards

John

Bill said...

The user "scheduleradmin" needs to exist as a bi user so that bi server can authenticate the username with the username provided by the bi scheduler. I didn't create the "sheduleradmin" user in bi server which is probably what is causing the error.

Bill said...

Hello John,
I followed an article on "setting up obiee delivers/ibots to send alerts" and keep running into email device errors. Using the presentation service: settings => 'my account' and under Delivery Options selected 'Add Email Device'. First error was there was not directory called email under _prefs\devices and second error was 'Object expected: /users/administrator/_prefs/devices/Email'. So I'm not sure which application is supposed to create this email directory and what object is it requesting. I searched on Toolbox for IT and they indicated it may be a permission error so I opened the email folder to "full control" to all user accounts on my PC and still the error persisted. Some direction would be greatly appreciated.

ajay kumar said...

thanks a lot..