My first Publication Agile-Data-Warehouse-Design-eBook | Page 286

266
Chapter 9
Why and How
It simplifies the fact table join—this is now a simple inner join on the single
EVENT DATE KEY just like a standard CALENDAR dimension instead of a
BETWEEN join on START DATE KEY and END DATE KEY.
Figure 9-5
Event Day Calendar
Weighting factors
for each multi-
valued group (e.g.,
all the events on a
day) must total to 1
If you take a look at the example data in Figure 9-5 you will see that the weighting
factors for any one date add up to 1 (100%). For example, on June 21 2010 both the
World Cup and Wimbledon are taking place so they both receive 50% of the sales
activity by giving them a weighting factor of 0.5 (50%). Whereas, on July 14 2010
the Tour De France is the only significant sporting event taking place (perhaps this
is the only event that Pomegranate has sponsored that day) so it gets a weighting
factor of 1 (100%). Now when sales are grouped by EVENT, sales revenue can be
“correctly weighted” by multiplying each atomic revenue fact by the sporting event
weighting factor for the day it was recorded, as shown in the SQL below:
SELECT Event, SUM(Revenue * Weighting_Factor) as Weighted_Revenue
FROM
Sales_Fact s, Event_Day_Calendar d
WHERE s.Sale_Date_Key = d.Event_Date_Key
GROUP BY Event
Consequences
The “correct”
weighting factor
split can depend
on the facts being
queried and who is
doing the querying
Of course these may not be the “correctly weighted” figures at all—if a business
sells more tennis rackets than soccer balls, the Wimbledon/World Cup split should
be quite different. Allocation is usually problematic because different stakeholder
groups have different ideas about how the atomic facts should be split. However,
the one thing no one can argue about is the weighted total. If the weighting factors
always adds up to 1 for any day, the grand total for all the days covered by a report
will be correct—so long as no events are filtered out.
Useful impact reports can be constructed by querying both the weighted facts and
unweighted versions of the facts. The unweighted facts can be displayed in the
body of the report for each row header; for example, World Cup $30M and Wim-
bledon $10M. The weighted facts can be aggregated within the BI tool to produce
a correct grand total for the report; for example, $30M for the two events (because
they completely overlap).