Modelo Multidimensional Operaciones OLAP
El Modelo de Datos Multidimensional
Vista multidimensional del data warehouse => influencia el diseo de la base de datos, las herramientas front-end, y los motores OLAP. Modelo multidimensional de datos: un conjunto de medidas numricas son los objetos de anlisis. Ej: ventas, beneficios, duracin de llamadas, etc.
Adicionalmente existen, asociadas a las medidas, las dimensiones de anlisis, 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 tambin representar datos agregados. Las dimensiones se pueden organizar en jerarquas de agregacin.
2
Tiempo
id_fecha da
Establecimiento
id_establec nro_establec nombre direccin distrito ciudad pas
Producto
id_producto nro_producto descripcin marca
semana mes ao da_semana da_mes
trimestre
festivo ....
subcategora
categora departamento peso unidades_peso tipo_envase diettico ...
Ventas
id_fecha id_producto
tlfno fax superficie tipo_almacn ...
id_establec
importe unidades nro_clientes
Modelo de Datos Multidimensional
Ventas de Productos podran ser representados en una dimensin (como una fact relation) o en dos dimensiones, e.j. : clients and products
Fact Relation Cubo de dos dimensiones 2D_ Cube
Amt 12 11 50 8
sale
Product Client p1 c1 p2 c1 p1 c3 p2 c2
c1 p1 p2 12 11
c2 8
c3 50
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 Amt 12 11 50 8 44 4
3-dimensional cube
day 2
day 1
p1 p2 c1 p1 12 p2 11
c1 44 c2 8
c2 4 c3 50
c3
product
prodId p1 p2
name price bolt 10 nut 5
store
storeId c1 c2 c3
city nyc sfo la
sale oderId date o100 1/7/97 o102 2/7/97 o105 3/8/97
custId 53 53 111
prodId p1 p2 p1
storeId c1 c1 c3
qty 1 2 5
amt 12 11 50
customer
custId 53 81 111
name joe fred sally
address 10 main 12 main 80 willow
city sfo sfo la
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);
Modelo de Datos Multidimensional y Funciones de Agregacin
Sumar las cantidades (Amt) del da 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
Modelo de Datos Multidimensional y Funciones de Agregacin
Sumar las cantidades por da 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
Modelo de Datos Multidimensional y Funciones de Agregacin 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 Agregacin
En el Modelo de Datos Multidimensional junto con valores de medicin se almacena informacin sumarizada (agregados)
c1 56 11 67 c2 4 8 12 c3 50 50 Sum 110 19 129
p1 p2 Sum
12
Funciones de Agregacin
Operadores: sum, count, max, min, average Clasula Having Usando Jerarquas de dimensin
Promedio por regin (tienda -- store) Mximo por mes (fecha -- date)
13
Cube Aggregation (Agregacin del Cubo)
Ejemplo: calculando sumas
day 2
day 1
p1 p2 c1 p1 12 p2 11 c1 44 c2 8 c2 4 c3 50 c3
...
p1 p2
c1 56 11
c2 4 8
c3 50
sum
c1 67
c2 12
c3 50
129
p1 p2 sum 110 19
14
Cube Operators Operadores del Cubo
day 2 day 1
c1 44 c2 8 c2 4 c3 50 c3
p1 p2 c1 p1 12 p2 11
... sale(c1,*,*)
sum c1 67 c2 12 c3 50
p1 p2
c1 56 11
c2 4 8
c3 50
129
p1 p2 sum 110 19
sale(c2,p2,*)
sale(*,*,*)
15
Cube
*
day 2
day 1
p1 p2 *
p1 p2 c1 * 12
11 23
p1 p2 * c1 44 c2 44 8 8
c1 56 11 67 c2 4 c3 4 50 50
c2 4 8 12 c3 * 62 19 81
c3 50
* 50 48 48
* 110 19 129
sale(*,p2,*)
16
Agregacin Usando Jerarquas
day 2 day 1
p1 p2 c1 p1 12 p2 11
c1 44 c2 8
c2 4 c3 50
c3
customer region country
p1 p2
region A region B 12 50 11 8
(customer c1 en Region A; customers c2, c3 en Region B)
17
Agregacin Usando Jerarquas
client
New Orleans Pozna
city
c1 c2 c3 10 3 12 5 11 7 12 11 21 9 7 15
region
Date of sale
c4
CD Video Camera
agregacin 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 tpicas:
encontrar las ventas totales por da, semana, mes y ao encontrar las ventas totales semana, mes y ao, ... para cada agencia encontrar las ventas totales semana, mes y ao, ... Para cada modelo de carro encontrar las ventas totales por mes para todos las agencias en una ciudad, regin y estado dados.
20
Ejercicio (2)
Dimensiones:
time (day, week, month, quarter, year) dealer (name, city, state, region, phone) cars (serialno, model, color, category , )
Disee el esquema conceptual de la datawarehouse
21
Datawarehouse de AAA Automobile Co.
Date
Day Week Month Quarter Year
Dealer Price Fact Table Date
Name City State Region Phone
Dealer
Car
Car
SerialNo Model Color Category
unit_prices descount total
Measurements
22
AAA Co. DataWarehouse
price unitprice date name serialNo desc. total
dealer name city state region phone
car serialNo model color category
date dateid day week month year
23
Esquema de la DataWarehouse AAA (tarea)
car
date
serialno A1000 B2000 C3000
model Lupo Jetta Passat
color plata azul negro
category compacto sport lujo
dateId
01-01-06 01-03-06 01-05-06
day
week
month
quarter
year
01 01 01
1 1 1
01 03 05
1 1 2
2006 2006 2006
price
unitprice
110000 160000 280000
date
01-01-06 01-03-06 01-05-06
name
Dorada Angelpolis Centro
serialno
A1000 B2000 C3000
desc
10 15 25
total
99000 136000 210000
dealer
name
city
state
region
phone
Dorada
Angelpolis Centro
Puebla
Puebla Puebla
Puebla
Puebla Puebla
SW
SW SW
7557705
7566311 7557777
24
Consultas a AAA Datawarehouse
encontrar las ventas totales por da, semana, mes y ao
SELECT day, sum(total)
FROM PRICE, DATE WHERE date= dateid GROUP BY day
AADIR ROLLUP
25
Consultas a AAA Datawarehouse
encontrar las ventas totales por da, semana, mes y ao encontrar las ventas totales semana, mes y ao, ... para cada agencia encontrar las ventas totales semana, mes y ao, ... Para cada modelo de carro encontrar las ventas totales por mes para todos las agencias en una ciudad, regin 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 informacin incluyendo fact tables como relaciones
Multidimensional OLAP (MOLAP):
Servidor de propsito especial que directamente implementa operaciones y datos multidimensionales Almacena conjuntos de datos multidimensionales como arreglos
27
OLAP Servers
OLAP Hbrido (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 jerarqua de dimensin
Si sabemos el volumen total de ventas por ciudad es posible agregar sobre la ubicain (location) para obtener ventas por estado
29
OLAP Queries
client
city
New Orleans c1 c2 c3 10 3 12 5 11 7 12 11 21 9 7 15
region
Date of sale
Pozna
c4
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 categora 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
c1 44 c2 8 c2 4 c3 50 c3
day 1
p1 p2 c1 p1 12 p2 11
p1 p2
c1 56 11
c2 4 8
c3 50
sum
c1 67
c2 12
c3 50
129
p1 p2 sum 110 19
32
rollup drill-down
OLAP Queries
Slice and dice: select and project
Ventas de video en USA en los ltimos 6 meses Slicing and dicing reducen el nmero 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 date 1 1 1 1 2 2 amt 12 11 50 8 44 4
ser
combinado
con
day 2 day 1
p1 p2 c1 p1 12 p2 11
c1 44 c2 8
c2 4 c3 50
c3
1 2 Sum
c1 23 44 67
c2 8 4 12
c3 50 50
Sum 81 48 129
p1 p2 Sum
c1 56 11 67
c2 4 8 12
c3 50 50
Sum 110 19 129
34
OLAP Queries
Ranking: seleccin 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
