Capítulo 8 SSIS (SQL Server Integration Services)

SSIS es una herramienta ETL integrada en SQL Server, el sistema de gestión de bases de datos profesional de Microsoft. Trabajaremos sobre la versión 2019.

Usando esta herramienta, a partir de los datos en formato CSV, generaremos las tablas de dimensiones y hechos en una BD SQL Server.

Objetivos del capítulo

  • Usar una herramienta ETL profesional.

  • Conocer el funcionamiento de SSIS y usarlo para:

    • Realizar operaciones de importación de datos.

    • Transformar los datos.

    • Generar nuevas tablas.

    • Obtener un diseño en estrella.

  • Entender la estructura y la implementación de los cubos OLAP.

8.1 Herramientas de soporte

Para trabajar con SQL Server en este contexto usaremos principalmente dos herramientas de soporte:

  • SSMS (SQL Server Management Studio): es una aplicación que permite administrar los servidores de BD que gestionamos, por ejemplo: Database Engine, el motor de la BD relacional; Analysis Services, el componente multidimensional; Integration Services, el componente ETL.

  • Visual Studio 201926: permite desarrollar proyectos para los distintos componentes de SQL Server, en particular, en esta actividad nos interesará desarrollar paquetes de SSIS, el componente ETL.

Vamos a comenzar, por un lado, con SSMS creando la BD SQL Server de trabajo donde generaremos las tablas de hechos y dimensiones; por otro, creando un proyecto de tipo SSIS en Visual Studio.

Para ampliar información sobre estas herramientas una buena fuente es el libro de Knight (2012), en particular, lo relativo al despliegue de los paquetes (no tratado en esta actividad) está incluido en tres capítulos de su sección 7 (Configuring Packages). El libro está basado en la versión 2012 pero, en los temas tratados aquí, no hay diferencia sustancial con versiones posteriores.

8.1.1 Crear una base de datos SQL Server

En primer lugar, vamos a crear una BD SQL Server mediante SSMS. Al iniciar SSMS aparece la ventana de conexión con el servidor de la figura 8.1.

Pantalla de inicio de SSMS.

Figura 8.1: Pantalla de inicio de SSMS.

Permite conectarse a los distintos tipos de servidores integrados en SQL Server. En particular, en el campo Server type debemos seleccionar Database Engine, en el resto de campos podemos seleccionar el nombre del servidor a conectarnos y el tipo de identificación que vamos a usar. En este caso, podemos dejar los valores por defecto y pulsar sobre Connect.

Al conectarnos al servidor, nos muestra los objetos del mismo que podemos gestionar (figura 8.2). En particular, nos interesan las BD (Databases).

Objetos del servidor.

Figura 8.2: Objetos del servidor.

Para crear una nueva BD, en el menú contextual del grupo de objetos Databases (figura 8.3), seleccionamos la opción New Database.

Nueva base de datos.

Figura 8.3: Nueva base de datos.

La ventana de definición de la BD se muestra en la figura 8.4. En ella, el único parámetro que tenemos que definir es el nombre de la BD (Database name) y pulsamos sobre OK.

Parámetros de la nueva base de datos.

Figura 8.4: Parámetros de la nueva base de datos.

En este caso el criterio que vamos a seguir es:

  • Asignar a la BD el nombre de la provincia y el usuario (p.e., en mi caso granada-jsamos).

Una vez creada, podemos verla como un objeto más en SSMS, como se muestra en la figura 8.5.

Nueva base de datos creada.

Figura 8.5: Nueva base de datos creada.

8.1.2 Crear un proyecto SSIS

Para crear un proyecto SSIS necesitamos abrir Visual Studio. En la figura 8.6 se muestra la pantalla de inicio.

Pantalla de inicio de Visual Studio.

Figura 8.6: Pantalla de inicio de Visual Studio.

Al pulsar sobre Crear un proyecto, podemos elegir la plantilla del tipo de proyecto que vamos a crear (figura 8.7). Seleccionamos el tipo Integration Services Project, y pulsamos sobre Siguiente.

Elección de la plantilla del tipo de proyecto.

Figura 8.7: Elección de la plantilla del tipo de proyecto.

Nombre y carpeta del proyecto.

Figura 8.8: Nombre y carpeta del proyecto.

A continuación (figura 8.8), podemos introducir el nombre del proyecto, la carpeta donde se almacenará y el nombre de la solución (solo si desmarcamos la opción para situarlos en distintas carpetas). Podemos tener varios proyectos asociados al mismo tema de trabajo, en este caso proyectos SSIS. Los proyectos se pueden agrupar bajo el concepto de solución, que se definiría aquí. En este caso, no es necesario, podemos dejar la solución y el proyecto en la misma carpeta.

En este caso el criterio que vamos a seguir es:

  • Asignar al proyecto el nombre de la provincia, el usuario, el tema y la herramienta que vamos a usar (p.e., en mi caso granada-jsamos-etl-ssis).

En la figura 8.9, se muestra la ventana de inicio del nuevo proyecto.

Inicio del proyecto.

Figura 8.9: Inicio del proyecto.

En SSIS las transformaciones se estructuran en forma de paquetes (packages). Un paquete es el equivalente a un programa ejecutable. En la parte izquierda de la ventana se muestran los elementos que se pueden añadir a un paquete (se adaptan según la parte que estemos definiendo), en la parte principal (parte superior central) se estructuran y relacionan los elementos seleccionados, la parte inferior central está dedicada a gestionar las conexiones de datos para acceder a las fuentes o destinos de los datos y, en la parte derecha están, entre otros componentes, el Explorador de soluciones, y la ventana Propiedades donde se pueden configurar las propiedades de los elementos que se definen.

8.2 Importar datos a una BD

SQL Server incluye una herramienta para la importación de datos a partir de diversas fuentes. Vamos a importar los datos de nuestra provincia para tenerlos disponibles en forma de tabla en la BD que hemos creado.

En primer lugar, definiremos el proceso de importación como un paquete, después lo incorporaremos a nuestro proyecto y lo ejecutaremos.

8.2.1 Crear el paquete de importación

Desde el menú contextual de nuestra BD en SSMS, pulsamos sobre Tasks > Import Data (figura 8.10).

Importar datos a una BD.

Figura 8.10: Importar datos a una BD.

Se inicia SQL Server Import and Export Wizard, que nos explica en la pantalla inicial que permite crear paquetes de importación y exportación de datos. Pulsando sobre Next, accedemos a la pantalla de definición de la fuentes de datos (figura 8.11)

Definición de la fuente de datos.

Figura 8.11: Definición de la fuente de datos.

En el campo Data source seleccionamos Flat File Source y, en el campo File name, pulsando el botón Browse, seleccionamos el archivo con los datos de nuestra provincia. El valor del resto de campos lo toma del archivo y son válidos los valores obtenidos. Observamos que, al pie de la ventana, aparece un mensaje de aviso respecto a la definición de las columnas, que todavía no se ha hecho Para realizarla, pulsamos sobre Columns en la columna de la izquierda de la ventana. Accedemos a la pantalla de la figura 8.12.

Definición de las columnas de la fuente de datos.

Figura 8.12: Definición de las columnas de la fuente de datos.

La definición de las columnas también es correcta, solo nos obliga a revisarla, por lo que podemos pulsar sobre el botón Next.

En el apartado siguiente, indicamos el destino de los datos (figura 8.13). En el campo Destination mantenemos el valor SQL Server Native Client 11.0, seleccionamos el nombre del servidor, también mantenemos la forma de autentificación y seleccionamos la BD destino.

Definición de la BD destino de los datos.

Figura 8.13: Definición de la BD destino de los datos.

Al pulsar sobre Next, por defecto, nos indica una tabla con el mismo nombre del archivo que estamos importando (figura 8.14). Aquí podemos cambiar el nombre de la tabla destino pulsando sobre él e introduciendo un nuevo nombre.

Definición de la tabla destino de los datos.

Figura 8.14: Definición de la tabla destino de los datos.

Si pulsamos sobre el botón Edit Mappings, accedemos a la definición detallada de la tabla (figura 8.15).

Definición detallada de la tabla destino de los datos.

Figura 8.15: Definición detallada de la tabla destino de los datos.

En este apartado debemos seleccionar la opción Drop and re-create destination table para que, antes de importar la tabla, la elimine. De esta manera conseguimos que se pueda ejecutar múltiples veces el proceso de importación. En caso contrario, a partir de la primera vez que lo ejecutáramos, al crear la tabla produciría un error por existir previamente. Aceptamos los cambios y pulsamos sobre Next para continuar con la definición.

En la siguiente pantalla se define como se quiere guardar el proceso de importación y si se desea ejecutar en ese momento o no (figura 8.16).

Guardar el proceso de importación.

Figura 8.16: Guardar el proceso de importación.

En este caso, no necesitamos ejecutarlo en este momento por lo que podemos desactivar la opción Run immediately y seleccionamos File system para guardarlo como un archivo. En el resto de campos podemos dejar los valores por defecto y pulsamos sobre Next, para pasar a definir el nombre del archivo, la descripción del paquete y, pulsando sobre Browse, su ubicación (figura 8.17).

Guardar el proceso de importación como archivo.

Figura 8.17: Guardar el proceso de importación como archivo.

En este caso el criterio que vamos a seguir es:

  • Como nombre del archivo indicamos el prefijo “bd_importar_” y el nombre de la provincia asignada (p.e., en mi caso es bd_importar_granada).

Al volver a pulsar sobre Next, nos informa de las operaciones que va a realizar (figura 8.18). En particular, es interesante que nos aseguremos que aparece el literal The target table will be dropped and then re-created.

Operaciones a realizar para crear el paquete.

Figura 8.18: Operaciones a realizar para crear el paquete.

Por último, al pulsar sobre Next, ejecuta las operaciones y nos informa del resultado (figura 8.19).

Resultado de la creación del paquete.

Figura 8.19: Resultado de la creación del paquete.

El resultado es que ha creado un archivo con el nombre indicado en nuestra carpeta de trabajo.

8.2.2 Importar el paquete y ejecutarlo

Para ejecutar el paquete, previamente tenemos que incluirlo en nuestro proyecto en Visual Studio: desde el proyecto se podrá ejecutar.

En la ventana Explorerador de soluciones, en el menú contextual del apartado Paquetes SSIS, seleccionamos la opción Agregar paquete existente (figura 8.20).

Añadir paquete existente.

Figura 8.20: Añadir paquete existente.

En el campo Ubicación del paquete seleccionamos “File System” y en Ruta de acceso del paquete, pulsado sobre el botón etiquetado con “”, seleccionamos el archivo del paquete en nuestra carpeta de trabajo, donde lo habíamos salvado (figura 8.21).

Seleccionar el archivo del paquete.

Figura 8.21: Seleccionar el archivo del paquete.

Una vez importado el paquete (al pulsar OK), podemos abrirlo (pulsando doble-clic sobre su nombre) y ver su contenido (figura 8.22).

Visión global del flujo de control del paquete de importación.

Figura 8.22: Visión global del flujo de control del paquete de importación.

En la parte central de la pantalla (la ventana con el nombre del paquete, en mi caso la ventana bd_importar_granada.dtsx [Diseño]), permite definir los componentes del paquete. Tiene varias hojas, según el tipo de componente, las principales son:

  • Flujo de control: organiza la ejecución de los componentes del paquete. Cada paquete solo puede tener un componente flujo de control que contendrá uno más flujos de datos.

  • Flujo de datos: es un tipo de elemento del flujo de control (ha de estar incluido en el flujo de control del paquete). Permite diseñar los movimientos de datos. En un paquete puede haber varios componentes de flujo de datos, cada uno de ellos se puede definir seleccionándolo en el campo Tarea Flujo de datos de la hoja Flujo de datos.

  • Administradores de conexiones: en la parte inferior de esa ventana se encuentra la hoja administradores de conexiones, donde se pueden definir las conexiones a fuentes o destinos de los datos utilizados en el paquete.

En la parte izquierda de la ventana se encuentra el Cuadro de herramientas de SSIS27, que se configura en función de la hoja de la parte Diseño seleccionada: muestra los elementos que podemos utilizar en su desarrollo. En este caso, muestra las tareas que se pueden definir en el apartado Flujo de control.

Flujo de control del paquete de importación.

Figura 8.23: Flujo de control del paquete de importación.

En la figura 8.23, se muestra el flujo de control del paquete. Tiene tres tareas:

  • Borrar la tabla en la BD (Drop table(s) SQL Task 1).

  • Crear la tabla donde se importarán los datos (Preparation SQL Task 1).

  • Pasar los datos del archivo a la tabla (Data Flow Task1).

Si pulsamos doble-clic sobre el elemento Data Flow Task 1, o clic sobre la hoja Flujo de datos, podemos observar la pantalla de la figura 8.24.

Flujo de datos del paquete de importación.

Figura 8.24: Flujo de datos del paquete de importación.

Comprobamos que el flujo de datos es un tipo de tarea incluida en el flujo de control del paquete. Al seleccionarlo, la ventana Cuadro de herramientas de SSIS se ha configurado con los elementos que se pueden usar en la definición de un flujo de datos. En este caso tiene dos operaciones:

  • Obtener de los datos originales (Source - 2871_csv).

  • Incluir los datos en la tabla de la BD (Destination - 2781).

Para acceder a la fuente y destino de los datos, se han definido conexiones de datos, en el apartado Connection Managers (en la parte inferior de la ventana):

  • Acceso a nuestra BD (DestinationConnectionOLEDB).

  • Acceso al archivo de datos (SourceConnectionFlatFile).

Vamos a ejecutar el paquete. Para ello nos situamos de nuevo en la hoja Flujo de control y pulsamos sobre Depurar > Iniciar depuración (figura 8.25) o Iniciar en la barra de herramientas.

Ejecutar el paquete.

Figura 8.25: Ejecutar el paquete.

El resultado de la ejecución de las tareas se muestra en la figura 8.26.

Resultado de ejecutar el paquete.

Figura 8.26: Resultado de ejecutar el paquete.

Por el símbolo que muestra a la derecha de la representación de cada tarea, observamos que la primera tarea ha fallado ya que la tabla que se ha intentando borrar no existía. El resto de tareas no se han visto afectadas y se han ejecutado correctamente. Las tareas están relacionadas mediante flechas que indican precedencia. En el caso de la relación entre la tarea de borrar la tabla y la de crearla, la flecha aparece etiquetada con el literal Conclusión, indicando que la siguiente tarea se puede ejecutar cuando la primera se complete, independientemente de su resultado.

Si pulsamos sobre la hoja Flujo de datos, podemos ver el resultado detallado de la tarea de flujo de datos. En este caso, se muestra el número de filas que se ha pasado de una operación a otra (figura 8.27).
Resultado de ejecutar el paquete en el flujo de datos.

Figura 8.27: Resultado de ejecutar el paquete en el flujo de datos.

Podemos volver al modo de diseño pulsando sobre el enlace al pie de la ventana o desde el menú Depuración, como en el propio enlace se indica.

Para comprobar el resultado del proceso de importación, vamos a ver la BD y su contenido en SSMS. En primer lugar, debemos refrescar su contenido desde el menú contextual de nuestra BD (figura 8.28).

Refrescar el contenido de la BD.

Figura 8.28: Refrescar el contenido de la BD.

De esta forma, podemos desplegar las tablas y, para la nueva tabla, podemos ver las columnas que tiene definidas (figura 8.29).

Mostrar las columnas de una tabla.

Figura 8.29: Mostrar las columnas de una tabla.

Desde el menú contextual de la tabla, podemos pulsar sobre la opción Select Top 1000 Rows para generar y ejecutar la sentencia SQL que muestra el contenido de las primeras filas de la tabla (figura 8.30).

Mostrar el contenido de una tabla.

Figura 8.30: Mostrar el contenido de una tabla.

En principio parce que está todo bien pero, si repasamos la tabla hasta llegar a algún nombre con tilde, observamos que no es así. Tenemos un problema de codificación.

8.2.3 Corregir el problema de codificación

