Preventive Service Planning
In InfoSphere™ Warehouse 9.7, the technology of the Cubing Services engine was improved to allow for better performance. The data cache settings were modified, which impacts the real-time OLAP capability. To achieve near-real-time OLAP capability, you can modify settings in the Administration Console or change the data cache refresh interval from the command line or clear the cache with the mdxcmd tool.
The following settings can impact the data cache:
Member cache file system location: This is the only setting in the Administration Console that has any effect on the data cache. You can specify the location of the member cache and the data cache separately. The location for the member cache is used as the root of the location for all cubes that are supported by a cube server.
Refreshing the cache: By default, a cube server only checks for scheduled jobs every 5 minutes. From the Administration Console, the lowest latency that can be achieved with the default repository setting is 5 minutes. This setting can be modified using the cscmd command line tool to edit the cron_interval repository setting if you want the cube server to check more often. Instructions for how to modify this setting are found in the Workaround 1 section.
Data cache cell caching limitations: Data cache management was simplified in Version 9.7 so that you do not need to directly manage how much data is cached in memory. The V9.7 Cubing Services data cache is a disk-backed in-memory cache. The in-memory cache will grow as required, but will also release its memory to satisfy other memory allocation requirements of the server. The entire cache is disk-backed. If a value is required from the cache and is not present in-memory, the value is loaded into memory from the disk-backed cache.
The data cache settings are not changed if you change the cell caching limitations in the Administration Console. The Cubing Services engine always caches all cell values, and therefore it is not possible to limit the number of cells that are cached. If you specify a limit in the Administration Console, the setting is ignored, because the cube server manages the data cache.
To improve performance, the size of the Cubing Services data cache is not configurable in InfoSphere Warehouse V9.7. However, you can achieve near real-time OLAP by using one of two methods:
1. Continually refreshing the cube, which rebuilds the member cache and clears the data cache. This option is shown in the Workaround 1 section of this document.
2. Clearing the data cache by using the mdxcmd command line tool, which does not rebuild the member cache. This option is shown in the Workaround 2 section of this document.
When you refresh a cube, the member cache is rebuilt, and the data cache is cleared. The refresh process allows the cube server to re-populate the data cache with newer information, providing more current data.
Note: To get the best result from your refresh, make sure that the Refresh settings value (scheduled refresh time) is greater than the cube load time.
Workaround 1: cscmd
You can use the cscmd tool to edit the cron_interval repository setting to specify how often to refresh the cube.
Note: This workaround clears the data cache and rebuilds the member cache, which can cause performance issues. To avoid rebuilding the member cache, use the mdxcmd tool, as described in Workaround 2.
To achieve near real-time OLAP capability, use the cscmd tool to edit the repository setting to check more frequently for cube updates:
1. Prepare the cscmd tool:
a. Open a command window and use the cd command to change to the following directory:
b. Create an empty file called .ibm_support_only by running the following command:
UNIX/Linux: #touch .ibm_support_only
a. To display the cube servers that are defined in the repository, run the following command at the prompt:
To achieve a result that does not rebuild the member cache, use the mdxcmd tool to clear the data cache.
Workaround 2: mdxcmd
You can incorporate this mdxcmd workaround into a script to automate the refresh process.
To avoid rebuilding the member cache, use the mdxcmd tool to clear the data cache:
1. If mdxcmd is not configured, configure the tool:
a. Locate the
csdatasources.xml file in the following directory:
b. Update the csdatasources.xml file by adding a data source that corresponds to each of your cube server instances. The following example configures a data source for the cubeserver1 cube server that runs on port 9000 with the default XMLA port of 9002 for the user “db2admin” whose password is “password”:
cube_server_instance="cubeserver1" desc="cubserver1 – first cube server instance"
server="localhost" port="9000" xmla_http_port="9002"
c. If InfoSphere Warehouse was installed in a directory that is not the default directory, update the mdxcmd file by running the following commands:
set JAVA_HOME= JVM_directory
set CUBINGSERVICES_HOME= CS_directory
where JVM_directory is the directory where you installed the JVM, and CS_directory is the directory where Cubing Services is installed for InfoSphere Warehouse.
a. Open a command prompt.
b. Change directories to the CubingServices\bin directory.
c. Start the mdxcmd tool for the cube server with the following command:
For example, to clear the Sales cube, run the following command: