Database Reference
In-Depth Information
dimension and a parent-child dimension. Although it is common for data warehouses to have some, but not all
possible features, it is good to understand these other design options so that you can include them when they are
required. To help with that, let's look at common dimensional patterns.
Dimensional Patterns
Dimensional patterns are different ways of creating tables to hold your dimensional data. Using the right
pattern for the right set of dimensional attributes is important. For example, if you incorrectly choose a standard
dimensional pattern for dealing with a many-to-many design, your analysis server cubes will come up with
incorrect data. The good news is that once you have seen the patterns, they are pretty easy to recognize.
Standard Dimensions
A standard dimension is a collection of one or more tables linked directly to the primary fact table (FactSales in
Figure
4-9
). The standard dimension is the one that you see most often, which is why it is called
standard
. Each
standard dimension table should have a key column and a name column. In addition to those two columns, you
can provide additional descriptive values that help further categorize the data.
In summary, in Figure
4-9
, the fact table is the FactSales table. The DimTitles table is an example of a
standard dimension, as are the snowflake tables, DimStores, and DimStates. These three tables represent two
dimensions (Titles and Stores), and although one is in a star design and the other is in a snowflake design, they
are both still considered standard dimensions.
Fact or Degenerate Dimensions
Fact dimensions (aka degenerate dimensions) have all their attributes stored in the fact table. They are most
commonly referred to as
fact dimensions
, because
degenerate dimension
is not as descriptive and it sounds, well…
degenerate.” The names are synonymous, but for the purpose of this text, we refer to them as fact dimensions.
In a fact dimension with two dimensional attributes, both would be stored in the fact table. A classic
example of this is the SalesId and the SalesLineItemId, as shown in Figure
4-10
. These columns are not measures;
they represent additional descriptions of the measures, and they do not link to any dimensional tables. This, by
definition, makes them part of a fact dimension we call DimSales.
Figure 4-10.
Fact dimenisons
