Keywords

1 Introduction

Analysis of public administration and public sector efficiency is an important area of public economics and public finance theory. The issue of the effectiveness of local authorities is also a question of the theory of regional development and management. The analysis of the effectiveness of municipalities and their comparison is an important tool of public policies and activities of public administration bodies (ministries, regions, municipalities). The statistical methods such as DEA (Data Envelopment Analysis) and FDH (Free Disposable Hull) are used to analyze the effectiveness of municipalities and their comparison. These methods are used to evaluate the effectiveness of units based on the size of inputs and outputs in the business and public sectors. Units are compared with each other.

We designed and obtained a grant for a project that uses the statistical methods mentioned above [5, 7]. The project will thus improve the quality of decision-making and management processes in public administration and increase the transparency of its activities at the level of municipalities, regions, and ministries. In next chapters, we briefly describe the basis of our project, its objectives, and first results. The main topics described in this contribution are the web application and associated database description.

2 Project of Information System for Decision Support of Municipalities

2.1 Performance Management in Public Sector

According to the last bibliographical analyses, e.g., [2, 10], performance management is still one of the most popular topics of research focusing on public management and public administration. Performance management is action, based on performance measures and reporting, which results in improved behavior, motivation, and processes, and which promotes innovation. The key aspects of performance management are deciding what to measure and how to measure it, interpreting the data, and communicating the results [5]. In the last decade, the potential of big data for performance management has also been discussed as well as the related problems of data misinterpretation and attribution problems[8]. Following the performance management literature, a general performance management model for local authorities has been developed. The performance management tools play an important role in this model (top of Fig. 1). Developing such “user-friendly” software tool dedicated for common users from municipalities was the goal of our project.

Fig. 1
figure 1

General performance management model for local authorities

Full size image

2.2 Performance Management in Czech Municipalities

Local government revenues in the Czech Republic amounted to 11.2% of gross domestic product (GDP). The EU average is 10.9% of GDP and 9.9% of GDP in the Eurozone [4]. Local government revenues in the Czech Republic consist mainly of shared taxes. The highest share is the value-added tax. According to the Eurostat data of 2016, local governments spend 10.2% of GDP on public spending. The EU-wide average was 10.8% of GDP in 2016; the share of the Eurozone countries was 9.7% [4]. These expenditures are realized in order to carry out the mission of the local government, which is defined in the Municipalities Act No. 128/2000 Coll. as follows: “The municipality takes care of the comprehensive development of its territory and the needs of its citizens; in the performance of its tasks it also protects the public interest.”

2.3 Performance Analysis with DEA

For our performance analysis, the Data Envelopment Analysis was chosen. We are currently experiencing a rapid increase in the use of this performance analysis tool in municipalities [3, 11]. Data Envelopment Analysis (DEA) determines the most efficient municipalities in the sample of our research. These form the “best-practice frontier” in a multidimensional space defined by inputs and outputs. The relative efficiency of municipalities lying under this best-practice frontier is computed by their deviations from the frontier [12]. This method is suitable for evaluating the efficiency, performance, or productivity of homogeneous production units – i.e., units that produce identical or equivalent effects, which we will refer to as outputs of this unit. Outputs are, by their nature, maximizing; their higher value results in higher performance of the tracking unit. To produce effects, the production unit utilizes inputs which are minimized by their nature; the lower value of these inputs leads to higher performance of the monitored unit [10]. The DEA method estimates the production units, whose input/output combinations lie at the efficiency boundary, are efficient units as it is not anticipated that there could actually be a unit that achieves the same outputs with lower inputs or higher outputs with lower inputs[1]. When using the DEA method, a constant yield model from a range or a model with variable yields from a range can be used.

2.4 Description of Project Objectives

We have designed a practical application of performance management to help analyze the effectiveness of selected municipalities in the Czech Republic. The title of the project is: “Application of nonparametric methods (DEA, FDH) to analysis and comparison of municipal efficiency.” The project output is the development of methodology and IT software application for easy use by potential users. This project was awarded by TACR agency to solve in 2018–2021 years [7].

3 Description of Information System Requirements

The planned output of the project can be identified with the creation of a fully fledged information system and methodology and the software application will be part of it. The definition of information system is broader than many people can imagine and states that “…the information system is a collection of people, technical means and methods (programs) that ensure the collection, transmission, processing, storage of data, for the purpose of presenting information for the needs of users operating in management systems…” [6]. Figure 2 defines the nature of the project outputs as parts of an information system (IS).

Fig. 2
figure 2

Nature of the project outputs as parts of an information system

Full size image

3.1 Definition of Software Application in the Project

Software application for information system in general is created by [6]:

  • Users with different roles

  • Presentation layer for users

  • Data/information processing layer

  • Database Management System (Database)

  • Data

  • Documentation

  • User access to data

The planned software application will serve for needs of public administration. Within the meaning of Act No. 365/2000 Collection of Laws [9, 13] which defines the concept of public administration information system (IS), the developed software application will form the basis for the public administration agenda information system. The requirements for the public administration agenda information system are not so strictly viewed as requirements for public information systems from the state regulator side.

