One of the benefits of utilizing WhereScape RED to automate your data warehouse development is its drag-drop-click capability. This is especially the case when creating a partitioned Fact table. The dialogs walk you through identifying whether you want to partition by day, month, or year.
However, what happens if you want to partition by a dimension other than date? The example of a data warehouse that is sourced from systems in multiple Territories. An enterprise may update its data at different times throughout the day. Instead of waiting for all systems to be updated and processing once, the requirement is to update the data warehouse once processing in each territory has completed.
One solution would be to partition the Fact Table on Territory, so that once each territory has been updated, processing can begin. But this partitioning is not on a Date field, which is the mechanism used by the current release of WhereScape RED (18.104.22.168 at the time of this writing).
Fortunately, you can still partition on Territory, with an added extra step. This will allow for the generated code to remain completely unmodified and able to be regenerated cleanly.
Follow the normal steps to create a Fact Table.
1. Create the table and build the update procedure, using options “Set Based Insert” and “Include Delete Before Insert” and Click “OK”:
2. Select the option “Truncate Table Before Insert” and Click “OK” to create the table and normal update procedure.
3. Right-click on the Fact Table and select the “Storage” menu item. When you select the “Partitioned” check box, click “Yes” to confirm the change to the fact table.
4. In the “Partitioning attributes” window, select the partitioning key – in this instance, dim_Territory_key. For the partition granularity, select “Day” and in the “corresponding date dimension column” dropdown, select the Surrogate key for the dimension you selected – in this instance, select the same key as above – dim_Territory_key.
In the “First partition (YYYYMMDD)” and Final partition (YYYYMMDD)”, select any valid date value(s) in the specified pattern:
Click “OK”, the Partition Function and Partition Scheme boxes are now populated:
Now, here comes the extra step.
5. Identify the values that are in the dimension table you selected for partitioning and manually enter them in the “Partition Function”. In this example, the values “(20170101,20170102)” are replaced with “(0,1,2,3,4)” and Click “OK”:
6. The same window that popped up when you initially created the procedure will present itself again. Simply click “OK” through these until the new procedure has been created.
Upon execution, the procedure identifies the distinct partitioning key values in the stage table, inserts each set of rows into a partition exchange table created during the generate process, then switches the data into the fact table, updating that partition on the fly.
WhereScape is in the process of enhancing their software to allow for partitioning on non-date dimensions. But this work-around will allow you to accomplish that feat until that enhancement is released.