Modelado dimensional en Microsoft Fabric Warehouse: tablas de dimensiones

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 dimensiones 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, la tabla de dimensiones describe una entidad relevante para los requisitos empresariales y de análisis. En general, las tablas de dimensiones representan las cosas que modela. Las cosas podrían ser productos, personas, lugares o cualquier otro concepto, incluidas fechas y horas. Para identificar fácilmente las tablas de dimensiones, normalmente se usan prefijos en los nombres como d_ o Dim_.

Estructura de las tablas de dimensiones

Para describir la estructura de una tabla de dimensiones, considere el ejemplo siguiente de la tabla de dimensiones un comercial denominada d_Salesperson. 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 d_Salesperson
(
    --Surrogate key
    Salesperson_SK INT NOT NULL,
    
    --Natural key(s)
    EmployeeID VARCHAR(20) NOT NULL,
    
    --Dimension attributes
    FirstName VARCHAR(20) NOT NULL,
    <…>
    
    --Foreign key(s) to other dimensions
    SalesRegion_FK INT NOT NULL,
    <…>
    
    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditIsInferred BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

Clave suplente

La tabla de dimensiones de ejemplo tiene una clave suplente, denominada Salesperson_SK. La clave suplente es un identificador único de una sola columna que se genera y se almacena en la tabla de dimensiones. Se trata de una columna de clave principal que se usa para relacionarla con otras tablas del modelo dimensional.

Las claves suplentes tienen como objetivo aislar el almacenamiento de datos de los cambios en los datos de origen. También ofrecen muchas otras ventajas, puesto que le permiten:

• Consolidar varios orígenes de datos (y evitar conflictos de identificadores duplicados).
• Consolidar las claves naturales de varias columnas en una única clave de columna más eficaz.
• Llevar un seguimiento del historial de dimensiones de tipo 2 de dimensión de variación lenta (SCD).
• Limitar el ancho de la tabla de hechos para optimizarla para el almacenamiento (seleccionando el tipo de datos Integer más pequeño posible).

Se recomienda usar una columna de clave suplente incluso cuando una clave natural (descrita a continuación) parezca una opción aceptable. También se debe evitar dar significado a los valores de clave (excepto las claves de dimensión de fecha y hora, tal y como se describe más adelante).

Claves naturales

La tabla de dimensiones de ejemplo también tiene una clave natural, que se denomina EmployeeID. La clave natural es la clave almacenada en el sistema de origen. Permite relacionar los datos de dimensión con su sistema de origen; normalmente, esto realiza mediante un proceso de extracción, carga y transformación (ETL) para cargar la tabla de dimensiones. A veces, a la clave natural se la denomina clave empresarial y sus valores pueden ser relevantes para los usuarios profesionales.

Otras veces, las dimensiones no tienen clave natural. Podría ser el caso de la dimensión de fecha o de las dimensiones de búsqueda, o al generar datos de dimensión para normalizar un archivo plano.

Atributos de dimensión

La tabla de dimensiones de ejemplo también tiene atributos de dimensión, como la columna FirstName. Los atributos de dimensión proporcionan contexto a los datos numéricos almacenados en tablas de hechos relacionadas. Suelen ser columnas de texto que se usan en consultas de análisis para filtrar y agrupar (segmentar y dividir), pero no para agregarse. Algunas tablas de dimensiones contienen pocos atributos, mientras que otras contienen muchos (tantos como sean necesarios para admitir los requisitos de consulta del modelo dimensional).

Sugerencia

Una buena manera de determinar qué dimensiones y atributos necesita es dar con las personas correctas y formular las preguntas adecuadas. En concreto, preste atención a la palabra por. Por ejemplo, cuando alguien diga que necesita analizar las ventas por comercial, por mes y por categoría de producto, le estará indicando que necesita dimensiones que tengan esos atributos.

Si planea crear un modelo semántico de Direct Lake, debe incluir todas las columnas posibles necesarias para filtrar y agrupar como atributos de dimensión. Esto se debe a que los modelos semánticos de Direct Lake no admiten columnas calculadas.

Claves externas

La tabla de dimensiones de ejemplo también tiene una clave externa, que se denomina SalesRegion_FK. Otras tablas de dimensiones pueden hacer referencia a una clave externa y su presencia en una tabla de dimensiones es un caso especial. Indica que la tabla está relacionada con otra tabla de dimensiones, lo que significa que podría formar parte de una dimensión de copo de nieve o que está relacionada con una subdimensión.

Fabric Warehouse admite restricciones de clave externa, pero no se pueden aplicar. Por lo tanto, es importante que el proceso ETL compruebe la integridad entre las tablas relacionadas cuando se carguen los datos.

Sigue siendo buena idea crear claves externas. Una buena razón para crear claves externas no exigidas es permitir que las herramientas de modelado, como Power BI Desktop, detecten y creen automáticamente relaciones entre las tablas del modelo semántico.

Atributos de seguimiento histórico

La tabla de dimensiones de ejemplo también tiene varios atributos de seguimiento histórico. Los atributos de seguimiento histórico son opcionales según la necesidad de realizar un seguimiento de los cambios específicos a medida que se producen en el sistema de origen. Permiten almacenar valores para admitir el rol principal de un almacenamiento de datos, que es describir el pasado con precisión. En concreto, estos atributos almacenan el contexto histórico a medida que el proceso ETL carga datos nuevos o modificados en la dimensión.

Para obtener más información, consulte Administrar cambios históricos más adelante en este artículo.

Atributos de auditoría

La tabla de dimensiones de ejemplo también tiene varios atributos de auditoría. Los atributos de auditoría son opcionales, pero se recomiendan. Permiten realizar un seguimiento de cuándo y cómo se crearon o modificaron los registros de dimensión y pueden incluir información de diagnóstico o solución de problemas generada durante los procesos 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. Y pueden marcar los miembros de dimensión como errores o miembros inferidos.

Tamaño de la tabla de dimensiones

A menudo, las dimensiones más útiles y versátiles de un modelo dimensional son grandes y amplias. Son grandes en términos de filas (millones) y amplias en términos del número de atributos de dimensión (potencialmente, cientos). El tamaño no es tan importante (aunque debería diseñar y optimizar para lograr el tamaño más pequeño posible). Lo importante es que la dimensión admita el filtrado, la agrupación y el análisis histórico preciso de los datos de hechos necesarios.

Las dimensiones grandes pueden proceder de varios sistemas de origen. En este caso, el procesamiento de dimensiones debe agrupar, combinar, desduplicar y estandarizar los datos y asignar claves suplentes.

En comparación, algunas dimensiones son minúsculas. Pueden representar tablas de búsqueda que contienen solo varios registros y atributos. A menudo, estas pequeñas dimensiones almacenan valores de categoría relacionados con las transacciones de las tablas de hechos y se implementan como dimensiones con claves suplentes para relacionarlas con los registros de hechos.

Sugerencia

Cuando tenga muchas dimensiones pequeñas, considere la posibilidad de consolidarlas en una dimensión no deseada.

Conceptos sobre el diseño de dimensiones

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

Desnormalización frente a normalización

Casi siempre, hay que desnormalizar las tablas de dimensiones. El término normalización se usa para describir los datos almacenados de forma que se reduzcan los datos repetitivos, mientras que el término desnormalización se usa para definir dónde existen los datos redundantes previamente calculados. Normalmente, los datos redundantes existen debido al almacenamiento de jerarquías (se describe más adelante), lo que significa que las jerarquías se aplanan. Por ejemplo, una dimensión de producto podría almacenar la subcategoría (y sus atributos relacionados) y la categoría (y sus atributos relacionados).

Dado que las dimensiones suelen ser pequeñas (en comparación con las tablas de hechos), el coste de almacenar datos redundantes casi siempre se compensa con un mejor rendimiento y facilidad de uso de las consultas.

Dimensiones de copo de nieve

Como excepción a la desnormalización se puede diseñar una dimensión de copo de nieve. Las dimensiones de copo de nieve están normalizadas y almacenan los datos de dimensiones de varias tablas relacionadas.

En el diagrama siguiente se muestra una dimensión de copo de nieve que consta de tres tablas de dimensiones relacionadas: Product, Subcategory y Category.

Considere la posibilidad de implementar una dimensión de copo de nieve cuando:

• la dimensión sea extremadamente grande y los costes de almacenamiento superen la necesidad de obtener un alto rendimiento de las consultas. (Sin embargo, vuelva a evaluar periódicamente que sigue siendo así).
• necesite claves para relacionar la dimensión con hechos más específicos. Por ejemplo, la tabla de hechos de ventas almacena las filas a nivel de producto, pero la tabla de hechos de objetivo de ventas almacena las filas a nivel de subcategoría.
• necesite hacer un seguimiento de los cambios históricos en niveles de granularidad más altos.

Nota:

Tenga en cuenta que una jerarquía de un modelo semántico de Power BI solo se puede basar en las columnas de una única tabla de modelo semántico. Por lo tanto, la dimensión de copo de nieve debe proporcionar un resultado desnormalizado mediante una vista que combine las tablas de copo de nieve.

Jerarquías

Normalmente, las columnas de dimensión generan jerarquías. Las jerarquías permiten explorar datos en distintos niveles de resumen. Por ejemplo, la vista inicial de un objeto visual de matriz podría mostrar las ventas anuales y el consumidor del informe puede elegir explorar en profundidad para mostrar las ventas mensuales y trimestrales.

Hay tres maneras de almacenar una jerarquía en una dimensión. Puede usar:

• columnas de una sola dimensión desnormalizada.
• una dimensión de copo de nieve, que consta de varias tablas relacionadas.
• una relación de elementos primarios y secundarios (autorreferencia) en una dimensión.

Las jerarquías pueden ser equilibradas o desequilibradas. También es importante comprender que algunas jerarquías son desiguales.

Jerarquías equilibradas

Las jerarquías equilibradas son el tipo de jerarquía más habitual. Una jerarquía equilibrada tiene el mismo número de niveles. El típico ejemplo de jerarquía equilibrada es una jerarquía de calendario en una dimensión de fecha que comprende los niveles de año, trimestre, mes y fecha.

En el diagrama siguiente se muestra una jerarquía equilibrada de regiones de ventas. Consta de dos niveles, que son el grupo de regiones de ventas y la región de ventas.

Los niveles de una jerarquía equilibrada se basan en columnas de una sola dimensión desnormalizada o de tablas que forman una dimensión de copo de nieve. Cuando se basan en una sola dimensión desnormalizada, las columnas que representan los niveles superiores contienen datos redundantes.

En las jerarquías equilibradas, los hechos siempre se relacionan con un único nivel de la jerarquía, que suele ser el nivel más bajo. De este modo, los hechos se pueden agregar (acumular) al nivel más alto de la jerarquía. Los hechos pueden relacionarse con cualquier nivel, que está determinado por el intervalo de agregación de la tabla de hechos. Por ejemplo, la tabla de hechos de ventas podría almacenarse en el nivel de fecha, mientras que la tabla de hechos de objetivo de ventas podría almacenarse en el nivel de trimestre.

Jerarquías desequilibradas

Las jerarquías desequilibradas son un tipo menos habitual de jerarquía. Las jerarquías desequilibradas tienen niveles basados en una relación de elementos primarios y secundarios. Por este motivo, el número de niveles de una jerarquía desequilibrada viene determinado por las filas de dimensión y no por las columnas de tabla de dimensiones específica.

El típico ejemplo de jerarquía desequilibrada es una jerarquía de empleados donde cada fila de una dimensión de empleado está relacionada con una fila de administrador de informes en la misma tabla. En este caso, cualquier empleado puede ser un administrador con empleados a su cargo. Naturalmente, algunas ramificaciones de la jerarquía tendrán más niveles que otras.

En el diagrama siguiente se muestra una jerarquía desequilibrada. Consta de cuatro niveles y cada miembro de la jerarquía es un comercial. Tenga en cuenta que los comerciales tienen un número diferente de antecesores en la jerarquía en función de quién sea su superior.

Otros ejemplos comunes de jerarquías desequilibradas son: la lista de materiales, los modelos de propiedad de la empresa y el libro de contabilidad general.

Para las jerarquías desequilibradas, los hechos siempre se relacionan con el intervalo de agregación de la dimensión. Por ejemplo, los hechos de ventas se relacionan con diferentes comerciales, que tienen diferentes estructuras de informes. La tabla de dimensiones tendría una clave suplente (denominada Salesperson_SK) y una columna de clave externa ReportsTo_Salesperson_FK, que hace referencia a la columna de clave principal. Los comerciales sin nadie a su cargo no se encuentran necesariamente en el nivel más bajo de las ramificaciones de la jerarquía. Si no está en el nivel más bajo, el comercial podría vender productos y tener comerciales a su cargo que también venden productos. Por lo tanto, la acumulación de los datos de hechos debe tener en cuenta al comercial y a todos sus descendientes.

Consultar jerarquías de elementos primarios y secundarios puede ser complejo y lento, especialmente en grandes dimensiones. Aunque el sistema de origen puede almacenar las relaciones como elementos primarios y secundarios, se recomienda naturalizar la jerarquía. En este caso, naturalizar significa transformar y almacenar los niveles de jerarquía en la dimensión como columnas.

Sugerencia

Si decide no naturalizar la jerarquía, puede crear una jerarquía basada en una relación de elementos primarios y secundarios en un modelo semántico de Power BI. Sin embargo, no se recomienda este enfoque para dimensiones grandes. Para más información, consulte Descripción de las funciones para jerarquías de elementos primarios y secundarios en DAX.

Jerarquías desiguales

A veces, una jerarquía es desigual porque el elemento primario de un miembro de la jerarquía existe en un nivel que no está inmediatamente por encima de él. En estos casos, los valores de nivel que faltan repiten el valor del elemento primario.

Tomemos como ejemplo una jerarquía equilibrada de geografía. Existirá una jerarquía desigual cuando un país o región no tenga estados o provincias. Por ejemplo, Nueva Zelanda no tiene estados ni provincias. Por lo tanto, al insertar la fila de Nueva Zelanda, también tendrá que almacenar el valor de país o región en la columna StateProvince.

En el diagrama siguiente se muestra una jerarquía desigual de regiones geográficas.

Administrar cambios históricos

Cuando sea necesario, los cambios históricos se puede administrar mediante la implementación de una dimensión de variación lenta (SCD, por sus siglas en inglés). La SCD mantiene el contexto histórico a medida que se cargan datos nuevos o modificados.

A continuación, se describen los tipos de SCD más comunes.

• Tipo 1: sobrescribir el miembro de dimensión existente.
• Tipo 2: insertar un nuevo miembro de dimensión con versiones basadas en tiempo.
• Tipo 3: realizar un seguimiento del historial limitado con atributos.

Es posible que una dimensión admita cambios de tipo SCD 1 y SCD 2.

El tipo de SCD 3 no se suele usar, en parte debido al hecho de que es difícil de usar en un modelo semántico. Sopese atentamente si sería mejor opción un enfoque de SCD de tipo 2.

Sugerencia

Si prevé una dimensión de variación rápida, que es una dimensión con un atributo que cambia con frecuencia, en su lugar considere la posibilidad de añadir ese atributo a la tabla de hechos. Si el atributo es numérico, como el precio de un producto, puede agregarlo como medida a la tabla de hechos. Si el atributo es un valor de texto, puede crear una dimensión basada en todos los valores de texto y agregar su clave de dimensión a la tabla de hechos.

SCD de tipo 1

Los cambios de SCD de tipo 1 sobrescriben la fila de dimensión existente porque no es necesario realizar un seguimiento de los cambios. Este tipo de SCD también se puede usar para corregir errores. Es un tipo habitual de SCD y se usa para la mayoría de atributos cambiantes, como el nombre del cliente, la dirección de correo electrónico, etc.

En el diagrama siguiente se muestra el estado anterior y posterior de un miembro de dimensión de comercial cuyo número de teléfono ha cambiado.

Este tipo SCD no conserva la perspectiva histórica porque se actualiza la fila existente. Esto significa que los cambios de SCD de tipo 1 pueden dar lugar a diferentes agregaciones de nivel superior. Por ejemplo, si un comercial está asignado a una región de ventas diferente, un cambio de SCD de tipo 1 sobrescribiría la fila de dimensión. La acumulación de resultados de ventas históricos del comercial en la región generaría un resultado diferente porque ahora usa la nueva región de ventas actual. Es como si ese comercial estuviese siempre asignado a la nueva región de ventas.

SCD de tipo 2

Los cambios de SCD de tipo 2 generan nuevas filas que representan una versión basada en el tiempo de un miembro de dimensión. Siempre hay una fila de versión actual y refleja el estado del miembro de dimensión en el sistema de origen. Los atributos de seguimiento histórico de la tabla de dimensiones almacenan valores que permiten identificar la versión actual (la marca actual es TRUE) y su período de tiempo de validez. Se requiere una clave suplente porque habrá claves naturales duplicadas cuando se almacenen varias versiones.

Es un tipo común de SCD, pero debe reservarse para los atributos que necesiten conservar la perspectiva histórica.

Por ejemplo, si un comercial está asignado a una región de ventas diferente, un cambio de SCD de tip 2 implica una operación de actualización y una operación de inserción.

1. La operación de actualización sobrescribe la versión actual para establecer los atributos de seguimiento histórico. En concreto, la columna de fin de la validez se establece en la fecha de procesamiento de ETL (o una marca de tiempo adecuada en el sistema de origen) y la marca actual se establece en FALSE.
2. La operación de inserción añade una nueva versión actual y establece la columna de inicio de la validez en el valor de la columna de fin de la validez (que se usa para actualizar la versión anterior) y la marca actual en TRUE.

Es importante comprender que la granularidad de las tablas de hechos relacionadas no está en el nivel de comercial, sino en el nivel de versión del comercial. La acumulación de sus resultados históricos de ventas en la región generará resultados correctos, pero habrá dos (o más) versiones de miembro comercial para analizar.

En el diagrama siguiente se muestra el estado anterior y posterior de un miembro de dimensión de comercial para el que la región de ventas ha cambiado. Dado que la organización quiere analizar el esfuerzo de los comerciales según la región a la que están asignados, desencadena un cambio de SCD de tipo 2.

Sugerencia

Cuando una tabla de dimensiones admite cambios de SCD de tipo 2, debe incluir un atributo de etiqueta que describa el miembro y la versión. Tomemos como ejemplo cuando la comercial Lynn Tsoflias de Adventure Works cambia la asignación de la región de ventas de Australia a la región de ventas de Reino Unido. El atributo de etiqueta de la primera versión podría ser "Lynn Tsoflias (Australia)" y el atributo de etiqueta de la nueva versión actual podría ser "Lynn Tsoflias (Reino Unido)." Si resulta útil, también puede incluir las fechas de validez en la etiqueta.

Debe buscar un equilibro entre la necesidad de precisión histórica y la facilidad de uso y eficiencia. Intente evitar tener demasiados cambios de SCD de tipo 2 en una tabla de dimensiones, ya que puede dar lugar a un número abrumador de versiones que podrían dificultar la comprensión de los analistas.

Además, tener demasiadas versiones podrían indicar que quizás sería mejor almacenar el atributo cambiante en la tabla de hechos. Al ampliar el ejemplo anterior, si los cambios en la región de ventas eran frecuentes, la región de ventas podría almacenarse como una clave de dimensión en la tabla de hechos en lugar de implementar una SCD de tipo 2.

Tenga en cuenta los siguientes atributos de seguimiento histórico de SCD de tipo 2.

CREATE TABLE d_Salesperson
(
    <…>

    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,

    <…>
);

Estos son los propósitos de los atributos de seguimiento histórico.

• La columna RecChangeDate_FK almacena la fecha en que entró en vigor el cambio. Permite consultar cuándo se realizaron los cambios.
• Las columnas RecValidFromKey y RecValidToKey almacenan las fechas de entrada en vigor de la validez de la fila. Considere la posibilidad de almacenar la fecha más antigua que se encuentra en la dimensión de fecha de RecValidFromKey para que represente la versión inicial y almacenar 01/01/9999 para la RecValidToKey de las versiones actuales.
• La columna RecReason es opcional. Permite documentar el motivo por el que se insertó la versión. Podría codificar qué atributos han cambiado o podría ser un código del sistema de origen que indique un motivo empresarial determinado.
• La columna RecIsCurrent permite recuperar solo las versiones actuales. Se usa cuando el proceso ETL busca claves de dimensión al cargar las tablas de hechos.

Nota:

Algunos sistemas de origen no almacenan cambios históricos, por lo que es importante que la dimensión se procese periódicamente para detectar cambios e implementar las nuevas versiones. De este modo, puede detectar los cambios poco después de que se produzcan y sus fechas de validez serán precisas.

SCD de tipo 3

Los cambios de SCD de tipo 3 realizan un seguimiento del historial limitado con los atributos. Este enfoque puede ser útil cuando es necesario registrar el último cambio o un número de cambios más recientes.

Este tipo de SCD conserva la perspectiva histórica limitada. Puede ser útil cuando solo se tengan que almacenar los valores iniciales y actuales. En este caso, no se requieren cambios provisionales.

Por ejemplo, si se asigna un comercial a otra región de ventas, un cambio de SCD de tipo 3 sobrescribe la fila de dimensión. La columna que almacena específicamente la región de ventas anterior se establece como la región de ventas anterior y la nueva región de ventas se establece como la región de ventas actual.

En el diagrama siguiente se muestra el estado anterior y posterior de un miembro de dimensión de comercial para el que la región de ventas ha cambiado. Dado que la organización quiere determinar cualquier asignación de región de ventas anterior, desencadena un cambio de SCD de tipo 3.

Miembros de dimensión especiales

Puede insertar filas en una dimensión que represente los estados de error, N/A, desconocido o ausente. Por ejemplo, puede usar los siguientes valores de clave suplente.

Valor de clave	Propósito
0	Ausente (no disponible en el sistema de origen)
-1	Desconocido (error de búsqueda durante la carga de una tabla de hechos)
-2	N/A (no aplicable)
-3	Error

Calendario y hora

Casi sin excepción, las tablas de hechos almacenan medidas en puntos temporales específicos. Para admitir el análisis por fecha (y posiblemente hora), debe haber dimensiones de calendario (fecha y hora).

Es raro que un sistema de origen tenga datos de dimensión de calendario, por lo que debe generarse en el almacenamiento de datos. Normalmente, se genera una vez y, si es una dimensión de calendario, se amplía con fechas futuras cuando sea necesario.

Dimensión de fecha

La dimensión de fecha (o calendario) es la dimensión más común que se usa para el análisis. Almacena una fila por fecha y admite el requisito común de filtrar o agrupar por períodos específicos de fechas, como años, trimestres o meses.

Importante

La dimensión de fecha no debe incluir ningún intervalo de agregación que se amplíe a la hora del día. Si se requiere un análisis de la hora del día, debe tener una dimensión de fecha y una dimensión de tiempo (se describe a continuación). Las tablas de hechos que almacenan los hechos de la hora del día deben tener dos claves externas, una para cada una de estas dimensiones.

La clave natural de la dimensión de fecha debe usar el tipo de datos de fecha. La clave suplente debe almacenar la fecha mediante el formato YYYYMMDD y el tipo de datos int. Esta práctica aceptada debe ser la única excepción (junto con la dimensión de tiempo) cuando el valor de clave suplente tiene significado y es legible por el usuario. Almacenar YYYYMMDD como tipo de datos int no solo es eficiente y se ordena numéricamente, sino que además se ajusta al formato de fecha inequívoco de la Organización Internacional de Normalización (ISO) 8601.

Estos son algunos atributos más comunes que incluir en una dimensión de fecha.

• Year, Quarter, Month, Day
• QuarterNumberInYear, MonthNumberInYear: que pueden ser necesarios para ordenar las etiquetas de texto.
• FiscalYear, FiscalQuarter: algunos calendarios contables corporativos comienzan a mediados de año, por lo que el inicio/fin del año natural y del año fiscal son diferentes.
• FiscalQuarterNumberInYear, FiscalMonthNumberInYear: que pueden ser necesarios para ordenar las etiquetas de texto.
• WeekOfYear: hay varias maneras de etiquetar la semana del año, incluido un estándar ISO que tenga 52 o 53 semanas.
• IsHoliday, HolidayText: si la organización opera en varias zonas geográficas, debe mantener varios conjuntos de listas de los días festivos que se toman en cada zona geográfica como dimensión independiente o naturalizada en varios atributos de la dimensión de fecha. Agregar un atributo HolidayText podría ayudar a identificar los días festivos para los informes.
• IsWeekday: de forma similar, en algunas zonas geográficas, la semana laboral estándar no es de lunes a viernes. Por ejemplo, en muchas regiones de Oriente Próximo, la semana laboral es domingo a jueves, mientras que otras regiones emplean una semana laboral de cuatro o seis días.
• LastDayOfMonth
• RelativeYearOffset, RelativeQuarterOffset, RelativeMonthOffset, RelativeDayOffset: que podrían ser necesarios para admitir el filtrado de fechas relativas (por ejemplo, el mes anterior). Los períodos actuales usan un desplazamiento de cero (0); los períodos anteriores almacenan desplazamientos de -1, -2, -3, etc.; los períodos futuros almacenan desplazamientos de 1, 2, 3, etc.

Al igual que con cualquier dimensión, lo importante es que contiene atributos que admiten los requisitos conocidos de filtrado, agrupación y jerarquía. También puede haber atributos que almacenen traducciones de etiquetas en otros idiomas.

Cuando se usa la dimensión para hacer referencia a hechos más específicos, la tabla de hechos puede usar la primera fecha del período de fechas. Por ejemplo, una tabla de hechos del objetivo de ventas que almacena los objetivos trimestrales de los comerciales almacenaría la primera fecha del trimestre en la dimensión de fecha. Como alternativa, se pueden crear columnas de clave en la tabla de fechas. Por ejemplo, una clave de trimestre podría almacenar la clave de trimestre con el formato YYYYQ y el tipo de datos smallint.

La dimensión debe rellenarse con el intervalo conocido de fechas que usan todas las tablas de hechos. También debe incluir las fechas futuras en las que el almacenamiento de datos almacena datos sobre objetivos, presupuestos o previsiones. Al igual que con otras dimensiones, puede incluir filas que representen situaciones de error, N/A, desconocido o ausente.

Sugerencia

Busque en Internet "generador de dimensiones de fecha" para buscar scripts y hojas de cálculo que generen datos de fecha.

Normalmente, al comienzo del año siguiente, el proceso ETL tiene que ampliar las filas de dimensión de fecha para un número específico de años venideros. Cuando la dimensión incluye atributos de desplazamiento relativo, el proceso ETL debe ejecutarse diariamente para actualizar los valores de atributo de desplazamiento en función de la fecha actual (hoy).

Dimensión de tiempo

A veces, hay que almacenar los hechos en un momento dado (como en la hora del día). En este caso, cree una dimensión de hora (o reloj). Podría tener un intervalo de agregación de minutos (24 x 60 = 1440 filas) o incluso segundos (24 x 60 x 60 = 86 400 filas). Otros posibles intervalos de agregación son media hora u hora.

La clave natural de una dimensión de tiempo debe usar el tipo de datos de tiempo. La clave suplente podría usar un formato adecuado y almacenar valores que tengan significado y que sean legibles por el usuario, por ejemplo, mediante el formato HHMM o HHMMSS.

Estos son algunos atributos comunes que se pueden incluir en una dimensión de tiempo.

• Hour, HalfHour, QuarterHour, Minute
• Etiquetas de período de tiempo (mañana, mediodía, tarde, noche)
• Nombres de turnos de trabajo
• Marcas de hora punta o fuera de hora punta

Dimensiones conformes

Algunas dimensiones pueden ser dimensiones conformes. Las dimensiones conformes están relacionadas con muchas tablas de hechos, por lo que varias estrellas de un modelo dimensional las comparten. Proporcionan coherencia y pueden ayudarle a reducir el desarrollo y el mantenimiento continuos.

Por ejemplo, es habitual que las tablas de hechos almacenen al menos una clave de dimensión de fecha (ya que la actividad casi siempre se registra por fecha u hora). Por ese motivo, las dimensiones de fecha son dimensiones de conformidad habituales. Por lo tanto, debe asegurarse de que la dimensión de fecha incluya atributos relevantes para el análisis de todas las tablas de hechos.

En el diagrama siguiente se muestran las tablas de hechos Sales y Inventory. Cada tabla de hechos se relaciona con las dimensiones Date y Product, que son dimensiones conformes.

Como ejemplo adicional, el empleado y los usuarios podrían ser el mismo conjunto de personas. En este caso, puede tener sentido combinar los atributos de cada entidad para generar una dimensión conforme.

Dimensiones realizadoras de roles

Cuando se hace referencia a una dimensión varias veces en una tabla de hechos, se conoce como dimensión realizadora de roles.

Por ejemplo, cuando una tabla de hechos de ventas tiene claves de dimensión de fecha de pedido, fecha de envío y fecha de entrega, la dimensión de fecha se relaciona de tres maneras. Cada forma representa un rol distinto, pero solo hay una dimensión de fecha física.

En el diagrama siguiente se muestra una tabla de hechos Flight. La dimensión Airport es una dimensión realizadora de roles porque está relacionada dos veces con la tabla de hechos como dimensión Departure Airport y dimensión Arrival Airport.

Dimensiones no deseadas

Las dimensiones no deseadas son útiles cuando hay muchas dimensiones, especialmente si constan de pocos atributos (quizás uno) y si estos atributos tienen una baja cardinalidad (pocos valores). El objetivo de una dimensión no deseada es consolidar muchas dimensiones pequeñas en una única dimensión. Este enfoque de diseño puede reducir el número de dimensiones y disminuir el número de claves de la tabla de hechos y, por tanto, el tamaño del almacenamiento de las tablas de hechos. También ayudan a reducir el desorden del panel de datos porque presentan menos tablas a los usuarios.

Normalmente, una tabla de dimensiones no deseadas almacena el producto cartesiano de todos los valores de atributo de dimensión, con un atributo de clave suplente.

Entre los buenos candidatos se incluyen marcas e indicadores, estado del pedido y estados demográficos del cliente (sexo, grupo de edad y otros).

En el diagrama siguiente se muestra una dimensión no deseada denominada Sales Status que combina valores de estado del pedido y valores de estado de entrega.

Dimensiones degeneradas

Puede producirse una dimensión degenerada cuando la dimensión está en el mismo intervalo de agregación que los hechos relacionados. Un ejemplo común de dimensión degenerada es una dimensión de número de pedido de ventas que se relaciona con una tabla de hechos de ventas. Normalmente, el número de factura es un único atributo no jerárquico de la tabla de hechos. Por lo tanto, es una práctica aceptada no copiar estos datos para crear una tabla de dimensiones independiente.

En el diagrama siguiente se muestra una dimensión Sales Order que es una dimensión degenerada basada en la columna SalesOrderNumber de una tabla de hechos de ventas. Esta dimensión se implementa como una vista que recupera los distintos valores de número de pedido de ventas.

Sugerencia

Es posible crear una vista en Fabric Warehouse que presente la dimensión degenerada como una dimensión con fines de consulta.

Desde una perspectiva de modelado semántico de Power BI, se puede crear una dimensión degenerada como una tabla independiente mediante Power Query. De este modo, el modelo semántico se ajusta al procedimiento recomendado de que los campos usados para filtrar o agrupar se obtengan de tablas de dimensiones y los campos usados para resumir hechos se obtengan de tablas de hechos.

Subdimensiones

Cuando una tabla de dimensiones se relaciona con otras tablas de dimensiones, se conoce como subdimensión. Las subdimensiones pueden ayudar a cumplir y reutilizar definiciones en el modelo dimensional.

Por ejemplo, podría crear una dimensión de geografía que almacene ubicaciones geográficas para cada código postal. Entonces, la dimensión de cliente y la dimensión de comercial podrían hacer referencia a esa dimensión, que almacenaría la clave suplente de la dimensión de geografía. De este modo, podrían analizarse los clientes y los comerciales usando ubicaciones geográficas coherentes.

En el diagrama siguiente se muestra una dimensión Geography que es una subdimensión. No está relacionada directamente con la tabla de hechos Sales, sino que está relacionada indirectamente a través de la dimensión Customer y la dimensión Salesperson.

Tenga en cuenta que la dimensión de fecha se puede usar como subdimensión cuando otros atributos de tabla de dimensiones almacenan fechas. Por ejemplo, la fecha de nacimiento de una dimensión de cliente podría almacenarse mediante la clave suplente de la tabla de dimensiones de fecha.

Dimensiones con varios valores

Cuando un atributo de dimensión tiene que almacenar varios valores, debe diseñar una dimensión con varios valores. Para implementar una dimensión con varios valores, cree una tabla puente (a veces denominada tabla combinada). La tabla puente almacena una relación de varios a varios entre entidades.

Por ejemplo, imagine que hay una dimensión de comercial y que cada comercial está asignado a una o posiblemente más regiones de ventas. En este caso, tiene sentido crear una dimensión de región de ventas. Esa dimensión almacena cada región de ventas solo una vez. Una tabla independiente, conocida como tabla puente, almacena una fila para cada relación de comercial y región de ventas. Físicamente, hay una relación uno a varios de la dimensión comercial a la tabla puente y otra relación uno a varios de la dimensión de región de ventas a la tabla puente. Lógicamente, existe una relación de varios a varios entre el comercial y las regiones de ventas.

En el diagrama siguiente, la tabla de dimensiones Account se relaciona con la tabla de hechos Transaction. Dado que los clientes pueden tener varias cuentas y las cuentas pueden tener varios clientes, la tabla de dimensiones Customer se relaciona a través de la tabla puente Customer Account.

Contenido relacionado

En el siguiente artículo de esta serie, obtendrá información sobre las instrucciones y los procedimientos recomendados de diseño de tablas de hechos.