How To Define Measure Group Partitions in SQL Server Analysis Services SSAS

By:   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | > Analysis Services Measure Groups


Problem
We are anticipating building some very large cubes and would like to get a handle on how to partition our measure groups to provide flexibility in processing and administering our cubes.   Can you guide us through the steps to define multiple partitions for a measure group and provide some explanations?

Solution
The default behavior in SSAS is to create a single partition for a measure group.  Creating multiple partitions for a measure group is definitely a best practice when you have very large fact tables.  The following are the advantages of multiple partitions:

  • Improved Performance - SSAS can query a smaller amount of data by isolating one or more partitions to query rather than the entire measure group.  SSAS can also query multiple partitions in parallel.
  • Flexible Configuration - each partition has its own storage mode; e.g. MOLAP, HOLAP or ROLAP and aggregation design.  You can pick the optimal storage mode and aggregation based on how often the data is queried, how much data there is, how often the data changes, etc.
  • Flexible Processing - a partition can be processed separately or in parallel; you can drop a partition without having to process the cube.  If you have a partition for the latest week, month, quarter, etc. you can process just the rows from the fact table that belong in the partition as opposed to processing the entire fact table.
  • Multiple Source Tables - each partition can optionally specify its own fact table, allowing the flexibility to physically split large fact tables into multiple tables.
  • Improved Scalability - you can take advantage of processing a partition on another SSAS server.

In this tip we will walk through the steps to define multiple partitions for a measure group.   As a starting point we will use the Adventure Works DW Standard Edition sample project that comes with SQL Server 2005.  This project was chosen because the Enterprise edition sample already has multiple partitions, while the Standard edition sample has only one partition per measure group.  We'll define a single partition for each year of data in the Internet Sales measure group.

When defining multiple partitions for a measure group there is one critical point that should be emphasized.  You must specify a query for each partition such that every row from the fact table gets included in one and only one partition.  The simplest way of doing this is to partition based on the Time dimension which is what we will do in the example. 

Note that multiple partitions are only supported in the Enterprise Edition of SQL Server 2005.  You can also use the Developer Edition in your development environment.

Creating Multiple Partitions

Business Intelligence Development Studio (BIDS) is the integrated development environment that comes with SQL Server 2005.  Launch it from the Microsoft SQL Server 2005 program group then open the Adventure Works DW Standard Edition project (default location is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project\Standard).

Step 1: Edit the project properties by clicking Project, Properties from the top-level menu; set the Deployment Server Edition to Enterprise.  Multiple partitions are only available in the Enterprise Edition of SQL Server 2005 so you need to specify the Deployment Server Edition as Enterprise.

project properties

Step 2: Double click the Adventure Works cube in the Solution Explorer then click the Partitions tab to display the existing partitions (only the first partition is shown below). 

internet sales partitions

Click on Internet_Sales and edit the Partition Name to Internet_Sales_2001.

Step 3: Click the Internet_Sales partition (you should see the entire row highlighted as shown above) to display the partition properties.  Click the button in the Source property to display the binding:

partition properties

Step 4: Change the Binding type from Table binding to Query binding and add the WHERE clause as shown below:

partition binding

The idea is to change this partition so that it only has orders that were shipped in the year 2001.  The ShipDateKey column references the TimeKey column in the DimTime table (Time dimension) in the AdventureWorksDW database.  Run the following query to determine the ShipDateKey for the date 2001-12-31 and you will see that 184 is the TimeKey value:

SELECT TimeKey FROM DimTime
WHERE FullDateAlternateKey = '2001-12-31'

Step 5: Click New Partition (see hyperlink in Step 2 above) to add another partition to the Internet Sales measure group.  This will launch the Partition Wizard.  Select the dbo.FactInternetSales table under Available tables:

specify source

Step 6: Click Specify a query to restrict rows and add the WHERE clause as shown below:

restrict rows

The idea is to set this partition so that it only has orders that were shipped in the year 2002.  If you query the DimTime table you will find that the TimeKey value for 2002-12-31 is 549.

Step 7: Accept the defaults on the Processing and Storage Locations dialog:

processing and storage locations

Note that selecting Remote Analysis Services data source for the Processing location is a scale-out feature where you could actually process this partition on another SSAS server.

Step 8: Complete the Partition wizard; fill in the dialog as shown below:

complete partition wizard

Repeat steps 5 through 8 to create and configure partitions for the years 2003 and 2004.  

Step 9: Process the cube; right click on the Adventure Works cube in the Solution Explorer and select Process from the context menu. Accept the defaults on the Process Cube dialog; click Run (Run button not shown below):

