OBIEE Delivers-Schedulers-iBot Setup and Configuration

This necessarily need to be gone through couple of below steps :

Create Oracle BI Scheduler Tables

 Execute the ‘SAJOBS.Oracle.sql’ file located on ‘..\OracleBI\server\Schema’ to the schema where you like your Scheduler table has to be . The scripts will be different for the different database. Mentioned one is for Oracle database.In my case the script has been deployed under a schema called ‘S_NQ_SCHED’ .

 Configure BI Scheduler Schema in Job Manger

 Go to Manage -) Jobs -) Job Manger -) Configuration Options and put the below information : 

  

Will talk about The ‘SchedulerAdmin’ later.

   

You can change any of the information at above by directly editing ‘instanceconfig.xml’ file located at ‘..\OracleBIData\scheduler\config’  .

  

Configuring Oracle BI Scheduler Administrator

As part of the process of setting Oracle BI Scheduler configuration options, you will need to specify a username and password for the Scheduler Administrator. The Scheduler administrator must be a user in the Oracle BI repository (.rpd file) and have the Administrator group membership assigned.If you do not want to create a new administrator for Scheduler, you can set the Scheduler Administrator credentials to those of the Oracle BI user, Administrator that exists in the repository.

NOTE: The Scheduler Administrator is referred to in the following topics as the SchedulerAdmin.

Configuring Oracle BI Presentation Services

 Open the instanceconfig.xml file located under ‘..\OracleBIData\web\config’  .Between the <ServerInstance></ServerInstance> tags, locate the tag pair <Alerts> and </Alerts>. (If they do not exist, create them.) Between the Alerts tags, create the tag pair <ScheduleServer> and </ScheduleServer>.

If the Scheduler port has been changed from the default of 9705, specify the Scheduler port number.

For example:

<ServerInstance>

. . .

<Alerts>

<ScheduleServer>Schedulermachine:Port</ScheduleServer>

</Alerts>

Configure BI Presentation Services Credential Store

Oracle BI Presentation Services must be able to identify the Scheduler administrator and obtain the credentials to establish a connection with the Scheduler. Presentation Services stores the credentials that it uses in a Presentation Services Credential Store. The Scheduler administrator credentials must be added to the credential store, under the alias admin. To obtain the Scheduler Administrator credentials, Oracle BI Presentation Services searches the credential store for a username-password credential with the alias admin.

Use the following procedure to add the Scheduler administrator credentials to the Presentation Services Credential Store with the admin alias. This procedure adds the Scheduler administrator credentials to a proprietary XML file credential store called credentialstore.xml. The default location of this file is ‘..\OracleBIData\web\config’  on Windows .

Execute the CryptoTools utility to add the Scheduler Administrator credentials to the Presentation Services Credential Store:

cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml

>Credential Alias: admin

>Username: SchedulerAdmin

>Password: SchedulerAdmin

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

>Passphrase for encryption: secret

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

>File “OracleBIData_HOME/web/config/credentialstore.xml” exists. Do you want to

overwrite it? y/n (y):

The CryptoTools utility updates the credentialstore.xml file .

Configuring Oracle BI Presentation Services to Identify the Credential Store

 Oracle BI Presentation Services must be directed to the credential store that contains the Scheduler administrator credentials. This is done by setting parameters in the Oracle BI Presentation Services configuration file, instanceconfig.xml. In addition, if you have not stored the passphrase in the credential store, then the passphrase to decrypt the password credential must also be specified. In the above example, the passphrase was not stored in the credential store and needs to be specified in the instanceconfig.xml file.

 Modification, the instanceconfig.xml contains entries as shown in the following example:

 <?xml version=”1.0″?>

<WebConfig>

<ServerInstance>

<!– other settings … –>

<CredentialStore>

<CredentialStorage path=”"OracleBIData_HOME/web/config/credentialstore.xml” passphrase=”secret”/>

<!– other settings … –>

</CredentialStore>

<!– other settings … –>

</ServerInstance>

</WebConfig>

 

Create iBot in BI Presentation Interface

Set profile details for the user account .In my case Presentation admin will invoke iBot :

 

Add Devices to Email and put email address where you want to receive the deliverable content .

Change the permission for Delivers from ‘Manage Privileges’ as below:

Click on Delivers and Create new iBot .Set Schedule to immediate .

Set recipients as Me and additionally you can choose from user groups listed here .Select the deliverable content to Dashboard or Report .Select Destination to ‘Email’ .

Now once you save the iBot the process will be triggered immediately and you can detail log from ‘..\OracleBI\server\Log\iBots’. It would be like this :The strike thorough portion followed by “smtp/” would be replaced by the Email ID that you have configured for delivers .

  

Oracle BI EE 10.1.3.3.3/2 – Delivering Prompted Dashboard Pages using iBots – Bookmark links and HTML

One very good feature that BI EE does not have currently is the ability to send prompted dashboards to users via iBots. Currently, iBots can only send filtered reports but not dashboards. Lets look at an approach today to send prompted dashboards to end users. Use this approach only if you have no other option. Using the approach one can send the dashboards only in HTML format (PDF would not work). Let us start with the Brand Analysis Dashboard Page that we have in the Paint Dashboard. This page contains 4 prompts and a number of reports. Lets choose some values in all these prompts and run the reports like the one shown below

     

