Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Query Response Comparison by Storage Mode


By:   |   Read Comments   |   Related Tips: > 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 Update:






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





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools