OlapPartitionManager Database Objects

The OlapPartitionManager 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 OlapPartitionManager objects
OlapServer Table Yes Holds list of SSAS servers
OlapDatabase Table Yes Holds list of OLAP databases
OlapCube Table Yes Holds list of OLAP cubes
OlapMeasureGroup Table Yes Identifies measure groups and their partitioning scheme
OlapPartitionDefinitions View Yes Allows review of measure groups configuration
OlapDateDimWrapper View Yes Wrapper around your date local dimension
OlapPartitionPeriods View Yes Lists all partitioning schemes
OlapPartitions 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_OLAP_Partition_Definitions.sql script

Although not a database object in itself, the Populate_OLAP_Partition_Definitions.sql script is an example of how to write a post-deploy script to populate the three key tables to support the OlapPartitionManager. In the sample code, it contains everything to set up six different partitioning schemes on the Adventure Works database.

opm Schema

All the following database objects belong to the opm database schema.
  • opm = OlapPartitionManager :-)

OlapServer Table

This lists all the different servers running SSAS multidimensional in your environment with the OlapServerType column set to DEV, UAT, PROD etc. The OlapPartitionManager reads the OlapServerType from its own config file and will then query this table to find the corresponding server name. The OlapServerName 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
OlapServerKey Primary key for the table
OlapServerName Name of the SSAS server
OlapServerType The type of SSAS server i.e. DEV, UAT, PROD etc.

OlapDatabase Table

The OlapDatabase table contains a list of all the OLAP databases to which we wish to add partitions. Note that this is the visible name of the OLAP database, not the hidden ID.

Column Name Comments
OlapDatabaseKey Primary key for the table
OlapDatabaseName Name of the OLAP cube

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 OlapServer and OlapDatabase tables. This allows the flexibility of having DEV, UAT and PROD environments which have the same set of OLAP databases and cubes deployed.

OlapCube Table

The OlapCube table contains a list of the OLAP cubes 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
OlapCubeKey Primary key for the table
OlapCubeName Name of the OLAP cube
OlapDatabaseKey Enforced foreign key relationship to the OlapDatabase table

In the sample code it contains one row for the Adventure Works cube as shown below:

OlapMeasureGroup Table

The OlapMeasureGroup 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
OlapMeasureGroupKey Primary key for the table
OlapMeasureGroupName 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
OlapCubeKey Enforced foreign key relationship to the OlapCube 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 OlapPartitions 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 OlapPartitions 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).

OlapPartitionDefinitions View

The OlapPartitionDefinitions view allows you to review your entire configuration as shown below:


OlapDateDimWrapper 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 OlapPartitionPeriods view to create the PartitionSuffix and the PartitionSliceKey.

OlapPartitionPeriods View

The following image shows the content of the OlapPartitionPeriods 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 OlapPartitionManager.

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.

OlapPartitions View

OlapPartitions is the view which the OlapPartitionManager queries to obtain the list of partitions that need to be present in all the cubes on all the OLAP databases on the server.

Note that some columns have been removed for clarity.

The following table lists each column and its derivation.
Column Name Comment Source
OlapDatabaseName Name of the OLAP database OlapDatabase table
OlapCubeName Name of the cube OlapCube table
OlapMeasureGroupName Name of the measure group that is to be partitioned OlapMeasureGroup table
PartitionPeriod The type of partitioning scheme to apply. For reference only. Not actually used by the OlapPartitionManager OlapMeasureGroup table
PartitionName The name that the OlapPartitionManager will use for the new partition Generated by concatenating OlapMeasureGroupName and PartitionSuffix
PartitionWhereClause The where clause which will replace 1=0 in the template partition Generated by the OlapPartitions view

Last edited Apr 29, 2015 at 6:33 AM by JohnTunnicliffe, version 12