Para tratar de determinar dónde se produce el problema con la codificación, vamos a ver los datos intermedios que se transfieren entre la operación de lectura de los datos originales y la de escritura en la BD.

Accedemos a la hoja Flujo de datos y, pulsando doble-clic sobre la flecha que une las dos operaciones, se abre la ventana de configuración que, una vez seleccionado el apartado Visor de datos, se muestra en la figura 8.31.

Configuración del flujo de datos.

Figura 8.31: Configuración del flujo de datos.

En ese apartado, seleccionamos la opción Habilitar visor de datos. Esto hará que, cuando se ejecute el proceso y se pasen los datos de una operación a otra, se muestre una ventana con los datos. De esta forma podremos saber si se han leído mal o se han escrito mal.

Al cerrar la ventana se muestra la flecha con una lupa que indica esta funcionalidad28.

En la figura 8.32, se muestra el resultado al ejecutar el paquete.

Vista del flujo de datos.

Figura 8.32: Vista del flujo de datos.

Se puede apreciar que los datos se transfieren bien, el problema se produce al introducirlos en la BD.

Si pulsamos doble-clic sobre la conexión al archivo CSV, en el aparado Administradores de conexiones, se abre la ventana de la figura 8.33.

Codificación de la fuente de datos.

Figura 8.33: Codificación de la fuente de datos.

Observamos que este archivo CSV tiene codificación UTF-8, la herramienta de importación lo ha considerado correctamente.

Sin embargo, si vemos la codificación asignada a la BD, en la hoja Flujo de control, pulsando doble-clic sobre la tarea Preparation SQL Task 1, en este caso la codificación es Windows-1252 (figura 8.34).

Codificación del destino de datos.

Figura 8.34: Codificación del destino de datos.

Se puede crear una BD con codificación UTF-8, sin embargo, esto implica cambiar los tipos de los campos, no sería válido el tipo varchar. He explorado las opciones de la herramienta de generación del paquete de importación de datos y las seleccionadas eran las adecuadas.

Haciendo diversas pruebas, la solución más sencilla que he encontrado ha sido añadir transformaciones en el flujo de datos que añadan nuevos campos con la codificación adecuada.

En la hoja Flujo de datos, eliminamos la conexión entre las dos operaciones seleccionando Eliminar en el menú contextual de la flecha que las relaciona (figura 8.35). De esta forma, podemos añadir operaciones intermedias.

Eliminar relación entre operaciones.

Figura 8.35: Eliminar relación entre operaciones.

Añadimos una operación Conversión de datos pulsando-arrastrando-soltado el icono desde el Cuadro de herramientas de SSIS hasta el área de diseño. Pulsando-arrastrando-soltando la flecha azul que sale de la operación Source 2871_csv sobre la nueva operación Conversión de datos añadida, establecemos una relación entre estas operaciones (figura 8.36)

Establecer relación entre operaciones.

Figura 8.36: Establecer relación entre operaciones.

Pulsando doble-clic sobre la operación Conversión de datos se abre la ventana de configuración (figura 8.37). Seleccionando cada una de las celdas asociadas a los campos en la figura de la parte superior de la ventana, estos se añaden a la parte inferior para poder cambiar su tipo.

Definir conversión de tipo de datos.

Figura 8.37: Definir conversión de tipo de datos.

Se puede observar que los nuevos campos con prefijo “Copia de”, sin haber hecho ninguna transformación, tienen un tipo cadena [DT_STR] y una codificación UTF-8. Si buscamos información sobre los tipos de campos y la codificación, para almacenar correctamente los datos con esa codificación, deberían ser de tipo cadena Unicode [DT_WSTR]. Por tanto, vamos a cambiarlos de para que tengan ese tipo (figura 8.38)

Definir tipo de datos UTF-8.

Figura 8.38: Definir tipo de datos UTF-8.

Al hacer este cambio, desaparece la información de la codificación que aparecía en la parte derecha de la tabla pero los datos tendrán asociado un tipo de dato acorde con su codificación. Sin embargo, los datos de la BD tienen una codificación Windows-1252 por lo que todavía no hemos acabado: debemos definir otra transformación para obtener, a partir de los nuevos datos, otros con la codificación de la BD.

Añadimos una nueva operación Conversión de datos y la relacionamos con la anterior, añadimos los nuevos campos (los almacenados con el tipo correcto) para generar otros a partir de ellos, y les cambiamos el tipo a cadena [DT_STR], el adecuado para la codificación de la BD. Podemos observar que, automáticamente, la codificación de los campos se configura a 1252 (ANSI - Latín I), la codificación de la BD (figura 8.39).

Definir tipo de datos Windows-1252.

Figura 8.39: Definir tipo de datos Windows-1252.

Por último, conectamos el resultado de esta operación con la operación que almacena los datos en la BD y, pulsando doble-clic sobre ella, la configuramos. En el apartado Asignaciones se define la correspondencia entre los campos de entrada y los campos de la BD. Actualmente los campos de entrada seleccionados son los originales; en lugar de ellos debemos relacionar los campos correspondientes con el prefijo “Copia de Copia de” (los segundos que definimos, podíamos haberles dado otro nombre pero dejamos los nombres por defecto). Borramos las correspondencias entre campos pulsando sobre ellas para seleccionarlas y, a continuación, pulsando la tecla Supr o Del. Definimos las nuevas correspondencias pulsando-arrastrando-soltando un campo del origen sobre otro del destino. El resultado de la definición de correspondencias se muestra en la figura 8.40.

Definición de correspondencias entre campos.

Figura 8.40: Definición de correspondencias entre campos.

Si ejecutamos de nuevo el paquete con estas modificaciones y observamos el resultado en SSMS podemos comprobar que el problema de codificación está corregido (figura 8.41).

Problema con las tildes en la tabla corregido.

Figura 8.41: Problema con las tildes en la tabla corregido.

Ejercicio 8.1 Crea una BD en SQL Server, un proyecto SSIS e importa los datos de tu provincia asignada en una tabla de la BD, usando los criterios de nomenclatura indicados en los apartados anteriores (captura una pantalla donde se muestren filas de la tabla obtenida donde se pueda ver el nombre de algún municipio con tilde, como resultado de una consulta SQL, y otra donde se muestre el apartado Flujo de datos del paquete de importación de datos).

8.3 Desarrollo de un paquete

Al crear un proyecto, automáticamente se crea un paquete llamado Package.dtsx, podemos verlo en el entorno de trabajo de Visual Studio (figura 8.9). Para cambiar el nombre al paquete, se puede hacer desde su menú contextual en la ventana Explorador de soluciones, seleccionando la opción Cambiar nombre.

  • Llamamos al paquete con el prefijo “generar_ft_” y el nombre de la provincia asignada (p.e., en mi caso se llamará generar_ft_granada.dtsx).

8.3.1 Conexiones

En primer lugar, vamos a definir las conexiones con las fuentes y destino de los datos mediante los administradores de conexiones.

Nuestros datos fuente se encuentran en archivos de texto CSV y en la BD. Para definir una conexión, en el menú contextual del apartado Administradores de conexiones, seleccionamos la opción del tipo de origen de los datos con el que queremos conectarnos. En el caso de los archivos CSV, la operación a seleccionar es Nueva conexión de archivo plano (figura 8.42).

Selección del tipo de origen de los datos.

Figura 8.42: Selección del tipo de origen de los datos.

Se abre la ventana para configurar un origen de datos del tipo seleccionado (figura 8.43).

  • Le podemos asignar un nombre (en el campo Nombre del administrador de conexiones). En este caso, el nombre del archivo sin extensión, y seleccionamos el archivo origen de datos pulsando sobre el botón Examinar29.
Configuración del tipo de origen de los datos CSV.

Figura 8.43: Configuración del tipo de origen de los datos CSV.

Las opciones por defecto para el resto de los campos son adecuadas. Sin embargo, nos avisa al pie de la ventana sobre que no se han definido las columnas. Si pulsamos sobre la opción Columnas, de la columna de la parte izquierda de la ventana, podemos ver las columnas obtenidas (figura 8.44).

Columnas del tipo de origen de los datos CSV.

Figura 8.44: Columnas del tipo de origen de los datos CSV.

Por defecto, define las columnas como de tipo texto. El tipo se puede cambiar desde la opción Advanzadas (en la columna de la izquierda) pero, por ahora, no las vamos a cambiar. Sin hacer ningún cambio, ahora ya podemos pulsar sobre OK para llevar a cabo la definición.

  • Exactamente de la misma forma, definimos la conexión para cod-habitantes.csv a la que llamamos cod-habitantes.

Por último, vamos a definir la conexión con la BD SQL Server donde tenemos los datos de la provincia30, crearemos las tablas de dimensiones y hechos. El procedimiento es el mismo que para las conexiones a archivos CSV. En primer lugar, seleccionamos la operación, en este caso Nueva conexión OLE DB (figura 8.45).

Selección del tipo de origen de los datos para BD.

Figura 8.45: Selección del tipo de origen de los datos para BD.

Se abre la ventana donde se mostrará la lista a conexiones a BD (figura 8.46). En este caso no hay todavía ninguna31. Para crear una nueva conexión, pulsamos sobre el botón New.

Ventana de lista de conexiones a BD.

Figura 8.46: Ventana de lista de conexiones a BD.

Para definir la nueva conexión, en la ventana que se abre (figura 8.47), introducimos el nombre del servidor donde está funcionando SQL Server, en la instalación propuesta, el modo de conexión se es el seleccionado por defecto. En el apartado Catálogo original, seleccionamos el nombre de la BD que hemos creado (p.e, en mi caso granada-jsamos).

Definición de una conexión a BD.

Figura 8.47: Definición de una conexión a BD.

Podemos comprobar si la conexión se establece correctamente pulsando sobre el botón Probar Conexión. Al pulsar sobre Aceptar, se crea la conexión y aparece en la lista de conexiones disponibles (figura 8.48).

Nueva conexión en la de lista de conexiones a BD.

Figura 8.48: Nueva conexión en la de lista de conexiones a BD.

Para acabar, pulsamos sobre Aceptar y la nueva conexión queda definida en el paquete. En este caso, no podemos asignarle un nombre: su nombre está compuesto del nombre del servidor y del nombre de la BD a la que accede. Si se desea, se puede renombrar desde su menú contextual, en este caso no es necesario, dejaremos el nombre original.

En la figura 8.49, se muestran las conexiones definidas.

Conexiones definidas.

Figura 8.49: Conexiones definidas.

8.3.2 Flujo de control

En el apartado Flujo de control las acciones a realizar se definen en forma de Tareas. Los distintos tipos de tarea se presentan en la ventana Cuadro de herramientas de SSIS.

Está organizada en secciones dinámicas: podemos mover un elemento de una sección a otra desde su menú contextual, en particular, podemos ubicar en el apartado Favoritos los de uso más frecuente.

En nuestro caso, la tarea que vamos a necesitar es Tarea Flujo de datos: pulsamos-arrastramos-soltamos ese tipo de tarea desde la el apartado Cuadro de herramientas de SSIS hasta la ventana de diseño de Flujo de control. Podemos renombrar el objeto desde su menú contextual o haciendo clic sobre su nombre. La primera tarea que vamos a realizar es obtener una tabla plana con todos los datos, por lo que la llamaremos Tabla plana (figura 8.50).

Definición de una tarea.

Figura 8.50: Definición de una tarea.

Partiendo de la tarea definida hay una flecha que permite definir restricciones de precedencia entre tareas. En este caso, una vez obtenida la tabla plana con todos los datos disponibles, podemos definir las dimensiones y, a partir de ellas, los hechos. Definiremos una Tarea Flujo de datos para cada una de estas actividades y estableceremos su precedencia pulsando-arrastrando-soltando la flecha de una tarea sobre otra. En la figura 8.51 se muestran las tareas mencionadas a las que hemos renombrado como Dimensión Cuándo, Dimensión Dónde y Padrón.

Precedencia entre tareas.

Figura 8.51: Precedencia entre tareas.

Tal y como se muestra en la figura 8.51, en el menú contextual de las flechas que definen la precedencia entre las tareas, podemos definir la condición que ha de cumplirse para que la tarea siguiente pueda ejecutarse. En todos los casos la condición por defecto, Correcto, es la adecuada.

Se pueden definir restricciones múltiples a partir de las restricciones de precedencia que afectan a una tarea. Pulsando doble-clic sobre una cualquiera de esas restricciones se abre la ventana de la figura 8.52 que permite definir si se han de cumplir todas las restricciones (AND lógico) o basta con solo una (OR lógico) para que se ejecute la tarea correspondiente.

Definición de restricciones múltiples de una tarea.

Figura 8.52: Definición de restricciones múltiples de una tarea.

En este caso, necesitamos que se hayan generado correctamente todas las dimensiones para poder generar los hechos.

Podemos ejecutar el proceso que acabamos de definir, pulsando sobre Depurar > Iniciar depuración o Iniciar en la barra de herramientas.

En este caso, las tareas están vacías pero las ejecuta. El resultado de la ejecución se muestra en la figura 8.53). Dado que las tareas todavía no hacen nada, todas se han ejecutado correctamente y esto se muestra de forma gráfica.

Resultado de ejecutar el proceso.

Figura 8.53: Resultado de ejecutar el proceso.

Podemos consultar los resultados concretos de la ejecución pulsando sobre una nueva hoja del apartado de diseño en el que estamos llamada Progreso (figura 8.54).

Progreso del resultado de ejecutar el proceso.

Figura 8.54: Progreso del resultado de ejecutar el proceso.

Podemos volver al modo de diseño pulsando sobre el enlace al pie de la ventana o desde el menú Depurar como en el propio enlace se indica.

Como resultado de la ejecución, se ha añadido a la ventana de diseño una nueva hoja Resultados de la ejecución con un contenido similar al mostrado en la hoja Progreso, asociada a la ejecución del proceso.

Una vez definidas las tareas y sus dependencias, tenemos que especificarlas. En este caso, todas las tareas que necesitamos son Tarea Flujo de datos, por tanto, es lo que vamos a definir a continuación.

Ejercicio 8.2 Define las conexiones y el flujo de control del paquete, usando los criterios de nomenclatura indicados en los apartados anteriores (captura una pantalla donde se muestre el resultado de ejecución de las tareas del paquete en formato gráfico y otra en formato de texto).

8.3.3 Flujos de datos

Para acceder a los flujos de datos y poder definir sus componentes, podemos hacer doble-clic sobre el flujo de datos en la hoja Flujo de control o acceder a la hoja Flujo de datos y seleccionar el flujo concreto en el campo Tarea Flujo de datos, como se muestra en la figura 8.55.

Acceso a los flujos de datos.

Figura 8.55: Acceso a los flujos de datos.

El apartado Cuadro de herramientas de SSIS se configura con las operaciones que se pueden realizar para definir los flujos de datos. A continuación, vamos a desarrollar los flujos de datos que aparecen en el flujo de control del paquete que estamos desarrollando.

Tabla plana

El objetivo de este flujo de datos es, a partir de los datos de la provincia, que están en la BD, y los datos adicionales, que están disponibles en forma de archivos CSV, obtener una tabla en la BD con todos los datos, de manera que cada fila se corresponda a un individuo observado: en este caso, un municipio en un año.

Acceder a los datos en la BD

Para acceder a los datos de las fuentes, pulsamos-arrastramos-soltamos el elemento Asistente de orígenes desde el Cuadro de herramientas de SSIS hasta el área de diseño de Flujo de datos. Como resultado, se abre una ventana de configuración donde seleccionamos qué conexión de datos queremos usar (figura 8.56).

Selección de la conexión de datos.

Figura 8.56: Selección de la conexión de datos.

Una vez seleccionada, hemos definido la BD a la que nos conectamos, pero faltan por definir elementos: lo indica mediante el símbolo de error (X) a la derecha de su nombre. Falta por definir la tabla a la que nos conectamos. Pulsando doble-clic sobre la operación añadida, se abre la ventana de configuración (figura 8.57), donde podemos seleccionar el nombre de la tabla.

Selección de la tabla en la conexión de datos.

Figura 8.57: Selección de la tabla en la conexión de datos.

Podemos renombrar las operaciones, tal y como hicimos para las tareas:

  • Le asignaremos el nombre de la provincia (p.e., en mi caso se llamará granada).

A diferencia de las tareas, en este caso, cada operación tiene dos flechas de conexión:

  • La flecha azul (izquierda) se corresponde a los datos que se devuelven procesados correctamente.

  • La flecha roja (derecha) corresponde a los datos devueltos si se produce en ellos algún error.

De esta forma, podamos pasar los datos correctos a un elemento y los erróneos a otro distinto para realizar tratamientos específicos en cada situación.

Seleccionar datos no-agregados

Los datos con los que trabajamos incluyen datos de los individuos (a nivel municipio y año) y también datos agregados: datos para la provincia (en el campo Municipios, en lugar de un código de cinco dígitos y el nombre del municipio, aparece un código de dos dígitos y el nombre de la provincia, p.e., en mi caso “18 Granada”); en el campo Sexo, en lugar de “Mujeres” y “Hombres”, también aparece la suma de ambos bajo el concepto “Total”.

Vamos a comenzar la transformación de los datos, seleccionando exclusivamente los datos que no sean agregados. Para ello añadimos una operación División condicional, conectamos la salida de datos correctos (azul, a la izquierda) con la nueva operación y le cambiamos el nombre a no-agregados. Pulsando doble-clic sobre la nueva operación podemos definirla (figura 8.58).

Seleccionar datos no agregados.

Figura 8.58: Seleccionar datos no agregados.

A cada condición definida se le puede asignar un nombre y, posteriormente, seleccionar la salida con el nombre deseado para conectarla con la siguiente operación. En este caso, solo necesitamos definir una condición, le asignamos al nombre el valor “No agregados” que tiene asociada la condición Municipios != "18 Granada" && Sexo != "Total". Para definir la condición se puede pulsar-arrastrar-soltar las columnas y las operaciones de los apartados superiores de la ventana hasta la línea cuya cabecera es Condición.

Para probar el funcionamiento de cada operación, el recurso que se puede usar es añadir una operación adicional Multidifusión, que tiene como característica que no hay que hacer nada para configurarla, la relacionamos con la última operación definida (en este caso, seleccionamos la salida “No agregados”), como se muestra en la figura 8.59.

Seleccionar datos no agregados en la salida.

Figura 8.59: Seleccionar datos no agregados en la salida.

Podemos situar un visor en la comunicación entre las operaciones, (pulsando doble-clic sobre la flecha, como hicimos en el apartado 8.2.3), y ejecutar el proceso (Depurar > Iniciar depuración).

Al ejecutar el proceso, se muestra sobre cada flecha de comunicación entre las operaciones el número de filas que pasan. En este caso, al definir un filtro, deben pasar menos filas a la siguiente operación. Una vez hemos hecho la comprobación, podemos eliminar la operación Multidifusión o la relación entre la operación a comprobar y esta, para seguir definiendo el proceso de transformación.

Conversión de datos

Las columnas leídas de la BD son todas de tipo texto. El campo Total representa la cantidad de habitantes observados bajo las condiciones definidas por la combinación de los otros campos. Vamos a transformar a obtener un campo de tipo entero a partir de él llamado cantidad.

Añadimos una operación Conversión de datos, a la que también llamaremos cantidad, y la conectamos con el resultado de la operación anterior. Pulsando doble-clic sobre la nueva operación, configuramos la conversión de tipo (figura 8.60).

Definición de la conversión de tipo.

Figura 8.60: Definición de la conversión de tipo.

Se pueden generar varios campos a la vez, en este caso solo necesitamos uno. Seleccionamos el campo de entrada y el nombre y tipo del campo de salida. Para representar cantidades de personas es adecuado un tipo entero sin signo (al menos de cuatro bytes).

Al ejecutar la transformación, observamos que se produce un error (figura 8.61).

Error en la conversión de tipo.

Figura 8.61: Error en la conversión de tipo.

Podemos configurar la respuesta en las situaciones de error de la operación desde su ventana de configuración (pulsando doble-clic sobre la operación), pulsando sobre el botón Configurar la salida de errores (figura 8.62).

Configurar la respuesta ante errores en la definición de la conversión de tipo.

Figura 8.62: Configurar la respuesta ante errores en la definición de la conversión de tipo.

En caso de error, seleccionamos la opción Omitir error en lugar de la que había que era Error de componente, que era el comportamiento que estaba mostrando.

Al volver a ejecutar la transformación, en este caso podemos ver el resultado obtenido (si lo hemos configurado así en la salida de la operación), como se muestra en la figura 8.63.

Resultado de la conversión de tipo.

Figura 8.63: Resultado de la conversión de tipo.

Podemos observar que la conversión la ha realizado correctamente, incluso trata correctamente el separador de miles. Sin embargo, para las instancias donde el valor del campo de entrada estaba vacío (por no existir datos para ese año y municipio: ocurre para 1997 en todos los municipios y también para municipios nuevos u otros que han desaparecido, que no tienen datos para todos los años disponibles) ha generado el valor NULL en el nuevo campo.

En este caso, vamos a seleccionar los valores no nulos mediante una operación División condicional que llamaremos no-NULL. En la figura 8.64 se muestra su definición. La condición de selección es !ISNULL([cantidad]).

Selección de valores no nulos.

Figura 8.64: Selección de valores no nulos.

De esta forma, nos limitamos a trabajar exclusivamente con los datos de los que tenemos observaciones.

Agrupar los datos a nivel individuo

Consideramos que un individuo es un municipio en un periodo concreto. Observamos características de cada uno de los individuos, en este caso la cantidad de mujeres y de hombres. Actualmente tenemos cada carácter observado en una fila. Vamos a estructurar los datos de manera que cada fila se corresponda con un individuo (municipio y periodo).

La operación que nos permite hacer esa transformación es Dinamización. La añadimos, la conectamos a la salida de la operación anterior y abrimos su ventana de configuración (figura 8.65).

Configuración de la operación Dinamización.

Figura 8.65: Configuración de la operación Dinamización.

Comprobamos que se necesita identificar a los individuos mediante un solo campo (Clave fija) y, además, los datos deben estar ordenados por él para que puedan ser agrupados.

Los individuos están identificados mediante la combinación de los campos Municipios y Periodo. Por tanto, antes de aplicar esta operación, debemos:

  • Generar un nuevo campo que combine los valores de estos.

  • Ordenar los datos por el nuevo campo.

Añadimos una operación Columna derivada que hemos llamado clave donde se define un nuevo campo llamado clave con la expresión [Periodo] + [Municipios] (figura 8.66).

Definir un nuevo campo identificador de individuos.

Figura 8.66: Definir un nuevo campo identificador de individuos.

A continuación, añadimos una operación Ordenar para ordenar los datos por el nuevo campo (figura 8.67).

Ordenar por el campo identificador de individuos.

Figura 8.67: Ordenar por el campo identificador de individuos.

De esta forma, una vez conectada a la salida de la operación de ordenación, podemos volver a la configuración de la operación Dinamización (figura 8.65). En el apartado Generar columnas de salida dinámica a partir de valores, debemos indicar los valores del campo Clave dinámica a partir de los que generar las nuevas columnas del resultado. En este caso, sabemos que los valores son “Mujeres” y “Hombres”, por tanto, deberíamos indicar “[Mujeres], [Hombres]”.

En un caso general donde no supiéramos los posibles valores, en la propia pantalla nos da las indicaciones para obtenerlos:

  • Marcamos la opción Omitir los valores de clave dinámica no coincidentes y registrarlos después de la ejecución del flujo de datos.

  • Ejecutamos el flujo de datos y buscamos los valores que no hayan coincidido con los indicados (no hemos indicado ninguno todavía).

Podemos ver los valores posibles en el resultado de la ejecución, asociados al paso Dinamización (figura 8.68).

Valores a considerar para generar columnas.

