Modelo Multidimensional
Operaciones OLAP
El Modelo de Datos Multidimensional
• Vista multidimensional del data warehouse => influencia el diseño de la
base de datos, las herramientas front-end, y los motores OLAP.
•
Modelo multidimensional de datos: un conjunto de medidas numéricas son
los objetos de análisis.
– Ej: ventas, beneficios, duración de llamadas, etc.
• Adicionalmente existen, asociadas a las medidas, las dimensiones de
análisis, que proveen el contexto a las medidas, y se describen mediante
atributos.
• El modelo define una medida como un valor en un espacio multidimensional.
Estas medidas pueden también representar datos agregados.
• Las dimensiones se pueden organizar en jerarquías de agregación.
2
Tiempo
Establecimiento
id_fecha
id_establec
día
Producto
id_producto
nro_producto
descripción
marca
subcategoría
categoría
departamento
peso
unidades_peso
tipo_envase
dietético
...
nro_establec
semana
nombre
mes
dirección
año
distrito
día_semana
ciudad
día_mes
país
trimestre
festivo
....
Ventas
id_fecha
id_producto
id_establec
importe
tlfno
fax
superficie
tipo_almacén
...
unidades
nro_clientes
3
Modelo de Datos Multidimensional
Ventas de Productos podrían ser representados en
una dimensión (como una fact relation) o en dos
dimensiones,
e.j. : clients and products
Fact Relation
sale
Product Client
p1
c1
p2
c1
p1
c3
p2
c2
Cubo de dos dimensiones
2D_ Cube
Amt
12
11
50
8
c1
p1
12
p2
11
c2
c3
50
8
4
Modelo de Datos Multidimensional
Fact relation
sale
Product Client
p1
c1
p2
c1
p1
c3
p2
c2
p1
c1
p1
c2
Date
1
1
1
1
2
2
3-dimensional cube
Amt
12
11
50
8
44
4
day 2
day 1
p1
p2 c 1
p1
12
p2
11
c1
c2
44
4
c2
c3
c3
50
8
5
p ro d u c t
p ro d Id
nam e
p ric e
p1
bo lt
10
p2
nut
5
sale oderId date
o100 1/7/97
o102 2/7/97
o105 3/8/97
c u s to m e r
s to re
custId
53
53
111
prodId
p1
p2
p1
storeId
c1
c1
c3
s to re Id
c ity
c1
ny c
c2
s fo
c3
la
qty
1
2
5
amt
12
11
50
c u s tId
nam e
a d d re s s
c ity
53
jo e
1 0 ma in
s fo
81
fre d
1 2 ma in
s fo
111
s a lly
8 0 w illo w
la
6
Crear Esquema e Insertar valores
CREATE DATABASE VENTAS_DM,
USE VENTAS_DM;
CREATE TABLE SALE (product char(2), client char(2), date char(1), amt int);
INSERT INTO SALE VALUES ( 'p1', 'c1', '1', 12), ( 'p2', 'c1', '1', 11),
( 'p1', 'c3', '1', 50), ( 'p2', 'c2', '1', 8), ( 'p1', 'c1', ‘2', 44), ( 'p1', 'c2', ‘2', 4);
7
Modelo de Datos Multidimensional y
Funciones de Agregación
• Sumar las cantidades (Amt) del día 1 (Date)
• En SQL: SELECT sum(Amt)
FROM SALE
WHERE Date = 1
sale
Product Client
p1
c1
p2
c1
p1
c3
p2
c2
p1
c1
p1
c2
Date
1
1
1
1
2
2
Amt
12
11
50
8
44
4
result
81
8
Modelo de Datos Multidimensional y
Funciones de Agregación
• Sumar las cantidades por día
• En SQL: SELECT Date, sum(Amt)
FROM SALE
GROUP BY Date
sale
Product Client
p1
c1
p2
c1
p1
c3
p2
c2
p1
c1
p1
c2
Date
1
1
1
1
2
2
Amt
12
11
50
8
44
4
result
Date
1
2
sum
81
48
9
Modelo de Datos Multidimensional y
Funciones de Agregación
• Sumar cantidades por client, product
• En SQL: SELECT product, client, sum(amt)
FROM SALE
GROUP BY product, client
sale
Product
p1
p2
p1
p2
p1
p1
Client
c1
c1
c3
c2
c1
c2
Date
1
1
1
1
2
2
Amt
12
11
50
8
44
4
sale Product Client
p1
c1
p1
c2
p1
c3
p2
c1
p2
c2
Sum
56
4
50
11
8
10
11.11.2. GROUP BY Modifiers
• Ver archivo del Manual de Referencia MySQL
– MySql_groupBy_rollUp.docx
Modelo de Datos Multidimensional y
Funciones de Agregación
• En el Modelo de Datos Multidimensional
junto con valores de medición se almacena
información sumarizada (agregados)
p1
p2
Sum
c1
56
11
67
c2
4
8
12
c3
50
50
Sum
110
19
129
12
Funciones de Agregación
• Operadores: sum, count, max, min, average
• Claúsula “Having”
• Usando Jerarquías de dimensión
– Promedio por región (tienda -- store)
– Máximo por mes (fecha -- date)
13
Cube Aggregation
(Agregación del Cubo)
day 2
p1
p2 c 1
day 1
p1
12
p2
11
c1
c2
44
4
c2
c3
Ejemplo: calculando sumas
...
c3
50
8
sum
c1
c2
c3
p1
56
4
50
p2
11
8
c1
67
c2
12
c3
50
129
p1
p2
sum
110
19
14
Cube Operators
Operadores del Cubo
day 2
p1
p2 c 1
day 1
p1
12
p2
11
c1
c2
44
4
c2
c3
...
c3
50
sale(c1,*,*)
8
c1
c2
c3
p1
56
4
50
p2
11
8
sale(c2,p2,*)
sum
c1
67
c2
12
c3
50
129
p1
p2
sum
110
19
sale(*,*,*)
15
Cube
c2
4
8
c312
p1
p2
c1
*
12
p1
p2
c1*
44
c1
56
11
c267
4
c2
44
c3
4
50
11
23
8
8
50
*
62
19
81
*
day 2
day 1
p1
p2
*
c3
50
* 50
48
48
*
110
19
129
sale(*,p2,*)
16
Agregación Usando Jerarquías
day 2
day 1
p1
p2 c 1
p1
12
p2
11
c1
c2
44
4
c2
c3
customer
c3
50
region
8
country
p1
p2
region A region B
12
50
11
8
(customer c1 en Region A;
customers c2, c3 en Region B)
17
Agregación Usando Jerarquías
client
city
New
Orleans
Poznań
c1
c2
c3
c4
10 3
12
5
11 7
12 11
21
9
7
15
region
Date of
sale
CD
Video
Camera
agregación con
respecto a city
NO
PN
Video
22
23
Camera
8
18
CD
30
22
18
Ejemplo de Data Cube
Date
camera
video
CD
sum
1Q
2Q
3Q
4Q
sum
USA
Canada
Mexico
sum
C
o
u
n
t
r
y
19
Ejercicio (1)
• Suponga que AAA Automobile Co. construye una data
warehouse para analizar las ventas de sus autos.
• La medida measure - price de un auto
• Se necesita responder las siguientes consultas típicas:
– encontrar las ventas totales por día, semana, mes y año
– encontrar las ventas totales semana, mes y año, ... para cada
agencia
– encontrar las ventas totales semana, mes y año, ... Para cada
modelo de carro
– encontrar las ventas totales por mes para todos las agencias
en una ciudad, región y estado dados.
20
Ejercicio (2)
• Dimensiones:
– time (day, week, month, quarter, year)
– dealer (name, city, state, region, phone)
– cars (serialno, model, color, category , …)
• Diseñe el esquema conceptual de la datawarehouse
21
Datawarehouse de AAA Automobile Co.
Dealer
Date
Day
Week
Month
Quarter
Year
Car
SerialNo
Model
Color
Category
Price Fact Table
Date
Dealer
Name
City
State
Region
Phone
Car
unit_prices
descount
total
Measurements
22
AAA Co. DataWarehouse
dealer
name
city
state
region
phone
price
unitprice
date
name
serialNo
desc.
total
car
serialNo
model
color
category
date
dateid
day
week
month
year
23
Esquema de la DataWarehouse AAA (tarea)
car
date
serialno
model
color
category
dateId
day
week
month
quarter
year
A1000
Lupo
plata
compacto
01-01-06
01
1
01
1
2006
B2000
Jetta
azul
sport
01-03-06
01
1
03
1
2006
C3000
Passat
negro
lujo
01-05-06
01
1
05
2
2006
price
dealer
unitprice
date
name
serialno
desc
total
110000
01-01-06
Dorada
A1000
10
99000
160000
01-03-06
Angelópolis
B2000
15
136000
280000
01-05-06
Centro
C3000
25
210000
name
city
state
region
phone
Dorada
Puebla
Puebla
SW
7557705
Angelópolis
Puebla
Puebla
SW
7566311
Centro
Puebla
Puebla
SW
7557777
24
Consultas a AAA Datawarehouse
– encontrar las ventas totales por día, semana, mes
y año
SELECT day, sum(total)
FROM PRICE, DATE
WHERE date= dateid
GROUP BY day
• AÑADIR ROLLUP
25
Consultas a AAA Datawarehouse
– encontrar las ventas totales por día, semana, mes
y año
– encontrar las ventas totales semana, mes y año, ...
para cada agencia
– encontrar las ventas totales semana, mes y año, ...
Para cada modelo de carro
– encontrar las ventas totales por mes para todos
las agencias en una ciudad, región y estado dados.
26
OLAP Servers
 Relacional OLAP (ROLAP):
 DBMS relacional extendido que mapea operaciones
en datos multidimensionales a operaciones
relacionales estandar
 Almacen toda la información incluyendo fact tables
como relaciones
 Multidimensional OLAP (MOLAP):
 Servidor de propósito especial que directamente
implementa
operaciones
y
datos
multidimensionales
 Almacena conjuntos de datos multidimensionales
como arreglos
27
OLAP Servers
 OLAP Híbrido (HOLAP):
 Da a los usuarios y administradores del sistema
la libertad para seleccionar particiones.
28
OLAP Queries (Consultas)
 Roll up: resume datos dentro de una
jerarquía de dimensión
 Si sabemos el volumen total de ventas por
ciudad es posible agregar sobre la ubicaión
(location) para obtener ventas por estado
29
OLAP Queries
client
city
c1
c2
New
Orleans
c3
Poznań
c4
10 3
12
5
11 7
12 11
21
9
7
15
region
Date of
sale
CD
video
Camera
roll up
NO
PN
Video
22
23
Camera
8
18
CD
30
22
30
OLAP Queries
 Roll down, drill down: ir desde alto nivel de
resumen hasta bajo nivel de resumen o datos
detallados
 Para una categoría de producto particular,
encontrar el detalle de ventas para cada
vendedor por fecha
 Dado el total de ventas por estado, se pueden
pedir las ventas por ciudad; o solo las ventas por
ciudad para un estado seleccionado
31
OLAP Queries
day 2
p1
c1
c2
44
4
p2 c 1
day 1
p1
12
p2
11
c2
c3
c3
50
8
c1
c2
c3
p1
56
4
50
p2
11
8
rollup
drill-down
sum
c1
67
c2
12
c3
50
129
p1
p2
sum
110
19
32
OLAP Queries
• Slice and dice: select and project
 Ventas de video en USA en los últimos 6 meses
 Slicing and dicing reducen el número de
dimensiones
 Pivot: reorientar el cubo
 El resultado del pivoteo es llamado crosstabulation
 Si se pivotea el cubo Sales en las dimensiones
Client y Product, se obtiene una tabla para cada
client para cada valor de product
33
OLAP Queries
 Pivoteo
puede
aggregation
sale
prodId clientid
p1
c1
p2
c1
p1
c3
p2
c2
p1
c1
p1
c2
1
2
Sum
c1
23
44
67
c2
8
4
12
date
1
1
1
1
2
2
c3
50
50
amt
12
11
50
8
44
4
Sum
81
48
129
ser
combinado
day 2
day 1
p1
p2
Sum
p1
c1
c2
44
4
p2 c 1
p1
12
p2
11
c1
56
11
67
con
c2
4
8
12
c2
c3
c3
50
8
c3
50
50
Sum
110
19
129
34
OLAP Queries
 Ranking: selección de los primeros n elementos (e.j.
select los 5 mejores productos comprados en Julio)
 Otros: stored procedures, etc.
• Time functions
– e.j., time average
35
Descargar

Modelo Multidimensional