Itella Optimizes Warehouse Logistics Using IT Pro's BI.Qube

2021: Optimization of warehouse logistics using BI.Qube

On November 11, 2021, the company IT Pro announced the optimization logistic of business margins Itella and the implementation of the system. analysts BI.Qube Itella received tools to minimize costs by assigning incentive rates for piecemeal labor, as well as optimizing income by accurately setting tariffs for its customers, based on a detailed understanding of the volume of actually performed operations.

According to the company, having class A warehouses in Russia with a total area of ​ ​ about 200,000 m ², Itella provides a full range of warehouse services up to supply chain management. The BI.Qube system has become the "heart" of the single digital landscape for working with data on personnel work, warehouse and billing operations for Itella logistics complexes as part of the large-scale Digital Warehouse 2020 project program.

To manage your company's margin, you need to understand the cost structure for each customer. Before the digital transformation project, analytics was extremely labor-intensive, and the transition from time to piecemeal pay was impossible due to the need to introduce industrial technologies for consolidating data from disparate accounting systems.

People on temporary pay are not interested in working faster, as this does not affect the amount of pay. Shift managers and foremen spend a lot of effort controlling warehouse workers. The idea was to accurately manage their expenses, margins, determining how much time each operation takes, and already based on this manage labor rates. Reduce rates, but declare that we will pay more due to more intensive work. narrated by Nikolay Strakhov, Head of Systems Analysis at Itella IT

In order to optimize the process of controlling and optimizing costs, Itella has set itself the goal of implementing piecemeal labor for all warehouse personnel, as well as appropriate accounting and analysis tools. The company needed the opportunity to take into account not only the hours spent in the warehouse area, but also to automatically compare this time with the volume and types of transactions that each employee actually performed for a particular client, as well as on the basis of these data to compare efficiency on a large set of indicators, including the economic benefits of various forms of recruitment. So there was a project to build an analytical system, from which it was required:

• Analyze the activities performed by the employee
• calculate remuneration according to the piecemeal system of remuneration of employees;
• Have tools for checking and correcting data.

The system was to scale to all Itella sites and customers without modifications. As part of the project, it was necessary to consolidate heterogeneous sources into a single repository, since all operations performed by warehouse workers and time spent are recorded in various accounting systems. The following systems acted as data sources for the repository:

• 1C: ZiUP ;
• 1C: ARM Timesheet ;
• 1С:WFM (workforce management);
• WMS PSI;
• "BOSS Control";
• Billing system (customer certificates and accounts);
• Microsoft Master Data Services directories and non-system master data.

To complete the tasks of the project, it was necessary to extract, load information about who did and what operations, into a large fact table, compare with hours from 1C, take information from 1C ZiUP (positions, tariff rates, etc.) and then build analytical reports based on this data array.

Work on the project was built in several stages. At the first stage, the data of regular employees was loaded into the system. Based on these data, performance analysis forms were created, that is, how much each employee receives on a salary basis based on the work plan, and how much he could receive on a piecemeal basis. At the next stage of the project development, data on engaged (leasing) personnel were loaded into the system - how many hours and how many operations each such employee performed according to the warehouse accounting system. Further development of the project consisted in getting rid of paper timesheets by introducing a third-party software and hardware complex "BOSS Control" and connecting it as a data source to the BI.Qube system.

The BI.Qube system is the central analytical core of the entire digital landscape of Itella warehousing complexes. It allows you to calculate payments for staff and leasing personnel on the basis of various scenarios and a large number of influencing factors with the formation of reports for making economically sound management decisions to optimize FOT and the rate to leasing personnel suppliers.

The analytical system is based on Microsoft data technologies and BI.Qube meta-components, which are the own development of IT Pro. Use Microsoft SQL Server Reporting Services to build reports. Data from several heterogeneous accounting systems are consolidated in BI.Qube Metastaging, enriched with data from MDS (users enter the tariffs for types of work themselves) and transferred to the repository. Based on these data, analytical and verification reports are built in the reporting system to assess the effectiveness of the site, client, employee, as well as identify errors and frauds committed by employees in the course of their work.

BI.Qube System Architecture Diagram

System functions:

• operational analytics by pre-defined criteria;
• formation of regulated reports for labor calculations (for example, regarding the formation of an employee's calculation sheet indicating the work performed);
• accounting (enrichment of data with tariffs, regulations, hierarchies of types and subspecies of work - manual input through the MDS interface);
• reports (individually for Itella customer requests).

The system calculates 15 reports based on Microsoft Reporting Services. In them, the wage fund is considered in different sections: "Site Total," "Site Client Total," "Monthly Accruals," "Day Accruals," "Report on Completed Operations - Volume Indicators," "Number of Operations for the Required Period of Time, for the Selected Employee" and others. Reports are printed forms developed for the specificity of the customer, precisely for the Itella working time accounting group. In addition to the set of reports related to the calculation of PHO, a non-standard and complex report on the "Return" of leasing personnel was introduced into the system. The essence of it is that a person takes time to better get used to his duties. Accordingly, the longer he works, the higher his skills. The beginner becomes skilled, the experienced expert ― the expert. The system allows you to automatically assign and monitor the status of an employee engaged based on their employment history, work breaks, number, and duration.

Visual analytics is used to manage data quality. Heads of departments received Power BI dashboards to visually monitor and understand the causes of discrepancies in indicators coming from different sources of information. Dashboards were also built for the project manager, with the help of which it is possible to compare the effectiveness of various working time accounting methods. BI.Qube system provides data immersion (drill down). This enables you to analyze key figures at a detailed level and combine them across dimensions (employees, activities, groups, customers, clients, sites, and more). Access to information is provided according to the management level of the user. In addition to role hierarchies and distribution of report accesses, the system maintains a hierarchy of warehouse complex objects.

The solution allows you to perform the ETL process together with operating activities. Moreover, a slight drop in performance can be observed only when third-party systems work directly with DWH tables during the ETL process. The total duration of the ETL process and cube processing does not exceed 60 minutes. If BI.Qube errors are detected, Meta-control automatically sends notifications to responsible employees. The analytical system operates with data on hundreds of staff and leasing employees, from hundreds of thousands to a million warehouse operations are processed daily. It is used by dozens of specialists in the financial and logistics departments. Access to data is at a rate close to online mode. The solution can be scaled to all Itella sites and customers without any modifications to functional requirements.

As a result of the implementation of BI.Qube, a single analytical repository was created, in which data are collected and stored both on employee operations and on services provided to the company's customers. Itella for each warehouse employee, regardless of whether he is full-time or leasing, quickly receives information in a clear, understandable form - how many hours he worked, how many operations he performed and how much it all costs for the company. Thus, the project achieved the ultimate goal of optimizing the wage fund, transferring warehouse employees to its piecemeal form to calculate the number of actual operations.

As a result, they got an understanding of what exactly was done for specific customers, made it possible to understand the cost structure for each specific client, to understand whether the tariffs at which we work are profitable. And make the work of personnel suppliers more efficient, and more flexible to manage tariffs for customers. said Nikolay Strakhov, Head of Systems Analysis at Itella IT

The operation of the system positively affected the complex of the following characteristics:

• Speed and economic benefit. The BI.Qube system made it possible to quickly receive analytical reports on a large list of indicators, which is important for understanding the cost structure for each specific client and the business picture as a whole. BI.Qube's analytical tools help Itella gain more from personnel suppliers and customer contracts.
• Optimization and efficiency. The ability to individually track the load of each employee allows you to reduce downtime and more efficiently allocate labor duties.
• Optimization of interaction with contractors for warehouse operations services. The calculation of contractor fees has become more informative. Contractor sees and records actual data on personnel costs. Using the tools of the system, specialists can now track the real composition of leasing workers at the facility online.
• Transparency and warranty for customers. Due to the digital transformation of accounting processes, all transactions leave a digital trace. The client can get comprehensive details on each service provided to him, in addition, in the analysis of business activities (AHD) there is data for conducting investigations of insurance cases.

We managed to achieve the goal of optimizing the efficiency of work, workers perform faster and more operations. Specialists in warehouse logistics and personnel management regularly use analytics. said Dmitry Polikovsky, Director of IT Pro Development

As reported, the project is non-trivial in terms of tasks and technological solutions. A lot of code, a lot of calculations "on the fly," complex debugging. Requirements were formed during the project implementation. The first steps had to be taken in small iterations. The algorithms in the model are classic, all of which are reduced to arithmetic operations. Each digit depends on many conditions (work schedule, holidays/weekends, entry in the classifier, whether the login is associated with the name and others). A large number of combinations of different factors are calculated in the reports. All combinations, possible situations that were to be reflected in the calculations, could not be foreseen in advance by either the customer or the integrator at the stage of SOW formation. At the same time, it was necessary to take into account not only organizational situations (holiday, individual tariff, etc.) but also fluctuations arising as a result of the integration of completely different source systems. As a result, there were a lot of nuances that surfaced during the work on the project, and the customer himself did not suspect some of them. The information in the sources contained many distortions. It was time-consuming for the integrator to get the report to fully reflect all the required numbers. In addition, users wanted, having changed the number in the regulatory reference information system implemented on the MDS service, to immediately see a change in the report, which was contrary to the practice of building analytical repositories. In parallel with the project, the introduction of a working time accounting system was underway. Its introduction helped eliminate manual labor and related problems of information integration.