Autoplus (The Information and Analytical System (IAS) of monitoring of production indicators based on Pentaho BI)

Monitoring of indicators of activity

At the beginning December, 2013 the "Information Technologies and Services" company completed the project of creation on the platform of BI Pentaho CE of the information and analytical system (IAS) of monitoring of production indicators of Avtoplus Group.

About the Project

The project is implemented within 3 months during the period September-November, 2013 and included design, implementation of the project solution, trial operation, preparation and start of commercial operation. The project is completely executed by forces of specialists of "Information Technologies and Services" company generally in the remote mode with the minimum derivation of staff of Avtoplus Group. Project objective was improvements of controllability and efficiency of primary activity of Avtoplus Group due to increase in accuracy of the information, efficiency of its processing, data availability for the analysis and decision making, increased security from unauthorized access to information. The implemented system provides formation of the consolidated statements on all companies of the Group covering planned and actual targets on the main activities:

• car sales;
• rendering service;
• sales of spare parts and joint products;
• inventory management;
• insurance and credit sales.

IAS of monitoring of production indicators, on the one hand, allowed heads of divisions to make quickly management decisions, on the other hand, to standardize routine operations on data loading in storage and to establish accurate cooperation of all divisions which are taking part in preparation of the reporting.

Solution description

Specialists of "Information Technologies and Services" company developed an information and analytical system of monitoring of production indicators of Avtoplus Group on the Pentaho BI Community Edition platform. By means of ETL procedures a system provides automatic loading from 9 autonomous bases ERP Navision of credentials of 12 branches in the consolidated analytical ROLAP-storage and more than 200 indicators for the multidimensional analytical model including such cuts as branches, warehouses, brands of cars, categories of personnel, work types, actions, types of buyers and commodity groups perform calculation. In addition, on the basis of ETL Pentaho BI, easily configured procedures are implemented:

• elimination of the internal turnovers;
• restriction and calculation of production indicators;
• transformations of reference books.

The implemented system supports input of planned targets, allows to receive the regulated forms of the reporting and more than ten types of flexible (Ad-hoc) reports on production indicators of Avtoplus Group. Reports are available in different formats, including HTML, PDF, EXCEL. The flexible reporting gives to users the chance to work directly with data marts using the drag-and-drop mechanism in the usual interface of the Internet browser. A system works running the Web portal, supports automatic calculation and mailing of reports on the schedule, provides the authorized WEB ACCESS and a role model of management of powers.

Opinion of project participants

"Project deliverables entirely met our expectations, works are performed at the high professional grade according to technical requirements and in the planned terms. We wish to staff of "Information Technologies and Services" company of successful projects further and we hope for continuation of effective cooperation" Pavel Shestopalov, the CEO of Avtoplus Group

"We looked for the suitable tool and strong team for consolidation of credentials of our branches and formation of the summary reporting under key performance indicators long ago. Specialists of ITS, having developed a solution prototype at our request, visually confirmed both advantages of the platform and own professionalism. The created solution, except the target formatted monthly reporting, gave to heads and business analysts an opportunity with the minimum delay to keep track of daily dynamics of the consolidated indicators, independently selecting analytics and a form of representation" Shestopalov Sergey, the Director of information technology of Avtoplus Group

"The implemented system has big flexibility, both regarding visualization, and in respect of expansion of functionality and making changes in a technique of calculations. Thanks to the architecture put by a command of ITS, changes are made practically to a raid through tables of parameters and rules of transformations" Nadezhda Hvorova, the Programmer of Directorate on IT of Avtoplus Group

"As the platform for development of business applications of Pentaho CE is not inferior to the commercial BI systems, neither in the power of the instrument of integration nor in richness of visualization tools. Pentaho CE is the adequate choice for implementation of such multidimensional tasks as creation of IAS of Avtoplus Group Sergey Vershinin, the Chief consultant, Department of BI solutions of Informatsionnye tekhnologii i servisy LLC

Development of BI in Avtoplus Group

Geoinformation analysis

In 2014 the "Information Technologies and Services" company developed and implemented the geoinformation supplement to an information and analytical system of monitoring of production indicators of Avtoplus Group

The implemented system of the geoinformation analysis visualizes key indicators of the company in a binding to geographical coordinates. A system allows to make management decisions taking into account distribution of results of activities for territories. Within the project display of analytical indicators in a binding to residential districts of the cities of presence of the company – Yekaterinburg, Omsk, Nizhny Tagil and Kamensk-Uralsky was implemented. The developed tools provided support of such business challenges as:

• Management of marketing campaigns and advertizing campaigns:
  • the choice of areas for holding campaigns and actions;
  • costs planning on marketing on residential districts;
  • analysis of effectiveness of actions.

• Assessment of dynamics of growth of the company and competitive analysis.
• The choice of location for new salons.

A system was implemented on the Pentaho CDE platform (Community Dashboard Editor) using the analytical storage operating in Avtoplus Group. All components of a system of the geoinformation analysis belong to Open Source to products that finally allowed to reduce project cost considerably.

Solution description

Sales informations of cars and to service orders on the set residential districts on the map are displayed in the form of the thermal chart. The detailed information in a binding to geocoordinates is displayed in the form of markers using a clustering and depending on a measure value.

Within implementation of a system of the geoinformation analysis as a cartographic basis Open Source the solution OpenStreet Maps was selected. For a geokoding (determination of geographical coordinates to the text address) the API service Yandex. Maps. for geometrical calculations was used (hits of coordinate to the residential district) possibilities of the MS SQL Server are used.

As the tool for creation of borders of the set residential districts possibilities of a geographic information system of 2GIS are used. The user independently selects the inspected district of the city on card 2GIS and unloads coordinates of borders of the residential district in the file of the KMZ format. Further by means of the Pentaho BI ETL procedures data of borders of the residential district automatically remain in analytical storage for display on the card.

Plan-fact analysis of operational indicators

In 2014 the "Information Technologies and Services" company developed a packet of interactive panels (dashbord) for carrying out the plan-fact analysis of indicators of activity of Avtoplus Group

Dashborda with a possibility of viewing the queried information both on stationary, and in mobile workplaces (smartphones, tablets) were developed for evident data view of the plan-fact analysis of activity.

Within the project several tens of diagrams grouped in types * activity were implemented:

• sales;
• services;
• financial services.

and in a representation form:

• monthly in a year;
• monthly in a year an accumulative result;
• on days of month accumulative result;
• comparison the plan/fact in a month with last year.

At display of dashbord the following sample characteristics are available to the user:

• branch (salon);
• date (year and month).

Dashborda help managers to control quickly the indicators of activity of the company which are in their area of responsibility, including using also mobile devices.

Solution description

The solution was implemented using Pentaho CDE technology (Community Dashboard Editor). Interactive panels (dashborda) are brought as to the screen of the stationary PC via the corporate WEB PORTAL, and to mobile devices running iOs and Android using mobile applications of Pentaho.