How to automate the collection of KPI for the month and leave users almost satisfied

In many organizations, subdivisions are evaluated using KPI (Key Performance Indicators). In the organization where I work, such a system is called the “performance indicator system,” and in this article I want to talk about how we managed to automate part of the work with the indicators in a month. With all this, our labor costs were not the greatest, but at the same time we tried to implement some of the old Wishlist. In my story there will be no HYIP technologies or revelations (after all, the provincial development is harsh), but there will be some sketches on the topic that will help you understand where we started, what we did, and what thoughts we had on the results of the development. If you have not yet become bored, please under the cat.

Prehistory

Back in 2006, we already introduced a system of performance indicators: fifteen evaluation criteria were developed, a method for calculating them, a quarterly periodicity for calculating these indicators was established. The assessment was carried out for twenty-two branches of the organization located in all areas of our region. And so that the indicators are achieved with great enthusiasm, the premium was attached to them - the higher the sum of the indicators, the higher the place in the rating, the higher the place in the rating, the higher the premium and so every quarter and every year.

Over time, the composition of the criteria changed, each quarter new ones were added, then old ones were excluded. At the peak, approximately in 2016, the number of indicators exceeded forty, and now there are only fourteen.

However, all this time the process of their calculation was of the same type. Each criterion is calculated by the responsible division of the head organization according to the algorithm approved for this criterion. The calculation can be carried out using a simple formula, as well as a number of complex ones, it may require data collection from several systems, and it is very likely that the algorithm will change quite often. And then everything is much simpler: the indicator calculated as a percentage is multiplied by its coefficient, thus, a score is obtained according to the criterion, then the scores are ranked and each division takes its place according to the score. The same is done with the sum of points for the calculation of the final place.
')
From the very beginning, in order to take into account and calculate all the above, Excel was used. For many years, the final set of criteria and further calculation of points and places was made in a nice tablet, part of which is shown in the figure.



I note only that in this pretty tablet, most of the columns are simply hidden, but in reality in the 4th quarter of 2016 it looked like this



Agree, such a number of indicators and such a volume of the table do not add simplicity to it. In addition, each of the criteria was calculated separately, and the girls from the general department made this summary table with their hands. Considering that not only the composition of the criteria changed, but also the calculations could be redone several times (for many reasons), the last days of each quarter, to put it mildly, did not please. We had to constantly change the data in the table, check and recheck them: you will forget to add a new column to the sum of points, or you will not update the coefficient, and because of this, someone will have a lower place, and with it a premium less. Therefore, after the heroic information of the table, a no less heroic test began; all those who counted each criterion, managers, and even an organizational IT specialist, checked and agreed. Considering that all the work was done manually, and calculations of indicators for the pivot table were sent to the post office, quite often there were situations when there were both formulas errors and data of irrelevant versions in the pivot table. The captain of evidence reports that after identifying each error, the verification process was restarted.
When all indicators were calculated, the tablet was approved and sent to the branches, it was rechecked there in the branches: what if the insidious Excel thought something was wrong? I think it is obvious that drawing up an annual report on the criteria or analyzing historical data turned out to be no less exciting quest.

However, from a managerial point of view, such a system is quite effective and allows, if necessary, to pull down subsiding areas of work, monitor and have an idea of ​​what is happening in each of the branches in one or another area of ​​work. In addition, the location of the branch, calculated by the criteria, was an integral indicator.

The system must change

Practice proved the necessity and importance of the evaluation system. In early 2017, it became clear that the calculation of the criteria once a quarter allowed us to evaluate the work done, but here it was poorly allowed to monitor it. Too much time. In this regard, we decided that the calculation of indicators will be held once every two weeks, and the results will be summarized all the same quarterly. An increase in the frequency of calculation allowed the branch managers to quickly respond to changes and increase control over the processes within their departments.

Here, only in the head organization, few people were pleased with the prospect of carrying out the data collection process described above not quarterly, but every two weeks. As it is not difficult to guess, they decided to automate the process. The dates turned out to be tight: from the moment of the decision to switch to the new periodicity of the calculation to the actual transition, only a month had to pass. During this period it was very desirable to come up with something better than a bunch of Excel for the set and calculation, and mail for collection and notification.

At first, there was a rather hot discussion about the fact that it is very, very necessary to make automatic the calculation of indicators itself, taking into account all their formulas and data sources. But given the tight deadlines, the complexity of such functionality and the need to maintain it up to date, we managed to come to the following system requirements:

• There should be a reference book of criteria, preserving the history of their changes;
• It should be possible to enter and store the calculated indicators, as well as their conversion into points as in the plate itself;
• According to the values ​​of the indicators, a report should be made available to all interested parties;
• Naturally, all this should be provided with a web interface.

The functionality is quite small, but a little time.

Start of development

I have always been attracted to the rapid development and automatic generation of interfaces. In those cases when it is necessary to implement the functionality of CRUD, the idea seems very tempting when the interface and part of the business logic will be provided out of the box, so to speak. Of course, the interface will be unassuming and the logic is clumsy, but for many tasks this is enough.

Driven by these ideas, I decided to try something like this. There are Spring Roo, Cuba and other interesting tools, but the choice fell on OpenXava. First of all, once I already did quite a simple application on it and was satisfied, and secondly, at that time this framework fit into our technological stack quite well. In addition, the presence of a short tutorial in Russian is very pleased.

A brief description of the features and capabilities of OpenXava can be read here . OpenXava is a framework that implements the automatic construction of a web interface integrated with a database, based on JPA, and uses annotations to describe the rules of visualization. The application is based on business components — Java classes that contain the information necessary to create applications. Such information includes data structure, validators, valid representations, mapping on database tables. Operations on business components are carried out through controllers, out of the box who can CRUD, search, export to PDF, etc. OpenXava application is a set of modules. A module associates a business component with one or more controllers. To display the interface, the views defined for each business component are used. Nothing unusual, MVC with a bit of its atmosphere.

Data storage

In most applications, we use IBM DB2 DBMS. A small database was created, storing reference books of criteria and criteria for evaluating branches, a directory of branches, and a label containing calculated criteria values. Each criterion at a certain point in time is assigned a coefficient that will be used when calculating points. Each branch, for each criterion, receives an estimate also for a specific date. The data on the values ​​of the criteria are stored historically, that is, data that is relevant for any date will be those that were entered as of the nearest date in the past. This approach, inspired by the registers of information from 1C: Enterprise, seems to me to be quite convenient: there is a story with editing / deleting issues as well.


In order to get the coveted report on the indicators of the branches from the database, stored functions were created, the results of which are already distributed to the Java class. Functions look like this.



Transferring a part of business logic to a DBMS turns out to be quite justified, especially when it comes to preparing data for different reports. Operations can be written in a more concise and natural form. In Java, such data manipulations would require more code and some effort to structure it. Although relatively complex or non-trivial operations are still easier to program in Java. Therefore, in our application for data sampling, an approach is used when joining data sets, clipping conditions and some operations that can be performed by window functions are performed in stored functions and procedures, and more complex logic is implemented in the application.

application

As I said before, OpenXava was used to implement the application. To get a standard interface with it and CRUD out of the box, you must perform some actions.
Let's start with the fact that in the web.xml it is necessary to describe a filter and a servlet from an addon navigating through the application:


Next, in the controllers.xml file, we define the controllers used in the application. In our case, the simplest is enough:


In the given controller, the functions of the controllers included in OpenXava are combined by default, the functions of which are not difficult to guess from the names.

And, finally, in the application.xml file, we associate the created controller and model. Like that:


As mentioned above, the application is based on the business components that make up the application model. For example, consider the RegValueCriteria component associated with the controller in application.xml. This component describes the value of the criterion for the branch (for brevity, only the description of the class fields is left, and methods like getters and setters will be omitted):


In addition to the usual annotations JPA. You can see annotations and OpenXava. They should be considered in more detail.

The @View allows @View to control the presentation format of class fields, while using special syntax in the form of square brackets, fields can be combined into groups and arranged horizontally and vertically using the symbols , and ; . If several displays need to be defined for a single component, the @View annotations are grouped using the @View annotation. In our example, the properties were designed as follows:

 @View(members = "idCriteria [idCriteria];" + "idGroupCriteria [idGroupCriteria];" + "idDepart [idDepart];" + "data [dateReg, percent, val]") 

And it looks like this:



Unlikely, but with a minimum of effort. However, there are ways to "revive" the form a bit.
In order for the registration date to be filled when creating the component, the @DefaultValueCalculator annotation is @DefaultValueCalculator , which calls a special calculator object. Here, a calculator from OpenXava itself is used, but you can also make a custom one. To display the date using the appropriate control, the @Stereotype annotation is @Stereotype .

To configure drop-down lists containing related objects, use the @DescriptionsList annotation, in which you can specify which property will be displayed in the list.

Using annotations, you can implement some business logic of the form itself. For example, so that when the percentage changes, the value is calculated automatically, taking into account the criterion coefficient, you can apply the @OnChange annotation for the BigDecimal val field. For the @OnChange annotation @OnChange work, it needs to point to the class implementing the OnChangePropertyBaseAction interface. A single execute() method must be implemented in the class, in which the input data is taken from the view, the calculation is made and the calculated value is written back to the view:


For the tabular presentation of data, the @Tab annotation is @Tab , which allows listing the properties of objects that will be displayed in the tabular representation. In our example, the abstract was framed as follows:

 @Tab(properties= "idDepart.name, idCriteria.name, idGroupCriteria.name, dateReg, percent, val" ) 

It will look like this.



Pleases the presence of filters, pagination and export out of the box, but many details need to be improved by a file.

The work with other components is similarly constructed. Using OpenXava has dramatically reduced labor costs for the implementation of CRUD functions and most of the user interface. Using actions from predefined controllers and annotations to build forms saves a lot of time if you don’t find fault with the details and try to implement something more complicated than a form with multiple events. Although it may be a matter of experience.

That for which everything was started

Remember why the application was started? Yes, in order for the table with indicators not to be heroically reduced in Excel, but created automatically on the basis of the entered data. In the browser window, the pivot table looked like this:




I will not give details of the implementation, since everything is not very good with it and the mixture of JSP with the HTML generated when requesting data is not something that should be shared with the general public. At the same time, the data sampling itself was demonstrated above.

However, I want to dwell on one interesting detail. When the requirements for the application were collected, the management wanted very much that in addition to the summary report, the values ​​of a separate indicator could be displayed in the form of a map of the region, divided into districts, indicating the place and score of the respective branch. Who on the map learned the region - that fellow =)



On the one hand, the requirement was optional, but on the other hand, the picture promised to be vivid, and from the point of view of implementation it was interesting to try. After some thought, the idea came to find the image of the region in the SVG format and make it an XSLT template.

The resulting template is easily filled with data, and then converted to PNG.
First, using the query described above, the data is sampled, the resulting data is converted to an object of this class:


Next, convert the object to XML;


Now let's take the resulting XML, the prepared XSLT template, apply the transformation and get svg at the output:


In principle, this could be stopped, browsers display SVG without any problems. But the described reports were also obtained via Telegram-bot, therefore SVG should be converted to some type of JPEG or PNG. For this we use Apache Batik


Report in the form of a map is ready. It can also be viewed via the browser and requested from the telegram bot. In my opinion not bad.

Conclusion

By the deadline, we had, and in March 2017, performance indicators instead of Excel began to be regularly recorded in the created system. On the one hand, the maximum task was not solved, the most difficult part of the calculation of indicators is performed manually. But on the other hand, the implementation of these calculations carried the risk of constant improvement. In addition, even a simple data collection interface created removed a huge number of questions with constant changes, version control, and a set of Excel tables. A large amount of manual work, checks and rechecks have been removed.

It’s impossible not to say that the interface on Open Xav did not make the users too happy. At first, there were many questions about its features. At some point, users began to complain that it takes too much time to enter data and, in general, “we want, as in Excel, only the program”. It was even necessary to monitor the input rate based on data on the time of creation of records. This monitoring showed that even in the most severe cases, users did not spend more than 15 minutes on input, but usually fit into 5-7, despite the fact that they needed to enter data from 22 branches. Such indicators seem quite acceptable.

However, I want to note two things:

1. Open Xava has shown itself as a good tool to quickly create an interface. I would even say a prototype interface. Also its undoubted advantage is general orderliness and regularity. All forms in the application are created according to uniform principles, which allows the developer not to invent bicycles where it is not necessary, and the user to deal with typical sets of forms. However, attempts to implement more complex logic or change the controls for themselves prompted us to a number of problems that could not be solved in the allotted time. Most likely, we simply did not understand, and the goal was to create a simple CRUD interface with a minimum of effort. For myself, I conclude that Open Xav is an interesting tool, in which you simply do simple things, but if you need to do something complicated, I would prefer to spend more energy on creating the client side using ExtJS or React, but at the same time have more flexibility .
2. Even confident users take the new interfaces hard. This is certainly not a secret. In my opinion, this is primarily due to the lack of understanding of the consistency of many interfaces. For many, any application is a set of screens, each of which is unique and works according to its unknown principles: for example, there is a form with a list of objects / lines (a list form), but for very many users it’s not at all obvious that each such form in an application can have uniform filtering, pagination, sorting functions and, in general, the same behavior, complemented by specific functions. This is aggravated by the fact that a large number of corporate software are almost spontaneous clutter of buttons and forms, seasoned with vague documentation in the style of "Click this button." From this point of view, the interfaces created by the same Open Xav discipline the developers of users, create more order in the head. True, with this approach, the magic buttons do not disappear anywhere, but become neatly arranged in shapes.

If we talk about the benefits for the organization as a whole, then after the introduction of the application, as expected, the degree of control increased. Field managers received a performance monitoring tool with which they can influence work more quickly, compare indicators for different periods, without confusing in the stream of files sent. The interest of managers in the constant and vigilant control of their branch was discovered by us in a very interesting way: looking at the Telegram-bot logs, we noticed that some reports were received at 4 or 5 am. We immediately see who is interested in work.

That's all. I would appreciate constructive feedback!