Database Reference
In-Depth Information
TRUNCATE_ONLY
command, requiring log file entries to be backed up before truncation. oddly, Management Studio
offers a
TRUNCATEONLY
option for the
DBCC SHRINKFILE
command, but it is only applicable to data files. We are able
to use it in Listing 5-3 because it does not cause corruption even when shrinking a transaction log, and it does not
require data files to be backed up before truncation.
Keeping Data Warehouse Backups
As any database administrator (DBA) will attest, database backups are vital to every business. Still, you may not
need to keep a series of database backups for your data warehouse to the same degree as expected of an OLTP
database. In an OLTP database, a daily cycle of full and transactional backups is recommended. Data warehouse
databases, however, may be updated only once a night or once a week and do not require repeated backups
during the middle of the day or even daily. In point of fact, it is sometimes argued that because you can rebuild
the entire data warehouse from the OLTP database—which is continually backed up—you do not need a backup
of the data warehouse. If the data warehouse ever crashes, you can always rebuild it and reload.
This approach makes the DBAs very nervous, sometimes for good reason. Many OLTP databases are not
designed to track changing dimensional values over time. When an update or deletion occurs, previous data in
a column and table is lost. On the other hand, in a data warehouse this data is often tracked in some, if not all,
dimensions. If your data warehouse tracks changes to dimensional values and the OLTP database does not, you
need to retain your backups.
This is an important point, so let's take a look at an example. Suppose you have a table that is tracking
changes to product prices where the standard retail price of a product can change as time goes on. In the data
warehouse, you could add dimension columns to track these changes. (Tracking columns are referred to as
slow-
changing
dimension columns, as discussed in Chapter 4.)
In the example in Figure
5-15
, you see two different table designs: an OLTP table called Products without any
slow-changing dimension columns and an OLAP table called DimProducts. The DimProducts table includes four
slow-changing dimension columns (RecordStartDate, RecordEndDate, IsCurrent and Productkey).
Figure 5-15.
Tables with and without slow-changing dimension columns
If the standard retail price is overridden in the Products table, the original price will not have been lost, but
rather it will be recorded in the DimProducts table during the ETL process. In a perfect world, the slow-changing
dimension columns would have been included in the Products table and tracked in the OLTP environment.
There are times, however, where you will have no control over the design of the OLTP environment but may
