Multidimensional Modeling Concepts
Translated by
ScribdTranslationsMultidimensional Modeling Concepts
Translated by
ScribdTranslationsChapter 3: Multidimensional Modeling
Specific objectives
Understanding the concepts of the data model
multidimensional
Introduce operators on cubes
multidimensional
Introduce the logical design schemas
3.1. Basic concept of multidimensional modeling
Multidimensional modeling involves considering a subject being analyzed as a
point in a space with multiple dimensions. The data is organized in such a way that
highlight the subject analyzed and the different perspectives of the analysis.
This design is based on a multi-dimensional model of the data that sees the
data in the form of a data cube
3.1.1 The concept of dimension
A Data cube: allows for modeling and visualizing data along different dimensions;
the dimensions thus constitute the perspectives from which the data can be viewed
observed
Each dimension is represented by a table
• Example: Localization (city, department, country, region)
Product (item, type, category)
Date (day, week, month, quarter, year)
3.1.2. The concept of fact
The central theme is represented by a fact table. This fact table contains the
values of the measures and the keys to the dimension tables
The value of a measurement is the result of a data aggregation operation.
• Ex : montant_ventes, quantité_vendue
– The keys of dimension tables are generally constructed automatically.
3.1.3 The notion of cuboid
The set of values of a measure for a combination of dimension values
constitutes a cuboid
Example:
Electronic sales data warehouse of a company.
Sales amounts for the year by item type, by city, and by month
A value is associated with each type-city-month combination.
→Dimensions : Produit (type), Localisation (ville) et Date (mois)
3.1.4 Notion of Cube
data UnCube is a lattice
of cuboids.
Example:
3.1.5 Notion of multi-level dimensions
For each dimension, different levels of abstraction / specialization are defined.
During the OLAP process, data is typically analyzed starting from a low
level of detail towards more detailed data for 'drilling down'. To define these
different levels of detail, each dimension is equipped with one (or more) hierarchy(ies)
of parameters.
The hierarchy represented by the dimension tables:
used during analyses to restrict or increase the levels of detail of the analysis
organize the parameters of a dimension according to their level of detail. These
parameters will therefore be ordered by a hierarchical relationship 'is_more_refined'
The granularity of a dimension is defined as the number of levels.
of abstraction
Example:
3.1.6 Hierarchies of concepts of a dimension
A Concept is a value corresponding to a level of abstraction
Example: dimension Location
3.2. Operations on the data cube
Let's consider the following cuboid example; we can perform several operations on it.
interactive manipulation of cuboids
3.2.1 Slice Operation
It consists of a selection on one dimension of the cube–
3rd quarter on the Date dimension for visualization
Sales by Location and Product during this quarter
3.2.2 The Dice operation
It consists of defining a sub-cube by selection on two (or more) dimensions.
Ex: criterion (Location = Paris vs Rome) and (Date = 1st quarter vs 2nd quarter) and
Computing vs Telephony
3.2.3 The Pivot Operation
It allows for an alternative presentation of the cube.
Transformation into a series of 2D plans
– Reversal of the cube on one or more axes for an alternative view • Ex:
reversal on the Date axis
3.2.4 The Roll-up operation
It allows for a generalization of the cube; it consists of:
– Remove a dimension or
– Climb up a hierarchy of concepts from a
dimension
raise the level
Quarter at the level
Semester for Date
3.2.5 The Drill-down operation
Her role is the specialization of the cube. It involves
- Add a dimension • Ex: dimension ClientType
or
Descending into a hierarchy of concepts
• Ex: move from Category level to Type level for Product
3.3. Design of a data warehouse
3.3.1 Design Approaches
❖ There are three types of approaches: top-down, bottom-up, or one
combination of the two:
Bottom-up: start with planning and designing the model (mature)
Bottom-up: start with trials and prototypes (quickly)
❖ From the perspective of software engineering, this involves applying the models.
of the following life cycle:
Cascade: structured and systematic analysis at each step before moving on to the next.
next
Spiral: rapid generation of growing functional systems; rapid modifications and
easy model adaptation
❖ The process of designing a data warehouse includes the following steps
Choice of the process to model, e.g.: orders, sales, delivery
Choice of grain (level of detail of data) for the process
Choice of dimensions for each fact table
Choice of measures stored in the fact tables
3.3.2 Conceptual Schema of a Data Warehouse
The design of a data warehouse is a model at the logical level that is concise and
oriented towards a different subject than the entity-relationship model. Three types of schemas exist:
[Link] Star Schema
Characteristics of the star schema:
simple structure using the entity-relation model
a central entity: the fact table:
objects of the analysis
very large size
numerous fields
Peripheral entities: dimension tables:
dimensions of the analysis
small size
few fields
Example 1: Sales of medications in pharmacies
Star schema modeling the analysis of quantities and amounts of medications
in pharmacies according to 3 dimensions: time, category, and geographical location
Fact table: Sales
Dimension tables: Time, Category, Geography
Example 2: Sales of items in a supermarket
Associated with example 2:
a fact
3 copies were purchased for 1 euro.
of product pid3
by the client cid1
at the date did3
in the mid2 store
in the cid8 cart
corresponding to the promotion prid1
an element of the location dimension:
store id mid2
Rondpoint
City Blois
region centre
France
[Link] Snowflake Schema
A snowflake model is an evolution of the star schema with:
a breakdown of the dimensions of the star model into sub-hierarchies.
The fact is preserved and the dimensions are broken down according to its hierarchy.
parameters
This leads to a normalization of the dimension tables: hierarchical structure.
Lower dimensions and a lower level identify a higher level.
The advantages of this modeling are:
formalize a hierarchy within a dimension.
simplified maintenance of dimension tables
reduction of redundancy
The disadvantages of this modeling:
induces a denormalization of the dimensions leading to greater complexity in
terms of readability and management.
costly navigation
Example: Sales of medication in pharmacies
Each dimension of the previous star schema is denormalized, thus forming a sort of
of snowflake
[Link] Constellation diagram
Perhaps seen as a collection of stars (a galaxy or constellation of facts)
A constellation model:
merges several star schemas that use common dimensions.
therefore includes several facts and common or non-common dimensions
Sales of medication in pharmacies
A constellation consists of 2 star schemas:
One relates to sales made in pharmacies and
the other analyzes the prescriptions of the doctors
the dimensions Time and Geography are shared by the facts Prescriptions and
Sale.