El Rincon del BI

Descubriendo el Business Intelligence…

13.2. ETL para carga Dimension Producto. Mas ejemplos de Talend. Uso de logs, metricas y estadisticas.

Posted by Roberto Espinosa en 9 enero 2010


El siguiente proceso ETL que vamos a abordar es el de la carga de la Dimensión Producto. En esta dimensión se ubicarán, como ya vimos, los diferentes atributos relacionados con el producto que utilizaremos en nuestro sistema BI. Después de la revisión de la estructura física, teniendo en cuenta los origenes de datos, la estructura física de la tabla es la siguiente:

Diseño Fisico Tabla Dimensión Producto 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 Producto

Para el desarrollo del proceso con Talend, vamos a ir un paso mas alla, definiendo información de logs, registro de estadísticas y métricas. Esto nos permitira conocer otra funcionalidad de Talend, y orquestar la ejecución de los jobs (para permitir, por ejemplo, la recuperación de la fecha de ultima ejecución del job para poder buscar los cambios en el maestro de materiales desde la ultima ejecución del proceso de traspaso), controlar los errores de ejecución y notificar por email en el caso de que se produzca algún problema.

El esquema completo del job con todos los pasos sería el siguiente (a continuación veremos de forma detallada cada uno de los pasos incluidos):

Proceso ETL en Talend completo para la dimensión Producto

Los pasos del proceso ETL para llenar la Dimensión Producto serán los siguientes:

1) Ejecución de un prejob que recuperará la fecha de ultima ejecución del proceso y registrara en el log el inicio de la ejecución del job. Además, se lanzará un logCatcher (para recoger las excepciones Java o errores en el proceso), que generará el envio de un email de aviso en el caso de que se produzca algún problema en cualquier paso del job.

2) A partir de la fecha de ultima ejecución, recuperamos del maestro de materiales en el ERP, todas las modificaciones o altas de productos realizadas en la tabla desde dicha fecha.

3) Realizamos una sustitución de valores erroneos o en blanco en los registros seleccionados según los criterios establecidos.

4) Completamos el mapeo de dimensión Producto, llenando el resto de campos que provienen de otras tablas en la base de datos (los campos de lookup),  con sus correspondientes consultas SQL.

5) En el mapeo se podrán generar materiales erróneos que habrá que verificar y corregir en el sistema origen (por tener valores incorrectos). La lista de materiales se introduciran en un fichero excel. Igualmente, contaremos el número de registros leidos desde el sistema origen correctos para guardarlos en las tablas de metricas.

6) Verificamos que realmente haya modificaciones con los datos existentes en la base de datos del DW, y para los registros que si tienen modificaciones (o si son nuevos registros), realizamos la actualización. En principio, no vamos a realizar gestión de Dimensiones Lentamente Cambiantes, sino que siempre tendremos la foto de los datos tal y como están los ficheros maestros en el momento actual (mas adelante si gestionaremos una dimensión con esta casuistica para conocer los componentes de los que dispone Talend para este cometido).

7) Concluimos el proceso guardandonos en el log el correspondiente mensaje de finalización correcta del proceso.

Antes de profundizar y ver en detalle cada unos de los pasos, vamos a ver la forma de activar en Talend la gestión de logs, estadisticas y metricas. El significado de cada uno de estos elementos es el siguiente:

  • Logs: sería el registro de los mensajes de error generados por los procesos, las excepciones Java al ejecutar o los mensajes generados por nosotros utilizando los componentes tDie o tWarn (para generar diferentes tipos de mensajes).
  • Stats (estadísticas): es el registro de la información estadística de los diferentes pasos y componentes que forman un job. Para cada paso, se puede activar el registro de estadísticas seleccionando la opción tStatCatcher Statistics. Se guarda información de cada paso, cuando empieza, cuando termina, su status de ejecución, duración, etc.
  • Meter, volumetrics  (métricas): es el registro de metricas (numero de registros de un flujo) que podemos intercalar en los procesos utilizando el control tFlowMeter.

Talend nos permite trabajar de dos maneras con todos estos registros (se pueden combinar): una sería gestionando nosotros mismos los logs, stats y meters que se generan (recolectandolas en tiempo de ejecución con los componentes tLogCatcher, tStatcatcher o tFlowMeterCatcher en cada job y dandoles el tratamiento oportuno) o bien activar la gestión automática a nivel de cada proyecto o job, seleccionando en que lugar queremos guardar los registros generados(con tres opciones: visualización en consola, registro en ficheros de texto planos o registro en base de datos). Con esta ultima opción, toda la información que se genere quedara registrada en tablas y será mas fácil explotarla para revisión de procesos realizados, orquestación de procesos, verificación de cuellos de botella, corrección de errores, etc.

Para activar la gestión a nivel de proyecto seleccionaremos la opción de menú Archivo –> Edit Project Properties, opción Job Settings, Stats & Logs (tal y como vemos en la imagen):

Opciones de estadísticas y logs a nivel de Proyecto

Estas propiedades quedan habilitadas para todos los jobs del proyecto, aunque luego se pueden ajustar en cada job según nos interese (puede haber jobs para los que no queremos guardar nada de información). Esto lo modificaremos en las propiedades de cada job, en la pestaña Stats & Logs. En principio, mandan las propiedades establecidas en el proyecto, pero se pueden generar excepciones (llevar los logs a un sitio diferente o no generar) para un job en concreto:

Opciones de estadísticas y logs a nivel de Job

Una vez realizada esta aclaración, veamos en detalle cada uno de los pasos del ETL:

1) Ejecución de un prejob que recuperará la fecha de ultima ejecución del proceso y registrara en el log el inicio de la ejecución del job. Además, se lanzará un logCatcher (para recoger las excepciones Java o errores en el proceso), que generará el envio de un email de aviso en el caso de que se produzca algún problema en cualquier paso del job. Los componentes utilizados en este paso inicial son los siguientes:

  • Lanzador Prejob (componente tPrejob): sirve para realizar el lanzamiento de un pretrabajo, anterior al proceso principal.
  • Ultima Fecha Ejecución (componente tMySqlInput): recuperamos de la tabla de registro de logs, la ultima fecha de ejecución correcta del job. Esta fecha nos servira de fecha de referencia para buscar las altas/modificaciones de productos en el ERP desde la ultima ejecución. Sino hay una ejecución anterior del job, construimos una fecha de referencia para lo que sería la carga inicial de la dimensión producto.
  • Mensaje Log Inicio (componente tWarn): genera un mensaje de log indicando que se comienza la ejecución del job.
  • Set Variable Fecha (componente tSetGlobalVar): la fecha de ultima ejecución recuperada en el paso anterior se registra en una variable global que se podrá utilizar posteriormente en cualquier paso del job.
  • Control Errores (componente tLogCatcher): activamos el componente que “escuchara” durante toda la ejecución del job, esperando que se produzca algún tipo de error. En ese momento se activara para recuperar el error y pasarlo al componente siguiente para el envio de un email de notificación.
  • tFlowtoIterate: convertimos el flujo de registros de log a una iteración para poder realizar el envio del correo electrónico.
  • Envio Email Notif (componente tSendMail): generamos el envio de un email de notificación de errores, incluyendo el paso donde se paro el proceso, y el mesaje de error generado. Es una forma de avisar que ha fallado algo en el proceso.

Para ver en detalle como hemos definido cada componente, podeis pulsar en el link de cada componente o para ver la documentación HTML completa generada por Talend pulsando aquí.

2) A partir de la fecha de ultima ejecución, recuperamos del maestro de materiales en el ERP, todas las modificaciones o altas de productos realizadas en la tabla desde dicha fecha.

Job DimProducto - Lectura Maestro Productos Sap

Para ello, utilizamos el componente LECT_MAESTRO_PRODUC TO del tipo tOracleInput (ver detalle aqui ), que nos permite acceder a la base de datos de Sap, que en este caso es Oracle. También podiamos haber realizado la lectura de Sap utilizando los componentes tSAPConnectiony tSAPInput). Observar la sentencia SQL ejecutada en el componente, y ver como combinamos el texto escrito con el uso de una variable global y el uso de funciones de Taled (que también podrían ser Java).

3) Realizamos una sustitución de valores erroneos o en blanco en los registros seleccionados según los criterios establecidos. Utilizando el componente AJUSTE_CAMPOS, del tipo tReplace (ver detalle aqui), con el que podemos buscar valores existentes en los registros recuperados (utilizando cadena regulares) e indicar una cadena de sustitución. Puede ser util para cualquier transformación que haya que realizar para determinados valores, para normalizar, para corregir registros erróneos, etc. En nuestro caso, la vamos a utilizar para sustituir registros sin valor (se podría haber optado también por rechazar dichos registros).

Job DimProducto - Ajuste Campos

4) Completamos el mapeo de dimensión Producto, llenando el resto de campos que provienen de otras tablas en la base de datos (los campos de lookup),  con sus correspondientes consultas SQL. Para ello utilizamos el componente MAPEO_PRODUCTO del tipo tMap (ver detalle aqui ).

Job DimProducto - Mapeo Producto

Con este componente, juntamos en un paso los registros leidos del maestro de materiales, y los valores adicionales que estan en otras tablas del ERP que nos permiten completar cada registro (como son los valores de descripciones, unidades de medida, calculo de equivalencia de unidades de medida, etc).

En este mapeo, utilizamos una funcionalidad de Talend que no habiamos visto, que es realizar un filtrado selectivo de los registros. Esto nos permite generar en el paso dos flujos de datos, uno con los registros correctos, y otro con los registros que no cumplan unas determinadas condiciones. En nuestro caso, los registros que tengan un valor NULL en el campo umren, serán rechazados y pasados a un paso posterior donde serán grabados en un fichero excel para su revisión.

5) En el mapeo se podrán generar materiales erróneos que habrá que verificar por parte de algún usuario y corregir en el sistema origen (por tener valores incorrectos). La lista de materiales se introducira en un fichero excel a través del control  REGISTROS_ERRONEOS del tipo tFileOutputExcel (ver aqui) . Igualmente, utilizando el componente CUENTA_LEIDOS_SAP del tipo tFlowMeter (ver aqui ),contaremos el número de registros correctos leidos desde el sistema origen para guardarlos en las tablas de metricas (para luego poder otener información estadística de los procesos realizados).

6) Verificamos que realmente haya modificaciones con los datos existentes en la base de datos del DW , y para los registros que si tienen modificaciones (o si son nuevos registros), realizamos la actualización. Para ello, utilizamos el componente VERIF_MODIFICACIONES del tipo tMap (ver aqui ) que recibira como flujo principal los registros que hemos leido desde Sap totalmente completados. Por otro lado, recibira como flujo de lookup (ver aqui) los registros que ya tenemos cargados en Myql en la tabla DWD_PRODUCTO (la tabla de la dimensión Producto). Con esta información, podremos realizar la comparación en el mapeo,  realizarando un filtrado selectivo de los registros, pasando al siguiente componente solo aquellos que tienen modificaciones (hay alguna diferencia en alguno de los campos).

Job DimProducto - Verificacion Modificaciones

El código utilizado en el expression filter, que es la condición de filtrado, es el siguiente:

!row11.material_desc.equals(row15.material_desc) ||
row11.familia_id!=row15.familia_id||
!row11.familia_desc.equals(row15.familia_desc)||
!row11.denom_id.equals(row15.denom_id)||
!row11.variet_id.equals(row15.variet_id)||
!row11.formato_id.equals(row15.formato_id)||
!row11.um_id.equals(row15.um_id)||
row11.litros_id!=row15.litros_id||
row11.linprod_id!=row15.linprod_id||
!row11.linprod_desc.equals(row15.linprod_desc)||
!row11.target_id.equals(row15.target_id)

Observar como vamos realizando la comparación campo a campo de los dos flujos, para determinar si hay algún cambio entre los datos ya cargados y los provenientes del ERP.

En principio, no vamos a realizar gestión de Dimensiones Lentamente Cambiantes, sino que siempre tendremos la foto de los datos tal y como están los ficheros maestros en el momento actual (mas adelante si gestionaremos una dimensión con esta casuistica para conocer los componentes de los que dispone Talend para este cometido). Los registros que superan el mapeo, son registrados en la base de datos Mysql utilizando el control ACTUALIZA_DWD_PRODUCTO, del tipo tMySqlOutput (ver detalle aqui ), grabandose como ya hemos dicho en la tabla DWD_PRODUCTO.

Con el componente tFlowMeter, en el paso CUENTA_MODIFICADOS (ver detalle aquí ), registramos en las tablas de metricas el número de registros que van a generar actualización.

7) Concluimos el proceso guardándonos en el log el correspondiente mensaje de finalización correcta del proceso, con el componente MENSAJE_LOG_FIN del tipo tWarn (ver detalle aqui).

Para ver en detalle como hemos definido cada componente, podeís acceder a la documentación HTML completa generada por Talend aquí. Podeis descargaros el fichero zip que contiene dicha documentación aquí.

Conclusiones

Hemos realizado nuestro primer trabajo ETL complejo utilizando Talend. Ha sido un poco complicado entender como funciona la herramienta, como se vinculan y encadenan los diferentes componentes, la forma de pasar la información y los registros entre ellos, como se gestionan los errores, etc. Pero una vez comprendidos los conceptos básicos, se observa el gran potencial que tiene Talend (que ademas puede ser completado con el uso del lenguaje Java por todas partes).  Os recomiendo si quereis ampliar el conocimiento de la herramienta, la lectura del Manual de Usuario de la herramienta y la Guia de Referencia de Componentes (ambos en inglés) en este link y visitar la web del proyecto Open TalendForge, donde podeis encontrar Tutoriales, Foros, gestión de bugs, etc.

También he visto lo importante que es el proceso de análisis de los origenes de información y el detallado de todas las transformaciones que hay que realizar, las excepciones, que criterio se sigue para ellas, que sustituciones realizamos, etc. Quizas deberiamos de haber realizado ese paso mas en profundidad, pues luego al realizar los procesos ETL han aparecido situaciones que no habiamos previsto. Lo tenemos en cuenta para nuestro próximo proyecto.

Igualmente hemos visto que es importante establecer un mecanismo para la gestión de todos los logs de la ejecución de procesos, estadísticas y metricas, pues luego nos serán de utilidad para montar los procesos de automatización, seguimiento y depuración de errores necesarios para la puesta en productivo del proyecto y para su mantenimiento a lo largo del tiempo.

Y como no, la documentación. El hecho de ir documentando y explicando cada componente que utilizamos en Talend (nombrado de los pasos, texto explicativo en cada componente, comentarios en los metadatos, etc), ha permitido que utilizando una funcionalidad estandar de Talend, la generación de documentación HTML, disponer de un repositorio donde poder consultar como estan construidos los procesos, de una forma muy completa y detallada, de cara a la comprensión de como están montados los procesos por parte de terceras personas, para su posterior modificación o mantenimiento en el futuro.

En la siguiente entrada del Blog, detallaremos los procesos ETL para la carga del resto de dimensiones de nuestro proyecto. Posteriormente, abordaremos el proceso ETL para la carga de la tabla de Hechos, que será la mas compleja y para la que habrá que establecer un mecanismo de orquestación para su automatización. En dicha entrada incluiremos igualmente el estudio del particionado de tablas utilizando Mysql, que veremos en profundidad.

Posted in Business Intelligence, ETL, OpenSource, Talend | 7 Comments »

13.1. Mas ejemplos de Talend. Ejecución de sentencias SQL construidas en tiempo ejecución.

Posted by Roberto Espinosa en 5 enero 2010


Después de llenar la dimensión Tiempo con los procesos ETL utilizando Talend, al revisar los registros de la tabla DWD_TIEMPO, comprobamos que para algunos años, la ultima semana del año se ha llenado con el valor 1. La explicación es que Java utiliza la normalización ISO para el número de semana, y esta nunca puede ser superior a 52. Por tanto, para algunos años, la ultima semana del año ha quedado registrada con el valor 1.

Este problema nos sirve de base para desarrollar nuestro seguiente proceso ETL, que ira encandenado a la generación de los registros de la dimensión tiempo, y que tendrá el objetivo de arreglar los registros que han quedado erróneos en la base de datos.

El proceso tendría los siguientes pasos:

1) Recuperación para cada año, del mayor número de semana registrado en la tabla: para ello, ejecutamos una sentencia SQL , utilizando el componente TMySqlInput del grupo Databases, Mysql).

La sentencia ejecutada es la siguiente:

"SELECT dwd_tiempo.ano_id,max(dwd_tiempo.sem_id) as semana
FROM    dwd_tiempo group by dwd_tiempo.ano_id
order by dwd_tiempo.ano_id"

Este control nos genera un flujo con todos los registros devueltos por la sentencia SQL y para cada registro realizaremos las acciones siguientes:

2) Para cada año, ejecutamos una sentencia SQL construida en tiempo de ejecución con los datos pasados por el control anterior,para arreglar el número de semana erronea (utilizando el mayor número de semana + 1). Para ello, utilizamos el control  tMySqlRow. Este control nos permite ejecutar una sentencia SQL para cada registro del flujo y transmitir dicho flujo al paso siguiente del job.

La sentencia ejecutada es la siguiente (observad como vamos construyendo la sentencia SQL concatenando trozos de texto fijo con los valores de la variable row, que seran pasados por el componente anterior de la secuencia):

"update    dwd_tiempo set sem_id =" +  row2.semana  +
" + 1 where ano_id = " +
 row2.ano_id  + " and sem_id = 1 and mes_id = 12"

3) Terminamos el proceso de arreglo corrigiendo el campo compuesto semano_id, que también quedo erróneo y que corresponde a la Semana del año en la notacion AAAA-SS, donde AAAA es el año y SS es la semana. Para ello, utilizamos también el control  tMySqlRow.

La sentencia ejecutada es la siguiente (construida igualmente concatenando trozos de texto fijo con los valores de la variable row, que seran pasados por el componente anterior de la secuencia). En este caso, también estamos utilizando funciones de Mysql (CONCAT y CAST), para hacernos una idea de la potencia del lenguaje SQL en combinación con la utilización de variables de Talend:

"update enobi.dwd_tiempo " +
"set semano_id = CONCAT(CAST(ano_id AS CHAR),'-',CAST(sem_id AS CHAR))"
+ " where ano_id = " +  row2.ano_id  + " and mes_id = 12"

Los pasos 2 y 3 los podríamos haber realizado en una única sentencia SQL, pero los hemos separado para mayor claridad.

El proceso completo sería el siguiente:

Esquema Completo del Job en Talend para el arreglo de las semanas

Seguimos avanzando en nuestro proyecto y una de las cosas que va quedando clara es que los conocimientos del consultor de BI han de cubrir muchas areas: bases de datos, sql, algo de lenguajes de programación (Java en el caso de Talend), herramientas de modelado, herramientas ETL, teoria de modelado de datos multidimensional con sus variantes, algo de estadística para el datamining, nociones de erp´s,crm´s, etc. Eso sin contar los conocimientos de las empresas, indicadores de negocio y la visión diferente que habrá que aportar a la empresa donde se realize el proyecto. Volvemos a acordarnos de lo que decía Jorge Fernández en su blog ( “El consultor de BI, ese bicho raro“).

A continuación, vamos a realizar el proceso ETL para la dimensión Producto.

Posted in Business Intelligence, ETL, OpenSource, Talend | 2 Comments »

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

Posted by Roberto Espinosa en 3 enero 2010


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.

Participa en la encuesta:


Posted in Bases de Datos, Business Intelligence, Mysql, OpenSource | 2 Comments »

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

Posted by Roberto Espinosa en 1 enero 2010


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.

Posted in Business Intelligence, ETL, OpenSource, Talend | 3 Comments »

11. Herramientas ETL. ¿Que son, para que valen?. Productos mas conocidos. ETL´s Open Source.

Posted by Roberto Espinosa en 29 diciembre 2009


En la publicación 3 de nuestro Blog, cuando hablabamos del DataWarehouse, pasamos por encima de las herramientas ETL, considerandolas un elemento fundamental en la construcción, explotación y evolución de nuestro DW.

Esquema Tipico de Herramienta ETL

Deciamos que las  herramientas de este tipo, deberían de proporcionar, de forma general,  las siguientes funcionalidades:

Control de la extracción de los datos y su automatización, disminuyendo el tiempo empleado en el descubrimiento de procesos no documentados, minimizando el margen de error y permitiendo mayor flexibilidad.

Acceso a diferentes tecnologías, haciendo un uso efectivo del hardware, software, datos y recursos humanos existentes.

Proporcionar la gestión integrada del Data Warehouse y los Data Marts existentes, integrando la extracción, transformación y carga para la construcción del Data Warehouse corporativo y de los Data Marts.

Uso de la arquitectura de metadatos, facilitando la definición de los objetos de negocio y las reglas de consolidación.

Acceso a una gran variedad de fuentes de datos diferentes.

Manejo de excepciones.

Planificación, logs, interfaces a schedulers de terceros, que nos permitiran llevan una gestión de la planificación de todos los procesos necesarios para la carga del DW.

Interfaz independiente de hardware.

Soporte en la explotación del Data Warehouse.

Es hora de ampliar las definiciones y entrar un poco mas a fondo en lo que son realmente las ETL´s:

________________________________________________________________________________________

Definición de ETL en la Wikipedia

Si ampliamos las definiciones, en la Wikipedia se dice lo siguiente de las herramientas ETL:

ETL son las siglas en inglés de Extraer, Transformar y Cargar (Extract, Transform and Load). Es el proceso que permite a las organizaciones mover datos desde múltiples fuentes, reformatearlos y limpiarlos, y cargarlos en otra base de datos, data mart, o data warehouse para analizar, o en otro sistema operacional para apoyar un proceso de negocio.

Los procesos ETL también se pueden utilizar para la integración con sistemas heredados (aplicaciones antiguas existentes en las organizaciones que se han de integrar con los nuevos aplicativos, por ejemplo, ERP´s. La tecnología utilizada en dichas aplicaciones puede hacer dificil la integración con los nuevos programas).

Extraer

La primera parte del proceso ETL consiste en extraer los datos desde los sistemas de origen. La mayoría de los proyectos de almacenamiento de datos fusionan datos provenientes de diferentes sistemas de origen. Cada sistema separado puede usar una organización diferente de los datos o formatos distintos. Los formatos de las fuentes normalmente se encuentran en bases de datos relacionales o ficheros planos, pero pueden incluir bases de datos no relacionales u otras estructuras diferentes. La extracción convierte los datos a un formato preparado para iniciar el proceso de transformación.

Una parte intrínseca del proceso de extracción es la de analizar los datos extraídos, de lo que resulta un chequeo que verifica si los datos cumplen la pauta o estructura que se esperaba. De no ser así los datos son rechazados.

Un requerimiento importante que se debe exigir a la tarea de extracción es que ésta cause un impacto mínimo en el sistema origen. Si los datos a extraer son muchos, el sistema de origen se podría ralentizar e incluso colapsar, provocando que éste no pueda utilizarse con normalidad para su uso cotidiano. Por esta razón, en sistemas grandes las operaciones de extracción suelen programarse en horarios o días donde este impacto sea nulo o mínimo.

Interfaz Grafico herramienta ETL

Transformar

La fase de transformación aplica una serie de reglas de negocio o funciones sobre los datos extraídos para convertirlos en datos que serán cargados. Algunas fuentes de datos requerirán alguna pequeña manipulación de los datos. No obstante en otros casos pueden ser necesarias aplicar algunas de las siguientes transformaciones:

  • Seleccionar sólo ciertas columnas para su carga (por ejemplo, que las columnas con valores nulos no se carguen).
  • Traducir códigos (por ejemplo, si la fuente almacena una “H” para Hombre y “M” para Mujer pero el destino tiene que guardar “1″ para Hombre y “2″ para Mujer).
  • Codificar valores libres (por ejemplo, convertir “Hombre” en “H” o “Sr” en “1″).
  • Obtener nuevos valores calculados (por ejemplo, total_venta = cantidad * precio).
  • Unir datos de múltiples fuentes (por ejemplo, búsquedas, combinaciones, etc.).
  • Calcular totales de múltiples filas de datos (por ejemplo, ventas totales de cada región).
  • Generación de campos clave en el destino.
  • Transponer o pivotar (girando múltiples columnas en filas o viceversa).
  • Dividir una columna en varias (por ejemplo, columna “Nombre: García, Miguel”; pasar a dos columnas “Nombre: Miguel” y “Apellido: García”).
  • La aplicación de cualquier forma, simple o compleja, de validación de datos, y la consiguiente aplicación de la acción que en cada caso se requiera:
    • Datos OK: Entregar datos a la siguiente etapa (Carga).
    • Datos erróneos: Ejecutar políticas de tratamiento de excepciones (por ejemplo, rechazar el registro completo, dar al campo erróneo un valor nulo o un valor centinela).

Interfaz Grafico de la herramienta ETL Kettle - Pentaho

Carga

La fase de carga es el momento en el cual los datos de la fase anterior (transformación) son cargados en el sistema de destino. Dependiendo de los requerimientos de la organización, este proceso puede abarcar una amplia variedad de acciones diferentes. En algunas bases de datos se sobrescribe la información antigua con nuevos datos. Los data warehouse mantienen un historial de los registros de manera que se pueda hacer una auditoría de los mismos y disponer de un rastro de toda la historia de un valor a lo largo del tiempo.

Existen dos formas básicas de desarrollar el proceso de carga:

  • Acumulación simple: La acumulación simple es la más sencilla y común, y consiste en realizar un resumen de todas las transacciones comprendidas en el período de tiempo seleccionado y transportar el resultado como una única transacción hacia el data warehouse, almacenando un valor calculado que consistirá típicamente en un sumatorio o un promedio de la magnitud considerada.
  • Rolling: El proceso de Rolling por su parte, se aplica en los casos en que se opta por mantener varios niveles de granularidad. Para ello se almacena información resumida a distintos niveles, correspondientes a distintas agrupaciones de la unidad de tiempo o diferentes niveles jerárquicos en alguna o varias de las dimensiones de la magnitud almacenada (por ejemplo, totales diarios, totales semanales, totales mensuales, etc.).

La fase de carga interactúa directamente con la base de datos de destino. Al realizar esta operación se aplicarán todas las restricciones y triggers (disparadores) que se hayan definido en ésta (por ejemplo, valores únicos, integridad referencial, campos obligatorios, rangos de valores). Estas restricciones y triggers (si están bien definidos) contribuyen a que se garantice la calidad de los datos en el proceso ETL, y deben ser tenidos en cuenta.

Procesamiento

Un desarrollo reciente en el software ETL es la aplicación de procesamiento paralelo. Esto ha permitido desarrollar una serie de métodos para mejorar el rendimiento general de los procesos ETL cuando se trata de grandes volúmenes de datos. Hay 3 tipos principales de paralelismos que se pueden implementar en las aplicaciones ETL:

  • De datos: Consiste en dividir un único archivo secuencial en pequeños archivos de datos para proporcionar acceso paralelo.
  • De segmentación (pipeline): Permitir el funcionamiento simultáneo de varios componentes en el mismo flujo de datos. Un ejemplo de ello sería buscar un valor en el registro número 1 a la vez que se suman dos campos en el registro número 2.
  • De componente: Consiste en el funcionamiento simultáneo de múltiples procesos en diferentes flujos de datos en el mismo puesto de trabajo.

Estos tres tipos de paralelismo no son excluyentes, sino que pueden ser combinados para realizar una misma operación ETL.

Una dificultad adicional es asegurar que los datos que se cargan sean relativamente consistentes. Las múltiples bases de datos de origen tienen diferentes ciclos de actualización (algunas pueden ser actualizadas cada pocos minutos, mientras que otras pueden tardar días o semanas). En un sistema de ETL será necesario que se puedan detener ciertos datos hasta que todas las fuentes estén sincronizadas. Del mismo modo, cuando un almacén de datos tiene que ser actualizado con los contenidos en un sistema de origen, es necesario establecer puntos de sincronización y de actualización.

Desafíos

Los procesos ETL pueden ser muy complejos. Un sistema ETL mal diseñado puede provocar importantes problemas operativos.

En un sistema operacional el rango de valores de los datos o la calidad de éstos pueden no coincidir con las expectativas de los diseñadores a la hora de especificarse las reglas de validación o transformación. Es recomendable realizar un examen completo de la validez de los datos (Data profiling) del sistema de origen durante el análisis para identificar las condiciones necesarias para que los datos puedan ser tratados adecuadamente por las reglas de transformación especificadas. Esto conducirá a una modificación de las reglas de validación implementadas en el proceso ETL.

Normalmente los data warehouse son alimentados de manera asíncrona desde distintas fuentes, que sirven a propósitos muy diferentes. El proceso ETL es clave para lograr que los datos extraídos asíncronamente de orígenes heterogéneos se integren finalmente en un entorno homogéneo.

La escalabilidad de un sistema de ETL durante su vida útil tiene que ser establecida durante el análisis. Esto incluye la comprensión de los volúmenes de datos que tendrán que ser procesados según los acuerdos de nivel de servicio (SLA: Service level agreement). El tiempo disponible para realizar la extracción de los sistemas de origen podría cambiar, lo que implicaría que la misma cantidad de datos tendría que ser procesada en menos tiempo. Algunos sistemas ETL son escalados para procesar varios terabytes de datos para actualizar un data warehouse que puede contener decenas de terabytes de datos. El aumento de los volúmenes de datos que pueden requerir estos sistemas pueden hacer que los lotes que se procesaban a diario pasen a procesarse en micro-lotes (varios al día) o incluso a la integración con colas de mensajes o a la captura de datos modificados (CDC: change data capture) en tiempo real para una transformación y actualización continua.

________________________________________________________________________________________

Algunas Herramientas ETL

Libres

Las herramientas ETL no se tienen porque utilizar solo en entornos de construcción de un DW, sino que pueden ser utiles para multitud de propositos, como por ejemplo:
  • Tareas de Bases de datos: También se utilizan para consolidar, migrar y sincronizar bases de datos operativas.
  • Migración de datos entre diferentes aplicaciones por cambios de versión o cambio de aplicativos.
  • Sincronización entre diferentes sistemas operacionales (por ejemplo, nuestro entorno ERP y la Web de ventas).
  • Consolidación de datos: sistemas con grandes volumenes de datos que son consolidades en sistemas paralelos para mantener historicos o para procesos de borrado en los sistemas originales.
  • Interfases de datos con sistemas externos: envio de información a clientes, proveedores. Recepción, proceso e integración de la información recibida.
  • Interfases con sistemas Frontoffice: interfases de subida/bajada con sistemas de venta.
  • Otros cometidos: Actualización de usuarios a sistemas paralelos, preparación de procesos masivos (mailings, newsletter), etc.

Para que nos hagamos una idea de las herramientas ETL mas importantes, podemos leer el informe Gartner, que es una comparativa de las productos mas importantes del mercado, posicionandolos en el según diferentes criterios, y hablando de las ventajas y puntos de riesgo de cada fabricante ( acceder al informe aquí, es necesario registrarse en la Web de Talend ).

Fuente: Gartner (November 2009)


Las características mas importantes que ha de incluir una herramienta ETL según Gartner son las siguientes:

  • Conectividad / capacidades de Adaptación (con soporte a origenes y destinos de datos): habilidad para conectar con un amplio rango de tipos de estructura de datos, que incluyen bases de datos relacionales y no relacionales, variados formatos de ficheros, XML, aplicaciones ERP, CRM o SCM, formatos de mensajes estandar (EDI, SWIFT o HL7), colas de mensajes, emails, websites, repositorios de contenido o herramientas de ofimatica.
  • Capacidades de entrega de datos: habilidad para proporcionar datos a otras aplicaciones, procesos o bases de datos en varias formas, con capacidades para programación de procesos batch, en tiempo real o mediante lanzamiento de eventos.
  • Capacidades de transformación de datos: habilidad para la transformación de los datos, desde transformaciones básicas (conversión de tipos, manipulación de cadenas o calculos simples), transformaciones intermedias (agregaciones, sumarizaciones, lookups) hasta transformaciones complejas como analisis de texto en formato libre o texto enriquecido.
  • Capacidades de Metadatos y Modelado de Datos: recuperación de los modelos de datos desde los origenes de datos o aplicaciones, creación y mantenimiento de modelos de datos, mapeo de modelo fisico a lógico, repositorio de metados abierto (con posiblidad de interactuar con otras herramientas), sincronización de los cambios en los metadatos en los distintos componentes de la herramienta, documentación, etc.
  • Capacidades de diseño y entorno de desarrollo: representación grafica de los objetos del repositorio, modelos de datos y flujos de datos, soporte para test y debugging, capacidades para trabajo en equipo, gestion de workflows de los procesos de desarrollo, etc.
  • Capacidades de gestión de datos (calidad de datos, perfiles y  minería).
  • Adaptación a las diferentes plataformas hardware y sistemas operativos existentes: Mainframes (IBM Z/OS), AS/400, HP Tandem, Unix, Wintel, Linux, Servidores Virtualizados, etc.
  • Las operaciones y capacidades de administración: habilidades para gestion, monitorización y control de los procesos de integración de datos, como gestión de errores, recolección de estadisticias de ejecución, controles de seguridad, etc.
  • La arquitectura y la integración: grado de compactación, consistencia e interoperabilidad de los diferentes componentes que forman la herramienta de integración de datos (con un deseable minimo número de productos, un unico repositorio, un entorno de desarrollo común, interoperabilidad con otras herramientas o via API), etc.
  • Capacidades SOA.

Observamos que en el informe del año 2009, se incluye por primera vez una herramienta ETL OpenSource, que es Talend. Tambien se habla de Pentaho, cuya herramienta Kettle, tambien es OpenSource. Ambas herramientas serán las que utilizemos en nuestro proyecto para la construcción de los procesos ETL.

Interfaz Grafico de la herramienta ETL Talend

A continuación indicamos algunos links interesantes sobre herramientas ETL:

Podeis echar un vistazo al directorio EOS de productos OpenSource para ver otros productos ETL Open, así como valoraciones de estos y casos reales de uso.

La elección de una herramienta ETL puede ser una tarea compleja que va a tener mucha repercusión en el desarrollo posterior de un proyecto. Podeis ver la comparativa de ETL´s OpenSource vs ETL´s Propietarias a continuación ( gracias a http://www.jonathanlevin.co.uk/). Aqui se habla de que las herramientas OpenSource ya estan empezando a ser una alternativa real a los productos existentes y se estan desarrollando con rapidez.

Igualmente, os dejo el link a un documento donde se habla de todo lo que tendremos que tener en cuenta a la hora de realizar la selección de una herramienta ETL (características que habrán de tener, criterios para la evaluación, etc). Acceder al documento aquí.

Posted in Business Intelligence, ETL, OpenSource | 7 Comments »

10. Análisis del sistema Operacional para identificación de Dimensiones, Atributos e Indicadores. Preparación de los procesos ETL.

Posted by Roberto Espinosa en 29 diciembre 2009


Una vez disponemos del módelo lógico completo y revisado, vamos a analizar cada una de las dimensiones, sus atributos e indicadores de negocio para identificar en los sistemas operacionales de la empresa el origen de los datos. Es decir, el lugar en las tablas de las aplicaciones ( aquí también caben otros origenes de información, como aplicaciones web, hojas excel, ficheros planos, etc), desde los cuales vamos a obtener los datos para llenar de manera efectiva nuestro DataWarehouse.

Este proceso de análisis será el punto de partida para la construcción de los procesos ETL ( Extraction, Transform and Load ) que nos permitirán automatizar la carga de nuestro sistema BI. Los procesos ETL  seran un conjunto de trabajos o jobs, con diferentes pasos de diferentes tipos ( extracción de datos, filtrado, transformación, mapeo, verificación de errores, logs, etc), que provocaran que nuestro modelo lógico sea llenado con los datos de los sistemas de gestión de la empresa y de esa manera permitir su analisis según los requerimientos establecidos utilizando las herramientas de Business Intelligence.

La metodología para la identificación de cada uno de los componentes de nuestro DW va a consistir en revisar una por una las dimensiones y sus atributos, así como la tabla de hechos, y para cada componente, anotaremos en que lugar del sistema origen se encuentra, cuales son sus características, que tipo de transformaciónes deberemos realizar sobre ellos así como cualquier otra observación a tener en cuenta para la elaboración posterior de los procesos ETL.

Dimensión Tiempo: la dimensión tiempo es una dimensión ficticia, que no existe en nuestro ERP o sistema operacional como tal, y que construiremos a partir de los calendarios (en nuestro caso, generaremos los datos correspondientes a 20 años, desde el año 2000 al año 2020). Para la construcción de la dimensión y de todos sus atributos, construiremos un proceso ETL para la carga inicial de esta dimensión.

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

Dimensión Producto: los datos principales de la dimensión Producto están en nuestro ERP Sap en la tabla MARA Maestro de Materiales. El origen de cada uno de los atributos y las transformaciones a realizar vienen descritas en la siguiente tabla:

Transformaciones para la creación de la Dimension Producto

Dimensión Cliente: los principales datos de los clientes en Sap los tenemos en la tabla KNA1. El origen de cada uno de los atributos y las transformaciones a realizar vienen descritas en la siguiente tabla:

Transformaciones para la creación de la Dimension Cliente

Dimensión Logística: Los datos de los centros logísticos se encuentran en la tabla T001W. El origen de cada uno de los atributos y las transformaciones a realizar vienen descritas en la siguiente tabla:

Transformaciones para la creación de la Dimension Logistica

Dimensión Promoción: Los datos de las promociones se encuentran en la tabla KONA. El origen de cada uno de los atributos y las transformaciones a realizar vienen descritas en la siguiente tabla:

Transformaciones para la creación de la Dimension Promocion

Hechos Transacciones de Ventas: los datos de negocio de las operaciones de venta estan registrados en las tablas de pedido de nuestro ERP y en tablas paralelas (cabecera de ventas VBAK y detalle de ventas VBAP) . El origen de las claves de la tabla de hechos y de los indicadores de negocio, asi como las transformaciones a realizar vienen descritas en la siguiente tabla:

Transformaciones para la creación de la tabla Hechos de Venta (I)

Transformaciones para la creación de la tabla Hechos de Venta (II)

Los campos calculados no siempre es necesario guardarlos en la tabla de Hechos, sobre todo si son campos que no tiene sentido tener calculado cuando estemos navegando por las dimensiones (como los porcentajes). Nos guardaremos para ellos los operadores necesarios para poder realizar los cálculos.

Nota: el origen de los datos aquí descrito es un modelo de ejemplo que puede tener una complejidad mayor o diferencias considerables si estuviesemos trabajando con sistemas reales. Solo trata de ser un ejemplo de como se deben de identificar los origenes de datos para el posterior proceso de extracción (recordar que estamos trabajando con un empresa ficticia para el desarrollo del proyecto). En la documentación también se debería de tener en cuenta cualquier casuistica a aplicar en cada atributo de cara a su conversión en atributos normalizados dentro del DW e incluir esas excepciones en la documentación de cara a los procesos ETL y a posteriores mantenimientos sobre el sistema.

Antes de continuar, veremos en la siguiente entrada del blog que son las herramientas ETL con un poco mas de profundidad, antes de pasar a la construcción del modelo físico definitivo y los procesos de llenado.

Posted in Business Intelligence | 1 Comment »

9. Presentación del prototipo. Ajustes en el módelo para análisis de clientes.

Posted by Roberto Espinosa en 21 diciembre 2009


Despues de trabajar unos dias en el, hemos conseguido construir nuestro prototipo reflejando en el toda la estructura de nuestro modelo lógico.

Ademas, hemos generado un lote de informes ejemplo para poder presentar al “cliente” y con ellos explotar todas las posibilidades del diseño elegido.

Algunos de los informes son los siguientes.

  • Ventas por canal y día de la semana (en formato Web, es decir, accediendo desde el portal via navegador):

  • Ventas por linea de producto y semana (Web):

  • Año y Linea de Producto (Web):

  • Analisis de promociones Web (Via Microstrategy Desktop):

Al observar este informe vemos que hay rentabilidades negativas, y navegamos a nivel de cliente y de material para ver que estamos, con la promoción, vendiendo por debajo de coste:

En este informe hemos utilizado los umbrales para poder dar formato o colores diferentes a los indicadores que queremos que nos avisen de algún hecho a tener en cuenta (en este caso, rentabilidad negativa).

  • Ventas por región (Via Microstrategy Desktop):

En este informe hemos utilizado otro aspecto interesante, que es la generación de porcentajes según el peso de un valor en el total de valores de un indicador (para nuestro ejemplo, el importe neto de ventas y el total de litros vendidos).

  • Ventas por Tipo Cliente / Linea Producto (Via Microstrategy Desktop):

  • Analisis de Ventas y Rentabilidad por Tipo Cliente (Via Microstrategy Desktop):

En este informe hemos utilizado umbrales con un simbolo con diferentes colores según el rango de valores del indicador, que nos servira para localizar valores por debajo de lo deseado, valores en el limite o valores por encima de los previsto (de una forma gráfica muy rapida de analizar).

  • Ranking de ventas por Cliente (en este hemos utilizado los porcentajes sobre el indicador y hemos ordenado por el porcentaje para sacar los clientes con mayores volumenes):

El mismo informe, pero ejecutando desde el Desktop en lugar de la Web.

  • Informe de Ventas por Denominación de Origen y Varietal (Variedad de Uva utilizada en la elaboración):

En este informe se observa, gracias al grafico de tarta, que aunque por volumen de litros la uva mas vendida es la Garnacha sin denominación de origen, el mayor volumen de ventas en importe lo tenemos en la varietal Tempranillo de la denominación de origen de Ribera del Duero.

  • Ranking de Materiales:

  • Analisis de ventas por Target (destino):

En este informe también se observa que aunque el mayor volumen de litros lo tenemos en el Target Gran Consumo, el mayor volumen de ventas esta en Gourmet (de ahí que la empresa quiera fomentar ese target, utilizando, por ejemplo, el Club de Vinos).

  • Análisis de ventas por Mes y Canal:

En este informe observamos la subida de las ventas en el mes de diciembre en todos los canales por la Navidad.

  • Analisis de ventas por día y Canal:

Como ejemplo de navegación, hemos seleccionado el día 01 de Noviembre y hemos ido a la dimensión Cliente, al atributo Agrupador, de la siguiente manera:

El mismo informe utilizando el portal Web quedaría:

Y la navegación por las dimensiones en Web:

La navegación hacia arriba en las dimensiones quedaría algo asi:

  • Ventas por centro de distribución y denominación origen:

  • Ejemplo de dimensiones y jerarquias definidas en el Prototipo para la navegación por la información:

Con todos estos informes nos hemos hecho una idea y hemos podido mostrar al cliente las posibilidades de la herramienta y alguno de los análisis que podriamos realizar. Pero ahora vamos a ver los inconvenientes, es decir, donde nuestro Modelo Lógico no ha llegado y que cosas se han de reajustar.

Ajustes en el módelo para análisis de clientes

Uno de los requerimientos de nuestro cliente era que el Datamart departamental de Ventas nos permitiese hacer un análisis profundo de los clientes del Club de Vinos, para realizar análisis sobre ellos, estudio de lanzamiento de nuevos productos, promociones, etc.

Durante la revisión del prototipo, se constata que se ha de ampliar la información referente a los clientes de este canal. Es decir, se han de añadir nuevos atributos a la dimensión cliente para poder facilitar posteriormente nuevos análisis. Los atributos añadidos en la fase de revisión son los siguientes:

  • Edad.
  • Estado Civil.
  • Numero Hijos.
  • Sexo.
  • Email.
  • Nivel Estudios.
  • Nivel Ingresos.
  • Fecha Antiguedad.
  • Preferencias.
  • Tipo Pago.
  • Nivel Club (Estandar, Oro, Platino).

Como hemos visto, también se han añadido criterios para poner en marcha el club de fidelización y poder hacer ofertas especificas y personalizadas para los clientes mas antiguos o los clientes con mayor nivel de compra.

Tras la revisión, el modelo lógico con los cambios en la dimensión de clientes quedaría así:

Hemos modificado nuestro esquema en Estrella a un esquema en Copo de Nieve para añadir una nueva rama en la dimensión cliente donde se registran los datos correspondientes a los clientes que son del Club de Vinos.

Nuestro proyecto sigue avanzando y es hora de continuar con el resto de pasos. A partir de ahora, vamos a analizar los sistemas operacionales para identificar los origenes de la información (desde donde llenaremos nuestro DW), construir el modelo físico definitivo e implementar los procesos de traspaso de información (carga inicial y procesos de actualización regulares). En ese momento entrarán en juego las herramientas ETL, que nos permitiran que nuestro proyecto tome vida definitiva con los datos reales del cliente. Como hemos indicado varias veces, es una de las partes mas complejas del proyecto y puede suponer hasta el 80% del esfuerzo de construcción de un DW.

Posted in Business Intelligence, Microstrategy | Leave a Comment »

8. El modelo Lógico de nuestro DW. Revisión. Construcción de un prototipo para validación.

Posted by Roberto Espinosa en 11 diciembre 2009


Antes de continuar con las siguientes fases del proyecto, se revisa el modelo propuesto y es aprobado. Se va a construir un prototipo para verificar su corrección y funcionalidad antes de pasar al diseño físico definitivo, el analisis del sistema ERP para la identificación de las dimensiones e indicadores  y la construcción de los procesos ETL, que serán los que darán vida a nuestro DW (y que recordemos que sera una de las fases mas complejas y con mas carga de trabajo del proyecto).

Como hemos indicado anteriormente, el módelo lógico definitivo sería el siguiente:

La fase de construcción de un prototipo se podría obviar pero es interesante dedicar unas jornadas a la construcción de un pequeño prototipo con datos reales (o lo mas reales posible ) para que los usuarios puedan validar los resultados del diseño. De esta forma, validamos el modelo antes de empezar la tediosa tarea de construirlo fisicamente y llenarlo de datos desde nuestros sistemas operacionales u otras fuentes. En algunos proyectos, la construcción del prototipo se realiza en el momento de la venta del proyecto como una herramienta de ayuda a la toma de decisión de la compra.

Para la construcción de nuestro prototipo, partiendo del modelo lógico, vamos a utilizar Mysql ( y la herramienta de diseño Mysql Workbench ). Para la construcción del prototipo, utilizaremos la Microstrategy Reporting Suite. Es la suite gratuita que Microstrategy ha lanzado al mercado para hacer frente a la proliferación de los productos OpenSource y para establecer su propia estrategia de captación de clientes (hoy en día ya no valen estrategias de precios de licencia por las nubes). En nuestro caso, hemos registrado una licencia para 25 usuarios. La licencia free incluye lo siguiente:

Características incluidas en Microstrategy Reporting Suite: www.microstrategy.es

Puede ser un buen punto de partida para un pequeño proyecto o para la validación de productos (aunque al final estamos con un fabricante propietario y estamos de alguna manera “cogidos” a un producto cerrado ). Tener en cuenta que tenemos acceso a una gran parte de las herramientas, así como soporte por correo y online, recursos de formación, video tutoriales, etc, que pueden ser una gran ayuda para familiarizarnos con el producto. También se incluye una amplia documentación en pdf de todos los elementos que forman la suite.

En las entradas del blog iremos publicando el modelo de datos físico utilizado para la construcción del prototipo, la copia de seguridad de mysql con los datos “reales” cargados y los metadatos definidos en la herramienta de Microstrategy por si alguien se anima a construir y validar su propio prototipo.

Por tanto, las cuatro fases de construcción del prototipo serán las siguientes:

1) Diseño físico “preliminar”, orientado a la construcción del prototipo.

