Configuring near-real-time refresh intervals for cube servers

Preventive Service Planning


Abstract

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.

Content

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.


Real-time OLAP

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.

Procedure

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:
        InfoSphere_Warehouse_Home_Directory \CubingServices\bin
      where InfoSphere_Warehouse_Home_Directory is the directory where you installed InfoSphere Warehouse.
      b. Create an empty file called .ibm_support_only by running the following command:
        Windows®: notepad .ibm_support_only
        UNIX/Linux: #touch .ibm_support_only
      c. Type the following command on the command line:
        cscmd edit
      The cscmd tool starts. After the tool is started, you see the following entry on the command line:
        repository>
    2. Use the cscmd tool to edit the cron_interval configuration parameters:
      a. To display the cube servers that are defined in the repository, run the following command at the prompt:
        +show servers
      b. Update the value for the cron_interval parameter:
        +update server server_name  cron_interval value
      where server_name is the name of the cube server, and value is the value of the desired refresh time in seconds. For example, to update the cron_interval value to 1 minute on a cube server named DS1, issue the following command:
        +update server DS1  cron_interval 60
      c. Optional: To enable tracing for the cron job, run the following command:
        +update server server_name  cronsys_trace   value
      where server_name is the name of the cube server, and value is a value of "true" or "false". For example, if you want to enable tracing for the DS1 cube server, issue the following command:
        +update server DS1  cronsys_trace true

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:
          InfoSphere_Warehouse_Home /CubingServices/bin
        where InfoSphere_Warehouse_Home is the directory where you installed InfoSphere Warehouse.
        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”:
          <DataSource
              cube_server_instance="cubeserver1" desc="cubserver1 – first cube server instance"
              server="localhost" port="9000"  xmla_http_port="9002"
              uid="db2admin" pwd="password"
            />
        Note: Be sure to control the access to the csdatasources.xml file, since the password is stored in clear text.

        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.
      2. Run the mdxcmd command to clear the data cache:
        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:
          mdxcmd.bat Cube_Server_Name
        where Cube_Server_Name is the name of the cube server that contains the cube that you want to refresh. For example, if you want to use the mdxcmd tool on the DS1 cube server, run the following command:
          mdxcmd.bat DS1
        d. Use the +clear command to clear the data cache. Use the following command, and make sure that the name of the cube is in brackets:
          +clear [ cube_name ];
        where cube_name is the name of the cube for which you want to clear the data cache.
        For example, to clear the Sales cube, run the following command:
          +clear [Sales];

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

InfoSphere Warehouse
DWE - Cubing Services

Software version:

9.5, 9.5.1, 9.5.2, 9.7.0, 9.7.1

Operating system(s):

Linux, Windows

Software edition:

Edition Independent

Reference #:

1424947

Modified date:

2010-07-16

Translate my page

Machine Translation

Content navigation