What BI Server does?
It parse the incoming SQL statements, store and retrieve query results in cache, and generate physical SQL and MDX statements retrieve the data from the various data sources.
Oracle BI Server acts as "analytical middleware" that provides the unified, enhanced data layer over various data sources and allows the user to query the data using logical dimensional model made up of business data items and hierarchies.
Out of the box, for BI server there is no additional configuration settings needed. Most of configuration setting is done in "Fusion Middleware Enterprise Manager" in OBIEE 11g.
Oracle long term goal/strategies is to expose all configuration settings via either of following any one
- Fusion middleware control's Web console or via
- Configuration JMX MBeans.
- Weblogic Scripting Tool (WLST).
But even though most of the configuration settings are done through above mentioned options. But in current release of OBIEE 11.1.1.6.0, there are some configuration settings still need to be set using configuration files, within OBI file system.
For Oracle BI Server, file that configures these setting is NQSConfig.INI file.
The file NQSConfig.INI contains some significant configuration settings:
- Fine-tuning configuration settings for caching.
- Sorting order and sorting locale for BI server
- Time and date display Format
- Whether GLobaly Uniquer Identifies (GUIDs) are updated when ta user logs into dashboard
- Usage tracking.
Note: From OBIEE 11.1.1.6.0 the usage tracking is controlled through a JMX MBeans attribute and should not be manually altered in the configuration file.
Location of NQSConfig.INI file
[middleware_home]\instances\instances1\config\OracleBIServerComponent\coreapplication_obis1\NQSConfig.INI
Additional BI Server Functions and Management Tasks:
- Configuring and managing the query cache
- Setting up usage tracking
- Creating aggregates through the Aggregate Persistence Wizard
- Enabling the Write Back.
Overview of BI Server Query Cache:
BI server query cache can improve performance of analyses that request commonly used data. When query cache enabled results generated by analysis are stored in cache and then available for use by other analysis requesting same data or data could be partially satisfied or derived from data stored in cache.
To determine the following points one has to do control settings of cache.
- To determine how big the cache can get
- How many entries it can store
- How hard the BI server should search cache to find results that can be useful to query
- To know the data in cache is out of date or stale data compared to source system data.
How Does Query Caching Work?
Oracle BI Server query cache has 3 main elements in its architecture.
- Cache Storage space
- Cache metadata
- Cache detection in query compilation
Using query cache it will be faster than querying the underlying data source because cache is stored locally and already contains result that are precomputed, prejoined and prefiltered.
The contents of query cache are stored in set of files on Oracle BI Server file system.
Note:
- We can move the BI Server's cache files to solid state disk (SSD). If have one, giving the potentially faster access to these files and ever faster queries.
- When scale-out or scale up Oracle BI installations, so that we can have more than one BI server components in Oracle BI instance, we can also enable GLOBAL CACHE that can be accessed by all BI Servers in cluster.
The Oracle BI Server Query cache follows a series of rules to maximize the chance of achieving CACHE HIT. Cache Services performs the following actions when processing a query sent to Oracle BI server.
- When oracle BI server receive a logical SQL query from end user analysis via ODBC client. It parses sql statement and if query caching is enabled. It checks the CHACHE METADATA to determine whether data it requires can be satisfied by query cache / derived from data held in query cache.
- If found the required data is retreieved from query cache and then passed to Final processing stage. Here other activities like sorting, ordering is done and passed to final end user/calling application.
- If the data not found in cache metadata, a query plan is generated, queries issued to data ware house/ data sources, retuned data is processed to form final data set. BI server again checks, if QUERY CHACHE enabled ? if yes, results are stored in cache.
What are the factors or rules followed by cache to decide cache hit?
- The select query all the columns either should be present in cached query or it can be derivable (compute using columns of query cache).
- The WHERE clause condition mush match with cached query or subset of cached query.
- Time series functions or external aggregation functions must exactly match in both terms of columns selected and filter used.
- Query must match with same number of (full set of) logical tables that cached query used. This is to avoid – If any LTS is missed, the missing/extra join condition will alter the number of rows returned.
- If Session variables used the values of those variable must match those of cached query
- The incoming cached query needs to have equivalent join conditions.
- Query must contain compatible aggregation levels, must either be same as each other or cache query must be aggregated at a level that can be rolled up to provide the correct result of incoming query.
- The ORDER BY columns used in incoming query needs to use the columns of SELECT query list of cached query.
When BI Server will decide it should not check CACHE?
- If query has columns related to CURRENT_TIMESTAMP, CURRENT_TIME, POPULATE etc.
- Query using the table, which is marked for non-cacheable in RPD , physical layer.
- The query result set would be too big to store in cache, as defined by DATA_STORAGE_PATHS and MAX_ROWS_PER_CACHE_ENTRY.
- The query cancelled by user or by time-out.
WHEN SHOULD USE QUERY CACHE ?
- Whenever in data Ware house the data loaded infrequently. The data load process can include will purge the query cache, so that all new chache hits take place
- Where users typically request same or similar data. So it will maximizing the cache hits.
WHAT ARE OTHER CACHES IN ORACLE BI ?
- The Oracle BI Presentation Services cache, which stores the result of queries and used them. Ex: To avoid delay/round trips to BI server when end user switch between the screens on dashboard.
- The WEB BROWSER CACHE, which stores images and files locally to avoid round trips to web server used by OBI.
Note: Presentation Services cache can be configured through settings in INSTANCECONFIG.XML file.
How to achieve this ?
By changing Presentation Server cache settings. Add and entry within the
<ServerInstance></ServerInstance> tags in file. The parameters are listed below.
- MaxEntries
- MaxExpireMinutes
- MinExpireMinutes
- MinUserExpireMinutes
Once added, restart all Presentations Server components to register the new settings.
Benefits & Drawbacks of using QUERY CACHING ?
Benefits:
- Improve performance by analysis, dashboards. Its check the data in local stored files rather hitting databases to retrieve the results.
- Reduce network traffic to access the data locally.
- Reduce the work of source databases, by avoiding need to retrieve same data again n again
Reduced the work of BI server, otherwise it has to recombine the data set from retrieved data.
Drawbacks
- One has to purge the cache every time new ETL is ran. Otherwise stale data in cache.
- Retrieving the data from cache will cost additional CPU and I/O load on server.
- If the caches takes more space in file system, additional space needed like [SSD or RAM]
- Queries to seed the cache may have additional task in ETL load on source db. It may require extra time after the ETL process to perform the seeding.
MANAGING QUERY CACHE:
- Enabling, disabling and tuning the running query cache.
- Purging cache to remove the stale data.
- Seed Cache to "preload" with frequently accessed data.
Enabling, Disabling Cache:
- Open Fusion Middleware Control,
- Select "Business Intelligence" on left hand side, -> Capacity management tab -> Performance tab.
- Enable the cache.
- Activate changes, Restart the BI components.
Setting Cache Parameters in NQSConfig.INI
- DATA_STORAGE_PATHS: Defines the directories that will be sued to stre the cache files and maximum size that they can reach.
MAX_ROWS_PER_CACHE_ENTRY: This setting determines, per query, the maximum number of rows that query can have to allowed into CACHE.
Default value: 100000.
POPULATE_AGGREGATE_ROLLUP_HITS: Default as NO.
The queries satisfied from cache and then aggregate that data are not stored in cache. If this parameter is set to YES, queries roll up data from cache will stored in ached after the query completes.
- USE_ADVANCED_HIT_DETECTION: By default is set to NO. When you know the results are stored in cache but that cache retrieval process does not use them. If this value set to YES tells the BI server will look into cache, at the expense of CPU overhead.
- MAX_SUBEXPR_SEARCH_DEPTH:
- DISABLE_SUBREQUEST_CACHING
There parameters are edited to take effect these changes, Restart BI Server components.
PURGING QUERY CACHE:
- Configuring Cache Persistence Times for Individual Physical objects.
- Purging the cache Using Oracle BI Administrator Tool.
- Programmatically run the ODBC command to purge cache entries at the end of DW load process.
- Purging the cache Using Event Polling Tables.
Configuring Cache Persistence Times for Individual Physical objects:
- You can set at individual physical object, cache persistence times for each object. The entries in cache for this object will be purged according to timetable.
- This type of cache mgmt for more frequently updated source systems.
Purging the cache Using Oracle BI Administrator Tool.
The cache can be managed by opening the Administration Tool and connecting online to the OBIEE server. Go to Manage -> Cache:
This contains some very interesting information about each entry such as when it was created, who created it, what logical SQL it contains, how many rows are in the entry, how many times the entry has been used since it was created etc.
To purge cache entries select them in the right hand window and right click, choose purge from the context menu:
Programmatically purging the cache
Oracle provides a number of ODBC calls which you can issue to the OBIEE server over the Oracle provided ODBC driver to purge the cache these are:
SAPurgeCacheByQuery(x) - This will purge the cache of an entry that meets the logical SQL passed to the method.
SAPurgeCacheByTable(x,y,z,m) - This will purge the cache for all entries for the specified physical table where:
x = Database name
y = Catalog name
z = Schema name
m = Table name
SAPurgeAllCache() - This will purge all entries from the query cache.
These ODBC calls can be called by creating a .sql file with the call in it, i.e.:
Call SAPurgeAllCache();
Then create a script to call it, on Windows you can create a .cmd file with the contents:
nqcmd -d "AnalyticsWeb" -u Administrator -p [admin password] -s [path to sql file].sql -o [output log file name]
This will use nqcmd to open an ODBC connection to the ODBC AnalyticsWeb and then run your .sql file which will purge the cache.
I have used this many times as the only step in a custom Informatica WF which is then added as a task in the DAC and set as a following task for an Execution Plan. This way after the ETL the cache will be purged automatically and there is no risk of stale cache entries.
Purging the cache Using Event Polling Tables
Another method of purging the cache programmatically by event polling tables.
By event polling is way to notify the BI server that one or more physical tables have been updated. The even table is a physical table that can resides in relational database accessible to oracle BI server.
The event table is updated by SQL INSERT statements of data load or load scripts or programs that populate the databases. The INSERT statements add one row each whenever physical table modified.
To create an event polling table, run the Repository Creation Utility (RCU) to create the Business Intelligence Platform (BIPLATFORM) schemas in your physical database. RCU creates an event polling table called S_NQ_EPT.
create table S_NQ_EPT (
Update_Type NUMBER not null,
Update_TS DEFAULT SYSDATE not null,
Database_Name VARCHAR2 null,
Catalog_Name VARCHAR2 null,
Schema_Name VARCHAR2 null,
Table_Name VARCHAR2 not null,
Other_Reserved VARCHAR2 NULL
);
The Oracle BI Server must have read and write permission on the event polling table. The server reads the event table at specified intervals to look for changed data. Applications add rows to the event table when database tables are modified (for example, during a load operation). When there are rows in the event table, there is changed data in the underlying databases. The server then invalidates any cache entries that correspond to the changed physical tables and periodically deletes obsolete rows from the event table. The next time it checks the event table, the process repeats.
Event tables do not need to be exposed in the Business Model and Mapping layer.
- Open Admin Tool and open repository in online or offline.
- Select File – Import – Metadata – enter connection etails to database that contains cache polling table.
- Import the table S_NQ_EPT tablw with BIPLATFORM schema.
- To register table as repositorys cache polling table, select – Tools – Utilities – Oracle BI Event Tables, click Execute buttons.
- Define the polling frequency. [stale cache entries are purged automatically at the specified polling intervals.]
Test
INSERT
INTO
S_NQ_EPT
(
update_type,
update_ts,
database_name,
catalog_name,
schema_name,
table_name,
other_reserved
)
VALUES
(
1,
sysdate,
'orcl SH',
NULL,
'SH',
'PRODUCTS',
NULL
)
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13628>>):
select T4660.UPDATE_TYPE as c1,
T4660.UPDATE_TS as c2,
T4660.DATABASE_NAME as c3,
T4660.CATALOG_NAME as c4,
T4660.SCHEMA_NAME as c5,
T4660.TABLE_NAME as c6
from
S_NQ_EPT T4660
where ( T4660.OTHER_RESERVED in ('') or T4660.OTHER_RESERVED is null )
minus
select T4660.UPDATE_TYPE as c1,
T4660.UPDATE_TS as c2,
T4660.DATABASE_NAME as c3,
T4660.CATALOG_NAME as c4,
T4660.SCHEMA_NAME as c5,
T4660.TABLE_NAME as c6
from
S_NQ_EPT T4660
where ( T4660.OTHER_RESERVED = 'oracle10g' )
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13671>>):
insert into
S_NQ_EPT("UPDATE_TYPE", "UPDATE_TS", "DATABASE_NAME", "CATALOG_NAME", "SCHEMA_NAME", "TABLE_NAME", "OTHER_RESERVED")
values (1, TIMESTAMP '2010-06-16 00:44:53', 'orcl SH', '', 'SH', 'PRODUCTS', 'oracle10g')
+++Administrator:fffe0000:fffe0003:----2010/06/16 00:45:33
-------------------- Cache Purge of query:
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/administrator/cache/answers_with_product';
SELECT Calendar."Calendar Year" saw_0, Products."Prod Category" saw_1, "Sales Facts"."Amount Sold"
saw_2 FROM SH ORDER BY saw_0, saw_1
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13672>>):
select T4660.UPDATE_TIME as c1
from
S_NQ_EPT T4660
where ( T4660.OTHER_RESERVED = 'oracle10g' )
group by T4660.UPDATE_TS
having count(T4660.UPDATE_TS) = 1
+++Administrator:fffe0000:fffe001c:----2010/06/16 00:45:33
-------------------- Sending query to database named ORCL (id: <<13716>>):
delete from
S_NQ_EPT where S_NQ_EPT.UPDATE_TS = TIMESTAMP '2010-06-16 00:44:53'
