By: Dallas Snider | Comments (4) | Related: > Analysis Services Performance
Problem
How much of an improvement in processing time can a simple SQL Server Analysis Services (SSAS) partitioning structure provide? Can you provide any examples? Does this processing time improvement only apply to the fact tables? Check out this tip to learn more.
Solution
In this tip, we will show the differences in processing times between a cube with the default single partition created by Visual Studio and a cube utilizing partitions based on the year of an event in a fact table. Also, we will see how the processing times change with increasing numbers of records in the fact table.
The cube structure for both 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 between the two cubes was the partitioning structure.
The Partitions tab of the cube with the default single partition is shown here. Note that by default, the Estimated Rows is set to zero.
The Partition Wizard was used to create the six partitions for this tip for each calendar year from 2005 through 2010 inclusive. For each partition, the WHERE clause was modified to restrict the rows returned within a one calendar year range.
After the Partition Wizard completed, the Slice property for each partition was set to its corresponding tuple for the calendar year.
On the Aggregations tab, one aggregation was created using the Aggregation Design Wizard. The wizard was set to design aggregations until the performance gain reached 30 percent. Because the fact table data was distributed equally among the year values, the one aggregation was assigned for all partitions.
After creating one partition for each year and the aggregation scheme, the Partitions tab appears as shown below.
The populations of the dimensions 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
Twenty million records were inserted into the fact table. The cube with 6 partitions was processed 3 times with the "Process Full" option and its processing time was averaged. Next, the cube with the default single partition was processed 3 times with the "Process Full" option and its processing time was averaged. This processing was repeated with fact tables of 40, 60 and 80 million records.
The results are shown below in tabular and graphical form. As the number of records in the fact table increases, we can see where the processing time for the single partition cube increases at a greater rate than the six partition cube. Thus the importance of partitioning becomes more critical.
Next Steps
- Try different partitioning and aggregation strategies to see how much your processing time is reduced.
- Please note that partitioning can reduce the processing time, but not necessarily improve the query response time.
- 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
