Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 133

5.6.1 Facts with Multiple Granularities

Two approaches can be used for the logical representation of facts with
multiple granularities. The first one consists in using multiple foreign keys,
one for each alternative granularity, in a similar way as it was explained
for generalized hierarchies in Sect. 5.5.3 . The second approach consists
in removing granularity variation at the instance level with the help of
placeholders, in a similar way as explained for unbalanced hierarchies in
Sect. 5.5.2 .
Consider the example of Fig. 4.18 , where measures are registered at
multiple granularities. Figure 5.12 shows the relational schema resulting from
the first solution above, where the Sales fact table is related to both the City
and the State levels through referential integrity constraints. In this case,
both attributes CityKey and StateKey are optional, and constraints must be
specified to ensure that only one of the foreign keys may have a value.

Product City

ProductKey
ProductNo
ProductName
QuantityPerUnit
UnitPrice
Discontinued

CityKey
CityName
Population
Altitude
StateKey

Sales

TimeKey
ProductKey
CityKey (0,1)
StateKey (0,1)
Quantity
UnitPrice
Discount
SalesAmount
Freight

Time State Country

TimeKey
Date
DayNbWeek
DayNameWeek
DayNbMonth
DayNbYear
WeekNbYear

StateKey
StateName
EnglishStateName
StateType
StateCode
StateCapital
CountryKey

CountryKey
CountryName
CountryCode
CountryCapital
Population
Subdivision

Fig. 5.12 Relations for the fact with multiple granularities in Fig. 4.18

Figure 5.13 shows an example of instances for the second solution above,
where placeholders are used for facts that refer to nonleaf levels. There are
two possible cases illustrated by the two placeholders in the figure. In the first
case, a fact member points to a nonleaf member that has children. In this
case, placeholder PH1 represents all cities other than the existing children. In
the second case, a fact member points to a nonleaf member without children.
In this case, placeholder PH2 represents all (unknown) cities of the state.
Obviously, in both solutions, the issue is to guarantee the correct
summarization of measures. In the first solution, when aggregating at the
state level, we need to perform a union of two subqueries, one for each