Figura 8.68: Valores a considerar para generar columnas.

Podemos copiar todo el literal y borrar la parte inicial para tener los valores. Al pulsar sobre el botón Generate Columns Now, nos avisa de que va a generar columnas a partir de esos valores (figura 8.69)

Configuración de la operación Dinamización.

Figura 8.69: Configuración de la operación Dinamización.

Al aceptar los cambios, podemos comprobar el resultado de la operación (figura 8.70).

Resultado de la operación Dinamización.

Figura 8.70: Resultado de la operación Dinamización.

Se puede observar que tenemos la mitad de registros en la salida que en la entrada, como era de esperar. Sin embargo, tan solo aparece la clave y los campos generados. Incluir el resto de campos no es inmediato. Desde el menú contextual de la operación, seleccionamos la opción Mostrar el Editor avanzaddo (figura 8.71).

Mostrar el editor avanzado de la operación Dinamización.

Figura 8.71: Mostrar el editor avanzado de la operación Dinamización.

En la hoja Columnas de entrada del editor avanzado, seleccionamos las columnas adicionales que queremos incluir, en este caso Municipios y Periodo (figura 8.72).

Selección de campos adicionales en la operación Dinamización.

Figura 8.72: Selección de campos adicionales en la operación Dinamización.

Si lo probamos, comprobaremos que esto no es suficiente, con esto solo hemos indicado que se consideren estos campos en la entrada (ni siquiera podemos ejecutar la operación porque avisa de un error). Hay que definir una correspondencia entre los campos de entrada y los de salida. Se define desde la hoja Propiedades de entrada y salida del editor avanzado (figura 8.73).

Definir correspondencia entre campos de entrada y salida en la operación Dinamización.

Figura 8.73: Definir correspondencia entre campos de entrada y salida en la operación Dinamización.

Si, como se muestra en la figura 8.73, desplegamos en la columna de la izquierda los apartados Entrada predeterminada dinámica y Salida predeterminada dinámica, podemos apreciar que los campos adicionales aparecen en la entrada pero no están incluidos en la salida.

Para incluirlos, debemos definir un nuevo campo en la salida y asignarle una correspondencia con un campo de la entrada. La correspondencia se define manualmente, asociándole el valor de la propiedad LineageId del campo de la entrada. En la figura 8.73, observamos el valor de esa propiedad para el campo Municipios, lo anotamos o lo copiamos para nuestro caso.

Situándonos en el apartado Columnas de salida (dentro de Salida predeterminada dinámica) pulsamos sobre el botón Agregar Columna para añadir un nuevo campo a la salida (figura 8.74).

Añadir un nuevo campo a la salida en la operación Dinamización.

Figura 8.74: Añadir un nuevo campo a la salida en la operación Dinamización.

Le asignamos un nombre (no tiene que ser el mismo de la entrada) y le asignamos en la propiedad SourceColumn el valor que hemos anotado o copiado (el de la propiedad LineageId) del campo de la entrada correspondiente (figura 8.75).

Definir la correspondencia entre dos campos en la operación Dinamización.

Figura 8.75: Definir la correspondencia entre dos campos en la operación Dinamización.

Repetimos esta operación para el campo Periodo.

Si volvemos a ejecutar la transformación, comprobamos que el resultado no incluye los campos que acabamos de definir. El problema no es de la definición sino del visor de resultados: pulsando doble-clic sobre la flecha, observamos su configuración y los nuevos campos no están incluidos para ser presentados (figura 8.76). Debemos incluirlos en el apartado Columnas mostradas.

Incluir nuevos campos en el visor de datos.

Figura 8.76: Incluir nuevos campos en el visor de datos.

Si observamos de nuevo el resultado de la ejecución, comprobamos que la salida se corresponde a lo que esperábamos obtener (figura 8.77).

Resultado completo de la operación Dinamización.

Figura 8.77: Resultado completo de la operación Dinamización.

Obtener llaves externas

Vamos a enriquecer los datos de nuestra provincia con los datos adicionales disponibles en los archivos cod-habitantes.csv y municipios.csv. Para definir las relaciones entre ellos, necesitamos obtener campos que se correspondan con las llaves primarias de esos archivos.

  • Para definir la relación con cod-habitantes.csv necesitamos un código definido en función del número de habitantes del municipio.

  • Para la relación con municipios.csv necesitamos el código numérico de cinco dígitos que precede al nombre del municipio. En el archivo este código tiene varios ceros a la derecha que podemos eliminar.

En primer lugar, añadimos una operación Columna derivada, a la que llamamos códigos, donde definimos una columna habitantes como la suma de la cantidad de mujeres y de hombres (a partir de ella posteriormente definiremos el código), y separaremos el contenido del campo Municipios en dos campos: cod_municipio y municipio (figura 8.78).

Definición de campos derivados.

Figura 8.78: Definición de campos derivados.

Las expresiones de definición de estos campos son las siguientes:

  • [C_Hombres_cantidad] + [C_Mujeres_cantidad]

  • LEFT([Municipios],5)

  • SUBSTRING([Municipios],7,LEN([Municipios]) - 6)

Una vez comprobamos que estos campos están definidos correctamente, añadimos una nueva operación Columna derivada, a la que llamamos códigos 2, donde definimos una columna cod_habitantes en función del valor del campo habitantes, según los intervalos definidos en el archivo cod-habitantes.csv (figura 8.79).

Definición del campo derivado del nivel de habitantes.

Figura 8.79: Definición del campo derivado del nivel de habitantes.

En este caso, se usa la función ?:(Conditional) de forma anidada para contemplar todos los extremos de los intervalos. La expresión de definición del campo es la siguiente:

  • habitantes > 500000 ? "9" : habitantes > 100000 ? "8" : habitantes > 50000 ? "7" : habitantes > 10000 ? "6" : habitantes > 5000 ? "5" : habitantes > 1000 ? "4" : habitantes > 500 ? "3" : habitantes > 100 ? "2" : "1"

Comprobamos que la salida es la esperada, como se muestra en la figura 8.80.

Resultado de la definición de campos derivados.

Figura 8.80: Resultado de la definición de campos derivados.

Unir con cod-habitantes.csv

Accedemos a los datos del archivo cod-habitantes.csv, mediante una operación Asistente de orígenes en la que, una vez definidas las conexiones, seleccionamos el tipo de fuente y el nombre del archivo (figura 8.81).

Seleccionar la fuente de datos.

Figura 8.81: Seleccionar la fuente de datos.

Para unir la tabla de datos de nuestra provincia con el contenido de este archivo, usaremos una operación Combinación de mezcla que, si miramos su descripción, requiere que ambas tablas estén ordenadas por el campo que se va a usar para definir la unión. Por tanto, ordenamos las tablas por los campos correspondientes (Cod y cod_habitantes) y el resultado de estas operaciones es el que definimos como entrada a la operación Combinación de mezcla. Cuando definimos la primera entrada de esta operación podemos definir cómo se considera la tabla en la operación. En la figura 8.82 se muestra que hemos elegido que corresponda a la tabla considerada a la izquierda (aunque en el flujo de datos aparezca a la derecha).

Definición de la entrada en la operación Combinación de mezcla.

Figura 8.82: Definición de la entrada en la operación Combinación de mezcla.

En la ventana de configuración de la operación, considera los campos que se han usado para ordenar las tablas como componentes de la relación entre ellas. Podemos seleccionar los campos de ambas tablas que se considerarán en la salida y, adicionalmente, podemos aprovechar esta operación para renombrarlos (figura 8.83).

Seleccionar los campos del resultado (y renombrar).

Figura 8.83: Seleccionar los campos del resultado (y renombrar).

  • Campos renombrados: periodo, hombres, mujeres.

Mediante la operación Columna deriveda, vamos a definir un campo como combinación del código y el literal descriptor del intervalo del número de habitantes, de manera que las ordenaciones alfabética y lógica del resultado coincidan (figura 8.84).

Combinar campos de código y descripción de nivel de habitantes.

Figura 8.84: Combinar campos de código y descripción de nivel de habitantes.

Llamamos al nuevo campo nivel_habitantes y su expresión es:

  • [cod_habitantes] + " - " + [Tamaño de Municipio]
