16.1. Identificación origenes de datos. Utilizando Data Profiling.

Es fundamental antes de abordar la construcción del sistema ETL, la correcta identificación de los origenes de datos que  permitirán el llenado de nuestro DW. Para ello, sería conveniente la realización del llamado Mapa de Datos Lógico. Este mapa lógico será la guia que utilizaremos durante todo el desarrollo de los procesos ETL, pues los criterios y premisas que establezcamos en el deberán ser seguidas y cumplidas por todas las tareas a realizar hasta la finalización de todos los procesos de llenado. En una entrada anterior del blog realizamos una aproximación a este mapeo lógico, que vamos a ampliar un poco.

Ejemplo sencillo de mapeo Sistema Origen - Sistema Destino

Básicamente, el mapa lógico debería de incluir al menos los siguientes componentes (siempre queda en la creatividad de cada uno añadir otros aspectos de interes o cosas a tener en cuenta de cara a la construcción de procesos):

• Origen de datos: identificar la tabla, columna, el tipo de datos, el tipo de objeto dentro de nuestro modelo dimensional (dimensión, tabla de hechos, etc) y que tipo de gestión de Dimensiones Lentamente Cambiantes (SCD) vamos a realizar en el caso de que haya cambio en los valores del campo. Estableceremos los origenes de datos candidatos que habrá que validar antes de considerarlos definitivos (por si no tuviesen la calidad necesaria) o por si no cumpliesen los requisitos deseados.
• Destino: aquí indicaremos cual va a ser el destino de los datos. Detallaremos exactamente la/s tabla/s y campos que forman las tablas de dimensiones y hechos y su tipo de datos. Sera el lugar de destino de los datos extraidos de los sistemas origen.
• Transformación a realizar: finalmente, determinaremos la transformación necesaria a aplicar en los datos origen para realizar el llenado de los datos destino. Es necesario en esta sección considerar cualquier operación que haya que realizar sobre los datos ( codificación/decodificación, suma, tratamiento de cadenas, mapeo contra otras tablas, corrección de valores incorrectos, transformación a valores normalizados, etc). Para esto, habrá que realizar una verificación profunda de los valores de los datos en el sistema origen, e intentar no dejar sin considerar ninguna excepción o caso especial que se pueda presentar en los valores de los datos.

Teniendo en cuenta todos estos aspectos, un buen Mapa de Datos Lógico podría ser el siguiente:

Ejemplo de mapa lógico de procesos ETL

Como ayuda para la realización del mapa lógico, hemos de basarnos en herramientas y técnicas que nos permitan descubrir, por un lado, las características del diccionario de datos, y por otro lado, las características de los propios valores de los datos para descubrir casuisticas especiales, problemas de calidad en los datos que habrá que corregir, procesamientos a realizar,  etc.

Origenes de datos más habituales.

Podemos disponer de multitud de origenes de datos para llenar nuestro Dw. Algunos de los más habituales pueden ser los siguientes:

• Sistemas ERP: la mayoria de empresas trabaja hoy con sistemas ERP (Enterprice Resource Planning), desde los más sencillos a los grandes como son Sap, Oracle Financials, JDEdwards o Navision. Estos sistemas suelen dar soporte a todas las actividades de negocio de una empresa, desde las ventas, compras, fabricación, finanzas, mantenimiento o recursos humanos. Esto hace que sean sistemas complejos a los que puede ser complicado atacar sin conocer en profundidad su modelo de datos (pensar que Sap  pueden tener hasta 70 mil tablas). En muchas ocasiones, por temas de licencias, no se puede atacar directamente la base de datos y hay que utilizar una API para poder acceder a estos sitemas. Por ejemplo, para Sap tenemos un conector libre en Talend (y uno de pago en Kettle), pero para otros fabricantes habrá que utilizar otro tipo de herramientas. En estos sistemas también puede ser útil la utilización de las herramientas propias para intercambio de datos de las que dispone el propio sistema y que posiblemente ya estarán siendo utilizadas en la organización para otros cometidos.
• Mainframes: en grandes empresas como bancos y compañias de seguros aun se siguen utilizando este tipo de sistemas.
• Hojas de cálculo: no debería de ser habitual o fomentarse el uso de hojas de cálculo en las empresas. Pero vamos a ser realistas. Las hojas de cálculo se utilizan frecuentemente en todas las organizaciones, incluso formando completos subsistemas (o complementarios) en los departamentos de finanzas, ventas, etc. También en ocasiones en ellas se almacena información complementaria que no esta en los sistemas operacionales.
• Bases de datos de escritorio (tipo Access, OpenOffice Base, etc): con el mismo planteamiento que las hojas de cálculo.
• Datos estructurados externos: en muchas ocasiones vamos a tener organizaciones externas que nos proporcionan datos en formatos estructurados. Por ejemplo, podriamos tener información de estudios de mercado, de códigos postales, de encuestas, de resultados electorales, etc.
• Datos en formato XML: el lenguaje XML (Extensible Markup Language) se ha convertido en un estandar de hecho para la comunicación entre sistemas. La gran ventaja es que un fichero XML es un fichero de texto donde se almacen la información de una forma estructurada.
• Datos Online: muchisima información puede ser obtenida en la actualidad de internet y sus web, en forma de servicios web o Rss.

La mayoría de herramientas ETL disponen de complementos, conectores o utilidades para trabajar con todos estos  origenes de datos.

Por otro lado, en la mayoría de organizaciones, se da una combinación de todos estos orígenes de datos. Aunque casi siempre  partiremos de un ERP o aplicación de gestión integrada que almacenará la información en una base de datos relacional (de una forma estructurada), no toda la información podrá ser obtenida de este sistema  integrado. Ademas tendremos otros origenes de datos donde habra información de presupuestos, tesorería, estimaciones, mucha veces mantenidos en hojas de cálculo. El reto sera descifrar el contenido de estos datos, su estructura, calidad, significado de los datos y ser capaces de utilizarlos.

Utilizar herramientas de modelado de datos para ingenieria inversa. Analisis del diccionario de datos.

No siempre va a ser sencillo descubrir las tablas y campos en los sistemas origen donde se alojan los datos que son de nuestro interes. Si estamos trabajando con un ERP, seguramento dispongamos de un diccionario de datos donde poder analizar las características de las tablas donde estan los datos que nos interesan. Por ejemplo, en Sap tenemos una transacción, la SE13, donde podemos ver las características de cada tabla de la base de datos, incluyendo información adicional de los campos e incluso lógica de la aplicación (dominios de datos, posibles valores de campos, etc).

Diccionario de datos en el ERP Sap

Pero no siempre vamos a tener la suerte de trabajar con Sap y de tenerlo tan facil para descubrir el diccionario de datos. En otros casos, nos tocará «rascar» directamente en la base de datos o en estructuras de ficheros. Para descubrir la estructura de las tablas y campos de la base de datos tendremos multitud de opciones:

• Gestores de base de datos: en el caso de estar utilizando un motor de base de datos especifico, siempre vamos a tener la opción de utilizar el lenguaje Sql (con las características propias en lo referente al diccionario de datos de cada gestor RDBMS)  o las propias herramientas de administración donde poder analizar la estructura de datos de una tabla en concreto.
• Herramientas de modelado de datos: las herramientas de modelado de datos suelen tener utilidades de conectividad a diferentes bases de datos, que nos permiten hacer ingenieria inversa y, a partir de las tablas físicas de la base de datos, construir un modelo dentro de la herramienta, que podremos analizar con tranquilidad. Por ejemplo, Mysql Workbench nos permite hacer ingenieria inversa sobre una base de datos Mysql y recuperar en el modelo el diccionario de datos de las tablas que deseemos. Tiene la limitación de que solo se puede trabajar con Mysql. Si trabajamos con Oracle, puede ser interesante trabajar con Oracle Sql Developer, que nos permite analizar características del diccionario de datos (y conectarnos a otras bases de datos que no son Oracle a través de Jdbc). La herramienta Data Modeler de Oracle también incluye características de ingenieria inversa, al igual que herramientas mas avanzadas de modelado como Sybase Power Designer.

Ingenieria inversa con MySQL Workbench

• Herramientas ETL que incluyen metadata: la mayoria de herramientas ETL incluyen características para conectarnos y navegar por el diccionario de datos de las BD mas conocidas. Por ejemplo, podemos usar Kettle para este cometido, o de una forma mas avanzada el gestor de esquemas de Talend (tal y como vemos en la imagen). Con el uso de los componentes de que disponen esta herramientas para atacar diferentes fabricantes de bases de datos via Jdbc podemos facilmente «bucear» por los esquemas de datos.

Recuperacion esquema de BD con Talend

• Herramientas de Data Profiling: tambien suelen incluir características de metadatos para recuperar la información del diccionario de datos de una BD, y previsualizar el contenido de las tablas de una forma rápida.

En el caso de estar trabajando con ficheros, no nos quedará mas remedio que tirar de documentación (en el caso de que este debidamente realizada o exista) o utilizar herramientas para analizar el contenido y la estructura de los datos (os recomiendo la utilidad notepad++ (para el caso de ficheros de texto). También podemos utilizar herramientas ETL para analizar los ficheros, como Talend o Kettle, cuyos asistentes o visores nos pueden ser de gran utilidad, así como los asistentes de importación de datos de herramientas como Access o similares.

Utilizar el Data Profiling para revisar la calidad de los datos y para identificar transformaciones sobre los datos.

El Data Profiling es el proceso de recopilación de estadísticas y otra información sobre los datos existentes en nuestros origenes de información. Esta información va a ser de gran utilidad para el diseño de los procesos ETL. El Data Profiling también puede ser parte importante de cualquier iniciativa de calidad de datos, ya que antes de que la calidad de estos se pueda mejorar, habrá que establecer cual es el estado actual de los datos, y para ellos podemos valernos de estas técnicas.

El Profiling puede ser desarrollado a 3 niveles:

• Column profile: recopilación de estadísticas sobre los datos existentes en una columna individual.
• Dependency profile: analisis entre las dependencias de las diferentes columnas de una tabla.
• Join profile: chequeos de dependencias entre diferentes tablas.

El punto de partida  para el Profiling siempre es el Column Profile, que nos puede proporcionar información tan interesante como:

• Numero de valores distintos: cuantas entradas unicas contiene una determinada columna (en un análisis de clientes, que un valor exista varias veces puede, por ejemplo, reflejar registros duplicados).
• Numero de valores nulos (Null) o vacios en la columna: nos puede ayudar a identifcar registros cuyos datos estan incompletos.
• Valores mínimos y máximos en el campo, no solo a nivel númerico, sino también a nivel de texto.
• El uso de funciones estadísticas como suma, mediana, media o desviación estandar puede ser util también para sacar conclusiones sobre los datos.
• Longitud de los campos y patrones de cadenas: en muchas ocasiones los campos tendran que tener un formato determinado que obligara a una determinada longitud o al uso de unos determinados patrones (como por ejemplo el código postal, que en España ha de contener 5 dígitos). El control de la longitud de los valores de la columna o la busqueda de valores que no cumplan los patrones nos puede ayudar a encontrar valores incorrectos. Con los patrones de cadenas y expresiones regulares podemos buscar determinadas ocurrencias de valores que determinen datos incorrectos igualmente.
• Numero de palabras,numero de caracteres en mayusculas y minúsculas.
• Contadores de frecuencia de ocurrencia de valores.

Una vez estamos realizando estos análisis sobre los campos, las herramientas ofrecen funcionalidades de navegación por los datos para ver los valores exactos asociados a cada tipo de análisis o a cada tipo de valor.

Ademas de los análisis por columna, las herramientas Data Profiling nos pueden permitir descubrir dependencias entre diferentes campos de una misma tabla, como por ejemplo el análisis de los datos geográficos de un cliente y las relaciones entre los diferentes campos (codigo postal, población, provincia, región, etc).

El análisis de las relaciones entre tablas (Join profile) es más fácil de realizar, pues la verificación de dependencia entre valores de diferentes tablas puede ser sencilla de establecer (por ejemplo, buscar clientes en una tabla de pedidos que no existen en el maestro de clientes o materiales que no existen en el fichero maestro de productos).

Como Pentaho no dispone de una herramienta de Data Profiling, podriamos utilizar otras herramientas Open Source como DataCleaner y Talend Open Profiler para este cometido. Vamos a vemos un ejemplo utilizando esta última herramienta.

Data Profiling con Talend Open Profiler.

Vamos a realizar un analisis del tipo Column Profile sobre una columna de la tabla de clientes, en concreto sobre el campo código postal. Queremos validar que todos los códigos postales son correctos y que cumplen el patron de construcción de los valores. Para la prueba, hemos replicado un maestro de clientes de Sap en una base de datos externa con Oracle. Los pasos para realizar el análisis con Talend Open Profiler serán los siguientes:

• Definición de la conexión a la base de datos: al igual que en la herramienta ETL, Open Profiler tiene características de metadatos muy avanzadas. En primer lugar, definiremos la conexión a nuestra base de datos Oracle. En la conexión podemos navegar por las tablas y sus campos, filtrar las tablas visualizadas, etc.

Metadata en Talend Open Profiler

• Selección del tipo de análisis: a continuación, vamos a seleccionar el tipo de análisis que queremos realizar. Talend tiene un amplio repertorio de posibilidades de análisis, desde un análisis global sobre una base de datos o un catalogo/esquema (donde podremos ir profundizando en los diferentes elementos que los forman), análisis de columnas (de sus valores utilizando diferentes indicadores o patrones), hasta analísis de dependencia con otros campos, análisis de redundancia o correlación. En nuestro ejemplo, nos vamos a centrar en el análisis de una columna determinada, el código postal, para el que queremos validar su corrección y descubrir errores en los datos que tendremos en cuenta para construir los procesos ETL.

Selección del análisis a realizar

• Configuración del análisis de la columna: a continuación indicamos el tipo de indicadores que queremos analizar para la columna. Hay un montón de indicadores disponibles, que pueden variar según el tipo de columna que estemos tratando. Por ejemplo, podemos contar los registros, los valores nulos, el numero de valores distintos, los valores en blanco, valores mínimos y máximos de longitud del campo, valores estadísticos, etc.

Analisis de columna - selección de indicadores de análisis

• Selección de patrones de análisis: además de los indicadores, podemos seleccionar patrones de análisis para verificar, por ejemplo, que los valores de los campos cumplen unas determinadas reglas. En nuestro caso, vamos a verificar que los códigos postales de España tienen una longitud de 5. Para este cometido, Talend tiene un lote de patrones predefinidos (tanto Sql como a través de expresiones regulares), ademas de dejarnos la posibilidad de crear nuestro propios patrones.  En nuestro caso, vamos a definir un nuevo patrón para los códigos postales de España, con la siguiente expresión regular  ( ‘^(0[1-9]|[1-4][0-9]|5[0-2])[0-9]{3}$’ ), y el patrón lo vamos a añadir al análisis. Con esta funcionalidad podemos añadir todos los chequeos y validaciones que se nos puedan ocurrir. Para el tema de indicadores, también nos dejan la puerta abierta para incluir código en Java para añadir los propios. En este momento también podemos añadir filtros condiciones para limitar el ambito de análisis a unos valores determinados (por ejemplo, solo los clientes de un pais o de un canal de distribución).

Una vez están definidos todos los elementos a tener en cuenta en el análisis, procederemos a su ejecución. La herramienta se conecta a la base de datos para recopilar la información según los indicadores seleccionados y para realizar las validaciones según los  patrones que hayamos indicado. Los resultados se muestran en un visor gráfico tal y como vemos en la imagen siguiente:

Análisis gráfico de los resultados

Se nos muestra información numérica y gráfica de los diferentes análisis seleccionados. Tendremos una lista de frecuencia de valores, el menor y mayor valor descubierto, etc. Igualmente, se nos muestran los porcentajes de valores que cumplen el patrón de los códigos postales y cuales no. Desde el visor de resultados, podemos acceder con el menú contextual del botón derecho del ratón a visualizar los registros asociados a cada uno de los casos. Por ejemplo, para los registros que no cumplen el patrón, al seleccionar la opción de ver registros, nos aparecerá el visor de registros. Ahí también vemos la sentencia SQL que nos determina los valores devueltos con dicha condición:

VIsor de registros en el análisis de resultados

A partir de la visualización de los valores erroneos o incompletos, podremos establecer medidas de calidad a establecer en los sistemas origen, corregir los registros originales  o definir las transformaciones a realizar en los datos en el caso de que se cumplan determinadas condiciones. Pensar en lo utiles que pueden llegar a ser análisis de este tipo cuando estemos trabajando con grandes volumenes de información, en los que analizar los datos de forma manual puede ser una tarea artesanal muy compleja de realizar y en la que se nos pueden estar escapando muchas cosas. Este ejemplo es solo una introducción para ver las posibilidades de este tipo de análisis y lo que nos pueden ayudar en la a veces compleja de determinar las transformaciones a realizar sobre los datos para llenar nuestro DW con datos de calidad.

Este mismo tipo de analisis lo podemos utilizar para analizar validación de claves, dependencias e interelaciones de campos (por ejemplo, analisis de datos geográficos), desviación de valores determinados en un universo de valores, etc.

Bibliografía utilizada para elaborar esta entrada:

Pentaho  Solutions: Business Intelligence and Data Warehousing with Pentaho and  MySQL. Roland Bouman y Jos van Dongen.
The Data  Warehouse ETL Toolkit. Ralph Kimball y Joe Caserta.
Talend Open Profiler User Guide.
Ayuda Online de Java sobre expresiones regulares.