OBIEE11g: Purging Cache Using the Agents in Obiee11g

Purge Cache Using Agents in Obiee11g:

Create a new database by doing a right click on the empty space in the physical layer as shown in the below screenshot

Now you can see a window with the database properties as shown in the below screenshot

 Give a name to the database and select ODBC Basic from the Database drop down.Check in the option Allow direct database requests by default. Please follow the below screenshot.

 Click on Connection Pools tab and then click on Add(+) to create a connection pool for the database

Give the name for the connection pool(In my case i have given the name as PurgeCache) and set the values as per your requirement.

Now click on the Permissions tab then you can see a window as follows

 

Give the access for the users and application roles as per the requirement as shown in the below screenshot 

Click on OK then click on Check Out

Give the User Name and password in the connection pool, Once you enter the password then it will ask you to retype the password. Please follow the below screenshots.

Now you can see the connection pool is successfully created

Check the RPD consistency and then save the repository

Deploy the new  repository in the BI Server (Enterprise Manager) and then restart the services.

Now login to the presentation services and create a new report by using Create Direct Database Request as shown in the below screenshot.

Give the Connection Pool Name and type the following query in the query box

Call SAPurgeAllCache();

  Now Click on Results tab  and then save the report

 

 

We need to create an agent to scheduled this report now. So go and create one new agent to schedule this report.