process cube

You can also define measure group partitions using SQL Server Management Studio.  Connect to an Analysis Server, select a database and a cube, then right click on a measure group.  The context menu will have a New Partition option that launches the same Partition Wizard we used above.  There are also options to Merge Partitions and work with Aggregations.

Next Steps

  • If you don't already have the AdventureWorks SSAS sample projects and databases available, you can download them here to get the starting point for this tip.  Click the AdventureWorksBICI.msi link.  Also click on the Release Notes link for the details on attaching the relational database.  The default install location for the project is C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project; you will see Enterprise and Standard folders.  We used the project in the Standard folder.
  • Keep in mind that multiple partitions for a measure group provides several advantages as discussed; a good time to think about a partitioning strategy is while you're developing a cube.  You can change the partitions after deployment; you may want to use SQL Server Management Studio in this case.
  • You can download the modified version of the AdventureWorks Standard SSAS project used in this tip here.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, February 9, 2017 - 3:19:51 PM - Diane Back To Top (46138)

 I always partition my medium and big measure groups for improved performance and flexibity. I haven't taken advantage of the improved scalability yet. Does it require specific permissions?

 

Monday, December 12, 2016 - 8:57:16 AM - Tom Back To Top (44949)

Hi Ray,

I'm trying to create additional partitions. The Wizard appeared, I've filled all query details like described in your article, but when I click finish nothing happend. Still there is only one partition there.

Any iseas how to solve this issue ?

Tom

 

Wednesday, May 27, 2015 - 12:40:48 PM - John Tunnicliffe Back To Top (37290)

Check out the SSAS Partition Manager project on Codeplex which will dynamically partition your cube or model automatically with minimal configuration. See https://ssaspartitionmanager.codeplex.com/

Friday, July 27, 2012 - 10:07:15 AM - Ray Barley Back To Top (18827)

Partitions are not specifically designed to provide the ability to do a query that returns all of the data in a partition; they are used when you have alot of data (rule of thumb is 20 milliion rows per partition) and to make it easy to add data and remove data - think of the scenario where you want to provide the last N months of data where as a new month is added the oldest month is deleted.

Queriy results are based on the dimension(s) you use to filter and slice the data.  SSAS deals with what partitions contain the data; your query doesn't need to be concerned.  So as a for instance if you want to summarize a specific group of years you can select the year(s) in your filter; e.g. in an Excel pivot table you would put the year in the report filter then select the specfic years you want.  With a SQL Server Reporting Services Report or Report Builder you would use the year of the time dimension in the filter expression then select the specific years that you want to summarize.

 

Friday, July 27, 2012 - 1:23:56 AM - bo Back To Top (18819)

Hi,

 

I have two partitions in my SSAS project and would like to see the summary data for each partition in the browser tab. What I see now is the summary for all partitions all together. I do not know how the show the summary in the browser tab for one particular partition. Any suggestions would be very much appreciated.

 

To give you a background, I would like to see some summary data/info for different categories of years, e.g., 1989-1994 and then 1995-200 etc.

thanks in advance,

bo

Friday, June 29, 2012 - 10:50:30 AM - Ray Barley Back To Top (18266)

According to guidance from SQLCAT here http://blogs.msdn.com/b/sqlcat/archive/2009/03/13/analysis-services-partition-size.aspx 500MB to 1GB partition size is okay in order to keep the number of partitions at a reasonable level; e.g. less than 1,000

I think the best guidance available is here: http://www.microsoft.com/en-us/download/details.aspx?id=17303  I'm not sure what new capabilities are in SQL2012 but I would investigate to see if there's something compelling for you situation.

Thursday, June 28, 2012 - 4:56:43 PM - Harry Kermet Back To Top (18258)

