New Data Warehouse Technologies - Data Warehouse Systems: Design and Implementation - page 520

TimeKey EmployeeKey CustomerKey ProductKey SalesAmount
t1 e1 c1 p1 100

t2 e2 c2 p1 150

t3 e1 c3 p3 210

t4 e2 c4 p4 80

Suppose that this table is refreshed once a day and that we need current
fact data. We show below a transaction-granularity real-time partition, called
Partition Sales , storing the transactions that occurred during the last day,
which have not been loaded into the fact table.

TimeKey EmployeeKey CustomerKey ProductKey SalesAmount
t5

e1 c1 p1 30

t6 e2 c2 p1 125

t7 e3 c3 p3 300

A query asking for total sales by employee and customer would need to
access both tables, as follows:

SELECT EmployeeKey, CustomerKey, SUM(SalesAmount)
FROM (SELECT EmployeeKey, CustomerKey,
SUM(SalesAmount) AS SalesAmount
FROM Employee E, Customer C, Sales S
WHERE E.EmployeeKey = S.EmployeeKey AND
C.CustomerKey = S.CustomerKey
GROUP BY EmployeeKey, CustomerKey
UNION
SELECT EmployeeKey, CustomerKey,
SUM(SalesAmount) AS SalesAmount
FROM Employee E, Customer C, Partition Sales S
WHERE E.EmployeeKey = S.EmployeeKey AND
C.CustomerKey = S.CustomerKey
GROUP BY EmployeeKey, CustomerKey) AS FactFull
GROUP BY FactFull.EmployeeKey, FactFull.CustomerKey

A periodic-snapshot real-time partition is related to a fact table with
coarser granularity (e.g., week). The real-time partition contains all trans-
actions of the current snapshot period (in this case, the current week).
Data are added continuously to this partition and summarized at the
granularity of the fact table until the period completes, thus maintaining
a rolling summarization of the data that has not yet been loaded into the
static fact table. Suppose that in the simplified Sales fact table above the
time granularity is week. As new orders arrive, we perform in the real-
time partition a rolling summarization of the measure SalesAmount for the
combination of employee, customer, product, and week. This means that the