OBIEE: Call SAPurgeCacheByDatabase

So the reason I am investigating the ODBC Function extensions is that we are trying to tie this into our existing ETL process. After load, and within a designated time frame, we'd like to clear the cache for a given set of tables. Fortunately, all those tables exist in a single database. Unfortunately, I'm wasting your time by putting these into separate posts. :)

First, nothing up my sleeve.



So you understand the mapping, I made a pretty picture for that (it's not readily available in the cache manager)



I go off and run the reports (no need for screenshots of those).

Refresh the cache manager



So now I have cache entries for both "databases" (in quotes because they are actually the same physical database, just 2 separate connections).

I then create the SQL file and put the following:

CALL SAPurgeCacheByDatabase( 'local-sh' );

Then I just follow the same procedure as SAPurgeCacheAll()

nqcmd -d AnalyticsWeb -u Administrator -p Administrator -s sapurgecachebydatabase.sql

The output is almost identical as well

-------------------------------------------------------------------------------
          Oracle BI Server
          Copyright (c) 1997-2009 Oracle Corporation, All rights reserved
-------------------------------------------------------------------------------

CALL SAPurgeCacheByDatabase( 'local-sh' )
CALL SAPurgeCacheByDatabase( 'local-sh' )
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
RESULT_CODE  RESULT_MESSAGE

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
1            [59118] Operation SAPurgeCacheByDatabase succeeded!

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------
Row count: 1
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
------------------------------

Processed: 1 queries

And voila! The Sales cache has been cleared.