Snapshot Fact Columns

The columns for a snapshot fact table vary to a large degree. A date dimension is always present to reflect the ‘as at’ date. Some snapshots may have a large number of measures reflecting $values quantities etc, whereas others may be rich in attribute (non additive) facts.

Slowly changing fact tables provide a complete history of changes for some part of the business. Typically such fact tables are used for situations where a relatively small number of changes occur over time, but we need to track all of these changes. A good example is an inventory stock holding that does not change very often. In such a case a slowly changing fact table can record both the current inventory levels as well as providing a means of seeing the state of inventory holdings at any point in time.

QAD Data Warehouse Designer does not provide any automated way for creating a slowly changing fact table. The best method is to proceed as follows.

1 Drag and drop the stage table into a dimension target and create a dummy slowly changing dimension with the name we will be using for our fact table. (i.e. fact_...).

2 Answer the questions to the pop-up dialog boxes. Select a join if asked, but do not bother to join any of the tables. Select the columns to be managed as slowly changing. Normally these will be the measures such as quantity and all of the dimension keys. The generated procedure will fail to compile.

3 Inspect the additional columns added for the dimension and make a note of them. Specifically dss_end_date, dss_start_date, dss_current_flag and dss_version.

4 Inspect the indexes created to help manage the slowly changing dimension and make a note of their columns.

8 Add in the special columns as created for the dimension. Namely dss_end_date, dss_start_date, dss_current_flag and dss_version.

11 Modify the update procedure for the fact table. Bring up the old version of the procedure that was created for the dimension of the same name. This will have been automatically versioned out when the latest procedure was created and can be seen through the procedure viewer.

12 This new procedure will need to merge the code from the old procedure, which will provide a guide in how to build the code for a slowly changing fact table.

Partitioned fact tables are normally used for performance reasons. They allow us to break a large fact table into a number of smaller tables (partitions).

Note: Partitioned fact tables are not directly supported in SQL Server 2000. A view must be created over a number of tables to create the concept of a logical partitioned fact table.

QAD Data Warehouse Designer supports partitioned fact tables. The following section explains their creation and support.

In QAD Data Warehouse Designer 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 type is then changed to a partitioned table type and QAD Data Warehouse Designer will assist in creating the exchange table, modifying the indexes and building the procedure to handle the update of the partitions.

Note: Partitioned fact tables are not directly supported in SQL Server 2000. A view must be created over a number of tables to create the concept of a logical partitioned fact table.