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:
- Base de datos SQL Server (Contoso)
- Archivos .xlsx.
- 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.
- Qué es lo que queremos analizar
- Mediante qué lo vamos a querer analizar: identificar las dimensiones
- Qué granularidad debemos tener: identificar si el análisis va a ser a nivel de día, semana, mes…
- Objetivos: ¿están identificados?, ¿son medibles?
- ….
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:
Una vez creado nuestro DWH, vamos a crear nuestra tabla de dimensión de Producto que será la que usemos para cargar los datos desde el origen. Lo primero que hacemos es crear un esquema dentro de la base de datos para las dimensiones que vamos a crear y, a continuación, creamos la tabla de dimensión para la tabla Producto. Para ello ejecutamos el siguiente comando T-SQL en la que definimos el tipo de dato, su longitud y si puede ser nulo o no:
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:
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:
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”:
Rompemos la conexión de los componentes carga y destino y conectamos entre medias el componente “Columna derivada” y configuramos el nombre de la columna y el comando GETDATE() que nos devuelve la fecha y hora:
¿Y lo podemos ejecutar tal cual? .... No, no tiene columna asociada en la tabla del DWH, por lo que debemos asignársela. Para ello creamos la columna en la tabla de DWH_losdatoscomoherramienta y la mapeamos y volvemos a ejecutar el paquete y vemos que se nos ha creado la columna FechaInsercion y se nos ha rellenado:
Bueno y por hoy ya vale en cuanto a SSIS, que para ser el primer post de la serie… os he soltado un ladrillo J.
¡Nos vemos en los datos!
