Compartir a través de

Modelado dimensional en Microsoft Fabric Warehouse: tablas de carga

  • Artículo

Se aplica a:✅ punto de conexión de análisis de 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 cargar tablas de dimensiones y 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.

Carga de un modelo dimensional

Cargar un modelo dimensional implica ejecutar periódicamente un proceso de extracción, transformación y carga de datos (ETL). Un proceso ETL organiza la ejecución de otros procesos, que suelen ocuparse de los datos de origen de almacenamiento provisional, la sincronización de los datos de dimensión, la inserción de filas en tablas de hechos y la grabación de datos y errores de auditoría.

En el caso de una solución Fabric Warehouse, puede usar Data Factory para desarrollar y ejecutar el proceso ETL. El proceso puede almacenar provisionalmente, transformar y cargar datos de origen en las tablas del modelo dimensional.

En concreto, puede:

  • Use canalizaciones de datos para crear flujos de trabajo para organizar el proceso ETL. Las canalizaciones de datos pueden ejecutar scripts SQL, procedimientos almacenados y mucho más.
  • Use flujos de datos para desarrollar lógica con poco código para ingerir datos de cientos de orígenes de datos. Los flujos de datos admiten la combinación de datos de varios orígenes, la transformación de datos y la carga en un destino, como una tabla de modelos dimensionales. Los flujos de datos se crean mediante la conocida experiencia de Power Query que está disponible hoy en día en muchos productos de Microsoft, incluidos Microsoft Excel y Power BI Desktop.

Nota:

El desarrollo de ETL puede ser complejo y el desarrollo puede ser difícil. Se estima que el 60 al 80 % de un esfuerzo de desarrollo de almacenamiento de datos está dedicado al proceso ETL.

Orquestación

El flujo de trabajo general de un proceso ETL es el siguiente:

  1. Opcionalmente, cargue tablas de almacenamiento provisional.
  2. Procesar tablas de dimensiones.
  3. Procesar tablas de hechos.
  4. Opcionalmente, realice tareas posteriores al procesamiento, como desencadenar la actualización del contenido dependiente de Fabric (como un modelo semántico).

En el diagrama se muestran los cuatro pasos del proceso ETL, tal como se describe en el párrafo anterior.

Las tablas de dimensiones deben procesarse primero para asegurarse de que almacenan todos los miembros de dimensión, incluidos los agregados a los sistemas de origen desde el último proceso ETL. Cuando hay dependencias entre dimensiones, como sucede con las subdimensiones, las tablas de dimensiones deben procesarse en orden de dependencia. Por ejemplo, una dimensión de geografía que usa una dimensión de cliente y una dimensión de proveedor se debe procesar antes de las otras dos dimensiones.

Las tablas de hechos se pueden procesar una vez procesadas todas las tablas de dimensiones.

Cuando se procesan todas las tablas de modelos dimensionales, es posible que desencadene la actualización de modelos semánticos dependientes. También es una buena idea enviar una notificación al personal pertinente para informarles del resultado del proceso ETL.

Datos almacenados provisionalmente

Los datos de origen de almacenamiento provisional pueden ayudar a admitir los requisitos de carga y transformación de datos. Implica extraer los datos del sistema de origen y cargarlos en tablas de almacenamiento provisional, que se crean para admitir el proceso ETL. Se recomienda almacenar provisionalmente los datos de origen porque puede:

  • Minimice el impacto en los sistemas operativos.
  • Se usa para ayudar con el procesamiento ETL, y optimizarlo.
  • Proporcionar la capacidad de reiniciar el proceso ETL, sin necesidad de volver a cargar datos desde sistemas de origen.

Los datos de las tablas de almacenamiento provisional nunca deben estar disponibles para los usuarios empresariales. Solo es relevante para el proceso ETL.

Nota:

Cuando los datos se almacenan en Fabric Lakehouse, es posible que no sea necesario almacenar provisionalmente sus datos en el almacenamiento de datos. Si implementa una arquitectura de medallón, podría obtener sus datos de la capa de bronce, plata o oro.

Se recomienda crear un esquema en el almacén, posiblemente denominado staging. Las tablas de almacenamiento provisional deben ser lo más similares posible a las tablas de origen en términos de nombres de columna y tipos de datos. El contenido de cada tabla debe quitarse al principio del proceso ETL. Sin embargo, tenga en cuenta que las tablas de Fabric Warehouse no se pueden truncar. En su lugar, puede quitar y volver a crear cada tabla de almacenamiento provisional antes de cargarla con datos.

También puede considerar alternativas de virtualización de datos como parte de la estrategia de almacenamiento provisional. Puede usar:

