Proceso ETL para la carga de la Dimensión Tiempo. Ejemplo de uso de la ETL Talend.

Proceso ETL para la carga de la Dimensión Tiempo. Ejemplo de uso de la ETL Talend. respinosamilla 25 Febrero, 2010 - 09:50

Una vez identificados los origenes de datos, podemos proceder a la construcción de las tablas físicas de nuestro modelo y al desarrollo de los procesos de llenado. Empezaremos el proceso con la Dimensión Tiempo. Como ya indicamos, esta dimensión no dependera de nuestro ERP u otros sistemas externos, sino que la construiremos a partir de los calendarios. Generaremos todos los registros necesarios para esta tabla para un periodo de 20 años, que va desde el 01 de Enero de 2000 (para los datos históricos anteriores que también cargaremos en nuestro DW)  hasta el 31 de Diciembre de 2020.

La definición física de la tabla no ha variado tras el análisis de los origenes de datos, y será la siguiente:

Diseño Fisico Tabla Dimensión Tiempo en MySql

Los procesos que tendremos que implementar utilizando Talend serán los siguientes (tal y como vimos en una entrada anterior del Blog):

 

Transformaciones para la creación de la Dimensión Tiempo

Vamos a utilizar Talend con la opción de generación en lenguaje Java (también podriamos utilizar el lenguaje PERL). El utilizar Java significa que todos los procesos y transformaciones que definamos se van a “traducir” a lenguaje Java a nivel interno. Aunque no es estrictamente necesario conocer el lenguaje Java para trabajar con Talend, su conocimiento nos va a facilitar mucho el trabajo con la herramienta y nos va a permitir definir nuestro propio código para la transformaciones o procesos que no se incluyen de forma estandar.

Talend esta basado en el entorno de desarrollo Eclipse. Es un entorno gráfico con amplias funcionalidades donde la definición de las transformaciones se realiza de una forma visual muy intuitiva, seleccionando y arrastrando componentes y estableciendo relaciones entre ellos. Incluye un entorno de Debug para poder analizar los procesos y sus errores, así como la posibilidad de establecer trazas para realizar un seguimiento de los procesos cuando los estamos desarrollando y validando.

En la imagen siguiente vemos un ejemplo de un job desarrollado con Talend:

Entorno gráfico de la herramienta Talend

Los componentes  mas importantes dentro del entorno gráfico son los siguientes:

Repositorio de Objetos en Talend

Repositorio: incluye todos los objetos que podemos definir utilizando Talend, clasificado en un arbol de la siguiente manera:

  • Business Models: Talend dispone de una herramienta gráfica sencilla donde podemos definir nuestros modelos de negocio. En esta carpeta se ubicaran los diferentes modelos de negocio que hayamos “dibujado” utilizando Talend. La herramienta contiene los elementos gráficos mas habituales.
  • Job Designs: un proyecto de transformación o integración de datos se compone de multiples procesos o jobs que podemos definir y clasificar en una estructura de carpetas para organizarlos y clasificarlos. En esta sección vemos los diferentes jobs que hemos definido y la forma en la que los hemos clasificado.
  • Contexts: son los contextos de ejecución de los procesos. En ellos podemos definir constantes o parametros que se nos piden al ejecutar un proceso y que podemos utilizar en los diferentes componentes de un job. Los contextos también se pueden cargar en tiempo de ejecución provenientes de ficheros.
  • Rutinas: es el lugar donde podemos ver las rutinas de código desarrolladas por Talend(que luego podremos utilizar en las transformaciones) y donde nosotros podremos añadir nuestras propias rutinas para realizar operaciones, cálculos o transformaciones para las que no dispongamos de un método estandar. Las rutinas se programan en Java (o en Perl en el caso de haber seleccionado ese lenguaje).
  • SQL Template: son templates de sentencias SQL predefinidas que podremos utilizar o personalizar.
  • Metadata: es el lugar donde vamos a definir los metadatos del proyecto. Son definiciones de componentes que luego vamos a poder reutilizar en todos los procesos de diseño de las transformaciones. Por ejemplo, en el metadatos podremos definir conexiones a bases de datos, recuperar los esquemas de una base de datos y tenerlos documentados (con sus tablas, vistas, etc), definir sentencias Sql, definir patrones de diferentes tipos de ficheros, etc. Esto nos permite tener los elementos definidos en un único sitio y reutilizarlos a lo largo de los procesos. El Repositorio de metadatos centraliza la información de todos los proyectos y garantiza la coherencia en todos los procesos de integración. Los metadatos relacionados con los sistemas origen y destino de los procesos de integración se cargan fácilmente en el Repositorio de metadatos a través de utilidades avanzadas de analisis de las bases de datos o archivos, facilitada por diversos asistentes. Las características definidas en el Metadata son heredadas por los procesos que hacen uso de ellas.
  • Documentacion: podemos cargar en el proyecto los ficheros de documentacion de nuestros análisis y desarrollos, clasificandolos por carpetas. La vinculación se puede hacer con un link o cargando directamente el fichero en el repositorio. Esta utilidad nos permite tener centralizado en un único lugar todos los elementos de un proyecto de integración de datos.
  • Papelera de reciclaje: los objetos que borramos va a la papelera de reciclaje y de ahi los podremos recuperar en el caso de ser necesario.

Job Designer: es la herramienta desde la que manipulamos los diferentes componentes que conforman un job, estableciendo relaciones entre los diferentes elementos.

 

Job Designer

 

Cuando estamos trabajando con un job, en la parte inferior disponemos de un conjunto de pestañas desde donde podemos realizar varias acciones, tales como definir un contexto para el job, configurar las propiedades, ejecutar el job y establecer la forma de ejecución, modificar las propiedades de los componentes de los jobs, programar los jobs, establecer jerarquias en los jobs, etc.

Finalmente, en la parte derecha de la aplicación tenemos la Paleta de Componentes, que son los diferentes controles que nos proporciona Talend para utilizar en nuestros jobs. Se encuentran clasificados según su función.

Paleta de Componentes

 

Algunos de los componentes disponibles en Talend son los siguientes:

- Business Intelligence: grupo de conectores que cubre las necesidades de lectura o escritua en bases de datos multidimensionales u olap, salidas hacia reports Jasper, gestión de cambios en la base de datos para las dimensiones lentamente cambiantes, etc (todos ellos relacionados con Business Intelligence).

- Business: conectores para leer y escribir de sistemas tipo CRM (Centric, Microsoft CRM, Salesforce, Sugar, Vtiger) o para leer y escribir desde sistemas Sap. También permiten trabajar con el gestor documental Alfresco.

- Custom Code: componentes para definir nuestro propio código personalizado y poder utilizarlo integrado con el resto de componentes Talend. Podemos escribir componentes en Java y Perl, así como cargar librerias o personalizar comandos Groovy.

- Data Quality: componentes para la gestión de calidad de datos, como filtrados, calculos CRC, busquedas por lógica difusa, reemplazo de valores, validación de esquemas contra el metadata, limpieza de duplicados, etc.

- Databases: conectores de conexión, entrada o salida de las bases de datos mas populares ( AS400, Access, DB2, Firebird, Greenplum, HSQLdb, Informix, Ingres, Interbase, JavaDB, LDAP, MSSQL Server, MaxDB, MySql, Netezza, Oracle, Paraccel, PostreSQL, SQLite, Sas, Sybase, Teradaba, Vertica).

- ELT: componentes para trabajar con las bases de datos en modo ELT (con las tipicas transformaciones y procesos de este tipo de sistemas).

- Fichero: controles para la gestión de ficheros (verificacion existencia, copia, borrado, lista, propiedades), para lectura de ficheros de diferentes formatos (texto, excel, delimitados, XML, mail, etc) y para escritura en ellos.

- Internet: componentes para acceder a contenidos almacenados en internet, como servicios Web, flujos RSS, SCP, Mom, Email, servidores FTP y similares.

