By: Dallas Snider | 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:
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
