Cubing — Data and member caching

If more data is stored in the caches, queries to the cube do not need to retrieve results as often from the underlying database, providing faster query response time. However, if the cache grows too large, it will use more memory on the machine, potentially slowing the performance for all users.

Using the options available in the cube properties page for a particular cube, you can improve the performance of the cube. To determine the optimal size for your system, you will need to experiment and consider memory resources, user load, and query load. Depending on the user and query loads, you can determine the best data and member cache sizes.

Data cache

The data cache stores cube cells fetched from the relational database. Once loaded into the data cache, the stored data is shared among concurrent and subsequent queries when available. See Changing the runtime configuration properties of a cube for details about data cache options.

Member cache

The member cache stores dimension metadata (members) and can be tuned to either completely or partially cache members. For member caching, there are two modes available: static caching (default) and dynamic caching.

When static caching is used, all dimension members are read from the relational data source and preloaded into memory during cube startup.

When dynamic caching is selected, dimension members are read from the relational data source and stored in compressed data files in a user-specified location on the system. Disk space usage is proportional to the number of members in the cube. The amount of memory used for the member cache is proportional to the number of members and the size of each member. You can create a user-defined value for the size of the in-memory data cache. For information about where to set the user-specified location for the member cache when you create a cube server, refer to Creating a cube server.

When a cube is started or refreshed, the member cache files of the cube are generated, and any previous files are overwritten. As long as the cube is running, dimension members are dynamically read into memory from the dimension files on an as-needed basis. The number of members in the in-memory member cache is managed by the user-specified parameters for the settings of the cube cache. For information about where to set the member cache settings, see Changing the runtime configuration properties of a cube .