Performance Tips

Result Caching and Cube Updates

By default, the system maintains and uses a result cache for any cube that contains more than 64,000 records (one group). When you update a cube by synchronizing or rebuilding it, or when you manually process changes to records in the base class using %ProcessFact() and %DeleteFact(), parts of the result cache are considered invalid and are cleared. (The details depend upon options in the cube definition; see Cache Buckets and Fact Order, later in this page.) Therefore, it is not generally desirable to update the cubes constantly.

The result cache works as follows: Each time a user executes a query (via the Analyzer for example), the system caches the results for that query. The next time any user runs that query, the system checks to see if the cache is still valid. If so, the system then uses the cached values. Otherwise, the system re-executes the query, uses the new values, and then caches them. The net effect is that performance improves over time as more users run more queries.

Cache Buckets and Fact Order

As noted earlier, for large data sets, the system maintains and uses a result cache. When users evaluate an MDX query, the system computes and caches aggregate values that it later reuses whenever possible. The cache includes intermediate values which the system computes for each cache bucket, as well as the final value. (A cache bucket is a set of contiguous records in the fact table.)

The system invalidates and deletes the cached results for a cache bucket when a fact in that bucket changes. Therefore, it is beneficial to consolidate the facts which are most likely to change into as few cache buckets as possible, so that you can retain cached results for the remainder. However, this is not possible if the system retrieves records from your source class in an arbitrary order when it builds the cube’s fact table.

As a solution, Business Intelligence allows you to specify the Initial build order option for a cube (see Other Cube Options). For example: if changes to the records in your source class occur primarily among the most recent ones, Initial build order allows you to ensure that the system always builds the fact table in order by the age of the records, with the oldest records first. That way, the caches for cache buckets which contain the older records would not be made invalid when data changes in the more recent records. (By contrast: if old records and new ones were mixed together throughout the fact table, changes to data in the new records could potentially invalidate caches for any or all of the cube’s cache buckets.)

For more information, see How the Analytics Engine Works.

Initializing the Cache

As noted earlier, when users evaluate MDX queries, the system computes and caches aggregate values that it later reuses whenever possible. This caching means that the more users work with Business Intelligence, the more quickly it runs. (For details, see How the Analytics Engine Works.)

To speed up initial performance as well, you can run commonly-used MDX queries after you build or update a cube. Doing so caches the results for those queries and guarantees quick access the next time that a user runs them.

Alternative Option: Precomputing the Cell Cache

Although it is generally simpler and more efficient to speed up the initial performance of the system by running queries in advance (as described previously), in rare situations you may prefer to do so by precomputing and caching the specific aggregate values that your pivot tables use. To do so:

1. Specify an additional XData block (CellCache) that specifies cube cells that should be precomputed and cached.

2. After you build the cube, invoke the utility method to precomputes the cube cells which you have specified.

Defining the Cell Cache

Your cube class can contain an additional XData block (CellCache) that specifies cube cells that can be precomputed and cached, which speeds up the initial performance of Business Intelligence. The following shows an example:

/// This xml document defines aggregates to be precomputed.
XData CellCache [ XMLNamespace = " http://www.intersystems.com/deepsee/cellCache" ]
{
<cellCache xmlns= "http://www.intersystems.com/deepsee/cellCache" >
   <group name= "BS">
      <item>
         <element >[Measures].[Big Sale Count]</element >
      </item>
   </group>
   <group name= "G1">
      <item>
         <element >[UnitsPerTransaction].[H1].[UnitsSold]</ element>
         <element >[Measures].[Amount Sold]</element >
      </item>
      <item>
         <fact >DxUnitsSold</fact >
         <element >[Measures].[Amount Sold]</element >
      </item>
   </group>
</cellCache >
}

The <cellCache> element is as follows:

• It must be in the namespace "http://www.intersystems.com/deepsee/cellCache"

• It contains zero or more <group> elements.

Each <group> element is as follows:

• It has a name attribute, which you use later when specifying which groups of cells to precompute.

• It contains one or more <item> elements.

Each <item> element represents a combination of cube indexes and corresponds to the information returned by %SHOWPLAN. An <item> element consists of one or more <element> elements.

An <element> can include one or more of either of the following structures, in any combination:

<fact>fact_table_field_name</fact>

Or:

<element>mdx_member_expression</element >

Where:

• fact_table_field_name is the field name in the fact table for a level or measure, as given by the factName attribute for that level or measure.

• mdx_member_expression is an MDX expression that evaluates to a member. This can be either a member of a level or it can be a measure name (each measure is a member of the special MEASURES dimension).

  This expression cannot be a calculated member.

Note:

Each group defines a set of intersections. The number of intersections in a group affects the processing speed when you precompute the cube cells.

Precomputing the Cube Cells

To precompute the aggregate values specified by a <group>, use the %ComputeAggregateGroup() method of %DeepSee.UtilsOpens in a new tab. This method is as follows:

classmethod %ComputeAggregateGroup(pCubeName As %String, 
                                   pGroupName As %String, 
                                   pVerbose As %Boolean  = 1) as %Status

Where pCubeName is the name of the cube, pGroupName is the name of the <group>, and pVerbose specifies whether to write progress information while the method is running. For pGroupName, you can use "*" to precompute all groups for this cube.

If you use this method, you must first build the cube.

The method processes each group by looping over the fact table and computing the intersections defined by the items within the group. Processing is faster with fewer intersections in a group. The processing is single-threaded, which allows querying in the foreground.

Using the Index Compression Utility

When a cube is frequently updated via synchronization, its need for storage capacity for indexes will grow significantly. In order to minimize index storage requirements, InterSystems provides a %CompressIndices method as part of the %DeepSee.Utils class. This method is as follows:

classmethod %CompressIndices(pCubeName As %String, 
pVerbose As %Boolean = 0) As %Status

Where pCubeName is the name of the cube, and pVerbose specifies whether to write information while the method is running.

Limiting Worker Assignment for Background Tasks

Users may limit the number of %SYSTEM.WorkMgr agents assigned to particular groupings of background tasks via the %SetAgentCount method. This method is as follows:

classmethod %SetAgentCount(pNumAgents As %Integer = "", pType = "build", Output pStatus As %Status) As %Integer

Where pNumAgents is the number of agents which can be assigned to a given type of background task, and pType is the category of background task to which the limit is being applied. pType defaults to build tasks, but can also be set to runTime. Each type's limit is stored separately and can be retrieved by running the following command:

write %DeepSee.Utils:%GetAgentCount(type)

Where type is the category of task for which you want to see the limit of assignable agents.