En ocasiones al construir sistemas de Inteligencia de Negocios nos podemos olvidar de las buenas prácticas sobre nuestros sistemas relacionales en los que nos apoyamos. Sin embargo, esto puede derivar en problemas para nuestros procesos de carga. Para evitarlo es conveniente diseñar correctamente nuestros sistemas a todos los niveles, desde la primera fuente de datos hasta el último reporte que construyamos.
Problemática: No estamos solos en nuestro entorno
Cuando usamos SQL Server Integration Services para construir un Data Warehouse donde tenemos dimensiones que incluyan dimensiones lentamente cambiantes (Slowly Changing Dimensions, a.k.a. SCD) podemos plantearlo de dos maneras:
- Utilizando un componente SQL Command para actualizar miembros de la dimensión. Este componente actúa fila a fila para realizar las sentencias UPDATE necesarias y por lo tanto puede significar un cuello de botella importante en nuestra carga.
- Utilizando una tabla delta o tabla de cambios. En esta tabla guardaremos los cambios necesarios en la dimensión que no sean inserciones (que podemos realizar de forma masiva).
Obviaremos en este post el uso del SQL Command ya que puede sernos útil en casos con dimensiones pequeñas donde no tengamos muchos cambios pero no en situaciones con dimensiones que sufren muchos cambios en cada carga de nuestro DWH.
En el caso de utilizar una tabla delta, la construiremos al leer de nuestra fuente de datos y detectar cambios (normalmente en la clave de negocio de la dimensión). Esto resultará en una necesidad de actualizar el miembro de la dimensión e introducir otro nuevo con una nueva fecha de inicio.
Para agilizar este proceso, podemos incluir un proceso de actualización (sincronización) entre la dimensión y la tabla delta. Mediante una sentencia UPDATE utilizando la campos clave de la dimensión podremos actualizar todos los miembros sobre los que hayamos detectado cambios.
Sin embargo, en entornos de producción una sentencia UPDATE como esta sobre una tabla delta con cientos de miles de cambios y una dimensión con varios millones de miembros puede darnos problemas con nuestro motor relacional. Deberíamos estar actualizando la dimensión basándonos en la tabla tmp.ClienteSCDType1 para un ejemplo real, pero a efectos de sencillez lo dejaremos así, siendo suficiente para ilustrar la problemática.
1: UPDATE tmp.ClienteSCDType1 set Nombre = 'Pepe'
2: where Nombre <> 'Pepe'
Es raro disponer de entornos propios y sin accesos externos para desarrollar. Por eso, debido a los accesos externos que puedan existir, podremos encontrar básicamente dos problemas:
- Ralentización por bloqueos en los procesos que ataquen de la dimensión sobre la que estemos realizando los UPDATES.
- Crecimiento del log de transacciones hasta el final de la sentencia UPDATE. SQL Server mantiene el registro de TODAS las operaciones que se realizan dentro de la misma transacción para ser capaz de realizar un ROLLBACK en caso de error.
Hemos limitado nuestro log de transacciones a 10MB en una base de datos en modo de recuperación simple para simular un entorno en el que hay una gran presión sobre éste y ha crecido tanto que ocupa todo el disco en el que se encuentra (es algo que nos encontramos más frecuentemente de lo que parece en proyectos reales en cliente 🙁 ).
Si lanzamos nuestro paquete SSIS de carga de la dimensión con la anterior sentencia TSQL para actualizar nuestra dimensión, obtenemos un bonito error que nos indica claramente que el log de transacciones está lleno y que la operación no ha podido finalizar con éxito:
Las buenas prácticas salvan el día
Para minimizar esto podemos utilizar un pequeño truco en nuestra sentencia contra el motor relacional:
1: declare @filas bigint
2: set @filas = 1
3:
4: while @filas > 0
5: BEGIN
6:
7: UPDATE TOP(10000) tmp.ClienteSCDType1 set Nombre = 'Pepe'
8: where Nombre <> 'Pepe'
9:
10: set @filas = @@ROWCOUNT
11:
12: END
Al utilizar la cláusula TOP(N_FILAS) y el control de filas afectadas (@@ROWCOUNT) esta sentencia se ejecutará en bloques de tantas filas como valga N_FILAS y no contra TODAS las filas a la vez (en nuestro caso son más de 200.000 en la tabla delta). Así, el log de transacciones sólo almacenará las N_FILAS operaciones de cada iteración y una vez se realiza el COMMIT sobre la transacción abierta el log puede reutilizar el espacio para ir a por las N_FILAS siguientes. En el script tenemos como ejemplo un valor N_FILAS = 10000, pero este valor debe ajustarse a cada escenario concreto, dependiendo del modo de recuperación de la base de datos, la intensidad con la que se esté accediendo a las tablas, etc.
Si ejecutamos nuestro paquete con la sentencia de actualización modificada vemos que acaba correctamente:
Al reducir los picos de operaciones a almacenar dentro de la misma transacción evitamos el llenado del log de transacciones y podemos finalizar la operación de actualización con éxito. Además, en cada bloque estaremos bloqueando las N_FILAS correspondientes y no TODAS sobre la dimensión, minimizando así las ralentizaciones o bloqueos del resto de operaciones que pueda haber atacando a nuestra dimensión (procesos propios, consultas de usuarios, aplicaciones externas, etc.)
Feliz carga de dimensiones 🙂
P.S. Los dos temas mencionados en este post, el llenado del log de transacciones y el bloqueo de las tablas, son mucho más extensos y complejos de lo visto aquí, y pueden llegar a suponer un problema que no sólo se solucione con la aplicación de técnicas como la que hemos mostrado, requiriendo de un análisis más exhaustivo y minucioso. Para más información sobre escalado de bloqueos, podéis echar un vistazo a este artículo del blog del equipo de SQL Server Motor Relacional http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx (aplica a SQL Server 2005 pero cambia muy poco respecto a versiones posteriores).