Construimos el modelo relacional a través de la herramienta MySQL Workbench, y el diseño final sería el siguiente:

Antes de continuar, y al revisar el modelo físico, nos damos cuenta que es conveniente seguir un criterio para el nombrado de los campos que luego facilite el trabajo en la definición del modelo de datos con las herramientas de Microstrategy, asi como en el momento de definir las jerarquias de los atributos de las dimensiones (tema del que todavia no hemos hablado).

Por tanto, antes de continuar, procedemos a un ajuste del módelo fisico y la nomenclatura de los campos (por ejemplo, para los atributos que haya un código y una descripción, siempre llamaremos campox_id al campo del atributo, y campo campox_desc al campo de descripción del atributo). Es una forma de normalizar y utilizar un convenio que nos permite luego trabajar con mas claridad (teniendo en cuenta la cantidad de atributos diferentes de los que disponemos en nuestro proyecto). Para los atributos donde solo haya un identificador (sea numerico o texto, como por ejemplo, para la denominación de origen), llamaremos al campo nombreatributo_id (por ejemplo denomin_id para la denominación de origen de un producto o variet_id para la varietal utilizada en la elaboración del vino).

El esquema fisico revisado quedaría así:

El fichero del modelado con la herramienta lo podeis encontrar en el link: enobi.mwb

El fichero con las sentencias sql para la creación del esquema fisico en el link: enobi.sql

2) Llenado de datos “reales”.

En MySQL, creamos un catalogo que se llama EnoBI. Este catalogo va a almacenar los datos de nuestro DW (luego veremos que tenemos otros catalogos, como el del Metadatos de Microstrategy, el de estadísticas, etc). En este catalogo creamos las tablas físicas tal y como hemos definido y creamos un juego de datos de ejemplo para poder validar el modelo con el prototipo. Algunos ejemplos de los datos simulados serian los siguientes:

Ejemplo de Datos Simulados

El acceso completo a los datos lo teneis en el link siguiente, donde se accede al fichero de backup de la base de datos realizado con MySQL Administrator: backup.sql

3) Implementación del modelo utilizando Microstrategy Reporting Suite. Definición del modelo de metadatos.

Una vez tenemos preparada nuestra base de datos, ya podemos empezar a preparar el entorno de la Microstrategy Reporting Suite para trabajar con dicha base de datos y para hacer el modelado que luego nos va a permitir utilizar las herramientas de Microstrategy. Debemos realizar varias tareas, que en resumen son:

  • Instalación de la Microsoft Reporting Suite. Registro de la licencia y activación. En nuestro caso, hemos instalado en un Windows 7 Ultimate (necesitamos tener instalado el Office XP o superior y tener activado el IIS Internet Information Server en el sistema, pues la Suite hace uso de ella para el tema de acceso Web a los informes).
  • Configuración de origenes ODBC: en nuestro caso, no tenemos acceso directo a la bd de datos y vamos a definir los siguientes origenes de datos ODBC:
    • Metadatos: es el catalogo donde Microstrategy se va a guardar todas las definiciones del modelo de datos, informes, etc. Le llamamos ENOBI_MD.
    • Historial: donde Microstrategy se guarda un historial de las acciones realizadas sobre los objetos. Le llamamos ENOBI_HS.
    • Origen de datos: será para acceder a datos del DW en si. Le llamamos ENOBI_DW.
  • Ejecución del Configuration Wizard: nos va a permitir realizar de forma asistida la configuración de los elementos mas importantes del sistema. Se configuran 3 aspectos:
    • Tablas de repositorio de metadatos e historial: no pide el catalogo donde se van a ubicar y ejecuta las sentencias sql para crear la estructura de tablas necesaria.
    • Configuración del Microstrategy Intelligence Server.
    • Configuración de Origenes del Proyecto: definición de un origen de Proyecto y su vinculación con el Microstrategy Intelligence Server. Luego sobre el podremos construir nuestro proyecto de BI.
  • A continuación vamos a crear nuestro proyecto. Podemos utilizar el Project Builder o la herramienta Desktop (con el Architect o el Project Creation Assistant), donde se realiza la creación del proyecto con un asistente y luego se mantienen los diferentes elementos que lo conforman. Vamos a utilizar el Project Builder para este primer paso (es una herramienta orientada a la construcción de prototipos y para la construcción de Sistemas Productivos se recomienda el Architect/Project Creation Assitant ). El resumen de pasos seguido es el siguiente:
    • Selección del origen de datos para guardar los metadatos e identificación del proyecto. Al seleccionar el origen del proyecto estamos indicado en que esquema de bd de metadatos vamos a guardar todo el modelo.

    • Selección del origen de datos del DW.

    • Selección de la tabla de hechos.

    • Selección de indicadores de nuestro modelo: campos de tabla de hechos que queremos utilizar como indicadores de negocio en nuestro modelo.

    • Selección de atributos de las dimensiones.

    • Definición de las jerarquias entre los atributos: ya veremos en el desarrollo del proyecto como se definen las jerarquias de los diferentes atributos que tenemos en nuestras dimensiones (que al fin y al cabo son los que nos van a permitir la navegación estructurado por los datos). Sin jerarquía el modelo dimensional no tiene sentido.

    • Definición de indicadores calculados (a partir de indicadores base).

Realizamos algunos ajustes en las jerarquias, así como en la definición de indicadores y la asociación que realiza Microstrategy entre los campo id y los campos desc. El modelo esta preparado para la definición de los informes de ejemplo para presentar para la aprobación del módelo lógico presentado.

La definición de las jerarquias de los atributos las podeis encontrar en un documento word en el link: jerarquias.doc

4) Construcción de informes ejemplo. Preparación de la plataforma de prototipo para la revisión con usuarios.

En la siguiente entrada del blog analizaremos los informes construidos para el prototipo, con ejemplos de los mas usuales.

Posted in Business Intelligence, Microstrategy | 1 Comment »

7. Definición de objetivos. Analisis de requerimientos.

Posted by Roberto Espinosa en 9 diciembre 2009


La Empresa

La compañia Bodegas Vinicolas SA (BVSA) nacio en la denominación de origen Rioja en 1970 y en la actualidad, tras varios procesos de compra y ampliación de instalaciones, dispone de bodegas en Ribera del Duero, Rueda, Toro, Somontano, Priorat, Yecla, La Mancha y Alicante. En su portfolio de productos hay graneles, vinos jovenes, crianzas, reservas y gran reserva, de diferentes variedades, con o sin denominación de origen  y con diferentes formatos de envasado ( garrafas, tetra brik, granel, botellas de diferentes tamaños, etc ). Cada bodega tiene su propio almacén desde el que se sirve la mercancia, aunque las ventas estan centralizadas en la Bodega de La Rioja, donde hay un Call Center que recoge todos los pedidos nacionales e internacionales, y desde el que se atiende a los clientes del Club de Vinos. Cada bodega puede tener sus propias referencias así como referencias producidas en las otras bodegas, para venta en la tiendas propias o para distribución.

La compañia trabaja con clientes de todos los tipos, desde tiendas Gourmets o especializadas en vinos, Restaurantes y Hoteles, Mayoristas, Grandes cadenas de Distribución e Hipermercados. En el caso de grandes clientes, se sirve directamente a los centros desde cada bodega, aunque hay un estudio para construir un centro logístico que recoga la producción de todas las bodegas y realize la distribución, bien directamente a los clientes o a los centros logísticos propios de cada uno de ellos. Igualmente, se venden vinos a traves de internet y venta por correo en un Club de Vinos que se ha montado con el soporte de un portal Web donde se pueden comprar las referencias, se hacen selecciones mensuales y se ofrece gran cantidad de información referente al mundo del vino a los clientes que forman parte de este club.

El Club de Vinos se quiere fomentar como un canal de venta directa por su gran potencial de crecimiento, y a partir del cual también realizar estudios especificos de mercado y lanzamiento de nuevos productos, asi como programas de fidelización.

La empresa es una empresa joven y apuesta por la tecnologia en todos los ambitos de actuación, desde el propiamente relacionado con su sector (maquinaria, recolección automatica, instalaciones modernas en las bodegas), como a nivel de sistemas de información, donde trabajan con el ERP de Sap.

Objetivos

La empresa  pretende desarrollar un proyecto de BI para sacar el mayor partido posible  a la información de ventas de la que dispone. El director comercial actua como sponsor de este proyecto, que ademas es un proyecto piloto dentro de la compañia y que se ampliara a otros departamentos en el caso de obtener de él el exito esperado.

