Sunday, March 16, 2008

OBIEE Manage the cache part 2

Seeding / Filling the cache
  • Now why should you want to do that? OBIEE is not a database, so why would this be an advantage?
  • If you have a "slow” connection to your source database, maybe because it's on the other side of the world.
  • If you have "slow" aggregation views.
  • If you can "predict" what 90% percent of your queries look like .


From your user statistics you can pick up the top 10 of your queries, Check if you can make them "dynamic" by changing fixed date into dynamic dates.
Put the following in a .txt file in your maintenance directory.


// Load cache by Query

// John Minkjan CIBER Nederland

// FileName: LoadCache.txt

// executed by cmd string:

// nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s c:\obiee\mscripts\LoadCache.txt
// Query 1

SELECT TBLFUELCONSUMPTION.LICENSEPLATE, TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM Trucks WHERE TBLFUELCONSUMPTION.MEASUREDATE >= TIMESTAMPADD(SQL_TSI_YEAR, -2,NOW());


// Query 2

SELECT TBLFUELCONSUMPTION.LICENSEPLATE, TBLFUELCONSUMPTION.MEASUREDATE, TBLFUELCONSUMPTION.FUELCONSUMPTION FROM
Trucks WHERE TBLFUELCONSUMPTION.MEASUREDATE <= TIMESTAMPADD(SQL_TSI_YEAR, -3,NOW());


// The Queries are separated with a ;

Till Next Time

4 comments:

Ritesh said...

Hi John,

Is there any way to refresh the cache for a table for for a given user instead of giving queries.

We have lots of queries and would be big task to put all together as suggested here.

I would appreciate if you can suggest table / user based refresh option.

Also, can we call iBot routine from command line? Say I have created and iBot routine with name "monthly reports". Are there any APIs to call this from command line?

Thanks,
Ritesh

John Minkjan said...

@Rits

q1: It depends if a the user credentials are used/stored in the query (..where user = 'user_name')

q2: Never used it in that way. Put your question on the OTN forums, maybe they have a solution

regards

John

Narasimha Madhuvarsu said...

Ritesh,

you can call iBot from command line like this:

#!/bin/ksh
BI_DIR=/oracle/OracleBI/server/Bin64
ANA_INSTALL_DIR=/oracle/OracleBI
TEXTDIR=/export/home/oracle/
cd Oracle/OracleBI/server/bin
. $ANA_INSTALL_DIR/setup/common.sh
. $ANA_INSTALL_DIR/setup/sa-init64.sh
saschinvoke -u Administrator/Administrator -j 10

Sri said...

How to seed cache when we have data level/row level security implemented?
Will seeding the cache for whole dataset work?