SQL Server Analysis Services Query Response Comparison by Storage Mode

By: Dallas Snider   |   Updated: 2013-09-04   |   Comments   |   Related: > Analysis Services Performance

Problem

What are the differences in MDX query response times among the different Analysis Services storage modes?  SQL Server offers three cube types MOLAP, HOLAP and ROLAP.  In this tip we will look at response times for each of the different cube types.

Solution

In this tip, we will show the differences in MDX query response times among 3 cubes. One cube has its storage mode set to MOLAP, the second cube has its storage mode set to HOLAP, and the third cube has its storage mode set to ROLAP.

The cube structure for all 3 of the cubes in this tip is shown below. There was one measure group containing three measures with each measure having four dimensions. The only difference among the three cubes was their storage mode.

For the MOLAP cube, the storage settings were set to the standard MOLAP where the processing has to be scheduled or performed manually as shown below.

For the HOLAP cube, the storage settings were set to Real-time HOLAP.

For the ROLAP cube, the storage settings were set to Real-time ROLAP.

The populations of the dimensions and fact table remained consistent throughout the performance tests and are shown here.

• Customer dimension: 1,000,000 records
• Date dimension: 2,556 records representing January 1, 2005 through December 31, 2010
• Facility dimension: 1,000 records
• Product dimension: 606 records
• Fact table: 100,000,000 records

MDX queries similar to the query shown below using two and four cross joins were executed and the elapsed time shown in the bottom right of the SQL Server Management Studio was recorded. Small changes were made to each query before execution so the cache was not reused.

SELECT                                                                                  
NON EMPTY                                                                                
{                                                                                                                                         
  [Measures].[Amount Spent],
  [Measures].[Customer Purchases Count],
  [Measures].[Quantity Purchased]
  
} ON COLUMNS,      
NON EMPTY                                                                                
{ (                                                                                      
  [Date].[Calendar Date].[Calendar Year].ALLMEMBERS  *
  [Facility].[Facility Type].[Facility Type].ALLMEMBERS *
  [Product].[Product Category Name].[Product Category Name].ALLMEMBERS  *
  [Customer].[Gender].[Gender].ALLMEMBERS  
) } ON ROWS                            
FROM (SELECT ([Date].[Calendar Date].[Calendar Year].&[2005]:            
              [Date].[Calendar Date].[Calendar Year].&[2007]) ON COLUMNS 
FROM [PerformanceTest]
)                       

The results of the average MDX query execution times are shown below in tabular and graphical form. As shown in the literature, the MOLAP query runs considerably faster than the HOLAP and ROLAP queries.

Next Steps

• Results will vary based on a number of factors including hardware and the amount of data. It is important to note that users who are browsing a ROLAP or HOLAP cube using an interactive drag-and-drop tool can experience a significant delay in the return of their query results.
• Please refer to the following tips for further assistance with SQL Server Analysis Services:
  • SQL Server 2012 Analysis Services Partitioning Performance Demonstration
  • SQL Server Analysis Services (SSAS) Tutorial

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips