Database Reference
In-Depth Information
the designer to preserve the meaning of a hier-
archy in order to use the roll-up and drill-down
operations. This is not the case when using a
conceptual model.
SSAS requires several steps in order to use
this hierarchy and to obtain correct results. First,
the EmplDepBridge table is considered as another
fact table and Employee and Department dimen-
sions are handled as separate dimensions (Figure
11a). Later on, using a cube data browser, these
dimensions can be combined to form a hierarchy.
In the next step, designers must define in the
Dimension Usage tab that in order to aggregate
a measure from the Fact Reseller Sales table,
many-to-many cardinalities must be considered
(Figure 11b) along with the intermediate measure
group Empl Dep Bridge table. Note that the MS
representation of this cardinality in Figure 11b
does not clarify the possibility of performing
aggregation of measures from the Fact Reseller
Sales when rolling-up to the Department level.
Finally, in order to use a distributing factor from
the bridge table, for every measure of the fact
table the Measure Expression property must be
modified, e.g., for the SalesAmount measure we
include the expression [ SalesAmount ]*[ Distribu
tingFactor ].
perspective (e.g., time) using alternative aggrega-
tion paths. For these kinds of hierarchies it is not
semantically correct to simultaneously combine
the different component hierarchies to avoid
meaningless intersections, such as Fiscal 2003
and Calendar 2001. The user must choose only
one of the alternative aggregation paths for his/her
analysis and switch to the other one if required.
The logical-level representation provides a
table for each level leading to a snowflake schema.
When the denormalization process is applied,
all levels are represented in the same table as it
is done for the schema in Figure 2. It should be
clear that while this kind of hierarchy is easily
differentiated at the conceptual level, it is not
possible to characterize it in the logical schema
as all attributes forming both paths of alternative
hierarchies are included in the flat DimTime table
in Figure 2.
This kind of hierarchy was implemented
in the MS Analysis Services issued with SQL
Server 2000 with the goal of improving system
performance and storage requirements. This was
achieved by including a single key in a fact table
and sharing aggregate values of common levels
(if they exist). A name included two parts sepa-
rated by a dot: the first part was common for all
composing hierarchies and the second part was a
unique hierarchy name, e.g., Time.Calendar and
Time.Fiscal for our example.
The current version of the Analysis Services
does not include this kind of hierarchy and the
designers should define two different hierarchies
that we call parallel: one hierarchy corresponding
to calendar and another one to fiscal time periods,
allowing combinations between the alternative
paths (see Figure 12). This induces the existence
of meaningless intersections with null values for
measures and does not reuse aggregated values.
The better option would be to automatically switch
between composing hierarchies. However, if the
users are interested in combining hierarchies rep-
resenting the Gregorian and fiscal calendars and
analyze only the intersections with meaningful
Alternative Hierarchies
Alternative hierarchies , at the schema level, are
composed of several non-exclusive simple hierar-
chies sharing at least the leaf level and accounting
for the same analysis criterion (Malinowski &
Zimányi, 2008).At the instance level each member
participates in all composing hierarchies, thus
measure aggregations can be performed as for
simple hierarchies. The Time hierarchy in Figure
3 is an example of an alternative hierarchy, where
the Date dimension includes two hierarchies cor-
responding to the usual Gregorian calendar and
to the fiscal calendar of an organization.
Alternative hierarchies are used when the
user requires analyzing measures from a unique
Search WWH ::




Custom Search