Descargue un PDF de este Artículo
Anteriormente hicimos una introducción y hablamos de dos casuísticas muy habituales, como son la carga del Staging Area y del Data Mart. Ahora vamos a continuar con una serie de buenas prácticas y recomendaciones adicionales sobre el uso de algunos de los componentes que se utilizados con mayor frecuencia en la construcción de los procesos ETL.
Seguimos con las Buenas Prácticas
A continuación vamos a ir recopilando una serie de buenas prácticas. La ma-yoría de ellas han sido aplicadas en los ejemplos anteriores.
Vamos a ir comentando los componentes más usados en este tipo de proce-sos, cuál es su función, sus características principales, en qué casos debemos usar-los, y los pros y contras que tiene su uso.
Ordenaciones
El componente Sort es muy útil para resolver ciertos casos, pero tiene el in-conveniente de que hasta que no se han leído todas las filas no pueden ir fluyendo datos por los buffers hacia los siguientes componentes, ya que no podemos tener la certeza de que esa es la ordenación definitiva hasta que haya sido ordenada la últi-ma fila. Este componente puede aumentar el tiempo de ejecución de nuestros pro-cesos, por lo que deberemos evaluar si tenemos otras alternativas, como obtener los datos ya ordenados del origen o del destino, y los pros y contras que esto puede suponer en cada caso concreto.
Evitando joins en el origen
Siguiendo con las pautas marcadas anteriormente, una de las cosas que de-bemos evitar es consumir más recursos de los necesarios, tanto del origen como del destino. Uno de los puntos en los que podemos conseguir estas mejoras es evitando la realización de joins complejas a la hora de obtener los datos. Siempre deberemos plantearnos hacer las SELECTs de las tablas por separado y hacer las joins en el ser-vidor de SSIS, mediante los componentes que nos facilita para ello, que son:
Permite acceder mediante unos datos de entrada a otra tabla, seleccionando la fila coincidente, y pudiendo devolver las columnas seleccionadas. Además permite configurar una caché que facilita el realizar estos procesos en memoria.
Permite combinar dos conjuntos de datos que previamente han sido ordena-dos. Los tipos de combinaciones permitidas son: INNER, LEFT o FULL.
Una de las principales diferencias entre ambos componentes que tendremos que tener en cuenta a la hora de utilizar uno u otro en nuestros diseños son:
• Las posibilidades de caché que nos ofrece el componente Lookup. En cada caso debemos estudiar si nos ofrecerá un mejor rendimiento o no el uso de esta caché.
• La necesidad de que los datos de origen vengan ordenados para el uso de Merge Join, y de que ambos utilicen el mismo collation en di-cha ordenación (si se trata de datos alfanuméricos) para evitar obte-ner resultados inesperados. Mientras que Lookup no necesita que los datos le lleguen ordenados, aunque el que lo estén o no nos puede ayudar a la hora de configurar el uso de la caché.
La decisión de uso de uno u otro componente no es sencilla. Incluso para re-solver un mismo problema, dependiendo del volumen de datos que haya en cada una de las tablas que intervienen, del origen de cada uno de estos datos y de si está ordenado o no, hará que haya diferencias sustanciales de rendimiento según el componente que utilicemos.
Gestión del flujo de datos
Nos permite que una fila vaya a una salida u otra en función del contenido de los datos. Es similar a una instrucción CASE de los lenguajes de programación.
Multicast permite distribuir una entrada en una o más salidas, fluyendo por cada una de estas salidas una copia de los datos. Mientras que Union All permite justo lo contrario, es decir, unir varios de estos buffers de datos en uno sólo.
Transformaciones
En este caso nos vamos a centrar en el componente más completo y más ampliamente utilizado. Aunque hay otros como Data Conversion o Copy Column, prácticamente todas sus funcionalidades están también incluidas en el componente Derived Column.
Permite crear nuevos valores para una columna, aplicando una serie de ex-presiones. Puede utilizar variables, operadores, funciones y columnas de entrada de la transformación. El resultado puede agregarse al flujo de datos como columna nueva o sustituir una de las columnas existentes. Integration Services tiene su propio conjunto de expresiones que pueden ser utilizadas en este componente, y que re-comiendo al lector que estudie y conozca para aprovechar al máximo su uso.
Actualización de datos
Uno de los temas más importantes a tener en cuenta es el de la actualización de datos. Para ello básicamente tenemos dos componentes:
Ejecuta una instrucción SQL por cada fila del flujo de datos. Habitualmente se utiliza para actualización y borrado de filas. Es muy importante tener en cuenta que siempre debe incluirse una condición WHERE en dicha instrucción, para filtrar y hacer que los cambios realizados en la tabla afecten sólo a la fila en curso y no a todas las filas de la tabla.
Permite ejecutar desde un paquete cualquier instrucción SQL, incluida la lla-mada a procedimientos almacenados. Puede contener una sola, o múltiples instruc-ciones. El código escrito tendrá que ir en el dialecto SQL correspondiente al gestor de bases de datos al que nos estamos conectando.
Hay casos, que bien por tratarse de un bajo volumen de datos, o bien por cualquier otro motivo en el que no nos preocupe el rendimiento, podemos optar por soluciones fila a fila. Aunque lo recomendable es hacer, siempre que sea posible, actualizaciones de conjuntos de datos, y no actualizaciones fila a fila. Para ello, la solución pasa por evitar el uso del componente OLE DB Command del DataFlow, y en su lugar, hacer inserciones masivas en una tabla temporal, para que una vez fina-lizada la ejecución del dataFlow, en un siguiente paso se haga una actualización o borrado, mediante el componente del ControlFlow llamado SQL Task, haciendo join entre la tabla que queremos actualizar o borrar y esa tabla temporal que hemos creado en el DataFlow.
SCD (Slowly Changing Dimensions)
Anteriormente hemos citado brevemente este concepto. Ahora, ya centrados en Integration Services, nos queda por comentar que hay un componente en el DataFlow que, a modo de wizard, nos permite ir pasando por una serie de pantallas, configurando para cada una de las columnas qué tipo de cambios debe registrar (SCD tipos 1 y 2), así como el tratamiento de miembros inferidos. Y una vez introdu-cida esta información el componente “explota” generando una serie de cajitas adi-cionales con todos los componentes para hacer la gestión de cambios que hemos definido en él.
Básicamente le encuentro dos inconvenientes, el primero es, que si una vez diseñado el proceso, decido hacer cambios manualmente en alguno de los compo-nentes que ha generado me deja hacerlo sin problemas. Pero si en un futuro necesi-to hacer cualquier modificación que implique volver a usar el wizard, éste eliminará todas las personalizaciones que habíamos realizado, volviendo a “explotar”, elimi-nando todo, y volviéndolo a generar sin tener en cuenta ninguna de las personaliza-ciones que habíamos incluido. Este inconveniente es necesario saberlo, pero no tie-ne gran importancia, porque rara vez personalizamos lo que ha realizado el wizard.
El segundo, que sí que considero muy importante, es que es un componente con un muy mal rendimiento, y no es escalable en absoluto. Su principal problema es que todos los tratamientos los hace fila a fila, y en ningún caso orientado a con-juntos.
Mi recomendación es no utilizarlo, y proceder a diseñar nosotros mismos nuestros propios procesos SCD basados en el resto de componentes de la herra-mienta, haciendo un diseño que vaya orientado a conjuntos.
Otra alternativa es utilizar un componente de terceros. Hay uno del Grupo Kimball que funciona bien, y además, es gratuito. A estos componentes de terceros les veo en general dos inconvenientes: el primero es que si nos encontramos con un bug debemos esperar a que lo solucionen, y aun partiendo de que sea una empresa seria y de que se pondrá en ello, no tendremos una fecha fiable que poder comuni-car a nuestro cliente para la entrega del trabajo. Y, la segunda y principal, que nunca sabremos si en futuras versiones de Integration Services seguirá funcionando el componente o si sacarán nuevas actualizaciones para esas nuevas versiones del producto. Lo que nos deja con la incertidumbre de si en un futuro tendremos que rediseñar y rehacer todo el trabajo para poder migrar a una nueva versión de SQL Server.
Escritura de código VB.NET o C#
Hay dos elementos de Script, uno a nivel del ControlFlow, llamado Script Task, y otro a nivel del DataFlow, llamado Script Component. Ambos se utilizan para incluir código .Net (VB o C#) en los paquetes, y pudiendo interactuar con las variables de dichos paquetes. Dando así una total flexibilidad a la hora de cubrir necesidades que no están incluidas en el resto de componentes y tareas del producto.
Uso de Buffers
Como ya conoce el lector, Integration Services a nivel de DataFlow trabaja con una serie de buffers por los que van fluyendo los datos y pasando por los dife-rentes componente que hay incluidos en él. A continuación vamos a clasificar los componentes en tres tipos, en función de la utilización que hacen de estos buffers:
- Streaming: reutilizan el mismo buffer, la información pasa al siguiente componente a través del mismo buffer. Ejemplos: Data Conversion, De-rived Column, Lookup.
- Bloqueo parcial: copian los datos en un nuevo buffer, pero conforme se van procesando en los componentes van fluyendo dichos datos hacia el siguiente componente. Ejemplos: Pivot, Un-Pivot, Merge, Merge Join, Union All.
- Bloqueo: necesita todas las filas de entrada antes de poder continuar flu-yendo la información. También copian los datos a un nuevo buffer. Ejem-plos: Aggregate, Sort, Row sampling, Fuzzy Grouping.
Es importante tener en cuenta estos detalles para hacer un buen diseño y conocer qué está ocurriendo a nivel de flujo de datos en cada uno de los componen-tes que utilizamos, y así facilitarnos nuestro objetivo de obtener el mejor rendimien-to posible.
Conclusiones
En cualquier proyecto de BI, lo más habitual es que el principal foco de aten-ción sea el Data Warehouse. Debemos optimizar su diseño todo lo posible, para que sea una fuente confiable de información, y responda al mayor número de preguntas de negocio posible. También debemos diseñar nuestros procesos ETL aplicando una serie de buenas prácticas que nos permitan que la información fluya desde el origen a él lo más rápido posible y deje traza de todo lo ocurrido. Estas son las buenas prá
-ticas que me gustaría destacar:
- Disponer de un área de Staging, que nos facilite la trazabilidad de los da-tos y la identificación de los cambios (qué ha cambiado, quién lo ha cam-biado y cuándo han cambiado los datos).
- Intentar disponer de tablas delta siempre que sea posible.
- Hacer los procesos lo más escalables posibles, y con los menores consu-mos de recursos tanto en el origen como en el destino, centrando la car-ga de trabajo en el servidor de Integration Services.
- Evitar actualizaciones fila a fila, e intentar que se hagan orientadas a conjuntos de datos.
- Gestionar adecuadamente el historial de cambios para reflejar la realidad del negocio, y no falsearla manteniendo sólo la última versión de los datos.
- Conocer a fondo cada uno de los componentes de Integration Services, para hacer un uso óptimo de la herramienta, y conseguir así mejorar todo lo posible el rendimiento.
- No olvidar en ningún caso la inclusión de procesos de limpieza y depura-ción de datos.
Sobre el Autor
Salvador Ramos (blog), (twitter) Formador y Mentor de SolidQ en una amplia variedad de aplicaciones de negocio. Especializado en proyectos de Business Intelligence, bases de datos relacionales y diseño de Data Warehouses utilizando Microsoft SQL Server. Siempre interesado en proyectos de migración a SQL Server.