Database Reference
In-Depth Information
We could create a DimSales table and put them both in it, but there is really not much point. Leaving the
SalesId and SalesLineItemId in the fact table is more straightforward for reporting, and changing this would
complicate the design. Besides, this new DimSales table will effectively have a one-to-one relationship between
itself and the FactSales table.
Another fact dimension column is the SalesDate. From it, we can create a time dimension. You will almost
always want a time dimension for every data warehouse. Evaluating how something changes over time is one of
the most common types of reporting.
When you create time-based reports, chances are that you will want more than just a list of dates. It
is likely you will want to categorize those dates into months, quarters, and years. Each of these items is an
additional dimensional attribute that could be stored side by side with the date. In addition, you can extract all
of these dimensional attributes from the one date by performing calculations on the data itself. Because all the
dimensional attributes (either stored or calculated) are in the FactSales table, this means that, by definition, the
time dimension is currently designed as a fact dimension.
Time Dimensions
Designing a time dimension as a fact dimension works but is not considered the best practice. Instead, you
should create a date dimension table. A table called DimDates or DimTime is one of the most common features
of every data warehouse.
At a minimum, a date table includes a date key and a date name, but it also includes other dimensional
attributes such as the month, quarter, and year. These are basic date values and are easily calculated from
individual dates in the fact table. But what if you want to include additional attributes such as holidays, corporate
events, or fiscal weeks? These are not easy to calculate from a single date value. Creating a separate date
dimension table that holds this information makes creating reports that include holidays or fiscal weeks, easy.
Consider this: Figure 4-11 shows a typical date dimension table linked to the fact table by a date ID. If you
were to leave all of the additional attributes from the DimDates table in the fact table, it would dramatically
increase the size of each row! Because the fact table commonly has thousands if not millions of rows, this has a
big impact on the data warehouse.
Figure 4-11. Date or time dimension table
 
Search WWH ::




Custom Search