OVIEDO 2015

Ponencia: Tratamiento de las cuentas de las entidades locales en CYL con

una herramienta Big Data

Ponente: Gonzalo de Celis. Auditor-Técnico(informático)

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

1

Título: “Tratamiento de las cuentas de las entidades locales en CYL con una

herramienta Big Data.”

Introducción: Para ello se adquirió una aplicación de origen norteamericano TABLEAU,

que gestiona en España la empresa RUBICON_BI, de tratamiento masivo de datos, aplicada a

los estados contables en las Entidades Locales de Castilla y León, (2006-2013).

Objetivo: Explicar cómo nos hemos servido de esta herramienta para analizar, explotar y

publicar en Internet la información contable de más de 23.000 cuentas del sector público

local. Todas las semanas se actualiza la información con las nuevas cuentas rendidas en la

Plataforma.

Destacar: Tratamiento sencillo e intuitivo.

Requisitos: Conocimientos de informática.

Pasos principales por la Aplicación:

Conexión de datos.

o Múltiples conexiones a diferentes bases de datos u hojas de cálculo. En mi

caso, Base de datos de Access.

Análisis y explotación.

o La extracción de datos. Actualización de extractos.

o Algo parecido a las ‘tablas dinámicas’

o Filtros, Parámetros, Gráficos y Mapas.

Cuadros de mando informativos e interactivos.

o Hoja, Cuadro de mando ó Dashboard, Libro de trabajo.

Publicación web.

Uso externo de la información publicada.

Autor: Gonzalo de Celis de la Gala

Auditor Técnico-Informático en el Consejo de Cuentas de Castilla y León

Correo_e: Gonzalo.celis@consejodecuentas.es

Grupo de trabajo: Herramientas de Análisis de Datos.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

2

Tratamiento de las cuentas de las entidades locales en CYL

con una herramienta Big Data.

Introducción:

El objetivo de esta ponencia es contaros la experiencia del OCEX de CYL al utilizar esta

Aplicación informática, de tratamiento masivo de datos, aplicada en el sector público local en

el tratamiento de las cuentas rendidas.

Al implantarse la Plataforma de Rendición en el TCU y los OCEX comenzamos a recibir las

cuentas en soporte electrónico.

Ya desde el inicio se vislumbró la necesidad de almacenar dicha información en una Base de

datos para su análisis y explotación, dado que en CYL contamos con cerca de 5.000 EE.LL.

de censo.

Con el paso del tiempo nos enfrentamos a nuevos retos a los que intentamos dar respuesta:

Aumento del número de cuentas rendidas, que se van acumulando año tras año;

actualmente más de 23.000 cuentas, correspondientes a los años 2006 a 2013.

Manejo de tablas con millones de registros.

Necesidad de prestar mayor información detallada y comparada de ejercicios

anteriores a través de indicadores presupuestarios y financieros.

Poder realizar datos comparativos de los Indicadores por tramos de población

Necesidad de ser más transparentes dándonos a conocer en Internet. No sólo por

medio de los informes que se elaboran aquí, sino publicando también un extracto de

todas las cuentas rendidas, más accesible que el contenido completo de la cuenta,

facilitando su consulta.

(hasta ahora se publicaban en PDF en Anexos al Informe Anual una especie de ficha-

resumen con la información más significativa de cada entidad ocupando dos hojas

para las entidades con modelo normal y simplificado y una las del modelo básico; en

el ejercicio 2013 nos rindieron hasta el 31/03/2015 más de 4.390 cuentas, ellos

significaría unos anexos en pdf de más de 7.000 hojas)

Ante estas necesidades decidimos que había que publicar la información estadística del

informe anual en Internet y no en formato pdf, precisamente.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

3

Entendemos que el usuario debe poder utilizar la información a su gusto con consultas “Ad

Hoc”, interactivas, dinámicas y flexibles.

La solución comenzó a fraguarse en el Foro Tecnológico de Sevilla, donde la compañera del

Consello de Contas de Galicia, Mar Cibreiro, responsable de informática, nos presentó una

herramienta que encajaba con lo que estábamos buscando: una Aplicación BI (Business

Intelligence), de tratamiento masivo de datos, llamada Quiterian, luego Actuate(Birt) y

finalmente OpenText.

Nos desplazamos a Santiago de Compostela para conocer de primera mano su

funcionamiento. Aprovecho la ocasión para agradecer públicamente al Consello de Contas

por su apoyo y paciencia a la hora de enseñarnos su Aplicación.

Por motivos que no vienen ahora al caso, en el último momento no se concretó la compra de

la aplicación BIRT. Y surgió como alternativa TABLEAU, quizás una herramienta más

ligera, pero que incluía ya un módulo de publicación de datos en Internet. ¡Y mucho más

económica!

TRATAMIENTO DE LAS CUENTAS.

Todos los martes IECISA nos pone en un directorio de Internet los datos de todas las cuentas

a una fecha determinada. Se descargan los ficheros de la nube y mediante un ‘ejecutable’ se

