Partitioning is used for:
MS SQL Server Table Partitioning that splits large tables across several file groups typically located on different disks.
Differencing storage mode for Measure Groups in OLAP databases between Relational OLAP and Multidimensional OLAP.
A partition definition in Profitbase Studio consist of selecting a column (a System or Dimension column), and enter a list of criteria’s to split it in suitable parts. Each part then has properties for Sql Server file group name or OLAP storage mode. A definition can be used for either Data Source Fact Tables or OLAP Measure Groups, or both.
Click the "New" toolbar button to add a new Partition Definition.
Define the Partition Definition as described below then click the "Save" toolbar button. The Save will automatically invoke the "Split into many" pop-up dialog and based on user input in this dialog add items to the Partition Items list (also called the Partition Range Boundary Values list). It will also add the new definition to the list on the left. Select the definition from this list the next time it is to be edited/viewed.
Each Partition Item can be split or merged/removed by selecting the partitions to merge or split and clicking on a "Partition Range Boundary Values" toolbar button. Split and Merge/Remove operations are handled via a pop-up dialog.
By selecting a Partition Item, details about it is shown below the list. Properties can be edited and click "Save" to keep changes.
To use a partition definition you need to assign them either in the Data Source Table definition or to an OLAP Measure Group. When a Partition is in use, status is displayed in the list below Partition Items. Click on "Refresh" to update it after split/merge operations.
Partition Definition can only be deleted if it is not in use by Fact Tables or Measure Groups. Click on "Delete" if it is enabled to delete the partition definition.
The following fields can be edited:
| Name | This is the name of the Solution Partition definition. |
| Use for | Check for use in MS SQL DB Tables and/or SSAS Measure Groups. This opens for specific input and availability. When in active use in Fact Tables/Sql Server, MSSQL DB Tables setting cannot be unchecked. |
| Boundary Type | Select the data type (Date or Int) of the partition column. Once saved, this cannot be changed. |
| Boundary Column | Select the column to partition on. Select a valid Column name from a dropdown (Must be a System or DimensionID column from Name Definitions). Once saved, this can not be changed. |
| Left range | This specifies to which side of each boundary value interval, left or right, the boundary value belongs, when interval values are sorted by the Database Engine in ascending order from left to right. Best practice for date or datetime is RIGHT because of more intuitive behavior. When in active use in Fact Tables/Sql Server, MSSQL DB Tables setting cannot be unchecked. |
| Comments | Optional |
| Description | Optional |
Note - When hitting Save the first time the Split into many dialog is displayed.
The Partition Boundaries is shown in a list.
To Split or Merge partition boundaries, select partition boundar(ies)(y) to change and run Split, Split into many, Merge into one, Delete or Change Compression commands accordingly.
Partition Properties:
Sequence # - is a read only number from 1 to 14999. The item with the highest sequence number is the rightmost item that always exists. It has no boundary, it contains the ‘rest’ of the data (> last boundary value or all data if no other items exist).
Boundary Value – is the value used for splitting each partition. This cannot be changed once set.
DB File Group (MSSQL DB Tables partitions only) – Optional - if the definition is set to be used in MS SQL table partitioning; the dropdown lists the available physical file groups in the MS SQL Database Server. See Microsoft MS SQL Server documentation for more about file groups. The default file group is named PRIMARY.
Compression Type (MSSQL DB Tables partitions only) – Optional - Specify compression for the partition.
Slice (SSAS Measure Group partitions only) – Optional - Specify a SQL statement indicating the partition data slice.
Storage Location (SSAS Measure Group partitions only) – Optional - Specify the partition storage location.
Additional Where Expression (SSAS Measure Group partitions only) – Optional - This is also for use in OLAP Measure Groups. It may contain a valid sql expression on the Measure Group output view for additional use.
Partition operation dialogs for Delete, Merge and Split:
The Partition Operation dialog or wizard will guide you through operations on Partition Items.
A Split operation means that the selected partition item will be split into two (or more if "Split into many") parts and it must have a boundary value between the existing value and the value of the item at left.
If the Partition Definition is in use in a Fact Table this means that the physical data table in the database will be split and that parts of the data in the existing partition will be moved to the new partition.
Therefore this dialog will have a log page that records the split operation when you click on ‘Next’ button from the first definition page.
The "Merge into one" operation will join selected coherent partition items in to one. The same rules apply as for Split.
The "Delete" operation can only be done on the left most Partition Item.
Note: