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:

• Benefits

• Permissions

• Process Partitions

• Related Tasks

Benefits

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:

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.

Permissions

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:

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).

Process Partitions

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:

Related Tasks