el diseño de la base de datos de un data warehouse
TRANSCRIPT
Marta [email protected]/materias
El diseño de la base de datos de un Data Warehouse
Marta [email protected]/materias
El modelo MultidimensionalPrincipios básicos
Introducción
� Un data warehouse se construye de manera heurística
� En un primer momento, no se conocen bien los requerimientos
� Proceso de construcción nuevos requerimietos
Motivaciones
� ¿Cómo diseñar una base de datos que permita ejecutar consultas desconocidas?
� Entender la forma usar información en el data warehouse
Motivaciones
� La mayor parte de las consultas a un data warehouse sobre un hecho esencial, analizado de distintas formas:� Número medio de ventas de micros por tienda el último
mes� Los diez programas por cable más vistos la semana
pasada� Ventas previstas de natilla en Navidad comparado con el
stock actual
E/R vs. Multidimensional
� Los modelos E/R intentan eliminar la redundancia en los datos
� La búsqueda de consistencia hace que los procesos de consulta sean difíciles y costosos.
� Modelos dimensionales: meta comprensión y el rendimiento
Modelos Multidimensionales
� Técnica de diseño lógico para diseño de Data Warehouses
� Proporcionan los mejores resultados � Diseños de acuerdo a un estándar que es
intuitivo y fácil de entender� Permite accesos de alto rendimiento
Modelos Multidimensionales
� Formado por una tabla cuya clave es compuesta -Table Fact (tabla de hechos) y un conjunto de tablas más pequeñas denominadas Tablas de Dimensión.
� Su apariencia hace que se les denomine diagramas STAR
Diagramas en estrella
TablaTabla dede hechoshechos
VentasVentas
ClienteCliente
DimensiDimensi óónn
AlmacenAlmacen
DimensiDimensi óónn
FechaFecha
DimensiDimensi óónn
ProductoProducto
DimensiDimensi óónn
Hechos
� Transacciones que han ocurrido. Poco probable que cambien en el futuro .
� Los hechos se pueden analizar de diferentes formas dependiendo de la información de referencia
� Los hechos suelen tener pocos atributos, puesto que no tiene datos operacionales
Dimensiones
� Representa factores por los que se puede analizar un determinado área de negocio.
� Son tablas más pequeñas� A menudo se desnormalizan
Hechos y dimensiones
VENTASVENTAS
Tiendas Región
Producto
Diseño STAR: Etapas
� Un diagrama E/R se divide en múltiples diagramas en estrella
� Separar en procesos discretos de negocio (hechos) y modelizar cada hecho separadamente
� Seleccionar relaciones n:m con atributos numéricos
� Desnormalizar las tablas de dimensión� El esquema final serán de 10- 25 STAR cada
uno de ellos con 5-10 dimensiones
Marta [email protected]/materias
Una metodología de trabajo parael diseño de la base de datos de
un Data Warehose
Método de diseño: Enfoque 1
� Diseñar la base de datos del Data Warehouse global:
• Mucho esfuerzo• Mucho tiempo• Resultados tardan mucho tiempo en aparecer• No es abordable en la mayoría de los casos
Método de Diseño Enfoque 2
• Diseñar la base de datos de cada Data Mart por separado:
• Mucho más sencillo• Menos tiempo• Menos recursos• La integración posterior de varios Data Mart se
complica
Método de Diseño
� SOLUCIÓN:� Diseñar la Base de Datos de cada Data Mart sin
perder de vista el objetivo final de integración.� Definir una estructura común (bus del data
warehouse) sobre la que se apoyen todos los data marts
Unión de los modelos STAR
� Construir todo el DW desde el principio o hacer diseños separados para cada area de negocio??
� Planifiquemos Data Marts separados pero que compartan aquellas definiciones comunes a la organización
� Data Marts con arquitectura de bus común
La arquitectura del BUS común del DW
� Crear una arquitectura que defina todo el marco de la organización.
� Construir Data Marts dentro de ese marco común
� Dimensiones conformadas� Definición estándar de hechos
Dimensiones conformadas (I)
� Es una dimensión que significa lo mismo para cada posible tabla fact
� Ejemplos: cliente, producto, tiempo, localización
� Es responsabilidad del equipo diseñador establecer, publicar y mantener las dimensiones conformadas
Dimensiones conformadas (II)
� Sin un uso estricto de dimensiones conformadas el datawarehouse nunca funcionará como un todo.
� Hacen posible:
� Una única tabla de dimensión se puede usar contra múltiples tablas de hechos en el mismo espacio de bases de datos
� Las interfaces de usuario y el contenido de los datos son consistentes dondequiera que se use
� Hay una interpretación consistente de atributos
Dimensiones conformadas (III)
� Puede llevar mucho tiempo la identificarlas
� Se diseñan al nivel de granilaridad más bajo que sea posible
� Deben tener una clave no significativa para poder permitir cambios en el futuro
� La creación de las mismas es tan decisión técnica como política
Definiciones de hechos conformadas
� Se realiza al mismo tiempo que las dimensiones conformadas
� Ejemplos: costos, precios, beneficio, ...� Mismas unidades de medida� Mismos periodos� Mismas localizaciones� Si es imposible conformar un hecho único
asegurarse de dar a las distintas interpretaciones diferentes nombres
Data Marts con múltiples fuentes de datos
� data mart con una única fuente de datos son los más fáciles de construir.� Ej: sistema de pedidos disponible y diseño del data mart de pedidos.
� Es recomendable empezar con estos data marts porque minimiza el riesgo de implementaciones demasiado ambiciosas.
� Una implementación de data marts sencillos permitirá al equipo de desarrollo seguir trabajando mientras los usuarios ya van realizando sus consultas.
Data Marts con múltiples fuentes de datos (II)
� Cuando se hayan implementado suficientes data marts de una única fuente es razonable combinarlos para conseguir un data mart de más de una fuente.� Ej: Conseguir el data mart de beneficios
combinado distintos componentes de costos y ganancias
La arquitectura común del DW
� Las dimensiones conformadas y las definiciones estándar de los hechos configuran la arquitectura del bus del data warehouse.
� La definición del bus en un determinado entorno permite añadir un nuevo data mart que puede coexistir con los ya existentes.
Marta [email protected]/materias
Aspectos de diseño a tener encuenta en el diseño de las tablas
de un Data Mart
4 pasos para diseñar tablas de hechos
� Elegir el data mart� Empezar con data marts de una fuente
� Decidir el grano de la tabla de hechos� Establece lo que significa cada registro de la tabla de
hechos
� Decidir las dimensiones
� Decidir los hechos de la tabla de hechos� Deben ser específicos para el grano elegido para la tabla
de hechos
Buscar las transacciones en el proceso del negocio
� Transacciones que describen sucesos fundamentales para el negocio
� Para cada hecho potencial, preguntar: ¿se usa de verdad esta transacción en el proceso de negocio?
Determinar dimensiones principales
� En modelo lógico que entidades están asociadas con la entidad que representa la tabla hecho
� No todas las dimensiones principales están asociadas directamente a la tabla hecho en el modelo lógico
� Preguntarse cuál va a ser el enfoque en el análisis del negocio
Comprobar si un hecho es una dimensión
Buscar dimensiones no normalizadas en las tablas candidatas a hechos. Se puede dar el caso de que un candidato a hecho sea una dimensión que contenga grupos repetidos de atributos hechos
Comprobar si un hecho es una dimensión
Diseñar tablas hecho para almacenar filas que no varíen en el tiempo. Si la entidad parece que varía en el tiempo, considerar el crear una tabla hecho que represente eventos que cambien el estado de esa entidad
Comprobar si una dimensión es un hecho
� ¿Puedo ver esta entidad en otra dimensión?. ¿En cuántas?
� Si la respuesta es más de tres, la entidad no es una dimensión, sino un hecho
Diseñar las tablas de hechos
� ¿Cómo puede ser una tabla de grande?� Existen una serie de técnicas para reducir
el tamaño de las tablas de hechos.
Reducir el tamaño de la tabla de hechos
1. Identificar el periodo histórico significativo para los distintos procesos, y el grado de detalle requerido.
2. Si los requisitos del negocio no necesitan todos los datos hecho detallados, se puede considerar almacenar algunos ejemplos y agregar el resto
3. Eliminar todas las columnas de la entidad hecho que no sean requeridas para responder a preguntas de toma de decisiones
Reducir el tamaño de la tabla de hechos (II)
4. Asegurarse de que cada byte de cada columna se necesita
5. No usar claves automáticas en las tablas hecho, a menos que se tenga la certeza de que los identificadores no cambian durante la vida del dw
6. Usar fechas en la tabla de hechos, y no construir una tabla especial para el tiempo
Diseñar las tablas dimensión
Desnormalizar las entidades consultadas muy a menudo para acelerar el rendimiento de las consultas (Esquemas estrella)
PRODUCTO
Nombre, color, estilo, tamaño
Sección
Departamento
Negocio
PRODUCTO
Sección, departamento,
negocio, nombre, color,
estilo, tamaño
Jerarquías
� A veces no es posible desnormalizar todas las entidades en esquemas en estrella. Entidades que se relacionan entre si con relaciones muchos-a-muchos no se deberían desnormalizar en un esquema en estrella
Jerarquías
Las jerarquías son muy usadas para representar diferentes vistas. En estos casos, es más efectivo determinar cuál es la jerarquía que va a ser más consultada. Ésta será desnormalizada, y el resto permanecerán como están
Esquemas starflake
� En el diseño de un data warehouse, se usa una combinación de esquemas normalizados (esquemas snowflake) y desnormalizados (esquemas estrella)
� La combinación de ambos recibe el nombre de esquemas starflake
VentasLocalización Tiempo
Localización
Localización
Tiempo
SemanaMes
Drill up, Drill down
� Drilling down:más detalle de los datos por ejemplo añadiendo una nueva restricción.
� No es sólo cuestión de navegar en las jerarquías
� Drill up es la operación contraria esto es eliminar cabeceras de los informes
Snowflaking
� Una dimensión está “snowflaked” cuando los atributos de baja cardinalidad se llevan a tablas separadas.
� Generalmente no se recomienda en DW� A veces se usa para ahorrar espacio de
almacenamiento� No permite hacer uso de los índices bitmap� Sin embargo existen situaciones (datos
demográficos) en las que son aconsejables.
Claves primarias y foráneas
� Todas las claves que se utilicen en tablas del data warehouse deben ser claves sin significado.
� Nunca se deben usar claves de producción� Nunca se deben usar claves con
significado� Facilitan los cambios
La importancia de los atributos
� La calidad del Data warehouse se mide por la calidad de los atributos:� Descriptivos� Completos (sin valores nulos)� Indexados� Palabras enteras� Documentados (metadata)� Calidad asegurada
Degenerate Dimension
� La mayoría de los diseños multidimensionales están alrededor de un documento de control: nº de pedido, factura, ticket, ...
� Generalmente son contenedores con más de un producto
� Generalmente en estos casos el grano de la tabla es producto
� ¿Qué se hace con los números?� Se ponen en las tablas pero no tienen una
dimensión con la que hacer join
Junk Dimension
� En ocasiones se tienen atributos textuales y flags de distinta naturaleza que no parecen organizarse de manera coherente.
� La solución no parece sencilla:� Dejar los atributos en la tabla fact� Hacer dimensiones separadas para cada atributo� Quitar directamente estos atributos
� La mejor solución es compactarlos todos en lo que se denomina una “junk dimension”
Aditividad
� Siempre que sea posible, los hechos de la tabla fact deberían elegirse para que sean perfectamente aditivos.
� Esto significa que se pueden sumar por cualquier dimensión.
� Las medidas de actividad (ventas) son generalmente aditivas
Aditividad
� Las medidas de intensidad no siempre lo son (niveles de inventario, balance de cuentas...). Estas medidas generalmente son aditivas por todas las dimensiones menos por el tiempo.
� Existen otras medidas que no se pueden sumar por ninguna dimensión (temperaturas).
Familias de tablas de hechos (I)
Hay negocios con un flujo lógico con un principio y un fin (value chain):� Ciclo de vida de un producto� Ciclo de vida de un cliente� Seguros
Familias de tablas de hechos (I)
� Todos estos casos se pueden enfocar de la misma manera:� Se define una tabla fact y un conjunto de
dimensiones asociadas para cada paso de la cadena
� Se deciden las dimensiones conformadas y se utilizan para cada paso de la cadena
Familias de tablas de hechos (II)
Otros negocios se organizan como círculos. En estos casos todas las entidades miden la misma transacción:� Organizaciones de salud
Ejemplo
Tiempo
Cuenta
Sucursal
Titular
Balance
Cuotas pagadas
Num_transacciones
Clave para unir con fact personalizada
Tiempo
Sucursal
Clave Cuenta
Clave por tipo de cuenta
Clave por tipo de cuenta
Atributos por tipo
Clave de fact1
Hechos particulares
Clave de factn
Hechos particulares
Agregados
� Las tablas de agregados son resúmenes que se construyen y almacenan para mejorar el rendimiento de las consultas
� Se genera una familia de tablas que se derivan de la tabla de hechos a nivel detallado
� Cada miembro de la familia representa un grado determinado de resumen en relación a una o más dimensiones
Tablas fact sin hechos
diseño final una tabla fact sin hechos, solo interesa el suceso en sí:� Afluencia de público� Coberturas
Tablas fact sin hechos (II)
Fecha
Persona
Establecimiento
Fecha
Producto
Establecimiento
Promoción
Fecha
Persona
Establecimiento
Producto
Promoción
Relaciones n:m
� relaciones n:m desde la “table fact” a las tablas de dimensión (titulares de cuentas bancarias,....)
� El problema se soluciona añadiendo una tabla puente
Roles
Dimensiones comunes requiere la creación de vistas sobre las tablas de dimensión
Fin: identificar cada uno de los posibles roles de la dimensiónCiudad:
– Ciudad de destino– Ciudad de origen
Roles
Fecha:– Fecha de entrega– Fecha de venta– Fecha de devolución
Operador– Larga distancia– Metropolitana o Local
Agregaciones
Aceleran las consultas más comunes
costo de crear y de gestionar las agregaciones vs. beneficios
¿Qué es una agregación?
La mayoría de las consultas sobre un subconjunto o una agregación de los datos detallados
Ejemplo: se desea conocer las ventas de un determinado producto en un dpto. En Colombia: uso de tablas resumen
Diseñando tablas resumen
Proceso similar al de las tablas de hechos� Determinar las dimensiones por las cuales agregar� Determinar la agregación de múltiples valores� Agregar varios hechos en la tabla resumen� Determinar el nivel de agregación� Diseñar el tiempo en la tabla resumen� Hacer un índice de la tabla resumen
Determinar las dimensiones por las que agregar
� Dos técnicas:� Incorporar la dimensión : consultar con frecuencia datos
agregados, se crea una tabla con los datos resumidos y se prescinde de la dimensión
� Agregar la dimensión : consultas se hacen sobre datos agregados, con lo que se pueden crear tablas resumen en los que se prescinde de la dimensión agregada
Determinar la agregación de múltiples valores
Si existen muchas consultas que usan más de un valor agregado sobre la misma dimensión, se puede agregar los valores requeridos en un conjunto de columnas dentro de las misma tabla resumen
Determinar el nivel de agregación
� Cuando se hacen agregaciones de un nivel, se pierden datos sobre el nivel inferior
� Se puede considerar agregar los datos a un nivel inferior al deseado para no perder datos
Ejemplo : Si se quieren datos mensuales, considerar agregar datos a nivel de semana. De este modo, no se pierde este detalle, y siempre se pueden tener los datos mensuales
Diseñar el tiempo en la tabla resumen
Para acelerar las consultas, se puede almacenar el tiempo de varias formas:� Como una fecha física: lo más
recomendable� Como un desplazamiento: no es
apropiado. Se tarda mucho en calcular el desplazamiento
� Como un rango: nada recomendado
Hacer un índice de la tabla resumen
Maximizar la velocidad de las consultas sobre las tablas resumen indexando todos los posibles accesos, es decir, considerar usar un nivel de indexación muy alto sobre las tablas resumen