Tabular Model Partitions (SSAS Tabular)
Partitions divide a table into logical parts. Each partition can then be processed (Refreshed) independent of other partitions. Partitions defined for a model during model authoring are duplicated in a deployed model. Once deployed, you can manage those partitions and create new partitions by using the Partitions dialog box in SQL Server Management Studio or by using a script. Information provided in this topic describes partitions in a deployed tabular model database. For more information about creating and managing partitions during model authoring, see Partitions (SSAS Tabular).
Sections in this topic:
Effective model design utilizes partitions to eliminate unnecessary processing and subsequent processor load on Analysis Services servers, while at the same time, making certain that data is processed and refreshed often enough to reflect the most recent data from data sources.
For example, a tabular model can have a Sales table which includes sales data for the current 2011 fiscal year and each of the previous fiscal years. The model’s Sales table has the following three partitions:
Current fiscal year
Fiscal years 2001, 2002, 2003, 2004, 2005, 2006. 2007, 2008, 2009, 2010
All fiscal years prior to the last ten years.
As new sales data is added for the current 2011 fiscal year; that data must be processed daily to accurately be reflected in current fiscal year sales data analysis, thus the Sales2011 partition is processed nightly.
There is no need to process data in the Sales2010-2001 partition nightly; however, because sales data for the previous ten fiscal years can still occasionally change because of product returns and other adjustments, it must still be processed regularly, thus data in the Sales2010-2001 partition is processed monthly. Data in the SalesOld partition never changes therefore only processed annually.
When entering the 2012 fiscal year, a new Sales2012 partition is added to the mode’s Sales table. The Sales2011 partition can then be merged with the Sales2010-2001 partition and renamed to Sales2011-2002. Data from the 2001 fiscal year is eliminated from the new Sales2011-2002 partition and moved into the SalesOld partition. All partitions are then processed to reflect changes.
How you implement a partition strategy for your organization’s tabular models will largely be dependent on your particular model data processing needs and available resources.
In order to create, manage, and process partitions in SQL Server Management Studio, you must have the appropriate Analysis Services permissions defined in a security role. Each security role has one of the following permissions:
Read, process, create, copy, merge, delete
To learn more about creating roles during model authoring by using SQL Server Data Tools (SSDT), see Roles (SSAS Tabular). To learn more about managing role members for deployed tabular model roles by using SQL Server Management Studio, see Tabular Model Roles (SSAS Tabular).
Partitions can be processed (refreshed) independent of other partitions by using the Partitions dialog box in Management Studio or by using a script. Processing has the following options:
Detects the process state of a partition object, and performs processing necessary to deliver unprocessed or partially processed partition objects to a fully processed state. Data for empty tables and partitions is loaded; hierarchies, calculated columns, and relationships are built or rebuilt.
Processes a partition object and all the objects that it contains. When Process Full is run for an object that has already been processed, Analysis Services drops all data in the object, and then processes the object. This kind of processing is required when a structural change has been made to an object.
Load data into a partition or a table without rebuilding hierarchies or relationships or recalculating calculated columns and measures.
Removes all data from a partition.
Incrementally update partition with new data.
Describes how to create and manage partitions in a deployed tabular model by using SQL Server Management Studio.
Describes how to process partitions in a deployed tabular model by using SQL Server Management Studio.