ETL y Modelado de datos
Descripción del proyecto
En este proyecto utilizaremos los datos de una empresa de atención al cliente, almacenados en diferentes tablas y formatos, para crear un proceso ETL (Extracción Transformación y Carga) que limpie y transforme sus imperfecciones y fallos y los almacene en un Datawarehouse con un modelado óptimo para su posterior visualización o análisis. Utilizaremos SSIS en Visual Studio para crear el ETL y SQL Server como Datawarehouse.
Tras conversar con el cliente y estudiar los datos llegamos a las siguientes conclusiones:
Transformaciones para la tabla de Tickets:
1. Del campo [Ubicación] extraer el texto que se encuentra después del guion, este será el código de la agencia donde donde se debe atender el ticket.
Ejemplo: EDIFICIO LIMA – 191000 → 191000
2. En el campo [Estado] el valor “Terminado” y “Cerrado” representan lo mismo, por lo tanto deberemos remplazar el valor «Terminado» por «Cerrado».
3. Crear el campo [fecha_real_fin] a partir de los siguientes valores:
Si [fecha_término] es Null, [fecha_real_fin] = [fecha_cierre]. Si no es Null, [fecha_real_fin] = [fecha_término]
4. Crear un JOIN entre las tablas Tickets y Categoría mediante el campo [categoria] y reemplazarlo por el campo [categoriaID]. En caso de que el valor sea NULL, [categoriaID] = 10
5. Crear un JOIN entre las tablas Tickets y Tipo mediante el campo [tipo] y reemplazarlo por el campo [tipoID]. En caso de que el valor sea NULL, [tipoID] = 100
6. Crear un JOIN entre las tablas Tickets y Detalle mediante el campo [detalle] y reemplazarlo por el campo [detalleID]. En caso de que el valor sea NULL, [detalleID] = 100
Transformaciones para la tabla de Atenciones:
1. En el campo [costo] reemplazar el valor «SIN COSTO» por 0 o NULL que rompe el valor numérico de la columna.
2. En el campo [tipo_ticket] convertir a mayúsculas todas las palabras y quitar los espacios en blanco de delante y detrás del texto.
3. Además en el campo [tipo_ticket] aplicaremos también la siguiente regla:
Si los primeros 4 caracteres = «DIFE», [tipo_ticket] = «FLAT»
Si los primeros 4 caracteres = «VARI», [tipo_ticket] = «VARIABLE»
Por último juntaremos la tabla de hechos Tickets y los campos [fecha_programada], [service_desk], [tipo_ticket], [proveedor], [costo], [numero_os] de la tabla de hechos FactAtenciones para crear una única tabla de hechos que, junto a las dimensiones DimTipo, DimCategoria, DimDetalle y DimFecha, darán forma a nuestro modelado de datos en estrella final.
Proceso ETL
Ya sabiendo los pasos a tener en cuenta a la hora de configurar nuestro ETL nos ponemos manos la obra. Para este proceso abrimos el entorno de desarrollo Micrososft Visual Studio y creamos un nuevo proyecto de SQL Server Integration Services.
Extracción
Sabiendo que nuestros datos vienen en tablas distribuidas en diferentes archivos y formatos (Tickets.csv, Atencione.csv, Tipo.txt, Categoría.txt y Detalle.txt) creamos 2 contenedores de secuencias, 1 para los archivos .csv y otro para los archivos .txt.
En el primer contenedor de secuencias añadimos una tarea de ejecución de código SQL y 2 tareas de flujo de datos, una por cada tabla y en el segundo añadiremos también 1 tarea de ejecución de código SQL y 3 flujos de datos.
En cada uno los Data Flows añadimos un origen de datos de archivos planos (.csv/.txt), una tarea de conversión de datos y un destino de OLE DB que se conectará a nuestro servidor de SQL Server. A continuación conectamos cada uno de los orígenes de datos a su correspondiente archivo y creamos la base de datos «STAGING» en SQL Server con las tablas en las que almacenaremos los datos de las 2 tablas de hechos (BaseTickets y BaseAtenciones).
En el caso de los archivos .txt, como sólo tienen 2 columnas generaremos las tablas de la base de datos en la propia tarea de destino OLE DB. Ya con los orígenes y destinos de cada archivo listos simplemente nos queda configurar la tarea de conversión de datos para cada tabla y añadir el código SQL de truncamiento de tablas a los 2 contenedores de secuencias.
Transformación
Con los datos almacenados en diferentes tablas de la base de datos «STAGING» de SQL Server ya sólo nos queda darles forma y cargarlos en el Datawarehouse, para ello seguiremos utilizando código SQL en SSIS. Crearemos un nuevo contenedor de secuencias con una Tarea de ejecución SQL con el truncamiento de estas nuevas tablas y 3 flujos de datos:
1. Flujo de datos – Limpieza de BaseTickets:
El primer Data Flow, Limpieza de tickets, tendrá como origen de datos una consulta SQL con las 3 primeras transformaciones que hemos nombrado anteriormente de la tabla de Tickets y como destino una nueva tabla que llamaremos ETL_tickets1 de la cual se alimentará el próximo flujo.
2. Flujo de datos – Maestro de tickets:
En este segundo flujo le aplicaremos a la tabla ETL_Tickets1 los JOIN con las tablas Tipo, Categoría y Detalle y sus pertinentes transformaciones, que hemos explicado en la descripción del proyecto, como origen de datos y como destino crearemos una nueva tabla en la BBDD con el nombre de ETL_tickets2.
3. Flujo de datos – Maestro de atenciones:
En este último flujo aplicaremos a la tabla BaseAtenciones las transformaciones pertinentes mediante una consulta SQL como origen de datos y como destino crearemos la tabla ETL_Atenciones en la base de datos STAGING de SQL Server que contiene únicamente las 6 columnas que nos interesan.
Carga
Teniendo ya los datos transformados y almacenados en la base de datos «STAGING» sólo nos queda cargarlos en la Base de datos de SQL Server «DATAWAREHOUSE» a través del JOIN de las tablas ETL_tickets2 y ETL_atenciones1 que formarán nuestra tabla de hechos FactAtenciones y a través las tablas Tipo, Categoría y Detalle que junto a una nueva tabla Calendario que crearemos con código SQL formarán las dimensiones del modelo.
Dividiremos por lo tanto el paquete SSIS en 3 bloques, el primero será un truncamiento de las nuevas tablas FactAtenciones, DimTipo, DimCategoria y DimDetalle, el segundo será un comntenedor de secuencias con los flujos de las dimensiones y el tercero será el flujo de la tabla de hechos.
Resumen del proceso ETL:
Staging data
Datawarehouse
