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

changes to the summary table due to changes in the source data. Then, during
the refresh phase, these changes are applied to the summary table.
We will explain the algorithm with a simplified version of the Sales fact
table in the Northwind case study, whose schema we show next:

Sales(ProductKey, CustomerKey, TimeKey, Quantity)

Consider a view DailySalesSum defined as follows:

CREATE VIEW DailySalesSum AS (
SELECT ProductKey, TimeKey, SUM(Quantity) AS SumQuantity,
COUNT(*) AS Count
FROM Sales
GROUP BY ProductKey, TimeKey )

The Count attribute is added in order to maintain the view in the presence
of deletions, as we will explain later. In the propagate phase, we define two
tables, Δ + ( Sales )and Δ − ( Sales ), which store the insertions and deletions to
the fact table, and a view where the net changes to the summary tables are
stored. The latter is called a summary-delta table, which in this example is
created as follows:

CREATE VIEW SD DailySalesSum(ProductKey, TimeKey,
SD SumQuantity, SD Count) AS
WITH Temp AS (
( SELECT ProductKey, TimeKey,
Quantity AS Quantity, 1 AS Count
FROM Δ + ( Sales ) )
UNION ALL
( SELECT ProductKey, TimeKey,
-1 * Quantity AS Quantity, -1 AS Count
FROM Δ − ( Sales ) ))
SELECT ProductKey, TimeKey, SUM( Quantity), SUM( Count)
FROM Temp
GROUP BY ProductKey, TimeKey

In the temporary table Temp of the view definition, we can see that for
each tuple in Δ + ( Sales ),westorea1inthe Count attribute, while for
each tuple in Δ − ( Sales ), we store a

− 1. Analogously, the Quantity attribute

values are multiplied by 1 or 1 depending if they are retrieved from

Δ + ( Sales )or Δ − ( Sales ), respectively. Then, in the main SELECT clause,
the SD SumQuantity attribute contains the net sum of the quantity for each
combination of ProductKey and TimeKey , while SD Count contains the net
number of tuples in the view corresponding to such combination.
During the refresh phase, we apply to the summary table the net changes
stored in the summary-delta table. Below we give a general scheme of the
refresh algorithm valid when the aggregate function is SUM :

−

Refresh Algorithm
INPUT: Summary-delta table SD DailySalesSum
Summary table DailySalesSum