Las Bodegas de Datos(Data warehouses)

Francisco Moreno

Universidad Nacional

Medellín

Problemas evidenciados en bases de datos (BD) operativas con respecto a informes para la toma de decisiones:

- Su generación es laboriosa (codificación)

- Tiempos de respuesta inaceptables (días, semanas) para los usuarios Dificultades para analizar gran cantidad de datos

Introducción

Una bodega de datos (data warehouse DW) permite:

- Dar respuestas instantáneas a consultas para la toma de decisiones- Identificar estrategias efectivas en determinadas áreas del negocio (Ej: Impacto de una política de promoción

de un producto) - Analizar al instante grandes volúmenes de datos

Introducción

Sistemas de Soporte de Decisiones (DSS)

Un DW hace parte de un DSS.

Componentes de un DSS:

- DW: BD especializada para el apoyo a la toma de decisiones

- Extractor y depurador (filtro) de datos desde las BD operativas

- Herramienta de construcción de informes

- Herramienta de presentación (gráficos)

BD operativas

Extracción y Filtradode datos

Bodega deDatos

Construcción de informes

PresentaciónAnálisis y toma de decisiones

Un DSS permite por ejemplo:

- Comparar tasas de crecimiento durante un periodo

- Establecer la relación entre dos variables. Ej: Tipos de publicidad vs. Ventas

- Pronosticar

Un DSS ayuda en:

• El conocimiento del cliente y sus tendencias

• La predicción del comportamiento en el mercado de nuevos productos

• La detección de clientes de alto riesgo y minimizar el riesgo de perderlos

• Altamente normalizados• Registran operaciones diarias• Los datos de un “objeto” están

esparcidos en varias tablas Ej: una venta

• Muchas tablas, cada una con pocos atributos

• Actualizaciones en tiempo real

• Datos resúmenes (consolidados) de los datos operativos

• Registran operaciones durante un período específico

• Datos de naturaleza histórica• Suelen existir jerarquías de

clasificación, niveles de agregación. Ej: Tienda Ciudad Región

• Tiende a haber desnormalización• Pocas tablas, cada una con

muchos atributos• Orientados esencialmente a

consultas• Gran tamaño• Actualizaciones en lote

Datos Operativos Datos DSS

Requisitos de un SGBD (Sistema de Gestión de BD) para soportar un DSS:- Manejo eficiente de consultas con datos desnormalizados*- Herramientas avanzadas para extraer, filtrar e integrar datos desde las BD operativas- Herramientas de análisis de datos para el usuario final

* Un aspecto que se está evidenciando hoy en las bases de datos no-SQL, no relacionales.

El DW:

- Es un depósito de datos para un DSS

- Orientado a sujetos. Ej: Ventas por productoproducto, por vendedorvendedor, por regiónregión, etc.

Sujetos (dimensiones)

Tema(hecho)

Estructuradas en jerarquías (ver luego)

Extracción de

Datos

ExtraerFiltrarTrasformarIntegrarClasificarAgregarResumir

Bodega dedatos

BD operativas

Creación del DW:

- Compleja: como los datos

usualmente provienen

de diferentes fuentes

presentan diferentes formatos

- Requiere tiempo*, dinero y

esfuerzo considerables

* 1 a 3 años, quizás más…Lessons from a Successful

DataWarehouse Implementation

Porter & Rome

Debido a lo anterior se crean a veces más bien “mercados de datos” (data marts):

- Menor alcance y tamaño- Apoyan solo un sector de la empresa Ej: para un departamento específico- Se adaptan a las particularidades de los gerentes de cada sector - Son un “vehículo de prueba” (para todos los usuarios) que permite observar el beneficio de un DW

10 aspectos clave en un DW

1. El DW debería estar separado de las fuentes operativas

2. El DW suele integrar datos de diversas fuentes

3. El DW contiene datos históricos que abarcan un amplio horizonte de tiempo

4. Los datos en el DW son capturados en un punto dado del tiempo

5. Los datos en el DW están orientados a sujetos

6. Los datos motivan el desarrollo del DW,

los procesos motivan el método clásico

7. Los datos en el DW tienden a ser de solo lectura, pero hay actualizaciones periódicas (por lotes)

Ejemplo: Clientes, Productos, Vendedores etc.

Ejemplo: Tarifación, Facturación, Registro de Ventas

Vs

8. El DW puede contener datos con varios niveles de detalle: datos ligeramente resumidos y datos altamente resumidos

