SSIS Slowly Changing Dimension y alternativas

En este post hablaremos sobre el diseño de dimensiones y algunas aproximaciones para gestionar su mantenimiento con Integration Services.

En el diseño de una dimensión es necesario clasificar el tipo de cambio que se implementa a cada atributo. aunque en la wikipedia existe una entrada sobre este tema en la que describen hasta seis tipos, nos centraremos en los tres más usuales, :

1. Tipo 0. Este método contempla hacer nada con respecto cambios en el valor original. Ni se actualiza ni se crea histórico, simplemente mantiene el valor del atributo cuando se generó la fila en la tabla de dimensión.
2. Tipo 1. Cuando se detectan cambios en el origen, el valor del atributo se sobrescribe de manera que se pierde el histórico.
   
3. Tipo 2. Este tipo implica crear un registro histórico por cada cambio contemplado en el valor de origen y requiere al menos una clave de negocio o business key o natural key. Cuando se detecte que el valor de alguno de los atributos de tipo 2 se ha modificado, se crea una nueva fila en la tabla de dimensión con los nuevos valores. Para poder determinar que fila es la actual será necesario implementar algún mecanismo, como la evaluación de campos con la fecha de validez del registro o un campo booleano.

Vamos a ver como lo hacemos con campos de fecha:

Para este seguir método se hace necesario utilizar una clave propia de la dimensión, una clave surrogada o surrogate key. Normalmente un campo identity de la tabla .De forma es posible asignar a los nuevos hechos la fila de la dimensión adecuada.

Como se puede apreciar en la imagen de ejemplo, para gestionar la nueva fila se ha actualizado la fila anterior introduciendo un valor en el campo EndDate que contendrá en la que se generó el cambio. Hay que observar que se mantiene la clave de negocio que se utiliza para realizar las comparaciones entre origen y dimensión y la clave surrogada que se utilizará para asignar a los nuevos hechos.

Una vez hecho el repaso general, vamos a ver como resolver el mantenimiento de una tabla de dimensión con los tres tipops de atributos sin utilizar el componente  Slowly Changing Dimension.

¿Y por qué no utilizar el componente SCD incluido en SSIS? Pues sobre todo por deficiencias en rendimiento. Si, es un componente muy cómodo de utilizar y si el número de miembros en la dimensión (filas) es reducido, podremos emplearlo sin que se note demasiado. Básicamente realiza una comparación fila a fila para evaluar los distintos tipos de cambios y posteriormente utiliza un componente OLEDB Command para ejecutar las actualizaciones, también fila a fila. En resumen, si hay muchas filas genera muchas operaciones que realmente no son necesarias o mejor dicho, se pueden optimizar. Puedes utilizar el SQL Profiler para examinar este comportamiento, verás que ejecuta el procedimiento sp_executesql con el la clave de negocio como parámetro.

Siguiendo el ejemplo anterior, vamos a diseñar un paquete mantenga una tabla de dimensión con estos tres tipos de atributos.

Podemos utilizar Lookups o Merge Join como alternativas al SCD.

Lookups

En SQL Server 2008 el componente de búsqueda o Lookup tiene alguna opción de configuración más que la versión 2005 para gestionar las salidas de filas: Redirigir las filas no coincidentes a un salida.

Esto nos facilitará el diseño de nuestro paquete.
Supongamos un diseño básico con un origen de datos. Creamos la columna que establece la fecha de inicio de validez del registro con un componente Columna Derivada. Vamos utilizar la fecha de procesamiento del paquete para la columna StartDate:

Es posible que las reglas de negocio obliguen a utilizar una fecha de validez distinta, por ejemplo la fecha de modificación del registro en el modelo transaccional.
Añadimos un componente de búsqueda y configuramos la conexión para la base de datos dónde tengamos la tabla de dimensión, el datawarehouse o datamart y seleccionamos la opción Usar el resultado de una consulta. Hay que modificar la consulta para obtener sólo las filas válidas de la dimensión:

En la sección columnas, seleccionamos todas la de destino y asignamos un alias a cada una para poder distinguirlas de las columnas de origen:

De esta forma incorporamos al flujo todas las columnas del destino.
Para la filas nuevas, que obtendremos en la salida de filas no coincidentes añadimos un componente OLEDB Destination y las insertamos en la tabla de dimensión:


Ahora nos queda comparar los valores y actuar en función del tipo de cambio por atributo, para lo que conectaremos un componente Conditional Split a la salida de filas coincidentes del Lookup. 
Creamos dos salidas para los tipos de cambio 1 y 2, para lo que comparamos los campos de origen con los de destino. En nuestro ejemplo, el campo Country será de tipo 2 y el campo Phone será de tipo 1. Es importante el orden ya que las filas sólo se evalúan una vez, es decir, si cumple la primera condición será redirigida a la primera salida. Por tanto en primer lugar habrá que comparar los atributos (campos) de tipo 2 que generan una nueva fila con todos los valores nuevos y posteriormente los de tipo 1.

Añadimos una columna derivada para establecer la fecha de fin.Este es el campo que utilizaremos para asignar la fecha de caducidad del registro.

En el flujo de datos, añadimos un componente Multidifusión que conectamos a la salida de la columna derivada.
A una de las salidas de Multidifusión conectamos un Unión de todo. Utilizamos la salida de cambios de tipo 1 del componente División condicional y lo conectamos al Unión de todo.
Y ahora hay que prestar atención: En el Unión de todo configuramos el campo NuevoEndDate de la entrada 1 y lo mapeamos con el campo Destino.EndDate la entrada 2 de datos:

Si hemos seguido las indicaciones, en la entrada 1 de datos tendremos los cambios de tipo 2, por lo que utlizamos la fecha creada para establecer caducidad. En el campo Destin.EndDate también podemos dejar Null.
A la salida del Unión de todo conectamos un nuevo Destino OleDB. Para este destino es necesario crear una tabla con la misma estructura que la de dimensión pero con el campo SurrogateKey sin identity. Esta tabla nos servirá de Stage para poder ejecutar posteriormente una consulta de Update en un Ejecutar SQL en el flujo de control en lugar de un Comando OleDB.
En el mapeo de campos tendrás que tener en cuenta que debes guardar la clave de dimensión para poder ejecutar un update:

Como no hemos dicho nada acerca de los campos Name y Code, mantendremos los valores existentes en la dimensión.
Al final, el diseño del flujo de control tiene que tener un aspecto parecido a este:


Para terminar, añadimos al flujo de control un componente Ejecutar SQL añadimos dos consultas: Un update entre DimClientes y DimClientes_Updates y un truncado de esta última.


Y Tachán, ya tenemos listo un paquete para el mantenimiento de una dimensión con cambios tipo 1 y tipo 2. Se puede complicar más el diseño haciendo updates separados para cada tipo de cambio.
Si la dimensión tiene una cantidad de filas considerable, que no puedan cargarse en el caché del componente Lookup, podemos sustituirlo por un Merge join, solo que habría que utilizar el Conditional Split que tenemos en el flujo para detectar las filas nuevas.

Espero que os sirva, Saludos!

---