Occasionally a fact table is required in which you are unable to define a unique Business Key for the table. A common scenario is with retail or concession data, where you are tracking individual transactions that occur at the cash register. In this case it is possible that you could have multiple rows in the fact table which are identical. One option is to aggregate the duplicate rows and just insert a single row into the fact table. However, most likely you will want to have each individual transaction loaded into the fact table. In this situation you would be unable to define a business key on the fact table as there are no columns that would make for a unique row. To accommodate this, WhereScape RED allows you to take a slightly different approach to loading the staging and fact tables.
Note: Like other functionality within WhereScape RED, all of the resulting code is auto-generated.
If you are doing a "Cursor" approach for populating the staging table, you will be prompted for business key during the procedure creation process. When prompted for the business key, do not select any columns and then deselect the "include update statement" option. The "Set" based methods will not prompt for a business key.
When you build the fact table update procedure, do not select any columns for the business key definition. At this point you can select the "Set based insert" option, on the "Define fact business key(s)" dialog box. If you wish to delete rows from the fact table, prior to loading the staging table data you can also select the "Include delete before insert" option as well (same dialog box).
If you selected the "Include delete before insert" option you will presented with a "Definition for a delete statement before the fact table insert" dialog box. This allows you to build a delete statement that will run prior to inserting any data into the fact table and delete rows based on a specific criterion – typically a column in the staging table. For example, you could delete all rows from the fact table which have a matching "batch id" in the staging table. Once the delete has completed the rows in the staging table will then be loaded into the fact table.
Whether you build this delete statement depends on how the data is being loaded into the fact table. ie: what is the process for loading new/changed rows. If you are continually appending data and never have a need to run a delete, then you don’t select the "Include before insert" option. However, the delete option is important as it can be used to ensure that you do not double-load data into the fact table.
While the majority of fact tables will have a business key defined, there are some scenarios where this is not possible. With a couple minor changes during the procedure build process you can easily accommodate these scenarios.