vuelcan a una base de datos de ORACLE, en un modelo ‘matricial’, no entendible por los

auditores de cuentas.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

4

Tablas Contables del modelo matricial de IECISA (1)

Tabla ACC_Contablesx_08 (2)

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

5

¿Qué hacemos en el OCEX de CYL?

A partir de este momento, partiendo de la Base de datos de ORACLE, lo transformamos a una

Base de datos de SQL_SERVER, de la que tenemos licencia, mayores conocimientos; y

seguimos el ejemplo del Consello de Contas de Galicia.

Se elaboran una serie de Vistas o Consultas que ya son entendibles por los Auditores de la

toda la Cuenta General, teniendo en cuenta los 3 modelos: Básico, Normal y Simplificado.

Y a partir de ese momento ya podemos tratarlo con esta Herramienta, conectándonos a las

Vistas o Consultas.

Agradecer a Juanjo Villar, técnico de informática del Consello de Contas, que nos ha

facilitado gran parte del código para extraer la información y poder elaborar las Vistas o

Consultas en un modelo entendible.

Aprovecho la ocasión para reivindicar que este trabajo ímprobo de

rehacer la Cuenta para que la puedan descifrar los Auditores y trabajar con

sus datos, se facilite por parte de IECISA a todos los OCEX. ¡Ahorraríamos

tiempo y dinero!.

En definitiva, podemos definir tres grandes fases en el tratamiento de las Cuentas:

1. Descarga e importación de datos.

2. Análisis y explotación

3. Publicación en Internet.

Vamos a utilizar esta herramienta, Tableau, para las fases 2 y 3.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

6

¿Qué es TABLEAU?

Es una herramienta BI (Bussiness Intelligences), de tratamiento masivo de datos, (de BIG

DATA), orientada al sector de los negocios.

Las Aplicaciones de Big Data se caracterizan por lo que se llama las 3 Vs:

“Gestionar un volumen de datos descomunal a la mayor velocidad posible considerando su

extraordinaria variedad.”

Destacar el análisis visual de los datos, especialmente significativo en las páginas con datos

evolutivos de Indicadores Presupuestarios o Financieros. También las páginas web con

mapas. Con un simple ‘vistazo’ podemos detectar datos contables significativos o

sorprendentes, que nos estimulen a bucear en la Cuenta.

Destacar igualmente la facilidad en el diseño de las consultas así como la presentación de

formato tabla de los datos, a modo de plantilla.

Finalmente lo que nos terminó de convencer fue el módulo de publicación de datos en

Internet, con sus páginas interactivas gracias a los filtros, así como la posibilidad de exportar

los datos a formatos tratables. Igualmente la actualización periódica de dichas páginas sin

tocar el código Html.

No pretendo en esta exposición ‘vender’ el producto. No soy el Comercial ni cobro

comisiones. Y mis conocimientos del producto son limitados. Simplemente explicaros cómo

adaptamos o nos servimos de esta Herramienta para alcanzar unos fines u objetivos. Seguro

que existen otras alternativas.

De las distintas opciones (en Escritorio, en Red, en la Nube) para utilizar esta herramienta, se

eligió de inicio y por comodidad la versión más sencilla, TABLEAU DESKTOP, y se

compraron dos licencias y 40 horas de formación, mayormente on-line, contratadas con la

empresa Rubicon_BI, representante en España de TABLEAU, y con sede en Zaragoza y

Madrid.

Existe una versión Demo, gratuita, que te puedes descargar en:

www.tableau.es

http://rubiconbi.es/

Pasamos a abrir la Aplicación de Tableau, versión 9.0, instalada en este portátil, e intentamos

realizar una DEMO en vivo y en directo; pero antes una rápida vista de las páginas publicadas

en la Web del Consejo de Cuentas sobre el sector público local en Castilla y León para

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

7

hacernos una idea de la posible adaptación de esta Herramienta a las necesidades de los

OCEX en el sector público local:

1. Cinco (5) enlaces o vínculos: (Anexo I)

a. Estado de Rendición.

http://se.consejodecuentas.es:800/rendiciondecuentas.html

b. Base de datos contable. http://se.consejodecuentas.es:800/datoscontables.html

c. Base de datos contable (Agregados).

http://se.consejodecuentas.es:800/datoscontables_agregados.html

d. Principales magnitudes contables.

http://se.consejodecuentas.es:800/magnitudes.html

e. Consulta de Cuentas.

http://www.rendiciondecuentas.es/es/consultadeentidadesycuentas/buscarCuen

tas/

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

8

Pasos principales:

1) Conexión de datos.

Permite múltiples conexiones a diferentes bases de datos u hojas de cálculo. En mi

caso, Base de datos de Access.

Conexión (3)

Podemos abrir un fichero existente, un libro de Tableau, o crear uno nuevo.

En la conexión podemos ir a una consulta ya existente o definir la consulta con las

distintas tablas, Vistas, etc. Y los campos.

Vista (4)

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

9

2) Análisis y explotación.

Una vez determinado el origen de los datos, ya podemos comenzar a construir nuestra

página.

Panel Inicio Tableau (5)

En el panel de la izquierda nos muestra de arriba hacia abajo, tres dimensiones

fundamentales: Datos, Dimensiones y Medidas.

El panel de la derecha nos puede recordar una Tabla Dinámica de Excel, donde

arrastraremos los campos que nos importen, y donde se irá rehaciendo la página

progresivamente:

Las Medidas normalmente serán datos numéricos, mientras que las Dimensiones serán

los nombres de esas medidas. Lo interpreta automáticamente Tableau.

Una Dimensión se considera aquellos campos que contengan información categórica,

información por la cual las medidas se pueden agregar (Sumar, Contar…).

Dependiendo de la visualización que queremos crear, es posible que necesitemos que

las dimensiones se puedan utilizar como medidas y viceversa. Esto se hace arrastrando

una Dimensión a la zona de Medidas. Ejemplo típico es el campo “Ejercicio”.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

10

Diseño Página Tableau (6)

Resulta interesante la opción de extractos, por la cual Tableau hace una copia de los

datos de la consulta, y a partir de ese momento dejamos de estar en conexión con la

Base da datos de origen. Es imprescindible si queremos publicar en Internet. Y una

opción mucho más rápida para ver los resultados. Su rendimiento es mucho más

rápido porque pasa a utilizar el motor interno de procesamiento de datos (Tableau

Data Engine).

Si simplemente queremos actualizar porque las tablas de origen se han modificado y

poder ver las nuevas cuentas, tenemos una opción rápida de “Actualizar todos los

extractos”.

También en el margen izquierdo se pueden crear con el boton derecho del cursor,

Parámetros para filtrar la información o Campos Calculados.

Según vamos arrastrando campos a Filas o Columnas, se va actualizando la página.

Al final, estamos creando una especie de Plantilla, con una serie de Filtros a la

Derecha de la página, que nos permiten seleccionar la información buscada.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

11

Página Tableau (7)

Otra de las posibilidades que ofrece Tableau es incorporar mapas, a la hora de

presentar los datos, actualizándose de manera dinámica, según los filtros que

interesen. En nuestro caso, simplemente tenderemos que definir el campo ‘Provincia’

como mapa, generándose automáticamente el mapa por provincias de Castilla y León.

Mapas (8)

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

12

3) Cuadros de mando o Dashboard

Hoja Cuadro de mando Libro de Tableau

El libro de Tableau consta de Hojas, cada una con un origen de datos, igual o

diferente, parecido a un libro de Excel. Lo diferente son los Cuadros de Mando o

Dashboard, que permiten mostrar una o varias hojas a la vez, de manera dinámica.

Aparecen en la parte inferior izquierda de la Aplicación de Tableau.

Las Hojas se pueden ocultar, con lo que el cliente sólo ve los Cuadros de mando, si así

nos interesa.

Nueva…(9)

En el Dashboard vamos colocando las distintas hojas que nos interesen, determinando

el espacio y la posición que va a ocupar cada una.

Nuevo Dashboard (10)

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

13

4) Publicación web.

Una vez diseñado las Páginas o Cuadros de Mando debemos de guardar el libro y

subir la información al servidor de Tableau. Con la licencia incluye alojamiento en

servidor web.

Guardar y subir a Internet (11)

Una vez nos hemos dado de Alta como Administradores de Tableau Public, podemos

gestionar nuestras páginas en Internet.

La primera vez que subimos una página, genera un código HTML que tenemos que

insertar en nuestra página web, para que apunte a dicha página de internet. (Estilo

código que ofrece la AEMET para el tiempo de nuestro municipio).

Y no volveremos a tocar el código de la página web. Simplemente actualizaremos los

Extractos con nueva información.

Toda la información que se publica en Tableau Public es visible de manera pública a

nivel global.

CONSEJO DE CUENTAS DE CASTILLA Y LEÓN

14

Página web (12)

Justo en la parte inferior, a la derecha, aparece un icono y el texto “Descargar”.

Ofrece un módulo de exportación de datos a formato Imagen, Pdf, Excel o

Libro de Trabajo de Tableau.

5) Uso externo de la información publicada:

a. Cualquier ciudadano pueda consultar en Internet. Ley de transparencia.

b. Prensa autonómica. Útil para los periodistas.

c. Estudios académico-universitarios.

d. Corporaciones locales. Responsables políticos.

FIN

Anexo I: Ejemplos de páginas web publicadas por el OCEX de Castilla y León.

Anexo I

1. Rendición de cuentas.

1. Rendidas por provincia, tipo de entidad y fecha.

2. Datos Contables: Indicadores presupuestarios.

3. Datos Contables: .Liquidación del Presupuesto de Gastos

4. Datos Contables: Balance.

5. Datos Contables Agregados: Resultado presupuestario.

6. Datos Contables Agregados: Tesorería.

7. Principales magnitudes: Contables.

8. Principales magnitudes: Ficha Resumen-Indicadores