This means that the
level of detail, or granularity, of the fact table is by customer, day, and product.
Therefore, a single record contains a particular item sold to a particular customer on
a particular day. If the customer ordered the same item on the same day in two or
more different orders, the products will be summed to a single record.
Each fact record will contain a foreign key back to the dimension tables, and these
foreign keys make up the primary key of the fact table; after the key fields are the
measures themselves. Thus, the fact table is often made up entirely, or nearly so,
of numeric fields. This means that the fact table is relatively narrow, since storing
numbers requires less space than storing descriptive strings. While the fact table may
be narrow, it is often extremely large as far as the number of records being held. In
many cases, the fact table makes up ninety eight percent or more of the total storage
used by the relational warehouse.
To illustrate the sizing of a warehouse, consider the same three dimension tables:
Time, Product, and Customer. If tracking 10 years of data with a daily grain, this
means that the Time table will hold approximately 3652 records. The company
might have 10,000 products and sell to 100,000 customers. While not every
customer will buy every product every day, imagine that they did: the fact table
will hold 3652 ?— 10,000 ?— 100,000 records, or 3,652,000,000,000, or 3.65 trillion
records.
Pages:
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81