Embed Size (px)
Citation preview
CONSELLO DE CONTAS DE GALICIA
Uso de herramientas para el análisis Del papel al reporting web
Juan José García Martínez.
María del Mar Cibreiro Cordero
15/05/2015
1
CONTENIDO
Titulo: Uso de herramientas para el análisis: del papel al reporting web.
Autores: María de Mar Cibreiro Cordero. Departamento de Informática
Juan José García Martínez. Departamento de Corporaciones Locales.
Institución: Consello de Contas de Galicia.
Correos electrónicos:
Autoasignación: Herramientas de análisis de datos
2
0. INTRODUCCIÓN
El objeto de la comunicación es explicar de forma somera el proceso llevado a cabo en
el Consello de Contas de Galicia, para pasar de una situación en la que elaboraba la
cuenta general de las entidades locales en formato papel, al proceso actual de consulta
en la web del extracto de la cuenta general rendida, y publicación de informes gráficos
navegables de diferentes indicadores o de comparativas entre entidades, que se ofrecen
para análisis.
1. SITUACIÓN DE PARTIDA
Hasta el ejercicio 2007 se recibía la información contable de las entidades en formato
papel (y posteriormente desde 2007) en formato electrónico (ficheros xml).
La información recibida se carga en una aplicación elaborada a medida para este fin,
generándose, tras un tediosos proceso de carga y comprobación, los estados contables
Balance de Situación, Resultado Económico Patrimonial, Estado de Liquidación del
presupuesto agregado a nivel de capítulo, Resultado Presupuestario y Remanente de
Tesorería.
Con esa información se genera un libro “Informe general de las Entidades Locales de
Galicia”, maquetado en formato pdf, que es el que se aprueba y publica en la página
web del Consello de Contas.
Esta situación presentaba fundamentalmente las siguientes insuficiencias:
- Control de Rendición: no se hace un control y catalogación de la rendición de
cuentas. Además, una vez generada la Cuenta General de las entidades locales
(informe que se genera en una fecha de corte), las cuentas rendidas con
posterioridad no son objeto de ningún tratamiento.
- Análisis económico: Sólo se tiene acceso a los estados mencionados
anteriormente, y a la liquidación presupuestaria nivel de capítulo. No permite el
análisis de los datos.
- Informe: El único producto que se obtiene de todo el proceso anterior es una
ficha resumen de la cuenta general rendida, que por agregación permite elaborar
la Cuenta general de las entidades locales. Parta el usuario final, la información
que se ofrece es escasa, y difícil de localizar en un tomo de más de 500 páginas.
2. GESTIÓN DEL CAMBIO
Dada la situación descrita, el Consello de Contas aborda un proceso estratégico de
cambio, que permita superar las deficiencias anteriores, proceso que consta de tres
grandes líneas de actuación:
3
1. Gestión de la rendición de cuentas.
2. Análisis de la información económico financiera.
3. Reporting web.
2.1 GESTIÓN DE LA RENDICIÓN DE CUENTAS.
La primera decisión que se adopta es la firma del convenio de colaboración con el
Tribunal de Cuentas y la adhesión a la plataforma de rendición telemática de cuentas.
Al disponer de la cuenta general mediante un procedimiento telemático que garantiza la
integridad de los ficheros se introducen los siguientes cambios:
- Tras el correspondiente proceso de conciliación/depuración, se abandona el
censo de entidades propio, para adoptar como censo único del Consello de
Contas el censo de entidades de la plataforma. Toda la gestión censal se realiza
en la plataforma.
- Se implementa la funcionalidad de que la rendición a través de la plataforma
genere una llamada a nuestro registro telemático, con lo cual se está en
disposición de hacer un seguimiento de la rendición de cuentas a nivel entidad.
Para ello se desarrolla una aplicación de diseño propio en la que se va anotando
los hitos fundamentales del resultado de la revisión de la cuenta general en la
plataforma, y que nos permiten obtener una estadística de la rendición, con la
catalogación de la cuenta rendida en alguno de los estados siguientes en función
de los defectos observados:
o Cuenta General rendida, si no presenta defectos o sólo defectos de
tramitación de la cuenta general.
o Cuenta General rendida con defectos leves, si presenta defectos de
coherencia en los estados contables.
o Documentación recibida con defectos de tal naturaleza que impiden su
consideración como cuenta rendida, estado que se reserva a aquellas
cuentas que no vienen completas, que no has sido aprobadas o que no
aportan la totalidad de las certificaciones bancarias.
Esta estadística, mediante consultas SQL se ofrece con la periodicidad que se
quiera (que varía entre periodicidad diaria en pleno proceso de rendición hasta
periodicidad semanal o mensual cuando dicha actividad decae) en la página web
del Consello de Contas.
4
2.2 ANÁLISIS DE LA INFORMACIÓN ECONÓMICO FINANCIERA.
Se plantea la necesidad de avanzar en el ámbito del análisis de los datos contables
recibidos, de una forma ágil y asequible, sin que el personal de auditoría deba poseer
especiales conocimientos en el ámbito informático.
Se adopta como solución una herramienta de “business intelligence” que permita
importar la información de la cuenta general, consolidarla con otros datos y presentarla
al auditor como estados contables por él conocidos, con los que pueda realizar los
análisis que quiera.
La herramienta elegida se caracteriza por:
5
- Herramienta de propósito general, es decir, no es un software a medida del área
de Corporaciones locales. Se puede cargar cualquier tipo de información, una
vez consolidado el origen de los datos. Funciona en entorno web.
- Modelo de datos analítico, es una herramienta que permite no solo importar los
datos de la base de datos de la plataforma, sino transformar ese modelo de datos
de explotación (la estructura de tablas en las que se almacena la información
rendida a través de la plataforma), en un modelo de datos analítico, basado en la
estructura de la Cuenta General, legibles para el usuario sin necesidad de
conocimiento informático.
- Flexibilidad, pues permite realizar análisis a medida, sin necesidad de que estén
previamente definidos. El usuario define en tiempo real cual es el análisis que
quiere realizar.
Los pasos dados por el Consello de Contas para implementar este proyecto fueron los
siguientes:
a) Consolidación de los orígenes de datos: Es necesario consolidar los datos de la
plataforma de rendición desde la exportación que el tribunal de Cuentas ofrece
en formato Oracle DB al formato SQL Server utilizado en el Consello de
Contas.
b) Creación de un modelo analítico. Con apoyo de la empresa proveedora de la
herramienta, partiendo del modelo de datos de la plataforma de rendición de
cuentas, se elabora un modelo de datos analítico basado en la estructura de la
cuenta general de las entidades locales.
c) Programación de carga y transformación. Una vez fijado el modelo,
semanalmete se programan la carga y transformaciones necesarias en la
herramienta de ETL.
De esta manera se obtienen una tablas relacionales de entidades y de los distintos
estados contables de la Cuenta General y de la Memoria, que permiten mediante la
aplicación de filtros en las entidades y/o en los estados contable realizar el análisis que
se quiera, con el máximo nivel de detalle.
Por ejemplo: Para la creación de un análisis de la deuda viva por habitante, los pasos a
dar son los siguientes:
Se crea un nuevo crosstab. De la tabla de entidades, se arrastra el campo
“Comunidad Autónoma a la secciones de dimensiones del crosstab.
6
De la tabla Balance de Comprobación, se arrastra al crosstab el campo “Saldo
acreedor”, filtrado por los números de cuenta representativos de los pasivos
financieros a corto y largo plazo, y pivotando el crosstab por el campo ejercicio,
para que muestre los valores separados por ejercicio económico.
Se añade al crosstab el campo Habitantes de la tabla de entidades. Se crea una
nueva medida resultado de dividir los pasivos financieros entre los habitantes y
se editan los campos anteriores para que solo sea visible el de deuda viva por
habitante
7
Finalmente, añadiendo como dimensiones los campos provincia y nombre, se
obtiene el detalle a nivel provincial y a nivel de cada ayuntamiento en concreto.
8
Esta herramienta ya está totalmente consolidada y se utiliza para la gestión de censo y
para la elaboración del informe de Cuenta General de las Entidades Locales de Galicia,
así como para las tareas de fiscalización.
9
2.3 REPORTING WEB.
Finalmente, dada la posibilidad de integración del BI con herramientas de reporting, se
plantea el objetivo de publicar de informes web, que permitan al público en general de
forma dinámica y mediante una navegación a través de selectores y filtros, la siguiente
información:
- Resumen de la situación económico financiera de cada entidad, a nivel
individual o agregado con sus entes dependientes.
- Resumen de las cuentas generales rendidas por la entidad, con una selección de
los principales estados contables.
- Informes navegables de indicadores y magnitudes, que permiten a nivel gráfico
una comparativa a nivel nacional, autonómico y local.
- Informes evolutivos de medidas, que permiten a nivel gráfico el análisis
comparativo de entidades/medidas.
Este es la parte del proyecto en la que estamos trabajando actualmente y que aún no está
completa al 100%, por lo que os mostraremos la estructura general que tenemos
pensada, así como alguno de los informes que ya están disponibles.
Todos los datos a utilizar en estos informes son elaborados como crosstab en la
herramienta BIRT Analytics
2.3.1 INFORMES QUE SE OFRECEN.
En todos los informes figura una sección de filtros, mediante los cuales se puede
seleccionar la entidad (o conjunto de entidades) de las que se quiere mostrar el informe.
Los filtros disponibles son:
- Ejercicio
- Tipo de entidad
- Comunidad Autónoma
- Provincia
- Entidad principal
- Entidades dependientes
La no selección de alguno de los filtros implicará que se mostraran agregados todos los
valores afectados.
Se ofrece un conjunto de informes clasificados en tres grandes categorías:
1. Informes Generales
2. Informes de indicadores
10
3. Informes comparativos/evolutivos
1. Informes generales
1.1. Resumen de las cuentas anuales.
Informe en el que se muestra un resumen de los principales estados contenidos en la
Cuenta General rendida por la entidad seleccionada: Liquidación de Ingresos a nivel
capítulo, Liquidación de Gastos a nivel capítulo, Resultado Presupuestario, Remanente
de Tesorería, Tesorería, Estado de Endeudamiento, Balance de Situación, Resultado
Económico Patrimonial.
Se presenta en una ventana independiente cada uno de los estados citados, siendo el
filtro común para todos ellos. Es decir, una vez seleccionada la entidad, se mantiene el
foco cuando se navegan los diferentes estados disponibles. Además, los capítulos se
pueden desplegar a nivel artículo.
1.2 Resumen económico financiero.
Consiste en el análisis de los principales indicadores económicos de la entidad local
seleccionada e su comparativa con la media provincial, autonómica e nacional, así como
su evolución en los últimos 4 ejercicios. Es un informe para descargar, no para
visualizar en pantalla, y sustituye a la anterior Cuenta general de las Entidades Locales.
2. Informes de indicadores
Son informes gráficos que permiten la comparación de una magnitud concreta en un
conjunto de entidades locales, para un ejercicio dado.
Se utilizan los selectores de entidad anteriores (salvo en el caso de los indicadores que
se refieran a cuantías por habitante, en cuyo caso el tipo de entidad necesariamente tiene
que ser un ayuntamiento o una Diputación), así como otro selector de las diferentes
medidas afectadas en el informe de la magnitud seleccionada (por ejemplo, si se elige el
informe de la deuda por habitante, este selector permite elegir la visualización del
importe dela deuda, del número de habitantes, o del ratio deuda por habitante.)
Se ofrecen los siguientes informes:
a) INDICADORES DE MAGNITUDES PRESUPUESTARIAS: Deuda por
habitante, carga financiera por habitante, Índice de ahorro bruto, Período
mínimo de amortización de la deuda, Superávit/Déficit por habitante,
b) INDICADORES DEL PRESUPUESTO DE GASTOS: Gasto público por
habitante, Gasto de personal por habitante (con posibilidad de seleccionar la
visualización de un artículo concreto dentro del capítulo I), Gasto de los
servicios públicos por habitante (con posibilidad de seleccionar un programa de
gasto concreto dentro de los 13 que se ofrecen: Urbanismo, Saneamiento,
recogida residuos, ….) y Gastos en inversiones por habitante
11
c) INDICADORES DEL PRESUPUESTO DE INGRESOS: Ingresos públicos por
habitante, Ingresos fiscales por habitante, Principales ingresos corrientes (con
posibilidad de seleccionar una figura de ingresos en concreto: IBI, IAE, IVTM,
….) e Índice de dependencia de las subvenciones recibidas.
Para este tipo de informes se opta por el siguiente diseño:
- En su primer nivel, se muestra en un gráfico de barras horizontal a nivel
agregado de Comunidad Autónoma el valor de la medida seleccionada,
ordenadas de mayor a menor.
- Haciendo clic en la barra de la Comunidad Autónoma, se despliegan un conjunto
de barras representativas del valor de la medida a nivel de provincia, ordenadas
de forma descendente.
- Haciendo clic en la barra de la provincia, se despliega un conjunto de barras
representativas del valor da medida a nivel de las entidades de la provincia,
ordenadas de mayor a menor.
12
3 Informes evolutivos/comparativos
Se trata de informes que permitan, bien el seguimiento por entidad/ejercicio de una
medida concreta, bien la comparación de una medida concreta entre un conjunto de
entidades, para un ejercicio dado.
Se utilizan los ismos selectores que en los informes anteriores.
Los informes que se ofrecen son los siguientes:
3.1 Informes evolutivos de medidas
Son exactamente el mismo conjunto de informes de magnitudes anterior, si bien
confeccionado de forma que permita la visualización de las medidas concretas a lo largo
de los ejercicios.
Los clasificamos en tres grandes categorías: evolutivos de magnitudes presupuestarias,
del presupuesto de gastos y evolutivos del presupuesto de ingresos. Por ejemplo:
13
3.2 Informes comparativos de medidas
Son exactamente el mismo conjunto de informes de magnitudes anterior,
confeccionados de forma que se permita la visualización a lo largo del tiempo, de los
valores de la medida seleccionada para el conjunto de entidades locales a comparar.
2.3.2 INTEGRACIÓN EN LA PÁGINA WEB DEL CONSELLO DE
CONTAS.
La integración de todos estos informes en la página web del Consello de Contas se
realiza dividiendo los mismos en tres niveles: datos de rendición de Galicia, informes
agregados (nacional) e informes a nivel de entidad.
Datos de rendición de Galicia.
Desde esta página se pueden consultar los datos de rendición de las entidades locales
gallegas.
14
Inicialmente aparecen disponibles los links para consultar el histórico de rendición por
ejercicio, así como el histórico por entidades. Seleccionado ejercicio y/o tipo de entidad,
van apareciendo links que llevan a esa estadística filtrada
Informes agregados
Desde esta página, una vez que se selecciona como mínimo el ejercicio que se quiere
visualizar, se despliegan los links de los indicadores e informes disponibles para la
selección. Siguiendo el link, se ejecuta el indicador/informe
15
16
Informes a nivel entidad
Finalmente, desde esta página se puede seleccionar la entidad concreta (ya sea a nivel
agregado o a nivel individual) que se desee consultar. A medida que se avanza en la
selección, se van desplegando los links correspondientes a los indicadores, informes y
fichas resumen que están disponibles para esa selección.
4. DESCRIPCIÓN TÉCNICA DE LA SOLUCIÓN
A continuación se describen los elementos técnicos que intervienen para dar cobertura
al proceso comentado anteriormente.
17
4.1 ARQUITECTURA
Base de datos de la Plataforma de Rendición (réplica local en SQL Server):
almacena toda la información de la Plataforma de Rendición de Cuentas tal cual
se vuelca semanalmente al FTP
Base de datos del Consello de Contas para control de la Rendición (SQL
Server): base de datos propia donde se almacenan los datos y estados de
rendición de las entidades locales de Galicia
Aplicación web de control de la rendición (IIS, .Net): operativo utilizado por el
área de locales para el control de la rendición (estados, fechas, observaciones,
etc.)
Aplicación de BI de Actuate: consolida todas las fuentes de datos anteriores para
permitir análisis complejos de los datos utilizando herramientas analíticas
Página web del Consello y servidor de aplicaciones de la DMZ: permite la
publicación de los datos hacia el ciudadano. El ciudadano accede siempre a la
información estática volcada en la página web, así como a los informes
dinámicos ubicados en el servidor de aplicaciones, nunca accede directamente a
información de los sistemas internos del Consello de Contas.
4.2 PROCESOS AUTOMÁTICOS
A continuación se describen los procesos que intervienen en la consolidación de la
información. Todos los procesos son automáticos.
Los martes por la noche se realiza una réplica de la base de datos de la
Plataforma del Tribunal desde las exportaciones de Oracle colgadas en un
servidor FTP hacia un servidor SQL Server en el Consello de Contas, en 4
pasos:
Descarga del FTP
Importación de las bases de datos en un servidor BBDD Oracle gratuito
Migración de las BBDD desde Oracle a SQL Server utilizando la
herramienta Microsoft SQL Server Migration Assistant for Oracle
Limpieza, ajustes y optimizaciones varias (se optimizan vistas costosas
transformándolas en tablas, se generan algunas tablas de apoyo
adicionales y se corrige la población de las diputaciones como la suma
de todos los ayuntamientos que la componen)
Los miércoles por la noche se ejecuta un proceso ETL de carga de las bases de
datos anteriores en el producto de BI. Este proceso consolida la información de
la plataforma en estados contables que facilitan su análisis y la unifican con el
control de rendición.
Los jueves se generan “fotografías” de contenidos pesados y se publican en la
web del Consello de Contas, que estarán disponibles en ella de forma estática
hasta la siguiente semana, que se repite el proceso. Estos contenidos publicados
incluyen:
Fichas económica-financieras en formato PDF de las entidades de
Galicia
Estadísticas y análisis agregados del estado de la rendición de las
entidades de Galicia
