Multiple data sources
A stage table typically contains the change data for a detail fact table. As such it normally maps to a specific function within the business and in many cases relates back to one main OLTP table. In many cases however it may be necessary to combine information from a number of tables. One of the decisions required is whether or not it is practical or even possible to join the data from the different source tables.
In the tutorial the stage_sales_detail stage table is built by combining information from the order_header table and the order_line table. These two tables are connected via the order_number field which is common to both tables. The tutorial shows the building of a stage table and the joining of these two tables.
We could however also include two additional source tables being invoice_header and invoice_line which contain specific information relating to what was on the invoice. We may want our fact table to contain information from these tables as well. Although these two tables may contain the order_number and hence potentially allow a join with the order tables we may choose not to perform such a join for performance reasons. In such a situation we have three obvious choices in terms of how we ultimately update our fact table. These are:
1 Join all four tables in one large join in our staging table.
2 Update the staging table in two passes. One pass updating the order information and one pass updating the invoice information.
3 Generate two stage tables, one for order and one for invoice. Use these two staging tables to update the one sales_detail fact table.
Although all three options are viable and a normal situation in the QAD Data Warehouse Designer environment, options (2) and (3) will require specific coding and modifications to the generated procedures from the outset. Given the example provided option (2) would be the normal approach, although in some cases option (3) would be valid.