Todas estas dimensiones cambiantes (SCDs) pueden complicar el proceso ETL, especialmente para las de tipo histórico (SCD tipo 2) donde se debe mantener un registro histórico de los datos cambiantes. En SSIS por suerte, tenemos un componente para procesar este tipo de dimensiones, incluido en las tareas del flujo de datos: el componente Slowly Changing Dimension Component (componente SCD en adelante).
Sin embargo, uno de los principales problemas que este componente presenta ha sido siempre su rendimiento. Para entender este problema vamos a echar un vistazo a su comportamiento:
Para cada fila de la pipeline de entrada, el componente lanza una consulta SQL para recuperar la fila existente en destino y buscar valores que cambien respecto de los que vienen en la pipeline. Si existe algún valor señalado como no-histórico, el componente simplemente actualizará la fila en destino con los nuevos valores. Si existe algún valor que cambie del tipo histórico, entonces se realiza una actualización del registro de destino existente y se marca como no válido, para después realizar una inserción de una nueva fila con la nueva versión de la fila.
Estamos hablando de una consulta de selección, de una consulta de actualización y de una inserción (ésta sólo para las filas con cambios históricos) para cada una de las filas de destino.
En este artículo describiré las diferentes aproximaciones que podemos tomar para optimizar el rendimiento de nuestros paquetes con procesamiento de dimensiones y así, optimizar la carga de nuestro almacén de datos.
Para ilustrar el ejemplo utilizaré las siguientes bases de datos:
• Una base de datos origen ‘SourceDB’, con una tabla ‘Product’.
• Una base de datos destino ‘DataWarehouseDB’, con una tabla ‘DimProduct’
Type 1 Slowly Changing Dimensions (no históricas)
Cuando trabajamos con dimensiones cambiantes de tipo 1, lo único que debemos hacer es actualizar las filas en las que detectemos algún cambio, sin necesidad de mantener ninguna información histórica. Si detectamos el cambio, la fila se actualizará y perderemos el valor que existía antes.
Usando el componente SCD de SSIS
Podemos construir un flujo de datos que implemente el escenario que hemos descrito con un componente SCD de SSIS. Podemos verlo en la figura 1:
Para cada fila que recupere el componente OLEDB que lee de la tabla ‘Products’ de la base de datos origen, el componente SCD ejecutará una consulta SQL contra la tabla ‘DimProduct’ para ver si ese producto ya existe. En el caso de que no exista redirigirá la fila hacia la salida ‘New Output’ y acabará con una inserción mediante el componente OLEDB de destino .En el caso de que exista, comprobará si la fila leída contiene valores diferentes a los de la fila en la pipeline. Si hay cambios redirigirá la fila hacia el componente comando OLEDB que actualizará la fila en destino con los nuevos valores.
Dado el comportamiento del componente podemos esperar un rendimiento pobre. A continuación veremos cómo podemos optimizar este flujo de datos sin excesiva complejidad añadida.
La solución con dimensiones cambiantes de tipo 1
Afortunadamente, podemos aumentar considerablemente el rendimiento de nuestra pipeline en el flujo de datos, sustituyendo el componente SCD por un componente de Lookup más otro de tipo Split.
Para conseguir esta optimización, borraremos el componente SCD del flujo de datos, dejando los componentes OLEDB generados debajo por el propio SCD. A continuación añadiremos un componente de Lookup para recuperar las filas de DimProduct, tal y como lo haría el componente SCD, es decir, recuperando únicamente las columnas de los valores cambiantes y las que compongan la clave de negocio.
Después añadiremos un componente de tipo Conditional Split. En este componente añadiremos una condición llamada ‘Actualizar’ con todas las comparaciones necesarias entre los valores cambiantes.
El nuevo flujo de datos debería quedar como el siguiente diagrama:
Hay que tener en cuenta que no se puede utilizar una única comparación entre los valores cambiantes para averiguar si ha cambiado, ya que nos pueden llegar valores nulos. Por ejemplo, si queremos hacer la comparación entre el campo ‘ProductCategory’ fuente y destino, la expresión que usaremos será la siguiente:
!((ISNULL(SRCPRODUCTCATEGORY) && ISNULL(DWDIMPRODUCTCATEGORY)) || (!ISNULL(SRCPRODUCTCATEGORY) && !ISNULL(DWDIMPRODUCTCATEGORY) && (SRCPRODUCTCATEGORY == DWDIMPRODUCTCATEGORY)))
Con esta expresión evitaremos que los valores nulos provoquen cambios no deseados, y sólo actualizaremos si el cambio se produce por dos valores diferentes. Podemos repetir esta comparación en la expresión de condición del componente Split.
Lo único que queda es conectar la salida de error del componente Lookup al componente OLEDB de inserción, y la salida de ‘Actualizar’ al componente OLEDB de actualización.
El resultado final en nuestro ejemplo es que el componente Lookup recuperará en una sola operación las filas de destino y las mantendrá en memoria durante la ejecución del flujo de datos. Después de comparar cada fila con el Dataset que mantiene en memoria, realizará de la misma forma la inserción o la actualización en función de si se encuentra la fila en destino o no. La diferencia radica en el número de transacciones, que se reduce enormemente ya que no es necesario recuperar fila por fila para hacer la comparación.
Type 2 SCD (Históricas)
This was the SCD 1 type optimization, but we should use the SCD 2 type every time that a dimension has an historical attribute. If an historical attribute has been changed then rather than just u0pdate the destination row with the new values, the OLEDB component must mark the existing row as out-of-date, and then insert a new row with the new values. For example, if we want to track all the pricing changes in time, we'll have to insert a new row every time that we detect that the prize has been changed in the source system.
Type 2 SCD with the SCD Component
En el caso de que queramos implementar algún campo histórico, deberemos utilizar la aproximación de las dimensiones cambiantes de tipo 2 (SCD 2). En este caso, si un atributo histórico ha cambiado entonces en lugar de tan sólo actualizar la fila de destino con el valor nuevo, el componente deberá marcar la fila actual como inválida, e insertar una nueva fila con los nuevos valores. Por ejemplo si queremos registrar todos los cambios de precio de un producto, deberemos insertar una nueva fila cada vez que detectemos que un producto ha cambiado su precio en la fuente de datos.
Podemos diseñar un flujo de datos con este comportamiento de forma muy sencilla gracias al componente SCD de SSIS. Si seleccionamos el atributo precio como un atributo histórico en el asistente del componente SCD, el flujo de datos resultante debería parecerse al siguiente:
La solución con dimensiones cambiantes de tipo 2
Los pasos para optimizar este componente en este caso son muy similares al proceso que hemos seguido en el caso del SCD de tipo 1. Deberemos borrar el componente SCD una vez completado el asistente, y añadir un componente Lookup que devuelva los registros de la tabla DimProduct y un componente de Split. El componente de Lookup deberá recuperar tanto los atributos cambiantes como los históricos.
Para controlar los dos tipos de cambio utilizaremos el component Split. Tendremos una condición para los campos históricos y otra para los campos cambiantes. El comportamiento esperado será que si existe un campo histórico que ha cambiado no será necesario continuar mirando si existe algún cambio de tipo 1 ya que irá igualmente a la rama del histórico. Para conseguir este comportamiento lo único que tenemos que hacer es colocar la condición del histórico en primer lugar, ya que el componente procesa las condiciones en orden. En la siguiente figura podemos ver cómo queda configurado el componente Split:
Como último paso, conectaremos las salidas convenientes a los componentes que había generado el SCD de SSIS, y el flujo de datos quedará listo. El nuevo flujo deberá tener un aspecto similar a este:
Comparación del rendimiento
Para comparar el rendimiento de los 2 flujos, vamos a ejecutar el paquete sobre la tabla Product de la base de datos AdventureWorks.
En la carga de datos, se obtienen los siguientes tiempos de ejecución:
Primera carga:
- Con componente SCD de SSIS: 73 segundos
- Con Lookup+Split: 65 segundos
Debemos tener en cuenta que los tiempos de validación del paquete son idénticos para ambos flujos, de ahí la escasa diferencia entre ambas ejecuciones.
Ahora cambiaremos algunos atributos en la categoría y precio de algunos productos de la tabla Product. El siguiente script se encargará de ello:
Los tiempos del proceso de actualización son:
- Con componente SCD de SSIS: 74 seconds
- Con Lookup+Split: 65 seconds
Los resultados no son tan apasionantes como esperábamos, pero debemos tener en cuenta que una dimensión de 500 filas está muy lejos de ser apasionante. Simularemos una dimensión de 100.000 filas cambiando la sentencia SQL de origen por la siguiente:
SELECT PRODUCTID, NAME, '1'+ PRODUCTNUMBER AS PRODUCTNUMBER, LISTPRICE, PRODUCTSUBCATEGORYID, MODIFIEDDATE FROM PRODUCTION.PRODUCT UNION ALL
SELECT PRODUCTID, NAME, '2'+ PRODUCTNUMBER AS PRODUCTNUMBER, LISTPRICE, PRODUCTSUBCATEGORYID, MODIFIEDDATE FROM PRODUCTION.PRODUCT UNION ALL
[...]
SELECT PRODUCTID, NAME, '200'+ PRODUCTNUMBER AS PRODUCTNUMBER, LISTPRICE, PRODUCTSUBCATEGORYID, MODIFIEDDATE FROM PRODUCTION.PRODUCT
Después de truncar la tabla DimProduct y repetir los tests de nuevo. Los resultados los podemos ver a continuación:
Primera carga:
- Con componente de SCD de SSIS: 11 minutos 8 segundos
- Con Lookup+Split: 2 minutos 10 segundos
Proceso de actualización:
- Con componente de SCD de SSIS: 178 minutos 05 segundos
- Con Lookup+Split: 121 minutos 23 minutos
Los resultados son mucho más significantes, y ahora sí que podemos ver la enorme diferencia entre un diseño y otro. Debemos tener en cuenta además que si tuviéramos realmente una dimensión de 100.000 filas el servidor debería mover muchas más páginas de disco y los resultados seguramente serían más significativos todavía.
Hay que fijarse en la diferencia abismal entre la primera carga y la actualización posterior. Esto es debido básicamente a la diferencia de coste que tienen ambas instrucciones para el servidor SQL Server. Realizar una actualización requiere localizar la página de disco donde se encuentra ubicada la fila, modificar su valor y escribir de nuevo la página.
Con esta premisa sería posible crear una optimización adicional, consistente en modificar el componente OLEDB de actualización y combinar un componente OLEDB de inserción en una tabla temporal con una instrucción JOINED UPDATE en otro componente OLEDB de actualización. Como ejercicio individual, os dejo que probéis los beneficios de esta aproximación.
Conclusión
Podemos conseguir una importante mejora del rendimiento en paquetes de procesamiento de dimensiones si implementamos estas optimizaciones en nuestros flujos de datos. El componente SCD de SSIS realmente supone una sobrecarga de trabajo para el proceso que puede ser mejorada de forma sencilla y rápida.
Por el contrario es justo destacar que esta mejora conlleva un aumento de la complejidad del paquete, y por tanto perjudica su desarrollo y mantenimiento.
Posts
