SQL Analysis MDX Test

MDX (Multi – Dimensional eXpressions) is a query language used to retrieve data from multidimensional databases. More specifically, MDX is used for querying multidimensional data from Analysis Services. Multidimensional Expressions (MDX) lets you query multidimensional objects, such as cubes, and return multidimensional cellsets that contain the cube's data.

To ensure the peak performance of your MDX queries, it is essential that you write MDX efficiently. For that, you should first decide how your queries should evaluate calculations. Typically, calculations are evaluated in the cell-by-cell mode or bulk mode. Imagine you have a query that returns 3 product categories on the columns and 4 countries on the rows. So you have 12 cells in total, where every row has at least one value and every column has at least one value, but not every cell has a value.

Country Accessories Bikes Clothing
Australia   11 12
Canada 21    
UK   33 34
USA   44  

In cell-by-cell mode the formula engine will attempt to calculate all 12 cells. It will start with Accessories for Australia, ask the storage engine for the data, execute the MDX expression then move on to Bikes in Australia and so on.

From the above discussion, it is clear that the bulk mode reduces the number of executions the formula engine has to do, it reduces the number of calls to the storage engine, and the storage engine calls that are made more closely match how the data is stored on disk as SSAS only stores non-empty values.

Next, you should make sure that your queries use the evaluation node caches effectively. MDX calculation engine keeps separate caches for cell-by-cell evaluation nodes and for bulk-mode evaluation nodes.

If MDX queries are run in the more expensive cell-by-cell evaluation mode, and are engineered to use caches frugally, query performance is bound to suffer. To improve query performance, administrators should first figure out how many MDX queries use the less-desired cell-by-cell evaluation mode and how well caches are used by the queries. This is where the SQL Analysis MDX test helps! 

This test monitors MDX query execution plans and reports the count of cell-by-cell, bulk-mode, and subcube evaluation nodes built by these plans. This way, administrators can figure out if there are any queries that use the expensive cell-by-cell mode, and may hence require fine-tuning. The test also tracks the hits and misses experienced by the caches of the cell-by-cell and bulk-mode evaluation nodes, and alerts administrators to poor cache usage. This way, by monitoring factors that may impact MDX query performance, this test provides administrators with useful pointers for improving query performance.

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

Evaluation nodes

Indicates the total number of cell-by-cell evaluation nodes built by MDX execution plans.

Number

Ideally, the value of this measure should be low. This is because, typically, inefficient or badly-written calculations are evaluated in cell-by-cell mode, which is usually slower than bulk mode.

Evaluation node cache hits

Indicates the number of cell-by-cell hits that currently occurred in the evaluation nodes cache.

Number

A high value is desired for this measure.

Evaluation node cache misses

Indicates the number of cell-by-cell misses that currently occurred in the evaluation nodes cache.

Number

A low value is desired for this measure.

Cell-by-Cell evaluation node cache hit ratio

Indicates the ratio of cell hits to the sum of cell hits and misses.

Percent

This value should be high. If the value is lower than 85%, which means that the number of total cell misses are higher than expected.

if a change to your MDX increments this value by a smaller amount than before, and the query runs faster, you are doing something right.

Cells calculated

Indicates the total number of cell properties calculated since the last measurement period.

Number

 

Bulk-Mode evaluation nodes

Indicates the total number of bulk-mode evaluation nodes built by MDX execution plans.

Number

Ideally, the value of this measure should be high. This is because, evaluations in bulk-mode are less process-intensive, and will help MDX queries run faster.

Cached Bulk-Mode evaluation nodes

Indicates the current (approximate) number of cached evaluation nodes built by MDX execution plans.

Number

 

Subcube evaluation node cache hits

Indicates the total number of subcube hits in the cache of evaluation nodes.

Number

For best query performance, a high value is desired for this measure. Aggressively caching intermediate results is one of the key reasons for MDX execution engine to deliver great query performance. MDX execution engine maintains a complex system of various types of caches for different purposes. When a MDX query requires heavy calculation, caches for evaluation nodes tend to play a decisive role in good query performance. An evaluation node is a subspace along with query plans built for all applicable calculations plus, optionally, data caches holding the calculation results. Unlike cached results of storage engine queries which correspond to leaf nodes in an MDX evaluation tree, formula engine evaluation nodes can be at much higher level or even be the root node of an evaluation tree. Hitting or missing an evaluation node at high level in the cache will make a dramatic difference in performance for calculation intensive queries.

Subcube evaluation node cache misses

Indicates the total number of subcube misses in the cache of evaluation nodes.

Number

 

Subcube evaluation node cache hit ratio

Indicates the ratio of sub cube hits to the sum of sub cube misses and hits.

Percent

This value of this measure should ideally be high. If the value is lower than 85%, it implies poor cache usage.