Dimensiones en la gestión de datos y almacenamiento de datos contienen relativamente estáticas de datos sobre entidades tales como ubicaciones geográficas, clientes o productos. Los datos capturados por las dimensiones que cambian lentamente (SCD) cambian lentamente pero de manera impredecible, en lugar de de acuerdo con un programa regular. [1]
Algunos escenarios pueden causar problemas de integridad referencial .
Por ejemplo, una base de datos puede contener una tabla de hechos que almacena registros de ventas. Esta tabla de hechos estaría vinculada a las dimensiones mediante claves externas . Una de estas dimensiones puede contener datos sobre los vendedores de la empresa: por ejemplo, las oficinas regionales en las que trabajan. Sin embargo, los vendedores a veces se transfieren de una oficina regional a otra. Para propósitos de informes de ventas históricos, puede ser necesario mantener un registro del hecho de que un vendedor en particular había sido asignado a una oficina regional en particular en una fecha anterior, mientras que ese vendedor ahora está asignado a una oficina regional diferente. [ aclaración necesaria ]
El tratamiento de estos problemas implica metodologías de gestión de SCD denominadas Tipo 0 a 6. Las SCD de tipo 6 también se denominan a veces SCD híbridas.
Tipo 0: conservar el original
Los atributos de dimensión de Tipo 0 nunca cambian y se asignan a atributos que tienen valores duraderos o se describen como 'Originales'. Ejemplos: fecha de nacimiento , puntaje de crédito original . El tipo 0 se aplica a la mayoría de los atributos de dimensión de fecha. [2]
Tipo 1: sobrescribir
Este método sobrescribe los datos antiguos con los nuevos y, por lo tanto, no realiza un seguimiento de los datos históricos.
Ejemplo de tabla de proveedores:
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor |
---|---|---|---|
123 | A B C | Acme Supply Co | California |
En el ejemplo anterior, Supplier_Code es la clave natural y Supplier_Key es una clave sustituta . Técnicamente, la clave sustituta no es necesaria, ya que la fila será única por la clave natural (Supplier_Code).
Si el proveedor traslada la sede a Illinois, el registro se sobrescribirá:
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor |
---|---|---|---|
123 | A B C | Acme Supply Co | ILLINOIS |
La desventaja del método de Tipo 1 es que no hay historial en el almacén de datos. Sin embargo, tiene la ventaja de que es fácil de mantener.
Si se ha calculado una tabla agregada que resume los hechos por estado, será necesario volver a calcularla cuando se cambie el Estado_proveedor. [1]
Tipo 2: agregar una nueva fila
Este método realiza un seguimiento de los datos históricos mediante la creación de varios registros para una clave natural determinada en las tablas dimensionales con claves sustitutas independientes y / o números de versión diferentes. Se conserva un historial ilimitado para cada inserción.
Por ejemplo, si el proveedor se traslada a Illinois, los números de versión se incrementarán secuencialmente:
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor | Versión |
---|---|---|---|---|
123 | A B C | Acme Supply Co | California | 0 |
123 | A B C | Acme Supply Co | ILLINOIS | 1 |
Otro método es agregar columnas de 'fecha de vigencia'.
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor | Fecha de inicio | Fecha final |
---|---|---|---|---|---|
123 | A B C | Acme Supply Co | California | 2000-01-01T00: 00: 00 | 2004-12-22T00: 00: 00 |
123 | A B C | Acme Supply Co | ILLINOIS | 2004-12-22T00: 00: 00 | NULL |
La fecha / hora de inicio de la segunda fila es igual a la fecha / hora de finalización de la fila anterior. El nulo End_Date en la fila dos indica la versión de tupla actual. En su lugar, se puede utilizar una fecha alta sustituta estandarizada (p. Ej., 9999-12-31) como fecha de finalización, de modo que el campo se pueda incluir en un índice y no se requiera la sustitución de un valor nulo al realizar consultas.
Y un tercer método usa una fecha de vigencia y una bandera actual.
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor | Fecha efectiva | Current_Flag |
---|---|---|---|---|---|
123 | A B C | Acme Supply Co | California | 2000-01-01T00: 00: 00 | norte |
123 | A B C | Acme Supply Co | ILLINOIS | 2004-12-22T00: 00: 00 | Y |
El valor Current_Flag de 'Y' indica la versión de tupla actual.
Las transacciones que hacen referencia a una clave sustituta en particular (Supplier_Key) se vinculan permanentemente a los intervalos de tiempo definidos por esa fila de la tabla de dimensiones que cambia lentamente. Una tabla agregada que resume los hechos por estado continúa reflejando el estado histórico, es decir, el estado en el que se encontraba el proveedor en el momento de la transacción; no se necesita ninguna actualización. Para hacer referencia a la entidad a través de la clave natural, es necesario eliminar la restricción única que hace imposible la integridad referencial por DBMS.
Si se realizan cambios retroactivos en el contenido de la dimensión, o si se agregan nuevos atributos a la dimensión (por ejemplo, una columna Sales_Rep) que tienen fechas de vigencia diferentes de las ya definidas, entonces esto puede resultar en que las transacciones existentes deban ser actualizado para reflejar la nueva situación. Esta puede ser una operación de base de datos costosa, por lo que los SCD de tipo 2 no son una buena opción si el modelo dimensional está sujeto a cambios frecuentes. [1]
Tipo 3: agregar nuevo atributo
Este método realiza un seguimiento de los cambios mediante columnas independientes y conserva un historial limitado. El Tipo 3 conserva un historial limitado, ya que está limitado al número de columnas designadas para almacenar datos históricos. La estructura de la tabla original en el Tipo 1 y el Tipo 2 es la misma, pero el Tipo 3 agrega columnas adicionales. En el siguiente ejemplo, se agregó una columna adicional a la tabla para registrar el estado original del proveedor; solo se almacena el historial anterior.
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_de_proveedor_original | Fecha efectiva | Current_Supplier_State |
---|---|---|---|---|---|
123 | A B C | Acme Supply Co | California | 2004-12-22T00: 00: 00 | ILLINOIS |
Este registro contiene una columna para el estado original y el estado actual; no puede rastrear los cambios si el proveedor se muda por segunda vez.
Una variación de esto es crear el campo Previous_Supplier_State en lugar de Original_Supplier_State que rastrearía solo el cambio histórico más reciente. [1]
Tipo 4: agregar tabla de historial
El método de Tipo 4 generalmente se denomina "tablas de historial", donde una tabla mantiene los datos actuales y una tabla adicional se usa para mantener un registro de algunos o todos los cambios. Se hace referencia a ambas claves sustitutas en la tabla de hechos para mejorar el rendimiento de la consulta.
Para el ejemplo anterior, el nombre de la tabla original es Proveedor y la tabla de historial es Proveedor_Historia.
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor |
---|---|---|---|
124 | A B C | Acme & Johnson Supply Co | ILLINOIS |
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor | Fecha de Creación |
---|---|---|---|---|
123 | A B C | Acme Supply Co | California | 2003-06-14T00: 00: 00 |
124 | A B C | Acme & Johnson Supply Co | ILLINOIS | 2004-12-22T00: 00: 00 |
Este método se asemeja al funcionamiento de las tablas de auditoría de la base de datos y de las técnicas de captura de datos modificados .
Tipo 5
La técnica de tipo 5 se basa en la minidimensión de tipo 4 al incrustar una clave de minidimensión de "perfil actual" en la dimensión base que se sobrescribe como un atributo de tipo 1. Este enfoque, llamado tipo 5 porque 4 + 1 es igual a 5, permite acceder a los valores de atributo de minidimensión asignados actualmente junto con los demás de la dimensión base sin vincularlos a través de una tabla de hechos. Lógicamente, normalmente representamos la dimensión base y el estabilizador de perfil de minidimensional actual como una sola tabla en la capa de presentación. Los atributos de los estabilizadores deben tener nombres de columna distintos, como "Nivel de ingresos actual", para diferenciarlos de los atributos de la minidimensión vinculada a la tabla de hechos. El equipo de ETL debe actualizar / sobrescribir la referencia de minidimensión de tipo 1 siempre que la minidimensión actual cambie con el tiempo. Si el enfoque de estabilizadores no ofrece un rendimiento de consulta satisfactorio, entonces los atributos de minidimensiones podrían incorporarse físicamente (y actualizarse) en la dimensión base. [3]
Tipo 6: enfoque combinado
El método de Tipo 6 combina los enfoques de los tipos 1, 2 y 3 (1 + 2 + 3 = 6). Una posible explicación del origen del término fue que fue acuñado por Ralph Kimball durante una conversación con Stephen Pace de Kalido [ cita requerida ] . Ralph Kimball llama a este método "Cambios impredecibles con superposición de una sola versión" en The Data Warehouse Toolkit . [1]
La tabla de proveedores comienza con un registro para nuestro proveedor de ejemplo:
Proveedor_clave | Row_Key | Código de proveedor | Nombre del proveedor | Estado actual | Estado_histórico | Fecha de inicio | Fecha final | Current_Flag |
---|---|---|---|---|---|---|---|---|
123 | 1 | A B C | Acme Supply Co | California | California | 2000-01-01T00: 00: 00 | 9999-12-31T23: 59: 59 | Y |
Current_State y Historical_State son lo mismo. El atributo opcional Current_Flag indica que este es el registro actual o más reciente de este proveedor.
Cuando Acme Supply Company se muda a Illinois, agregamos un nuevo registro, como en el procesamiento de Tipo 2, sin embargo, se incluye una clave de fila para garantizar que tengamos una clave única para cada fila:
Proveedor_clave | Row_Key | Código de proveedor | Nombre del proveedor | Estado actual | Estado_histórico | Fecha de inicio | Fecha final | Current_Flag |
---|---|---|---|---|---|---|---|---|
123 | 1 | A B C | Acme Supply Co | ILLINOIS | California | 2000-01-01T00: 00: 00 | 2004-12-22T00: 00: 00 | norte |
123 | 2 | A B C | Acme Supply Co | ILLINOIS | ILLINOIS | 2004-12-22T00: 00: 00 | 9999-12-31T23: 59: 59 | Y |
Sobrescribimos la información Current_State en el primer registro (Row_Key = 1) con la nueva información, como en el procesamiento Tipo 1. Creamos un nuevo registro para rastrear los cambios, como en el procesamiento de Tipo 2. Y almacenamos el historial en una segunda columna de estado (Historical_State), que incorpora el procesamiento de tipo 3.
Por ejemplo, si el proveedor se reubicara nuevamente, agregaríamos otro registro a la dimensión Proveedor y sobrescribiríamos el contenido de la columna Current_State:
Proveedor_clave | Row_Key | Código de proveedor | Nombre del proveedor | Estado actual | Estado_histórico | Fecha de inicio | Fecha final | Current_Flag |
---|---|---|---|---|---|---|---|---|
123 | 1 | A B C | Acme Supply Co | Nueva York | California | 2000-01-01T00: 00: 00 | 2004-12-22T00: 00: 00 | norte |
123 | 2 | A B C | Acme Supply Co | Nueva York | ILLINOIS | 2004-12-22T00: 00: 00 | 2008-02-04T00: 00: 00 | norte |
123 | 3 | A B C | Acme Supply Co | Nueva York | Nueva York | 2008-02-04T00: 00: 00 | 9999-12-31T23: 59: 59 | Y |
Implementación de hechos tipo 2 / tipo 6
Clave sustituta de tipo 2 con atributo de tipo 3
En muchas implementaciones de SCD Tipo 2 y Tipo 6, la clave sustituta de la dimensión se coloca en la tabla de hechos en lugar de la clave natural cuando los datos de hechos se cargan en el repositorio de datos. [1] La clave sustituta se selecciona para un registro de hechos determinado en función de su fecha de vigencia y Start_Date y End_Date de la tabla de dimensiones. Esto permite que los datos de hechos se unan fácilmente a los datos de dimensión correctos para la fecha de vigencia correspondiente.
Aquí está la tabla de proveedores tal como la creamos anteriormente utilizando la metodología híbrida de tipo 6:
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado actual | Estado_histórico | Fecha de inicio | Fecha final | Current_Flag |
---|---|---|---|---|---|---|---|
123 | A B C | Acme Supply Co | Nueva York | California | 2000-01-01T00: 00: 00 | 2004-12-22T00: 00: 00 | norte |
124 | A B C | Acme Supply Co | Nueva York | ILLINOIS | 2004-12-22T00: 00: 00 | 2008-02-04T00: 00: 00 | norte |
125 | A B C | Acme Supply Co | Nueva York | Nueva York | 2008-02-04T00: 00: 00 | 9999-12-31T23: 59: 59 | Y |
Una vez que la tabla Entrega contiene la clave_proveedor correcta, se puede unir fácilmente a la tabla Proveedor usando esa clave. El siguiente SQL recupera, para cada registro de hechos, el estado actual del proveedor y el estado en el que se encontraba el proveedor en el momento de la entrega:
SELECCIONAR la entrega . costo_envío , proveedor . nombre_proveedor , proveedor . estado_histórico , proveedor . current_state FROM delivery INNER JOIN proveedor EN la entrega . proveedor_clave = proveedor . proveedor_clave ;
Implementación pura de tipo 6
Tener una clave sustituta de tipo 2 para cada intervalo de tiempo puede causar problemas si la dimensión está sujeta a cambios. [1]
Una implementación de tipo 6 pura no usa esto, pero usa una clave sustituta para cada elemento de datos maestros (por ejemplo, cada proveedor único tiene una clave sustituta única).
Esto evita que cualquier cambio en los datos maestros tenga un impacto en los datos de transacción existentes.
También permite más opciones a la hora de consultar las transacciones.
Aquí está la tabla de proveedores que utiliza la metodología pura de Tipo 6:
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor | Fecha de inicio | Fecha final |
---|---|---|---|---|---|
456 | A B C | Acme Supply Co | California | 2000-01-01T00: 00: 00 | 2004-12-22T00: 00: 00 |
456 | A B C | Acme Supply Co | ILLINOIS | 2004-12-22T00: 00: 00 | 2008-02-04T00: 00: 00 |
456 | A B C | Acme Supply Co | Nueva York | 2008-02-04T00: 00: 00 | 9999-12-31T23: 59: 59 |
El siguiente ejemplo muestra cómo se debe ampliar la consulta para garantizar que se recupere un único registro de proveedor para cada transacción.
SELECCIONAR proveedor . proveedor_código , proveedor . provider_state FROM proveedor INNER JOIN delivery ON proveedor . proveedor_clave = entrega . proveedor_clave Y entrega . fecha_envío > = proveedor . fecha_inicio Y entrega . fecha_envío < proveedor . end_date ;
Un registro de hechos con una fecha de vigencia (Delivery_Date) del 9 de agosto de 2001 se vinculará al Supplier_Code de ABC, con un Supplier_State de 'CA'. Un registro de hechos con una fecha de vigencia del 11 de octubre de 2007 también se vinculará al mismo Supplier_Code ABC, pero con un Supplier_State de 'IL'.
Si bien es más complejo, hay una serie de ventajas de este enfoque, que incluyen:
- La integridad referencial por DBMS ahora es posible, pero no se puede usar Supplier_Code como clave externa en la tabla Producto y usar Supplier_Key como clave externa, cada producto está vinculado a un intervalo de tiempo específico.
- Si hay más de una fecha en el hecho (por ejemplo, fecha de pedido, fecha de entrega, fecha de pago de la factura), se puede elegir qué fecha usar para una consulta.
- Puede realizar consultas "como en este momento", "como en el momento de la transacción" o "como en un momento determinado" cambiando la lógica del filtro de fecha.
- No es necesario volver a procesar la tabla de hechos si hay un cambio en la tabla de dimensiones (por ejemplo, agregar campos adicionales retrospectivamente que cambian los intervalos de tiempo, o si se comete un error en las fechas de la tabla de dimensiones, se pueden corregir fácilmente) .
- Puede introducir fechas bitemporales en la tabla de dimensiones.
- Puede unir el hecho a las múltiples versiones de la tabla de dimensiones para permitir reportar la misma información con diferentes fechas de vigencia, en la misma consulta.
El siguiente ejemplo muestra cómo se puede usar una fecha específica como '2012-01-01T00: 00: 00' (que podría ser la fecha y hora actual).
SELECCIONAR proveedor . proveedor_código , proveedor . provider_state FROM proveedor INNER JOIN delivery ON proveedor . proveedor_clave = entrega . proveedor_clave Y proveedor . fecha_inicio <= '2012-01-01T00: 00: 00' Y proveedor . fecha_finalización > '2012-01-01T00: 00: 00' ;
Tanto la clave sustituta como la natural
Una implementación alternativa es colocar tanto la clave sustituta como la clave natural en la tabla de hechos. [4] Esto permite al usuario seleccionar los registros de dimensión apropiados en función de:
- la fecha de vigencia principal en el registro de hechos (arriba),
- la información más reciente o actual,
- cualquier otra fecha asociada con el registro de hechos.
Este método permite enlaces más flexibles a la dimensión, incluso si se ha utilizado el enfoque Tipo 2 en lugar del Tipo 6.
Aquí está la tabla de proveedores, ya que podríamos haberla creado utilizando la metodología de Tipo 2:
Proveedor_clave | Código de proveedor | Nombre del proveedor | Estado_proveedor | Fecha de inicio | Fecha final | Current_Flag |
---|---|---|---|---|---|---|
123 | A B C | Acme Supply Co | California | 2000-01-01T00: 00: 00 | 2004-12-22T00: 00: 00 | norte |
124 | A B C | Acme Supply Co | ILLINOIS | 2004-12-22T00: 00: 00 | 2008-02-04T00: 00: 00 | norte |
125 | A B C | Acme Supply Co | Nueva York | 2008-02-04T00: 00: 00 | 9999-12-31T23: 59: 59 | Y |
El siguiente SQL recupera el Supplier_Name y Supplier_State más recientes para cada registro de hechos:
SELECCIONAR la entrega . costo_envío , proveedor . nombre_proveedor , proveedor . proveedor_estado DESDE la entrega INNER JOIN proveedor EN la entrega . proveedor_código = proveedor . proveedor_código DONDE proveedor . bandera_actual = 'Y' ;
Si hay varias fechas en el registro de hechos, el hecho se puede unir a la dimensión utilizando otra fecha en lugar de la fecha de vigencia principal. Por ejemplo, la tabla Entrega puede tener una fecha de vigencia principal de Delivery_Date, pero también puede tener una Order_Date asociada con cada registro.
El siguiente SQL recupera el Supplier_Name y el Supplier_State correctos para cada registro de hechos según la Order_Date:
SELECCIONAR la entrega . costo_envío , proveedor . nombre_proveedor , proveedor . proveedor_estado DESDE la entrega INNER JOIN proveedor EN la entrega . proveedor_código = proveedor . proveedor_code Y entrega . fecha_pedido > = proveedor . fecha_inicio Y entrega . order_date < proveedor . end_date ;
Algunas precauciones:
- La integridad referencial por DBMS no es posible ya que no existe una clave única para crear la relación.
- Si se establece una relación con el sustituto para resolver el problema anterior, se termina con la entidad vinculada a un intervalo de tiempo específico.
- Si la consulta de combinación no está escrita correctamente, puede devolver filas duplicadas y / o dar respuestas incorrectas.
- Es posible que la comparación de fechas no funcione bien.
- Algunas herramientas de Business Intelligence no manejan bien la generación de combinaciones complejas.
- Los procesos ETL necesarios para crear la tabla de dimensiones deben diseñarse cuidadosamente para garantizar que no haya superposiciones en los períodos de tiempo para cada elemento distinto de datos de referencia.
Combinando tipos
Se pueden aplicar diferentes tipos de SCD a diferentes columnas de una tabla. Por ejemplo, podemos aplicar el Tipo 1 a la columna Nombre_proveedor y el Tipo 2 a la columna Estado_proveedor de la misma tabla.
Ver también
Notas
- ^ a b c d e f g Kimball, Ralph; Ross, Margy. El kit de herramientas de almacenamiento de datos: la guía completa para el modelado dimensional .
- ^ http://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
- ^ https://www.kimballgroup.com/2013/02/design-tip-152-slowly-changing-dimension-types-0-4-5-6-7/
- ^ Ross, Margy; Kimball, Ralph (1 de marzo de 2005). "Las dimensiones que cambian lentamente no siempre son tan fáciles como 1, 2, 3" . Empresa inteligente .
Referencias
- Bruce Ottmann, Chris Angus: Sistema de procesamiento de datos , Oficina de Patentes de EE . UU . , Número de patente 7,003,504 . 21 de febrero de 2006
- Ralph Kimball : Universidad de Kimball: Manejo de reformulaciones arbitrarias de la historia [1] . 9 de diciembre de 2007