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 2012 Analysis Services Partitioning Performance Demonstration


By:   |   Read Comments (4)   |   Related Tips: > Analysis Services Performance


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


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 only difference between the two cubes was the partitioning structure


The Partitions tab of the cube with the default single partition

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.

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.

For each partition, the WHERE clause was modified

After the Partition Wizard completed, the Slice property for each partition was set to its corresponding tuple for the calendar year.

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.

the Aggregation Design Wizard

After creating one partition for each year and the aggregation scheme, the Partitions tab appears as shown below.

creating one partition for each year and the aggregation scheme

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.

The results are shown below in tabular and graphical form
the number of records in the fact table increases
Next Steps


Last Update:


signup button

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





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     



Monday, August 19, 2013 - 11:39:28 AM - Steven Neumersky, MCITP, CBIP Back To Top

Good demo.

Keep in mind that partitioning is an Enterprise Edition feature. The Analysis Services Performance Guide provides a ton of advice on optimizing SSAS processing performance even if you are on Standard Edition. This guide applies to the multidimensional model in SQL Server 2008 and 2008 R2 but remains relevant today.

http://www.microsoft.com/en-us/download/details.aspx?id=17303

If you are using the tabular model, there is also a white paper on optimizing performance of that mode as well:

http://msdn.microsoft.com/en-us/library/dn393915.aspx

Good luck to all :)

 


Wednesday, August 14, 2013 - 7:24:04 AM - Dallas Snider Back To Top

If the processing time on your small dataset is acceptable, then I would say "don't fix what isn't broken." 

If the processing time on your small dataset is not acceptable, then partitioning might not fix the problem.  There could be an issue with the cube or dimension design.  If Visual Studio is generating warnings regarding performance, please pay attention to them. 


Thursday, August 08, 2013 - 2:30:12 PM - Meghan Back To Top

Does this technique help with smaller data sets?

I know I can test it, but is it even worth the time if you have 1 to 5 million records, not 20 million?


Thursday, August 01, 2013 - 10:47:58 AM - PaulE Back To Top

The numbers really seal the deal here

There is no doubt about what my team needs to start doing


Learn more about SQL Server tools