Chaining iBots to Purge and Build Cache

This can be obtained by following steps;

1. Create a purging cache report from the direct database request;

           The steps are elaborated in the previous post.

2. Create the iBot for the report whom cache is required to be build;

3. Now create an iBot and add the purging cache report in the conditional request;

4. In the next step chain the iBot for whom the cache is required to be built in the advanced Tab of the iBot.

5. Schedule as required.

When the iBot will run it will purge the cache first and then build the cache.

Cheers and Long Live Pakistan ;)

Purging Cache in the Answers using Direct Database Request:

In order to purge cache using direct request create a new database in repository as follows;

1 )      Set Database to “ODBC Basic”

2 )      Check “Allow Direct Database Request by Default”

Next Step is to add connection pool in the database as follows;

1)      Set Call Interface to “ODBC 2.0”

2)      Set Data Source Name to “Analytics Web”

3)      Add username and password

In the next step create a direct database request and add following;

To Purge the whole cache;

1)      Add Call SAPurgeAllCache(); in the SQL Statement

2)      Provide connection poll information

To Purge cache by Query;

1)      Add Call SAPurgeAllCache(‘SQL QUERY’); in the SQL Statement

e.g. Call SAPurgeCacheByQuery('SELECT Ledgers."Charge #" saw_0 FROM Payments ORDER BY saw_0');

The query can be obtained from the Manage Session window in the administration;

2)      Provide connection poll information

To Purge cache by Table;

1)      Add Call SAPurgeCacheByTable (‘TABLE NAME’); in the SQL Statement

e.g. Call SAPurgeCacheByTable( ‘CHARGES’);

2)      Provide connection poll information

To Purge cache by Database;

1)      Add Call SAPurgeCacheByDatabase (‘DATABASE NAME’); in the SQL Statement

e.g. Call SAPurgeCacheByDatabase( 'SEQUELMED' );

2)      Provide connection poll information

Purge Cache report can also be added as a link on the dashboard to purge cache at any time;

Furthermore purge cache report can also be added in the conditional request of an iBot to schedule purging.

Cheers and Long Live Pakistan ;)

Setting up iBot to save report to a folder

Here are the steps;


Create a JavaScript file with below contents name it "savetofile.js" and save it at \\bise1\bi\server\Scripts\Common ;

var FSO = new ActiveXObject("Scripting.FileSystemObject");
//var foldername = GetConfigurationValue("Log Dir", "iBots") + "\\" + UserID
var foldername = "\\Reports"
var rptname;
with (new Date) {
rptname = ((((getFullYear() * 100 + getMonth() + 1) * 100 + getDate()) * 100 + getHours()) * 100 + getMinutes()) * 100 + getSeconds();
}
if (FSO.FolderExists(foldername))
{
var fileName = foldername + "\\" + "RPT_" + rptname + Parameter(1) + Parameter(2);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);
}
else
{
FSO.CreateFolder(foldername);
//var fileName = foldername + "\\" + JobID + "-" + InstanceID + "-" + Parameter(1);
var fileName = foldername + "\\" + "RPT_" + rptname + Parameter(1) + Parameter(2);
var fooFile = FSO.CopyFile(Parameter(0), fileName, true);
}



Step 1: Create an iBot and set schedule as required;

Step 2: Select Delivery Contents for iBot and select send content as “Attachement(PDF)”

Step 3: Select Email for the Specific Devices in the destination Tab;

Step 4: In the advanced tab select Add Action and then Custom Script

Step 5:
In the file name add “savetofile.js”
Select “Pass Delivery content to script” in the radio button
In the other parameter add two text boxes. In the first box add file name and in the second box add file extension and click OK

Step 6: Save the iBot

Step 7: The report will be saved in the directory “C:\Reports”
File will be named with following convention;
RPT_DATETIME_REPORTNAME.EXTENSION

Step 8: Done and the iBot will look like this;

Cheers & Long Live Pakistan ;)

OBIEE - Query Miss the Cache Hit

Yes, a superset logical query that is seeded can satisfy a number of other queries that return the subset of the superset. But there are a lot of limitations and reason for missing the hit on an already seeded superset cache and are given below;

1.    Cache hit will miss if the aggregation rules are applied at report level rather than implementing at repository (RPD) level.

e.g. AVG, AvgDistinct, BottomN, Count, CountDistinct, Count (*) , First, Last, Max, Median, Min, NTile, Percentile, PeriodAgo, PeriodToDate, Rank, StdDev, Sum, SumDistinct, TopN etc.

By default these aggregate functions are grouped at the lowest level however can be grouped on some other levels using the BY statement in the function definition.

2.    The logical query/report has an additional column as compared to the seeded report/query

3.    The logical query/report has a where clause on the column that is not available in the seeded query/report

4.    There are very limited aggregation possibilities available e.g. The logical query/report contains a column plan_payment and is cached, a request for sum(plan_payment) will miss the cache hit.

5.    The relationship operator in the filters does not qualify the subset of a superset.

6.    The logical query/report is created with a distinct superset and cached. The cache hit will miss if the logical query/report eliminates the distinct processing.

As a matter of fact the cache seeding and hitting in case of subset of a superset works in relatively simpler cases. 

Driect Database Request and Use of Presentation Variables

Let’s create a direct database request and see the use of presentation variables.

After clicking Create Direct Request, following layout will appear. Put your connection pool and SQL statement and then click Validate SQL and Retrieve Columns. If no error appears then everything is fine. Click Results and proceed.

Now as mentioned above, presentation variables can be used in Direct Database Requests.

Syntax:
@{variable_name}{default_value}

See the example SQL below:

You can put the request on Dashboard and utilize these presentation variables in dashboard prompt.

Cheers ;)

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’. 

All Done!

CHEERS :)

OBIEE Backup and Recovery

BI Backup and Recovery Using Windows Batch Files:

These batch files can be executed as a windows scheduled task.

For Backup:


File Name: backup_bi.bat

Following are the contents of this file:
-----------------
@echo off

java.exe -jar D:\OracleBI\oc4j_bi\j2ee\home\admin.jar ormi://localhost:23791 oc4jadmin admin1 -shutdown force
sleep 10
net stop "Oracle BI Presentation Server"
sleep 10
net stop "Oracle BI Java Host"
sleep 10
net stop "Oracle BI Server"

if not exist D:\BIBackup1\Tues mkdir D:\BIBackup1\Tues
echo Backing Up Repository...
copy /Y D:\OracleBI\server\Repository\myrepository.rpd D:\BIBackup1\Tues
copy /Y D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora D:\BIBackup1\Tues
echo Backing Up Configs...
copy /Y D:\OracleBI\server\Config\NQSConfig.INI D:\BIBackup1\Tues
copy /Y D:\OracleBI\server\Config\DBFeatures.INI D:\BIBackup1\Tues
echo Backing Up XML...
copy /Y D:\OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml D:\BIBackup1\Tues
copy /Y D:\OracleBI\xmlp\XMLP\Admin\Security\principals.xml D:\BIBackup1\Tues
copy /Y D:\OracleBI\xmlp\XMLP\Admin\Security\security.xml D:\BIBackup1\Tues
copy /Y D:\OracleBI\xmlp\XMLP\Admin\DataSource\datasources.xml D:\BIBackup1\Tues
if not exist D:\BIBackup1\Tues\msgdb mkdir D:\BIBackup1\Tues\msgdb
xcopy /E /Y /Q D:\OracleBI\web\msgdb D:\BIBackup1\Tues\msgdb
copy /Y D:\OracleBIData\web\config\credentialstore.xml D:\BIBackup1\Tues
copy /Y D:\OracleBIData\web\config\instanceconfig.xml D:\BIBackup1\Tues\web_instanceconfig.xml
copy /Y D:\OracleBIData\scheduler\config\instanceconfig.xml D:\BIBackup1\Tues\scheduler_instanceconfig.xml
echo Backing Up WEB Catalog...
if not exist D:\BIBackup1\Tues\Cataloge mkdir D:\BIBackup1\Tues\Cataloge
xcopy /E /Y /Q D:\OracleBIData\web\catalog D:\BIBackup1\Tues\Cataloge
echo Backing Up WEB Resources...
if not exist D:\BIBackup1\Tues\res mkdir D:\BIBackup1\Tues\res
xcopy /E /Y /Q D:\OracleBIData\web\res D:\BIBackup1\Tues\res
echo Backup Complete!


start D:\OracleBI\oc4j_bi\bin\oc4j.cmd -start
sleep 10
net start "Oracle BI Server"
sleep 10
net start "Oracle BI Java Host"
sleep 10
net start "Oracle BI Presentation Server"

@exit
-----------------


For Restore:

File Name: restore_bi.bat

Following are the contents of this file:
-----------------
@echo off

java.exe -jar D:\OracleBI\oc4j_bi\j2ee\home\admin.jar ormi://localhost:23791 oc4jadmin admin1 -shutdown force
sleep 10
net stop "Oracle BI Presentation Server"
sleep 10
net stop "Oracle BI Java Host"
sleep 10
net stop "Oracle BI Server"


echo Restoring Repository file…
xcopy /s/Y/Q D:\BIBACKUP\myrepository.rpd D:\oraclebi\server\Repository
echo Restoring all Configuration files…
xcopy /s/Y/Q D:\BIBACKUP\NQSConfig.INI D:\oraclebi\server\Config
xcopy /s/Y/Q D:\BIBACKUP\DBFeatures.INI D:\oraclebi\server\Config
echo Restoring XML Files…
del D:\oraclebidata\web\config\instanceconfig.xml
del D:\oraclebidata\scheduler\config\instanceconfig.xml
del D:\oraclebi\web\msgdb\l_en\messages\uimessages.xml
xcopy /s/Y/Q D:\BIBACKUP\xmlp-server-config.xml D:\oraclebi\xmlp\XMLP\Admin\Configuration
xcopy /s/Y/Q D:\BIBACKUP\principals.xml D:\oraclebi\xmlp\XMLP\Admin\Security
xcopy /s/Y/Q D:\BIBACKUP\security.xml D:\oraclebi\xmlp\XMLP\Admin\Security
xcopy /s/Y/Q D:\BIBACKUP\datasources.xml D:\oraclebi\xmlp\XMLP\Admin\DataSource
if not exist D:\oraclebi\web\msgdb mkdir D:\oraclebi\web\msgdb
xcopy /s/Y/Q D:\BIBACKUP\msgdb D:\oraclebi\web\msgdb
xcopy /s/Y/Q D:\BIBACKUP\credentialstore.xml D:\oraclebidata\web\config
copy D:\BIBACKUP\web_instanceconfig.xml D:\oraclebidata\web\config\instanceconfig.xml
copy D:\BIBACKUP\scheduler_instanceconfig.xml D:\oraclebidata\scheduler\config\instanceconfig.xml
echo Restoring web catalog.Please wait….
xcopy /s/Y/Q D:\BIBACKUP\Cataloge D:\oraclebidata\web\catalog
echo Restoring Up WEB Resources.Please wait….
xcopy /S/Y/Q D:\BIBACKUP\res D:\oraclebi\web\app\res
echo Restore Complete!


start D:\OracleBI\oc4j_bi\bin\oc4j.cmd -start
sleep 10
net start "Oracle BI Server"
sleep 10
net start "Oracle BI Java Host"
sleep 10
net start "Oracle BI Presentation Server"


@exit
------------------

Cheers ;)

BI Cache Planning for Optimal Performance

-          Cache can be set to never expire for optimal performance.

-          Whole cache can be purged over nightly using a scheduled task.

-          Following policies can be adopted to refresh cache.

o   Using ‘Cache Persistence Time’ to purge cache at any desired time:

1.       Using this approach, “Cache Persistence Time” can be set to purge cache of any report based upon subject area. For example, purge all reports that are using ‘Charges’ subject area.

2.       This approach is specific to BI Server layer and cannot be done from Presentation layer.

3.       The drawback of this approach is that it cannot refresh cache automatically once gets purged.

4.       This will cause delay in report loading at first load.

o   Using ibot to purge and refresh cache:

1.       Using this approach, ibot can be scheduled early morning or any specific time to purge and refresh cache of any Report or all reports on a dashboard page.

2.       This will not cause any delay in report loading at first load.

3.       Any user that has ibot access can do it.

-          Following two mechanisms can be adopted to view real time data. However performance will always be poor in both cases because report query will be getting results on the fly directly from the OLTP database.

o   A Report Designer can set following parameters in the Advance tab (Prefix input) of report. Saving a report with these parameters will always bypass cache.

SET VARIABLE DISABLE_CACHE_HIT=1, DISABLE_CACHE_SEED=1, LOGLEVEL=7;

o   A Report Designer can also use following functions in the report to view real time data all the time.

CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE

Cheers ;)
 

Cache Seeding using iBot - Things to Consider

Following are some important points for Seeding Cache using iBot:      

1-      iBot can seed both the dashboard page and the report.

2-      If delivery content is report then cache is built for the whole report regardless of "is prompted" values, the only exception is the report level hardcoded filters.

3-      If delivery content is dashboard page then cache seeding through iBot depends on the default values in the prompt. If there is no default value then the cache is made for the whole report otherwise cache will only be created for default values set in the prompts.

4-      I recommend not to add report in the conditional request while creating iBot.

Cheers :)

Setting Up BI Publisher & MS Office Integration

Follow the below steps to setup OBIEE 10g BI Publisher and integration with MS Office

Step 1- Open BI Publisher
    From Server:
    goto -> start -> all programs -> oracle business Intelligence -> BI Publisher
    From workstation:
    http://domainname.com:9704/xmlpserver/
    login:
    user = Administrator
    pass = Administrator
    (above password will be valid if no other password has been setup)

Step 2- Goto Admin tab -> select user from security center
    Change the password of Administrator User if required.
    Create user same as presentation server user. if user name is same as presentation server user then BI publisher will be linked to that user automatically. In our case UMIRZA is presentation server user.
    user = umirza
    pass = ******

    Assign Required Roles:
    select all roles for umirza user

    Now UMIRZA user is linked with Presentation Server

Step 3- Connecting to Data source
   
     Can choose either option (i) ot (ii)

    i)    Link Presentation Server to BI Publisher:

        a)    Open URL
            http://domainname.com:9704/xmlpserver/
            goto -> Admin -> Oracle BI Presentation Services          

        b)    Update Data Source: Oracle BI EE
            In BI Publisher
            goto Admin > JDBC > Update Data Source: Oracle BI EE
          

      ii)    Link to a database: [For Read Only views]
      
        connect to database and create user.

        <script start>
        CREATE USER bipub
          IDENTIFIED BY bipub
          DEFAULT TABLESPACE USERS
          TEMPORARY TABLESPACE TEMP
          PROFILE DEFAULT
          ACCOUNT UNLOCK
        /
          GRANT READ_ONLY_ROLE TO bipub
        /
          GRANT UNLIMITED TABLESPACE TO bipub
        /
          ALTER USER bipub DEFAULT ROLE ALL
        /
          GRANT CREATE SESSION TO bipub
        /
          GRANT CREATE TABLE to bipub
        / 
        <script end>

        Goto Admin > JDBC > Add Data Source

        Data Source Name  = MyDataSource
 
        *Connection String = jdbc:oracle:thin:@server:port:sid
         For example
            Connection String = jdbc:oracle:thin:@dbhostname:1523:mysid

            Connection String = jdbc:oracle:thin:@(DESCRIPTION =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = dbhostname)(PORT = 1523))
                                (CONNECT_DATA =
                                  (SERVICE_NAME = mysid)
                            )
                        )
         
        * Username   = bipub
        * Password   = bipub
         
        * Database Driver Class = oracle.jdbc.driver.OracleDriver

Step 4- Oracle BI Publisher Desktop Setup
    *Need to download Template Builder  
    For that Open Presentation Server and login using UMIRZA
        Goto -> Products -> BI Publisher
        then Goto Reports > My Folders
        Click on "Template Builder" for download "BIPublisherDesktop.exe" and save it on local machine

        Run "BIPublisherDesktop.exe" with Admin privs

        Possible Errors:
        Setup could not find Microsoft .NET Framework 2.0 on your machine.
        BI Publisher Desktop will not run and setup cannot continue without this component installed.

        Goto: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=25150
        Download "dotnetfx35.exe"
        I have downloaded it and placed at [D:\Sequel - Work\BI\Deployment\BI Publisher\dotnetfx35_SP1.exe]
      
        Run "dotnetfx35.exe" and install "Microsoft .NET Framework"
        Restart Computer.

        While Log-On into MS Word Publisher Addin, you may encounter following errors:

        1) "ActiveX - Runtime Error 429"
        to get rid of it
        remove and reinstall dotnetfiles using setup.exe
        located at <C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\DotNetInstallFiles>

        2) Oracle BI Server is incompatible with Office 2010 and can give error  
        The window error is:
        "There was an error '-2147467261 (80004003)' at runtime: Object reference not set to an instance of an object."
        OR
        "There was an error at runtime: Access is denied"
        You can get rid of it in an unofficial way:
        In all windows user profiles, rename *.exd files to *.exd_bak and restart MS Word.

        3) Msg "Please enter the correct address for your report server. If you don't know the address please contact your IT department"


Step 5- Download Sample RTF file     "category_sales_and_profits.rtf"
    Goto http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/bipub/bip_biee/Category%20Sales%20and%20Profits.rtf
    Save the file.
  

Step 6- Testing BI Publisher Desktop
        Open MS Word
        Goto Add-Ins > Oracle BI Publisher > Log On

        Enter following credentials  
        user = umirza
        pass = ******
        Report Server = http://domainname:9704/xmlpserver

        <If Log On successful, then you are done with setup>

Cheers ;)