Transformación de datos

Es posible que la estructura de los datos de origen no se parezca a las estructuras de destino de las tablas del modelo dimensional. Por lo tanto, el proceso ETL debe cambiar la forma de los datos de origen para alinearse con la estructura de las tablas del modelo dimensional.

Además, el almacenamiento de datos debe proporcionar datos limpios y conformes, por lo que es posible que los datos de origen deban transformarse para garantizar la calidad y la coherencia.

Nota:

El concepto de elementos de incluir elementos no utilizados, excluir elementos no utilizados se aplica ciertamente al almacenamiento de datos; por lo tanto, evite cargar datos de elementos no utilizados (de baja calidad) en las tablas de modelos dimensionales.

Estas son algunas transformaciones que podría realizar el proceso ETL.

  • Combinar datos: los datos de diferentes orígenes se pueden integrar (combinar) en función de las claves coincidentes. Por ejemplo, los datos del producto se almacenan en diferentes sistemas (como la fabricación y el marketing), pero todos utilizan una unidad común de mantenimiento de existencias (SKU). Los datos también se pueden anexar cuando comparten una estructura común. Por ejemplo, los datos de ventas se almacenan en varios sistemas. Una unión de las ventas de cada sistema puede producir un superconjunto de todos los datos de ventas.
  • Convertir tipos de datos: los tipos de datos se pueden convertir en los definidos en las tablas del modelo dimensional.
  • Cálculos: los cálculos se pueden realizar para generar valores para las tablas del modelo dimensional. Por ejemplo, para una tabla de dimensiones de empleado, puede concatenar los nombres y apellidos para generar el nombre completo. Como otro ejemplo, para la tabla de hechos de ventas, puede calcular los ingresos brutos de ventas, que es el producto de precio unitario y cantidad.
  • Detectar y administrar los cambios históricos: los cambios se pueden detectar y almacenar correctamente en tablas de dimensiones. Para obtener más información, consulte Administrar cambios históricos más adelante en este artículo.
  • Datos agregados: la agregación se puede usar para reducir la dimensionalidad de la tabla de hechos o para aumentar la granularidad de los hechos. Por ejemplo, la tabla de hechos de ventas no necesita almacenar números de pedido de ventas. Por lo tanto, se puede usar un resultado agregado que agrupa por todas las claves de dimensión para almacenar los datos de la tabla de hechos.

Carga de datos

Puede cargar tablas en Fabric Warehouse mediante las siguientes opciones de ingesta de datos.

  • COPY INTO (T-SQL): esta opción es útil cuando los datos de origen componen archivos Parquet o CSV almacenados en una cuenta de almacenamiento de Azure externa, como ADLS Gen2 o Azure Blob Storage.
  • Canalizaciones de datos: además de orquestar el proceso ETL, las canalizaciones de datos pueden incluir actividades que ejecutan instrucciones T-SQL, realizan búsquedas o copian datos de un origen de datos a un destino.
  • Flujos de datos: como una alternativa a las canalizaciones de datos, los flujos de datos permiten una experiencia sin código para una fácil limpieza y transformación de los datos.
  • Ingesta entre almacenes: cuando los datos se almacenan en la misma área de trabajo, la ingesta entre almacenes permite unir diferentes tablas de almacenes o almacenes. Admite comandos T-SQL como INSERT…SELECT, SELECT INTO y CREATE TABLE AS SELECT (CTAS). Estos comandos son especialmente útiles cuando desea transformar y cargar datos de tablas de almacenamiento provisional en la misma área de trabajo. También son operaciones basadas en conjuntos, que es probable que sean la manera más eficaz y rápida de cargar tablas de modelos dimensionales.

Sugerencia

Para obtener una explicación completa de estas opciones de ingesta de datos, incluidos los procedimientos recomendados, consulte Ingesta de datos en almacén.

Registro

Normalmente, los procesos ETL requieren supervisión y mantenimiento dedicados. Por estos motivos, se recomienda registrar los resultados del proceso ETL en tablas de modelos no dimensionales del almacén. Debe generar un identificador único para cada proceso ETL y usarlo para registrar los detalles de cada operación.

Considere la posibilidad de registrar:

  • El proceso ETL:
    • Un Id. exclusivo para cada ejecución ETL
    • Hora de inicio y finalización
    • Estado (correcto o error)
    • Todos los errores detectados
  • Cada tabla de modelos de ensayo y dimensionales:
    • Hora de inicio y finalización
    • Estado (correcto o error)
    • Filas insertadas, actualizadas y eliminadas
    • Recuento de filas de la tabla final.
    • Todos los errores detectados
  • Otras operaciones:
    • Hora de inicio y hora de finalización de las operaciones de actualización del modelo semántico

