Hechos sobre Hechos: Organización de tablas de hechos en sistemas de almacén de datos

El proceso de definición de su sistema de almacén de datos (DWH) ha comenzado. Ha esbozado las tablas de dimensiones relevantes, que se vinculan a los requisitos del negocio. Estas tablas definen lo que pesamos, observamos y escalamos. Ahora tenemos que definir cómo medimos.

Las tablas de hechos son donde almacenamos estas medidas. Contienen datos de negocio que pueden ser agregados a través de combinaciones de dimensiones. Pero el hecho es que las tablas de hechos no son tan fáciles de describir – tienen sabores propios. En este artículo, responderemos a algunas preguntas básicas sobre las tablas de hechos, y examinaremos los pros y los contras de cada tipo.

¿Qué son las tablas de hechos?

En el sentido más general, las tablas de hechos son las medidas de un proceso de negocio. Contienen principalmente datos numéricos y corresponden a un evento más que a un informe particular.

La característica más importante de una tabla de hechos, además de las medidas, es el grano. El grano define qué nivel de detalle se observa para un evento en particular. (Compare esto con la frase «controles de grano fino», que significa que los usuarios pueden controlar detalles muy pequeños para su cuenta).

Cuanto más detallado sea el grano de la tabla de hechos, mejor manejará los requisitos imprevisibles del negocio. La desventaja de un mayor grano es que se requiere más espacio físico para el almacenamiento de datos. También puede causar un rendimiento más lento.

¿Cómo se estructuran las tablas de hechos?

Una tabla de hechos general consta de dos grupos de atributos principales:

• Claves externas a las tablas dimensionales
• Medidas

Las claves externas se explican por sí mismas; las dimensiones degeneradas también pertenecen a este grupo. Una dimensión degenerada es una clave de dimensión que no tiene una tabla de dimensión principal. Ocurren cuando toda la información importante sobre la dimensión ya está en la tabla de hechos. Los ejemplos incluyen varios números de cabecera de control, números de ticket, números de pedido, etc.

Medidas (es decir métricas o hechos empresariales) en una tabla de hechos pueden ser:

• Aditivas: sumables a través de cualquier dimensión
• Semi-aditivas: sumables a través de algunas dimensiones
• No-aditivas: no sumables (por ejemplo, varios ratios)

Además de las medidas y las claves externas, puede haber muchas columnas técnicas. Las columnas técnicas son útiles para la auditoría y el mantenimiento de bajo nivel del modelo. Las marcas de tiempo, que se utilizan para marcar cuando se producen inserciones o actualizaciones en la tabla de hechos, son un ejemplo común de una columna técnica.

Para ilustrar una tabla de hechos genérica, veamos un esquema en estrella muy sencillo:

En este modelo, tenemos una única tabla de hechos rodeada por tres tablas de dimensiones. Nuestras claves externas para la tabla de hechos incluyen:

• time_id – se refiere a la tabla de dimensión tiempo (dim_time)
• product_id – se refiere a la tabla de dimensión producto (dim_product)
• store_id – se refiere a la tabla de dimensión tienda (dim_store)
• pos_transaction – una dimensión degenerada. Esta es toda la información sobre el TPV que necesitamos, así que podemos almacenarla aquí. No hay razón para construir otra tabla de dimensión.

Nuestro grupo de claves de medida consiste en:

• sales_quantity – Las ventas de un producto en un momento dado en una tienda. Esta medida es aditiva.
• sales_price – El precio de un artículo tal y como se vende en la tienda. Esta es una medida no aditiva.

Hay una columna técnica, time_inserted, que almacena el momento en que se inserta una fila.

Ahora que tenemos una idea general de las tablas de hechos, vamos a profundizar en sus diferentes sabores. Hay cuatro tipos de tablas de hechos: de transacciones, de instantáneas periódicas, de instantáneas acumulativas y de tablas de hechos sin hechos.

Cada sabor sirve para representar el negocio subyacente que soporta el sistema de data warehousing. Sin embargo, antes de profundizar en lo que hacen estas diferentes tablas de hechos, vamos a hablar de un importante factor común: la dispersión, o la cantidad proporcional de datos almacenados en una tabla de hechos. La dispersión está relacionada con el grano y tiene un efecto en el rendimiento de las consultas.

¿Qué es la dispersión de las tablas de hechos?

Cuando se diseña una tabla de hechos, hay que tener en cuenta su dispersión, es decir, qué número de filas de la tabla están pobladas y cuántas están vacías. Si llenamos las tablas de hechos a partir de muchas tablas subyacentes, es conveniente estimar la dispersión. Hacemos la estimación basándonos en el nivel de grano de la tabla de hechos. Si su tabla de hechos coincide con la tabla de origen subyacente, puede utilizar la cardinalidad de la tabla subyacente para calcular la dispersión.

Para calcular la dispersión, primero encontramos la proporción de llenado, es decir, el número de celdas no vacías respecto al total de celdas. Para calcular la proporción de llenado, se divide el número de filas de la tabla de origen entre el número de filas distintas de cada tabla de dimensión. La dispersión se calcula entonces como 1-(f), donde f = proporción de llenado.

Ejemplo:

Supongamos que llenamos la tabla fact_retail_sale a partir de dos fuentes: una tabla de base de datos con 100.000 filas y una hoja de cálculo con 20.000 filas. Estas filas se rellenan anualmente. Las tablas de dimensiones son: fechas (365 filas), productos (100 filas) y tiendas (1.000 filas).

Calculamos la dispersión como:

1-(100.000 + 20.000)/(365*100*1.000) = 0,99671

Esta tabla de hechos es dispersa, porque menos del 1% de sus filas tienen valores distintos de cero.

Una buena regla general es considerar que todo lo que esté por debajo del 2% es muy escaso; todo lo que esté por encima no es tan escaso.

Tablas de hechos de transacciones

Este es el tipo más simple y más común de tabla de hechos. El grano de este tipo es una fila por transacción, o una fila por línea en una transacción. El grano de una tabla de hechos de transacciones es un punto en el espacio y el tiempo. Contienen los detalles más pequeños del negocio.

Cuando ocurre una transacción, se captura un amplio contexto sobre ella. Este contexto crea muchos detalles en las tablas de dimensiones, por lo que esperamos que haya muchos.

Una vez que insertamos una fila en una tabla de transacciones, rara vez, o nunca, se vuelve a visitar. Esto nos lleva a considerar técnicas específicas para poblar este tipo de tablas. La ausencia de actualizaciones implica un proceso ETL (extracción, transformación y carga) mucho más sencillo.

Pros: Una mayor granularidad permite el seguimiento de actividades empresariales detalladas.

Contra: Problemas de rendimiento en la consulta. Dificultad para interpretar comportamientos de tendencias en puntos fijos en el tiempo.

Un ejemplo de tabla de hechos de transacciones

Veamos un típico esquema de estrella financiera:

Registramos cada acción de la cuenta en la tabla de hechos. Los datos de muestra de esta tabla incluyen:

Nota: time_date se representa como se muestra en la dimensión dim_time, y la columna técnica se ignora.

A medida que el propietario de la cuenta completa las transacciones, como depositar y retirar dinero, registramos cada acción.

Con esta estructura podemos responder a preguntas como: ¿Cuántas transacciones al día procesa nuestro negocio? Cuál es la cantidad media retirada en un día? etc.

Tablas de hechos de instantáneas periódicas

Las tablas de instantáneas periódicas registran el rendimiento acumulado del negocio en períodos de tiempo predefinidos. Un intervalo predeterminado para tomar instantáneas es la clave: diario, semanal, mensual, etc. Los resultados se guardan en la tabla de hechos de instantáneas periódicas.

Este tipo de tabla de hechos nos da mucha libertad de acción: podemos incorporar cualquier información que describa la actividad durante un periodo de tiempo.

El paso de una transacción a una tabla periódica es posible. (Hay algunos algoritmos ordenados en SQL que se ocupan de esto, como veremos en próximos artículos). Sólo se trata de sumar las transacciones de una instantánea a otra. Así como las tablas de hechos de transacciones tienen muchas dimensiones, la instantánea suele tener menos dimensiones.

Pros: Ganancia de rendimiento, visión longitudinal del negocio, compactación física.

Contra: Grano inferior.

Un ejemplo de tabla de hechos de instantáneas periódicas

Consideremos otro esquema estrella financiero:

Supongamos que construimos esta tabla de hechos y su data mart a partir de la misma fuente que el ejemplo anterior. Incluso podríamos haber construido esta tabla de hechos a partir de los datos de la tabla de hechos anterior.

Necesitamos mostrar el balance_amount en cada fecha de instantánea; en este caso, la granularidad de la instantánea es de un día. Agregamos todas las transacciones de salida diariamente, y el tipo de depósito es sumar y el retiro es restar. El resultado es:

Nota: Presentamos time_date tal y como se muestra en la dimensión dim_time y se ignora la columna técnica.

Ahora podemos responder a algunas preguntas de negocio comunes, como: ¿Cuántos días lleva un cliente un saldo de cuenta positivo en un mes? ¿Cuál es el saldo medio de un tipo específico de cliente cada mes? etc.

Reorganización de una tabla de hechos de instantáneas periódicas

