Tabla de hechos

Introducción

En las bases de datos, y más concretamente en un data warehouse, una tabla de hechos (o tabla fact) es la tabla central de un esquema dimensional (en estrella o en copo de nieve) y contiene los valores de las medidas de negocio o dicho de otra forma los indicadores de negocio. Cada medida se toma mediante la intersección de las dimensiones que la definen, dichas dimensiones estarán reflejadas en sus correspondientes tablas de dimensiones que rodearán la tabla de hechos y estarán relacionadas con ella.

En la figura de la derecha, la tabla central (Ventas) es la tabla de hechos de un diseño de modelo de datos en estrella, las cinco tablas que la rodean (Producto, Tiempo, Almacén, Promoción y Cliente) son las cinco dimensiones de que consta esta tabla de hechos, en dicha tabla se almacenan, en este caso, las unidades vendidas y el precio obtenido por dichas ventas, estos son los hechos o medidas de negocio almacenados y que, gracias al diseño multidimensional en estrella, podrán ser analizados de forma exhaustiva, típicamente mediante técnicas OLAP (procesamiento analítico on-line).

Las medidas del negocio (hechos)

Las medidas más útiles para incluir en una tabla de hechos son los aditivos, es decir, aquellas medidas que pueden ser sumadas como por ejemplo la cantidad de producto vendido, los costes de producción o el dinero obtenido por las ventas; son medidas numéricas que pueden calcularse con la suma de varias cantidades de la tabla. En consecuencia, por lo general los hechos a almacenar en una tabla de hechos van a ser casi siempre valores numéricos, enteros o reales.

Cardinalidad de la tabla de hechos

Las tablas de hechos pueden contener un gran número de filas, a veces cientos de millones de registros cuando contienen uno o más años de la historia de una gran organización, esta cardinalidad estará acotada superiormente por la cardinalidad de las tablas dimensionales, Por ejemplo, si se tiene una tabla de hechos "TH" de tres dimensiones D1, D2 y D3, el número máximo de elementos que tendrá la tabla de hechos TH será:

Card(TH) = Card(D1) x Card(D2) x Card(D3)

Donde 'Card(x)' es la cardinalidad de la tabla 'x'

Naturalmente, estas cardinalidades no son fijas, ya que, por ejemplo, si una de las dimensiones se refiere a los clientes de la empresa, cada vez que se dé de alta un nuevo cliente se estará aumentando la cardinalidad de la tabla de hechos. Una de las dimensiones suele ser el tiempo, éste puede medirse de muy distintas formas (por horas, días, semanas, ...), pero lo cierto es que transcurre continuamente, y para que el sistema funcione se deben añadir registros periódicamente a la tabla de esta dimensión (tabla de tiempos) y esto también produce un aumento de la cardinalidad de la tabla de hechos, ésta es la principal causa de que las tablas de hechos lleguen a tener una cantidad de registros del orden de millones de elementos.

Granularidad

Una característica importante que define a una tabla de hechos es el nivel de granularidad de los datos que en ella se almacenan, entendiéndose por 'granularidad' el nivel de detalle de dichos datos, es decir, la granularidad de la tabla de hechos representa el nivel más atómico por el cual se definen los datos. Por ejemplo, no es lo mismo contar el tiempo por horas (grano fino) que por semanas (grano grueso); o en el caso de los productos, se puede considerar cada variante de un mismo artículo como un producto (por ejemplo, en una empresa textil, cada talla y color de pantalón podría ser un producto) o agrupar todos los artículos de una misma familia considerándolos como un único producto (por ejemplo, el producto pantalón genérico).

Como se puede observar, la granularidad afecta a la cardinalidad, tanto de las dimensiones como de la tabla de hechos, a mayor granularidad (grano más fino) mayor será el número de registros final de la tabla de hechos.

Cuando la granularidad es mayor, es frecuente que se desee disponer de subtotales parciales, es decir, si tenemos una tabla de hechos con las ventas por días, podría interesar disponer de los totales semanales o mensuales, estos datos se pueden calcular haciendo sumas parciales, pero es frecuente añadir a la tabla de hechos registros donde se almacenan dichos cálculos para no tener que repetirlos cada vez que se requieran y mejorar así el rendimiento de la aplicación. En este caso se dispondrá en la misma tabla de hechos de datos de grano fino y de grano más grueso aumentando aún más la cardinalidad de la tabla.

Agregación

La agregación es un proceso de cálculo por el cual se resumen los datos de los registros de detalle. Esta operación consiste normalmente en el cálculo de totales dando lugar a medidas de grano grueso. Cuando se resumen los datos, el detalle ya no está directamente disponible para el analista, ya que este se elimina de la tabla de hechos.

Esta operación se realiza típicamente con los datos más antiguos de la empresa con la finalidad de seguir disponiendo de dicha información (aunque sea resumida) para poder eliminar registros obsoletos de la tabla de hechos para liberar espacio.

Tipos de datos adecuados

Como ya se ha comentado, es normal que las tablas de hechos almacenen muchos millones de registros, por esta razón es muy importante que no se despilfarre memoria, hay que procurar utilizar los tipos de datos adecuados, si una medida a almacenar puede guardarse en un campo de tipo entero, no debemos definir ese campo como de tipo entero largo o como tipo real. Del mismo modo, si una magnitud necesita decimales, si las características de ésta lo permiten, será mejor utilizar un tipo real simple que un tiporeal de doble precisión. Nótese que elegir uno u otro de estos campos, en principio sólo supondría una diferencia de unos pocos bytes en un registro, pero dado que en una tabla de hechos estamos hablando de cientos de millones de registros, en realidad, esa diferencia no es despreciable (5 bytes x 200 millones de registros = 1GB de memoria).

Tabla de dimensión

En un almacén de datos o un sistema OLAP, la construcción de Cubos OLAP requiere de una tabla de hechos y varias tablas de dimensiones, éstas acompañan a la tabla de hechos y determinan los parámetros (dimensiones) de los que dependen los hechos registrados en la tabla de hechos.

Introducción

En la construcción de cubos OLAP, las tablas de dimensiones son elementos que contienen atributos (o campos) que se utilizan para restringir y agrupar los datos almacenados en una tabla de hechos cuando se realizan consultas sobre dicho datos en un entorno de almacén de datos o data mart.

Estos datos sobre dimensiones son parámetros de los que dependen otros datos que serán objeto de estudio y análisis y que están contenidos en la tabla de hechos. Las tablas de dimensiones ayudan a realizar ese estudio/análisis aportando información sobre los datos de la tabla de hechos, por lo que puede decirse que en un cubo OLAP, la tabla de hechos contiene los datos de interés y las tablas de dimensiones contienen metadatos sobre dichos hechos.

Granularidad de dimensión y jerarquías

Cada dimensión puede referirse a conceptos como 'tiempo', 'productos', 'clientes', 'zona geográfica', etc. Ahora bien, cada dimensión puede estar medida de diferentes maneras según la granularidad deseada, por ejemplo, para la dimensión "zona geográfica" podríamos considerar 'localidades', 'provincias', 'regiones', 'países' o 'continentes'.

Detalle de una tabla de dimensión de un cubo OLAP con un esquema en estrella.

Detalle de una tabla de dimensión de un cubo OLAP con un esquema en copo de nieve.

La unidad de medida (por localidades, provincias, etc.) determinará esa granularidad, cuanto más pequeña sea esta unidad de medida más fina será esta granularidad (grano fino); si las unidades de medida son mayores, entonces hablaremos de granularidad gruesa (grano grueso).

En muchas ocasiones interesa disponer de los datos a varios niveles de granularidad, es decir, es importante para el negocio poder consultar los datos (siguiendo el ejemplo de las zonas) por localidades, provincias, etc., en estos casos se crea una jerarquía con la dimensión, ya que tenemos varios niveles de asociación de los datos (con otras dimensiones como el tiempo, se podrían crear niveles jerárquicos del tipo 'días', 'semanas', 'meses', ...).

Cuando las tablas de dimensión asociadas a una tabla de hechos no reflejan ninguna jerarquía (por ejemplo: Las zonas siempre son 'provincias' y sólo provincias, el tiempo se mide en 'días' y sólo en días, etc.) el cubo resultante tendrá forma de estrella, es decir, una tabla de hechos central rodeada de tantas tablas como dimensiones, y sólo habrá, además de la tabla de hechos, una tabla por cada dimensión.