Hello, at your convenience please provide your five cents the following SSAS situation:  I will be building a cube based on a fact table which will have 24 billion records in it. The table is partitioned by day (one day's worth of data collected into one partition). The cube will have one row-count metric, and two distinct count metrics (thus, 3 measurement groups, since best practice states that distinct count metrics should be in their own group). I think the right approach here should be to have one cube partition per week's worth of data per measurement group. Thus, for one month's of data, each of my three measurement groups will have four partitions (12 partitions all together for a month). Yes, we will be waaayyy beyond the 20 million record per partition rule, but from my SSAS experience, the cube performance is satisfactory, even with 500 million in one partition. Please share your thoughts. Thank you!

Friday, June 1, 2012 - 8:14:14 AM - Ray Barley Back To Top (17774)

Here is an example of how to find the latest date with data.  I found this in the book MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook.  It is based on the Adventure Works sample data.  One important point is that the DataType of the Date attribute must be Date; i.e. in order for the Max function to work correctly in this case, you have to have a date. 

WITH

MEMBER [Measures ] . [Las t date] AS

   Max( [Date] . [Date ] . [Date ] .MEMBERS,

      i if( [Measures ] . [ Internet Order Count ] = 0 ,

           nul l ,

           [Date ] . [Date ] .CurrentMember .MemberValue

         )

      )

SELECT

   { [Measures ] . [ Internet Order Count ] ,

     [Measures ] . [Las t date] } ON 0 ,

   { [Promot ion] . [Promot ion] .MEMBERS } ON 1

FROM

   [Adventure Works ]

Thursday, May 31, 2012 - 10:40:17 PM - Nani Back To Top (17765)
Need help in creating calculated member for a measure .
 
We have 2 measure groups: a. Gross Reserve amnt  b. Calendar
 
 I need to generate an excel report to pull the gross reserve amnt for a particular calendar filter
 
I need to pull the  gross reserve amnt for latest date of the calendar filter. 
 
Ex: When I apply April 2012 filter, I need to get the Gross Reserve amnt for April 30, 2012. If there is no Gross reserve amnt for April 30, I need to get for April 29. If not available for 29 or none of the dates of April it has to get from March 2012 or whichever is latest. 
 
Note: I am applying the April filter, even then I need to get the latest for March.
 
How can I get this done?
 
Please let me know if i haveto provide any other info.
 
 
Thanks,
Nani

Tuesday, April 10, 2012 - 12:02:46 PM - Ray Barley Back To Top (16838)

2 million rows isn't so many that you have to partition.  Take a look at these best practices: http://technet.microsoft.com/en-us/library/cc966527.aspx  http://technet.microsoft.com/en-us/library/cc966399.aspx

There are a number of things to consider besides partitioning to make sure you are getting the best performance you can. 

From the above links here are two points that seem to say with 2 million rows it's not worth partitioning:

Avoid having partitions with more than 20 million rows

Large partitions should generally be broken up into multiple smaller partitions for better performance. As a rule, no partition should have more than 20 million rows.

Avoid having many small partitions in a measure group

Small partitions should generally be combined into fewer, larger partitions for better performance. As a guideline, consider combining partitions when a measure group has more than five partitions with less than two million rows each.

A distinct count measure should be in its own measure group for best performance.  Keep this in mind if you add measures.

Tuesday, April 10, 2012 - 10:47:40 AM - Rajshekhar Back To Top (16836)

Hi, I have FACT table with 2 million records and has 32 dimensions. Cube has only one measure - Distinct count. Please advise if I can apply any partition which will help improve my query performance.

 

Thanks,

Raj

Wednesday, July 14, 2010 - 3:25:00 PM - santade Back To Top (5827)

Sorry to post here mu doubt, I can create a new post, though.

Actually, I have two fact tables wich will be result a metric by fact table. In one I have product dimension with high granurality and other, I have just 2 levels (here we are using 2 product tables). When the users are accessing the data depending in the fact table, the values are always repeated to a fact #1 and the write data is in fact#2. When the user, select other product dimension, he/she write data in fact#2 and repeated values on fact#1. Question: How can I put NULL or not visible depending of dimension and fact table?

Here an example:

Division Dw MPR_Line Number of Transactions Number of Instances Volume of Transactions
120-D-Loan Portfolio Manag -542868098158.162 85730962 85730962 1.52509081274716E+15
130-D-Other S&B 218671925043.266 85730962 85730962 1.52509081274716E+15
160-D-Priv Cl Ret Branches 85730962 85730962 1.52509081274716E+15
161-D-Private Bank- Offset 85730962 85730962 1.52509081274716E+15
162-D-Branch System Admin 85730962 85730962 1.52509081274716E+15
164-D-CIS-IRB 85730962 85730962 1.52509081274716E+15
165-D-Int'l Priv Client 85730962 85730962 1.52509081274716E+15

Number of Transaction, Instances and Volume are part of fact#2 and MPR Line is part of fact#1. I need to put other 3 columns in blank or NULL. How can I do that?

thanks,

Gustavo Santade

Friday, March 26, 2010 - 12:41:17 PM - jroughgarden Back To Top (5122)

FWIW, we use the Standard Edition and can define up to 3 partitions per cube.














get free sql tips
agree to terms