Sugerencia

Puede crear un modelo semántico dedicado a la supervisión y el análisis de los procesos ETL. Las duraciones de proceso pueden ayudarle a identificar cuellos de botella que podrían beneficiarse de la revisión y la optimización. Los recuentos de filas pueden permitirle comprender el tamaño de la carga incremental cada vez que se ejecuta el ETL y también ayudar a predecir el tamaño futuro del almacenamiento de datos (y cuándo escalar verticalmente la capacidad de Fabric, si procede).

Procesar tablas de dimensiones

El procesamiento de una tabla de dimensiones implica la sincronización de los datos del almacenamiento de datos con los sistemas de origen. Los datos de origen se transforman y preparan para cargarlos en su tabla de dimensiones. A continuación, estos datos se combinan con los datos de la tabla de dimensiones existentes mediante la combinación en las claves de negocio. A continuación, es posible determinar si los datos de origen representan datos nuevos o modificados. Cuando la tabla de dimensiones aplica el tipo 1 de dimensión de variación lenta (SCD), los cambios se realizan actualizando las filas de la tabla de dimensiones existentes. Cuando la tabla aplica cambios en el SCD de tipo 2, la versión existente ha caducado y se inserta una nueva versión.

En el diagrama siguiente se muestra la lógica usada para procesar una tabla de dimensiones.

Diagrama que muestra un flujo que describe cómo se cargan las filas de origen nuevas y modificadas en una tabla de dimensiones, como se describe en el párrafo siguiente.

Considere el proceso de la tabla de dimensiones Product.

  • Cuando se agregan nuevos productos al sistema de origen, las filas se insertan en la tabla de dimensiones Product.
  • Cuando se modifican los productos, las filas existentes de la tabla de dimensiones se actualizan o insertan.
    • Cuando se aplica el SCD de tipo 1, las actualizaciones se realizan en las filas existentes.
    • Cuando se aplica SCD de tipo 2, se realizan actualizaciones para expirar las versiones de fila actuales y se insertan nuevas filas que representan la versión actual.
    • Cuando se aplica SCD de tipo 3, se produce un proceso similar al SCD de tipo 1, actualizando las filas existentes sin insertar nuevas filas.

Claves suplentes

Se recomienda que cada tabla de dimensiones tenga una clave suplente, que debe usar el tipo de datos Integer más pequeño posible. En entornos basados en SQL Server, esto se suele hacer con la creación de una columna de identidad, sin embargo, esta característica no se admite en un Fabric Warehouse. En su lugar, deberá usar una técnica alternativa que genere identificadores únicos.

Importante

Cuando una tabla de dimensiones incluye claves suplentes generadas automáticamente, nunca debe realizar un truncamiento y una recarga completa. Esto se debe a que invalidaría los datos cargados en tablas de hechos que utilizan la dimensión. Además, si la tabla de dimensiones admite cambios de SCD de tipo 2, es posible que no sea posible volver a generar las versiones históricas.

Administrar cambios históricos

Cuando una tabla de dimensiones debe almacenar el cambio histórico, deberá implementar una dimensión de variación lenta (SCD).

Nota:

Si la fila de la tabla de dimensiones es un miembro inferido (insertado por un proceso de carga de hechos), debe tratar los cambios como detalles de dimensión de llegada tardía en lugar de un cambio de SCD. En este caso, los atributos modificados deben actualizarse y la columna de marca de miembro inferida debe establecerse en FALSE.

Es posible que una dimensión admita cambios a los SCD de tipo 1 o tipo 2.

SCD de tipo 1

Cuando se detectan cambios en el SCD de tipo 1, use la siguiente lógica.

  1. Actualice los atributos modificados.
  2. Si la tabla incluye las columnas de fecha de última modificación y última modificación hecha por, establezca la fecha y el proceso actuales que en que se realizaron las modificaciones.

SCD de tipo 2

Cuando se detectan cambios en el SCD de tipo 2, use la siguiente lógica.

  1. Expire la versión actual estableciendo la columna de validez de fecha de finalización en la fecha de procesamiento de ETL (o una marca de tiempo adecuada en el sistema de origen) y la marca actual en FALSE.
  2. Si la tabla incluye las columnas de fecha de última modificación y última modificación hecha por, establezca la fecha y el proceso actuales que en que se realizaron las modificaciones.
  3. Inserte nuevos miembros que tengan la columna de validez de fecha de inicio establecida en el valor de la columna de validez de fecha de finalización (que se usa para actualizar la versión anterior) y que tenga la marca de versión actual establecida en TRUE.
  4. Si la tabla incluye las columnas fecha de creación y creado por, establezca la fecha y el proceso actuales en que se realizaron las inserciones.

SCD de tipo 3

Cuando se detectan cambios en el SCD de tipo 3, actualice los atributos mediante una lógica similar al procesamiento de SCD de tipo 1.

Eliminaciones de miembros de dimensión

Tenga cuidado si los datos de origen indican que se eliminaron los miembros de dimensión (ya sea porque no se recuperan del sistema de origen o se han marcado como eliminados). No debe sincronizar eliminaciones con la tabla de dimensiones, a menos que se crearan miembros de dimensión en error y no haya registros de hechos relacionados con ellas.

La manera adecuada de controlar las eliminaciones de origen es registrarlas como una eliminación temporal. Una eliminación temporal marca un miembro de dimensión como ya no activo o válido. Para admitir este caso, la tabla de dimensiones debe incluir un atributo booleano con el tipo de datos bit, como IsDeleted. Actualice esta columna para los miembros de dimensión eliminados a TRUE (1). La actual y última versión de un miembro de dimensión podría marcarse de forma similar con un valor booleano (bit) en las columnas IsCurrent o IsActive. Todas las consultas de informes y los modelos semánticos de Power BI deben filtrar los registros que son eliminaciones temporal.

Dimensión de fecha

Las dimensiones de calendario y hora son casos especiales porque normalmente no tienen datos de origen. En su lugar, se generan mediante lógica fija.

Debe cargar la tabla de dimensiones de fecha al principio de cada año nuevo para ampliar sus filas a un número específico de años posteriores. Puede haber otros datos empresariales, por ejemplo, datos del año fiscal, días festivos, números de semana que se deben actualizar periódicamente.

Cuando la tabla de dimensiones de fecha incluye atributos de desplazamiento relativos, el proceso ETL debe ejecutarse diariamente para actualizar los valores de atributo de desplazamiento en función de la fecha actual (hoy).

Se recomienda que la lógica para extender o actualizar la tabla de dimensiones de fecha se escriba en T-SQL y se encapsule en un procedimiento almacenado.

Procesamiento de tablas de hechos

El procesamiento de una tabla de hechos implica sincronizar los datos del almacenamiento de datos con los hechos del sistema de origen. Los datos de origen se transforman y preparan para cargarlos en su tabla de hechos. A continuación, para cada clave de dimensión, una búsqueda determina el valor de clave suplente que se almacenará en la fila de hechos. Cuando una dimensión admite el SCD de tipo 2, se debe recuperar la clave suplente para la versión actual del miembro de dimensión.

Nota:

Normalmente, la clave suplente se puede calcular para las dimensiones de fecha y hora, ya que deben usar los formatos YYYYMMDD o HHMM. Para obtener más información, consulte Calendario y horas.

Si se produce un error en la búsqueda de una clave de dimensión, podría indicar un problema de integridad con el sistema de origen. En este caso, la fila de hechos todavía debe insertarse en la tabla de hechos. Todavía se debe almacenar una clave de dimensión válida. Un enfoque consiste en almacenar un miembro de dimensión especial (como Desconocido). Este enfoque requiere una actualización posterior para asignar correctamente el valor de clave de dimensión true, cuando se conoce.

Importante

Dado que Fabric Warehouse no aplica claves externas, es fundamental que el proceso ETL compruebe la integridad cuando carga datos en tablas de hechos.

Otro enfoque, relevante cuando hay confianza en que la clave natural es válida, es insertar un nuevo miembro de dimensión y, a continuación, almacenar su valor de clave suplente. Para obtener más información, vea Miembros de dimensión inferidos más adelante en esta sección.

En el diagrama siguiente se muestra la lógica usada para procesar una tabla de hechos.

Diagrama que muestra un flujo que describe cómo se cargan las nuevas filas de origen en una tabla de hechos, como se describe en los párrafos anteriores.

Siempre que sea posible, se debe cargar una tabla de hechos de forma incremental, lo que significa que se detectan e insertan hechos nuevos. Una estrategia de carga incremental es más escalable y reduce la carga de trabajo tanto para los sistemas de origen como para los sistemas de destino.

Importante

Especialmente para una tabla de hechos grande, debe ser un último recurso para truncar y volver a cargar una tabla de hechos. Este enfoque es costoso en términos de tiempo de proceso, recursos de proceso y puede causar una interrupción en los sistemas de origen. También implica complejidad cuando las dimensiones de la tabla de hechos aplica el SCD de tipo 2. Esto se debe a que las búsquedas de claves de dimensión deberán realizarse dentro del período de validez de las versiones de miembro de dimensión.

