Transcript MultiDimensional Databases

Multi-Dimensional Databases
& Online Analytical Processing
This presentation uses some materials from: “An Introduction to
Multidimensional Database Technology,” by Kenan Technologies.
Learning Objectives
1.
2.
3.
4.
5.
Multidimensional Databases
Contrast MDD and Relational Databases
When is MDD (In)appropriate?
MDD Features
Pros/Cons of MDD
1
What is a Multi-Dimensional
Database?
A multidimensional database (MDDB) is a computer
software system designed to allow for the efficient
and convenient storage and retrieval of large
volumes of data that are
(1) intimately related and
(2) stored, viewed and analyzed from different
perspectives. These perspectives are called
dimensions.
2
Contrasting Relational and MultiDimensional Models: An Example
SALES VOLUMES FOR GLEASON DEALERSHIP
MODEL
MINI VAN
MINI VAN
MINI VAN
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SEDAN
SEDAN
SEDAN
COLOR
BLUE
RED
WHITE
BLUE
RED
WHITE
BLUE
RED
WHITE
The Relational Structure
SALES VOLUME
6
5
4
3
5
5
4
3
2
Multidimensional Structure
Sales Volumes
Dimension
M
O
D
E
L
Mini Van
6
5
4
Coupe
3
5
5
Sedan
4
3
2
Blue
Red
White
COLOR
Positions
Measurement
Dimension
The “Classic” Star Schema
Fact Table
Store Dimension
STORE KEY
Store Description
City
State
District ID
District Desc.
Region_ID
Region Desc.
Regional Mgr.
STORE KEY
PRODUCT KEY
PERIOD KEY
Dollars
Units
Price
Product Dimension
PRODUCT KEY
Product Desc.
Brand
Color
Size
Manufacturer
Time Dimension
PERIOD KEY
Period Desc
Year
Quarter
Month
Day
Differences between MDDB and
Relational Databases
Normalized Relational
MDDB
Data reorganized based on
query. Perspectives are placed
in the fields – tells us nothing
about the contents
Perspectives embedded directly
in the structure.
Browsing and data manipulation Data retrieval and manipulation
are not intuitive to user
are easy
Slows down for large datasets
Fast retrieval for large datasets
due to multiple JOIN operations due to predefined structure.
needed.
Flexible. Anything an MDDB can Relatively Inflexible. Changes in
do, can be done this way.
perspectives necessitate
reprogramming of structure.
Contrasting Relational Model
and MDD-Example 2
SALES VOLUMES FOR ALL DEALERSHIPS
MODEL
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
MINI VAN
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
SEDAN
COLOR
BLUE
BLUE
BLUE
RED
RED
RED
WHITE
WHITE
WHITE
BLUE
BLUE
BLUE
RED
RED
RED
WHITE
WHITE
WHITE
BLUE
BLUE
BLUE
RED
RED
RED
WHITE
WHITE
WHITE
DEALERSHIP
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
CLYDE
GLEASON
CARR
VOLUME
6
6
2
3
5
5
2
4
3
2
3
2
7
5
2
4
5
1
6
4
2
1
3
4
2
2
3
Mutlidimensional Representation
Sales Volumes
M
O
D
E
L
Mini Van
Coupe
Carr
Gleason
Clyde
Sedan
Blue
Red
White
COLOR
DEALERSHIP
Viewing Data - An Example
Sales Volumes
M
O
D
E
L
DEALERSHIP
COLOR
•Assume that each dimension has 10 positions, as shown in the cube above
•How many records would be there in a relational table?
•Implications for viewing data from an end-user standpoint?
Adding Dimensions- An Example
Sales Volumes
M
O
D
E
L
Mini Van
Mini Van
Coupe
Mini Van
Coupe
Carr
Gleason
Clyde
Sedan
Blue
Red
White
COLOR
JANUARY
Coupe
Carr
Gleason
Clyde
Sedan
Blue
Red
White
COLOR
FEBRUARY
Carr
Gleason
Clyde
Sedan
Blue
Red
White
COLOR
MARCH
DEALERSHIP
3
When is MDD (In)appropriate?
First, consider situation 1
PERSONNEL
LAST NAME
SMITH
REGAN
FOX
WELD
KELLY
LINK
KRANZ
LUCUS
WEISS
EMPLOYEE#
01
12
31
14
54
03
41
33
23
EMPLOYEE AGE
21
19
63
31
27
56
45
41
19
When is MDD (In)appropriate?
Now consider situation 2
SALES VOLUMES FOR GLEASON DEALERSHIP
MODEL
MINI VAN
MINI VAN
MINI VAN
SPORTS COUPE
SPORTS COUPE
SPORTS COUPE
SEDAN
SEDAN
SEDAN
COLOR
BLUE
RED
WHITE
BLUE
RED
WHITE
BLUE
RED
WHITE
VOLUME
6
5
4
3
5
5
4
3
2
1. Set up a MDD structure for situation 1, with LAST NAME
and Employee# as dimensions, and AGE as the measurement.
2. Set up a MDD structure for situation 2, with MODEL and
COLOR as dimensions, and SALES VOLUME as the measurement.
When is MDD (In)appropriate?
MDD Structures for the Situations
Employee Age
Smith
21
Regan
19
Sales Volumes
Fox
M
O
D
E
L
Miini Van
6
5
4
Coupe
3
5
5
4
3
2
Blue
Red
White
Sedan
L
A
S
T
63
Weld
31
Kelly
N
A
M
E
27
Link
56
Kranz
45
COLOR
Lucas
41
Weiss
19
31
41
23
01
14
54
03
12
33
EMPLOYEE #
Note the sparseness in the second MDD representation
When is MDD (In)appropriate?
Highly interrelated dataset types be placed in a
multidimensional data structure for greatest ease of
access and analysis. When there are no
interrelationships, the MDD structure is not
appropriate.
4
MDD Features - Rotation
Sales Volumes
M
O
D
E
L
Mini Van
6
5
4
Coupe
3
5
5
Sedan
4
3
2
Blue
Red
C
O
L
O
R
o
( ROTATE 90 )
White
COLOR
View #1
Blue
6
3
4
Red
5
5
3
White
4
5
2
Mini Van Coupe
Sedan
M ODEL
View #2
•Also referred to as “data slicing.”
•Each rotation yields a different slice or two dimensional table
of data – a different face of the cube.
MDD Features - Rotation
Sales Volumes
M
O
D
E
L
Mini Van
C
O
L
O
R
Coupe
Carr
Gleason
Clyde
Sedan
Blue
Red
Red
Carr
Gleason
Clyde
White
Sedan
White
COLOR
Coupe
D
E
A
L
E
R
S
H
I
P
Mini Van
Coupe
Sedan
White
Red
Blue
COLOR
o
( ROTATE 90 )
MODEL
View #3
Mini Van
Carr
M
O
D
E
L
Gleason
Blue
Red
White
Clyde
Mini Van Coupe Sedan
o
Coupe
Blue
Red
White
Sedan
Clyde Gleason Carr
MODEL
( ROTATE 90 )
o
DEALERSHIP
( ROTATE 90 )
MODEL
View #4
DEALERSHIP
o
View #2
Gleason
Gleason Clyde
DEALERSHIP
View #1
Carr
Mini Van
Coupe
Sedan
( ROTATE 90 )
DEALERSHIP
Clyde
Red
White
Carr
MODEL
o
Blue
Mini Van
( ROTATE 90 )
D
E
A
L
E
R
S
H
I
P
C
O
L
O
R
Blue
COLOR
View #5
COLOR
View #6
MDD Features - Ranging
Sales Volumes
M
O
D
E
L
Mini Van
Mini Van
Coupe
Coupe
Normal
Blue
Carr
Clyde
Normal
Blue
Metal
Blue
Carr
Clyde
Metal
Blue
DEALERSHIP
COLOR
• The end user selects the desired positions along each dimension.
• Also referred to as "data dicing."
• The data is scoped down to a subset grouping
MDD Features - Roll-Ups & Drill
Downs
ORGANIZATION DIMENSION
Midwest
REGION
DISTRICT
DEALERSHIP
Chicago
Clyde
Gleason
St. Louis
Carr
Levi
Gary
Lucas
Bolton
• The figure presents a definition of a hierarchy within the
organization dimension.
• Aggregations perceived as being part of the same dimension.
• Moving up and moving down levels in a hierarchy is referred to
as “roll-up” and “drill-down.”
MDD Features:
Multidimensional Computations




Well equipped to handle demanding mathematical
functions.
Can treat arrays like cells in spreadsheets. For
example, in a budget analysis situation, one can
divide the ACTUAL array by the BUDGET array to
compute the VARIANCE array.
Applications based on multidimensional database
technology typically have one dimension defined as a
"business measurements" dimension.
Integrates computational tools very tightly with the
database structure.
The Time Dimension
TIME as a predefined hierarchy for rolling-up and
drilling-down across days, weeks, months, years and
special periods, such as fiscal years.


Eliminates the effort required to build sophisticated
hierarchies every time a database is set up.
Extra performance advantages
5
Pros/Cons of MDD





Cognitive Advantages for the User
Ease of Data Presentation and Navigation, Time
dimension
Performance
Less flexible
Requires greater initial effort