OBIEE 11g Architecture

OBIEE 11g Architecture

At this high level, components in OBIEE 11g can be divided into two types:

• Java Components, the parts of OBIEE that are written in J2EE and run directly in the application server. These components include BI Publisher, the BI Office plugin, the BI Security Service, BI SOA Services, BI Action Services and the JMX MBeans that are used for configuring OBIEE

• System Components, which were implemented as Windows services in 10g, and include the BI Server, BI Presentation Server, BI Scheduler and BI Cluster Controller

Web Logic Server

This part of OBIEE system contains Admin Server and Managed Server. Admin server is responsible for managing the start and stop processes for Managed server. Managed Server comprises of BI Plugin, Security, Publisher, SOA, BI Office, etc.

Node Manager

Node Manager triggers the auto start, stop, restart activities and provides process management activities for Admin and Managed server.

Oracle Process Manager and Notification Server (OPMN)

OPMN is used to start and stop all components of BI system. It is managed and controlled by Fusion Middleware Controller.

Oracle BI System Components

These are non-Java components in an OBIEE system.

Oracle BI Server

This is the heart of Oracle BI system and is responsible for providing data and query access capabilities.

BI Presentation Server

It is responsible to present data from BI server to web clients which is requested by the end users.

Scheduler

This component provides scheduling capability in BI system and it has its own scheduler to schedule jobs in OBIEE system.

Oracle BI Java Host

This is responsible for enabling BI Presentation server to support various Java tasks for BI Scheduler, Publisher and graphs.

BI Cluster Controller

This is used for load balancing purposes to ensure that the load is evenly assigned to all BI server processes.

File Locations in OBIEE 11G

File Locations in OBIEE 11G

Start BI Services (parameter:  start_all)

..\\\instances\instance1\bifoundation\OracleBIApplication\coreapplication\StartStopServices.cmd start_all

Stop BI Services (parameter:  stop_all)

..\\\instances\instance1\bifoundation\OracleBIApplication\coreapplication\StartStopServices.cmd stop_all

Sample Application Data and Repository

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample

BI Repository(RPD)

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

Usage tracking and Marketing Segmentation Scripts

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema

BI Server Cache file

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache

BI Presentation Catalog

..\\\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog

Clustered Component

..\\\instances\instance1\bifoundation\OracleBIClusterControllerComponent\coreapplication_obiccs1

Scheduler Components (Scheduler schema etc.)

..\\\instances\instance1\bifoundation\OracleBISchedulerComponent\coreapplication_obisch1

BI ODBC , Javahost and other components

..\\\instances\instance1\bifoundation

Start Weblogic Server (startWebLogic.cmd)

..\\\user_projects\domains\bifoundation_domain

BI Config Files

..\\\instances\instance1\config

Repository Config files (NQSConfig.INI,DBFeatures.INI etc)

..\\\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

Catalog config files(instanceconfig.xml,credentialstore.xml etc)

..\\\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

Scheduler Catalog config files (instanceconfig.xml)

..\\\instances\instance1\config\OracleBISchedulerComponent\coreapplication_obisch1

Cluster Config files

..\\\instances\instance1\config\OracleBIApplication\coreapplication

OPMN Startup (parameter:  startall)

..\\\instances\instance1\bin opmnctl startall

OPMN Stop (parameter:  stopall)

..\\\instances\instance1\bin opmnctl stopall

BI PATH Initialisation

..\\\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd

Weblogic Admin and Managed Server start/stop scripts(startWebLogic.cmd,startManagedWebLogic.cmd etc.)

..\\\user_projects\domains\bifoundation_domain\bin

BIPublisher repository and Config files

..\\\user_projects\domains\bifoundation_domain\config\bipublisher

FMW Security Config files (system-jazn-data.xml)

..\\\user_projects\domains\bifoundation_domain\config\fmwconfig

BI Components Log files

..\\\instances\instance1\diagnostics\logs

BI Server Log files (NQserver.log , NQQuery.log etc.)

..\\\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1

BI Presentation Server Log files (sawlog)

..\\\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1

Installation Log files

..\\\logs

Date Calculation Formulas OBIEE

Date Calculation Formulas

In OBIEE, we cannot perform an arithmetic operation on a DATE or TIMESTAMP. These are considered Non-numeric types.

To calculate difference between two dates, OBIEE provides a TIMESTAMPDIFF function. The syntax for using the function is:

TIMESTAMPDIFF(interval, first_date, second_date)

First_date and second_date have to be valid values in the TIMESTAMP type. Interval is a specified value.  Valid values for INTERVAL are:

• SQL_TSI_SECOND
• SQL_TSI_MINUTE
• SQL_TSI_HOUR
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

If the dates are not in a valid TIMESTAMP format, use CAST function to convert values into TIMESTAMP type. See example below:

TIMESTAMPDIFF(SQL_TSI_DAY, CAST(VALUEOF("MY_DATE_COLUMN") as TIMESTAMP), CURRENT_DATE)

1)    First Day of the Previous Year
-------------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year

2) First Day of the Current Year
----------------------------------
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This calculation returns the first day of the year by deducting one less than the total number of days in the year.

3) First Day of the Next Year
---------------------------------------
TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))
From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.

4) First Day of the Previous Month
-----------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

 From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.

5) First Day of the Current Month
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.

6) First Day of the Next Month
-------------------------------------
TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.

7) First Day of Current Quarter
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.

8) Last Day of the Previous Month
--------------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.

9) Last Day of Current Month
---------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.

10) Last Day of the Next Month
-----------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.

11) Last Day of Previous Year
------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,
CURRENT_DATE)) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.

12) Last Day of Current Year
------------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.

13) Last Day of the Next Year
----------------------------------------
TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

14) Last Day of Current Quarter
--------------------------------------------
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.

15) Number of days between First Day of Year and Last Day of Current Month
------------------------------------------------------------------------------------------------------------------
 TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))) 

For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

16) Lat Date of Week:

TIMESTAMPADD(SQL_TSI_DAY,DAYOFWEEK(Current_Date)*-1,TIMESTAMPADD(SQL_TSI_WEEK, 1, Current_date))

17) First Date of Year:

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

18) Last Date of Year:

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

How to Manage OBIEE services Manually

How to Manage OBIEE services Manually

Start your Oracle Database

Start Weblogic Node Manager
ORACLE_BIEE_HOME/wlserver_10.3/server/bin/startNodeManager.cmd

Steps to Manually Start Oracle BIEE 11g

1. ORACLE_BIEE_HOME/user_projects/domains/bifoundation_domain/bin/startWebLogic.cmd

2. ORACLE_BIEE_HOME/user_projects/domains/bifoundation_domain/bin/startManagedWebLogic.cmd bi_server1

3. ORACLE_BIEE_HOME/instances/instance1/bin/opmnctl startall

Steps to Manually Stop Oracle BIEE 11g

1. ORACLE_BIEE_HOME/instances/instance1/bin/opmnctl stopall

2. ORACLE_BIEE_HOME/user_projects/domains/bifoundation_domain/bin/stopManagedWebLogic.cmd bi_server1

3.ORACLE_BIEE_HOME/user_projects/domains/bifoundation_domain/bin/stopWebLogic.cmd

Informatica PowerCenter 9 Architecture

Informatica PowerCenter 9 Architecture

Before we learn how to use Informatica, we need to understand what are the important components of Informatica and how it works.

Informatica tool consists of following services & components

1.Repository Service – Responsible for maintaining Informatica metadata & providing access of same to other services.

2.Integration Service – Responsible for the movement of data from sources to targets

3.Reporting Service - Enables the generation of reports

4.Nodes – Computing platform where the above services are executed

5.      Informatica Designer - Used for creation of mappings between source and target

6.      Workflow Manager – Used to create workflows and other task & their execution

