Next Steps

In this chapter, we concentrated on the effects of changes to dimension data, but some solutions also need to support changes to the fact data.

Dealing with Updated Facts

Financial systems may restate the figures for historical time periods, or records in transaction systems occasionally need to be corrected. These applications may then retransmit the updated facts to the ETL process.

As we have seen for dimensions, there are a number of potential solutions depending on the business requirements. If each updated transaction has a unique key that can be used to locate the original record in the data warehouse, you could simply update the record in place. In this case, you need to reprocess any cubes (or specific partitions within a cube, as described in Chapter 11, "Very Large Data Warehouses") that reference those time periods. This approach would not be appropriate for applications that need to show that an update has taken place, because the original information is lost.

If users need to be able to see original versus updated facts, for each update that is received, you could work out the difference for each measure between the original record and the updated record and then append a new record that has the (positive or negative) adjustment required with a flag that states that this is an update.

If users need to be able to see multiple versions of the data such as for financial restatements, you could add a Version dimension to the cube. This is somewhat complex to model, however, because if a user selects a specific version, the only information they will see will relate to that one version. One technique is to combine the Version dimension with the Date dimension so that each period can have multiple versions.

Historical Snapshots for Reporting

As you saw in Chapter 6, "Reporting," Reporting Services enables administrators to store snapshots of reports at certain points in time, which can be retrieved by users. These snapshots are complete representations of the data required to display the report as it currently exists, even if the underlying data source is changed in the future.

This is a useful feature because we do not have to make any changes to the data warehouse to support it. For systems that have a high volume of fact changes and need to accurately reprint reports for historical periods, this might be the easiest approach.