How to answer the how to design question - 1

Last time I asked this and promised to say more next time.

Most data warehouses fulfill Bill Inmon's criteria of being time-variant and non-volatile (well at least for the measures being stored if not the reference data.) That is, we have the ability to track change across time by inspecting a series of slices of history. Typically the transactional systems that feed data warehouses only hold a few (the most recent) of these time slices; there may also be other systems that store historic transactions for tax or audit purposes, but often they are just a subset of the warehouse data domain or in a format that can not readily be loaded to the warehouse. This difference between the timespan of the transactional systems and data warehouse leads us to the conclusion that it is not going to possible to rebuild an established data warehouse from the source systems. That is we are going to need a robust recovery mechanism for the times that things go wrong.

Designing a data warehouse for data recovery after a failure is fundamental, it can not be an afterthought bolted on during the post delivery wash-up phase of warehouse development project. The backup method chosen needs to be reliable and fit for the proposed operating parameters. Backups can database driven, say in the case of RMAN, OS level, or even at the storage level (cloning a disk set on the SAN is a popular method) Given the cost improvements for disk storage and the need for greater database availability, backing up a data warehouse to disk and then moving the copy to tape for secure storage is a very attractive way to proceed. Each technique imposes its own set of restrictions on the granularity that can be recovered and it is a business decision to work out the balance between the costs of storage, speed of recovery, impact on runtime performance and thence the technique to be used for your particular data warehouse.

But designing the best backup is of no use at all unless you can prove it works; not just "in theory" but by doing it for real. When delivering a new data warehouse I always build a full recovery test into the project plan. Usually this happens with the full production volume of data (or as much history as being delivered in the project). This full recovery test:

• Shows us the method works
• Gives us a benchmark time for recovery - businesses like that
• And allows us to fully document the recovery process for the time we need to do it for real