9. El ambiente (DSS) del DW dispone de un sistema que rastrea las fuentes, extrae y trasforma datos

10. Hay poca actualización de datos, es decir, el ambiente de los datos es relativamente estático

• Es posible:

A partir de un DW derivar diferentes data marts

A partir de un conjunto de data marts generar el DW Usualmente es más complejo

• Los DSS incluyen técnicas para el análisis de datos multidimensionales (DM)multidimensionales (DM): OLAP

• OLAP incluye:– Gráficos de presentación avanzados– Funciones avanzadas de consulta de

agregados (navegación por las dimensiones Ej: semana mes trimestre año)

– Funciones de pronóstico ¿qué sucederá si?

Análisis What if

Vista Operativa de las ventas

Tabla Factura

Número Fecha Cliente

111 Ago 3/04 7172

129 Ago 3/04 7172

135 Ago 3/04 7865

… … …

218 Oct 1/04 7172

222 Oct 2/04 7172

Vista Operativa de las ventas

Producto Nombre Precio_unidad

p1 Mesa 500

p3 Silla 300

p2 Loro 200

Tabla Producto

Vista Operativa de las ventas

Tabla Detalle_Factura

Num_fac Producto Cantidad

111 p1 2

111 p3 3

129 p1 1

135 p1 2

218 p2 1

222 p2 1

Vista Multidimensional de las ventas Versión 1

Dimensión Tiempo (a nivel día)

… Ago

3/04

… Oct

1/04

Oct

2/04

… Totales

7172 … 2400 … 200 200 … 2800

7865 … 1000 … 0 0 … 1000

… … … … … … … …

Totales 3400 200 200 3800

Se proporcionan agregados para ambas dimensiones Gran Total

Dimensión Cliente

Vista Multidimensional de las ventas Versión 2

Dimensión Tiempo (a nivel mes)

… Ago

04

Sep

04

Oct

04

… Totales

7172 … 2400 … 400 … 2800

7865 … 1000 … 0 … 1000

… … … … … … …

Totales 3400 400 3800

Se proporcionan agregados para ambas dimensiones Gran Total

Dimensión Cliente

• El DW contiene los datos de soporte para la toma de decisiones

• OLAP permite acceder y analizar tales datos (aunque es posible por medio de OLAP acceder directamente a una BD operativa)

• Los DM se pueden guardar en BD relacionales o en BD especializadas para DM

BD operativas

Bodega de datos

Lógica de Procesamiento

Análitico

Motor OLAP

Sistema OLAP

ROLAP: Extensión a los SGBD relacionales

para soportar OLAP. Ofrece:

- Soporte para DM (esquema en estrella, ver luego)

- Lenguaje de acceso y desempeño de consultas optimizadas para DM

- Soporte para grandes BD

- Estructuras de almacenamiento especiales (índices, dimensiones, etc.)

MOLAP: Usa técnicas especiales para

guardar los datos en matrices de n dimensiones.

Conceptualmente se ven como cubos (tres

dimensiones) o hipercubos (n dimensiones)

Raleza del cubo: Medida que indica que tan poblado

está un cubo. Ej: Podría haber muchos productos

que no se vendieron en un período en una región

dada

• Los SGBD que soportan MOLAP se denominan SGBD multidimensionales

• Usan técnicas especiales para almacenar los cubos poco poblados (cubos con muchas celdas vacías)

• Nótese que en ROLAP una combinación que no existe (por ej. un pdto, período y región específicos) simplemente no se almacena

• Algunos productos combinan los enfoques ROLAP y MOLAP (OLAP Híbrido: HOLAP)

• C.J. Date: “Hay muchas controversias sobre cual de estos tres enfoques es mejor”

ROLAP

Esquema en estrella:

Técnica de modelado de datos usada

para soportar DM en BD relacionales.

Esquema en estrella

Componentes:

1. Hechos

Es un tema de análisis de interés para la

organización. Ej: Ventas, Envíos, Goles,

Crímenes, etc.

Los hechos conforman la tabla de hechos

Centro del esquema en estrella, rodeada

por las dimensiones

• Un hecho tiene medidas, i.e., indicadores que los analistas evalúan y generan informes.

• Por ejemplo, el número de unidades vendidas y el valor de una venta son medidas típicas de una venta.

2. Dimensiones

Características calificadoras asociadas con

un hecho.

Ej: Posibles dimensiones de una venta:

Producto, Ubicación geográfica, Tiempo,

Cliente y Vendedor.

Éxito Robledo

12

Jul.

05

Televisor LG32

Hecho de Ventas12.000.000

10

Dimensión Tiempo

Dimensión Producto

Dimensión Ubicación

Considerarla granularidaddel tiempo a usar…

Valor de ventasNúmero de unidades vendidasMedida: Medida:

3. Niveles

Componentes de las dimensiones.

Organizados jerárquicamente.

Ej:

• Dimensión Ubicación:

Tienda Ciudad Estado Región

• Dimensión Tiempo:

Día Mes Trimestre Año

Semana

• Los niveles pueden tener atributos, por ejemplo, Store_id, Sto_name y Sto_description pueden ser atributos del nivel Tienda.

• Las dimensiones son “la lupa” a través de la cual se estudian los hechos.

• Ej: Un hecho de venta es efectuado en una tienda, “al aumentar” se llega a la ciudad en la que está ubicada esa tienda, al aumentar de nuevo se llega al estado de esa ciudad y así sucesivamente

• El modelo de DM se puede representar conceptualmente mediante un cubo (hipercubo).

• Es posible consultar secciones del cubo “rebanadas” y otros tipos de consultas

(mediante OLAP)

• En MOLAP estos cubos se almacenan en matrices especiales.

PRODUCTO

T I E M P O

UB

IC

AC

IÓ

N

Los hechos de ventas se guardan en las celdasde la intersección de cada dimensión ProductoProducto, TiempoTiempo y UbicaciónUbicación

¿Qué representa esta “tajada”?

¿Qué representa esta “tajada”?¿Qué representa

esta “tajada”?

Tajada Azul: Producto x Ubicación en un tiempo específico

#

UBICACIÓN

# #

# # #

# # #

PRODUCTO

Tajada Naranja: Producto x Tiempoen un lugar específico

#

TIEMPO

# #

# # #

# # #

PRODUCTO

Tajada Rosa: Tiempo x Ubicación con un producto específico

#

UBICACIÓN

# #

# # #

# # #

T IEMPO

Operaciones sobre el cubo

• Roll-up: transforma medidas detalladas en medidas agregadas a un mayor nivel. Por ejemplo, pasar medidas que están a nivel de ciudad a nivel de país o medidas que están a nivel de día a nivel de mes.

• Nota: los nombres y definiciones de algunas de estas operaciones pueden variar en la literatura.

Roll-up

Nota: Aquí la dimensión Ubicación se llama igual que su nivel inferior Store.

Operaciones sobre el cubo

• Drill-down: Ejecuta la operación contraria a roll-up, i.e., se mueve de un nivel general a uno más detallado, ofreciendo de esta forma una visión más detallada de los datos. Por ejemplo, pasar medidas que están a nivel de trimestre a nivel de mes o medidas que están a nivel de país a nivel de ciudad

Drill-down

Time (Month)

Operaciones sobre el cubo: Pivot

• Pivot: rota los ejes del cubo para ofrecer una presentación alternativa de los datos.

Operaciones sobre el cubo: Slice

• Slice: realiza una selección sobre una dimensión. Por ejemplo, mostrar solo los datos de las tiendas de Paris.

Operaciones sobre el cubo: dice

• Dice: realiza una selección en dos o más dimensiones, generando un subcubo.

• Otras operaciones: drill-across (op. entre cubos), drill-through (op. entre un cubo y los sistemas operacionales)

• A partir del cubo se puede calcular por ejemplo:– Total de ventas– Total de ventas de un producto dado– Navegar por las dimensiones: Total de ventas por

región*.

* Suponiendo la Dimensión Ubicación: Tienda Ciudad Estado Región

PRODUCTO

T I E M P O

UB

IC

AC

IÓ

N

Total de Ventas decada producto en cada ubicación (sin importar el tiempo)

PRODUCTO

U B I C A C I Ó N

Total de Ventas decada producto sin importar la ubicación ni el tiempo

Total de Ventas

PRODUCTO

• Nótese que el total de ventas de cada producto (sin importar la ubicación ni el tiempo) se puede calcular directamente desde el cubo, aunque es costoso, o a partir del total de ventas de cada producto en cada ubicación (sin importar el tiempo), más eficiente

• Igual sucede con el gran total de ventas…

• Por lo anterior, se suelen almacenar algunos de estos datos agregados junto con el cubo

• El problema es el espacio y la coordinación de los datos del cubo con estos datos precalculados

Representación del Esquema en Estrella

UBICACIÓNStore_idSto_nameSto_descriptionRegión_idState_idCity_id

