Creation of an analytical repository for a unified digital landscape of Itell - BI.Qube, based on Microsoft data technologies

The main objectives of this project were:

- Cost optimization by Itella when introducing piecework wages for all warehouse personnel

- Transparency and completeness of data for the Client for each service rendered to him

- Implementation of appropriate accounting and cost analysis tools

- Consolidation of heterogeneous sources into a single data warehouse

The analytical system is built on Microsoft data technologies and BI.Qube meta-components, which are proprietary developments of IT Pro.

To build reports, Microsoft SQL Server Reporting Services tools are used.

The following accounting systems acted as data sources for the storage:

1C:ZiUP

1C:ARM Report card

1С:WFM (workforce management)

WMS PSI

BOSS Control

Billing system (acts and accounts of Clients)

Directories and off-system master data (Microsoft Master Data Services)

The project is interesting and non-trivial in terms of tasks and technological solutions.

The development was initially carried out according to the Agilu methodology: the requirements were formed during the implementation process, each sprint was a complete solution, in accordance with the requirements agreed at that time.

The algorithms in the model are classic, but there are a lot of “ifs” when calculating the required indicators. The reports calculate a large number of combinations of different factors. It was not possible to foresee all the combinations that needed to be reflected in the calculations at the stage of the formation of the TOR. An additional requirement was the prompt reflection of changes in reports when changing directories on the MDS service, which went against the practice of building analytical repositories.

The solution allows you to perform an ETL process in conjunction with operational activities. The total duration of the ETL process and cube processing does not exceed 60 minutes. When errors are detected, BI.Qube Meta-control automatically sends notifications to responsible employees.

The analytical system operates with large data arrays, from hundreds of thousands to a million warehouse operations are processed daily.

System functions:

• operational analytics in the context of pre-defined criteria;

• formation of regulated reports for labor calculations (for example, concerning the formation of an employee's pay slip indicating the work performed);

• accounting (enrichment of data with tariffs, standards, hierarchies of types and subtypes of work - manual entry via the MDS interface);

• reports (individually according to the requests of Itella clients).

The system calculates 15 reports based on Microsoft Reporting Services. In reports in different sections, the wage fund is considered: “Total by site”, “Total by client by site”, “Accruals per month”, “Accruals per day”, “Report on completed operations - volumetric indicators”, “Number of operations, for the required period of time, for the selected employee”, etc.

BI.Qube provides data immersion (drill down). Thanks to this, you can both analyze indicators at a detailed level, and combine them by various dimensions (by employees, operations, their groups, customers, clients, sites, etc.).

Access to information is provided in accordance with the management level of the user. In addition to hierarchies of roles and distribution of access to reports, the system maintains a hierarchy of objects of warehouse complexes.

Currently, the system is used by dozens of specialists from the financial and logistics departments.

The operation of the system had a positive effect on a set of characteristics:

• Efficiency and economic benefit. The BI.Qube system made it possible to quickly receive analytical reports on a large list of indicators

• Optimization and Efficiency. The ability to individually track the workload of each employee allows you to reduce downtime and more rationally distribute work responsibilities.

• Improvement of interaction with contractors for services of warehouse operations. Calculation of payment for contractors' services has become more informative. The contractor sees and records the actual data on personnel costs online.

• Transparency and Guarantee for clients. Through the digital transformation of accounting processes, all transactions leave a digital footprint. The client can get an exhaustive detail on each service rendered to him.