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