CLIENTECust_idCust_lnameCust_fnameCust_initialCust_dob

TIEMPOTime_idTime_yearTime_quarterTime_monthTime_day

PRODUCTOProd_idProd_descriptionProd_type_idProd_brandProd_colorProd_sizeProd_packageProd_price

VENTAS Time_id Store_id Cust_id Prod_id Sales_qty Sales_price Sales_total

1

1

1

1

M

M

M

M

25 registros

125 registros

365 registros

3000 registros

3.000.000* registros

Agregados deVentas diarias por

tienda, cliente y producto

Medidas

* No necesariamente todas las combinaciones de las cuatro dimensiones

• La clave primaria de la tabla de hechos* está formada por la clave foránea hacia cada una de las tablas de dimensiones

• Usualmente hay múltiples esquemas en estrella en el sistema y pueden compartir dimensiones entre ellas

• Las dimensiones se pueden normalizar dando lugar al esquema de copo de nieve o de constelación:

* En una implementación relacional

Dimensión UBICACIÓN normalizada

REGIÓNRegión_idRegión_name

CIUDADCity_idCity_nameState_id VENTAS

Time_idStore_idCust_idProd_idSales_qtySales_priceSales_total

1

M

ESTADOState_idState_nameRegión_id

TIENDAStore_idSto_nameSto_descriptionCity_id

M

M

1

1

M

1

Dimensión TIEMPO

Dimensión CLIENTE

Dimensión PRODUCTO

Dimensión UBICACIÓN

Notación de Malinowski

a) Nivel, b) Jerarquía entre niveles, c) Cardinalidades y d) Hecho

Notación de Malinowski

UBICACIÓN

CLIENTE

TIEMPO

PRODUCTO

VENTAS

Sales_qtySales_priceSales_total

También se pueden mostrar los atributos y los niveles de cada dimensión:

Notación de Malinowski

VENTAS

Sales_qtySales_priceSales_total

REGIÓNRegión_idRegión_name

CIUDADCity_idCity_name

ESTADOState_idState_name

TIENDAStore_idSto:nameSto_description Dimensión

TIEMPO

Dimensión PRODUC

TO

Dimensión CLIENTE

Dimensión UBICACIÓN

Sobre el DW:• Ralph Kimball: “Resístase a la normalización…

los esfuerzos para ahorrar espacio son una pérdida de tiempo…las tablas de dimensión normalizadas destruyen la habilidad para navegar”

• C. J. Date: “Debe quedar claro que tal ‘disciplina’ conducirá con seguridad a una redundancia sin control y probablemente incontrolable”

• Es posible también crear tablas de hechos a diferentes niveles de agregación para facilitar los cálculos y mejorar el tiempo de respuesta de las consultas:

Tablas de hechos con diferentes niveles de granularidad

VENT_REGIÓNTime_idRegión_idCust_idProd_idSales_reg_qtySales_reg_priceSales_reg_total

VENT_ESTADOTime_idState_idCust_idProd_idSales_sta_qtySales_sta_priceSales_sta_total

VENT_CIUDADTime_idCity_idCust_idProd_idSales_city_qtySales_city_priceSales_city_total

VENT_TIENDATime_idStore_idCust_idProd_idSales_sto_qtySales_sto_priceSales_sto_total

REGIÓNRegión_id

…

CIUDADCity_id…

ESTADOState_id…

TIENDAStore_id…

Nota: Aquí no se muestran las otras dimensiones que “rodean” a las tablas de ventas

• En el diseño del DW se deben considerar entre muchas cosas:– Desnormalización– Manejo de agregados ¿cuáles se van a

necesitar?– Manejo del tiempo (nivel de detalle: día,

mes, semana, etc.) Igualmente se debe considerar el nivel de detalle (granularidad) para cada dimensión.

Minería de DatosPermite:

• Agrupar, clasificar o identificar secuencias de datos

• Descubrir relaciones ocultas entre los datos

• Descubrir patrones, tendencias, desviaciones

• Pronosticar Ej: Existe un 90% de probabilidad de que el 82% de los clientes que compran un TV de 32 pulgadas nuevo compren un “teatro en casa” en las cuatro semanas siguientes

• Una compañía telefónica encontró que aproximadamente 10.000 clientes supuestamente residenciales gastaban más de 1000 US$ en facturas telefónicas. Después de un estudio adicional se detectó que eran pequeños negocios que trataban de evitar pagar las tarifas comerciales.

• Algunas técnicas de minería se detallarán más adelante en el curso.

Minería de Datos