Modelado dimensional en Microsoft Fabric Warehouse: tablas de hechos

Se aplica a: punto de conexión de análisis SQL y un almacén de Microsoft Fabric

Nota:

Este artículo forma parte de la serie de artículos sobre el Modelado dimensional. Esta serie se centra en guías y procedimientos recomendados de diseño relacionados con el modelado dimensional en Microsoft Fabric Warehouse.

En este artículo se proporcionan guías y procedimientos recomendados para diseñar tablas de hechos en un modelo dimensional. Ofrece instrucciones prácticas para Warehouse en Microsoft Fabric, que es una experiencia que admite muchas funcionalidades de T-SQL, como crear tablas y administrar datos en tablas. Por lo tanto, usted tiene control total sobre la creación de tablas de modelos dimensionales y la carga de datos.

Nota:

En este artículo, el término almacenamiento de datos hace referencia a un almacenamiento de datos empresarial, que ofrece una integración completa de los datos críticos de toda la organización. En cambio, el término independiente almacén hace referencia a Fabric Warehouse, que es una oferta de base de datos relacional de software como servicio (SaaS) que se puede usar para implementar un almacenamiento de datos. Para evitar malentendidos, en este artículo lo llamaremos Fabric Warehouse.

Sugerencia

Si no tiene experiencia con el modelado dimensional, considere esta serie de artículos el primer paso. No está pensada para ofrecer una explicación completa sobre el diseño del modelado dimensional. Para obtener más información, consulte directamente el contenido publicado con mayor difusión, como The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3ª edición, 2013) de Ralph Kimball y otros.

En un modelo dimensional, una tabla de hechos almacena medidas asociadas a observaciones o eventos. Podría almacenar pedidos de venta, saldos de existencias, tipos de cambio, lecturas de temperatura, etc.

Las tablas de hechos incluyen medidas, que suelen ser columnas numéricas, como la cantidad de pedidos de ventas. Las consultas analíticas resumen las medidas (mediante suma, recuento, promedio y otras funciones) en el contexto de filtros y agrupaciones de dimensiones.

Las tablas de hechos también incluyen claves de dimensiones, que determinan la dimensionalidad de los hechos. Los valores de clave de dimensiones determinan la granularidad de los hechos, que es el nivel atómico por el que se definen los hechos. Por ejemplo, una clave de dimensiones de fecha de pedido en una tabla de hechos de ventas establece la granularidad de los hechos por fecha, mientras que una clave de dimensión de fecha de destino en una tabla de hechos de destino de ventas podría establecer la granularidad por trimestre.

Nota:

Aunque es posible almacenar hechos en una granularidad mayor, no es fácil dividir los valores de medida en niveles inferiores de granularidad (si es necesario). Los grandes volúmenes de datos, junto con los requisitos analíticos, pueden proporcionar un motivo válido para almacenar hechos de granularidad más altos, pero a costa del análisis detallado.

Para identificar fácilmente las tablas de hechos, normalmente se usan prefijos en los nombres como f_ o Fact_.

Estructura de la tabla de hechos

Para describir la estructura de una tabla de hechos, considere el ejemplo siguiente de una tabla de hechos de ventas denominada f_Sales. En este ejemplo se aplican buenas prácticas de diseño. En las secciones siguientes se describen cada uno de los grupos de columnas.

