SSAS Partition Manager Database Objects
The SSAS Partition Manager only requires a few database objects to operate. However, the sample SSDT project contains additional objects which are provided as examples of how you may want to set up logging
etc. The following table highlights which objects are important and what they do.
Object | Object Type | Important | Description |
opm | Schema | Yes | Schema for all SSAS Partition Manager objects |
SsasServer | Table | Yes | Holds list of SSAS servers |
SsasDatabase | Table | Yes | Holds list of OLAP or Tabular databases |
SsasCube | Table | Yes | Holds list of OLAP cubes or Tabular models |
SsasMeasureGroup | Table | Yes | Identifies measure groups and their partitioning scheme |
SsasPartitionDefinitions | View | Yes | Allows review of measure groups configuration |
SsasDateDimWrapper | View | Yes | Wrapper around your date local dimension |
SsasPartitionPeriods | View | Yes | Lists all partitioning schemes |
SsasPartitions | View | Yes | Lists all partitions which need to be created |
EventLog | Table | No | Sample table for logging |
LogEvent | SP | No | Sample stored proc for logging |
Full documentation for each of these objects can be found on the rest of this page.
Populate_Partition_Definitions.sql script
Although not a database object in itself, the Populate_Partition_Definitions.sql script is an example of how to write a post-deploy script to populate the three key tables to support the SSAS Partition Manager. In the sample code, it contains everything to set up six different partitioning schemes on the Adventure Works database.
spm Schema
All the following database objects belong to the
spm database schema.
- spm = SSAS Partition Manager :-)
SsasServer Table
This lists all the different servers running SSAS multidimensional in your environment with the SsasServerType column set to DEV, UAT, PROD etc. The SSAS Partition Manager reads the SsasServerType from its own config file and will then query this table to find the corresponding server name. The SsasServerName column can contain not only the server name, but also the instance name and port number if required. Clearly DNS aliases can be used.
Column Name | Comments |
SsasServerKey | Primary key for the table |
SsasServerName | Name of the SSAS server |
SsasServerType | The type of SSAS server i.e. DEV, UAT, PROD etc. |
SsasDatabase Table
The SsasDatabase table contains a list of all the OLAP or Tabular databases to which we wish to add partitions. Note that this is the visible name of the OLAP or Tabular database, not the hidden ID.
Column Name | Comments |
SsasDatabaseKey | Primary key for the table |
SsasDatabaseName | Name of the OLAP or Tabular database |
SsasModelType | Valid values are: Tabular or Multidimensional |
In the sample code it contains a single row for the Adventure Works database as shown below:

Note that there is no relationship between the SsasServer and SsasDatabase tables. This allows the flexibility of having DEV, UAT and PROD environments which have the same set of OLAP or Tabular databases and cubes deployed.
SsasCube Table
The SsasCube table contains a list of the OLAP cubes or Tabular models in the database to which we wish to add partitions. Note that this is the visible name of the OLAP cube, not the hidden ID.
Column Name | Comments |
SsasCubeKey | Primary key for the table |
SsasCubeName | Name of the OLAP cube or Tabular model |
SsasDatabaseKey | Enforced foreign key relationship to the SsasDatabase table |
In the sample code it contains one row for the Adventure Works cube as shown below:
SsasMeasureGroup Table
The SsasMeasureGroup table contains a list of measure groups in each cube which need to be partitioned. The key elements in this table are described below:
Column Name | Comments |
SsasMeasureGroupKey | Primary key for the table |
SsasMeasureGroupName | The name of the measure group |
PartitionPeriod | The type of partitioning scheme to apply. Valid values are D, W, M, Q, Y, H (explained below) |
PartitionStartDate | The date on which the first partition should start (explained below) |
PartitionSliceIsRange | True/False field that indicates if the partition slice covers a range (explained below) |
DateColumnName | The name of the column used for partitioning |
DateColumnDataType | The data type of the date column used for partitioning. Valid values are int, date, and datetime |
SsasCubeKey | Enforced foreign key relationship to the SsasCube table |
Of course, DO NOT add rows to this table for measure groups that do not need partitioning!
In the sample code it contains six entries, each showing how a different partitioning scheme can be applied to the Adventure Works cube.
PartitionPeriod Column
The PartitionPeriod column is interpreted as follows:
- D = daily partitions
- W = weekly partitions
- M = Monthly partitions
- Q = Quarterly partitions
- Y = Yearly partitions
- H = A Hybrid partitioning scheme
PartitionSliceIsRange Column
True/False field that indicates if the partition slice covers a range. When False, a single PartitionSliceMember is created for use in the PartitionSlice. When true, the PartitionSliceMember is repeated in a set covering the entire date range. The screenshot below shows the effect this flag has on the output of the SsasPartitions view. Note how the
PartitionSlice for
Internet Orders 2014M01 contains a date range because
PartitionSliceIsRange = True. The other partitions have
PartitionSliceIsRange = False and therefore contain a single MDX member.
DateColumnName and
DateColumnDataType Columns
The DateColumnDataType column defines the data type of the DateColumnName. These two fields are subsequently used by the SsasPartitions view (shown about) to formulate the PartitionWhereClause for the partition query. Exactly how the
PartitionWhereClause is put together depends on the data type of the DateColumnName i.e. the content of the DateColumnDataType field. This can be modified if you wish to support special data types or implement a matrix partitioning scheme (i.e. using the date plus some other dimension simultaneously).
SsasPartitionDefinitions View
The SsasPartitionDefinitions view allows you to review your entire configuration as shown below:
SsasDateDimWrapper View
Essentially this view provides a layer of abstraction over your own date dimension or equivalent. Apart from the DateKey and ActualDate columns, the rest of the columns are strings which are used by the SsasPartitionPeriods view to create the
PartitionSuffix and the
PartitionSliceKey.
SsasPartitionPeriods View
The following image shows the content of the SsasPartitionPeriods view when everything is configured to work with the AdventureWorks cube. Note: only partitions covering the first two weeks of 2014 are listed in order to show the flexibility of the SSAS Partition Manager.

Note that this view will need to be customized by you to contain the correct MDX members and the correct logic to create
PartitionSliceKeys that match your cube definition.
SsasPartitions View
SsasPartitions is the view which the SSAS Partition Manager queries to obtain the list of partitions that need to be present in all the cubes on all the OLAP or Tabular databases on the server.

The following table lists each column and its derivation.
Column Name | Comment | Source |
SsasDatabaseName | Name of the OLAP or Tabular database | SsasDatabase table |
SsasModelType | Either Tabular or Multidimensional | SsasDatabase table |
SsasCubeName | Name of the cube | SsasCube table |
SsasMeasureGroupName | Name of the measure group that is to be partitioned | SsasMeasureGroup table |
PartitionName | The name that the SSAS Partition Manager will use for the new partition | Generated by concatenating SsasMeasureGroupName and PartitionSuffix |
PartitionPeriod | The type of partitioning scheme to apply. For reference only. Not actually used by the SSAS Partition Manager | SsasMeasureGroup table |
PeriodStartDate | The start date for the partition | SsasPartitionPeriods |
PeriodEndDate | The end date for the partition | SsasPartitionPeriods |
PartitionSlice | Only set for Multidimensional cubes, this contains the MDX to be inserted into the partition slice | Generated by the SsasPartitions view |
PartitionWhereClause | The where clause which will replace 1=0 in the template partition | Generated by the SsasPartitions view |