1. Administering Oracle Analytics Server
  2. Scale and Deploy for High Availability and Performance
  3. Manage Performance Tuning and Query Caching
  4. Configure the Data Source Cache

Configure the Data Source Cache

To improve performance for data blending, you must configure a data source cache. You do this either using a database or on the file system.

Create a Data Source Cache on the File System

You can store your data source cache data on the file system for direct access by BI Server. This is the default configuration. This may be adequate for small- to medium-sized data sources, such as spreadsheet files that you have uploaded. For larger data sources, configuring the data source cache on a database may provide better performance.

The file system-based data source cache works as follows:

  1. BI Server receives logical SQL statement against the data source.

  2. BI Server retrieves the metadata of the data source. Using the metadata's content, BI Server checks if there's any existing data source cache entry that can be used to answer the query instead of requesting data from the data source.

  3. For any data source that doesn’t have an existing data source cache entry, BI Server starts a thread to load the data source’s data into the cache.

  4. The logical SQL statement (and all other concurrent logical SQL statements that require data from the same data source) waits for the thread to finish.

  5. Once the thread's execution is complete, any subrequest that depends on the newly-seeded data source cache entry uses the cache.

Configure the data source cache on the file system:

  1. Make the following changes to the NQSConfig.INI file in the XSA_CACHE section:
    [XSA_CACHE]
ENABLE = YES;

# The schema and connection pool where the XSA data is cached. 
# Set PHYSICAL_SCHEMA to ""."" to use file-based XSA cache. 
# This indicates that a file-based XSA cache should be used by BI Server and the value of the CONNECTION_POOL parameter should be ignored.
PHYSICAL_SCHEMA = "".""; # "<Database>"."<Schema>"; 
CONNECTION_POOL = "".""; # "<Database>"."<Connection Pool>";

# The path to the location where cache data files is persisted. 
# This is only used if file-based XSA cache is configured. 
# If a relative path is specified, it is relative to: 
# BI_DOMAIN/servers/obisn 
STORAGE_DIRECTORY = "storage"; # location where the cache data files are stored

# The maximum space allocated in the schema for the cache data.
MAX_TOTAL_SPACE = 5 GB;

# The maximum size allowed for a single XSA cache entry. 
MAX_CACHE_ENTRY_SIZE = 200 MB;

# The path to the location where descriptor files of the cache data is persisted. 
# If a relative path is specified, it is relative to: 
# BI_DOMAIN/servers/obisn 
DESCRIPTOR_STORAGE_PATH = "xsacache"; # location where the cache metadata files are stored

# The number of threads available for seeding XSA cache entries. 
CACHE_SEED_THREAD_RANGE = 0-40; 
CACHE_SEED_THREAD_STACK_SIZE = 0; # default is 256 KB (32 BIT mode), 1 MB (64 BIT mode), 0 for default

  2. Restart BI Server.

  3. Validate that the data source cache is configured correctly, check the obis1-diagnostic.log. Upon server startup, look for entries similar to the following:
    [2017-01-13T14:41:30.715-07:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: ] [tid: 81c8] [101043] External Subject Area cache with internal storage is started successfully using configuration from the repository with the logical name Star.
[2011-01-13T14:41:30.716-07:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: ] [tid: 81c8] [101017] External Subject Area cache has been initialized. Total number of entries: 0 Used space: 0 bytes Maximum space: 107374182400 bytes Remaining space: 107374182400 bytes. Cache table name prefix is XC.

Once you enable the data source cache, it stores the data from the data source on the file system as defined in the STORAGE_DIRECTORY parameter in the XSA_CACHE section of NQSConfig.INI. It stores the metadata on the local file system in the directory specified by the DESCRIPTOR_STORAGE_PATH parameter under the XSA_CACHE section in NQSConfig.INI. To improve performance, put your data source data and data source cache metadata files on a RAM disk.

Create a Data Source Cache on a Database

For larger data sources, configuring the data source cache on a database may provide better performance.

