Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 239

a

Product
Key

Time
Key

Sum
Quantity

Count

p2 t2 100 10

p3 t3 100 20

p4 t4 100 15

p5 t5 100 2

p6 t6 100 1

b c

Product
Key

Customer
Key

Time
Key

Product
Key

Customer
Key

Time
Key

Quantity Quantity

p1 c1 t1 150 p2 c1 t2 10

p2 c1 t2 200 p5 c2 t5 10

p2 c2 t2 100 p6 c5 t6 100

p4 c2 t4 100

p6 c5 t6 200

d e

Product
Key

Time
Key

SD Sum
Quantity SD Count

Product
Key

Time
Key

Sum
Quantity

Count

p1 t1 150 1 p1 t1 150 1

p2 t2 290 1 p2 t2 390 11

p4 t4 100 1 p3 t3 100 20

p5 t5 -10 -1 p4 t4 200 16

p6 t6 100 0 p5 t5 90 1

p6 t6 200 1

Fig. 7.4 An example of the propagate and refresh algorithm with aggregate function
SUM .( a )Originalview DailySalesSum .( b ) Δ + ( Sales ). ( c ) Δ − ( Sales ). ( d ) Summary-
delta table SD DailySalesSum .( e )View DailySalesSum after update

FROM Sales S2
WHERE S1.ProductKey = S2.ProductKey AND
S1.TimeKey = S2.TimeKey )
GROUP BY ProductKey, TimeKey )

Figure 7.5 b shows the summary-delta table. As can be seen, we need a
column for keeping the maximum value in the tuples inserted or deleted,
as well as another column counting the number of insertions or deletions
of tuples having the maximum value . Thus, the first four tuples in the
summary-delta table correspond to insertions, while the last three correspond
to deletions since the count value is negative. The view for creating the
summary-delta table is given next:

CREATE VIEW SD DailySalesMax(ProductKey, TimeKey,
SD MaxQuantity, SD Count) AS (