¡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!
