SQL Analysis Procedure Aggregations Test

Processing is the process to load data into SSAS objects like Cubes, Partitions, and Dimensions, etc. on the OLAP Server with the data from the underlying relational data warehouse. As part of the processing step(s), aggregations are performed, data is loaded into one or more pre-defined partitions and various actions, which involve data modifications are performed in this step. The processed data is stored into respective locations like the OLAP Server, relational data warehouse, etc. depending on the Storage Mode defined for the different objects.

Processing consumes both memory and time. In fact, if the server does not have enough physical memory for processing aggregations, SSAS will have to use temp files for processing. This can increase the processing time.

This is why, if processing slows down, then administrators should first determine whether a memory shortage is causing the slowness. This is where the SQL Analysis Procedure Aggregations test helps! This test monitors the partitions and aggregations processed, measures the memory used during aggregation processing, and further reveals if temporary files were utilized for processing. If a processing slowdown is observed, then these metrics will indicate if it is because the server is not sized with sufficient memory for processing aggregations.

Target of the test : A Microsoft SQL Analysis Services Server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the SSAS server being monitored

Configurable parameters for the test
Parameters Description

Test Period

How often should the test be executed.

Host

The host for which the test is to be configured.

Port

The port at which the specified Host listens.

Instance

The name of a specific SQL Analysis Services Server instance to be monitored. The default value of this parameter is “default”. However, if the Microsoft SQL Server hosting SSAS uses named instances, then do the following:

  • Configure the Instance parameter with the name of the SQL Server instance that hosts SSAS.

  • Do not change the default value of the Port parameter

SSAS Version

Specify the SSAS version being monitored. By default, 11 is displayed here, indicating that version 11 of SSAS is monitored by default.

Measurements made by the test

Measurement

Description

Unit

Interpretation

Current partitions

Indicates the current number of partitions being processed.

Number

 

Partitions processed

Indicates the total number of partitions processed, successfully or otherwise.

Number

 

Memory size

Indicates the size of current aggregations in memory.

MB

 

Temporary file data write rate

Indicates the rate of writing rows to a temporary file.

Writes/Sec

An Analysis Services instance uses temporary files to supplement the memory allocated to each process buffer when a process buffer is too small for the aggregations being processed.

This means that if the value of this measure is high, you can conclude that the SSAS instance does not have enough memory to process aggregations.

For best performance, it is more important to allocate a sufficiently large process buffer to obviate the need for temporary files during processing. If processing requires temporary files, the algorithm is slower than if the process buffer was large enough to perform the processing entirely in memory. If you find that the temporary files are used extensively and you cannot eliminate their use, you can add a second temporary file folder on a different physical drive. Using two temporary folders increases processing performance.