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

a

Product
Key

Time
Key

Max
Quantity

Count

p2 t2 150 4

p3 t3 100 5

p4 t4 50 6

p5 t5 150 5

p6 t6 100 3

p7 t7 100 2

b c

Product
Key

Time
Key

SD Max
Quantity SD Count

Product
Key

Time
Key

Max
Quantity

Count

p1 t1 100 2 p1 t1 100 2

p2 t2 100 2 p2 t2 150 4

p3 t3 100 2 p3 t3 100 7

p4 t4 100 2 p4 t4 100 2

p5 t5 100 -2 p5 t5 150 5

p6 t6 100 -2 p6 t6 100 1

p7 t7 100 -2 p7 t7 ? ?

Fig. 7.5 An example of the propagate and refresh algorithm with aggregate function
MAX .( a )Originalview DailySalesMax .( b ) Summary-delta table SD DailySalesMax .
( c ) Updated view DailySalesMax

SELECT ProductKey, TimeKey, Quantity, COUNT(*)
FROM Δ + ( Sales ) S1
WHERE Quantity = (
SELECT MAX(Quantity)
FROM Δ + ( Sales ) S2
WHERE S1.ProductKey = S2.ProductKey AND
S1.TimeKey = S2.TimeKey )
GROUP BY ProductKey, TimeKey
UNION ALL
SELECT ProductKey, TimeKey, Quantity, -1 * COUNT(*)
FROM Δ − ( Sales ) S1
WHERE Quantity = (
SELECT MAX(Quantity)
FROM Δ − ( Sales ) S2
WHERE S1.ProductKey = S2.ProductKey AND
S1.TimeKey = S2.TimeKey )
GROUP BY ProductKey, TimeKey )

Finally, Fig. 7.5 c shows the view after the update. Let us consider first
theinsertions.Thetuplefor p1 in the summary-delta table does not have a
corresponding tuple in the view, and thus, it is inserted in the view. The tuple
for p2 in the summary-delta table has a maximum value smaller than that in
the view so the view is not modified. The tuple for p3 in the summary-delta