Wednesday, October 17, 2012

OBIEE Delivers/Schedulers/iBot Setup and Configuration

Following is the step by step configuration of OBIEE Delivers a.k.a. iBots.

Step 1-  Create iBot/Scheduler repository

Suppose you are connected with Oracle database MYDB with user UMIRZA.
Execute following script. This script will create required scheduler repository.


<<<<<<<<<<< Script starts here >>>>>>>>>>>>>>


CREATE TABLE umirza.S_NQ_ERR_MSG
(
  JOB_ID          NUMBER(10)                    NOT NULL,
  INSTANCE_ID     NUMBER(20)                    NOT NULL,
  RELATIVE_ORDER  NUMBER(10)                    NOT NULL,
  ERROR_MSG_TEXT  VARCHAR2(255 BYTE),
  DELETE_FLG      NUMBER(10)                    NOT NULL
)
/
CREATE TABLE umirza.S_NQ_INSTANCE
(
  JOB_ID         NUMBER(10)                     NOT NULL,
  INSTANCE_ID    NUMBER(20)                     NOT NULL,
  STATUS         NUMBER(10),
  BEGIN_TS       DATE,
  END_TS         DATE,
  EXIT_CODE      NUMBER(10),
  DELETE_FLG     NUMBER(10)                     NOT NULL,
  ERROR_MSG_FLG  NUMBER(10)                     NOT NULL
)
/
CREATE TABLE umirza.S_NQ_JOB
(
  JOB_ID              NUMBER(10)                NOT NULL,
  NAME                VARCHAR2(50 BYTE),
  DESC_TEXT           VARCHAR2(255 BYTE),
  SCRIPT_TYPE         VARCHAR2(20 BYTE),
  SCRIPT              VARCHAR2(255 BYTE),
  MAX_RUNTIME_MS      NUMBER(10),
  USER_ID             VARCHAR2(128 BYTE),
  NEXT_RUNTIME_TS     DATE,
  LAST_RUNTIME_TS     DATE,
  MAX_CNCURRENT_INST  NUMBER(10),
  BEGIN_YEAR          NUMBER(10),
  BEGIN_MONTH         NUMBER(10),
  BEGIN_DAY           NUMBER(10),
  END_YEAR            NUMBER(10),
  END_MONTH           NUMBER(10),
  END_DAY             NUMBER(10),
  START_HOUR          NUMBER(10),
  START_MINUTE        NUMBER(10),
  END_HOUR            NUMBER(10),
  END_MINUTE          NUMBER(10),
  INTERVAL_MINUTE     NUMBER(10),
  TRIGGER_TYPE        NUMBER(10),
  TRIGGER_DAY_INT     NUMBER(10),
  TRIGGER_WEEK_INT    NUMBER(10),
  TRIGGER_RANGE_DOW   NUMBER(10),
  TRIGGER_RANGE_DOM   NUMBER(10),
  TRIGGER_RANGE_MTH   NUMBER(10),
  TRIG_RANGE_DAY_OCC  NUMBER(10),
  DELETE_DONE_FLG     NUMBER(10)                NOT NULL,
  DISABLE_FLG         NUMBER(10)                NOT NULL,
  HAS_END_DT_FLG      NUMBER(10)                NOT NULL,
  EXEC_WHEN_MISS_FLG  NUMBER(10)                NOT NULL,
  DEL_SCPT_DONE_FLG   NUMBER(10)                NOT NULL,
  PATH_IN_SCPT_FLG    NUMBER(10)                NOT NULL,
  ISUSER_SCPT_FLG     NUMBER(10)                NOT NULL,
  DELETE_FLG          NUMBER(10)                NOT NULL,
  TZ_NAME             VARCHAR2(100 BYTE)
)
/
CREATE TABLE umirza.S_NQ_JOB_PARAM
(
  JOB_ID          NUMBER(10)                    NOT NULL,
  RELATIVE_ORDER  NUMBER(10)                    NOT NULL,
  JOB_PARAM       VARCHAR2(255 BYTE),
  DELETE_FLG      NUMBER(10)                    NOT NULL
)
/
CREATE INDEX umirza.S_NQ_ERR_MSG_F1 ON umirza.S_NQ_ERR_MSG (INSTANCE_ID)
/
CREATE UNIQUE INDEX umirza.S_NQ_ERR_MSG_U1 ON umirza.S_NQ_ERR_MSG (JOB_ID, INSTANCE_ID, RELATIVE_ORDER)
/
CREATE INDEX umirza.S_NQ_INSTANCE_M1 ON umirza.S_NQ_INSTANCE (END_TS, STATUS, INSTANCE_ID)
/
CREATE INDEX umirza.S_NQ_INSTANCE_M2 ON umirza.S_NQ_INSTANCE (BEGIN_TS, STATUS, INSTANCE_ID)
/
CREATE INDEX umirza.S_NQ_INSTANCE_M3 ON umirza.S_NQ_INSTANCE (INSTANCE_ID, DELETE_FLG)
/
CREATE INDEX umirza.S_NQ_INSTANCE_M4 ON umirza.S_NQ_INSTANCE (JOB_ID, INSTANCE_ID, STATUS, DELETE_FLG)
/
CREATE INDEX umirza.S_NQ_INSTANCE_M5 ON umirza.S_NQ_INSTANCE (STATUS, DELETE_FLG)
/
CREATE UNIQUE INDEX umirza.S_NQ_INSTANCE_U1 ON umirza.S_NQ_INSTANCE (JOB_ID, INSTANCE_ID)
/
CREATE INDEX umirza.S_NQ_JOB_M1 ON umirza.S_NQ_JOB (NEXT_RUNTIME_TS)
/
CREATE INDEX umirza.S_NQ_JOB_M2 ON umirza.S_NQ_JOB (USER_ID)
/
CREATE UNIQUE INDEX umirza.S_NQ_JOB_P1 ON umirza.S_NQ_JOB (JOB_ID)
/


<<<<<<<<<<< Script ends here >>>>>>>>>>>>>>

 Step 2-  Configure Scheduler Schema

Open Command Prompt...

D:\oracle\bise1\bi\server\Bin>schconfig
Copyright (c) 1997-2006 Oracle Corporation, All rights reserved

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice: 1

***** Scheduler Configuration *****
 1 - Database
 2 - General
 3 - Advanced
 0 - Quit

>>> Enter Choice: 1

***** Scheduler Database Configuration *****
 1 - Database Type                    : Oracle 10g R2
 2 - Call Interface                   : OCI 10g
 3 - Data Source Name                 : MYDB
 4 - User Name                        : umirza
 5 - Password                         : *****
 6 - Timeout (Minutes)                : 60
 7 - Maximum Connections              : 5
 8 - Bulk Fetch Buffer Size (bytes)   : 33792
 9 - Database Table for Jobs          : S_NQ_JOB
10 - Database Table for Instances     : S_NQ_INSTANCE
11 - Database Table for Parameters    : S_NQ_JOB_PARAM
12 - Database Table for Messages      : S_NQ_ERR_MSG
13 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Scheduler Configuration *****
 1 - Database
 2 - General
 3 - Advanced
 0 - Quit

>>> Enter Choice: 2

***** Scheduler General Configuration *****
 1 - Scheduler Script Path            : D:\oracle\bise1\bi\server\Scripts\Scheduler
 2 - Default Script Path              : D:\oracle\bise1\bi\server\Scripts\Common
 3 - Temporary File Path              : D:\oracle\bise1\bidata\tmp
 4 - Port Number                      : 9705
 5 - Purge Old instances after X days : 7
 6 - Purge DB every X minutes         : 5
 7 - Minimum Execution Threads        : 1
 8 - Maximum Execution Threads        : 100
 9 - Pause When Service Starts        : False
10 - Administrator Name               : Administrator
11 - Administrator Password           : *****
12 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Scheduler Configuration *****
 1 - Database
 2 - General
 3 - Advanced
 0 - Quit

>>> Enter Choice: 3

***** Scheduler Advanced Configuration *****
 1 - Script Server Port                  : 9707
 2 - Log All SQL Statements              : False
 3 - Participant in Cluster              : False
 4 - Cluster Monitor Port                : 9708
 5 - Use SSL                             : False
14 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Scheduler Configuration *****
 1 - Database
 2 - General
 3 - Advanced
 0 - Quit

>>> Enter Choice: 0

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice: 2

***** Mail Configuration *****
 1 - General
 2 - Advanced
 0 - Quit
>>> Enter Choice: 1

***** Mail General Configuration *****
 1 - From Display Name               : domainname.com
 2 - Sender Address                  : admin@
domainname.com
 3 - SMTP Server                     : ###.###.#.##          <------------- this will be the server ip
 4 - SMTP Port                       : 25
 5 - Number of Retries Upon Failure  : 1
 6 - Maximum Recipients              : 0
 7 - Use Bcc                         : False
 8 - Authenticate against SMTP server: True
 9 - User Name                       :
10 - Password                        : *****
11 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Mail Configuration *****
 1 - General
 2 - Advanced
 0 - Quit
>>> Enter Choice: 2

***** Mail Advanced Configuration *****
 1 - Use SSL                             : False
 5 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Mail Configuration *****
 1 - General
 2 - Advanced
 0 - Quit
>>> Enter Choice: 0

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice: 3

***** General Configuration *****
 1 - SAW Machine Name                    : umirza:9710
 2 - iBot Log Directory                  : D:\oracle\bise1\bi\server\Log\iBots
 3 - Purge iBot Log files older than     : 7
     (days)
 4 - Number of Global Tries              : 2
 5 - Minimum Global Sleep Seconds        : 3
 6 - Maximum Global Sleep Seconds        : 10
 7 - Number of Request Tries             : 3
 8 - Minimum Request Sleep Seconds       : 2
 9 - Maximum Request Sleep Seconds       : 10
10 - Number of Delivery Tries            : 4
11 - Minimum Delivery Sleep Seconds      : 5
12 - Maximum Delivery Sleep Seconds      : 10
13 - Maximum Rows Times Columns          : 10000
     (regarding passing of filters)
14 - Debug Enabled                       : False
15 - Keep Error Log Files                : True
16 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice: 4

***** Workflow Configuration *****
 1 - Server                              : http://localhost
 2 - System                              : eai_enu
 3 - Service                             : ANALYTICS
 4 - User                                : sadmin
 5 - Password                            : *****
 6 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice: 5

***** General Configuration *****
 1 - Java Host Server             : localhost:9810
 2 - DEFAULT VALUES
 0 - Quit

>>> Enter Choice: 0

***** Delivers Configuration Menu *****
1 - Configure Scheduler
2 - Configure Mail
3 - Configure iBots
4 - Configure Workflow
5 - Configure Java Extension
0 - Quit
>>> Enter Choice: 0

D:\oracle\bise1\bi\server\Bin>








 Step 3-  Credential Store Configurations


C:\oracle\bise1\bi\server\Bin>cryptotools credstore -add -infile C:\oracle\bise1\bidata\web\config\credentialstore.xml
>Credential Alias: admin
>Username: Administrator
>Password: *****
>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 "C:\oracle\bise1\bidata\web\config\credentialstore.xml" exists.
Do you want to overwrite it? y/n (y): y



 Step 4-  Entry of Credential Store in InstanceConfig.xml


<?xml version="1.0" encoding="utf-8"?>
<WebConfig>
.
.
.
.
.
<CredentialStore>
<CredentialStorage type="file" path="d:/oracle/bise1/bidata/web/config/credentialstore.xml" passphrase="secret"/>
</CredentialStore>
</ServerInstance>
</WebConfig>



 Step 5-  Presentation Layer Settings

 
Setting up delivery profile for the user account. In this case presentation administrator will kickoff iBot:





Add respective devices to Email and put email address to whom you want to send deliverable content .




Setting the permission for Delivers/iBot from ‘Manage Privileges’ as below:



Goto Delivers and Create new iBot .Schedule it to immediate.







Select recipients as Me and also you can choose from user groups. You can set deliverable content to Dashboard or Report. Select "Email" to be the Destination.







Now you can save the iBot. Once you save the iBot the process will be triggered immediately and you can see detail log from ‘..\OracleBI\server\Log\iBots’. 



Thanks,
Satya Ranki Reddy

No comments:

Post a Comment