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

Country United States

State Florida Georgia ...

City PH1 Orlando Tampa PH2

Fig. 5.13 Using placeholders for the fact with multiple granularities in Fig. 4.18

alternative path. In the second solution, when aggregating at the city level,
we obtain the placeholders in the result.

5.6.2 Many-to-Many Dimensions

The mapping to the relational model given in Sect. 5.3 , applied to many-to-
many dimensions, creates relations representing the fact, the dimension levels,
and an additional bridge table representing the many-to-many relationship
between the fact table and the dimension. Figure 5.14 shows the relational
representation of the many-to-many dimension in Fig. 4.19 . As can be seen,
abridgetable BalanceClient relates the fact table Balance with the dimension
table Client . Note also that a surrogate key was added to the Balance fact
table so it can be used in the bridge table for relating facts with clients.

Client
ClientKey
ClientId
ClientName
ClientAddress
...

BalanceClient
BalanceKey
ClientKey

Time Balance Account Agency

TimeKey
Date
Event
WeekdayFlag
WeekendFlag
...

BalanceKey
TimeKey
AccountKey
Amount

AccountKey
AccountNo
Type
Description
OpeningDate
AgencyKey

AgencyKey
AgencyName
Address
Area
NoEmployees
...

Fig. 5.14 Relations for the many-to-many dimension in Fig. 4.19

We have seen in Sect. 4.3.2 several solutions to decompose a many-to-many
dimension according to the dependencies that hold on the fact table. In this