¡Hoy vengo con las pilas cargadas después de ver que el blog ha tenido más de 3000 visitas! ¡Muchas gracias a tod@s!
Seguimos con la serie de SSIS, esta vez vamos a ver cómo hacer una carga de dimensiones de variación lenta o (SCD: Slowly Changing Dimensions) de manera que no perdamos la traza de la carga de datos en nuestro DWH. Os estaréis preguntado… ¿pero realmente es necesario todo esto si con Power Query puedo hacer un millón de cosas? Mi respuesta es sí, que no está demás conocer este prisma de ETL y os voy a decir el por qué, cuando estamos trabajando con Power Query, las buenas prácticas dicen que debemos mantener el Query Folding o el plegado de consultas lo máximo posible de cara a que toda o la mayor parte de la carga en el proceso de transformación se haga en origen y no en destino. A modo ilustrativo:
Nota: El plegado de consultas es la capacidad de una consulta de Power Query para generar una única instrucción de consulta para recuperar y transformar los datos de origen. El motor de mashup de Power Query se esfuerza por lograr el plegado de consultas siempre que sea posible por motivos de eficiencia.
Antes de empezar, vamos a comentar que los cambios soportados por SSIS son 3, y son:
Tipo 0: No hay cambios.
Tipo 1: Sobrescribe el valor. ¿Qué significa? Veámoslo con un ejemplo:
En la imagen superior, en la tabla de dimensión de la izquierda (Origen) el atributo para el producto A es Coche, mientras que, en la tabla de Destino, el atributo es Bici. Esto es que, en algún momento, este se ha modificado, pero no se ha guardado registro del cambio y se sobrescribe directamente el valor.
Tipo 2: Se añade un nuevo registro y se mantiene el histórico.
En este caso, se realiza una modificación en la tabla de dimensión, pero se quiere mantener el histórico. Como se puede ver, el cambio de la categoría en la tabla de dimensión da como resultado una nueva tabla en la cual se lleva el registro de las modificaciones mediante las columnas de FechaInicio y FechaFin en las que los atributos están activos y cuando no.
De primeras, vamos a ver cómo hacerlo de manera automática, con componente de “Dimensión de variación lenta” que por defecto traer Visual Studio, pero no es la opción óptima que veremos en otro momento.
Vamos a Visual Studio y creamos un nuevo paquete SSIS:
necesitamos cargar la dimensión al modelo, para ello arrastramos el componente “Tarea Flujo de Datos” al diseñador y le conectamos el componente de la tarea anterior.
Entramos dentro del componente de flujo de datos, y le tenemos que indicar el origen de los datos, para ello arrastramos al diseñador de flujo de datos el componente “Origen de OLE DB” y lo configuramos de la siguiente manera:
Y como sentencia SQL escribimos lo siguiente y pulsamos sobre el botón Preview para visualizar los datos de nuestra sentencia:
Una vez que vemos que nuestra sentencia SQL es correcta y nos devuelve los dato que necestiamos (menos mal ¿eh? J ) vamos a la pestaña de Columns para revisar que todas las columnas están marcadas con el check y pulsamos en Aceptar y cambiamos el nombre del componente (que no se nos olvide).
Ahora arrastramos al diseñador de flujo el componente “Dimensión de variación lenta” y lo conectamos con nuestro paso anterior.
Ahora, le damos dos clicks para entrar en su configuración y Visual Studio nos abre una ventana con Wizard:
Le damos a Next y se nos despliega la siguiente ventana, en la que debemos configurar la conexión y la tabla o vista que queremos tratar:
Y como podemos ver, en la parte inferior se nos muestra los campos de la tabla que queremos tratar, es decir, realizamos el mapeo de los campos de origen con los campos de destino. En este paso, no es necesario seleccionar todos los campos. Lo único que tenemos que definir es la clave identificadora del negocio para esta tabla, que en este caso es “ProductSubCategoryKey” y la marcamos como Business Key:
Una vez seleccionada la Business Key para la tabla, pulsamos en Next y se nos abre la siguiente ventana:
En ella, seleccionamos dentro de las columnas que tenemos, las columnas con que queremos trabajar y qué tipo de cambio va a tener esa columna, es decir, tipo 0, tipo 1 o tipo 2. Para ello, cargamos todas las columnas y vamos seleccionando el tipo de cambio:
Como hemos configurado, para la descripción de la categoría hemos seleccionado atributo cambiante (tipo 1), para el título y SubCategoryKey hemos seleccionado atributo fijo (tipo 0) y para el nombre hemos seleccionado atributo histórico (tipo 2).
Nota: esto es a modo de ejemplo, una aplicación práctica de atributo histórico puede ser el histórico de precios de un artículo.
Pulsamos en Next y se nos abre la venta en la que podemos seleccionar que hacer con los atributos fijos y cambiantes,
Desmarco la primera opción que nos indica que falle la transformación si se detectan cambios en un atributo fijo.
Y marco la segunda opción para que cambie todos los registros coincidentes, incluidos los registros obsoletos, cuando se detecten cambios en un atributo cambiante. Es decir, si yo tengo más de una versión de mi producto para aquellos que tengo definidos como cambiantes se aplicará sobre todas las versiones.
Una vez marcados y/o desmarcados los checks, pulsamos en Next y se vamos a la siguiente pantalla:
Para los atributos históricos, en esta pantalla que se nos ha abierto podemos seleccionar dos opciones, en la primera podemos seleccionar que columna queremos utilizar para definir la versión actual del registro o bien usar fecha de inicio y fecha fin para identificar las fechas de validez y expiración de las versiones.
Marcamos la segunda opción y seleccionamos las columnas que queremos usar como Start Date y End Date:
Y le indicamos la varible StartTime, con esta configuración le estamos indicando que cuando genere las nuevas versiones, coja estos campos como el período de validez de esa versión. Luego, en nuestra tabla de hechos, cuando carguemos los datos, tendremos que tener en cuenta para cargar los hechos, que el lookup por el que voy a obtener la clave subrogada, sea la business key sea igual y además la fecha del registro este entre el periodo del Start date y End Date. ¿Y para qué os estaréis preguntado? Pues por ejemplo para verificar que el producto que he vendido se corresponde con el producto activo. Es una doble condición. ;) Pulsamos en Next:
Y deshabilitamos la opción de miembros inferidos (que ya hablaré de ellos en otro escenario) y pulsamos en Next y llegamos a la pantalla resumen. Le damos Finish:
Y después de unos pocos segundos… Visual Studio nos ha generado el siguiente flujo:
Si analizamos lo que nos ha realizado SSIS de manera automática, vamos observamos tres flujos distintos que son:
En rojo, la salida de los componentes que son históricos.
En amarillo los componentes que son nuevos
Y en verde las actualizaciones de los atributos variables.
Podría entrar a analizar cada componente, pero estaríamos aquí hasta final de año y no es plan… ¿no creéis?
Por otro lado, vaya retórica que os he pegado hasta aquí, ahora os voy a contar algo que no me gusta de esto. Lo siento, ha llegado mis 30 segundos de pataleta y me toca J. Si hacemos doble click en el componente Insert Destination se nos abre la siguiente ventana:
Y como podéis ver, no usa Fast Load…. Y si lo ponemos, nos podemos encontrar con bloqueos en la base de datos y eso es otro cantar…
Ahora ya que he terminado mi pataleta J, ejecutamos una consulta a la tabla de Contoso ProductSubCategory para ver la tabla:
Y vemos que nos devuelve 44 registros. Ahora vamos a SSIS y ejecutamos el paquete que se nos ha creado:
Y como podemos ver se nos ha ejecutado correctamente y el número total de filas insertadas son 44. ¡Eureka! Las mismas que en la tabla de origen J. Lo comprobamos en SSMS:
Como podemos ver, la nueva tabla creada tiene las dos columnas de fechas hemos creado y la columna Inicio tiene la fecha de validez. Vamos a modificar el campo ProductSubcategoruLabel de un producto en la tabla origen para validar el paquete. Para ello, ejecutamos la siguiente sentencia:
Y ejecutamos de nuevo el paquete SSIS:
Y podemos observar que ahora que ha sido insertada una fila por el lado del flujo “Salida de inserciones de atributos históricos”. Volvemos a SSMS y ejecutamos la consulta a la tabla Dimension.ProductSubCategorySCD, filtrando el ProductSubCategoryKey a 1:
Y nos devuelve 2 filas:
Una con la fecha de inicio y fin completada y la otra con sólo la fecha de inicio completada, por lo que ya tenemos nuestro SCD de tipo 2 configurado y funcionando.
¡Nos vemos en los datos!
