DatawarehouseDatawarehouse(Almacenes de Datos(Almacenes de Datos))
Caso de Estudio: VentasCaso de Estudio: Ventasalal detaldetal
DatawarehouseDatawarehouse(Almacenes de Datos(Almacenes de Datos))
Caso de Estudio: VentasCaso de Estudio: Ventasalal detaldetal
Prof. Concettina Di Vasta
Contenido
Ejemplo de un Diseño de Almacenes de DatosPasos para el diseño de un DW.
Actividad Práctica
218/12/2013 Almacenes de Datos - UCV
EJEMPLO
Organización: Cadena de supermercados.
Actividad objeto de análisis: ventas de productos.
Información registrada sobre una venta: “delproducto “Tauritón 33cl” se han vendido en elalmacén “Almacén nro.1” el día 17/7/2003, 5unidades por un importe de 103,19 euros.”
Diseño de Almacenes de Datos
EJEMPLO
Organización: Cadena de supermercados.
Actividad objeto de análisis: ventas de productos.
Información registrada sobre una venta: “delproducto “Tauritón 33cl” se han vendido en elalmacén “Almacén nro.1” el día 17/7/2003, 5unidades por un importe de 103,19 euros.”
Para hacer el análisis no interesa la venta individual(ticket) realizada a un cliente sino las ventas diarias deproductos en los distintos almacenes de la cadena.
18/12/20133
Almacenes de Datos - UCV
El desarrollo de la tecnología de almacenes de datos se hacaracterizado por:
- el uso de metodologías de diseño centradasprincipalmente en los niveles lógico e interno. (laatención se ha centrado en mejorar la eficiencia en laejecución de consultas)
Diseño de Almacenes de Datos
Metodología de diseño basada en elmodelo relacional: Modelo
multidimensional de Kimball
18/12/20134
Almacenes de Datos - UCV
Pasos en el diseño del almacén de datos:
• Paso 1. Elegir un “proceso” de la organización paramodelar.
• Paso 2. Decidir la granularidad (nivel de detalle) derepresentación del proceso.
• Paso 3. Identificar las dimensiones que caracterizan elproceso.
• Paso 4. Decidir la información a almacenar sobre elproceso.
Diseño de Almacenes de Datos
• Paso 1. Elegir un “proceso” de la organización paramodelar.
• Paso 2. Decidir la granularidad (nivel de detalle) derepresentación del proceso.
• Paso 3. Identificar las dimensiones que caracterizan elproceso.
• Paso 4. Decidir la información a almacenar sobre elproceso.
18/12/20135
Almacenes de Datos - UCV
Paso 1. Elegir un “proceso” de la organizaciónpara modelar.
Proceso: actividad de la organización soportadapor un OLTP del cual se puede extraer informacióncon el propósito de construir el almacén de datos.
Pedidos (de clientes)
Compras (a suministradores)
Facturación
Envíos
Ventas
Inventario
…
Diseño de Almacenes de Datos
Paso 1. Elegir un “proceso” de la organizaciónpara modelar.
Proceso: actividad de la organización soportadapor un OLTP del cual se puede extraer informacióncon el propósito de construir el almacén de datos.
Pedidos (de clientes)
Compras (a suministradores)
Facturación
Envíos
Ventas
Inventario
…
18/12/20136
Almacenes de Datos - UCV
Ejemplo: Cadena de supermercados.
Cadena de supermercados con 300 almacenes en laque se expenden unos 30.000 productos distintos.
Actividad: Ventas.
La actividad a modelar son las ventas de productosen los almacenes de la cadena.
Diseño de Almacenes de Datos
Ejemplo: Cadena de supermercados.
Cadena de supermercados con 300 almacenes en laque se expenden unos 30.000 productos distintos.
Actividad: Ventas.
La actividad a modelar son las ventas de productosen los almacenes de la cadena.
18/12/20137
Almacenes de Datos - UCV
Paso 2. Decidir el granularidad (nivel de detalle) derepresentación.
Granularidad: es el nivel de detalle de la información aalmacenar sobre la actividad a modelar.
La granularidad define el nivel atómico de datosen el almacén de datos.
La granularidad determina el significado de lastuplas de la tabla de hechos.
La granularidad determina las dimensiones básicasdel esquema
transacción en el OLTP
información diaria
información semanal
información mensual. ....
Diseño de Almacenes de Datos
Paso 2. Decidir el granularidad (nivel de detalle) derepresentación.
Granularidad: es el nivel de detalle de la información aalmacenar sobre la actividad a modelar.
La granularidad define el nivel atómico de datosen el almacén de datos.
La granularidad determina el significado de lastuplas de la tabla de hechos.
La granularidad determina las dimensiones básicasdel esquema
transacción en el OLTP
información diaria
información semanal
información mensual. ....
18/12/20138
Almacenes de Datos - UCV
Ejemplo: Cadena de supermercados.
Granularidad: “se desea almacenar información sobrelas ventas diarias de cada producto en cada almacénde la cadena”.
Granularidad:
define el significado de las tuplas de la tabla dehechos.
determina las dimensiones básicas del esquema.
Diseño de Almacenes de Datos
Ejemplo: Cadena de supermercados.
Granularidad: “se desea almacenar información sobrelas ventas diarias de cada producto en cada almacénde la cadena”.
Granularidad:
define el significado de las tuplas de la tabla dehechos.
determina las dimensiones básicas del esquema.
producto
día
almacén
ventas
18/12/20139
Almacenes de Datos - UCV
producto
día
almacén
ventas
Diseño de Almacenes de Datos
id_producto
id_fecha
id_almacén
.....
.....
......
tabla dehechos la clave primaria está
formada por losidentificadores de lasdimensiones básicas.
datos (medidas) sobre lasventas diarias de unproducto en un almacén.
18/12/201310
Almacenes de Datos - UCV
Paso 3. Identificar las dimensiones que caracterizan el proceso.
Dimensiones: dimensiones que caracterizan la actividad alnivel de detalle (granularidad) que se ha elegido.
Tiempo (dimensión temporal: ¿cuándo se produce laactividad?)
Producto (dimensión ¿cuál es el objeto de la actividad?)
Almacén (dimensión geográfica: ¿dónde se produce laactividad?)
Cliente (dimensión ¿quién es el destinatario de laactividad?)
De cada dimensión se debe decidir los atributos (propiedades)relevantes para el análisis de la actividad.
Entre los atributos de una dimensión existen jerarquíasnaturales que deben ser identificadas (día-mes-año)
Diseño de Almacenes de Datos
Paso 3. Identificar las dimensiones que caracterizan el proceso.
Dimensiones: dimensiones que caracterizan la actividad alnivel de detalle (granularidad) que se ha elegido.
Tiempo (dimensión temporal: ¿cuándo se produce laactividad?)
Producto (dimensión ¿cuál es el objeto de la actividad?)
Almacén (dimensión geográfica: ¿dónde se produce laactividad?)
Cliente (dimensión ¿quién es el destinatario de laactividad?)
De cada dimensión se debe decidir los atributos (propiedades)relevantes para el análisis de la actividad.
Entre los atributos de una dimensión existen jerarquíasnaturales que deben ser identificadas (día-mes-año)
18/12/201311
Almacenes de Datos - UCV
Ejemplo: Cadena de supermercados.
definición degranularidad
dimensionesbásicas
tiempo
producto
almacén
Diseño de Almacenes de Datos
almacén
Nota: En las aplicaciones reales el número dedimensiones suele variar entre 3 y 15 dimensiones.
18/12/201312
Almacenes de Datos - UCV
Dimensión Tiempo:
dimensión presente en todo DW porque el DW contieneinformación histórica sobre la organización.
aunque el lenguaje SQL ofrece funciones de tipo DATE, unadimensión Tiempo permite representar otros atributostemporales no calculables en SQL.
atributos frecuentes:– nro. de día, nro. de semana, nro. de año: valores absolutos delcalendario juliano que permiten hacer ciertos cálculos aritméticos.
– día de la semana (lunes, martes, miércoles,...): permite haceranálisis sobre días de la semana concretos (ej. ventas en sábado,ventas en lunes,..).
Diseño de Almacenes de Datos
dimensión presente en todo DW porque el DW contieneinformación histórica sobre la organización.
aunque el lenguaje SQL ofrece funciones de tipo DATE, unadimensión Tiempo permite representar otros atributostemporales no calculables en SQL.
atributos frecuentes:– nro. de día, nro. de semana, nro. de año: valores absolutos delcalendario juliano que permiten hacer ciertos cálculos aritméticos.
– día de la semana (lunes, martes, miércoles,...): permite haceranálisis sobre días de la semana concretos (ej. ventas en sábado,ventas en lunes,..).
18/12/201313
Almacenes de Datos - UCV
Dimensión Tiempo:
atributos frecuentes: día del mes (1..31): permite hacer comparaciones sobre el
mismo día en meses distintos (ventas el 1º de mes). marca de fin de mes, marca de fin de semana : permite hacer
comparaciones sobre el último día del mes o días de fin desemana en distintos meses.
trimestre del año (1..4): permite hacer análisis sobre untrimestre concreto en distintos años.
marca de día festivo: permite hacer análisis sobre los díascontiguos a un día festivo.
estación (primavera, verano..) evento especial: permite marcar días de eventos especiales
(final de futbol, elecciones...)
jerarquía natural:día - mes - trimestre -año
Diseño de Almacenes de Datos
atributos frecuentes: día del mes (1..31): permite hacer comparaciones sobre el
mismo día en meses distintos (ventas el 1º de mes). marca de fin de mes, marca de fin de semana : permite hacer
comparaciones sobre el último día del mes o días de fin desemana en distintos meses.
trimestre del año (1..4): permite hacer análisis sobre untrimestre concreto en distintos años.
marca de día festivo: permite hacer análisis sobre los díascontiguos a un día festivo.
estación (primavera, verano..) evento especial: permite marcar días de eventos especiales
(final de futbol, elecciones...)
jerarquía natural:día - mes - trimestre -año
18/12/201314
Almacenes de Datos - UCV
Dimensión Producto:
la dimensión Producto se define a partir del fichero maestro deproductos del sistema OLTP.
las actualizaciones del fichero maestro de productos debenreflejarse en la dimensión Producto
la dimensión Producto debe contener el mayor número posiblede atributos descriptivos que permitan un análisis flexible. Unnúmero frecuente es de 50 atributos.
atributos frecuentes: identificador (código estándar),descripción, tamaño del envase, marca, categoría,departamento, tipo de envase, producto dietético, peso,unidades de peso, unidades por envase, fórmula, ...
jerarquías: producto-categoría-departamento
Diseño de Almacenes de Datos
la dimensión Producto se define a partir del fichero maestro deproductos del sistema OLTP.
las actualizaciones del fichero maestro de productos debenreflejarse en la dimensión Producto
la dimensión Producto debe contener el mayor número posiblede atributos descriptivos que permitan un análisis flexible. Unnúmero frecuente es de 50 atributos.
atributos frecuentes: identificador (código estándar),descripción, tamaño del envase, marca, categoría,departamento, tipo de envase, producto dietético, peso,unidades de peso, unidades por envase, fórmula, ...
jerarquías: producto-categoría-departamento
18/12/201315
Almacenes de Datos - UCV
Dimensión Almacén : Representa la información geográfica básica.
Esta dimensión suele ser creada explícitamente recopilandoinformación externa que sólo tiene sentido en el DW y que no latiene en un OLTP (número de habitantes de la ciudad del almacén,caracterización del tipo de población del distrito, ...)
atributos frecuentes: identificador (código interno), nombre,dirección, distrito, región, ciudad, país, director, teléfono, fax, tipo dealmacén, superficie, fecha de apertura, fecha de la últimaremodelación, superficie para congelados, superficie para productosfrescos, datos de la población del distrito, zona de ventas, ...
jerarquías:
– almacén - distrito - ciudad - región - país (jerarquía geográfica)
– almacén - zona_ventas - región_ventas (jerarquía de ventas)
Diseño de Almacenes de Datos
Representa la información geográfica básica.
Esta dimensión suele ser creada explícitamente recopilandoinformación externa que sólo tiene sentido en el DW y que no latiene en un OLTP (número de habitantes de la ciudad del almacén,caracterización del tipo de población del distrito, ...)
atributos frecuentes: identificador (código interno), nombre,dirección, distrito, región, ciudad, país, director, teléfono, fax, tipo dealmacén, superficie, fecha de apertura, fecha de la últimaremodelación, superficie para congelados, superficie para productosfrescos, datos de la población del distrito, zona de ventas, ...
jerarquías:
– almacén - distrito - ciudad - región - país (jerarquía geográfica)
– almacén - zona_ventas - región_ventas (jerarquía de ventas)
18/12/201316
Almacenes de Datos - UCV
id_almacen
nro_almacen
nombre
dirección
distrito
ciudad
país
tlfno
fax
superficie
tipo_almacén
...
Almacénid_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
Tiempoid_producto
nro_producto
descripción
marca
subcategoría
categoría
departamento
peso
unidades_peso
tipo_envase
dietético
...
Producto
Diseño de Almacenes de Datos
id_almacen
nro_almacen
nombre
dirección
distrito
ciudad
país
tlfno
fax
superficie
tipo_almacén
...
id_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
id_producto
nro_producto
descripción
marca
subcategoría
categoría
departamento
peso
unidades_peso
tipo_envase
dietético
...
18/12/201317
Almacenes de Datos - UCV
id_almacen
nro_almacen
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
...
Producto
id_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
Tiempo Almacén
Diseño de Almacenes de Datos
id_fecha
id_producto
id_almacen
...
...
...
Ventas
id_almacen
nro_almacen
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
...
id_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
18/12/201318
Almacenes de Datos - UCV
Paso 4. Decidir la información a almacenar sobre elproceso.
Hechos: información (sobre la actividad) que sedesea almacenar en cada tupla de la tabla dehechos y que será el objeto del análisis.
Precio
Unidades
Importe
....
Diseño de Almacenes de Datos
Paso 4. Decidir la información a almacenar sobre elproceso.
Hechos: información (sobre la actividad) que sedesea almacenar en cada tupla de la tabla dehechos y que será el objeto del análisis.
Precio
Unidades
Importe
....
18/12/201319
Almacenes de Datos - UCV
Ejemplo: Cadena de supermercados.
Granularidad: “se desea almacenar información sobre lasventas diarias de cada producto en cada almacén de lacadena”.
– importe total de las ventas del producto en el día
– número total de unidades vendidas del producto en el día
– número total de clientes distintos que han comprado elproducto en el día.
Diseño de Almacenes de Datos
– importe total de las ventas del producto en el día
– número total de unidades vendidas del producto en el día
– número total de clientes distintos que han comprado elproducto en el día.
18/12/201320
Almacenes de Datos - UCV
id_almacen
nro_almacen
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
...
Producto
id_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
Tiempo Almacén
Diseño de Almacenes de Datos
id_fecha
id_producto
id_almacen
importe
unidades
nro_clientes
Ventas
id_almacen
nro_almacen
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
...
id_fecha
día
semana
mes
año
día_semana
día_mes
trimestre
festivo
....
18/12/201321
Almacenes de Datos - UCV
Otras orientaciones de diseño: usar claves sin significado.
evitar normalizar.
incluir la dimensión Tiempo.
dimensiones “que cambian”.
definición de agregados.
Diseño de Almacenes de Datos
Otras orientaciones de diseño: usar claves sin significado.
evitar normalizar.
incluir la dimensión Tiempo.
dimensiones “que cambian”.
definición de agregados.
18/12/201322
Almacenes de Datos - UCV
Otras orientaciones de diseño:uso de claves sin significado.
– en un almacén de datos debe evitarse el uso de las clavesdel sistema operacional.
– las claves de las dimensiones deben ser generadasartificialmente: claves de tipo entero (4 bytes) sonsuficiente para dimensiones de cualquier tamaño (232
valores distintos).
– la dimensión TIEMPO debe tener también una claveartificial.
Diseño de Almacenes de Datos
– en un almacén de datos debe evitarse el uso de las clavesdel sistema operacional.
– las claves de las dimensiones deben ser generadasartificialmente: claves de tipo entero (4 bytes) sonsuficiente para dimensiones de cualquier tamaño (232
valores distintos).
– la dimensión TIEMPO debe tener también una claveartificial.
18/12/201323
Almacenes de Datos - UCV
Otras Orientaciones de diseño:evitar normalizar.
Si se define una tabla de dimensión para cada dimensiónidentificada en el análisis, es frecuente que entre elconjunto de atributos de la tabla aparezcan dependenciasfuncionales que hacen que la tabla no esté en 3ª F.N.
Diseño de Almacenes de Datos
Si se define una tabla de dimensión para cada dimensiónidentificada en el análisis, es frecuente que entre elconjunto de atributos de la tabla aparezcan dependenciasfuncionales que hacen que la tabla no esté en 3ª F.N.
Evitar normalizar:
el ahorro de espacio no es significativo
se multiplican los JOIN durante las consultas.
18/12/201324
Almacenes de Datos - UCV
En un almacén de Datos muchas consultas sonrestringidas y parametrizadas por criteriosrelativos a periodos de tiempo (último mes, esteaño, ...).
Otras Orientaciones de diseño: siempre introducir la dimensión Tiempo.
Diseño de Almacenes de Datos
En un almacén de Datos muchas consultas sonrestringidas y parametrizadas por criteriosrelativos a periodos de tiempo (último mes, esteaño, ...).
18/12/201325
Almacenes de Datos - UCV
Otras orientaciones de diseño:dimensiones “que cambian”.
Se considera relevante el caso en que, en el mundo real, paraun valor de una dimensión, cambia el valor de un atributo quees significativo para el análisis sin cambiar el valor de su clave.
Diseño de Almacenes de Datos
Ejemplo: En un DW existe la dimensión CLIENTE. En latabla correspondiente un registro representa la informaciónsobre el cliente “María García” cuyo estado civil cambia el15-01-1994 de soltera a casada. El estado civil del clientees utilizado con frecuencia en el análisis de la información.
18/12/201326
Almacenes de Datos - UCV
Otras orientaciones de diseño:dimensiones “que cambian”.
Existen tres estrategias para el tratamiento de los cambios en lasdimensiones:
Tipo 1: Realizar la modificación (Sobrescribir el Valor)
Tipo 2: Crear un nuevo registro (Agregar una fila a la Dimensión)
Tipo 3: Crear un nuevo atributo (Agregar una columna a la Dimensión)
Diseño de Almacenes de Datos
Existen tres estrategias para el tratamiento de los cambios en lasdimensiones:
Tipo 1: Realizar la modificación (Sobrescribir el Valor)
Tipo 2: Crear un nuevo registro (Agregar una fila a la Dimensión)
Tipo 3: Crear un nuevo atributo (Agregar una columna a la Dimensión)
18/12/201327
Almacenes de Datos - UCV
Otras orientaciones de diseño:definición de agregados.
¡En un almacén de datos es usualconsultar información agregada!
Diseño de Almacenes de Datos
El almacenamiento de datos agregados por distintoscriterios de agregación en la tabla de hechos mejora laeficiencia del DW.
18/12/201328
Almacenes de Datos - UCV
Datos Multidimensionales
Análisis de los Datos
No es común : ¿ Cuánto vendí?
Gerente de Ventas: ¿ Cuánto vendí del producto “A” en elperíodo “X” en la región “Y”?
Gerente Financiero: ¿ A cuánto ascendieron las ventas detodos los productos en todas las regiones al cierre del mes“M”?
Gerente Regional: ¿ Cuánto fueron las ventas de todos losproductos en el período J ó K en mi región?
Análisis de los Datos
No es común : ¿ Cuánto vendí?
Gerente de Ventas: ¿ Cuánto vendí del producto “A” en elperíodo “X” en la región “Y”?
Gerente Financiero: ¿ A cuánto ascendieron las ventas detodos los productos en todas las regiones al cierre del mes“M”?
Gerente Regional: ¿ Cuánto fueron las ventas de todos losproductos en el período J ó K en mi región?
18/12/201329
Almacenes de Datos - UCV
Ejemplos de Aplicaciones de Negocio
Preguntas típicas a ser respondidas son:
¿Qué productos son generalmente comprados juntos?
¿Que otros productos son comprados con las promociones?
¿Cuál es la afinidad entre las distintas forma de pago?
¿Cómo afectó la última campaña la venta de un determinadoproducto?
¿Cuáles fueron los clientes más sensibles a una determinadapublicidad?
¿Cuáles son los clientes con mayor probabilidad de irse a lacompetencia?
Preguntas típicas a ser respondidas son:
¿Qué productos son generalmente comprados juntos?
¿Que otros productos son comprados con las promociones?
¿Cuál es la afinidad entre las distintas forma de pago?
¿Cómo afectó la última campaña la venta de un determinadoproducto?
¿Cuáles fueron los clientes más sensibles a una determinadapublicidad?
¿Cuáles son los clientes con mayor probabilidad de irse a lacompetencia?
18/12/201330
Almacenes de Datos - UCV
Actividad:
Elaborar el Modelo Dimensional siguiendo los pasos de Kimball
S deben satisfacer los siguientes requerimientos:
1. Cantidad de presupuestos realizados por año,concesionario, marca, segmento.
2. Cantidad de unidades vendidas por provincia, concesionario,marca, modelo.
3. Cantidad de unidades vendidas por provincia, localidad, tipo devehículo.
4. Cantidad de unidades vendidas por año, concesionario y montototal facturado.
18/12/2013 Almacenes de Datos - UCV31
S deben satisfacer los siguientes requerimientos:
1. Cantidad de presupuestos realizados por año,concesionario, marca, segmento.
2. Cantidad de unidades vendidas por provincia, concesionario,marca, modelo.
3. Cantidad de unidades vendidas por provincia, localidad, tipo devehículo.
4. Cantidad de unidades vendidas por año, concesionario y montototal facturado.
Actividad: Elaborar el Modelo Dimensional
32Diagrama de base de datos (OLTP)Diagrama de base de datos (OLTP)
idConcesionario
descConcesionario
Concesionario
18/12/2013 Almacenes de Datos - UCV
DatawarehouseDatawarehouse(Almacenes de Datos)(Almacenes de Datos)
DatawarehouseDatawarehouse(Almacenes de Datos)(Almacenes de Datos)
Prof. Concettina Di Vasta