Puede que haya notado cierta redundancia en los datos mostrados en el ejemplo anterior. A veces, incluso la tabla de hechos de instantáneas es demasiado grande y el rendimiento se tambalea. ¿Qué hacer entonces? Podemos reorganizar nuestra tabla de hechos instantáneos en algo más compacto.

Hemos añadido dos nuevas columnas, date_from y date_to. Representan el periodo de tiempo en el que el balance_amount fue válido.

Los datos de esta tabla tendrían este aspecto:

Cada vez que el balance_amount cambia, cerramos el periodo de tiempo y abrimos uno nuevo.

Nota que la columna date_to contiene un delimitador de tiempo no inclusivo. Hay que tenerlo en cuenta a la hora de consultar los datos. También encontramos una fecha interesante: 31/12/9999. Esta fecha demuestra que la última fila es el último saldo conocido. La actualizaremos cuando el saldo cambie.

A continuación, podemos ver el SQL que utilizamos para consultar una instantánea en una fecha determinada (:fecha).

SELECT ft.*FROMfact_account_snapshot ftWHERE date_from 
Accumulating Snapshot Fact Tables
The accumulating snapshot fact table is the least common type, but it's also by far the most interesting and challenging. We use this table type when we represent processes that have a definite beginning and end.They are most appropriate when users must perform pipeline and workflow analysis.
Accumulating snapshot tables usually have multiple date foreign keys, each corresponding to a major milestone in the workflow. And while the processes captured in this type of table have beginnings and ends, intermediate steps are recorded as well.
Transaction fact tables may have several dates associated with each row; the accumulating fact table allows for many of the date values to be unknown when the row is first loaded. This is unique among fact tables, and it can lead to anomalies – incomplete dimensions – in dimensional modeling.
We solve this by adding a default value to the missing fields in the fact table and the appropriate dimension table. As we revisit the row containing specific workflows, we must implement a different type of ETL for working with these tables.
Pros: Enables complex analysis of tables and workflow analysis.
Cons: High ETL complexity.
An Example of an Accumulating Snapshot Fact Table
In this example, imagine that we have to analyze the effectiveness of our loan approval process, from initial application to final funding. Obviously, we first need to prepare our data!

  

Our loan approval process has four steps:
 
Loan application from a bank customer
 
Submission of complete documentation needed for loan approval (appraisal, credit status etc.)
 
Approval of the loan application from a higher-ranking bank employee
 
Funding the loan
 
One row in our fact table represents one instance of this process.
As we see in the data table, not all our workflows are complete; future actions will make us revisit the fact table and update our data. This is the main difference between this and other types of fact table. 
Some questions that we can answer with this type of fact table are: Where is the bottleneck in the loan approval process? What is causing it? What is the average time needed for end-to-end approval? etc.
 Let's look at the data from the fact table. We'll split the display into two tables:


Looking at the data, we see a strange date: 01.01.1900. This date represents a missing or "not yet filled" date. It is more practical to have a default value for missing key data so we don't have abnormalities when we do a roll-up. 
We can also draw some conclusions from this data. Bigger loans take more time to process. This is normal because they need more documentation and safety checks. Smaller loans complete the process much faster.
Factless Fact Tables
This inconspicuous fact table can also be found in the data warehouse modeling world. The factless fact table does not have any measurements; it only holds foreign keys to dimensional tables. This information is enough to answer relevant business questions.
Pros: Simplicity
Cons: Limited usefulness
An Example of a Factless Fact Table
In the following example, we see a star schema featuring a factless fact table. The schema is used for tracking student attendance, with four dimensional tables representing courses, facilities, dates, and departments.


With this schema, we can find answers to questions like: How many students attended a class last month? What course has the lowest attendance this year? What facility is not being used? etc.
A simple query can answer the last question. The code would be:
SELECT course_id, count(*) number of attendanceFROMfact_student_attendance ftJOIN dim_course dc ON (dc.course_id = ft.course_id)JOIN dim_time dt ON (dt.time_id = ft.time_id)WHERE dt.time_year = 2016GROUP BY course_idORDER BY count(*) desc
Nuestra respuesta se devuelve en la primera línea de la consulta.
Las tablas de datos son la carne del mundo del almacenamiento de datos. Su tamaño puede equivaler a muchos terabytes, y son las que más espacio ocupan en un almacén de datos. Un error al principio del proceso de diseño de la tabla de hechos genera muchos problemas, ¡que no son más fáciles de resolver a medida que el almacén evoluciona! 
Es posible reorganizar los datos de un tipo de tabla de hechos a otro. Exploraremos las técnicas para hacerlo en un próximo post. Qué tal tus experiencias con diferentes tipos de tablas de hechos? ¿Has conseguido mezclarlas de alguna manera nueva y emocionante? Si lo hiciste, házmelo saber.