3.2 Defining the Procedures for Creating a Software Application

It has been determined that the software application is part of the IS (which it creates together with the methodology). Standard IS development methods will be used in the development of the software application. These methods define the individual stages of software application development. The following stages are known: specification of requirements, analysis, design, implementation, testing, deployment, using, changing, and their implementation. The development of the software application by the research team, which would be solved without the implementation of the whole IS as a project output, represents a risk of alignment of project objectives. All mentioned stages of software application development can be repeated and versions of the information system can be created in iteration from the simpler variant to the final version of the IS, namely, functional IS model, IS prototype, and final IS version.

3.3 Definition of the Task of the Assignment and Specification of the Functional Requirements of the Software Application

The software application is based on defined project outputs where software application is an integral part of the information system. The specification of software application requirements is based on the division of requirements between functional requirements and (nonfunctional) system requirements. Functional requirements describe individual user roles that interact with a software application (user, government worker, database administrator, etc.) and also describe the individual role functions that the software application will perform on a role’s base. Functional requirements consist of the following features:

  • The IS process model is based on the methodology that is part of the project output. The process model includes a description of individual processes, process owners and process inputs and outputs.

  • A list of roles and a list of their assigned use cases (and usage scenario for each use case) can be created based on a process model.

  • Individual functions of the software application can be assigned to individual use cases.

Software application system (nonfunctional) requirements describe the system from the perspective of software architecture, applied technologies, security, standards used, and so on. Individual functional and system (nonfunctional) requirements will be gradually filled in the system and software versions of the functional model, e.g., in functional prototype and final version. All the above-mentioned requirement documents will be part of the IS documentation and software application.

Figure 3 shows the hierarchy of requirements as was described in the text above.

Fig. 3
figure 3

Hierarchy of requirements

Full size image

3.4 WebDEAr Software Application Architecture

In the case of a software application for a project, one will be kind of a so-called web application. The user of the output of the project (the output is an information system that consists of methodology and software application) will be communicated with the software application via a web browser. The working name of the web application is WebDEAr. The WebDEAr web app is like any web app and consists of (Fig. 4):

  1. 1.

    Presentation layer, a part of software application called the front-end WebDEAr, which is the application’s user interface and allows user interaction in one of the defined roles.

  2. 2.

    Information layer of software application, the so-called back-end WebDEAr, which represents the program implementation within the project developed algorithms DEA and FDH and represents also other activities necessary for running the software application.

  3. 3.

    Database (DataDEAr) that represents a database management system (DBMS).

  4. 4.

    The user data itself.

Fig. 4
figure 4

WebDEAr software application architecture

Full size image

3.4.1 WebDEAr-Front-End of Software Application

The WebDEAr web application runs on a dedicated server that provides all the necessary services. The front-end user interface has user inputs (such as buttons) and graphical visualization outputs. The user interface will be specified in the request document in the Front-end section.

The front-end technology can be based on:

  1. 1.

    DJANGO web framework (Python programming language path)

  2. 2.

    PHP for web development (PHP programming language path)

  3. 3.

    SHINY server for R environment (R programming language)

3.4.2 WebDEAr Back-End of Software Application

The WebDEAr back-end is in detail specified in the requirement document. Back-end technology used can be based on:

  1. 1.

    Programming language Python with libraries for DEA and FDH implementation, libraries for visualization (SciPHY), numerical calculations (NumPHY), and libraries for communication with database (DataDEAr) (path of programming language Python)

  2. 2.

    Based on PHP programming language (PHP programming language path)

  3. 3.

    Based on the R programming language (R programming language)

The DEA and FDH algorithms are the outputs of the project economist team and are ported in to the back-end of WebDEAr application by being delivered to the IT team in the form of R-written functions along with a validation set of data. The functions in R representing DEA and FDH algorithms are validated on a set of data that is provided along with the functions written in the R language to the IT team, which also verifies the correctness of porting into by team used language (Python or PHP). The DEA and FDH algorithms used in the WebDEAr back-end are specified in detail in the back-end requirement document, including their verification method.

3.4.3 DataDEAr Database Solution Architecture: Relational Data Management System

Database management system (DBMS) is based on relational database SQL server (Microsoft MS SQL Server Express, Oracle SQL Server Express or MySQL). Express version DBMS is a free version and is used for commercial and noncommercial purposes – it is limited to a few GB of data needed for the project. WebDEAr Back-End communicates with DBMS. The DataDEAr is specified in detail in the requirement document in the Database section.

4 Description of Developed Functional Prototype of Information System

4.1 Description of Implementation of Functional Prototype Architecture

The requirements for a functional prototype of a software application are:

  • Access to the application without authentication

  • Displaying information about the municipality or type of municipality

  • Displaying the results of the municipality, resp. categories of municipalities including inputs and outputs

  • Calculation according to the chosen method with entered data (inputs/outputs) for a particular municipality

  • Logging user requests, collecting information about their work with the application

It is a classic three-tier architecture. The data layer is represented by the Oracle relational database management system. The application layer that performs data verification and processing is made up of several modules. The lowest level of PL/SQL performs procedures and functions that are part of the database system. The calculation using DEA and FDH methods is solved by “plug-in software modules,” which are available as extensions directly to the Oracle database system. Because it is necessary to perform other statistical calculations, the research team decided to use the R language, which is directly designed for statistical analysis of data and their graphic representation; the module is also available for Oracle. The presentation layer is provided by the Apache web server with dynamically generated pages in PHP; PHP’s scripts also include partial data processing that interferes with the application layer.

A virtual server with the Oracle Linux 7.5 operating system was put into operation at the Faculty of Transportation Sciences Czech Technical University in Prague. The Oracle Database Version 18c and Apache HTTP Server were installed. The WebDEAr.fd.cvut.cz domain has been set up to access the website and is publicly available. Basic data has already been imported into the database system.

Screenshots (Figs. 5, 6, 7 and 8) represent various outputs of front-end of web application including the DEA algorithm implementation in the back-end.

Fig. 5
figure 5

List of municipalities

Full size image
Fig. 6
figure 6

Municipality financial details (anonymized data)

Full size image
Fig. 7
figure 7

List of statistical calculation with DEA algorithm

Full size image
Fig. 8
figure 8

Statistical calculation with DEA algorithm – detail (anonymized data)

Full size image

4.2 Description of Relational Database Model

Based on the analysis of the requirements for a software application for the analysis of municipal efficiency (the output of the project TL01000463-V002), a relational scheme of the database was created, which forms the core of data structures for storing the necessary data. The scheme currently contains 23 tables (Fig. 9).

Fig. 9
figure 9

DataDEAr database model

Full size image

The central table is the MUNICIPALITY table, containing basic information about municipalities, such as the code according to the CZSO, code NUTS5, IČO, name, GPS position (also in WGS84 format), altitude, catastral area, type of municipality (TYPE_MUNICIPALITY), reference to district (district municipality), municipality with extended competence, and municipality with authorized municipal office. There are currently 6249 entries in the MUNICIPALITY table.

The REGION table is a codebook that contains all regions (their code and name) of the Czech Republic. The REGION_DISTRICT table links the region codebook to district municipalities.

For the purposes of comparing the relevant municipalities, the enumeration table (List of Value table) CATEGORY is created for the purpose of categorizing municipalities that is linked to the MUNICIPALITY table using the reference table CATEGORY_MUNICIPALITY.

Important data for analyzing the effectiveness of municipalities are statistical and financial data. The statistical data list (code list) is in the LIST_STATISTICAL_DATA table, which also contains a reference to the UNIT code list. Individual statistical data on submunicipalities are linked in the MUNICIPALITY_STATISTICAL_DATA table.

For financial data, we distinguish paragraphs and items that have a code list in the LIST_FINANCIAL_PARAGRAPH table, respectively. LIST_FINANCIAL_ITEM. Both dials are linked to the UNIT table. The financial data of individual municipalities are linked to the period and the municipality in the MUNICIPAL_FINANCIAL_DATA table, where three data are available – budget approved, after changes, and actual. Currently, there are a total of 5,263,845 entries in the MUNICIPAL_FINANCIAL_DATA table for 524 entries in the LIST_FINANCIAL_PARAGRAPH table and 527 entries in the LIST_FINANCIAL_ITEM table. Financial data is from 2012 to 2017.

Various inputs and outputs of statistical and financial data are used to analyze the effectiveness of municipalities using the DEA and FDH methods. The INPUTS and OUTPUTS tables referring to LIST_INPUTS and LIST_OUTPUTS are used to record which data was used as inputs and which as outputs. Specific data are captured in the MUNICIPALITY_INPUTS and MUNICIPALITY_OUTPUTS tables, with a view to a possible later change of data.

Database structure design allows for the retention of all results in recalculations. The basic data in the CALCULATION table, including reference to the method used (table LIST_METHODS), are stored for each calculation. And the results from each calculation for each selected village are stored in the OUTPUTS table.

When processing data from various sources, we encountered not entirely 100% functional integrity constraints on financial data on the code list of municipalities, sections, and items.

5 Conclusion

In the frame of the project dedicated to develop a performance management tool for municipalities, a functional prototype of information system was designed. The requirement document for describing functional and nonfunctional requirements was prepared. Database model and database itself were developed and were filled with data from the public sector. Functional prototype of web software application which consists of front-end and back-end layers was developed. Back-end of developed application is filled with database connectivity functions and with DEA and FDH statistical functions. Porting of developed DEA and FDH algorithms from statistical level to back-end web application level was tested. Next time we will devote our concern to the development of process model of comparison of effectiveness of local authorities and the following comparison methodology. We have to optimize data structure of database model and data cleaning next time. Importing data mechanism and data manipulate based on Microsoft EXCEL as client for database management system should be prepared.