The database-based data source cache works as follows:

  1. BI Server receives a logical SQL statement against the data source.

  2. BI Server retrieves the metadata of the data source. Using the metadata's content, BI Server checks if there's any existing data source cache entry that can be used to answer the query instead of requesting data from the data source. Any subrequest that can use the data source cache is rewritten to the native physical SQL of the cache database.

  3. For any data source that doesn’t have an existing data source cache entry, BI Server starts a thread to load the data source’s data into the cache database. Each data source translates to a single database table containing all columns form the data source.

  4. The logical SQL statement (and all other concurrent logical SQL statements that require data from the same data source) waits for the thread to finish.

  5. Once the thread's execution is done, any subrequest that depends on the newly-seeded data source cache entry is rewritten to the native physical SQL of the cache database.

You can choose between the two following supported data source cache database setups:

Configure the Data Source Cache on a Different Database than the Data Warehouse

  1. Create the physical objects that represent the cache database in your semantic model. This includes a new database object, a new physical schema object, and a new connection pool. For this example, our database object is named XSACache, our connection pool is named CP, and a new physical schema object is named XSA_CACHE.

    • The user name used in the connection pool must be the same as the name of the schema.

    • The user specified in the connection pool must have the necessary privileges to run DDLs and DMLs against tables in the schema.  For example, on Oracle, the user must have at least the CREATE TABLE privilege.

  2. Update parameters under the XSA_CACHE section in NQSConfig.INI to point to the right schema and connection pool. 

    [XSA_CACHE]
ENABLE = YES;

# The schema and connection pool where the XSA data is cached. 
# Set PHYSICAL_SCHEMA to ""."" to use file-based XSA cache. 
PHYSICAL_SCHEMA = "XSACache"."XSA_CACHE"; # "<Database>"."<Schema>"; 
CONNECTION_POOL = "XSACache"."CP"; # "<Database>"."<Connection Pool>";

# The path to the location where descriptor files of the cache data is persisted. 
# If a relative path is specified, it is relative to: 
# BI_DOMAIN/servers/obisn 
DESCRIPTOR_STORAGE_PATH = "xsacache"; # location where the cache metadata files are stored

  3. Restart BI Server.

  4. To validate that the data source cache is configured correctly, check the obis1-diagnostic.log. Upon server startup, look for entries similar to the following:
    [2017-01-13T14:41:30.715-07:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: ] [tid: 81c8] [101001] External Subject Area cache is started successfully using configuration from the repository with the logical name Star.
[2011-01-13T14:41:30.716-07:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: ] [tid: 81c8] [101017] External Subject Area cache has been initialized. Total number of entries: 0 Used space: 0 bytes Maximum space: 107374182400 bytes Remaining space: 107374182400 bytes. Cache table name prefix is XC2627531314.

Once you enable the data source cache, it stores the data for the data source in the database. However it stores the metadata on the local file system in the directory specified by the DESCRIPTOR_STORAGE_PATH parameter under the XSA_CACHE section in NQSConfig.INI. To improve performance, put your data source cache metadata files on a RAM disk.

Configuring the Data Source Cache on the Same Database as the Data Warehouse

This configuration improves query performance because joins can be pushed down to the data source instead of being run internally by BI Server.

  1. Create the physical objects that represent the cache database in your semantic model under the data warehouse database object. This includes a new physical schema object and a new connection pool. For this example, the new connection pool is called XSA Cache Connection Pool, and the physical schema object is called XSA_CACHE.

    The approach is to have a dedicated connection pool for data source cache seeding and purging while allowing the data warehouse connection pool to query data source cache tables created in a separate schema. The data source cache schema should ideally have its own tablespace to simplify management.

    • The user name used in the new connection pool must be the same as the name of the new schema.

    • The user specified in the new connection pool must have the necessary privileges to run DDLs and DMLs against tables in the new schema.  For example, on Oracle, the user must have at least the CREATE TABLE privilege.

    • The user specified in the original data warehouse connection pool must have the necessary privileges to SELECT tables in the new schema. For example, on Oracle, the user in the existing data warehouse connection pool must have at least the SELECT ANY TABLE privilege.

    • The data source cache connection pool must not be the primary connection pool under the database object. In other words, the data source cache connection pool must be ordered after the data warehouse connection pool that's used for data warehouse queries.

  2. Update parameters under the XSA_CACHE section in NQSConfig.INI to point to the right schema and connection pool. 

    [XSA_CACHE]
ENABLE = YES;

# The schema and connection pool where the XSA data is cached. 
# Set PHYSICAL_SCHEMA to ""."" to use file-based XSA cache. 
PHYSICAL_SCHEMA = "Oracle Data Warehouse"."Catalog"."XSA_CACHE"; # "<Database>"."<Schema>"; 
CONNECTION_POOL = "Oracle Data Warehouse"."XSA Cache Connection Pool"; # "<Database>"."<Connection Pool>";

# The path to the location where descriptor files of the cache data is persisted. 
# If a relative path is specified, it is relative to: 
# BI_DOMAIN/servers/obisn 
DESCRIPTOR_STORAGE_PATH = "xsacache"; # location where the cache metadata files are stored

  3. Restart BI Server.

  4. To validate that the data source cache is configured correctly, check the obis1-diagnostic.log. Upon server startup, look for entries similar to the following:
    [2017-01-13T14:41:30.715-07:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: ] [tid: 81c8] [101001] External Subject Area cache is started successfully using configuration from the repository with the logical name Star.
[2011-01-13T14:41:30.716-07:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: ] [tid: 81c8] [101017] External Subject Area cache has been initialized. Total number of entries: 0 Used space: 0 bytes Maximum space: 107374182400 bytes Remaining space: 107374182400 bytes. Cache table name prefix is XC2627531314.

Once you enable the data source cache, it stores the data for uploaded files in the database. However it store the metadata on the local file system in the directory specified by the DESCRIPTOR_STORAGE_PATH parameter under the XSA_CACHE section in NQSConfig.INI. To improve performance, Oracle recommends that you put your data source cache metadata files on RAMDisk.

Manage the Data Source Cache

Once you've enabled the data source cache, you may need to delete individual cache entries or the entire cache.

Removing Data Source Cache Entries

Oracle BI Server provides ODBC-extension functions for purging cache entries. The format of the stored procedure is as follows:
call SAPurgeXSACache('<XSA_PATH>', '<XSA_TABLE>');

<XSA_PATH> is the path to a data source definition. This is the value used within an XSA() clause in a logical SQL. This parameter also supports the wildcard character % that can be used to purge all data source cache entries in the system.

<XSA_TABLE> is a table name within a data source. This is the value that follows an XSA() clause in a logical SQL. This parameter also supports the wildcard character % that can be used to purge cache entries of all tables within a data source definition.

For example, assume a data source cache entry is generated using a query: SELECT XSA('weblogic'.'Sample Order Lines')."Columns"."Product Category" FROM XSA('weblogic'.'Sample Order Lines').

  • To purge the data source cache entry: call SAPurgeXSACache('''weblogic''.''Sample Order Lines''', 'Columns');

  • To purge all data source cache entries in the system: call SAPurgeXSACache('%', '%');

Manually Clean Up the Cache on the File System

You can remove data source cache entries manually using the following steps:

  1. Stop BI Server.

  2. Delete the files in the directories specified by the DESCRIPTOR_STORAGE_PATH and STORAGE_DIRECTORY parameters under the XSA_CACHE section in NQSConfig.INI.

Manually Cleaning Up the Cache in a Database

You may need to drop all tables under a data source cache schema for the following reasons:

  • To clean-up an existing schema to be used by a new BI Server installation, replacing an old installation.

  • To start the data source cache in a clean slate during maintenance down-time.

To clean the cache:

  1. Ensure that there are no running BI Servers using this schema. If there are such servers running when the tables are removed, these servers are left in an inconsistent state and it results in data source queries that fail due to missing table errors until these servers are rebooted.

  2. From an SQL client, log in to the database as the user of the data source cache schema.

  3. Run the following PL/SQL:
    BEGIN  
FOR i IN (SELECT table_name FROM user_tables where table_name like 'XC%')  
LOOP  
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS PURGE');  
END LOOP;  
END;
    Note that data source cache table names are prefixed with XC.

  4. Delete the files in the directory specified by the DESCRIPTOR_STORAGE_PATH parameter under the XSA_CACHE section in NQSConfig.INI.