Cuando una o varias de las dimensiones del cubo refleja algún tipo de jerarquía existen dos planteamientos con respecto a la forma que deben ser diseñadas las tablas de dimensión. El primero consiste en reflejar todos los niveles jerárquicos de una dimensión dentro de una única tabla, en este caso también tendríamos un esquema en estrella como el que se ha descrito anteriormente.

El otro planteamiento consiste en aplicar a las dimensiones las reglas de normalización de las bases de datos relacionales. Estas normas están ideadas para evitar redundancias en los datos aumentando el número de tablas, de esta forma se consigue almacenar la información en menos espacio. Este diseño da como resultado en esquema en copo de nieve. Este modo de organizar las dimensiones de un cubo OLAP tiene un inconveniente respecto al modelo en estrella que no compensa el ahorro de espacio de almacenamiento. En las aplicaciones OLAP el recurso crítico, no es tanto el espacio para almacenamiento como el tiempo de respuesta del sistema ante consultas del usuario, y está constatado que los modelos en copo de nieve tienen un tiempo de respuesta mayor que los modelos en estrella.

Ejemplo de Cubo OLAP modelo de datos en estrella de 5 dimensiones.

Ejemplo de Cubo OLAP modelo de datos en copo de nievede 5 dimensiones.

La dimensión "tiempo"

En cualquier Dataware house se pueden encontrar varios cubos con sus tablas de hechos repletas de registros sobre alguna variable de interés para el negocio que debe ser estudiada. Como ya se ha comentado, cada tabla de hechos estará rodeada de varias tablas de dimensiones, según que parámetros sirvan mejor para realizar el análisis de los hechos que se quieren estudiar. Un parámetro que casi con toda probabilidad será común a todos los cubos es el tiempo, ya que lo habitual es almacenar los hechos conforme van ocurriendo a lo largo del tiempo, obteniéndose así una serie temporal de la variable a estudiar.

Tabla de tiempos (1) Fecha (PK) datetime Año char(4) Trimestre char(6) Mes char(10)

Tabla de tiempos (2) TiempoID (PK) integer Fecha datetime Año char(4) Trimestre char(6) Mes char(10)

Dado que el tiempo es una dimensión presente en prácticamente cualquier cubo de un sistema OLAP merece una atención especial. Al diseñar la dimensión tiempo (tanto para un esquema en estrella como para un esquema en copo de nieve) hay que prestar especial cuidado, ya que puede hacerse de varias maneras y no todas son igualmente eficientes. La forma más común de diseñar esta tabla es poniendo como clave principal (PK) de la tabla la fecha ofecha/hora (tabla de tiempos 1). Este diseño no es de los más recomendables, ya que a la mayoría de los sistemas de gestión de bases de datos les resulta más costoso hacer búsquedas sobre campos de tipo "date" o "datetime", estos costes se reducen si el campo clave es de tipo entero, además, un dato entero siempre ocupa menos espacio que un dato de tipo fecha (el campo clave se repetirá en millones de registros en la tabla de hechos y eso puede ser mucho espacio), por lo que se mejorará el diseño de la tabla de tiempos si se utiliza un campo "TiempoID" de tipo entero como clave principal (tabla de tiempos 2).

A la hora de rellenar la tabla de tiempos, si se ha optado por un campo de tipo entero para la clave, hay dos opciones, la que quizá sea más inmediata consiste en asignar valores numéricos consecutivos (1, 2, 3, 4, ...) para los diferentes valores de fechas. La otra opción consistiría en asignar valores numéricos del tipo "yyyymmdd", es decir que los cuatro primeros dígitos del valor del campo indican el año de la fecha, los dos siguientes el mes y los dos últimos el día. Este segundo modo aporta una cierta ventaja sobre el anterior, ya que de esta forma se consigue que el dato numérico en sí, aporte por si solo la información de a que fecha se refiere, es decir, si en la tabla de hechos encontramos el valor 20040723, sabremos que se refiere al día 23 de julio de 2004; en cambio, con el primer método, podríamos encontrar valores como 8456456, para saber a que fecha se refiere este valor tendríamos que hacer una consulta sobre la tabla de tiempos.

Además del campo clave TiempoID, la tabla de hechos debe contener otros campos que también es importante estudiar. Estos campos serían:

Tabla de tiempos (3) TiempoID (PK) integer Fecha datetime Año char(4) Trimestre char(6) TrimestreID int Mes char(10) MesID int Quincena char(10) QuincenaID int Semana char(10) SemanaID int Día char(10) DíaID int DíaSemana char(10) DíaSemanaID int

• Un campo "año".- Para contener valores como '2002', 2003, '2004', ...

• Un campo "mes".- Aquí se pueden poner los valores 'Enero', 'Febrero', ... (o de forma abreviada: 'Ene', 'Feb', ...), esto no está mal, pero se puede mejorar si el nombre del mes va acompañado con el año al que pertenece, es decir '2004 Enero', '2004 Febrero', ... de esta forma se optimiza la búsqueda de los valores de un mes en concreto, ya que con el primer método, si se buscan los valores pertenecientes al mes de "Enero de 2003", toda esa información está contenida en un sólo campo, el "mes", no haría falta consultar también el campo año.

• Un campo "mesID".- Este campo tendría que ser de tipo entero y serviría para almacenar valores del tipo 200601 (para '2006 Enero') o 200602 (para '2006 Febrero'), de esta forma es posible realizar ordenaciones y agrupaciones por meses.

De forma análoga a como se ha hecho con el campo mes, se podrían añadir más campos como "Época del año", "Trimestre", "Quincena", "Semana" de tipo texto para poder visualizarlos, y sus análogos de tipo entero "Época del año_ID", "TrimestreID", "QuincenaID", "SemanaID" para poder realizar agrupaciones y ordenaciones. En general se puede añadir un campo por cada nivel de granularidad deseado.

Otro campo especial que se puede añadir es el "Día de la semana" ('lunes', 'martes', ...), este campo se suele añadir para poder hacer estudios sobre el comportamiento de los días de la semana en general (no del primer lunes del mes de enero de un año concreto, este tipo de estudio no suele tener interés), por esta razón, este campo no necesita ir acompañado del mes o del año como los campos anteriores. También se puede añadir su campo dual "ID" de tipo entero para poder ordenar y agrupar si fuera necesario.

Con los añadidos descritos podríamos tener una tabla de tiempos como la de la figura "Tabla de tiempos (3)". Esta sería válida para un diseño en estrella, para un diseño en copo de nieve habría que desglosar la tabla de tiempos en tantas tablas como niveles jerárquicos contenga. Obsérvese que los campos de tipo "ID" son todos de tipo entero, ya que será sobre estos campos sobre los que se realizarán la mayoría de las operaciones y estas se realizarán más eficientemente sobre datos enteros.

Esquema en estrella

Ejemplo de modelo de datos en estrella.

En las bases de datos usadas para data warehousing, un esquema en estrella es un modelo de datos que tiene una tabla de hechos (o tabla fact) que contiene los datos para el análisis, rodeada de las tablas de dimensiones. Este aspecto, de tabla de hechos (o central) más grande rodeada de radios o tablas más pequeñas es lo que asemeja a una estrella, dándole nombre a este tipo de construcciones.

Las tablas de dimensiones tendrán siempre una clave primaria simple, mientras que en la tabla de hechos, la clave principal estará compuesta por las claves principales de las tablas dimensionales.

Razones a favor del esquema en estrella

Este esquema es ideal por su simplicidad y velocidad para ser usado en análisis multidimensionales (OLAP, Datamarts, EIS, ...). Permite acceder tanto a datos agregados como de detalle.

El diseño de esquemas en estrella permite implementar la funcionalidad de una base de datos multidimensional utilizando una clásica base de datos relacional (más extendidas que las multidimensionales).

Otra razón para utilizar los esquemas en estrella es su simplicidad desde el punto de vista del usuario final. Las consultas no son complicadas, ya que las condiciones y las uniones (JOIN) necesarias sólo involucran a la tabla de hechos y a las de dimensiones, no haciendo falta que se encadenen uniones y condiciones a dos o más niveles como ocurriría en un esquema en copo de nieve. En la mayoría de los casos son preferibles los de estrellas por su simplicidad respecto a los de copo de nieve por ser más fáciles de manejar.

Finalmente, es la opción con mejor rendimiento y velocidad pues permite indexar las dimensiones de forma individualizada sin que repercuta en el rendimiento de la base de datos en su conjunto.

Ejemplo

Considerar una base de datos de 'ventas' (de una cadena de grandes almacenes) clasificados por fecha, almacén y producto.

TH_ventas es la tabla de hechos, y tiene tres dimensiones, representadas por la tablas D_fecha, D_almacen, y D_producto.

Cada tabla de dimensión tiene una clave primaria denominada id. En la tabla de hechos TH_ventas los campos clave de las tablas de dimensión aparecerán como clave ajena (id_fecha, id_almacen y id_producto) y todos juntos formarán la clave principal de dicha tabla.

Otras columnas de datos incluidas en las tablas descritas serían:

• TH_ventas.unidades_vendidas
• D_fecha.año
• D_almacen.país
• D_producto.categoría
• D_producto.marca

La siguiente instrucción SQL obtendría cómo han sido vendidos algunos conjuntos de televisores, para cada marca y país, en el año 2006.

SELECT

  P.marca, A.pais, SUM(TH.unidades_vendidas)

FROM

  TH_ventas TH

  INNER JOIN D_fecha F ON F.id = TH.id_fecha

  INNER JOIN D_almacen A ON A.id = TH.id_almacen

  INNER JOIN D_producto P ON P.id = TH.id_producto

WHERE

  F.anyo = 2006 

  AND 

  P.categoria = 'tv'

GROUP BY

  P.marca, A.pais

Esquema en copo de nieve

Ejemplo de modelo de datos en copo de nieve.

En las bases de datos utilizadas en (data warehousing), un esquema en copo de nieve es una estructura algo más compleja que el esquema en estrella. Se da cuando alguna de las dimensiones se implementa con más de una tabla de datos. La finalidad es normalizar las tablas y así reducir el espacio de almacenamiento al eliminar la redundancia de datos; pero tiene la contrapartida de generar peores rendimientos al tener que crear más tablas de dimensiones y más relaciones entre las tablas (JOINS) lo que tiene un impacto directo sobre el rendimiento.

Introducción

En las aplicaciones OLAP implementadas sobre bases de datos relacionales (ROLAP), un elemento clave es el Cubo OLAP. Estos cubos (también llamados hipercubos) almacenan grandes volúmenes de datos que posteriormente deben ser analizados en función de unos determinados parámetros. Al diseñar las tablas en las que se han de almacenar estos datos y parámetros, si se aplican las técnicas de Normalización de bases de datos para optimizar el espacio requerido para guardar estos datos eliminando las redundancias, es habitual que se termine obteniendo un esquema en copo de nieve; en este tipo de esquemas se tiene una tabla central de hechos en la que se guardan las medidas del negocio que se quiere analizar, y en las tablas adyacentes se tendrán las dimensiones (parámetros) de que dependen los datos del negocio. Si por alguna dimensión se requiere más de una tabla se dice que el esquema resultante es un esquema en copo de nieve.

En el ejemplo de la figura adjunto, pese a no estar totalmente normalizada (por ejemplo, la tabla 'Dimension_Almacen' tiene redundancias) se observa como para algunas dimensiones de la tabla de hechos como Producto y Cliente se ha empleado más de una tabla, dando lugar a una jerarquía de dimensiones. Por ejemplo, los productos se pueden clasificar pormarcas, además, estos mismos productos se pueden agrupar por categorías y subcategorías.

Argumentos a favor y en contra del esquema en copo de nieve

El único argumento a favor de los esquemas en copo de nieve es que al estar normalizadas las tablas de dimensiones, se evita la redundancia de datos y con ello se ahorra espacio. Pero si tenemos en cuenta que hoy en día, el espacio en disco no suele ser un problema, y sí el rendimiento, se presenta con una mala opción en Data warehouse, ya que el hecho de disponer de más de una tabla por cada dimensión de la tabla de hechos implica tener que realizar código más complejo para realizar una consulta que a su vez se ejecutará en un tiempo mayor, debido en parte al mayor número de uniones (JOINS) que habrá que realizar.

Se puede usar un esquema de copo de nieve en un Data warehouse, aunque estos sean realmente grandes y complejos, pero nunca en sistemas donde el tiempo de respuesta sea un factor crítico para los usuarios.