- Logs & Errors: controles para la gestión de errores y logs en la definición de procesos.

- Miscelanea: componentes varios, como ventanas de mensajes, verificación de funcionamiento de servidores, generador de registros, gestión de contextos de variables, etc.

- Orchestration: componentes para generar las secuencias y la tareas de orquestación y procesamiento de los jobs y subjobs definidos en nuestras transformaciones (generación loops, ejecución de jobs previos o posteriores, procesos de espera para ficheros o datos, etc).

- Processing: componentes para procesamiento de los flujos de datos,  como agregación, mapeos, transformaciones, filtrados, desnormalización, etc.

- System: componentes para interaccion con el sistema operativo (ejecución de comandos, variables de entorno, etc).

- XML: componentes para trabajo con estructuras de datos XML, con operaciones de parsing, validación o creación de estructuras.

Para haceros una idea de la forma de trabajar con Talend, es interesante ver una demo de 5 minutos en la Web de Talend (acceder desde este link). También podeis ver el siguiente video de demostración de como generar datos de test en una tabla Mysql.

Igualmente, podeís descargaros para profundizar más el Manual de Usuario de la herramienta y la Guia de Referencia de Componentes (ambos en inglés) en este link.

Job en Talend para el llenado de la Dimensión Tiempo

Ahora que ya conocemos un poco en que consiste Talend, vamos a profundizar viendo un ejemplo práctico. Necesitamos generar un flujo de fechas que comenzando en 01.01.2000, llegue hasta el dia 31.12.2020 (con eso cargamos las fechas de 20 años en la dimensión).

Para hacer el proceso vamos a definir los siguientes pasos:

1) Loop que se ejecuta 10.000 veces, con un contador que vas desde 0 a 9999 (utilizando el componente tLoop del grupo Orchestration).

2) El loop llama al control Generador de Registros, que genera un registro con la fecha 01.01.2000 (utilizando el componente tRowGenerator del grupo Miscelaneous).

3) el RowGenerator pasa la fecha a un transformación (MAP), que le suma a la fecha el contador del paso 1 (con eso vamos incrementando la fecha de partida dia a dia y generando todas las fechas necesarias). Utilizamos el componente tMap del grupo Processing.

4) las fechas generadas las pasamos a otra transformación (MAP), donde para cada fecha, generamos todos los atributos de la dimensión tiempo según la tabla de transformación que hemos indicado anteriormente (mes, año, dia, dia de la semana, trimestre, semestre, etc). Utilizamos el componente tMap del grupo Processing.

 

Hemos tenido que definir unas rutinas en java para la generación correcta del número de semana en el año de cada fecha, así como para la generación de los datos de trimestres, semestres, dia festivo y fin de semana. Por ejemplo, para la generación de las semanas hemos escrito el siguiente código en Java:

// template routine Java
package routines;
import java.util.Calendar;
import java.util.Date;
public class fechas {
public static String semana_del_anyo(Date date1) {
Calendar c1 = Calendar.getInstance();
c1.set(Calendar.DAY_OF_WEEK,Calendar.MONDAY);
c1.setMinimalDaysInFirstWeek(1);
c1.setTime(date1);
int semana = c1.get(Calendar.WEEK_OF_YEAR);
if (semana < 10) {
return ( 0 + Integer.toString(semana));
} else {
return (Integer.toString(semana));
}}

5) Filtramos los registros para desechar los que son mayores de 31.12.2020, pues esos nos los queremos cargar en la base de datos. Utilizamos el componente tFilterRow del grupo Processing.

6) Insertamos los registros en la base de datos Enobi, en la tabla DWD_TIEMPO, utilizando el componente tMySqlOutput, del grupo Databases, MySql. Si los registros ya existen en la base de datos, se actualizan.

El esquema completo del Job sería el siguiente:

 

Job llenado Dimensión Tiempo

Esta ha sido nuestro primera toma de contacto con una herramienta ETL. Sin programar (o casi, pues hemos tenido que preparar una rutina en java para el tratamiento de las semanas y otros atributos de las fechas),  hemos llenado con datos reales la primera tabla/dimensión de nuestro modelo.

Bases de Datos OpenSource. ¿Porque elegimos Mysql para nuestro proyecto?.

Bases de Datos OpenSource. ¿Porque elegimos Mysql para nuestro proyecto?. respinosamilla 25 Febrero, 2010 - 09:50

Antes de continuar con la construccion de los procesos ETL para el resto de las dimensiones del proyecto, vamos a hacer una pausa para explicar el motivo de elegir MySql como gestor de base de datos para el proyecto ENOBI.

En primer lugar, el económico. Estamos realizando un proyecto utilizando productos OpenSource o productos licenciados libremente por los fabricantes (como Microstrategy Reporting Suite). Seguramente si estuviesemos en un gran proyecto, elegiriamos una opción de base de datos propietaria, como Oracle ( que es para muchos la mejor opción por estudios, prácticas o consenso). Podeis ver el estudio comparativo de bases de datos realizado por Forrester ( gracias a todobi.com ).

Estudio Forrester 2009 sobre Gestores de Base de Datos

 

En segundo lugar, estamos buscando productos con reconocido prestigio, fiabilidad, velocidad, rendimiento, facilidad de administración y conexión con otros productos, bien documentados, con una buena evolución y soporte. Productos de los que sea fácil obtener información, con buenas herramientas, y para los que incluso podamos recibir cursos de formación si fuese necesario. Productos que esten siendo utilizados en muchos entornos productivos y que nos den la suficiente confianza.

Ademas, los gestores de bases de datos OpenSource hace tiempo que dejaron de ser un experimiento y ya son una alternativa real para las empresas (incluso aparecen en los cuadrantes Gartner). Son productos cada vez mas evolucionados, con mas funcionalidades y las empresas que los desarrollan tienen también cada vez más volumen de negocio (cuestión importante para continuar la evolución de los productos).

Los productos OpenSource mas conocidos son:

MySQL, PostgreSQL, MaxDB, Firebird, Ingres , MonetDB, LuciDb.

Podeis echar un vistazo al directorio EOS de productos OpenSource para ver otras bases de datos Open, así como valoraciones de estas y casos reales de uso.

Existen multitud de comparativas sobre las bases de datos OpenSource, incluso estudios comparándolas con productos propietarios (ver comparativa Oracle/Mysql). La elección de una u otra dependerá del tipo de proyecto, el uso que vayamos a dar a la base de datos (Servidor Web, desarrollo aplicaciones, Dw), posibilidades de integración con otros productos, plataformas hardware o sistema operativo a utilizar, etc.

En nuestro caso, hemos decidido trabajar con MySql o PostgreSql, y vamos a centrarnos en analizar cual de los dos productos nos quedamos.

Existen multitud de comparativas, aunque algunas de las mas interesantes son:

Comparativa MySql vs PostGreSql.

Comparativa MySql vs PostGreSql: ¿cuando emplear cada una de ellas?

PostgreSQL Vs MySQL: Comparative Review: es una comparativa mas reciente donde se tiene en cuenta la evolución de los dos productos en los ultimos años.

Si analizamos las bases de datos centrandonos en el ámbito de los Datawarehouse, también existen estudios que analizan las diferentes opciones existentes ( otra vez gracias a todobi.com ). El estudio original, realizado por Jos van Dongen, lo tenemos aquí.

 

Comparativa BD-DW (estudio Jos van Dongen)

 

Incluso, tenemos en la Wikipedia un estudio comparativo de las bases de datos relacionales mas importantes, al que podemos acceder desde aquí.

Comparativa BD Wikipedia

 

Tenemos mucha información, pero es el momento de las conclusiones y de elegir el producto que utilizaremos en nuestro proyecto, teniendo en cuenta las siguientes consideraciones:

1) Vamos a construir un DataWarehouse, por lo que tendrá prioridad para nosotros la velocidad de acceso a los datos (habrá cargas de datos regulares cuando estemos alimentando el DW a partir de los sistemas operacionales, y la mayoria de accesos serán para consultar dichos datos).

2) Para mejorar el rendimiento de la base de datos, en la tabla de hechos (que recordemos es la que tendra millones de registros, pues en ella se guardan todas las transacciones de ventas), realizaremos particionado. Eso significa que para la misma tabla lógica, habrá diferentes tablas físicas y toda la gestión de dicho particionado recaera sobre el motor de base de datos, siendo el proceso totalmente transparente para el usuario (también habría cabido la opción de gestionarlo nosotros en los procesos de carga ETL separando los datos en tablas distintas según un criterio determinado (por ejemplo, el año), teniendo en cuenta que esto es soportado, por ejemplo, por las herramientas de Microstrategy, que son capaces de generar las sentencias SQL apropiadas para leer información de las diferentes tablas donde la información está repartida).

3) Es la primera vez que trabajamos con ambas bases de datos, y en ambos casos buscaremos la facilidad de trabajo, la existencia de herramientas gráficas y de administración, la documentación, etc.

Teniendo en cuenta estas consideraciones y los estudios y comparativas que hemos descrito anteriormente, decidimos trabajar con MySql por los siguiente motivos:

1)Velocidad: aunque en algunos estudios PostgreSql es mejor para entornos donde la integridad de datos es fundamental (como en el desarrollo de aplicaciones), a nivel de rendimiento MySql es mejor. En concreto, cuando utilizamos el tipo de motor MyIsam, el rendimiento de MySql es mucho mejor. Este será el tipo de Engine con el que definiremos todas nuestras tablas en el DW. El motor InnoDb es mas lento y esta orientado a aplicaciones donde la actualización e integridad es mas importante.

2)Particionado: aunque ambas plataformas permiten particionado, la gestión utilizando Mysql es mas sencilla.

3)Herramientas gráficas, documentación, plataformas: Mysql proporciona una amplia documentación (muy completa) y multitud de herramientas gráficas de gestión y de conectividad. PostgreSql también cumpliría los requisitos en el tema de documentación y plataformas soportadas. También es importante para nosotros poder disponer en Mysql de una herramienta de Diseño de Bases de datos, como es MySql Workbench, que nos permite definir nuestros modelos relacionales, generar las sentencias SQL y construir la base de datos, así como realizar ingenieria inversa (construir el módelo de datos a partir de una base de datos existente) o comparar el modelo definido en la herramienta con el existente en la base de datos.

Algunos de los usuarios mas destacados de Mysql son los siguientes:

  • Amazon.com
  • Cox Communications – La cuarta televisión por cable más importante de EEUU, tienen más de 3.600 tablas y aproximadamente dos millones de inserciones cada hora.
  • Craigslist
  • CNET Networks
  • Digg – Sitio de noticias.
  • flickr, usa MySQL para gestionar millones de fotos y usuarios.
  • Google – Para el motor de búsqueda de la aplicación AdWords.
  • Joomla!, con millones de usuarios.
  • phpBB, Uno de los más famosos sitios de foros, con miles de instalaciones y con millones de usuarios.
  • LiveJournal – Cerca de 300 millones de páginas servidas cada día.[2]
  • NASA
  • NetQOS, usa MySQL para la gestión de algunas de las redes más grandes del mundo como las de Chevron, American Express y Boeing.
  • Nokia, usa un cluster MySQL para mantener información en tiempo real sobre usuarios de redes de móviles.
  • Omniture
  • Sabre, y su sistema de reserva de viajes Travelocity
  • Slashdot – con cerca de 50 millones de páginas servidas cada día.
  • Wikipedia, sirve más de 200 millones de consultas y 1,2 millones de actualizaciones cada día, con picos de 11.000 consultas por segundo.
  • WordPress, con cientos de blogs alojados en él.
  • Yahoo! – para muchas aplicaciones críticas.

Si quereis ampliar la información sobre MySql, la entrada en la wikipedia es bastante completa.