Creating a Partitioned Fact Table in SQL Server

In WhereScape RED, a partitioned fact table can only be created from an existing fact table. The process therefore is to create a fact table normally, including the update procedure and indexes.
The table is then partitioned through the Storage screen of the fact table's Properties window. WhereScape RED assists in creating the exchange table, modifying the indexes, and building the procedure to handle the update of the partitions. 

Create a detail or rollup/combined fact table

• Create the fact table that you wish to partition in the usual way.
• You must create an update procedure but do not need to load any data. The process of creating the update procedure will also build any indexes for the fact table.
• RED will be using a partition exchange table to move data to and from the partitioned fact table. This exchange table must have the same columns, column order and indexes as the fact table. It is therefore recommended to get the fact table into its final form before partitioning it.
• Add any indexes to avoid extra work later.

Convert to a partitioned fact table

1. Double-click the fact table that you want to partition to bring up its properties screen.
2. Click the Storage tab and tick the Partitioned checkbox (see example in the screens below).
3. A prompt is displayed warning you that this table needs to be recreated to change the current fact to a partitioned table and offering assistance to create the partitioned table. The exchange table is then used to replace the relevant period in the fact table. The fact table remains in a queryable state throughout the process.
4. Click Yes to launch the wizard that will assist you through the partitioning of the table.
   

   Converting an existing non-partitioned table to a partitioned table cannot be done using a deployment application.

   To convert non-partitioned to partitioned tables using deployment applications, may require some manual intervention to update the target databases to match the new metadata.

   
5. As you click the Partitioned checkbox, a dialog pop-up is displayed as follows asking for confirmation that the table is to be converted to a partitioned fact table.  If you click Yes, the existing fact table is renamed to become the exchange partition table. Any current data will remain in this table.
   
6. The parameters for the partitioned fact table are then prompted for and are described below:
   
7. The partition exchange table name is prompted. This table is identical to the fact table and is used to swap partitions to and from the fact table. Enter a name for the exchange partitioned table.
   
   
   • The fact table will be partitioned by the date dimension key. Select a date dimension key that will be used as the basis for the partitioning of the fact table. It is assumed that this key has the standard WhereScape format of YYYYMMDD. 
   • Select a partition granularity of day, month, or year along with the corresponding column in the date dimension.
     • For a daily partition, a column with a date data type must be chosen. The date dimension key will be used for the partitioning but the date column is required.
     • For a monthly partition, a column with the format YYYYMM must be chosen. In the standard WhereScape date dimension, this is the column cal_month.
     • For a yearly partition, a column with the format YYYY must be chosen. In the standard WhereScape date dimension, this is the column cal_year.
   • The first and last partitions should then be selected. The last partition is not that important as additional partitions will be created as required. Normally just select a partition a few on from the first. The first partition is however important as any data that is dated before the first partition will be loaded into this first partition. The partition must be entered in the format shown. For example, monthly partitions require the format YYYYMM.
   • After all the relevant fields have been completed, click OK and the conversion process will populate the Partition Function, Partition Scheme, and Table Partition Scheme fields of the table's storage properties.
     

     This field is limited in size, so if too many partitions are chosen only the first few will be added to this field. The other partitions will be created dynamically by the update procedure.

8. Click the OK button on the table's Storage screen to begin the process of converting the fact table, creating the new fact table, and building the new update procedure.

   Examine the results screen to see what has been done.

   

   The exchange partition table will now have any current data, to populate the fact table, copy the data from the partition table to the fact table, drop and recreate the fact index.
   The date dimension key used to base the partitioning on is not allowed to have nulls. WhereScape RED has modified the table property, however, the partition table needs to be validated and recreated to be valid.

   Then the fact table can be processed.

The partitioned fact table update procedure

The generated procedure will handle the creation of new partitions as required.
Any data for periods prior to the first partition is placed in the first partition. If data is received for a period after the last partition then new partitions are created.
Empty partitions are created if necessary to skip periods where no data is present. For example, if we have a table partitioned by month and the latest partition is 200204 (April/2002).  If data is then received for say September 2002 the update procedure will build partitions for May, June, July, August and September.
There is a variable at the start of the update procedure called v_max_skip_periods. This variable is by default set to 12. This defines the maximum number of continuous partitions we will leave empty.
From our previous example if our latest partition was April 2002 and we received data for July 2003 with no interim data then the update procedure will fail as it would have to skip 14 partitions. This check is present to prevent erroneous data from creating hundreds of partitions into the future.