Database Reference
In-Depth Information
have control over the OLAP environment. And if that is the case, you will be left implementing slow-changing
dimension tracking similar to this example (Figure
5-15
).
Were the data warehouse database to crash, you would not be able to reload it from the current OLTP
database, but rather, it must be reloaded from the backups. In this scenario, you need to maintain a set of data
warehouse backups.
Using the Filegroups Option
The Filegroups page of the New Database window allows you to create and configure filegroups for your data
warehouse (Figure
5-16
). Using filegroups can increase performance, but doing so adds complexity. With
filegroups, you can control the placement of your fact and dimension tables in a specific set of one or more files.
These files, in turn, are located on separate hard drives allowing the database engine to read and write from
several hard drives at once. With this design, not only will you benefit from ETL performance but also from data
retrieval performance.
Figure 5-16.
Creating filegroups
Every database contains a filegroup called
primary
, and a master data file (
.MDF
) within it. All the internal
system tables are mapped to the primary filegroup and are therefore placed in the
.MDF
le.
To create an additional filegroup, just click the Add button for each filegroup you want to create. As an
example, you might create one filegroup to store your fact tables, another filegroup to store one set of dimension
tables, and yet a third to store other dimension tables.
At first filegroups have no files associated with them. To use the filegroup, you must go back to the General
page (Figure
5-16
), click the Add button to create a new file, type in a logical file name, select the file group you
want to use, define a file path, and type in a physical file name (Figure
5-17
). Other options are available as well,
but these are the common ones.
■
The dropdown box showing the filegroups (Figure
5-17
) “automagically” appears only when you click the
file group cell; it can be confusing because it is set to Primary by default with no dropdown option in sight!
Note