7.      Workflow Monitor – Used to monitor the execution of workflows

8.      Repository Manager – Used to manage objects in repository

Informatica Repository:
The informatica repository is at the center of the informatica suite.

·        The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools.

·        Metadata is data about data which include information such as source definitions , target definitions, mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings for sources and targets. 

·        The repository also stores administrative information such as usernames and passwords, permissions and privileges, and product version. 

·        Use repository manager to create the repository. The Repository Manager connects to the repository database and runs the code needed to create the repository tables. These tables stores metadata in specific format the informatica server, client tools use. 

Informatica Repository: The informatica repository is at the center of the informatica suite. ·        The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. ·        Metadata is data about data which include information such as source definitions , target definitions, mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings for sources and targets.  ·        The repository also stores administrative information such as usernames and passwords, permissions and privileges, and product version.  ·        Use repository manager to create the repository. The Repository Manager connects to the repository database and runs the code needed to create the repository tables. These tables stores metadata in specific format the informatica server, client tools use.   Informatica Components:  Server Components: Repository Service Integration Service Client Components: Repository Manager Designer Workflow Manager Workflow Monitor Server Components  1. Repository Server: Ø      The Repository Server manages the metadata in the repository database. Ø      The Repository Server manages connections to the repository from client applications. Ø      The Repository Service is a separate, multi-threaded process that retrieves, inserts, and updates metadata in the repository database tables. The Repository Service ensures the consistency of metadata in the repository. 2. Integration Service: Ø      The Integration Server reads mapping and session information from the repository. It extracts data from the mapping sources and stores the data in memory while it applies the transformation rules that you configure in the mapping. The Integration Server loads the transformed data into the mapping targets. Ø      Manages the scheduling and execution of workflows Ø      The Integration Server can start and run multiple workflows concurrently. It can also concurrently process partitions within a single session.

Seeding the Cache IN OBIEE11G

Seeding the Cache

I runs an OBIEE11G dashboard, and the results are added to the cache so that when Bill runs the same dashboard Bill gets a great response rate because his dashboard runs straight from cache. Kinda sucks for Bob though, because his query ran slow as it wasn’t in the cache yet. What’d be nice would be that for the first user on a dashboard the results were already in cache. There are several options for seeding the cache. These all assume you’ve figured out the queries that you want to run in order to load the results into cache.

1. Run the analysis manually, which will return the analysis data to you and insert it into the BI Server Cache too.
2. Create an Agent to run the analysis with destination set to Oracle BI Server Cache (For seeding cache), and then either:
   1. Schedule the analysis to run from an Agent on a schedule
   2. Trigger it from a Web Service in order to couple it to your ETL data load / cache purge batch steps.
3. Use the BI Server Procedure SASeedQuery (which is what the Agent does in the background) to load the given query into cache without returning the data to the client. This is useful for doing over JDBC/ODBC/Web Service (as discussed for purging above). You could just run the Logical SQL itself, but you probably don’t want to pull the actual data back to the client, hence using the procedure call instead.

4. SET VARIABLE SAW_SRC_PATH='/users/weblogic/Cache Test 01',  
   DISABLE\_CACHE\_HIT=1:SELECT  
      0 s_0,  
      "A - Sample Sales"."Time"."T02 Per Name Month" s_1,  
      "A - Sample Sales"."Base Facts"."1- Revenue" s_2  
   FROM "A - Sample Sales"  
   ORDER BY 1, 2 ASC NULLS LAST  
   FETCH FIRST 5000001 ROWS ONLY  

Checking the RPD for Cacheable Tables

The RPD Query Tool is great for finding objects matching certain criteria. However, it seems to invert results when looking for Cacheable Physical tables - if you add a filter of Cacheable = false you get physical tables where Cacheable is enabled! And the same in reverse (Cacheable = true -> shows Physical tables where Cacheable is disabled)

Cache Location

