Partitions (SSAS Tabular)

Partitions divide a table into logical parts. Each partition can then be processed (Refreshed) independent of other partitions. Partitions created by using the Partitions dialog box in SQL Server Data Tools (SSDT) during model authoring apply to the model workspace database. When the model is deployed, the partitions defined for the model workspace database are duplicated in the deployed model database. You can further create and manage partitions for a deployed model database by using the Partitions dialog box in SQL Server Management Studio. Information provided in this topic describes partitions created during model authoring by using the Partition Manager dialog box in SQL Server Data Tools (SSDT). For information about creating and managing partitions for a deployed model, see Create and Manage Tabular Model Partitions (SSAS Tabular).

Sections in this topic:

• Benefits

• Related Tasks

Benefits

Partitions, in tabular models, divide a table into logical partition objects. Each partition can then be processed independent of other partitions. For example, a table may include certain row sets that contain data that rarely changes, but other row sets have data that changes often. In these cases, there is no need to process all of the data when you really just want to process a portion of the data. Partitions enable you to divide portions of data you need to process frequently from the data that can be processed less frequently.

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. How you implement and utilize partitions during model authoring can be very different from how partitions are implemented and utilized for deployed models. Keep in-mind that during the model authoring phase, you may be working with only a subset of the data that will ultimately be in your deployed model.

Processing Partitions

For deployed models, processing occurs by using SQL Server Management Studio, or by running a script which includes the process command and specifies processing options and settings. When authoring models by using SQL Server Data Tools, you can run process operations by using a Process command from the Model menu or toolbar. A Process operation can be specified for a partition, a table, or all.

When a process operation is run, a connection to the data source is made using the data connection. New data is imported into the model tables, relationships, hierarchies, are built or rebuilt for each table, and calculations in calculated columns and measures are re-calculated.

By further dividing a table into logical partitions, you can selectively determine what, when, and how the data in each partition is processed. When you deploy a model, processing of partitions can be completed manually using the Partitions dialog box in SQL Server Management Studio, or by using a script that executes a process command.

Partitions in the Model Workspace Database

You can define partitions during model authoring by using Partition Manager in SQL Server Data Tools. Partitions defined here are first applied to the model workspace database. You can create new partitions, edit, merge, or delete partitions using the Partition Manager.

Partition Manager provides two modes for selecting tables, rows, and columns for a partition: Table Preview mode and SQL query mode. All partitions are defined by using a SQL query, however, by using the Table Preview mode, you can preview and select the data you want to include in the partition. The SQL query is automatically created and validated for you. Because the Table Preview mode is the same table preview that is in the Edit Table Properties dialog box and in the Table Preview page of the Table Import Wizard, the maximum number of rows in preview is 50.

Partitions in a deployed model database

When you deploy a model, the partitions for the deployed model database will appear as database objects in SQL Server Management Studio. You can create, edit, merge, and delete partitions for a deployed model by using the Partitions dialog box in SQL Server Management Studio. Managing partitions for a deployed model in SQL Server Management Studio is outside the scope of this topic. To learn about managing partitions in SQL Server Management Studio, see Create and Manage Tabular Model Partitions (SSAS Tabular).

Related Tasks

See Also

Concepts

DirectQuery Mode (SSAS Tabular)

Process Data (SSAS Tabular)