Unir con municipios.csv

Accedemos a los datos del archivo municipios.csv tal y como acabamos de hacer para acceder a cod-habitantes.csv, mediante una operación Asistente de orígenes.

Para definir la unión, en primer lugar, debemos obtener el código de municipio para este archivo con el mismo tamaño que lo tenemos en nuestra tabla de municipios: con cinco posiciones (sin considerar el resto de ceros por la derecha). Para ello, mediante una operación Columna derivada, definimos el campo cod_municipio. La expresión de definición es:

  • LEFT(COD_INE,5)

A partir de esta tabla y de la tabla con los datos de nuestra provincia, ordenando cada una de ellas previamente por el código de municipio, definimos una operación Combinación de mezcla, seleccionando todos los campos de ambas tablas (excepto el código y nombre de municipio de la tabla obtenida a partir de municipios.csv que ya están incluidos en la nuestra, y cod_habitantes y Tamaño de municipio que están incluidos en el campo nivel_habitantes) y los renombramos escribiéndolos en minúscula según el criterio snake case (figura 8.85).

Selección y nuevo nombre de los campos del resultado de la unión.

Figura 8.85: Selección y nuevo nombre de los campos del resultado de la unión.

De esta manera ya tenemos todos los datos en una sola tabla. En concreto, los campos del resultado son:

  • cod_municipio

  • municipio

  • periodo

  • hombres

  • mujeres

  • nivel_habitantes

  • capital

  • cod_comarca

  • comarca

  • cod_provincia

  • provincia

  • cod_comunidad_autonoma

  • comunidad_autonoma

  • altitud_m

  • nivel_altitud

  • superficie_ha

Almacenar el resultado

Para almacenar el resultado, mediante una operación Asistente de destinos, seleccionamos la conexión a la BD y definimos como entrada a esta operación el resultado de la última operación realizada (Combinación de mezcla).

  • Llamamos a esta operación mediante el prefijo “ft_”32 y el nombre de nuestra provincia (p.e., en mi caso se llama ft_granada).

En la ventana de definición de la operación, podemos seleccionar la tabla de la BD en la que almacenar el resultado. Como no hemos definido ninguna tabla con ese fin, podemos obtenerla pulsando sobre el botón Nueva. Se abre la ventana Crear tabla con una sentencia SQL generada a partir de los campos del flujo de datos de entrada a la operación (figura 8.86).

Crear la tabla del resultado.

Figura 8.86: Crear la tabla del resultado.

Si pulsamos sobre el botón Aceptar, se ejecuta esta sentencia SQL y se crea la tabla con la estructura adecuada a los datos.

Para acabar de definir esta operación, tenemos que revisar el apartado Asignaciones de la misma ventana. No tenemos que modificar ninguna de las correspondencias definidas (figura 8.87).

Revisar correspondencias definidas.

Figura 8.87: Revisar correspondencias definidas.

Si ejecutamos el proceso completo, aparentemente todo funciona de forma correcta (figura 8.88).

Ejecución del proceso completo.

Figura 8.88: Ejecución del proceso completo.

El único problema es que, si lo ejecutamos varias veces, las filas se van añadiendo progresivamente a la tabla de la BD.

Ejercicio 8.3 Define el flujo de datos para obtener una tabla plana con todos los datos de la provincia enriquecidos con los datos adicionales disponibles, usando los criterios de nomenclatura indicados en los apartados anteriores (captura una pantalla donde se muestre el resultado de ejecución de las tareas junto con la ventana abierta de un visor de datos en el último paso).

Completar el flujo de control

Para poder repetir las operaciones de transformación tantas veces como sea necesario, en el apartado Flujo de control, debemos añadir operaciones previas para borrar y crear la tabla de resultados, similares a las generadas en el proceso de importación realizado en el apartado 8.2.

Podemos definir nuevas operaciones o copiar las del paquete de importación y modificarlas adecuadamente. Las copiamos, accediendo al paquete de importación, seleccionando las operaciones a copiar y, en el menú contextual de la selección, pulsando sobre la opción Copiar (figura 8.89).

Copiar operaciones de un paquete.

Figura 8.89: Copiar operaciones de un paquete.

Accedemos al apartado Flujo de control de nuestro paquete, las pegamos y la relacionamos con el Flujo de datos Tabla plana que hemos definido (figura 8.90).

Pegar y relacionar las operaciones.

Figura 8.90: Pegar y relacionar las operaciones.

Observamos, como también se puede apreciar en la figura 8.90, que aparecen errores porque no está definida la conexión. Las conexiones las tenemos definidas de forma particular en cada paquete. Debemos modificar esas dos tareas para cambiar:

  • Su nombre.

  • La conexión que usan.

  • La tabla sobre la que trabajan.

Si abrimos la ventana de configuración de la operación de creación de tabla, podemos cambiar la conexión en la propiedad Connection del apartado General, pulsando sobre el valor actual y seleccionando el adecuado. En la propiedad SQLStatement copiamos la sentencia SQL de creación de la tabla que podemos copiar de la operación de almacenamiento de datos en el Data Flow, como se muestra en la figura figura 8.91.

Pegar la sentencia SQL de definición de la tabla.

Figura 8.91: Pegar la sentencia SQL de definición de la tabla.

Hacemos exactamente igual para la operación de eliminar la tabla, en este caso, solo hay que cambiar el nombre de la tabla (figura 8.92).

Modificar la sentencia SQL de eliminiación de la tabla.

Figura 8.92: Modificar la sentencia SQL de eliminiación de la tabla.

En la figura 8.93, se muestran las nuevas operaciones en el Flujo de control.

Operaciones en el flujo de control.

Figura 8.93: Operaciones en el flujo de control.

Si ejecutamos el paquete varias veces, podemos comprobar que los datos no se añaden repetidamente.

Por último, podemos incluir el paquete de importación como una tarea más de este paquete. Esta tarea debería ejecutarse con éxito antes de la tarea del flujo de datos Tabla plana, para que los datos estén disponibles.

Añadimos una operación Tarea Ejecutar paquete, le cambiamos el nombre con el prefijo “bd_importar_” y el nombre de nuestra provincia (p.e., en mi caso se llama bd_importar_granada), y la conectamos a la tarea Tabla plana exigiendo que acabe con éxito (figura 8.94).

Incluir un paquete en el flujo de control.

Figura 8.94: Incluir un paquete en el flujo de control.

En la ventana de configuración de esta operación, en la propiedad PackageNameFromProjectReference, seleccionamos el paquete de importación de datos definido en el apartado 8.2 (figura 8.95).

Configuración de un paquete incluido en el flujo de control.

Figura 8.95: Configuración de un paquete incluido en el flujo de control.

Si ejecutamos el paquete, comprobamos que no hay errores (figura 8.96). Durante la ejecución, también se abre el paquete referenciado (si no estaba ya abierto) y también se muestra su resultado.

Resultado de la ejecución del paquete completo.

Figura 8.96: Resultado de la ejecución del paquete completo.

Por último, podemos comprobar que los datos se han almacenado en la BD, también con la codificación correcta (figura 8.97).

Datos almacenados en la BD.

Figura 8.97: Datos almacenados en la BD.

Ejercicio 8.4 Completa el flujo de control del paquete para obtener una tabla plana de manera que la tabla resultado sea borrada y creada en cada ejecución, y que integre el paquete de importación de datos como una tarea, usando los criterios de nomenclatura indicados en este apartado (captura una pantalla donde se muestre el resultado de ejecución de las tareas y otra que muestre el contenido de la tabla en la BD donde se pueda ver el nombre de algún municipio con tilde).

Dimensión Cuándo y Dimensión Dónde

Generaremos las dimensiones a partir de la tabla plana, en el apartado Flujo de datos destinado a cada una de ellas:

  • Seleccionando los campos incluidos en cada dimensión.

  • Eliminando las filas duplicadas.

  • Añadiendo una llave generada autonumérica.

  • Almacenando los datos en una tabla.

En la figura 8.98, se muestra la definición del Data Flow para la dimensión Dónde.