The BI Server cache is held on disk, so it goes without saying that storing it on fast (eg SSD) disk is a Good Idea. There's no harm in giving it its own filesystem on *nix to isolate it from other work (in terms of filesystems filling up) and to make monitoring it super easy.

Use the DATASTORAGEPATHS configuration element in NQSConfig.ini to change the location of the BI Server cache

Managing Performance Tuning and Query Caching In Obiee11g

OBIEE11G BI Server Cache Management:-

What is Cache?

Analytics systems reduce database calls by creating a ‘Cache’ of data on the same machine as the Analytics engine.  Caching has a small cost in terms of disk space to store and a small number of I/O transactions on the server, but this cost is easily outweighed by the improvement in response time.

Types of Cache in OBIEE

There are two different types of cache in Oracle Business Intelligence Enterprise Edition (OBIEE):

1. The Query Cache (BI Server Cache): The OBIEE Server can save the results of a query in cache files and then reuse those results later when a similar query is requested. This type of cache is referred to as Query cache.
2. The Presentation Server Cache: When users run analytics, Presentation server can cache the results

Why do we need to Purge Cache?

The main reason to implement the cache purge process is, when we have data that is being updated frequently in the warehouse and if there is a query cache present for the query that hits on the database, then the numbers may vary from that of the database. In such cases if you purge the cache the issue gets resolved.

Steps to Automatically Purge BI Server Cache

[Assuming a Oracle Business Intelligence Enterprise Edition (OBIEE) server and Data warehouse Administration Console (DAC) server are installed on the Linux machines.]

Here are the few steps to purging BI SERVER cache automatically in Oracle Business Intelligence Applications.  For our purposes, the OBIEE server and DAC server are on two different Linux machines.

Step 1

As the OBIEE and DAC servers present are two different servers we need to login to OBIEE server from DAC server.  This can be achieved by using the SSH command in Linux.

Step 2

As the DAC server requires password-less login, we either need to setup the RSA keys or use the ‘sshpass’ command to login to the OBIEE server.  Where RSA keys are most preferable by the client personal.  Setting up RSA keys is mostly the work of a DBA. 

Step 3

Once the login without using a password is completed, we now need to move to the OBIEE HOME and create two files: purge.txt and purgecache.sh

We also must provide the privileges required for execution.

Thus after every ETL load the purging of the cache can be achieved.

We can check the output of the purging process by navigating to the dac/log folder and check for post_etl.sh.log file.

The Above steps help us purge the BI SERVER cache automatically after every DAC load.

Presentation Server Cache Management

The Presentation server cache can be managed in the instanceconfig.xml file and it’s a one-time setup by adding the tags shown below (just above the

at the end of the file).  Don’t bother to automate the purge of Presentation server cache.

Web Cache
1440 1440 1440

120 180 180 120 600
 
1440  
1440  

This one only for BI Server to Presentation server expire only. 10

A cache management strategy sounds grand doesn’t it? But it boils down to two things:

1. Accuracy – Flush any data from the cache that is now stale
2. Speed – Prime the cache so that as many queries get a hit on it, first time

Maintaining an Accurate Cache

Every query that is run through the BI Server, whether from a Dashboard, Answers, or more funky routes such as custom ODBC clients or JDBC, will end up in cache. It’s possible to “seed” (“prime”/“warmup”) the cache explicitly, and this is discussed later. The only time you won’t see data in the cache is if (a) you have BI Server caching disabled, or (b) you’ve disabled the Cacheable option for a physical table that is involved in providing the data for the query being run.

Purging Options

So we’ve a spread of queries run that hit various dimension and fact tables and created lots of cache entries. Now we’ve loaded data into our underlying database, so we need to make sure that the next time a user runs an OBIEE query that uses the new data they can see it. Otherwise we commit the cardinal sin of any analytical system and show the user incorrect data which is a Bad Thing. It may be fast, but it’s WRONG….

We can purge the whole cache, but that’s a pretty brutal approach. The cache is persisted to disk and can hold lots of data stretching back months - to blitz all of that just because one table has some new data is overkill. A more targeted approach is to purge by physical database, physical table, or even logical query. When would you use these?

• Purge entire cache - the nuclear option, but also the simplest. If your data model is small and a large proportion of the underlying physical tables may have changed data, then go for this
• Purge by Physical Database - less brutal that clearing the whole cache, if you have various data sources that are loaded at different points in the batch schedule then targeting a particular physical database makes sense.
• Purge by Physical Table - if many tables within your database have remained unchanged, whilst a large proportion of particular tables have changed (or it’s a small table) then this is a sensible option to run for each affected table
• Purge by Query - If you add a few thousand rows to a billion row fact table, purging all references to that table from the cache would be a waste. Imagine you have a table with sales by day. You load new sales figures daily, so purging the cache by query for recent data is obviously necessary, but data from previous weeks and months may well remain untouched so it makes sense to leave queries against those in the cache. The specifics of this choice are down to you and your ETL process and business rules inherent in the data (maybe there shouldn’t be old data loaded, but what happens if there is? See above re. serving wrong data to users). This option is the most complex to maintain because you risk leaving behind in the cache data that may be stale but doesn’t match the precise set of queries that you purge against.

Which one is correct depends on

1. your data load and how many tables you’ve changed
2. your level of reliance on the cache (can you afford low cache hit ratio until it warms up again?)
3. time to reseed new content

If you are heavily dependant on the cache and have large amounts of data in it, you are probably going to need to invest time in a precise and potentially complex cache purge strategy. Conversely if you use caching as the ‘icing on the cake’ and/or it’s quick to seed new content then the simplest option is to purge the entire cache. Simple is good; OBIEE has enough moving parts without adding to its complexity unnecessarily.

Note that OBIEE itself will perform cache purges in some situations including if a dynamic repository variable used by a Business Model (e.g. in a Logical Column) gets a new value through a scheduled initialisation block.

Performing the Purge

There are several ways in which we can purge the cache. First I’ll discuss the ones that I would not recommend except for manual testing:

1. Administration Tool -> Manage -> Cache -> Purge. Doing this every time your ETL runs is not a sensible idea unless you enjoy watching paint dry (or need to manually purge it as part of a deployment of a new RPD etc).
2. In the Physical table, setting Cache persistence time. Why not? Because this time period starts from when the data was loaded into the cache, not when the data was loaded into your database. 
   An easy mistake to make would be to think that with a daily ETL run, setting the Cache persistence time to 1 day might be a good idea. It’s not, because if your ETL runs at 06:00 and someone runs a report at 05:00, there is a going to be a stale cache entry present for another 23 hours. Even if you use cache seeding, you’re still relinquishing control of the data accuracy in your cache. What happens if the ETL batch overruns or underruns?
   The only scenario in which I would use this option is if I was querying directly against a transactional system and wanted to minimise the number of hits OBIEE made against it - the trade-off being users would deliberately be seeing stale data (but sometimes this is an acceptable compromise, so long as it’s made clear in the presentation of the data).

So the two viable options for cache purging are:

1. BI Server Cache Purge Procedures
2. Event Polling Table

BI Server Cache Purge Procedures

These are often called “ODBC” Procedures but technically ODBC is just one - of several - ways that the commands can be sent to the BI Server to invoke.

As well as supporting queries for data from clients (such as Presentation Services) sent as Logical SQL, the BI Server also has its own set of procedures. Many of these are internal and mostly undocumented (Christian Berg does a great job of explaining them here, and they do creep into the documentation here and here), but there are some cache management ones that are fully supported and documented. They are:

• SAPurgeCacheByQuery
• SAPurgeCacheByTable
• SAPurgeCacheByDatabase
• SAPurgeAllCache
• SAPurgeCacheBySubjectArea (>= 11.1.1.9)
• SAPurgeCacheEntryByIDVector (>= 11.1.1.9)

Seeding the Cache

http://dineshng03.blogspot.in/2017/03/seeding-cache-in-obiee11g.html