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

Northwind cube given in Fig. 5.4 , there is a many-to-many relationship
between employees and cities represented by the table Territories . Such a
table must be defined as a fact table in the corresponding cube. In this case,
using the terminology of Analysis Services, the City dimension has a many-
to-many relationship with the Sales measure group, through the Employee
intermediate dimension and the Territories measure group.
5.9.5 Cubes
In Analysis Services, a cube is built from one or several DSVs. A cube consists
of one or more dimensions from dimension tables and one or more measure
groups from fact tables. A measure group is composed by a set of measures .
The facts in a fact table are mapped as measures in a cube. Analysis Services
allows multiple fact tables in a single cube. In this case, the cube typically
contains multiple measure groups, one from each fact table.
Figure 5.29 shows the definition of the Northwind cube in Analysis
Services. As can be seen in Fig. 5.29 a, Analysis Services adds a new measure
to each measure group, in our case Sales Count and Territories Count ,which
counts the number fact members associated with each member of each
dimension. Thus, Sales Count would count the number of sales for each
customer, supplier, product, and so on. Similarly, Territories Count would
count the number of cities associated with each employee.
Figure 5.30 shows the relationships between dimensions and measure
groups in the cube. With respect to the Sales measure group, all dimensions
except the last two are regular dimensions, they do not have an icon to the
left of the attribute relating the dimension and the measure group. On the
other hand, Geography is a many-to-many dimension linked to the measure
group through the Territories fact table. Finally, Order is a fact dimension
linked to the measure group through the Order No attribute.
Analysis Services supports the usual additive aggregation functions SUM ,
MIN , MAX , COUNT ,and DISTINCT COUNT . It also supports semiadditive
measures , that is, measures that can be aggregated in some dimensions but
not in others. Recall that we defined such measures in Sect. 3.1.2 . Analysis
Services provides several functions for semiadditive measures, namely, Avera-
geOfChildren , FirstChild , LastChild , FirstNonEmpty ,and LastNonEmpty ,among
other ones.
The aggregation function associated with each measure must be defined
with the AggregationFunction property. The default aggregation measure is
SUM , and this is suitable for all measures in our example, except for Unit
Price and Discount . Since these are semiadditive measures, their aggregation
should be AverageOfChildren , which computes, for a member, the average of
its children.