dimensional data model data warehouse tutorial with examples
Este tutorial explica los beneficios y los mitos del modelo de datos dimensionales en el almacén de datos. Además, aprenda sobre tablas de dimensiones y tablas de hechos con ejemplos:
Pruebas de almacenamiento de datos se explicó en nuestro tutorial anterior, en este Serie de formación sobre almacenamiento de datos para todos .
Los datos enormes se organizan en el almacén de datos (DW) con técnicas de modelado de datos dimensionales. Estas técnicas de modelado de datos dimensionales facilitan el trabajo de los usuarios finales para realizar consultas sobre los datos comerciales. Este tutorial explica todo sobre los modelos de datos dimensionales en DW.
Público objetivo
- Desarrolladores y probadores de almacenamiento de datos / ETL.
- Profesionales de bases de datos con conocimientos básicos de conceptos de bases de datos.
- Administradores de bases de datos / expertos en big data que desean comprender los conceptos de almacenamiento de datos / ETL.
- Graduados universitarios / Freshers que buscan trabajos de almacén de datos.
Lo que vas a aprender:
Modelos de datos dimensionales
Los modelos de datos dimensionales son las estructuras de datos que están disponibles para los usuarios finales en el flujo ETL, para consultar y analizar los datos. El proceso ETL termina cargando datos en los modelos de datos dimensionales de destino. Cada modelo de datos dimensional se construye con una tabla de hechos rodeada de varias tablas de dimensiones.
Pasos a seguir al diseñar un modelo de datos dimensional:
Beneficios del modelado de datos dimensionales
A continuación se enumeran los diversos beneficios del modelado de datos dimensionales.
- Están asegurados para utilizar los entornos DW en constante cambio.
- Se pueden construir fácilmente datos enormes con la ayuda de modelos de datos dimensionales.
- Los datos de los modelos de datos dimensionales son fáciles de comprender y analizar.
- Los usuarios finales pueden acceder rápidamente a ellos para realizar consultas con alto rendimiento.
- Los modelos de datos dimensionales nos permiten desglosar (o) agrupar los datos jerárquicamente.
Modelado ER versus modelado de datos dimensionales
- El modelado ER es adecuado para sistemas operativos, mientras que el modelado dimensional es adecuado para el almacén de datos.
- El modelado ER mantiene datos transaccionales actuales detallados, mientras que el modelado dimensional mantiene el resumen de datos transaccionales actuales e históricos.
- El modelado ER tiene datos normalizados, mientras que el modelado dimensional tiene datos desnormalizados.
- El modelado ER usa más uniones durante la recuperación de consultas, mientras que el modelado dimensional usa un número menor de uniones, por lo que el rendimiento de la consulta es más rápido en el modelado dimensional.
Mitos del modelado de datos dimensionales
A continuación se muestran algunos de los mitos existentes en el modelado de datos dimensionales.
- Los modelos de datos dimensionales se utilizan solo para representar el resumen de los datos.
- Son específicos del departamento de una organización.
- No admiten la escalabilidad.
- Están diseñados para servir el propósito de informes y consultas del usuario final.
- No podemos integrar los modelos de datos dimensionales.
Tablas de dimensiones
Las tablas de dimensiones juegan un papel clave en el sistema DW al almacenar todos los valores métricos analizados. Estos valores se almacenan en atributos dimensionales (columnas) fácilmente seleccionables en la tabla. La calidad de un sistema DW depende principalmente de la profundidad de los atributos de dimensión.
Por lo tanto, deberíamos intentar proporcionar muchos atributos junto con sus respectivos valores en las tablas de dimensiones.
¡Exploremos la estructura de las tablas de dimensiones!
# 1) Clave de la tabla de dimensiones: Cada tabla de dimensión tendrá cualquiera de sus atributos de dimensión como clave principal para identificar de forma única cada fila. Por tanto, los distintos valores numéricos de ese atributo pueden actuar como claves primarias.
Si los valores de atributo no son únicos en ningún caso, puede considerar los números de sistema generados secuencialmente como claves primarias. También se denominan claves sustitutas.
Los modelos de datos dimensionales deben tener la restricción de integridad referencial para cada clave entre dimensiones y hechos. Por lo tanto, las tablas de hechos tendrán una referencia de clave externa para cada clave principal / sustituta en la tabla de dimensiones para mantener la integridad referencial.
Si falla, los datos de la tabla de hechos respectivos no se pueden recuperar para esa clave de dimensión.
# 2) La mesa es ancha: Podemos decir que las tablas de dimensiones son amplias, ya que podemos agregar cualquier número de atributos a una tabla de dimensiones en cualquier punto del ciclo DW. El arquitecto DW solicitará al equipo de ETL que agregue los nuevos atributos respectivos al esquema.
En escenarios en tiempo real, puede ver tablas de dimensiones con 50 (o) atributos más.
# 3) Atributos textuales: Los atributos dimensionales pueden ser de cualquier tipo, preferiblemente de texto (o) numéricos. Los atributos textuales tendrán palabras comerciales reales en lugar de códigos. Las tablas de dimensiones no están pensadas para cálculos, por lo que los valores numéricos rara vez se utilizan para los atributos dimensionales.
# 4) Los atributos pueden no estar directamente relacionados: Es posible que no todos los atributos de una tabla de dimensiones estén relacionados entre sí.
# 5) No normalizado: La normalización de una tabla de dimensiones trae más tablas intermedias a la imagen, lo que no es eficiente. Por tanto, las tablas de dimensiones no están normalizadas.
Los atributos dimensionales pueden actuar como fuente de restricciones en las consultas y también pueden mostrarse como etiquetas en los informes. Las consultas funcionarán de manera eficiente si selecciona directamente un atributo de la tabla de dimensiones y hace referencia directamente a la tabla de hechos respectiva sin tocar ninguna otra tabla intermedia.
# 6) Profundizar y enrollar: Los atributos de dimensión tienen la capacidad de desglosar (o) resumir los datos cuando sea necesario.
# 7) Jerarquías múltiples: Una tabla de una sola dimensión con varias jerarquías es muy común. Una tabla de dimensiones tendrá una jerarquía simple si solo existe una ruta desde el nivel inferior al superior. De manera similar, tendrá múltiples jerarquías si hay múltiples rutas presentes para llegar desde el nivel inferior al superior.
# 8) Pocos registros: Las tablas de dimensiones tendrán menos registros (en cientos) que las tablas de hechos (en millones). Aunque son más pequeños que los hechos, proporcionan todas las entradas a las tablas de hechos.
A continuación, se muestra un ejemplo de una tabla de dimensiones de clientes:
Al comprender los conceptos anteriores, puede decidir si un campo de datos puede actuar como un atributo de dimensión (o) mientras extrae los datos de la fuente misma.
El plan de carga básico para una dimensión
Las dimensiones se pueden crear de dos maneras, es decir, extrayendo los datos de las dimensiones de sistemas de fuente externos (o) El sistema ETL puede construir las dimensiones a partir de la puesta en escena sin involucrar ninguna fuente externa. Sin embargo, un sistema ETL sin ningún procesamiento externo es más adecuado para crear tablas de dimensiones.
A continuación se muestran los pasos involucrados en este proceso:
el mejor software de máquina virtual para windows
- Limpieza de datos: Los datos se limpian, validan y las reglas comerciales se aplican antes de cargarlos en la tabla de dimensiones para mantener la coherencia.
- Conformidad de datos: Los datos de otras partes del almacén de datos deben agregarse correctamente como un solo valor, con respecto a cada campo de la tabla de dimensiones.
- Comparta los mismos dominios: Una vez que se confirman los datos, se almacenan nuevamente en tablas de etapas.
- Entrega de datos: Finalmente, todos los valores de los atributos dimensionales se cargan con claves primarias / sustitutas asignadas.
Tipos de dimensiones
Los diversos tipos de Dimensiones se enumeran a continuación para su referencia.
¡¡Empecemos!!
# 1) Dimensiones pequeñas
Las pequeñas dimensiones en el almacén de datos actúan como tablas de búsqueda con menos filas y columnas. Los datos en pequeñas dimensiones se pueden cargar fácilmente desde hojas de cálculo. Si es necesario, las pequeñas dimensiones se pueden combinar como una superdimensión.
# 2) Dimensión conformada
Una dimensión conformada es una dimensión a la que se puede hacer referencia de la misma manera con cada tabla de hechos con la que está relacionada.
La dimensión de fecha es el mejor ejemplo de una dimensión conformada, ya que los atributos de la dimensión de fecha, como año, mes, semana, días, etc., comunican los mismos datos de la misma manera en cualquier número de hechos.
Un ejemplo de dimensión conformada.
# 3) Dimensión basura
Pocos atributos en una tabla de hechos, como banderas e indicadores, se pueden mover a una tabla de dimensión basura separada. Estos atributos tampoco pertenecen a ninguna otra tabla de dimensiones existente. En general, los valores de estos atributos son simplemente un 'sí / no' (o) 'verdadero / falso'.
La creación de una nueva dimensión para cada atributo de marca individual lo hace complejo al crear más claves externas para la tabla de hechos. Al mismo tiempo, mantener todas estas banderas e información de indicadores en tablas de hechos también aumenta la cantidad de datos almacenados en hechos, lo que degrada el rendimiento.
Por lo tanto, la mejor solución para esto es crear una única dimensión basura, ya que una dimensión basura es capaz de contener cualquier número de indicadores 'sí / no' o 'verdadero / falso'. Sin embargo, las dimensiones basura almacenan valores descriptivos para estos indicadores (sí / no (o) verdadero / falso) como activo y pendiente, etc.
Según la complejidad de una tabla de hechos y sus indicadores, una tabla de hechos puede tener una o más dimensiones basura.
Un ejemplo de Junk Dimension.
# 4) Dimensión de juego de roles
Una sola dimensión a la que se puede hacer referencia para múltiples propósitos en una tabla de hechos se conoce como dimensión de juego de roles.
El mejor ejemplo para una dimensión de juego de roles es nuevamente una tabla de dimensión de fecha, ya que el mismo atributo de fecha en una dimensión se puede usar para diferentes propósitos en un hecho como la fecha de pedido, fecha de entrega, fecha de transacción, fecha de cancelación, etc.
Si es necesario, puede crear cuatro vistas diferentes en la tabla de dimensiones de fecha con respecto a cuatro atributos de fecha diferentes de una tabla de hechos.
Un ejemplo de una dimensión de juego de roles.
# 5) Dimensiones degeneradas
Puede haber pocos atributos que no puedan ser dimensiones (métricas) ni hechos (medidas) pero necesitan análisis. Todos esos atributos pueden trasladarse a dimensiones degeneradas.
Por ejemplo, puede considerar el número de pedido, el número de factura, etc. como atributos de dimensión degenerados.
Un ejemplo de dimensión degenerada.
# 6) Dimensiones que cambian lentamente
Una dimensión que cambia lentamente es un tipo en el que los datos pueden cambiar lentamente en cualquier momento en lugar de en intervalos regulares periódicos. Los datos modificados en tablas de dimensiones se pueden manejar de diferentes formas, como se explica a continuación.
Puede seleccionar el tipo de SCD para responder a un cambio individualmente para cada atributo en una tabla dimensional.
(i) Tipo 1 SCD
- En el tipo 1, cuando hay un cambio en los valores de los atributos dimensionales, los valores existentes se sobrescriben con los valores recién modificados, lo que no es más que una actualización.
- Los datos antiguos no se mantienen como referencia histórica.
- Los informes anteriores no se pueden volver a generar debido a la inexistencia de datos antiguos.
- Facil de mantener.
- El impacto en las tablas de hechos es mayor.
Ejemplo de SCD tipo 1:
(Ii) Tipo 2 SCD
- En el tipo 2, cuando hay un cambio en los valores de los atributos dimensionales, se insertará una nueva fila con los valores modificados sin cambiar los datos de la fila anterior.
- Si existe alguna referencia de clave externa al registro anterior en cualquiera de las tablas de hechos, entonces la clave sustituta anterior se actualiza en todas partes con una nueva clave sustituta automáticamente.
- El impacto en los cambios de la tabla de hechos es muy menor con el paso anterior.
- Los datos antiguos no se consideran en ningún lugar después de los cambios.
- En el tipo 2, podemos rastrear todos los cambios que están ocurriendo en los atributos dimensionales.
- No hay límite para el almacenamiento de datos históricos.
- En el tipo 2, agregar algunos atributos a cada fila, como la fecha de cambio, la fecha y hora de vigencia, la fecha de finalización, el motivo del cambio y la bandera actual, es opcional. Pero esto es importante si la empresa desea conocer la cantidad de cambios realizados durante un período de tiempo determinado.
Ejemplo de SCD tipo 2:
(Iii) Tipo 3 SCD
- En el tipo 3, cuando hay un cambio en los valores de los atributos dimensionales, los nuevos valores se actualizan pero los valores antiguos siguen siendo válidos como segunda opción.
- En lugar de agregar una nueva fila para cada cambio, se agregará una nueva columna si no existe previamente.
- Los valores antiguos se colocan en los atributos añadidos anteriormente y los datos del atributo principal se sobrescriben con el valor modificado como en el tipo 1.
- Existe un límite en el almacenamiento de datos históricos.
- El impacto en las tablas de hechos es mayor.
Ejemplo de SCD tipo 3:
(iv) Tipo 4 SCD
- En el tipo 4, los datos actuales se almacenan en una tabla.
- Todos los datos históricos se mantienen en otra tabla.
Ejemplo de SCD tipo 4:
(v) Tipo 6 SCD
- Una tabla dimensional también puede tener una combinación de los tres tipos de SCD 1, 2 y 3, lo que se conoce como una dimensión de cambio lento de tipo 6 (o) híbrida.
Tablas de hechos
Las tablas de hechos almacenan un conjunto de valores medidos cuantitativamente que se utilizan para los cálculos. Los valores de la tabla de hechos se muestran en los informes comerciales. A diferencia del tipo de datos textuales de las tablas de dimensiones, el tipo de datos de las tablas de hechos es significativamente numérico.
Las tablas de hechos son profundas, mientras que las tablas de dimensiones son anchas, ya que las tablas de hechos tendrán una mayor cantidad de filas y una menor cantidad de columnas. Una clave principal definida en la tabla de hechos es principalmente para identificar cada fila por separado. La clave principal también se denomina clave compuesta en tabla de hechos.
Si falta la clave compuesta en una tabla de hechos y si dos registros tienen los mismos datos, es muy difícil diferenciar entre los datos y hacer referencia a los datos en tablas de dimensiones.
Por lo tanto, si existe una clave única adecuada como clave compuesta, entonces es bueno generar un número de secuencia para cada registro de la tabla de hechos. Otra alternativa es formar una clave primaria concatenada. Esto se generará concatenando todas las claves primarias referidas de las tablas de dimensiones en filas.
Una sola tabla de hechos puede estar rodeada por varias tablas de dimensiones. Con la ayuda de las claves externas que existen en las tablas de hecho, se puede hacer referencia al contexto respectivo (datos detallados) de los valores medidos en las tablas de dimensiones. Con la ayuda de consultas, los usuarios realizarán un desglose y un resumen de manera eficiente.
El nivel más bajo de datos que se puede almacenar en una tabla de hechos se conoce como Granularidad. El número de tablas de dimensiones asociadas con una tabla de hechos es inversamente proporcional a la granularidad de los datos de la tabla de hechos. es decir, el valor de medición más pequeño necesita más tablas de dimensiones para referirse.
En un modelo dimensional, las tablas de hechos mantienen una relación de varios a varios con las tablas de dimensiones.
Un ejemplo de una tabla de hechos de ventas:
Plan de carga para tablas de hechos
Puede cargar los datos de una tabla de hechos de manera eficiente si considera los siguientes indicadores:
# 1) Eliminar y restaurar índices
Los índices de hecho, las tablas son buenos impulsores del rendimiento al consultar los datos, pero destruyen el rendimiento al cargar los datos. Por lo tanto, antes de cargar datos enormes en tablas de hechos, coloque principalmente todos los índices en esa tabla, cargue los datos y restaure los índices.
# 2) Insertos separados de las actualizaciones
No combine registros de inserción y actualización mientras se carga en una tabla de hechos. Si el número de actualizaciones es menor, procese las inserciones y las actualizaciones por separado. Si el número de actualizaciones es mayor, es recomendable truncar y volver a cargar la tabla de hechos para obtener resultados rápidos.
# 3) Particionamiento
Realice la partición físicamente en una tabla de hechos en mini tablas para un mejor rendimiento de la consulta en los datos de la tabla de hechos masivos. A excepción de los DBA y el equipo ETL, nadie estará al tanto de las particiones en los hechos.
Como un ejemplo , puede particionar una tabla por mes, por trimestre, por año, etc. Durante la consulta, solo se consideran los datos particionados en lugar de escanear toda la tabla.
# 4) Carga en paralelo
convertir la matriz de caracteres a int c ++
Ahora tenemos una idea sobre las particiones en tablas de hechos. Las particiones de hechos también son beneficiosas al cargar grandes datos en hechos. Para hacer esto, primero, divida los datos lógicamente en diferentes archivos de datos y ejecute los trabajos ETL para cargar todas estas porciones lógicas de datos en paralelo.
# 5) Utilidad de carga masiva
A diferencia de otros sistemas RDBMS, el sistema ETL no necesita mantener registros de reversión explícitamente para fallas a mitad de transacción. Aquí las 'cargas masivas' se convierten en hechos en lugar de 'inserciones SQL' para cargar datos enormes. Si falla una sola carga, entonces todos los datos se pueden recargar fácilmente (o) se pueden continuar desde donde se dejaron con la carga masiva.
# 6) Eliminar un registro de hechos
La eliminación de un registro de tabla de hechos ocurre solo si la empresa lo desea explícitamente. Si hay datos de la tabla de hechos que ya no existen en los sistemas de origen, entonces esos datos respectivos pueden eliminarse físicamente (o) lógicamente.
- Eliminación física: Los registros no deseados se eliminan de la tabla de hechos de forma permanente.
- Eliminación lógica: Se agregará una nueva columna a la tabla de hechos, como 'eliminado' de tipo bit (o) booleano. Esto actúa como una bandera para representar los registros eliminados. Debe asegurarse de no seleccionar los registros eliminados mientras consulta los datos de la tabla de hechos.
# 7) Secuencia de actualizaciones y eliminaciones en una tabla de hechos
Cuando hay datos para actualizar, las tablas de dimensiones deben actualizarse primero y luego actualizar las claves sustitutas en la tabla de búsqueda si es necesario y luego se actualiza la tabla de hechos respectiva. La eliminación ocurre a la inversa porque la eliminación de todos los datos no deseados de las tablas de hechos facilita la eliminación de los datos no deseados vinculados de las tablas de dimensiones.
Debemos seguir la secuencia anterior en ambos casos porque las tablas de dimensiones y las tablas de hechos mantienen la integridad referencial todo el tiempo.
Tipos de hechos
Según el comportamiento de los datos de las tablas de hechos, se clasifican como tablas de hechos de transacciones, tablas de hechos de instantáneas y tablas de hechos de instantáneas acumuladas. Todos estos tres tipos siguen diferentes características con diferentes estrategias de carga de datos.
# 1) Tablas de hechos de transacciones
Como su nombre indica, las tablas de hechos de transacciones almacenan datos a nivel de transacción para cada evento que ocurre. Este tipo de datos es fácil de analizar a nivel de tabla de hechos. Pero para un análisis más detallado, también puede consultar las dimensiones asociadas.
Por ejemplo, cada venta (o) compra que se realice desde un sitio web de marketing debe cargarse en una tabla de hechos de transacciones.
A continuación se muestra un ejemplo de una tabla de hechos de transacciones.
# 2) Tablas de datos de instantáneas periódicas
Como su nombre lo indica, los datos en la tabla de hechos de instantáneas periódicas se almacenan en forma de instantáneas (imágenes) a intervalos periódicos, por ejemplo, para todos los días, semanas, meses, trimestres, etc., según las necesidades comerciales.
Entonces, está claro que se trata de una agregación de datos todo el tiempo. Por lo tanto, los hechos instantáneos son más complejos en comparación con las tablas de hechos de transacciones. Por ejemplo, los datos de los informes de ingresos por rendimiento se pueden almacenar en tablas de hechos instantáneos para facilitar su consulta.
A continuación se muestra un ejemplo de una tabla de datos de instantáneas periódicas.
# 3) Acumulación de tablas de hechos de instantáneas
La acumulación de tablas de hechos de instantáneas le permite almacenar datos en tablas durante toda la vida útil de un producto. Esto actúa como una combinación de los dos tipos anteriores donde los datos pueden ser insertados por cualquier evento en cualquier momento como una instantánea.
En este tipo, las columnas de fecha y los datos adicionales de cada fila se actualizan con cada hito de ese producto.
Un ejemplo de una tabla de hechos de instantáneas acumuladas.
Además de los tres tipos anteriores, aquí hay algunos otros tipos de tablas de hechos:
# 4) Tablas de hechos sin hechos: Un hecho es una colección de medidas, mientras que hecho menos captura solo eventos (o) condiciones que no contienen ninguna medida. Una tabla de hechos sin hechos se usa principalmente para rastrear un sistema. Los datos de estas tablas se pueden analizar y utilizar para la elaboración de informes.
Por ejemplo, puede buscar detalles de un empleado que ha tomado una licencia y el tipo de licencia en un año, etc. Incluyendo todos estos detalles de hechos no claros de hecho, la tabla definitivamente aumentará el tamaño de los hechos.
A continuación se muestra un ejemplo de una tabla de hechos sin hechos.
# 5) Tablas de hechos conformadas: Un hecho conformado es un hecho que puede ser referido de la misma manera con cada data mart con el que está relacionado.
Especificaciones de una tabla de hechos
A continuación se presentan las especificaciones de una tabla de hechos.
- Nombre del hecho: Esta es una cadena que describe brevemente la funcionalidad de la tabla de hechos.
- Procesos de negocio: Las conversaciones sobre el negocio deben cumplirse con esa tabla de hechos.
- Preguntas: Menciona una lista de preguntas comerciales que serán respondidas por esa tabla de hechos.
- Grano: Indica el nivel más bajo de detalle asociado con los datos de la tabla de hechos.
- Dimensiones: Enumere todas las tablas de dimensiones asociadas con esa tabla de hechos.
- Medidas: Los valores calculados almacenados en la tabla de hechos.
- Frecuencia de carga Representa los intervalos de tiempo para cargar datos en la tabla de hechos.
- Filas iniciales: Consulte los datos iniciales que aparecen en la tabla de hechos por primera vez.
Ejemplo de modelado de datos dimensionales
Puede tener una idea de cómo se pueden diseñar las tablas de dimensiones y las tablas de hechos para un sistema si observa el siguiente diagrama de modelado de datos dimensionales para ventas y pedidos.
Conclusión
A estas alturas, debería haber adquirido un excelente conocimiento sobre las técnicas de modelado de datos dimensionales, sus beneficios, mitos, tablas de dimensiones, tablas de hechos, junto con sus tipos y procesos.
¡Consulte nuestro próximo tutorial para obtener más información sobre los esquemas de almacenamiento de datos!
=> Visite aquí para aprender el almacenamiento de datos desde cero.
Lectura recomendada
- Tutorial de pruebas de almacenamiento de datos con ejemplos | Guía de prueba ETL
- Ejemplos de minería de datos: aplicaciones más comunes de minería de datos 2021
- Tutorial de Python DateTime con ejemplos
- Fundamentos de almacenamiento de datos: una guía definitiva con ejemplos
- Tutorial de prueba de volumen: ejemplos y herramientas de prueba de volumen
- Las 10 principales herramientas de almacenamiento de datos y tecnologías de prueba más populares
- Minería de datos: procesos, técnicas y problemas principales en el análisis de datos
- Cómo realizar pruebas basadas en datos en SoapUI Pro - Tutorial de SoapUI n. ° 14