Improved Business Intelligence Solution with Reimbursement

Tracking System for the Brazilian Ministry of Planning, Budget and

Management

Antonio M. Rubio Serrano

, Paulo H. B. Rodrigues

, Ruben C. Huacarpuma

João Paulo C. L. da Costa

, Edison Pignaton de Freitas

, Vera Lopes de Assis

Ararigleno A. Fernandes

, Rafael T. de Sousa Jr.

, Marco A. M. Marinho

and Bruno H. A. Pilon

Dept. of Electrical Engineering, University of Brasilia (UnB), Brasilia, DF, Brazil

Electrical Engineering Department, Federal University of Rio Grande do Sul, Brasília, RS, Brazil

Keywords: e-Government, Business Intelligence, Auditing, Open Source.

Abstract: Business Intelligence (BI) systems are crucial for assisting the decision making processes of private and

governmental institutions. The Human Resources Auditing Department (CGAUD) of the Brazilian Ministry

of Planning, Budget and Management (MP) has been developing its own BI for auditing the payroll of all

federal employees since 2010. Given that the monthly payroll is approximately 12.5 billion reais, the initial

version of the proposed BI system in 2012 was able to audit approximately 1.5 billion reais. In this paper,

we propose an improved BI system, which can deal with an increased volume of data, a greater amount of

monitoring trails and a higher granularity of the final reports. As consequence, the total audit value has

increased to approximately 5 billion reais. In addition, our new BI system has incorporated a

Reimbursement Tracking System for monitoring the payroll of federal employees who have to reimburse

the Brazilian government. Around 4.5 million reais are automatically monthly tracked by our new BI

system. Our proposed BI system has been validated using the real environment of the MP and the results are

compared to the previous BI system.

1 INTRODUCTION

According to (Brown & Brudney, 2011), electronic

government (e-gov) is the use of technology,

especially Web-based applications to enhance access

to and efficiently deliver government information and

services.

In this context, the Human Resources Auditing

Department (CGAUD) previously known as AUDIR,

which belongs to the Brazilian Ministry of Planning,

Budget and Management, in Portuguese Ministério do

Planejamento, Orçamento e Gestão (MP), has created

its own BI system in order to support and to

automatize the auditing process of the payroll of the

Brazilian federal employees. Such BI system is

aligned with a holistic governance approach for

sustainable development taking into account aspects

such as efficacy, transparency, responsiveness,

participation and social inclusion in the delivery of

public services (United Nations, 2012).

In (Campos, et al., 2012), an initial BI solution

was presented where the authors proposed to use an

ontology indexation process via concept maps in

order to detect irregularities on the payrolls of the

Brazilian federal staff. The BI system in (Campos, et

al., 2012) had some limitations, for instance, in terms

of velocity, i.e. system responsiveness. Therefore, in

(Huacarpuma, Rodrigues, Serrano, da Costa, de Sousa

Junior and Holanda, 2013), we show that the

incorporation of Big Data schemes in our BI system

can significantly increase its velocity.

In this paper, we propose an improved BI system,

which can deal with an increased volume of data, a

greater amount of monitoring trails and a higher

granularity of the final reports. In addition, our new

BI system has incorporated a Reimbursement

Tracking System known as SIGA for monitoring the

payroll of federal employees who have to reimburse

the Brazilian government.

The remainder of this paper is divided into five

sections. Section 2 presents the state of the art, while

Section 3 presents the original audit process without

BI and our previous BI system solution. The proposed

434

M. Rubio Serrano A., H. B. Rodrigues P., C. Huacarpuma R., Paulo C. L. da Costa J., Pignaton de Freitas E., Lopes de Assis V., A. Fernandes A., T. de

Sousa Jr. R., A. M. Marinho M. and H. A. Pilon B..

Improved Business Intelligence Solution with Reimbursement Tracking System for the Brazilian Ministry of Planning, Budget and Management.

DOI: 10.5220/0005169104340440

In Proceedings of the International Conference on Knowledge Management and Information Sharing (KMIS-2014), pages 434-440

ISBN: 978-989-758-050-5

 2014 SCITEPRESS (Science and Technology Publications, Lda.)

improved BI system solution with reimbursement

tracking system is described in Section 4, and Section

5 shows the achieved results. Finally, in Section 6 the

conclusions are drawn.

2 STATE OF THE ART

Among different types of information and

communication technologies (ICT), business

intelligence based solutions are being widely

employed in e-government systems for different

purposes (Gil-Garcia & Martinez-Moyano, 2007).

The study presented in (Cordella & Iannacci, 2010)

reveals how this technology is important in the

development of information systems used in e-

government initiatives, while (Hamner, Negrón, Taha

& Brahimi, 2012) discusses cases of success and fail

in implementing e-government systems and the role

of BI and other technologies in this process.

In the work presented in (Bukhsh & Weigand,

2012), the authors describe an approach called

Service-Oriented Auditing, which is an innovative

auditing service based on the Service-Oriented

Architecture. This approach aims to tackle

coordination problems among different governmental

entities while performing activities chains that depend

on each other. At certain degree this work is related to

our in the sense that the audit process carried on by

CGAUD can be seen as a process chain as the one

handled in the referred work. However, the BI-based

solution proposed in our work provide additional

features, compared to the Service-Oriented one

proposed by these authors, as a comprehensive tool to

scrutinize data, while their approach just provide the

process automation.

3 CGAUD AUDIT PROCESS

SCENARIO

One of the main responsibilities of the CGAUD is to

audit the payroll of the federal staff. The payroll is

monthly elaborated, and includes the information

about the active, retired and pensioners of the Federal

Public Administration. 16 Gb of information are

generated every month, containing the data about 6,2

million federal employees and summing a total value

around R$ 12.5 billion. The basic element of the

payroll is called rubric. Each rubric stands for a

positive or negative value that is included on the

payroll according to the characteristics of the position

of each federal employee. There are 2,200 different

rubrics. The data of the payroll is stored on the

database called Integrated System for the

Administration of Human Resources, in Portuguese

Sistema Integrado de Administração de Recursos

Humanos (SIAPE).

The legal base that regulates the payroll is the

Federal Constitution of Brazil and there are also other

specific laws and decrees that complement it.

According to this legislation, the responsibility of the

CGAUD department is to analyse the rubrics of every

payroll in order to detect incompatibility of benefits,

inconsistencies and irregularities.

The audit process is based on audit trails, which

consist of a set of rules based on the legislation that

are used to verify the irregularities on the payrolls.

Once the inconsistency on the payment is detected,

the auditors are informed in order to take the required

actions to fix the inconsistency or the irregularity.

In Subsection 3.1, we overview the original audit

process without any automatized system, while in

Subsection 3.2, we review our previous BI system.

3.1 Original Audit Process

Before the deployment of the initial BI solution

proposed in (Campos, et al., 2012), the audit process

was manually performed. The payrolls were

generated and the salary was transferred to the

employees, the CGAUD personnel ran manually a

query on a small part of the payroll database with a

set of filters that maps the current legislation. Such

filters are known as trails as shown in Figure 1. The

result set containing the possible irregular cases was

exported to an Excel document where each register

was analysed in detail by the auditors.

Figure 1: Original audit process.

Such method had several limitations that implied a

low efficiency on the audit process. The first and

more evident is the fact that it was executed after the

payment was done. In case an irregularity was

detected, another bureaucratic process was required to

recover the money paid due to this irregularity. Also,

this manual process required an intensive human

intervention, which implies the expenses of human

resources and possible errors on data manipulation.

ImprovedBusinessIntelligenceSolutionwithReimbursementTrackingSystemfortheBrazilianMinistryofPlanning,

BudgetandManagement

435

Finally, the presentation of the data and the possibility

of elaborating statistics and historic views were very

limited.

Considering this scenario, a BI system with

preventive control, automatic process and access to

the statistics has been proposed in (Campos, et al.,

2012). The proposed BI system should be able to

detect irregularities before the payment was done. In

addition, the audit process in the BI system should

have almost no human intervention in order to reduce

the expenses in human resources and to increase its

reliability. Finally, a different and clearer presentation

of the data, including different types of graphics, lists,

reports and dashboards should be added.

3.2 The Previous BI Solution

In a first approach for the solution, we proposed in

(Campos, et al., 2012) a BI system considering the

federal payroll information and some important audit

trails in order to automatically monitor the audit

process.

The first step of the conception of a BI system is

the identification and definition of the indicators

required by the decision-making managers. In a

previous work described in (Fernandes, Amaro, da

Costa, Martins, Serrano and de Sousa Jr., 2012), it is

shown how this process can be improved by the use

of concept maps. This approach was applied to the

concept of Audit Trails. Figure 2 shows the BI system

considered in (Campos, et al., 2012).

Once the indicators have been defined, a BI

solution for providing that information to the

decision-making responsible manager is built.

However, from the sources of information to the final

reports, the data have to be processed in several steps.

As shown in Figure 2, the payroll data is

generated and stored on the SIAPE database. Each

row of the database is identified by the field called

matricula_servidor, a unique id for each federal

employee. Moreover, the information contained in the

SIAPE database is divided into two groups: personal

information and financial information. This

information is sent to the CGAUD department

through a mirror file.

The 16 Gb data is received every month, and it is

directly loaded onto a relational database. This

relational database contains also the result of the

execution of the audit trails.

This data is extracted from the data sources

through an Extract-Transform-Load (ETL) process,

where the data is transformed and prepared to be

loaded onto the DW.

Figure 2: Previous BI solution proposed in (Campos, et al.,

2012).

A general problem of ETL tools is their limited

interoperability due to proprietary application

programming interfaces (API) and proprietary

metadata formats making it difﬁcult to combine the

functionality of several tools (Oracle, 2005). In order

to avoid this drawback, the Pentaho Data Integration

(PDI) tool was adopted. Its connection is based on

Java Database Connectivity (JDBC) technology that

establishes a set of classes and interfaces (API) in

Java that allows sending and receiving SQL

instructions from any relational database.

The BI step is composed by several Data Marts

(DM) that concentrate all the information in a Data

Warehouse (DW). A DW is a database used for

reporting and data analysis and it is a central

repository of data which is created by integrating data

from different sources. Data warehouses store current

as well as historical data and are used for creating

trending reports for senior management reporting

such as annual and quarterly comparisons. On the

other hand, DM are smaller slices of the DW

containing the data of a specific area of the system,

according to the semantics of the application under

concern.

Although the previous solution solved several

problems of the original system, new requirements

were identified and solved by our new BI system as

described in Section 4.

4 THE PROPOSED IMPROVED BI

SOLUTION FOR THE CGAUD

SCENARIO

Based on the new requirements, a new BI solution

was needed. In subsection 4.1, the functionalities of

the proposed reimbursement tracking system known

as SIGA application are explained, while subsection

4.2 contains the details of the new BI proposed

solution.

KMIS2014-InternationalConferenceonKnowledgeManagementandInformationSharing

436

4.1 The Proposed SIGA

Reimbursement Tracking System

One of the limitations of previous BI solution was the

low efficiency on the tracking of the detected

irregularities or inconsistencies. Therefore, we

propose here the SIGA application, which is a

reimbursement tracking system that supports the

auditor along the tracking process of an irregularity or

an inconsistency detected on the Audit Trails.

According to Figure 3, the implemented SIGA

functionalities are (1) Audit Trails, (2) Document

Management, (3) Process Registration, (4) Process

Monitoring, (5) Exception Register, (6)

Reimbursement Tracking and (7) Reporting.

Figure 3: The proposed SIGA reimbursement tracking

system composed of seven steps.

As shown in Figure 3, the SIGA reimbursement

tracking process starts with the execution of the Audit

Trails (1). Once an irregularity is detected, several

documents have to be created in order to register it

and notify the department charged of the payment of

the federal employee. These documents can be

automatically created and easily monitored through

the Documents module (2) of the SIGA. With the

detected irregularity and the correspondent

documents, a new process is created into the

application using the Process registration module (3).

Also, for each irregularity an internal process on

SIGA is created. This process can be linked to one or

several documents and is related to the starting user.

As the audit process requires several analyses by

different auditors, there is a Process Monitoring

module (4) that allows changing the responsible user,

the status of the process and tracks the historical

evolution of the process.

Continuing with the flow in Figure 3, the

Exception Register module (5) permits to register

specific cases and exceptions that may occur again in

the next month. Therefore, an exceptional case which

can be for instance protected by some judicial

authorization will not be detected again.

If the irregularity is not an exception, then the

process is confirmed as irregular, the public employee

has to refund the received value to the public treasury.

In this case, the Reimbursement Tracking module (6)

registers the process and monitors it until the

reimbursement is finished. This module is of special

interest as it allows monitoring the complete

reimbursement process, something that was

previously very difficult to be done. Moreover, this

module also provides an estimate of the total amount

of month to complete the reimbursement process.

Therefore, the monthly portion refunded by the

federal employee can be adjusted according to his or

her age.

Finally, in (7) Reporting, different types of reports

can be generated by selecting specific cases, for

instance, by the total amount of the reimbursement ro

by the final age of the federal employee at the end of

the process.

4.2 The Improved BI Solution

Due to the useful amount of the data generated by the

SIGA, the BI solution included integration with the

SIGA allowing evaluating the efficiency of

monitoring of the processes carried out into the MP.

Therefore, our proposed BI solution has two data

sources: the SIAPE database and the SIGA database

as shown in Figure 4.

In our improved solution shown in Figure 4, the

data received from the SIAPE is actually loaded onto

an Operational Data Store (ODS). The ODS contains

all the registers of the mirror file and becomes the

internal data source. The inclusion of the ODS

facilitates the operational access to the data coming

from external entities and provides more reliability to

the loading process of the data.

From the ODS, the personal and financial

information about the federal staff is loaded onto a

relational database called internal SIAPE where the

information is consolidated and more reliability and

consistency are guaranteed. As implemented in the

previous solution, the Trail database contains the

result of the execution of the audit trails. Also two

databases SIGA and Trail are also used in this

relational step. The SIGA database contains the

information generated by the SIGA software.

DM and DW relations can be configured into two

manners: top-down and bottom-up. In the first case,

exposed on Figure 2, several DM are created with the

information coming from different sources and then

joined in order to build the DW. Top-down

environments are architected to deliver precise

answers to predefined questions. On the other hand,

ImprovedBusinessIntelligenceSolutionwithReimbursementTrackingSystemfortheBrazilianMinistryofPlanning,

BudgetandManagement

437

Figure 4: Proposed BI solution with the SIGA reimbursement tracking system.

in bottom-up environments like the one used in

Figure 4, all the data is first integrated into the DW

and then subdivided in several DM.

In this new BI system, a top-down structure has

been adopted. First, a DW is built with the data

obtained on the ETL process, and from this, four DM

are created: Audit trails, Restitution to the treasury,

Payroll and SIGA.

In the previous BI solution, a bottom-up structure

was used. However, it showed some limitations

mainly due the increasing volume of the data, the

addition of new modules of the system with specific

indicators and the necessity of increasing the

granularity on the final reports. In this new scenario,

where precise and detailed reports in four different

knowledge areas are required, the top-down structure

has been observed as the most efficient.

Finally, the information loaded on the four DM

can be easily accessed from the Pentaho Report

Designer and used to create different types of outputs

such as Static Reports, Dashboards, Maps or Web

Services.

5 BI REPORTS. PRESENTATION

OF THE RESULTS

In this section, some results obtained with our

improved BI system are presented.

Figures 5 and Figure 6 show the evolution of the

volume of the auditing process per month, in terms of

number of cases and value in BRL, respectively. In

both cases we observe a quite stable tendency on

time, with two evident exceptions on June and

November. This is due to the 13

payment, an extra

payment that all the public sector staff receives in

Brazil, which is divided into two parts. In comparison

with the results presented in (Campos, et al., 2012), in

which the value of the audited rubric is 1,5 billion,

there is a clear increase in the audited value due to the

new trails that were created and due to the capability

to manage bigger volumes of data.

Figure 7 and Figure 8 show the evolution of the

detected irregular cases per month. When an irregular

case is detected, the responsible or the audition

analyses the case and notifies the correspondent

department in order to avoid the irregular payment on

the next month. In this case it is possible to observe a

clear decreasing tendency, which demonstrates the

efficiency of the proposed system in identifying

irregularities, which can be fixed and do not appear in

the following months.

Figure 9 and Figure 10 show the evolution in time

of the number of cases and value of the restitution to

the treasury. In this case note that there is a very

irregular evolution, since the restitution to the treasury

is a complex juridical process. Therefore, although

the system correctly detects the irregularity, the

restitution is not done automatically, since it depends

on the analysis of a juridical process.

Figure 5: Evolution of the audited quantity per month

based on the incompatibility of rubrics.

6 CONCLUSIONS

In the present work, we proposed an improved BI

solution for the Department for the Auditing of

KMIS2014-InternationalConferenceonKnowledgeManagementandInformationSharing

438

Figure 6: Evolution of the audited value per month based

on the incompatibility of rubrics,

Figure 7: Evolution of the amount of irregular cases per

month based on the incompatibility of rubrics.

Figure 8: Evolution of the amount of irregular cases per

month taking into account only the irregular extra salary

for alimentation.

Figure 9: Restitution to the public treasury.

Human Resources (CGAUD) of the Brazilian

Ministry of Planning, Budget and Management. A

new software module including the complete

Figure 10: Restitution to the public treasury.

Reimbursement Tracking System has been

implemented.

Moreover, the improved BI solution based on a

top-down structure fulfils the new requirements of the

project, which are to manage a bigger volume of data,

to include the restitution to the treasury module on the

BI and to provide more customization on the

granularity of the final reports.

The results were validated using the real data of

the federal staff payroll providing evidence of the

technical and social efficiency of the new BI system.

As a future work, the inclusion of predictive

analytics module to support the evaluation of the

resulting indicators in order to detect statistically

irregularity and future incidents is planned.

ACKNOWLEDGEMENTS

This work was supported by the Brazilian Ministry

of Planning, Budget and Management, the Brazilian

Agency FINEP - Rede Renasic - Convênio

01.12.0555.00 and the Fundação de Apoio à Pesquisa

do Distrito Federal (FAP-DF).

REFERENCES

Brown, M. M., Brudney, J. L. (2011, October). Achieving

advanced electronic government services: An

examination of obstacles and implications from an

international perspective. National Public Management

Research Conference.

Bukhsh, F. A, Weigand, H. (2012) E-Government Controls

in Service-Oriented Auditing Perspective: Beyond

Single Window. Electronic Government Research,

8(4), 20p.

Campos, S. R., Fernandes, A. A., de Sousa Jr., R. T., de

Freitas, E. P., da Costa, J. P. C. L., Serrano, A. M. R.,

Rodrigues, D. C., Rodrigues, C. T. (2012). Ontologic

Audit Trails Mapping for Detection of Irregularities in

Payrolls. International Conference on Next Generation

ImprovedBusinessIntelligenceSolutionwithReimbursementTrackingSystemfortheBrazilianMinistryofPlanning,

BudgetandManagement

439

Web Services Practices (NWeSP 2012). São Carlos,

Brazil.

Cordella, A., Iannacci, F. (2010). Information systems in

the public sector: The e-Government enactment

framework. The Journal of Strategic Information

Systems, 19(1), p. 52-66.

Fernandes, A. A., Amaro, L. C., da Costa, J. P. C. L.,

Martins, V. A., Serrano, A. M. R., de Sousa Jr., R. T.

(2012). Construction of Ontologies by using Concept

Maps: a Study Case of Business Intelligence for the

Federal Property Department. Proc. International

Conference on Business Intelligence and Financial

Engineering (BIFE'12). Lanzhou, China.

Gil-Garcia, J. R., Martinez-Moyano, I. J. (2007).

Understanding the evolution of e-government: The

influence of systems of rules on public sector

dynamics. Government Information Quarterly, v. 24,

n.2, p. 266-290.

Hamner, M, Negrón, M. A., Taha, D., Brahimi, S. (2012)

e-Government Implementation in a Developing

Country: A Case Study. Managing E-Government

Projects: Concepts, Issues and Best Practices. 18p.

Huacarpuma, R. C., Rodrigues, D., Serrano, A. M. R., da

Costa, J. P. C. L., de Sousa Jr, R. T., & Holanda, M. T.

(2013). BIG DATA: A Study Case on Data From

Brazilian Ministry of Planning, Budgeting and

Management. IADIS Applied Computing 2013 (AC

2013) Conference. Fort Worth, Texas, USA.

Klimek, P., Yegorov, Y., Hanel, R., & Turner, S. (9 de

November de 2009). Statistical detection of systematic

election irregularities. Proc. National Academy of

Sciences of the United States of America (PNAS),

109(41).

Oracle. (2005). Oracle Database Data Warehousing Guide,

Tagaris, A. K., Benetou, X., Dimakopoulos, T., Kassis, K.,

Athanasiadis, N., Ruping, S., Grosskreutz, H.,

Koutsouris, D. (2009). Integrated web services

platform for the facilitation of fraud detection in health

care e-government services. 9th International

Conference on Information Technology and

Applications in Biomedicine.

United Nations. (2012). E-Government Survey 2012.

United Nations. Available in: http://unpan1.un.org/

intradoc/groups/public/documents/un/unpan048065.pdf.

Accessed in: May 2014.

KMIS2014-InternationalConferenceonKnowledgeManagementandInformationSharing

440