Improving Expressive Power in Modeling Data Warehouse and OLAP Applications - Evolving Application Domains of Data Warehousing and Mining - page 20

Hurtado & Gutierrez, 2007; Jagadish, Lakshmanan,
& Srivastava, 1999; Pedersen, Jensen & Dyrsen,
2001; Niemi, Nummenmaa, & Thanisch, 2001;
Pourabbas & Rafanelli, 2003). Although Tsois,
Karayannidis, and Sellis (2001) state that, without
considering graphical representation, many models
have the same core expressivity, we believe that
a graphical support is an important feature of a
conceptual model that allows both designers and
users to better understand modeled concepts.
A limited number of papers refers to the rep-
resentation of different kinds of hierarchies and
dimensions at the logical level. Some authors
(e.g., Pedersen et al., 2001) transform complex
hierarchies into simple ones and implement those
using star or snowflake schemas.Another approach
is to include an additional structure (e.g., for non-
strict hierarchies) or additional members (e.g., for
unbalanced or non-covering hierarchies) (Kimball
& Ross, 2002), as we will see in the following
sections. Other authors (e.g., Jagadish et al., 1999)
provide specific mapping to the relational model
that captures the semantics of the hierarchy; how-
ever, they produce a significant number of relations
and require SQL extensions for their management.
On the other hand, Song, Rowen, Medsker, and
Ewen (2001) have proposed several logical solu-
tions to the problem of managing many-to-many
relationships between facts and dimension tables
or the so-called multivalued dimensions.
Very few authors present the mappings from
the conceptual multidimensional model to the
OLAP implementation platform (e.g., Han, Sapia
& Blaschka, 2000; Trujillo, Palomar, Gomez &
Song, 2001). Even though their conceptual models
allow representing some constructs as described
in this chapter (i.e., they better represent multi-
dimensional semantics), the chosen implementa-
tion tools do not support such constructs. As a
consequence, some multidimensional elements
presented in the conceptual schemas must be
ignored or transformed to simple ones in order to
be implemented.

Some commercial products, such as Microsoft
Analysis Services 2005 and Oracle OLAP 10g, can
cope with some of the hierarchies and dimensions
described in the following sections. However, they
rely mainly on the star or snowflake structure that
does not allow the designer to clearly represent
the different kinds of hierarchies and dimensions
or they use specific representations related to the
chosen OLAP tool.

MotIvAtIng exAMPle

In this section we briefly describe an example that
we use throughout this chapter in order to show
the necessity of having a conceptual model for
representing different kinds of hierarchies and
dimensions for the DW and OLAP applications.
The schema in Figure 2 shows an extract of
the AdventureWorksDW database issued by Mi-
crosoft 1 (Microsoft, 2005). This schema is used
for analysis of sales by resellers (the fact table
FactResellerSales ) and represents the measures
(e.g., SalesAmount or OrderQuantity, not shown
in the figure), on the level of individual prod-
ucts. This fact table also includes an attribute
SalesOrderNumber that allows the user to group
the products that were purchased together in the
same transaction.
Sales are analyzed from different perspectives,
i.e., dimensions. The Product dimension includes
a hierarchy using the snowflake structure repre-
senting products, subcategories, and categories
(the DimProduct , DimProductSubcategory , and
DimProductCategory tables). The Time dimen-
sion (the DimTime table) includes attributes that
allow users to analyze data considering calendar
and fiscal periods of time. This dimension is also
used for representing different dates, e.g., order
date (when the product was ordered), due date
(when the product was delivered), and ship date
(when the product was shipped).
Another perspective of analysis is represented