WhereScape RED 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.
  5. Delete the dummy dimension you have created.
  6. Delete the get_.._key procedure created for the dimension. Do not delete the update procedure.
  7. Create a detail fact table in the normal manner.
  8. Add in the special columns as created for the dimension. Namely dss_end_date, dss_start_date, dss_current_flag and dss_version.
  9. Recreate the table
  10. Create indexes as per the dimension table to help maintain the slowly changing fact table.
  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 would have been automatically versioned out when the latest procedure was created and can be seen through the procedure viewer. 
  12. This new procedure needs 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.
  • No labels