Our aim is to send this page(with the prompt values that we have chosen) via delivers. In order to achieve this lets start with create a Bookmark link to the page. If you are not sure what Bookmark link does, check my previous blog entry here. This bookmark link would give us a dashboard page with the same prompt values that we applied above. Now save this link. Then go to answers and create a simple report. Include a dummy column with the below formula

1	CASE WHEN 1=0 THEN Markets.Region ELSE 'Dummy' END

     

This is to ensure that the report does not throw out any error. Now, go to the narrative view of this report and enter the below iframe html code.

[/sourcecode]

     

In the above iframe, replace the src= attribute with the bookmark link that you saved in the previous step. Also, ensure that you are adding nquser and nqpassword parameters as shown above. Now, in the compound layout of this report just have this narrative view. Save this report. Now go to delivers and schedule an ibot to send this report to end users. Ensure that you are sending the content only as HTML.

     

Now, your end users would be receiving the prompted dashboards via delivers.

     

Not the best solution, but can be used in certain cases where the requirement is an absolute must. One can add more customizations to this by choosing a single bookmark page from multiple pages.

Oracle BI EE 10.1.3.3.3/2 – Delivering Prompted Dashboard Pages using iBots – Saved Selections

If you have looked at my blog entry here, i would have shown you how to go about delivering prompted dashboard pages. But the major drawback with that approach was one cannot send out PDF attachments as it involved the use of GO URL and iframes. Lets look at another approach (thanks to an excellent tip from Mike Durran, Oracle BI Delivers Product Manager) wherein one can deliver prompted dashboard pages even as PDF attachments. Lets start with the same example as before. Open up the Brand Analysis Dashboard page and apply some prompt values on the page.

     

Now, our aim is to send this page as is (with the applied prompt values) to end users. In order to so this, create a shared selection for this page

     

     

Make the saved selection as default for this page (if you want to deliver this as multiple users then you need to make the selection as default for all the users). Once this done, just deliver the dashboard page. This will automatically send the page after applying the selection

     

     

An elegant solution if you want to send a dashboard page with applied prompt values.

Oracle BI EE 10.1.3.3/2 – Executing iBots from Dashboards/Answers – Sending Emails to Administrators

In my previous blog entry here, i had shown you how to go about calling command line utilities(for Purging the Cache) from Dashboards/Answers. Along the same lines, lets look at another possible use case of the procedure that was used there today. Today, we shall see how to go about calling the ibots from Dashboards/Answers. i.e our aim is to execute the ibots directly from Dashboards by clicking a single URL. This would be useful in a lot of situations wherein you would like to send emails to Administrators whenever the database server is down or when there is some error in some of the reports. In order to achieve this, the first step is to create a simple ibot which would basically send out an email to the Administrator. Also, this ibot would contain a report (generally a diagnostics report). In our case, lets include a simple report.

      

      

Once this is done, find out the jobid of this ibot from the Job Manager console.

      

Now, create a batch file to call this ibot on an on-demand basis(on unix you would have to create a shell script). This batch file would basically have this command.

saschinvoke -u Administrator/Administrator -j 1

      

The advantage of the above batch file is that you can also pass parameters (jobids etc) dynamically to it. The next step is to create a simple DBMS_SCHEDULER job to call this utility.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1′,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

Now, create another procedure to run this job.

CREATE OR REPLACE PROCEDURE PRO_INVOKEIBOTS IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1′);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>EMAIL Successfully Sent</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>Email Successfully Sent</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall expose to be called from a URL using the Pl/SQL gateway. In order to do that, grant execute privilege on the above procedure to ANONYMOUS and add the procedure to the list of accepted and executable packages by the pl/sql gateway.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKEIBOTS’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

GRANT EXECUTE ON PRO_INVOKEIBOTS to ANONYMOUS;

Once this is done, create a URL link in the dashboard to call the below URL

http://localhost:7779/apex/hr.pro_invokeibots

      

Clicking on the above URL should automatically send an email to the Administrator.

      

Configuring Scheduler and Ibots when using External Table Authentication

I was recently at a client site configuring scheduler on their development environment to set up ibots . The scheduler was configured as per the documentation and the Scheduler service started up just fine. We then went on to create ibots and selected multiple users as the recipients of the Ibot.  

However, the ibots executed successfully only for the Administrator and threw the following error for all other recipients.

+++ ThreadID: 334 : 2009-04-12 21:44:43.359
[nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Authentication Failure.
Error Codes: IHVF6OM7:OPR4ONWY:U9IM8TAC

Odbc driver returned an error (SQLDriverConnectW).
State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43001] Authentication failed for emp 1 in repository Star: invalid user/password. (08004)

I should point out at this point that we had External Table Authentication configured for authenticating users into the application using the following initialization block:

select login from employee_d where login=’:USER’ and auth_id= ‘:PASSWORD’

The fix for this issue though available in the  documentation, could be easily overlooked.

The solution is to modify your Authentication initialization block as follows:

select login from employee_d where login=’:USER’ NQS_PASSWORD_CLAUSE (and auth_id= ‘:PASSWORD’)NQS_PASSWORD_CLAUSE

It is important that you use the NQS_PASSWORD_CLAUSE term in order to successfully schedule Ibots for users who are authenticated using external table authentication.