Definición del Data Flow para la dimensión Dónde.

Figura 8.98: Definición del Data Flow para la dimensión Dónde.

En primer lugar, obtenemos los datos de la tabla plana almacenada en la BD. A continuación, incluimos una operación Agregado que permite agrupar los valores de los campos seleccionados, en este caso los correspondientes a la dimensión, como se muestra en la figura 8.99.

Definición de la operación de agregación para la dimensión Dónde.

Figura 8.99: Definición de la operación de agregación para la dimensión Dónde.

En concreto, las columnas seleccionadas para esta dimensión son:

  • cod_municipio

  • municipio

  • capital

  • comarca

  • provincia

  • comunidad_autonoma

  • nivel_altitud

  • nivel_habitantes

No hay una operación específica en SSIS para añadir una llave generada autonumérica a la tabla. Sin embargo, como vamos a almacenar la tabla en la BD, podemos usar la funcionalidad de la propia BD en este sentido: cuando generemos la sentencia SQL en el paso de almacenamiento de los datos en la BD, la modificaremos para que cree una llave generada.

La modificación a hacer consiste en añadir una línea donde se genere la llave primaria con el nombre adecuado. En el caso de la dimensión Dónde, la línea a añadir sería:

  • [donde_key] INT NOT NULL IDENTITY(1,1) PRIMARY KEY

De manera que la sentencia SQL de creación de la tabla sería de la forma:

CREATE TABLE [dim_donde] (
  [donde_key] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  ...
)

Para la dimensión Cuándo, en su flujo de datos, realizaremos un desarrollo totalmente equivalente al explicado para la dimensión Dónde (adaptando columnas y nombres para este caso).

Ejercicio 8.5 Para las dimensiones Dónde y Cuándo:

  • Define el apartado Flujo de datos, para crear las tablas y obtener los datos de las dimensiones con sus campos y una llave generada autonumérica, usando criterios de nomenclatura como los mostrados en este apartado para la dimensión Dónde (para cada una muestra el resultado gráfico de su ejecución).

  • Modifica el “Flujo de control” de manera que se puedan ejecutar repetidas veces las transformaciones sin que se tengan en cuenta los resultados de las ejecuciones anteriores.

Padrón

Una vez generadas las tablas de las dimensiones, a partir de ellas y de la tabla plana, podemos generar la tabla de hechos para nuestro diseño multidimensional. Para ello, tenemos que sustituir en la tabla plana todos los campos de las dimensiones por su llave generada de manera que en la tabla de hechos solo queden las llaves generadas y las mediciones.

En la figura 8.100, se muestra la definición del flujo de datos para la definición de la tabla de hechos.

Definición del flujo de datos para la tabla de hechos.

Figura 8.100: Definición del flujo de datos para la tabla de hechos.

Partimos de la tabla plana almacenada en la BD. A continuación, buscamos los campos oportunos en cada una de las tablas de las dimensiones (operación Búsqueda) para obtener la llave generada asociada. Por último, almacenamos los datos en la BD.

La definición de la operación Búsqueda para la dimensión Dónde se muestra en la figura 8.101.

Definición de la operación Búsqueda para la dimensión Dónde.

Figura 8.101: Definición de la operación Búsqueda para la dimensión Dónde.

Podríamos usar todos los campos para realizar la búsqueda en la tabla de la dimensión Dónde, pero no es necesario. También podríamos haber pensado en usar solo el código del municipio, pero no sería suficiente porque un municipio ha podido tener asignados distintos niveles de número de habitantes a lo largo de la historia.

En el momento de crear la tabla de hechos, en lugar de limitarnos a definir los campos que la componen, podemos indicar cuáles de ellos forman su llave primaria, modificando la sentencia SQL generada añadiendo la línea siguiente:

  • PRIMARY KEY ([cuando_key], [donde_key])

De manera que la sentencia SQL de creación de la tabla sería de la forma:

CREATE TABLE [fact_padron] (
  [cuando_key] int,
  [donde_key] int,
  ...
  PRIMARY KEY ([cuando_key], [donde_key])
)

Ejercicio 8.6 Para los hechos:

  • Define el apartado Flujo de datos para crear la tabla de hechos con las llaves externas y las mediciones, usando criterios de nomenclatura como los mostrados en este apartado (muestra el resultado gráfico de su ejecución).

  • Modifica el “Flujo de control” de manera que se puedan ejecutar repetidas veces las transformaciones sin que se tengan en cuenta los resultados de las ejecuciones anteriores.

Ejercicio 8.7 Obtener una BD OLAP:

  • Crea una nueva BD SQL Server cuyo nombre sea el nombre de la provincia asignada y el sufijo "_olap" (p.e., en mi caso se llamará granada_olap).

  • Define las operaciones necesarias para incluir en la nueva BD solo las tablas de hechos y de dimensiones, renombrándolas para que su nombre sea “padron_”, “cuando_” y “donde_”, y el sufijo del nombre de la provincia (p.e., en mi caso, la BD solo contendrá las tablas padron_granada, cuando_granada y donde_granada), de manera que estas operaciones se ejecuten siempre junto a las operaciones definidas anteriormente (captura una pantalla donde se muestre la definición de las operaciones y otra con la BD resultado).

8.4 Modificaciones

Ejercicio 8.8 Una vez definido el proceso de transformación, realiza las siguientes modificaciones, para cada una, indica qué elementos se han visto afectados y qué modificaciones ha habido que hacer en ellos:

  • Define en la dimensión Dónde un campo llamado nivel_superficie en función del campo superficie_ha (que debería ser de tipo real), con 3 niveles, con los límites que consideres oportunos según tu criterio (captura una pantalla de la definición y otra del resultado en la dimensión en la BD OLAP).

  • Define en la dimensión Cuándo un campo llamado decenio de manera que a cada año le correspondan las tres primeras cifras seguidas de un cero (captura una pantalla de la definición y otra del resultado en la dimensión en la BD OLAP).

Reestructuración de las transformaciones.

Figura 8.102: Reestructuración de las transformaciones.

Ejercicio 8.9 Re-estructura el contenido de los paquetes de manera que tengamos:

  • Un paquete principal llamado “transformar_” y el nombre de la provincia (p.e., en mi caso transformar_granada). Ese paquete organice la ejecución del resto de paquetes (figura 8.102).

  • El paquete llamado “generar_ft_” y el nombre de la provincia (p.e., en mi caso generar_ft_granada) que genere la tabla plana a partir de todos los datos de inicio.

  • El paquete llamado “generar_dm_” y el nombre de la provincia (p.e., en mi caso generar_dm_granada) que genere el esquema multidimensional a partir de la tabla plana.

  • El paquete llamado “exportar_olap_” y el nombre de la provincia (p.e., en mi caso exportar_olap_granada) que exporte el esquema multidimensional desde la BD de trabajo a la BD destinada a OLAP.

Muestra el resultado gráfico de la ejecución de cada uno de los paquetes.

Bibliografía

Knight, Brian, Devin Knight, Mike Davis, and Wayne Snyder. 2012. Knight’s Microsoft Sql Server 2012 Integration Services 24-Hour Trainer. Wrox Press.


  1. EDI (Entorno de Desarrollo Integrado) de Microsoft.↩︎

  2. Si en algún momento (por ejemplo, al cerrar un proyecto y volverlo a abrir) desaparece esta ventana, se puede volver a mostrar seleccionando Cuadro de herramientas de SSIS en el menú contextual del área de diseño.↩︎

  3. Cuando no necesitemos que se muestren, desactivaremos esta función de forma similar.↩︎

  4. En la ventana de selección de archivos, por defecto solo muestra los de extensión TXT, tenemos que seleccionar la extensión CSV.↩︎

  5. Podríamos reutilizar la conexión definida automáticamente en el proceso de importación de estos datos, definiéndola como global al proyecto (desde su menú contextual, opción Convertir a conexión de proyecto), pero vamos a definir una nueva para ver cómo se lleva a cabo esta operación.↩︎

  6. Por defecto, las conexiones son particulares de cada paquete.↩︎

  7. “ft_” de Flat Table.↩︎