SQL Server Analysis Services Query Response Comparison by Storage Mode


By:   |   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.

What are the differences in MDX query response times among the different Analysis Services storage modes?


 the differences in MDX query response times among 3 cubes.

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.

the storage settings were set to the standard MOLAP where the processing has to be scheduled or performed manually

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

the storage settings were set to Real-time HOLAP

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

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.

The results of the average MDX query execution times


the MOLAP query runs considerably faster than the HOLAP and ROLAP queries.
Next Steps


Last Updated: 2013-09-04


get scripts

next tip button



About the author
MSSQLTips author Dallas Snider 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.

View all my tips





Comments For This Article





download


Recommended Reading

SSAS - Best Practices and Performance Optimization - Part 1 of 4

How to Use Partitioning in an Analysis Services Tabular model to Improve Processing Performance

How to partition data in Tabular SSAS

Improve Analysis Services Tabular 2017 Processing with Encoding Hints

SSAS - Best Practices and Performance Optimization - Part 3 of 4





get free sql tips
agree to terms


Learn more about SQL Server tools