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

A dimension table is used to record values of attributes that describe the fact measures
in the fact table, and these attributes are used in queries to select and group the measures in
the fact table. Thus, CUSTOMER records data about the customers referenced by CustomerID
in the SALES table, TIMELINE provides data that can be used to interpret the SALES event in
time (which month? which quarter?), and so on. A query to summarize product units sold by
Customer (CustomerName) and Product (ProductName) would be:
/* *** SQL-Query-CH12-01 *** */
SELECT C.CustomerID, C.CustomerName,
P.ProductNumber, P.ProductName,
SUM(PS.Quantity) AS TotalQuantity
FROM CUSTOMER AS C, PRODUCT_SALES AS PS, PRODUCT AS P
WHERE C.CustomerID = PS.CustomerID
AND P.ProductNumber = PS.ProductNumber
GROUP BY C.CustomerID, C.CustomerName,
P.ProductNumber, P.ProductName
ORDER BY C.CustomerID, P.ProductNumber;
The results of this query are shown in Figure 12-15.
In Chapter 6, we discussed how an N:M relationship is created in a database as two 1:N
relationships by use of an intersection table. We also discussed how additional attributes can
be added to the intersection table in an association relationship.
In a star schema, the fact table is an association table—it is an intersection table for the
relationships between the dimension tables with additional measures also stored in it. And, as
with all other intersection and association tables, the key of the fact table is a composite key
made up of all the foreign keys to the dimension tables.
Illustrating the Dimensional Model
When you think of the word dimension , you might think of “two dimensional” or “three dimen-
sional.” And the dimensional models can be illustrated by using a two-dimensional matrix
and a three-dimensional cube. Figure 12-16 shows the SQL query results from Figure 12-15
displayed as a two-dimensional matrix of Product (using ProductNumber) and Customer
(using CustomerID), with each cell showing the number of units of each product purchased by
each customer. Note how ProductNumber and CustomerID define the two dimensions of the
matrix: CustomerID labels what would be the x -axis, and ProductNumber labels the y -axis.
Figure 12-17 shows a three-dimensional cube with the same ProductNumber and
CustomerID dimensions, but now with the added Time dimension on the z -axis. Now instead
of occupying a two-dimensional box, the total quantity of products purchased by each
customer on each day occupies a small three-dimensional cube, and all these small cubes
combine to form a large cube.
As human beings, we can visualize two-dimensional matrices and three-dimensional
cubes. Although we cannot visualize models with four, five, and more dimensions, BI systems
and dimensional databases can handle such models.
Multiple Fact Tables and Conformed Dimensions
Data warehouse systems build dimensional models, as needed, to analyze BI questions, and
the HSD-DW star schema in Figure 12-12 would be just one schema in a set of schemas.
Figure 12-18 shows an extended HSD-DW schema.
In Figure 12-18, a second fact table named SALES_FOR_RFM has been added:
SALES_FOR_RFM ( TimeID , CustomerID , InvoiceNumber , PreTaxTotalSale)
This table shows that fact table primary keys do not need to be composed solely of for-
eign keys that link to dimension tables. In SALES_FOR_RFM, the primary key includes the
InvoiceNumber attribute. This attribute is necessary because the composite key (TimeID,