CREATE TABLE f_Sales
(
    --Dimension keys
    OrderDate_Date_FK INT NOT NULL,
    ShipDate_Date_FK INT NOT NULL,
    Product_FK INT NOT NULL,
    Salesperson_FK INT NOT NULL,
    <…>
    
    --Attributes
    SalesOrderNo INT NOT NULL,
    SalesOrderLineNo SMALLINT NOT NULL,
    
    --Measures
    Quantity INT NOT NULL,
    <…>
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

Clave principal

Como sucede en el ejemplo, la tabla de hechos de ejemplo no tiene una clave principal. Esto se debe a que normalmente no sirve para un propósito útil y aumentaría innecesariamente el tamaño de almacenamiento de tablas. Una clave principal suele estar implícita en el conjunto de claves de dimensiones y atributos.

Claves de dimensión

La tabla de hechos de muestra tiene varias claves de dimensiones, que determinan la dimensionalidad de la tabla de hechos. Las claves de dimensiones son referencias a las claves suplentes (o atributos de nivel superior) en las dimensiones relacionadas.

Nota:

Es una tabla de hechos inusual que no incluye al menos una clave de dimensiones de fecha.

Una tabla de hechos puede hacer referencia a una dimensión varias veces. En este caso, se conoce como dimensión realizadora de roles. En este ejemplo, la tabla de hechos tiene las claves de dimensiones OrderDate_Date_FK y ShipDate_Date_FK. Cada clave de dimensiones representa un rol distinto, pero solo hay una dimensión de fecha física.

Se recomienda establecer cada clave de dimensión como NOT NULL. Durante la carga de la tabla de hechos, puede usar miembros de dimensión especiales para representar los estados de error faltantes, desconocidos, N/A o error (si es necesario).

Atributos

La tabla de hechos de muestra tiene dos atributos. Los atributos proporcionan información adicional y establecen la granularidad de los datos de hechos, pero no son claves de dimensión ni atributos de dimensión ni medidas. En este ejemplo, las columnas de atributo almacenan información del pedido de ventas. Otros ejemplos podrían incluir números de seguimiento o números de vale. Para el análisis, un atributo podría formar una dimensión degenerada.

Medidas

La tabla de hechos de muestra también tiene medidas, como la columna Quantity. Las columnas de medida suelen ser numéricas y habitualmente de adición (lo que significa que se pueden sumar y resumir mediante otras agregaciones). Para más información, consulte la sección Tipos de medidas más adelante en este artículo.

Atributos de auditoría

La tabla de hechos de muestra también tiene varios atributos de auditoría. Los atributos de auditoría son opcionales. Permiten realizar un seguimiento de cuándo y cómo se crearon o modificaron los registros de hechos y pueden incluir información de diagnóstico o solución de problemas generada durante los procesos de extracción, transformación y carga de datos (ETL). Por ejemplo, puede realizar un seguimiento de quién (o qué proceso) actualizó una fila y cuándo. Los atributos de auditoría también pueden ayudar a diagnosticar un problema complicado, como cuando un proceso de ETL se detiene inesperadamente.

Tamaño de tabla de hechos

Las tablas de hechos varían en tamaño. Su tamaño corresponde a la dimensionalidad, granularidad, número de medidas y cantidad del historial. En comparación con las tablas de dimensiones, las tablas de hechos son más estrechas (menos columnas), pero grandes o incluso inmensas en cuanto a filas (en exceso de miles de millones).

Conceptos de diseño de hechos

En esta sección se describen varios conceptos sobre el diseño de hechos.

Tipos de tabla de hechos

Hay tres tipos de tablas de hechos:

• Tablas de hechos de transacciones
• Tablas de hechos de instantáneas periódicas
• Acumulación de tablas de hechos de instantáneas

Tablas de hechos de transacciones

Una tabla de hechos de transacciones almacena eventos de negocio o transacciones. Cada fila almacena hechos en términos de claves de dimensión y medidas, y opcionalmente otros atributos. Todos los datos se conocen completamente cuando se insertan y nunca cambian (excepto para corregir errores).

Normalmente, las tablas de hechos de transacciones almacenan hechos en el nivel más bajo posible de granularidad y contienen medidas que son aditivas en todas las dimensiones. Una tabla de hechos de ventas que almacena todas las líneas de pedidos de ventas es un buen ejemplo de una tabla de hechos de transacciones.

Tablas de hechos de instantáneas periódicas

Una tabla de hechos de instantánea periódica almacena medidas en un momento predefinido o intervalos específicos. Proporciona un resumen de las métricas clave o los indicadores de rendimiento a lo largo del tiempo, por lo que resulta útil para el análisis de tendencias y la supervisión de los cambios a lo largo del tiempo. Las medidas siempre son semididitivas (descritas más adelante).

Una tabla de hechos de inventarios es un buen ejemplo de una tabla de instantáneas periódicas. Se carga todos los días con el saldo de existencias de fin de día de todos los productos.

Las tablas de instantáneas periódicas se pueden usar en lugar de una tabla de hechos de transacciones cuando el registro de grandes volúmenes de transacciones es costoso y no admite ningún requisito analítico útil. Por ejemplo, puede haber millones de movimientos de existencias en un día (que se pueden almacenar en una tabla de hechos de transacciones), pero el análisis solo se ocupa de las tendencias de los niveles de existencias de fin de día.

Acumulación de tablas de hechos de instantáneas

Una tabla de hechos de instantánea de acumulación almacena medidas que se acumulan en un periodo o flujo de trabajo bien definido. A menudo registra el estado de un proceso empresarial en distintas fases o hitos, lo que puede tardar días, semanas o incluso meses en completarse.

Una fila de hechos se carga poco después del primer evento de un proceso y, a continuación, la fila se actualiza en una secuencia predecible cada vez que se produce un evento de hito. Las actualizaciones continúan hasta que finaliza el proceso.

La acumulación de la tabla de hechos de instantáneas tiene varias claves de dimensiones de fecha, cada una representa un evento hito. Algunas claves de dimensiones pueden registrar un estado N/A hasta que el proceso llegue a un hito determinado. Mide, por lo general, duraciones de registros. Las duraciones entre hitos pueden proporcionar información valiosa sobre un flujo de trabajo empresarial o un proceso de ensamblado.

Tipos de medidas

Las medidas suelen ser numéricas y normalmente aditivas. Sin embargo, algunas medidas no siempre se pueden sumar. Estas medidas se clasifican como semididitivas o no aditivas.

Medidas aditivas

Una medida aditiva se puede sumar en cualquier dimensión. Por ejemplo, la cantidad de pedidos y el ingreso de ventas son medidas aditivas (siempre que el ingreso se registre en una sola moneda).

Medidas de suma parcial

Una medida semididitiva solo se puede sumar en determinadas dimensiones.

Estos son algunos ejemplos de medidas semiaditivas.

• Cualquier medida de una tabla de hechos de instantáneas periódicas no se puede sumar en otros periodos de tiempo. Por ejemplo, no debe sumar la antigüedad de un artículo de inventario muestreado por la noche, pero podría sumar la antigüedad de todos los artículos de inventario en un estante, cada noche.
• Una medida de saldo de existencias de una tabla de hechos de inventario no se puede sumar en otros productos.
• El ingreso de ventas de una tabla de hechos de ventas que tiene una clave de dimensiones de moneda no se pueden sumar entre monedas.

Medidas no aditivas

Una medida no aditiva no se puede sumar en cualquier dimensión. Un ejemplo es una lectura de temperatura, que por su naturaleza no tiene sentido agregarla a otras lecturas.

Entre otros ejemplos se incluyen tarifas, como precios unitarios y ratios. Sin embargo, se recomienda almacenar los valores que se utilizan para calcular la relación, lo que permite calcular la relación si es necesario. Por ejemplo, un porcentaje de descuento de un hecho de ventas podría almacenarse como una medida de importe de descuento (que se dividirá por la medida de ingresos de ventas). O bien, la antigüedad de un artículo de inventario del estante no debe sumarse con el tiempo, pero es posible que observe una tendencia en la antigüedad media de los artículos de inventario.

Aunque algunas medidas no se pueden sumar, siguen siendo medidas válidas. Se pueden agregar mediante recuento, recuento de valores únicos, mínimo, máximo, media y otros. Además, las medidas no aditivas pueden convertirse en aditivas cuando se utilizan en cálculos. Por ejemplo, el precio unitario multiplicado por la cantidad de pedidos genera ingresos de ventas, que es aditivo.

Tablas de hechos sin hechos

Cuando una tabla de hechos no contiene ninguna columna de medidas, se denomina tabla de hechos sin hechos. Una tabla de hechos sin hechos normalmente registra eventos o concurrencias, como los alumnos que asisten a la clase. Desde una perspectiva de análisis, se puede lograr una medida mediante el recuento de filas de hechos.

Tablas de hechos agregadas

Una tabla de hechos agregada representa un resumen de una tabla de hechos base a una dimensionalidad inferior o granularidad superior. Su finalidad es acelerar el rendimiento de las consultas para las dimensiones consultadas habitualmente.

Nota:

Un modelo semántico de Power BI puede generar agregaciones definidas por el usuario para lograr el mismo resultado o usar la tabla de hechos agregada del almacenamiento de datos mediante el modo de almacenamiento de DirectQuery.

Contenido relacionado

En el siguiente artículo de esta serie, se presenta información sobre instrucciones y procedimientos recomendados de diseño para cargar tablas de modelos dimensionales.