Complex Transformations in the data warehouse
At this point, we have a populated set of temporary tables that match the destination tables in the data warehouse. These tables have been populated with the source data, but still without any of the complex transformations that need to take place. The data may not be consistent with itself or with other data in the data warehouse. Before we can reconcile detailed transactions, we have to ensure that the basis of comparison is the same: that is, the reference information is identical. If this is not the case, we may find that a can of beans today has a different stock-keeping unit (SKU) from the one used yesterday.
In order to avoid this situation, we have to reconcile data in the order in which it makes sense. In practice, this means we start by reconciling the key reference items, and then work our way outwards to related reference items, before addressing the base transaction. This is harder than it sounds, because many of the entities are interrelated to a point where it becomes difficult to decide how to prise them apart. It will be necessary to keep changing the summaries that are produced to match the query profiles at each point intime. If we had to modify the warehouse manager every time we wished to add a new summary or change an existing one, the system would be perpetually in flux. Metadata can be used to address this issue, by data-driving the generation of summaries. Within the database itself, we store descriptions of the summary tables we require in terms of facts and dimensions.