Esperemos que pueda detectar de forma eficaz nuevos hechos al confiar en identificadores de sistema de origen o marcas de tiempo. Por ejemplo, cuando un sistema de origen registra de forma confiable los pedidos de ventas que están en secuencia, puede almacenar el número de pedido de ventas más reciente recuperado (conocido como límite máximo). El siguiente proceso puede usar ese número de pedido de ventas para recuperar los pedidos de ventas recién creados y, de nuevo, almacenar el número de pedido de ventas más reciente recuperado para su uso en el siguiente proceso. También puede ser posible que se pueda usar una columna de fecha de creación para detectar pedidos nuevos de forma confiable.

Si no puede confiar en los datos del sistema de origen para detectar de forma eficaz hechos nuevos, es posible que pueda confiar en una funcionalidad del sistema de origen para realizar una carga incremental. Por ejemplo, SQL Server y Azure SQL Managed Instance tienen una característica denominada captura de datos modificados (CDC), que puede realizar un seguimiento de los cambios en cada fila de una tabla. Además, SQL Server, Azure SQL Managed Instance y base de datos de Azure SQL tienen una característica denominada seguimiento de cambios, que puede identificar las filas que han cambiado. Cuando está habilitada, puede ayudarle a detectar datos nuevos o modificados de forma eficaz en cualquier tabla de base de datos. También puede agregar desencadenadores a tablas relacionales que almacenan claves de registros de tabla insertados, actualizados o eliminados.

Por último, es posible que pueda correlacionar los datos de origen con la tabla de hechos mediante atributos. Por ejemplo, el número de pedido de ventas y el número de línea de pedido de ventas. Sin embargo, para las tablas de hechos de gran tamaño, podría ser una operación muy costosa para detectar hechos nuevos, modificados o eliminados. También podría ser problemático cuando el sistema de origen archiva los datos operativos.

Miembros de dimensión inferidos

Cuando un proceso de carga de hechos inserta un nuevo miembro de dimensión, se conoce como miembro inferido. Por ejemplo, cuando un huésped del hotel se registra, se les pide que se unan a la cadena de hoteles como miembro de la fidelidad. Un número de pertenencia se emite inmediatamente, pero es posible que los detalles del invitado no sigan hasta que el invitado envíe los documentos (si es que los envía).

Todo lo que se conoce sobre el miembro de dimensión es su clave natural. El proceso de carga de hechos debe crear un nuevo miembro de dimensión mediante el valor de atributo Desconocido. Importantemente, debe establecer la auditoría de atributos IsInferredMember en TRUE. De este modo, cuando se crean los detalles de llegada tardía, el proceso de carga de dimensiones puede realizar las actualizaciones necesarias en la fila de dimensión. Para obtener más información, consulte la sección Administrar cambios históricos de este artículo.

Actualizaciones o eliminaciones de hechos

Es posible que tenga que actualizar o eliminar datos de hechos. Por ejemplo, cuando se cancela un pedido de venta o se cambia la cantidad de un pedido. Como se ha descrito anteriormente para cargar tablas de hechos, debe detectar de forma eficaz los cambios y realizar las modificaciones adecuadas en los datos de hechos. En este ejemplo del pedido cancelado, el estado del pedido de venta probablemente cambiaría de Abierto a Cancelado. Ese cambio requeriría una actualización de los datos de hechos y no la eliminación de una fila. Para el cambio de cantidad, sería necesaria una actualización de la medida de cantidad de filas de hechos. Esta estrategia de uso de eliminaciones temporales conserva el historial. Una eliminación temporal marca una fila como ya no activa o válida, y todas las consultas de informes y los modelos semánticos de Power BI deben filtrar los registros que son eliminaciones temporales.

Al prever actualizaciones o eliminaciones de hechos, debe incluir atributos (como un número de pedido de ventas y su número de línea de pedido de ventas) en la tabla de hechos para ayudar a identificar las filas de hechos que se van a modificar. Asegúrese de indexar estas columnas para admitir operaciones de modificación eficaces.

Por último, si los datos de hechos se insertaron mediante un miembro de dimensión especial (como Desconocido), deberá ejecutar un proceso periódico que recupere los datos de origen actuales para estas filas de hechos y actualizar las claves de dimensión a valores válidos.

Contenido relacionado

Para obtener más información sobre cómo obtener datos en un Fabric Warehouse, consulte: