Configuring Oracle BI Scheduler

	What is Oracle BI Scheduler

Oracle BI Scheduler is a service that manages and schedules jobs. Oracle BI Scheduler supports two kinds of jobs:

Scripted jobs.

Scripted jobs are set up and submitted using the Job Manager feature of the Oracle Business

Intelligence Server Administration Tool. For example, a scripted job could periodically load Oracle

BI Server usage statistics into a back-end database. For scripted jobs, Oracle BI Scheduler

communicates with Oracle BI Server.

Oracle BI Scheduler supports two published Java interfaces and two scripting languages.

Supported Java interfaces:

• SchedulerJavaExtension.java
• SchedulerJobInfo.java

Supported script languages:

• VBScript
• Jscript

Unscripted jobs, called iBots.

iBots deliver reports and alerts to end users. iBots are configured and submitted for execution

using Oracle Business Intelligence Delivers. Oracle BI Scheduler communicates with Oracle

Business Intelligence Presentation Services for unscripted jobs.

	Step 1: Creating Oracle BI Scheduler databases and tables

1) Make sure that you have a valid database administrator account to create a database and tables.

2) Create the database and call it SCHED_TEST_DB

3) Run SAJOBS.MSSQL.SQL /SAJOBS.ORACLE.SQL script to create the necessary tables in the database SCHED_TEST_DB for the scheduler. This script is present at ORACLEBI_HOME\Server\Scripts

4) Verify that S_NQ_ERR_MSG, S_NQ_INSTANCE, S_NQ_JOB, and S_NQ_JOB_PARAM are created.

Please note that separate Databases needs to be created for BI scheduler in QA and production environment.

	Step 2: Create System DSN for the scheduler database

Create a new System DSN on the machine where BI Scheduler service is supposed to run.

Oracle database guys can simply give TNS service name as DSN name and hence if they wish can skip this step.

1) From the Windows Start menu, select Settings > Control Panel > Administrative Tools > Data Sources (ODBC).

2) Start the ODBC Data Source Administrator

3) Select the System DSN tab, and then click Add.

4) Select the driver SQL Server, and then click Finish

5) In the wizard Create a New Data Source to SQL Server, do the following

6) Select the appropriate Authentication

7) Make sure check box corresponding to ‘Connect to SQL Server …’ is ticked and click next

8) Check the box corresponding to ‘Select default database’ and select BI Scheduler database created in the step 1. Leave other fields as default.

9) Click next and then finish.

10)Test the connection. The result should be a success.

Please ensure that the default database of the system DSN should not be left blank.

	Step 3: Configure Job Manager

1) From the Windows Start menu, select Programs->Oracle BI Intelligence->Job Manager.

2) Job Manager Window pops up. Fill in the values as shown in the screenshot.

3) Click Scheduler->General tab and Fill in the values as shown in the screen shot below.

Please Note that Administrator Name is the RPD Administrator. Or you can create a new account in the RPD with admin writes.

Type in Administrator Name: Administrator

Administrator Password: xxxxx

4) Click on Mail->General Tab and fill in the values as shown in the screenshot below. Display Name can be changed to whatever seems to be appropriate.

5) For rest of the fields, leave them to their default values and click OK.

	Step 4: Configure instanceconfig.xml

1) Open the instanceconfig.xml file for editing from OracleBIData_HOME/web/config

2) Between the <ServerInstance></ServerInstance> tags, locate the tag pair <Alerts> and </Alerts>. (If they do not exist, create them.)

3) Between the Alerts tags, create the tag pair <ScheduleServer> and </ScheduleServer>.

4) Between the ScheduleServer tags, insert the machine name of the Scheduler machine. Please see the screen shot below

5) Save the file when you are done.

Your changes take effect when the Oracle BI Presentation Services service is restarted.

	Step 5: Configure credentialstore.xml

Open command prompt (Press Windows+R and type in cmd)

i) Run the following command.

cryptotools credstore -add –infile OracleBIData/web/configcredentialstore.xml

ii)>Credential Alias: admin

iii)>Username: Administrator

iv)>Password: DTADMIN

v)>Do you want to encrypt the password? y/n (y):

vi)>Passphrase for encryption: secret

vii)>Do you want to write the passphrase to the xml? y/n (y):

viii)>File "OracleBIData_HOME/web/config/credentialstore.xml" exists. Do you want to overwrite it? y/n (y)

Please note that Credential Alias must always be ‘admin’. BI Presentation service identifies BI Scheduler service using this alias.

	Step 6: Reconfigure instanceconfig.xml

The reconfiguration of instance config is done to identify the credentialstore.xml.

1) Open the instanceconfig.xml file for editing.

2) Locate the <CredentialStore> node within this file

3) If the <CredentialStore> node does not exist, create this element with sub-elements and attributes. See the screenshot below.

4) Restart Presentation Services to reflect the configuration changes.

	Step 7: Start BI Scheduler Service

1) Restart Oracle BI Server,

2) Restart Oracle BI Presentation Server

3) Restart Oracle BI Scheduler Service.

	Step 8: Test the setup

To test the setup, create a very simple iBOT and see whether it works.

Procedure to create a simple iBOT.

1) Log into BI Presentation Analytics homepage using Administrator account

2) Go to ‘Settings’ on the top right corner and then go to ‘My Account’.

3) ‘My Account’ window pops up. Click on Add Email Device.

4) Add Email Device as shown in the screenshot below and then click finish.

5) Click on Add Delivery profile and then add Delivery profile as shown in the screenshot.

6) Again to ‘Settings’-> Administration->Manage Privileges.

7) Check for the privileges for ‘Delivers’. Ensure that privileges are awarded as shown or according to the business needs

8) Click on ‘More Products’ and then click on ‘Delivers’

9) Click on ‘Create iBot’ and select the ‘Recipients’ Tab

10) Check ‘Me’ box and add more recipients if you want to.

11) Select ‘Delivery Content’ and select a report from ‘select content’ tab.

12) Give headline in Headline text box. This would appear as Subject line for the email.

13) Go to ‘Destination’ Tab and check Email box.

14) Go to ‘Schedule’ Tab and check start immediately.

15) Save the iBot.

After a couple of Minutes if the setup is configured correctly, you should get the alert on email. If you get an email, this implies that BI Scheduler has been setup correctly.

	References

1)

Oracle® Business Intelligence Infrastructure Installation and Configuration Guide

Version 10.1.3.2.1

April 2007

2)

Oracle® Business Intelligence Scheduler Guide

Version 10.1.3.2

December 2006

Installing Oracle Business Intelligence 10.1.3.4.1

1) Hardware Requirements

2) Supported Server Operating Systems

Points to Note:

For Windows and Linux 64 bit environment, though the OS and processor are 64 bit but the OBIEE binaries(software) are still 32 bit. This implies that OBIEE would run in 32-bit emulation mode on 64 bit processors and hence you can expect little performance gain on 64 bit environment.

Further, Itanium2 processor is not supported.

3) Minimum Patch Levels needed on the Operating Systems

4) Certified Web Servers and Application Servers

5) Certified Identity Management Applications

6) Example

7) Install Sun Java Development Kit on your host system.

The minimum version of JDK required is JDK 1.5. You can download JDK from

java.sun.com/javase/downloads/index.jsp. Please do not confuse JRE with JDK. Both are entirely different.

8) Download OBIEE 10.1.3.4.1 for your environment from OTN website.

Rest of the installation is demonstrated for windows environment. For Linux environment, the installation process is similar as well.

9) Unzip the downloaded file and go to \biee_windows_x86_101341\Windows\Server\Oracle_Business_Intelligence.

10) Click on setup.exe and this should launch the installation wizard. Make sure you have the installation rights on your host system.

11) The following window should pop and click next

Please note that minimum of 2GB of spacle is needed in your installation drive and atleast 200MB of space is required for the temp directory

12) Based on your requirements select the whether you want to install all OBI components or only some of them. If you have no idea about what to install and what not to install, please select the 'Complete' option as shown below and click next.

13)Enter the JDK location. Enter any password for OC4J. This password is needed during the uninstallation. Click next

14) Select what services you want to start automatically. Simply leave the default values and click next if you are not very sure about what to do in this window.

15) Chose the language in which you would want to see the errors. You would most likely want to see the error messages in your localization language. For example, if you working on Japanese windows, then you make want to see errors in japanese language.

16) Review the components that are being installed and click next

16) OBIEE installation will begin. You may or may not choose to install .Net framework that comes with OBIEE. It will take a few minutes for the installation to complete.

17) Click finish to complete the installation.

Your OBIEE setup is now up and running.

Understanding Oracle BI Application(OBIApps) methodology of deriving AGO Measures

Very often I have members from the IT team at Customer site asking me the question on how to customize OBIApps in order to derive Ago(Year,Quarter,Month,Day) Measures, which are not delivered Out of the Box(OOTB) .Of course there is a Time series function(AGO) in OBIEE which can be leveraged to fulfill these requirements , but in this article I would like to explain the OOTB approach that is taken to derive AGO measures. Getting familiar with OOTB approach and the underlying Data Model that is there to support similar requirements, can help us follow similar methodology for our customization.

In this article I have takenOracle BI Application 7.9.6>Human Resources>Workforce Profile Subject area as an example for understanding the Concepts.

1.Below is the Screnshot of the OOTB Human Resources>Workforce Profile Subject Area .

2. Building a quick report with the Metrics above displays the results as below .

Note that Prior Period Headcount for 2006 is equivalent to Headcount value of 2005.

3. Let us now look into the design . Open the OOTB RPD and expand the Human Resource - Workforce Profile Presentation Catalog. Scroll Down and further expand the Presentation Table Workforce Profile Facts you should notice the two Presentation Columns Headcount and Prior Period Headcount .

4. These two Presentation columns are mapped to different Logical Table Sources as listed below
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Year
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Year_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Quarter
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Quarter_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Month
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Month_Ago
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Day
Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Day_Ago

5.Further drilling down into the Physical Layer reveals that the above logial table sources are derived from the two Physical Table Aliases of the same table W_WRKFC_EVT_MONTH_F.

6.Below is the Physical Diagragm of the alias Fact_W_WRKFC_EVT_MONTH_F_Snapshot joinining with the Time Dimensions.

7.Below is the expression definition used in the join criteria . Note that ROW_WID of Year,Quarter,Month and Day Dimensions are used to join with the Fact table alias.

8.Below is the Physical diagram of the alias Fact_W_WRKFC_EVT_MONTH_F_Snapshot_Period_Ago joining with the Time Dimensions.

9.Below is the expression definition used in the join criteria . Note that YEAR_AGO_WID,QUARTER_AGO_WID,MONTH_AGO_WID and DAY_AGO_WID are used to join with the Time Dimensions.

10.Let us now analyze the Data of the Time Dimension. As you can notice below the Data Model is in place which can be used to join and derive AGO measures.

11.Similar approach can be followed during Oracle BI Application(OBIApps) Customization for deriving ago measures which are not present Out of the Box(OOTB).

Configuring OBIEE Proxy(Act As) Functionality

As an OBIEE Developer we always want to be able to login as the Business Users in order to validate the security development. This can be done without the need to know the Business user's logon credentials by simply configuring the 'Act As' feature in OBIEE.
Below are the steps involved in enabling this feature.
1.The Script below will create a Proxy table and insert three records so that the developer 'shiv' can login(proxy) as 'business_user1' and 'business_user2' and validate the security requirement is as expected.Also the 'Administrator' can proxy the user 'shiv'.


CREATE TABLE OBIEE_ACT_AS_USERS
(PROXY_ID VARCHAR2(50),
TARGET_ID VARCHAR2(50),
PROXY_LEVEL VARCHAR2(50));
INSERT INTO OBIEE_ACT_AS_USERS(PROXY_ID,TARGET_ID,PROXY_LEVEL) VALUES('Administrator','shiv','full');
INSERT INTO OBIEE_ACT_AS_USERS(PROXY_ID,TARGET_ID,PROXY_LEVEL) VALUES('shiv','business_user1','full');
INSERT INTO OBIEE_ACT_AS_USERS(PROXY_ID,TARGET_ID,PROXY_LEVEL) VALUES('shiv','business_user2','full');
COMMIT;

2.Import the table OBIEE_ACT_AS_USERS into the Physical Layer and Name the Database and Connection Pool as Act_As_Users , CP_Act_As_Users.

3. We now need to create three Session Initialization Blocks Set_RUNAS(Variable:RUNAS),ProxyLevel(Variable:PROXYLEVEL) and ProxyBlock(Variable:PROXY).

4.Initialization Block:Set_RUNAS
Default Initialization String:
Select 'dummy' from dual


5.Initialization Block:ProxyBlock
Default Initialization String:
Seleselect target_id from obiee_act_as_users where 'VALUEOF(NQ_SESSION.RUNAS)'=target_id and ':USER'=proxy_Id


6.Initialization Block:ProxyLevel
Default Initialization String:
select proxy_level from obiee_act_as_users where 'VALUEOF(NQ_SESSION.RUNAS)'=target_id and ':USER'=proxy_Id


7.Navigate to /OracleBIData/web/config/ and modify the file instanceconfig.xml to add the below tag before the </ServerInstance>

<LogonParam>
<TemplateMessageName>Act_As_Logon_Template</TemplateMessageName>
<MaxValues>100</MaxValues>
</LogonParam>


8.Navigate to /OracleBI/web/msgdb/customMessages/ and create a new XML file Act_As_Logon_Template.xml containing the below script.

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable system="SecurityTemplates" table="Messages">
<WebMessage name="Act_As_Logon_Template">
<XML>
<logonParam name="RUNAS">
<getValues>EXECUTE PHYSICAL CONNECTION POOL Act_As_Users.CP_Act_As_Users select target_Id from OBIEE_ACT_AS_USERS where proxy_Id='@{USERID}'</getValues>
<verifyValue>EXECUTE PHYSICAL CONNECTION POOL Act_As_Users.CP_Act_As_Users select target_Id from OBIEE_ACT_AS_USERS where proxy_Id ='@{USERID}' and target_Id='@{VALUE}'</verifyValue>
<getDelegateUsers>EXECUTE PHYSICAL CONNECTION POOL
Act_As_Users.CP_Act_As_Users select proxy_Id, proxy_Level from OBIEE_ACT_AS_USERS where target_Id ='@{USERID}'</getDelegateUsers>
</logonParam>
</XML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

9. Within OBIEE Security add three new Users 'shiv','business_user1' and 'business_user2' as shown below.

10.Create a new Web group Act_As_Users_Group and add the user 'shiv' to this group.

11.Cotinue to be logged on as an Administrator and Navigate to Settings>Administration>Manage Privileges and add 'Act_As_Users_Group to Proxy .

12. Restart the Oracle BI Server and Oracle BI Presentation Server.

13. Login in as 'shiv' and then navigate to settings>Act as>business_user1