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.

SsasServer.png

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:
SsasDatabase.png

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:
SsasCube.png

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.
SsasMeasureGroup.png

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.
SsasPartitionsIsRange.png

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:

SsasPartitionDefinitions.png

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.
SsasDateDimWrapper.png

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.

SsasPartitionPeriods.png
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.

SsasPartitions.png

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


Last edited May 26, 2015 at 7:35 PM by JohnTunnicliffe, version 5