Big Data, Data Warehouses, and Business Intelligence Systems - Database Processing: Fundamentals, Design, and Implementation - page 546

Figure 12-14
The HSD-DW Table Data
(a) TIMELINE Dimension Table
(b) CUSTOMER Dimension Table
(c) PRODUCT Dimension Table
(d) PRODUCT_SALES Fact Table
A fact table is used to store measures of business activity, which are quantitative or fac-
tual data about the entity represented by the fact table. For example, in the HSD-DW database,
the fact table is PRODUCT_SALES:
PRODUCT_SALES ( TimeID , CustomerID , ProductNumber , Quantity,
UnitPrice, Total)
In this table:
● Quantity is quantitative data that record how many of the item were sold.
● UnitPrice is quantitative data that record the dollar price of each item sold.
● Total (= Quantity * UnitPrice) is quantitative data that record the total dollar value of
the sale of this item.
The measures in the PRODUCT_SALES table are for units of product per day . We do not
use individual sale data (which would be based on InvoiceNumber), but rather data summed
for each customer for each day. For example, if you could compare the HSD database INVOICE
data for Ralph Able for 6/5/13, you would see that Ralph made two purchases on that date
(InvoiceNumber 35013 and InvoiceNumber 35016). In the HSD-DW database, however, these
two purchases are summed into the PRODUCT_SALES data for Ralph (CustomerID = 3) for
6/5/13 (TimeID = 41430).
By The WAy The TimeID values are the sequential serial values used in Microsoft Excel
to represent dates. Starting with 01-JAN-1900 as date value 1, the date
value is increased by 1 for each calendar day. Thus, 05-JUN-2013 - 41430. For more
information, search “Date formats” in the Excel help system.