Modelo Multidimensional
Operaciones OLAP
2
El Modelo de Datos Multidimensional
• Vista multidimensional del data warehouse => influencia el diseño de labase 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 deanálisis, que proveen el contexto a las medidas, y se describen medianteatributos.
• 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.
3
id_fecha
id_producto
id_establec
importe
unidades
nro_clientes
Ventas
id_establec
nro_establec
nombre
dirección
distrito
ciudad
país
tlfno
fax
superficie
tipo_almacén
...
id_producto
nro_producto
descripción
marca
subcategoría
categoría
departamento
peso
unidades_peso
tipo_envase
dietético
...
Establecimiento
Producto
id_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
Tiempo
4
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 Cubo de dos dimensiones2D_ Cube
sale Product Client Amtp1 c1 12p2 c1 11p1 c3 50p2 c2 8
c1 c2 c3p1 12 50p2 11 8
5
Modelo de Datos Multidimensional
sale Product Client Date Amtp1 c1 1 12p2 c1 1 11p1 c3 1 50p2 c2 1 8p1 c1 2 44p1 c2 2 4
day 2 c1 c2 c3p1 44 4p2 c1 c2 c3
p1 12 50p2 11 8
day 1
Fact relation 3-dimensional cube
6
customer custId name address city53 joe 10 main sfo81 fred 12 main sfo
111 sally 80 willow la
product prodId name pricep1 bolt 10p2 nut 5
store storeId cityc1 nycc2 sfoc3 la
sale oderId date custId prodId storeId qty amto100 1/7/97 53 p1 c1 1 12o102 2/7/97 53 p2 c1 2 11o105 3/8/97 111 p1 c3 5 50
7
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);
8
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 Date Amtp1 c1 1 12p2 c1 1 11p1 c3 1 50p2 c2 1 8p1 c1 2 44p1 c2 2 4
81
result
9
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 Date Amtp1 c1 1 12p2 c1 1 11p1 c3 1 50p2 c2 1 8p1 c1 2 44p1 c2 2 4
Date sum1 812 48
result
10
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 Client Date Amtp1 c1 1 12p2 c1 1 11p1 c3 1 50p2 c2 1 8p1 c1 2 44p1 c2 2 4
sale Product Client Sump1 c1 56p1 c2 4
p1 c3 50 p2 c1 11
p2 c2 8
11.11.2. GROUP BY Modifiers
• Ver archivo del Manual de Referencia MySQL– MySql_groupBy_rollUp.docx
12
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)
c1 c2 c3 Sump1 56 4 50 110p2 11 8 19
Sum 67 12 50 129
13
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)
14
Cube Aggregation(Agregación del Cubo)
day 2 c1 c2 c3p1 44 4p2 c1 c2 c3
p1 12 50p2 11 8
c1 c2 c3p1 56 4 50p2 11 8
c1 c2 c3sum 67 12 50
sump1 110p2 19
129
. . .Ejemplo: calculando sumas
day 1
15
Cube OperatorsOperadores del Cubo
day 2 c1 c2 c3p1 44 4p2 c1 c2 c3
p1 12 50p2 11 8
c1 c2 c3p1 56 4 50p2 11 8
c1 c2 c3sum 67 12 50
sump1 110p2 19
129
. . .
sale(c1,*,*)
sale(*,*,*)sale(c2,p2,*)
day 1
16
Cube
c1 c2 c3 *p1 56 4 50 110p2 11 8 19* 67 12 50 129day 2 c1 c2 c3 *
p1 44 4 48p2* 44 4 48
c1 c2 c3 *p1 12 50 62p2 11 8 19* 23 8 50 81
day 1
*
sale(*,p2,*)
17
Agregación Usando Jerarquías
day 2 c1 c2 c3p1 44 4p2 c1 c2 c3
p1 12 50p2 11 8
day 1
region A region Bp1 12 50p2 11 8
customer
region
country
(customer c1 en Region A;customers c2, c3 en Region B)
18
Agregación Usando Jerarquías
c1c2
c3
c4
VideoCamera
New Orleans
Poznań
CD
Date of sale
1012
1112
3
5
711
219
715
Video Camera CDNO 22 8 30PN 23 18 22
agregación conrespecto a city
client
city
region
19
Ejemplo de Data Cube
sum
sum
sum
USA
Canada
Mexico
Country
Date
Product
CDvideocamera
1Q 2Q 3Q 4Q
20
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.
21
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
22
Datawarehouse de AAA Automobile Co.
DayWeekMonthQuarterYear
Date
Price Fact Table
Date
Dealer
Car
unit_prices
descount
total
Measurements
NameCityStateRegionPhone
Dealer
SerialNoModelColorCategory
Car
23
AAA Co. DataWarehouse
priceunitprice
datename
serialNo
desc.total
carserialNomodel
colorcategory
dealer
namecitystateregionphone
date
dayweekmonthyear
dateid
24
Esquema de la DataWarehouse AAA (tarea)
price 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
car serialno model color category
A1000 Lupo plata compacto
B2000 Jetta azul sport
C3000 Passat negro lujo
dealer name city state region phone
Dorada Puebla Puebla SW 7557705
Angelópolis Puebla Puebla SW 7566311
Centro Puebla Puebla SW 7557777
date dateId day week month quarter year
01-01-06 01 1 01 1 200601-03-06 01 1 03 1 200601-05-06 01 1 05 2 2006
25
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
26
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.
27
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
28
OLAP Servers
· OLAP Híbrido (HOLAP):· Da a los usuarios y administradores del sistema
la libertad para seleccionar particiones.
29
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
30
OLAP Queries
c1c2
c3
c4
videoCamera
New Orleans
Poznań
CD
Date of sale
1012
1112
3
5
711
219
715
Video Camera CDNO 22 8 30PN 23 18 22
city
region
client
roll up
31
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
32
OLAP Queries
day 2 c1 c2 c3p1 44 4p2 c1 c2 c3
p1 12 50p2 11 8
c1 c2 c3p1 56 4 50p2 11 8
c1 c2 c3sum 67 12 50
sump1 110p2 19
129
drill-down
rollup
day 1
33
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 cross-tabulation
· Si se pivotea el cubo Sales en las dimensiones Client y Product, se obtiene una tabla para cada client para cada valor de product
34
OLAP Queries· Pivoteo puede ser combinado con aggregation
sale prodId clientid date amtp1 c1 1 12p2 c1 1 11p1 c3 1 50p2 c2 1 8p1 c1 2 44p1 c2 2 4
day 2 c1 c2 c3p1 44 4p2 c1 c2 c3
p1 12 50p2 11 8
day 1
c1 c2 c3 Sump1 56 4 50 110p2 11 8 19
Sum 67 12 50 129
c1 c2 c3 Sum1 23 8 50 812 44 4 48
Sum 67 12 50 129
35
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