Databases Reference
In-Depth Information
A fact table is the central table in a star schema or snowflake schema. A fact table
typically contains numerical measurements or amounts and is supplemented by
contextual information in dimension tables. For example, a fact table might include
transaction identifiers and transaction amounts. Dimension tables could add contextual
information about customers, products, and salespersons. Fact tables are associated
with dimension tables via key columns. Foreign key columns in the fact table contain
the same values as the primary key columns in the dimension tables.
Slowly changing dimensions (SCD) is a technique for tracking changes to dimension
table values in order to analyze trends. For example, a dimension table named
Customers might have columns for Customer ID, Home Address, Age, and Income.
Each time the address or income changes for a customer, a new row could created for
that customer in the dimension table, and the old row could be retained. This historical
record of changes could be combined with purchasing information to forecast buying
trends and direct customer marketing campaigns.
Type 2 SCD Dimensional Model
Dimension tables store attribute values. Dimension tables are combined with fact
tables in data structures known as star schemas or snowflakes. In these data
structures, fact tables record numeric measures that are associated with events.
Dimension tables record categories of attribute values that are associated with the facts.
Key columns associate facts with attributes. For example, consider a star schema
that consists of a fact table and several dimension tables. The fact table records product
sales. As shown in the following diagram, the numeric measures in the fact table are
recorded in columns for amount, date, and time. The primary key column Transaction
ID uniquely identifies each row in the fact table. The foreign key columns in the fact
table provide referential integrity between the fact table and the dimension tables. The
foreign key values enable each fact table row to accurately reference all of the attribute
values that are associated with that event.
Search WWH ::
Custom Search