Conceptual Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 107

this case, the Client level in the two schemas in Fig. 4.21 is replaced by a
nonstrict hierarchy composed of the ClientGroup and the Client levels.
Finally, to avoid many-to-many dimensions, we can choose one client as
the primary account owner and ignore the other clients. In this way, only
one client will be related to a specific balance, and the schema in Fig. 4.19
can be used without any problems related to double counting of measures.
However, this solution may not represent the real-world situation and may
exclude from the analysis the other clients of joint accounts.

ClientGroup Client

GroupId
...

ClientId
ClientName
ClientAddress
...

Time Account Agency

Balance

Date
Event
WeekdayFlag
WeekendFlag
...

AccountNo
Type
Description
OpeningDate
...

AgencyName
Address
Area
NoEmployees
...

Amount

Fig. 4.22 Alternative decomposition of the fact in Fig. 4.19

In summary, many-to-many dimensions in multidimensional schemas
can be avoided by using one of the solutions presented in Fig. 4.21 .The
choice between these alternatives depends on the functional and multivalued
dependencies existing in the fact, the kinds of hierarchies in the schema, and
the complexity of the implementation.

4.4 Querying the Northwind Cube Using the OLAP
Operations

We conclude the chapter showing how the OLAP operations studied in
Chap. 3 can be used to answer a series of queries addressed to the Northwind
cube in Fig. 4.2 . The idea of this section is to show how these operations
can be used to express queries over a conceptual model, independently of the
actual underlying implementation.

Query 4.1. Total sales amount per customer, year, and product category.
ROLLUP*(Sales, Customer → Customer, OrderDate → Year,

Product → Category, SUM(SalesAmount))