SSIS - Creación de paquetes

Hoy vengo con las pilas cargadas y con una serie de post un
tanto diferente a los que venía escribiendo hasta ahora. Vamos a realizar un
recorrido por SSIS, con el objetivo de ver en profundidad el proceso de ETL y
posteriormente la creación de en un DWH ya con nuestros datos ya tratados, de
manera que puedan responder a nuestro modelo de negocio. SSIS nos permite
unificar, preparar, analizar, aplicar reglas de datos y esto se debe a que, a
la hora de afrontar el reto de realizar nuestro modelo, nos podemos encontrar
con datos en múltiples orígenes de datos ya sean bases de datos, Excel, txt,
Sharepoint…. Esto se debe a la multitud de repositorios de datos que nos
podemos encontrar en el mundo empresarial, por ejemplo: ERP, MES, GMAO, CRM…

Todos estos datos por separado, aportan valor a la
organización, pero juntos sí que son un arma poderosa y para ello vamos a tener
que buscar la forma de alinearlos y poderlos unificar de manera que resulten
datos coherentes, fiables y sobre todo valiosos. Ahora, os preguntaréis, ¿pero
que nos está contando Javi hoy de SSIS si con Power BI puedo hacer todo eso?
Sí, es una solución que es muy válida y hoy en día muy extendida debido a la
facilidad de uso y a la multitud de conectores que dispone Power Query, pero también
tiene sus limitaciones, y aquí es donde entra SSIS que es un sistema más
coorporativo, es decir, más robusto.

En resumen, Power Query es una herramienta fantástica y
súper potente, que la uso a diario y que la veremos en la próxima serie, pero
está más orientada al uso individual, aunque cada vez menos con Power Query
Online y con la incorporación de los Datamart incluidos en las licencias
Premium, pero por ahora y hasta que se lleguen a extender, Power Query es como
dice el refrán: “Yo me lo guiso, yo me lo como”.

Como he comentado, en el mundo real, cada vez hay más
orígenes de datos distintos y nuestro objetivo es integrarlos todos en un
almacen de datos (DWH) en el que estos datos estén consolidados y aporten valor
a la organización. Un esquema completamente estándar, puede ser el que muestro
a continuación dónde existen diferentes orígenes de datos, mediante SSIS los
consolidamos en un DWH y luego posteriormente generamos los correspondientes
datamarts a cada departamento, por ejemplo, compras, recursos humanos, etc… para
graficar esa información en Power BI. Añadir también que en ocasiones entre los
datos de origen y el DWH en algunos casos puede crearse un área de
almacenamiento masivo con el objetivo de no estresar el origen de datos en caso
de ser muy sensible, es decir, consultas sencillas y simples para extraer la
información y luego las transformaciones las realizamos en el DWH.

 

Una vez visto a dónde queremos llegar, vamos a ver el
escenario, a modo de ejemplo, en el que nos encontramos y es el siguiente:

1. Base de datos SQL Server (Contoso)
2. Archivos .xlsx.
3. Archivos .txt.

Una vez estudiados los orígenes, debemos dedicarle tiempo a
responder las preguntas de negocio que queremos resolver, esto parece una tarea
trivial y en muchos casos se pasa por alto, y no, no es una tarea trivial y no
debemos pasarlo por alto ya que esta parte son los cimientos de nuestro modelo.
¿Vivirías tranquilo en una casa con unos cimientos pobres? J Pues lo mismo debemos
aplicar a nuestro análisis.

A modo de guía, estas son unos ejemplos de preguntas que
debemos responder antes de empezar a diseñar el modelo.

1. Qué es lo que queremos analizar
2. Mediante qué lo vamos a querer analizar: identificar las
   dimensiones
3. Qué granularidad debemos tener: identificar si el análisis va
   a ser a nivel de día, semana, mes…
4. Objetivos: ¿están identificados?, ¿son medibles?
5. ….

Me podría extender con un montón de preguntas, ya que me
conocéis, pero no es el momento. Vamos a lo que nos interesa, meternos en
“harina”.

Una vez que hemos obtenido las respuestas a las preguntas,
podemos obtener el esquema de cuales van a ser las dimensiones y cuales los
hechos.

Para el caso que vamos a seguir, este es el esquema:

 

Lo primero que vamos hacer es crear nuestra base de datos, y
para eso abrimos el SQL Server Management Studio ya que lo voy a desarrollar en
modo on-premise, pero todo esto es aplicable a la nube.

Mientras se nos abre SSMS, vamos a crear una carpeta en la
raíz de nuestro directorio C con el nombre que hemos definido
(DWH_losdatoscomoherramienta):

 

 

Le asignamos el nombre que queramos a la misma:

 

Pulsamos en Aceptar, y podemos ver que se nos ha creado
nuestra base de datos:

 

 

 

 

 

También podemos crear la tabla haciendo click derecho sobre
la carpeta denominada Tablas: 

 

 

 

 

Y en la ventana que se nos ha abierto, rellenamos los campos
y le asignamos el nombre que deseemos:


 

 

 

 

 

Una vez ya creada la tabla dimensión de destino, procedemos
a la creación de nuestro paquete de SSIS. Arrancamos Visual Studio y creamos un
nuevo proyecto de Integration Services Project y le asignamos el nombre de
DWH_losdatoscomoherramienta.

 

 

Buena práctica: renombramos el
nombre que Visual Studio le ha asignado al paquete SSIS al que queramos, en
este caso como el paquete va a ser para cargar datos al DWH, le vamos a llamar
CargaDWH.

Lo siguiente que debemos hacer, es crear la conexión para el
paquete que acabamos de crear, este va a utilizar un driver “OLE DB Data
Provider”. Para ello hacemos click con el botón derecho en la superficie vacía
del panel “Connection Managers”.

 

 

En ventana que se nos acaba de abrir, pulsamos sobre Nueva:

 

 

En la ventana que se nos ha desplegado, seleccionamos el
nombre del servidor, el modo de autenticación, el nombre de la base de datos
con la que queremos establecer la conexión: 

 

 

Para verificar que hay conexión, hacemos click en Probar
Conexión para comprobar que la conexión es correcta. Si todo ha ido
correctamente, el mensaje que nos devuelve es el siguiente:

 

 

Validamos todo y vemos que se nos ha creado en panel de
“Connection Managers” la conexión:

 

 

Nota: Mi recomendación es cambiarle el nombre por uno algo
más sencillo pero al gusto del consumidor J.

 

Hacemos lo mismo para generar la conexión a la base de datos
de Contoso siguiendo los pasos anteriores:

 

 

Una vez creadas los paquetes de conexión, tenemos que
convertirlas a conexiones de proyecto ya que cada tabla dimensión y hecho
necesitaran la misma, de esta manera las podemos invocar y no estar creando las
mismas conexiones por cada paquete que generemos. Para eso, hacemos click sobre
las conexiones con el botón derecho y elegimos “Convertir a conexión de
Proyecto”: 

 

Y ahora ya podemos ver que se nos han convertido a
conexiones de proyecto:

 

Ahora, desde el cuadro de herramientas de Visual Studio,
arrastramos la “Tarea Ejecutar SQL” al diseñador del flujo de control.

 

Abrimos la tarea que acabamos de arrastrar y rellenamos los
siguientes campos:

 

Nota: La instrucción
TRUNCATE quita todas las filas de una tabla o las particiones especificadas de
una tabla, sin registrar las eliminaciones individuales de filas. TRUNCATE
TABLE es similar a la instrucción DELETE sin una cláusula WHERE; no obstante,
TRUNCATE TABLE es más rápida y utiliza menos recursos de registros de
transacciones y de sistema.

Aceptamos y renombramos la tarea para que se legible por el
usuario, por ejemplo:

A continuación, arrastramos al diseñador la “Tarea de flujo
de datos”:

 

Y conectamos la tarea anterior a la nueva arrastrando la
flechita:

 

Recordar, hay que renombrar los campos, de esta manera nos será
más fácil la interpretación:

 

 

 

 

 Y configuramos la conexión al origen de la siguiente manera: 

 

 

Una manera de comprobar que la conexión es correcta y que
accedemos a los datos es dándole al botón de Preview y nos realiza una
previsualización:  

 

Acto seguido, vamos a crear el destino, para ello arrastramos
al diseñador desde el cuadro de herramientas el componente “Origen OLE DB” ….
¿Cómo qué origen? Sí has dicho destino… eso es, es para ver que no os habéis
dormido aún J.

 

¿Y qué tenemos que hacer ahora? Pues exactamente lo mismo
que para el componente de origen, pero con la conexión de destino, es decir, Conexión_DWH.

 

 

Una vez comprobado, le damos OK y… ¿qué está pasando? Nos
está mostrando un error… ¿Por qué puede ser?

 

 

 

¿Y por qué es? Es debido a que la columna ProductKey es una
columna de identidad y esta se rellena de manera automática, por lo que en la
pestaña Mappings sobre la columna Input Column de ProductKey seleccionamos la
opción <ignore>:

 

 

Y ahora sí, el error ha desaparecido:

 

Pero aquí nos está faltando una buena práctica, tanto para
nosotros como para la futura persona que lo coja, y es renombrar los
componentes:

 

 

 

Volvemos a la pestaña de control del flujo y arrastramos al
diseñador un contenedor de secuencias e introducimos dentro del mismo los dos
componentes creados anteriormente. 

 

Y si
ejecutamos el paquete que acabamos de crear, vemos que se ejecuta sin errores y
nos ha completado la tabla Dimension.Product de DWH_losdatoscomoherramienta:

 

Y en
la base de datos:

 

Woauh! Se ha traspasado
la información de una base de datos a otra. ¿Pero sabemos cuándo se ha
realizado? Y diréis… Javi, ahora mismo que le has dado tú al botón. Sí, tenéis
razón, pero todos estos procesos, generalmente se programan para que se hagan
de manera automática, por lo que es muy importante saber cuándo se han cargado los
datos en nuestra base de datos. Para saber cuándo se han añadido, debemos
añadir campos de control, para ello entramos en la tarea de “Carga de dimensión”
y arrastramos el componente “Columna derivada”: