Scheduler Service config and setup

If one installs scheduler via the typical setup; they will not or should not have any issues.  It is pretty simple.  You need a database for your scheduler tables(This is a given and requirement).  You need the scheduler service and you need the presentation service.  Now, you really don’t need the presentation services if you are just using scheduler to run only bi publisher jobs!  But don’t get me started on BI Punisher…err… I mean BI Publisher(This is another post topic entirely).  Scheduler is just a service whose sole purpose is.. you guessed it…scheduling jobs.  We use the scheduler with presentation server for iBots.  iBots are presentation server specific jobs to pre-run long running reports or run reports to be sent via email, dashboards or to other devices.  Oracle highly recommends running scheduler service on the same box as presentation server.  This is for reasons discussed later and also due to fact that scheduler will more than likely be used solely with presentation server to scheduler iBot jobs via Delivers.  Now, don’t get me wrong you could run scheduler on a different box; but, there are more specific configurations needed.  For example, lets say you want to run presentation services on box1, bi server and scheduler on box2.  This is what needs to be done.

1. Configure you scheduler either via the job manager UI or at the root config file, which is located at OracleBIData\scheduler\config\instanceconfig.xml (NOTICE HOW THIS IS LOCATED WITHIN THE ORACLEBIDATA DIRECTORY.  That is because it is presumed to run on the same machine as the presentation services!!!)

You will want to add the tns entry for your scheduler database here along with the userid/password to access scheduler tables. (As seen below)

Here are the contents of the schedulers instanceconfig.  As you can see there are lots of entries.  I find it best to use the UI in order to not make XML errors!  But for those with lots of XML experience its up to you.  As for me I can do both;)  (In any case please keep note of the settings highlighted in blue. PLEASE TAKE NOTICE OF DATABASE PASSWORD AS IT IS ENCRYPTED THAT’S WHY IT IS GOOD TO USE THE UI TOOL TO CONFIGURE ALSO.)

<!– Oracle BI scheduler Config file. –>
<!– This is a generated file, user-added comments may be lost. –>
<!– User added config values will be maintained though. –>
<scheduler>
<ServerInstance>
<AdminName>Administrator</AdminName>
<AdminPswd>a9ff96857fba4f6a</AdminPswd>
<BulkFetchBufferSize>33792</BulkFetchBufferSize>
<CLIType>12</CLIType>
<ClusterPort>9708</ClusterPort>
<DB_Column_Names>
<TABLE_ERRMSGS>S_NQ_ERR_MSG</TABLE_ERRMSGS>
<TABLE_INSTANCES>S_NQ_INSTANCE</TABLE_INSTANCES>
<TABLE_JOBS>S_NQ_JOB</TABLE_JOBS>
<TABLE_PARAMS>S_NQ_JOB_PARAM</TABLE_PARAMS>
</DB_Column_Names>
<DSN>tnsnames_entry_here</DSN>
<DefaultScriptPath>e:\OracleBI\server\Scripts\Common</DefaultScriptPath>
<Java_Extension>
<Java_Host_Server>localhost:9810</Java_Host_Server>
</Java_Extension>
<LogAllSqlStmts>False</LogAllSqlStmts>
<Mail>
<From>Oracle Delivers</From>
<MaxRecipients>0</MaxRecipients>
<Password>a9ff96857fba4f6a</Password>
<SMTP_Port>25</SMTP_Port>
<SMTP_Server>defaultmailserver.com</SMTP_Server>
<Sender>defaultuser@defaultmailserver.com</Sender>
<SmtpCaCertificateDirectory/>
<SmtpCaCertificateFile/>
<SmtpCaCertificateVerificationDepth>0</SmtpCaCertificateVerificationDepth>
<SmtpCipherList/>
<Try>1</Try>
<UseBcc>True</UseBcc>
<UseSSL>False</UseSSL>
<Username/>
</Mail>
<MaxExecThreads>100</MaxExecThreads>
<MinExecThreads>1</MinExecThreads>
<NumDbConnections>5</NumDbConnections>
<PartOfCluster>True</PartOfCluster>
<PauseOnStartup>False</PauseOnStartup>
<PoolPswd>f0126d06538c5ad301b41bb70f50912d0cf9f2f3e7b923d8</PoolPswd>
<PoolTimeout>60</PoolTimeout>
<PoolUsername>S_NQ_SCHED</PoolUsername>
<PortString>9705</PortString>
<PurgeInstDays>7</PurgeInstDays>
<PurgeIntervalMinutes>5</PurgeIntervalMinutes>
<SSL>
<CACertificateDir/>
<CACertificateFile/>
<CertPrivateKeyFileName>e:\OracleBI\server\config\private.pem</CertPrivateKeyFileName>
<CertificateFileName>e:\OracleBI\server\config\public.pem</CertificateFileName>
<CertificateVerifyDepth>0</CertificateVerifyDepth>
<CipherList/>
<PassphraseFileName>e:\OracleBIData\scheduler\config\passphrase</PassphraseFileName>
<PassphraseProgramName/>
<TrustedPeerDNs/>
<UseSSL>False</UseSSL>
<VerifyPeer>False</VerifyPeer>
</SSL>
<SchedulerScriptPath>e:\OracleBI\server\Scripts\Scheduler</SchedulerScriptPath>
<ScriptEngine>
<ScriptRPCPort>9707</ScriptRPCPort>
</ScriptEngine>
<TargetType>46</TargetType>
<TempPath>e:\OracleBIData\tmp</TempPath>
<Workflow>
<Auth_PWD>706b3e23758697344f58fc12b0b1487b</Auth_PWD>
<Auth_User>sadmin</Auth_User>
<Server>http://localhost</Server>
<Service>ANALYTICS</Service>
<System>eai_enu</System>
</Workflow>
<iBots>
<Debug>False</Debug>
<KeepErrorLogFiles>True</KeepErrorLogFiles>
<LogPurgeDays>7</LogPurgeDays>
<Log_Dir>e:\OracleBI\server\Log\iBots</Log_Dir>
<MaxDeliverySleepSecs>10</MaxDeliverySleepSecs>
<MaxGlobalSleepSecs>10</MaxGlobalSleepSecs>
<MaxRequestSleepSecs>10</MaxRequestSleepSecs>
<MaxRowsTimesColumns>10000</MaxRowsTimesColumns>
<MinDeliverySleepSecs>5</MinDeliverySleepSecs>
<MinGlobalSleepSecs>3</MinGlobalSleepSecs>
<MinRequestSleepSecs>2</MinRequestSleepSecs>
<NumDeliveryRetries>4</NumDeliveryRetries>
<NumGlobalRetries>2</NumGlobalRetries>
<NumRequestRetries>3</NumRequestRetries>
<Web_Server>box1:9710</Web_Server>
</iBots>
</ServerInstance>
</scheduler>

2. Run job manager to validate the scheduler services is running correctly and can communicate with the scheduler tables.  You could do this on any machine with the job manager exe but just make it simple and run on the box it is setup on.  In our case box2.  If job manager connects fine to our scheduler all is well with the service itself.  If you have issues, look in OracleBI\server\log\ for the scheduler.log for specific issues.

3. Now since scheduler is verified and running, we need to configure presentation services for scheduler so that it can talk with the scheduler service and create jobs.  First we need to run cryptotools like it says within the infrastructure guide.  Cryptotools.exe can be found in “OracleBI\web\bin” dir.  This tool is used to enter the username and password for scheduler within the credentialstore.xml located in “OracleBIData\web\config” and to encrypt it, if need be, with passphrase.  Scheduler uses the alias of “admin”.  Once this is complete you will need to tell presentation services where the scheduler services is and also to find and locate the credentialstore.xml.  If schedulerServer is not specified correctly you will get error from delivers when trying to create ibot.  Something like service or server can not be found or communication error. (This is not a problem with standard install where sched server is on same machine as presentation server)  Also if you get Authentication error when trying to create iBot this is because the credential store and alias is either not setup or not setup right.

Your instanceconfig.xml for presentation services should now look like this after making entries.  The entries for this step is highlighted in blue.

<?xml version=”1.0″ encoding=”utf-8″?>
<WebConfig>
<ServerInstance>
<DSN>AnalyticsWeb</DSN>
<CatalogPath>E:/OracleBIData/web/catalog/samplesales</CatalogPath>
<Alerts>
<ScheduleServer>box2</ScheduleServer>
</Alerts>
<AdvancedReporting>
<ReportingEngine>XmlP</ReportingEngine>
<Volume>XmlP</Volume>
<ServerURL>http://FRANK-G8ANIAY3K:9704/xmlpserver/services/XMLPService</ServerURL>
<WebURL>http://FRANK-G8ANIAY3K:9704/xmlpserver</WebURL>
<AdminURL>http://FRANK-G8ANIAY3K:9704/xmlpserver/servlet/admin</AdminURL>
<AdminCredentialAlias>bipublisheradmin</AdminCredentialAlias>
</AdvancedReporting>
<JavaHome>C:\Program Files (x86)\Java\jdk1.5.0_12</JavaHome>
<BIforOfficeURL>client/OracleBIOffice.exe</BIforOfficeURL>
<!– To configure a limited set of languages to be available to users uncomment the <AllowedLanguages> tag below and choose a subset set of language tags from the list. Values must be comma separated. –>
<!– <AllowedLanguages>ar,cs,da,de,el,en,es,fi,fr,hr,hu,it,iw,ja,ko,nl,no,pl,pt,pt-br,ro,ru,sk,sv,th,tr,zh,zh-tw</AllowedLanguages> –>
<!– To configure a limited set of locales to be available to users uncomment the <AllowedLocales> tag below and choose a subset set of locale tags from the list. Values must be comma separated. –>
<!– <AllowedLocales>ar-dz,ar-bh,ar-dj,ar-eg,ar-iq,ar-jo,ar-kw,ar-lb,ar-ly,ar-ma,ar-om,ar-qa,ar-sa,ar-so,ar-sd,ar-sy,ar-tn,ar-ae,ar-ye,cs-cz,da-dk,de-at,de-ch,de-de,de-li,de-lu,el-gr,en-au,en-ca,en-cb,en-gb,en-hk,en-ie,en-in,en-jm,en-nz,en-ph,en-us,en-za,en-zw,es-ar,es-bo,es-cl,es-co,es-cr,es-do,es-ec,es-es,es-gt,es-hn,es-mx,es-ni,es-pa,es-pe,es-pr,es-py,es-sv,es-uy,es-ve,fi-fi,fr-be,fr-ca,fr-ch,fr-fr,fr-lu,fr-mc,hr-hr,hu-hu,id-id,it-ch,it-it,iw-il,ja-jp,ko-kr,ms-my,nl-be,nl-nl,no-no,pl-pl,pt-br,pt-pt,ro-ro,ru-ru,sk-sk,sv-fi,sv-se,th-th,tr-tr,zh-cn,zh-mo,zh-sg,zh-tw</AllowedLocales> –>
<!– <Disconnected><ArchiveIbots>true</ArchiveIbots><DisconnectedDir>disconnected</DisconnectedDir></Disconnected> –>
<CredentialStore>
<CredentialStorage type=”file” path=”e:\oraclebidata\web\config\credentialstore.xml”/>
</CredentialStore>

</ServerInstance>
</WebConfig>

4. One last thing to remember and this is important and is typically overlooked.  You need to configure scheduler to communicate with the presentation server.  This way when it runs reports via an iBot, it can run them.  Scheduler runs iBots through presentation server as that is where the reports themselves live.  And then presentation server in turn submits the report to BI Server to fulfill the logical data request like usual.  If you setup scheduler on another box other than presentation server you might get the 77030 error when running an ibot job since the default value for this is local:9710.

So thinking about it, it does not make sense to have scheduler on another box as it needs to communicate directly with presentation server.  Thus one more network hop to get a job complete.  In our example scheduler on box2 needed to communicate to box 1 (presentation server) to run an iBot job.  Then box1 (presentation server) need to communicate to box 2(bi server) to run the report.  If we kept presentation server and scheduler on box1 then we would eliminate that one network request as it would just be local request.

If you have any ?s or comments please let me know and I will try to answer them.

-Frank

Performance Tuning in OBIEE

Performance Tuning

Today we are going to discuss about various options for improving performance of reports in OBIEE.

There are lots of options available in OBIEE to improve performance of reports.

Today will discuss about these…

1)   Aggregate tables

2)  Cache  ( Cache Management)

3) Turning off logging

4) Constructing result using where clause

5) Limiting Number of Initialization block

6) Setting Query limits

7) Modeling Dimension Hierarchies  Correctly

8)  Setting NQSConfig Parameters

1) Aggregate Table (Aggregate Persistence Wizard)

Aggregate Table: Aggregate tables store precalculated measures that have been aggregate over a set of dimensional attributes.

This is very useful technique for speeding up query response time in decision support systems. This eliminates the need of run time calculations and delivers faster results to users.

The calculations are done ahead of time and the results are stored in the tables.

The key point is that the aggregate table should have fewer rows than the non aggregate table and therefore processing should be quicker.

Aggregate Persistence Wizard

Go to: OBIEE Admin > tool> Utilities > Aggregate Persistence Wizard

2) Caching 

3) Turning off Logging

Logging can affect in performance  of  Oracle BI Server and can create large log files. Logging can be used in problem solving and trouble shooting problematic queries.  So when it is necessary then only set logging level to 0 to 2. Set log level greater than 2 is not recommended by Oracle.

4) Constructing result using where clause

With the help of where clause in content tab in logical table source you can filter the data ( limits the rows ) returned from database.

Here , you can see the where clause and restrict the data using Expression Builder( marked in Blue).

5) Limiting Number of Initialization block

Initialize block are the only means to initialize dynamic repository , system session  and non system session variables. We should be very careful about not to create too many init blocks.

As we know, In the case of system and non system session variables, the initialization blocks get executed every time a user logs in to the server.  And In the case of Dynamic repository variables, the SQL in the Initialization blocks get executed every time the server is started or periodically if a schedule is set up to refresh the value of the variable.

6) Setting Query limits

We can enable oracle BI Server to track and cancel run way queries by placing various limits on the repository for a given user or group. For each user or group it is possible to limit queries by various condition :

1) maximum number of rows a query can retrieve from a database

2) maximum time a query can run on a database

3) restricting access to a database during particular time periods from Analytic server

7) Modeling Dimension Hierarchies  Correctly

Dimension Hierarchy must be modeled accurately to ensure that Oracle BI optimizer chooses the most economical source. The Number of element for each level must be specified. The Number does not have to be exact,but ratio of number from one parent to child logical level should be accurate. See below screen shot,

8 ) Setting NQSConfig Parameters

NQSConfig.INI includes parameters that affect Oracle BI performance :

• Sort Memory size
• Sort buffer Increment size
• Virtual table page size