Saturday, July 19, 2008

OBIEE managing the cache: Emptying/Purging the cache Part 2

Besides using batch / shell command to purge the cache (see: http://obiee101.blogspot.com/2008/03/obiee-manage-cache-part-1.html), it’s also possible to do this automatically based on events in your ETL process.

This is done by having the OBIEE cache manager polling one or more a ‘EVENTS’ table(s). What you do is have a predefined reference table in a each database you are using. (If you have 1 complete ETL process you could log all table changes in one table.)

The Event table should have the following structure (copied from the OBIEE documentation )

Note 1: You can play around with the column names as long as they imported in this order in the OBIEE repository.

Note 2: Although UpdateTime needs to be unique, you can not put a UNIQUE constrain on this column. OBIEE copies the row back into the table during the purging process.

A script for an Oracle version would look like this:

CREATE TABLE OBIEE_UPDATE_EVENTS
(
UPDATE_TYPE INTEGER DEFAULT 1 NOT NULL,
UPDATE_TIME DATE DEFAULT SYSDATE NOT NULL,
DB_NAME VARCHAR2(40 BYTE),
CATALOG_NAME VARCHAR2(40 BYTE),
SCHEMA_NAME VARCHAR2(40 BYTE),
TABLE_NAME VARCHAR2(40 BYTE) NOT NULL,
OTHER VARCHAR2(80 BYTE) DEFAULT NULL
)
/

Imported in the OBIEE repository it would look like this:
Now assign this table as an event table:

Tools > Utilities > Oracle Event Tables
Drag the event table(s) to the right side
Set the polling frequency, press OK.

Let’s fill the cache with a simple query: (SH schema)

Check if the cache is actually filled:
Fill the Event table: (Based on the SH schema)

INSERT INTO obiee_update_events
(db_name, catalog_name, schema_name, table_name
)
VALUES (’orcl SH’, NULL, ‘SH’, ‘Sales’
)
/
COMMIT
/

Look in the cache directory after the polling time:

Note 3: OBIEE removes each entry from the EVENT table independent if it was a success or not.
Note 4: OBIEE only logs failures in the NQServer.log. Usually found in ..OracleBI\server\Log

Till Next Time

10 comments:

MC said...

John,

Great post - more descriptive than the documentation. You mention that OBIEE inserts an identical record and then deletes them both. I've noticed the same thing in my testing. Do you happen to know why it does that?

John Minkjan said...

Hi Matt,

Looks to me that they are using it as a temporary storage place. The inserted record has some extra information in the OTHER field....
Probably a "historical" development

MC said...

Yes, when OBIEE inserts a record, OTHER is populated with the name of our BI server. That is the only difference between the two records. In fact, I had to remove the unique index on UPDATE_TIME to get this to work properly. Does yours work with the unique index on UPDATE_TIME?

John Minkjan said...

Hi Matt,

I removed all the indexes in the script during development, forgot to take them out of the blog posting. (Fixed now). Anyway the EVENTS table doesn't grow that much during the "polling" period that an index gives great performance benefits

Regards

John

Ziky said...

Hi John and Matt,

Can we do the reverse of the same process. Can we seed the cache with OBIEE report queries using C Prompt. Our case is a bit tricky. We cant use IBots as we dont have license for it and we want the OBIEE admin to run these queries at the application server and seed these for different users without knowing their passwords.

John Minkjan said...

Hi Zicky,

Have a look at the RUNAS options in the OBIEE documentation.

regards John

Anonymous said...

If I understand this correctly it purges BI_Server Cache not Presentation Services cache and if I am correct is there a similar method for purging presentaion services chache.

John Minkjan said...

Yes, you are correct.

Info about the presentationserver cache can be found here:
http://obiee101.blogspot.com/2008/12/obiee-bypassing-presentation-web-cache.html
and
http://obiee101.blogspot.com/2008/11/obiee-presentation-services-cache.html

regards John

Anonymous said...

It wont work we need to write a trigger in data base to update the event pool table ...... :-)

Unknown said...

i dont have datawarehouse we are building reports on oltp for current day the query should go to database and yesterday it sholuld bring from cache can anyone suggest your comments