Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 142

5.8.1 Data Cube

A relational database is not the best data structure to hold data that is, in
nature, multidimensional. Consider a simple cube Sales , with two dimensions,
Product and Customer ,andameasure, SalesAmount , as depicted in Fig. 5.15 a.
This data cube contains all possible (2 2 ) aggregations of the cube cells,
namely, SalesAmount by Product ,by Customer ,andbyboth Product and
Customer , in addition to the base nonaggregated data. Computing such
aggregates can be easily done by performing matrix arithmetic. This explains
why MOLAP systems, which store data in special arrays, deliver good
performance.

a b

c1 c2 c3 Total
p1 100 105 100 305
p2 70 60 40 170
p3 30 40 50 120
Total 200 205 190 595

ProductKey CustomerKey SalesAmount
p1 c1 100

p1 c2 105

p1 c3 100

p2 c1 70

p2 c2 60

p2 c3 40

p3 c1 30

p3 c2 40

p3 c3 50

Fig. 5.15 ( a ) A data cube with two dimensions, Product and Customer .( b )Afact
table representing the same data

Consider now the corresponding Sales fact table depicted in Fig. 5.15 b.
Computing all possible aggregations along the two dimensions, Product and
Customer , involves scanning the whole relation. A possible way to compute
this in SQL is to use the NULL value as follows:

SELECT ProductKey, CustomerKey, SalesAmount

FROM Sales

UNION
SELECT ProductKey, NULL, SUM(SalesAmount)
FROM Sales
GROUP BY ProductKey
UNION
SELECT NULL, CustomerKey, SUM(SalesAmount)
FROM Sales
GROUP BY CustomerKey
UNION
SELECT NULL, NULL, SUM(SalesAmount)

FROM Sales