NQCMD and Cache

In one of my project, customer did not had the OBIEE Scheduler setup, so iBot was not an option to seed the cache. But still customer want to seed the cache and clear it after the ETL run finish.

NQCMD is one of the option to achive this. We used NQCMD utility to clear the cache and then seed it by running the logical queries for the Reports. For example

working_dir='/oraclebi/product/OracleBI'
cd $working_dir
cd setup
. ./user.sh
. ./common.sh
. ./sa-cli.sh
cd ..
nqcmd -d AnalyticsWeb -u Administrator -p SADMIN -s cmd.txt -o out.txt

In the cmd.txt, use the following.

{call SAPurgeAllCache()};
SELECT Time."Fiscal Quarter" saw_0, "Facts - AR Payments Due Aging"."AR Due1-30 Amount" saw_1, "Facts - AR Payments Due Aging"."AR Due 31-60 Amount" saw_2, "Facts - AR Payments Due Aging"."AR Due 61-90 Amount" saw_3, "Facts - AR Payments Due Aging"."AR Due 90+ Amount" saw_4, "Facts - AR Payments Due Aging"."Total AR Due Amount" saw_5, "Facts - AR Payments Due Aging"."AR Due 1-30 Amount to Total Due %" saw_6, "Facts - AR Payments Due Aging"."AR Due 31-60 Amount to Total Due %" saw_7, "Facts - AR Payments Due Aging"."AR Due 61-90 Amount to Total Due %" saw_8, "Facts - AR Payments Due Aging"."AR Due 90+ Amount to Total Due %" saw_9, "Facts - AR Payments Due Aging"."AR Due Amount to Total %" saw_10 FROM "Financials - AR Overview" WHERE ((Time."Fiscal Quarter" <= VALUEOF(CURRENT_FSCL_QUARTER)) AND (Time."Fiscal Quarter" > VALUEOF(FSCL_QTR_YEAR_AGO))) ORDER BY saw_0;
SELECT Time."Fiscal Year" FROM "Financials - AR Overview" WHERE Time."Fiscal Year" BETWEEN valueof(FILTER_FSCL_FROM_YEAR) AND valueof(FILTER_FSCL_TO_YEAR) ORDER BY 1 DESC;
SELECT Time."Fiscal Quarter" FROM "Financials - AR Overview" WHERE Time."Fiscal Year" BETWEEN valueof(FILTER_FSCL_FROM_YEAR) AND valueof(FILTER_FSCL_TO_YEAR) ORDER BY 1 DESC;

The cmd.txt first clear the cache and then seed it by running all the logical queries for each report customer has.

If you have BI on Unix Platform, you can setup this as Cron job.