La construcción del DW y el uso posterior de herramientas de BI se va a centrar en los procesos de negocio de ventas. Se pretende construir un Datamart departamental para ventas y marketing que será alimentado con la información proveniente de su sistema operacional.

El objetivo es disponer de toda la información referente a ventas, productos, clientes, logistica, promociones en un unico almacén de datos a partir del cual poder extraer información de las siguiente manera:

1) A nivel de reporting y consultas ad-hoc.

2) Navegación dimensional por los datos utilizando herramientas OLAP.

3) Preparacion de cuadros de mando para los diferentes niveles de actuación: bodegas, representantes de ventas, servicios centrales, dirección, etc.

4) Establecer las bases para estudio de promociones,  analisis y dataming en los clientes del Club de Vinos.

Analisis de requerimientos

Despues de varias reuniones con el responsable comercial, los responsables comerciales de cada bodega/delegación y el departamento de sistemas, y a partir de los requerimientos de información, se decide:

a) El ambito del proyecto será, como hemos indicado, los procesos de negocio relacionados con ventas. El objetivo es tener un mejor conocimiento de la casuistica de ventas, incluyendo las promociones, para poder analizar la información desde todas las perspectivas posibles de interes para el negocio.

b) Granularidad de la información: en un principio se piensa, a partir de los requerimientos de información, quedarnos en el detalle acumulado de ventas por dia, producto, cliente. Pero durante la revisión de requerimientos se amplia el ambito del proyecto con los estudios referentes al Club de Vinos y se decide incluir también en el DW la información referente a promociones, para poder explotarla posteriormente. Por eso se decide que el nivel de granularidad sea cada uno de  los pedidos de ventas, para poder reflejar en el las promociones y permitir luego estudios mas profundos a nivel de data mining. Obviando la sumarización, permitimos mas funcionalidades en el futuro. Como incoveniente, el numero de registros será mucho mayor, pero no es significativo en este proyecto, pues se trabaja con unos 2000 clientes, unas 500 referencias y no mas de 100 mil pedidos anuales.

c) Dimensiones: las perspectivas por las que se quiere analizar la informacion en la empresa BVSA son las siguientes.

Dimensión Tiempo: incluira los atributos dia, mes, año, semana, trimestre, semestre, dia semana, festivo o no.

Dimensión Producto: incluira los atributos material, familia, d.o., varietal, formato venta, unidad medida, l/envase, linea de producto, target.

Dimensión Cliente: incluira todos los atributos relacionados con el cliente, como código cliente, agrupador, responsable comercial, canal, tipo de cliente, pais, region, provincia, cod. postal y población.

Dimensión Logística: incluira los atributos relacionados con la distribucion (dejando preparado el sistema para el futuro centro logistico). Los atributos serán centro y sus propiedades (numero almacenes, capacidad, etc).

Dimensión Promociones, Ofertas, Dtos: incluira el tipo de promoción, fechas de la promoción, dto. aplicado y toda la información de interes relacionada con la promoción (publicidad, cupones, carteleria, ubicacion, etc).

d) Hechos: los valores de negocio que vamos a querer analizar serán los siguientes:

Unidades Vendidas, Venta en litros, Precio Unitario, Importe Bruto, Importe Descuentos, Importe Promociones, Importe Neto, Coste Unitario, Coste Total, Margen %, Margen Unitario, Margen total.

De este analisis podemos ya construir el modelo conceptual del DW de la empresa, que podría ser algo asi:

Desarrollando el modelo conceptual, vamos a profundizar en cada una de las dimensiones, construyendo el modelo lógico. El modelo inicial sería el siguiente:

El desarrollo de cada una de las dimensiones de análisis y de los indicadores de estudio de nuestro modelo es el siguiente:

Dimensión Tiempo.

Como todos sabemos, es la dimesión básica de cualquier modelo, pues el tiempo siempre es una de las perspectivas por las que queremos analizar la información. Los datos que forman esta dimensión los generaremos para un periodo de tiempo determinado (por ejemplo 10 o 20 años, para incluir periodos pasados y periodos futuros). Vamos a intentar generar el mayor número posible de atributos para esta dimensión para facilitar luego el análisis. Ademas, el número de componentes o registros de esta dimensión va a ser limitado y no hay problemas de tamaño en la BD.

Incluira los siguientes atributos:

Fecha Clave: fecha en la notacion yyyymmdd para procesarla como enteros. Será la clave de la dimensión.

Fecha: fecha en la notación habitual (dd-mm-yyyy).

Fecha en texto: fecha formateada ( por ejemplo: 15 de abril de 2009).

Dia de la semana:  dia de la semana en texto (lunes, martes, miercoles,..).

Numero de dia de la semana: dia 1,2,3…7.

Dia del Mes: numero de dia de la fecha en el mes (dia 14, dia 28, dia 31).

Dia del Año: numero de día de la fecha en el año (dia 234, dia 365).

Semana: numero de semana donde se incluye la fecha (semana 23).

Semana del año: notacion año-semana para comparativas, cabeceras (YYYY-SS, 2008-45).

Mes: número del mes en el año (Enero = 1, Febrero = 2, etc).

Descripcion del Mes: descripción en texto del mes (Enero, Febrero, Marzo,…).

Mes del año: notación año-mes para comparativas,cabeceras (YYYY-MM, 2008-11).

Trimestre: trimestre donde se incluye la fecha (1T,2T,3T o 4T).

Trimestre del año: notacion año-trimestre para comparativas, cabeceras (YYYY-TT, 2008-1T).

Semestre: semestre donde se incluye la fecha (1S, 2S).

Semestre del año: notacion año-semestre para comparativas, cabeceras (YYYY-SS, 2008-1S).

Año: año de la fecha, con 4 digitos.

Festivo: indicador de si la fecha es un festivo o no.

Fin de semana: indicador si la fecha es fin de semana o dia entre semana.

Algunos de los atributos que incluimos para esta dimensión se podrían calcular en el momento de ejecutar las consultas, pero al ser una tabla pequeña, es preferible por temas de rendimiento tenerlos ya calculados y utilizarlos cuando nos haga falta según el requerimiento de información.

Dimensión Producto.

En esta dimensión incluiremos todos los atributos relacionados con los productos de venta o materiales, tales como las diferentes clasificaciones, propiedades, etc. En nuestro modelo, tendrán relevancia las siguientes:

Material: clave que identifica a cada uno de los productos vendidos en la compañia. Es la clave de la dimensión. Utilizaremos los códigos numericos definidos en nuestro ERP, que ya estan homogeneizados.

Descripcion Material: texto que describe las características del material (por ejemplo, Viña Pomela Reserva 2004 ).

Familia: clasificación de los productos por naturaleza del producto (codigo nemotecnico que incluimos en el DW para facilitar el trabajo de los usuarios y para las relaciones con el sistema operacional).

Descripcion Familia: texto que describe los diferentes valores del atributo familia ( 01 Blanco Joven, 02 Blanco Crianza, 03 Rosado, 04 Tinto Joven, 05 Tinto Barrica 6m, 06 Tinto Crianza, 07 Tinto Reserva, 08 Tinto Gran Reserva, 09 Tinto Seleccion, 20 Aceites, 30 Productos Gourmet, 40 Quesos). Los ultimos códigos nos indican que tambien se venden productos que no son vinos, y que tambien tendremos en cuenta en nuestros análisis.

Denominación Origen: texto que nos describe la denominación de origen a la que esta adscrita el vino. Los posibles valores para este atributo serían Ribera del Duero, Rueda, Toro, Somontano, Priorat, Yecla, La Mancha y Alicante. Igualmente, hay otros vinos que estan adscritos a la categoria Vinos de la Tierra, Vinos de Autor y Vinos sin DO.

Varietal: composición del producto (variedad/es de uva utilizadas en su elaboración). Por ejemplo, Monastrell, Tempranillo, Syrah, Merlot, Carbenet, Petit Verdot, Verdejo y las combinaciones de variedades.

Formato venta: formato de venta en el que se distribuye el producto. Cada vino (aun viniendo de la misma elaboración), tendrá un codigo de producto diferente según el formato en el que se venda. Ejemplos de valores para este atributo serán Granel, Tetra Brik, Botella, Pack, Bote, Paquete (estos ultimos para productos no vinicolas).

Unidad medida: unidad de medida utilizada para la venta de los materiales (C/U, Litros, Cajas, etc).

Litros: equivalencia en litros.

Linea de producto: otra clasificación de los productos. Se incluyen los códigos del sistema operacional para facilitar el trabajo de los usuarios.

Descripción linea de producto: texto que describe cada una de las lineas de producto definidas. Por ejemplo 01 Vinos Mesa, 02 Bodega Seleccion, 03 Cavas, 04 Gran Bodega, 05 Vinos unicos, 10 Merchandising, 20 Ediciones y Publicaciones, 30 Productos Selectos. Nos permiten ver la clasificación de los materiales desde otra perspectiva.

Target: indica el segmento de mercado al que esta dirigido el producto ( Gran Consumo, Gourmet, Lujo, Jovenes, etc).

Todos estos datos estan presente en nuestro sistema operacional. El modelo lógico para esta dimensión seria algo asi:

Dimensión Cliente.

La dimensión Cliente nos permitira el análisis desde la perspectiva de todos los atributos relacionados con el cliente, como código cliente, agrupador, responsable comercial, canal, tipo de cliente, pais, region, provincia, cod. postal y población. No van a permitir analizar quien nos compra, bajo que agrupaciones, clasificaciones y criterios geográficos (que van asociados a cada cliente).

Cliente: identifca al cliente que realiza una transacción de compra. Sera la clave de la dimensión Cliente.

Descripcion Cliente: Datos identificativos de cada cliente. Por ejemplo, Mercadona Muchamiel I, Carrefour San Juan, etc.

Agrupador: los clientes que pertenecen a una cadena (por ejemplo, grandes superficies), estan identificados individualmente (cada tienda, centro, etc). El agrupador es el código que agrupa a todas las tiendas o centros en un nivel superior (que podría coincidir con el nivel de facturación o contable). Por ejemplo, todos los clientes que realizan pedidos que pertenecen a Centro Comerciales Carrefour compartiran el código de agrupador, que los agrupa a todos con fines analíticos.

Descripcion Agrupador: Datos identificativos del cliente agrupador. Para el ejemplo anterior, la descripción sería Centros Comerciales Carrefour.

Responsable Comercial: responsable del departamento comercial que tiene asignado al cliente.

Canal: canal de venta. Incluimos la codificación del sistema operacional para facilitar el uso.

Descripcion Canal: descripciones de cada canal de venta (por ejemplo, 01 Venta Club, 02 Venta Directa, 03 Venta Intermediacion Propia, 04 Venta Representantes, etc.).

Tipo de Cliente: clasificacion de los clientes según su naturaleza. También se incluye la codificación del ERP.

Descripcion Tipo Cliente: descripciones de los tipos de cliente ( 01 Cliente contado, 02 Cliente Club, 03 Minorista, 04 Restauracion, 05 Hoteles, 06 Tiendas Gourmet, 07 Tiendas Bodega, 08 Supermercados, 09 Grandes Superficies, 10 Hipermercados, 11 Centrales de compras, …).

Todos los atributos relacionados con el ámbito geográfico del cliente (relacionado con el punto de venta): Pais, Region, Provincia, Cod.Postal, Poblacion.

Codigo Nielsen: codigo asociado al cliente para estudios de mercado.

El modelo lógico para la dimensión cliente sería el siguiente:

Dimensión Logística.

En esta dimensión veremos la información desde la perspectiva logística de la venta, es decir, desde que lugares se sirven las ventas. Esta información nos puede permitir en el futuro optimizar nuestros procesos de distribucion. Los atributos de esta dimensión serán los siguientes:

Centro: centro de distribución. Sera la clave de la dimensión logística.

Desc. Centro: Identificacion del centro logístico.

Todos los atributos relacionados con el ámbito geográfico del centro logístico: Pais, Region, Provincia, Cod.Postal, Poblacion. Todos los datos geográficos, que se utilizan aquí o en la dimensión cliente estan homogeneizados.

Capacidad Almacenamiento: volumen de almacenamiento disponible en m3.

Almacenes: numero de almacenes en el centro logistico/bodega.

Tecnologia almacén: tipo de tecnologia usada para la gestión del almacén.

El modelo lógico en la dimensión  logística sería el siguiente:

Dimensión Promociones.

La dimensión promociones es una dimensión causal (a diferencia de todas las que hemos visto hasta ahora, que eran casuales). Esto implica que el contenido de esta dimensión afecta a los procesos de venta y no todo ha quedado registrado en los sistemas transaccionales (en el erp seguramente solo haya quedado identificada la promoción y los descuentos o ventajas que aplica), pero no otros muchos aspectos.

Los atributos de la dimensión promociones serán los siguientes:

Promocion: código que identifica a la promoción. Es la clave de la dimensión.

Desc.Promoción: texto explicativo de la promoción.

Tipo Promocion: promocion propia, promoción estudio, promoción en cadena, promoción-concurso, etc.

Tipo Descuento: descuento porcentual, descuento importe, 3×2, 2×1, regalo por volumen, etc.

Tipo Publicidad: folletos, prensa, internet.

Tipo Accion: Acciones comerciales incluidas en la campaña (degustación, stand, promotores, vehiculos, etc).

Fecha Inicio: fecha inicio de la promoción.

Fecha Fin: fecha final de la promoción.

Dimensión Pedido de Venta (Dimensión Degenerada).

La dimensión pedido de venta, que es clave en la tabla de hechos, es una dimensión degenerada. Esto significa que la clave se identifica a si mismo y no tiene ningún atributo adicional, por lo que no es necesario construir una tabla de dimensión para ella. Solo lo vamos a utilizar para realizar analisis a nivel de transacción y no es necesario identificar ni concretar nada mas en el.

Indicadores de nuestro modelo (Hechos)

Los valores de negocio que querremos analizar para nuestras perspectivas o dimensiones serán los siguientes:

Unidades Vendidas: unidades vendidas en cada una de las lineas de detalle del pedido.

Venta en litros: conversión a litros (en el caso de que fuera posible) de las unidades vendidas.

Precio Unitario: precio unitario de venta.

Importe Bruto: importe bruto de venta (sin incluir condiciones comerciales de los clientes ni promociones).

Importe Descuentos: importe descuentos aplicados, según las condiciones pactadas con los clientes (no son las promociones).

Importe Promociones: importe descuentos aplicados por las promociones en curso.

Importe Neto: importe neto de las ventas, unas vez descontados los descuentos y promociones.

Coste Unitario: coste unitario del producto.

Coste Total: importe del coste total.

Margen %: porcentaje de margen de la venta.

Margen Unitario: margen unitario de venta por producto.

Margen total: margen total de la transacción.

Habrá otros muchos indicadores que se podrán construir a partir o como calculo de los definidos. A la hora de construir el modelo físico, determinaremos que indicadores nos guardamos en la base de datos y cuales se calcularan en el momento de ejecutar los informes o herramientas (por ejemplo, los indicadores no sumarizables). También puede haber indicadores que nos interese tener calculados en la base de datos por temas de diseño o por evitar errores en los cálculos según el usuario que lo defina (sería algo asi como unificar un indicador).

Nuestro modelo lógico completo, incluyendo todas las dimensiones e indicadores sería el siguiente:

Posted in Business Intelligence | 2 Comments »

6. Nuestro primer proyecto. Definición y consideraciones iniciales.

Posted by Roberto Espinosa en 5 diciembre 2009


Hasta aquí hemos visto en que consiste un sistema Business Intelligence, que tecnologias se utilizan en la actualidad para su desarrollo, los diferentes elementos que lo componen (bases de datos, herramientas, técnicas, etc), cuales son las tendencias actuales en dicho campo  y una metodología básica para poder abordar un proyecto de este tipo.

Partiendo de aqui, vamos a pasar de la teoria a la práctica y vamos a afrontar el desarrollo de nuestro primer proyecto. Es un proyecto totalmente ficticio, basado en consideraciones generales y con el que intentaremos plasmar todo lo visto hasta ahora.

Proyecto EnoBI: nos vamos a mover en el ambito de una empresa productora de vino. Es una empresa de tamaño medio, en plena fase de expansión con la compra de varias bodegas por todo el territorio nacional y en diferentes denominaciones de origen, que pretende dotar a este plan de expansión de las herramientas tecnologicas apropiadas para afianzar dicho proceso. El proyecto se va a afrontar gradualmente, liderado por el departamento de ventas, cuyo manager ha utilizado herramientas de este tipo en otras organizaciones del sector de distribución y es consciente de las posiblidades que van a ofrecer a la empresa.

El proyecto va a consistir en la construcción de un DW departamental (Datamart), para el analisis de ventas y margenes. El Datamart se explotara con herramientas de query y reporting en un primer nivel, y con herramientas OLAP en un segundo nivel. Se prepararan cuadros de mando en los diferentes niveles del departamento de ventas para facilitar el analisis de los indicadores de negocio o KPI según el ambito de actuación. Igualmente, habra un cuadro de mando para dirección donde se presentara la información mas importante relacionada con la marcha de las ventas.

En un segundo nivel, se pretenden utilizar técnicas de analisis y de datamining para determinar nichos de mercado a los que ofrecer productos especificos (o identificar targets para el desarrollo de nuevos productos) y para el analisis de las promociones que se realizan con determinados clientes del sector distribución.

Es un proyecto piloto dentro de la empresa y si tiene exito, se aplicara a otros ambitos de la compañia.

El proyecto se quiere implementar en la fase final con dos tipos de herramientas: herramientas propietarias y herramientas open source (que definiremos mas adelante).

La fase de definición de objetivos, analisis y toma de requerimientos, asi como de modelización, diseño, construcción de la BD e implementación de los procesos ETL será común a ambos “subproyectos”.

Las herramientas a utilizar durante el proyecto serán las siguientes:

1) Herramientas para gestión de proyectos: Microsoft Project 2007.

2) Herramientas ETL: Talend y Pentaho Data Integration.

3) Herramientas para la gestión de  incidencias del proyecto y el mantenimiento posterior: Eventum (Mysql).

4) Base de datos: Mysql.

5) Gestor de documentación y herramienta de colaboración: Alfresco.

En la parte final, las herramientas para la explotación del DW serán las siguientes:

5) Herramientas BI OpenSource: Pentaho Community Edition ( y la trial Pentaho BI Suite 3.5 ).

6) Herramientas BI Propietarias: Microstrategy Reporting Suite (y la trial Microstrategy 9 para los ambitos no cubiertos por las version  anterior).

Posted in Business Intelligence | Leave a Comment »

 
Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 172 seguidores

%d personas les gusta esto: