Overview:
Partitioning allows tables and indexes to be subdivided into smaller pieces, and each small piece (eg., each subset of the overall data) is called a partition.
Partitions are typically created based on a date field.
This article covers how to create a partition schema using the Jet Data Manager, and how to use that partition schema to partition a table.
Partitioning is only available when using SQL Server Developer and Enterprise editions if your SQL Server version is older than 2016 SP1. Starting with SQL Server 2016 SP1 partitioning became standard in all editions of SQL Server.
Process:
-
Right-click the table that you would like to setup table partitioning on, and select Table Settings
-
On the Performance tab check Enable Physical Valid Table and hit the first Add button to create a template:
-
Give the template a descriptive name, and select among the options available. In this example, we will partition using the Month (YYYYMM) operation, the use of which will become apparent in Step 4
-
Complete the partitioning setup by providing values for the following three fields:
a. Template: Pick the template you created in step #3 from the drop-down list
b. Field: Specify a date field from the table you are partitioning. In this example, I selected Posting Date
c. Time Table: The time table chosen in this list must be a time table (such as the Date table in the stage,) if one is not present you can create one by hitting the second Add button:
-
Click OK and then deploy & execute your table.
Notice the creation of a new partition field in the format of YYYYMM inside the table, as well as a Partition Templates folder inside the Jet Data Manager. Partition templates can be re-used in other tables
-
Once that completes, right-click the table on which you set up the partition and select Edit Cube.
Then, check the box for Incremental Load
When the cube gets executed again, it will utilize the partition.
Comments