Data Applications

Para la implementación del Mercado de Datos se utilizó la herramienta Pentaho Data Integration(PDI). Esta herramienta proporciona una interfaz de usuario sencilla e intuitiva para la manipulación de los datos desde una fuente externa. PDI tiene un entorno gráfico de

- 55 -

desarrollo, usa de tecnologías estándar: Java, XML, JavaScript, es fácil de instalar y configurar, es multiplataforma, incluye cuatro herramientas de las cuales se utilizo el SPOON para diseñar transformaciones ETL usando su entorno gráfico.

El Sistema de Gestión de Base de Datos utilizado para la solución es MySQL.

Se implementaron dos transformaciones una para los mayores consumidores y otra para los menores porque la información en ambas fuentes de datos presenta conflicto de nombres y de estructura, el conflicto de nombre es cuando se hace uso de diferentes nombres para el mismo dato, y el conflicto estructural se refiere a representar de manera diferente el mismo dato en las diferentes fuentes de datos, lo cual se describe en el epígrafe 1.4.

Transformación de los mayores Consumidores.

En la figura 3.6 se muestra la transformación de los mayores consumidores. En esta trasformación se hace el proceso de extracción desde las fuentes de datos de los ficheros Excel que tienen los datos de los mayores consumidores y de la base de datos del sistema MEnTOr para integrarla y almacenarla en las tablas de dimensiones, y de hechos. La transformación también ejecuta la carga de las tablas agregadas hch_agg_org_mayores y hch_agg_plan_real_mayores, la granularidad de ambas tablas es consumo de energía por organismo por mes

- 56 -

Paso # 1 de la transformación: Mayores

Este paso es una entrada de fichero Excel. En la figura 3.7 se comienza con la descripción de este paso. Este paso permite leer los datos de uno o más ficheros Excel y ficheros OpenOffice. Lo primero que se configura es la localización del fichero Excel que se debe leer, de la siguiente manera:

Step Name: especifica el nombre de la trasformación, el nombre tiene que ser único en la misma transformación.

File or directory: especifica la localización del fichero que se desea cargar, con ayuda del botón Browse y se acepta con el botón Add.

Selected files: se muestra el fichero escogido.

Figura 3.7 Configuración del paso Mayores

Para configurar las hojas del fichero Excel que se van a leer, se usa la opción Sheets, como se muestra en la figura 3.8, esta opción permite elegir las hojas disponibles en la ventana Enter List y muestra las que finalmente se eligieron en “List of sheet to read”. Para indicar que las hojas se comiencen a leer desde el inicio se debe especificar el valor cero con los campos Star row y Star column.

- 57 -

Para obtener los campos que se desean del fichero Excel se utiliza el botón Get fields from header row de la opción Fields y para verificar la información procedente del fichero se utiliza el botón Preview row. Luego de terminada todas las configuraciones se acepta la fuente de datos del fichero Excel.

Figura 3.8 Configuración de la entrada del fichero Excel.

Paso # 2 de la transformación: Ultma_actualización:

Este paso es de tipo Get system Data, el cual se utiliza para obtener la fecha de los datos cargados en el Mercado de Datos, su configuración es muy fácil en la columna Field se pone el nombre del campo que irá al flujo de datos y en la columna Type se muestra una lista desplegable con un conjunto de opciones, de la cual se escoge la información del sistema que se desee, en el caso de la implementación se escogió fecha del sistema(fijo) y el nombre del campo que va al flujo es ultma_actualización, e indica la fecha de cada carga en el Mercado de Datos.

- 58 -

Paso # 3 de la transformación:Calculadora

El paso Calculadora es de tipo calculator, el cual se encuentra en la carpeta Transform en la opción Desing. Es utilizado para calcular la fecha que viene del campo mes en el flujo los datos del fichero Excel. De este paso salen los atributos: año y número del mes (num_mes).

Paso # 4 de la transformación: Cal_mes

Este paso es de tipo Modified Java Script Value, que se encuentra en la carpeta Scripting en la opción Desing. Se utiliza para calcular el mes anterior al mes calculado en el paso anterior, porque los ficheros Excel que son enviados a la oficina cada mes contienen la fecha del mes posterior y con este paso se obtiene el mes real.

Paso # 5 de la transformación: nombre_mes

El paso nombre_mes es de tipo Value Mapper opción que se encuentra en la carpeta Transform en la opción Desing. Este paso es utilizado para correlacionar un dato a otro dato. Ver figura 3.9

Figura 3.9 Configuración del paso nombre_mes Opciones de configuración:

Step name:especifica el nombre de la configuración.

- 59 -

Targed field name (empty==ovrwrite): se especifica el nombre del campo que va a recibir los valores asociados.

Field values: contiene dos columnas para asociar valores. En la columna Source Value se indica los datos originales y en Targed value los nuevos valores que serán almacenados en nombre_mes.

Para completar los atributos de la dimensión Mes, se utilizan adicionalmente otros dos pasos de tipo Value Mapper para trimestre y valor_trimestre. Se configura de la misma manera que se explicó anteriormente. Estos atributos son utilizados para brindar al usuario final la posibilidad de análisis de la información sea más amplio y entendible ya que el análisis por número del mes y también por el nombre del mes.

Paso # 8 de la transformación: dim_año

Este paso es de tipo Combination lookup-update y se encuentra en la carpeta Data Warehouse en la opción de Desing. En este paso se crea la dimensión año y se almacenan sus datos. En la figura 3.10 se muestra la configuración de la misma.

- 60 - Opciones de configuración:

Step name: especifica el nombre de la transformación.

Connection: especifica el nombre de la conexión de la base de datos donde va a almacenarse la tabla de dimensión, en caso de que no se haya hecho ninguna conexión, se crea una nueva.

Target table: se especifica el nombre de la tabla de dimensión.

Key fields (to look row in table): Contiene dos columnas para obtener los campos del flujo de datos y definir los campos de la dimensión. En la columna Field in Stream se específican los campos procedentes del flujo de datos y estos se obtienen presionando el botón Get Fields. En columna Dimension field se específica los campos que se almacenarán en la dimensión.

Technical key field: especifica el nombre de la columna de la llave primaria (llave subrogada) en la tabla de dimensión. La llave subrogada es la combinación de los campos del flujo de entrada en la tabla.

Creation of the technical Key:especifica cómo se va a generar la llave subrogada.

 Use table maximum + 1: Se crea una nueva llave subrogada a partir de la llave máxima. Notar que el máximo es siempre almacenado, por lo que no es necesario calcular el máximo para cada nueva fila.

 Use sequence: Específica la secuencia, si usted quiere usar una secuencia de base de datos en la conexión de la tabla para generar la llave subrogada. (típico para Oracle).

 Use auto increment field: Usa un campo de auto incremento en la tabla de dimensión para generar la llave subrogada (típico de DB2).

Una vez configurado el paso se ejecuta la consulta que crea la tabla de dimensión en la base de datos con el botón SQL.

Los pasos dim_trimestre, dim_mes, dim_circuito y dim_organismo son del mismo tipo que el paso dim_año, por lo que se configuran de la misma manera. Estos pasos corresponden a las dimensiones trimestre, mes, circuito y organismo respectivamente.

Configuración para establecer conexión a la base datos.

En la transformación se define una conexión a la base de datos dm_energy donde se almacenan todas las tablas de dimensiones, de hechos y de hechos agregados. La conexión a la base de datos se puede ver en la opción view en la carpeta Database connections y también da la posibilidad de crear una nueva conexión a la base de datos dando click derecho encima de la carpeta y seleccionando la opción New.

- 61 -

La otra manera de crear una conexión a la base de datos es a través de los pasos que requieran de una conexión, como por ejemplo Combination lookup-update, Dimesion lookup- update, Table Input, Table output, entre otros. En estos pasos la opción de configuración que se utiliza para establecer la conexión es el campo conecction, a través del botón New. Ver figura 3.11.

Opciones de configuración:

En la parte más izquierda de la figura se escoge la categoría de configuración general. Conecction name: se indica el nombre de la conexión que tiene que ser único.

Connection Type:aparece una lista de los diferentes gestores de base de datos, aquí se escoge el Sistema de Gestión de Bases de Datos al que se hará la conexión.

Access: se escoge la interfaz que se usará para establecer la conexión.

Seetings: muestra un número de campos usados para especificar detalles de la conexión actual.

Host name: nombre de la máquina donde está el Sistema de Gestión de Bases de Datos.

Database name: nombre de la base de datos donde se almacenarán las tablas de dimensiones, hechos y agregados.

Port number: número del puerto según el tipo de conexión.

User name: nombre del usuario administrador de la base de datos.

- 62 - Pasword: contraseña del usuario administrador.

El botón Test se utiliza para probar que la conexión es correcta, luego de verificar que es correcta, se acepta.

Paso # 11 de la transformación: LEFT_JOIN

El paso LEFT_JOIN es de tipo Merge Join que se encuentra en la carpeta Joins en la opción Desing. Es usado para hacer un acople entre el flujo de datos procedente del fichero Excel y la base de datos del sistema MEnTOR, porque se necesitan obtener los códigos originales de los clientes que han sido cambiado en el fichero Excel y dicho códigos se encuentran almacenados en la base de datos.

Este paso acopla dos flujos de entrada por los atributos de acople y como requerimiento del paso las entradas de datos deben ser ordenadas por dichos atributos de acople. También permite ejecutar los diferentes tipos de acoples clásicos entre los flujo de datos procedentes de las diferentes entradas, las opciones de acoples que incluye son INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER.

Para ordenar los dos flujos de entrada se utilizan los pasos Ordenar_Codcli y Ordenar_Codcli2 (Ver figura 3.12) que son de tipo Sort Rows y se encuentran en la carpeta Transform; para la configuración de ambos, lo primero que se hace es presionar el botón Get fields y se escogen los campos que se desean ordenar, en la columna Ascending se especifica la manera en que se ordenan los datos, la columna Case sensitive compare? es opcional. El paso Ordenar_Codcli ordena los códigos de clientes procedentes de la fuente de datos del fichero Excel y el paso Ordenar_Codcli2 ordena los códigos de clientes procedentes de la fuente de datos de la base de datos del sistema MEnTOR. La fuente de datos del sistema MEnTOr es obtenida a través del paso MEnTOr_codcli que es de tipo Access Input y se encuentra en la carpeta Inputs, este paso permite leer los archivos directamente desde Microsoft Access. La configuración de este paso es similar a la del paso Mayores. En la opción Fields se configura la localización del fichero Access que contiene la base de datos, en la opción Contet se configura el campo Table para seleccionar la tabla que será accedida para leer los datos a través del Get table y en la opción Fields se obtienen los campos de dicha tabla. La tabla utilizada en este paso es Empalme_codcli la información que ella contiene es:

No. de cliente:este atributo contiene el código nuevo que se le asigna a los clientes. Contactar con: este atributo contiene el código original que tenían los clientes antes de que su código fuera cambiado.

- 63 -

Esta tabla contiene los códigos de clientes cambiados con su correspondiente código original. El fichero Excel contiene todos los códigos de los clientes, los que no han sido cambiados y los cambiados.

Figura 3.12 Configuración de los pasos Ordenar_Codcli y Ordenar_Codcli2 En la figura 3.13 se muestra la configuración del paso LEFT_JOIN.

Opciones de configuración:

Step Name: especifica nombre del paso.

First Step: especifica el primer paso de entrada para el acople (left)

Second Step: especifica el segundo paso de entrada para el acople (right) Join Type: selecciona el tipo de acople a utilizar.

Keys for 1st step: especifica el campo llave ordenado, ya seleccionado en el paso anterior. El botón Get key permite obtener los campos.

- 64 -

Keys for 2nd step: especifica el campo llave ordenado, ya seleccionado en el paso anterior. El botón Get key permite obtener los campos.

En este paso el tipo de acople seleccionado fue LEFT OUTER, porque muestra todos los valores de la tabla izquierda y los valores de la tabla derecha que no coincidieron con la clave toman valores nulos.

Si el código de cliente (codcli) procedente del fichero Excel coincide con el código del cliente cambiado (No. de cliente) procedente de la base de datos, significa que el código del cliente en el fichero Excel sufrió un cambio y como el acople coincidió se obtienen los valores de los atributos (Contactar con) y (Nombre de cliente); en caso contrario significa que el código del cliente mantiene su código original y el acople no coincide por lo que los campo Contactar con y Nombre de cliente toman valores nulos.

Paso # 12 de la transformación: Cal_id_centro

Este paso es de tipo Calculator, utilizado para calcular por una situación especial la llave natural que se usa en la dimensión dim_centros_mayores. Ver figura 3.14. Este paso provee un conjunto de funciones predefinidas que pueden ser ejecutadas sobre los valores de entrada en las columnas Field A, Field B y Field C. En la columna Calculation se muestra un listado con todas las funciones. La función utilizada para el paso Cal_id_centrofue NVL(A, B), esta función devuelve el valor del campo A si no es nulo y si no devuelve el valor del campo B. En el paso anterior obtenemos el campo (Contactar con) que es el valor de entrada de la columna Field A y el otro campo obtenido es (codcli) utilizado como valor de entrada de la columna Field B.

Figura 3.14 Cálculo de la llave natural id_centro.

En la columna New field se especifica el campo que almacena el cálculo realizado por la función escogida. Si el código del cliente del fichero Excel fue cambiado la función devuelve el campo (Contactar con), obteniéndose el código original, si el código del cliente no cambió la función, devuelve el campo codcli porque el campo Contactar Con va a tener valor nulo. De esta manera el atributo id_centro tendrá siempre el código original de cada cliente, cumpliendo con los requisitos de llave natural.

- 65 -

Pasos que integran las fuentes de datos

En la trasformación de la figura 3.6 notar que se utilizan 2 pasos de tipo Merge Join con el objetivo de integrar la fuente de datos procedentes de los ficheros Excel y la base datos del sistema MEnTOr. El paso INNER JOIN es utilizado para obtener los códigos de organismo y su nombre correspondiente ya que esta información no se encuentra en el fichero Excel, el tipo de acople es INNER JOIN y se realiza a través del código de cliente (codcli).

El otro paso acopla por codcli, mes y año, es utilizado para obtener los planes del consumo de energía, el tipo de acople es INNER JOIN y se realiza por codcli, mes y año. De esta manera se obtiene la información para crear la tabla de hechos consolidada que compara el consumo real de energía contra lo planeado.

La configuración de ambos pasos se hace de manera similar a lo anteriormente explicado en el paso LEFT_JOIN.

Paso # 16 de la transformación: dim_centro

Este paso es de tipo Dimension Lookup-Update que se encuentra en la carpeta Data Warehouse en la opción Desing. Este tipo de paso implementa los tipo de dimensiones lentamente cambiantes 1 y 2. La configuración del paso dim_centro se muestra en la figura 3.15.

El paso Dimension Lookup-Update opera de dos modos diferentes:

Modo update: en este modo las opciones de actualización son establecidas y combina los tipos de dimensión lentamente cambiante 1 y 2.

Modo lookup: en este modo se deshabilitan las opciones de actualización y trabaja como una dimensión lentamente cambiante de tipo 2.

Configuración General:

Los campos Step Name, Conecction y Target table son similares a la configuración explicada en el paso dim_año.

Modo de operación:

Para especificar el modo de operación se utiliza la opción Update the dimension. Cuando la opción está marcada indica que la dimensión está en modo update, en caso contrario está en modo lookup, mencionado anteriormente.

En la opción Keys se configura Key fields (to lookup row in dimension): usada para mapear la llave natural de la tabla de dimensión con el flujo de datos de entrada.

Dimension field: columna utilizada para especificar cual columna en la tabla de dimensión constituirá la llave natural.

- 66 -

Field in stream: es usado para especificar el campo procedente de la fuente de datos que tiene que coincidir con el campo de la columna Dimension field.

Estos campos son comparados siempre sobre la base de la igualdad.

Technical key field: especifica el nombre de la columna de la llave primaria(llave subrogada) en la tabla de dimensión.

Creation of technical key: ofrece varias opciones para el control automático de la generación de la llave subrogada.

Este paso tiene un número de opciones a configurar para poder mantener la historia en la dimensión lentamente cambiante de tipo 2 como son:

Version field: usado para especificar el nombre de la columna en la tabla de dimensión que almacena el número de versiones de la fila. La combinación de la llave natural y el número de versión puede ser usada como un único identificador de una fila en la tabla de dimensión. El paso Dimension lookup / update en modo update almacena apropiadamente el número de versión siempre que el paso adicione una nueva fila en la tabla de dimensión.

La dimensión lentamente cambiante de tipo 2 tiene un par de columnas donde especifica el período de tiempo en que ocurre el cambio.