RSS
Логотип
Баннер в шапке 1
Баннер в шапке 2
Project

L Etual, retail network (Microsoft Business Intelligence)

Customers: L Etual, retail network

Product: Microsoft Business Intelligence

Project date: 2007/01  - 2007/12
Number of licenses: 100

Content

Situation

In 2004 in the company the ERP system of Microsoft Dynamics AX was implemented. The OLAP technologies which allowed to receive the most necessary analytical reports were at the same time involved. In 2006 the end-to-end system of standard reports in a standard tabular form was created. For viewing the received reports Microsoft Excel was used.

Expansion of number of users demanded attraction of the new tools allowing to create panels of indicators with the publication via the web interface.

Therefore in 2007 in the company it was decided to develop a new system for the analysis of the continuous and historical data using modern technologies of visualization for obtaining the objective information in due time and in the right place. At the same time special attention was paid to questions of access isolation of users to information, increase in number of users of a system and decrease in load of the ERP system.

Solution

Daily our employees manage the movement of tens of thousands of SKU in hundreds of shops. In a tabular style to analyze this enormous amount of data very difficult. A new system gives to users the chance at a single glance to estimate the generalized, most important indicators, - Nikolay Zaytsev, the Head of Department of IT of L'etual company

Microsoft applications of SharePoint Server, allowing to organize a portal system for the publication of the reporting and storage of individual preferences for each user, and Microsoft PerformancePoint Server, providing the developed means for visual representation of data, including dashboards on which data are displayed by means of evident diagrams, charts, indicators allowing to estimate at a single glance a current status of processes became a basis for a new solution.

The pilot project was executed with involvement of the Microsoft partner, TEKORA company. At this stage in the company set the task to study new tools and rich opportunities given by them therefore pilot projects were implemented on certain conditional indicators.

Nikolay Zaytsev, Head of Department of IT of L'etual company: "We had a great desire not only to receive ready result, but also to deeply master this technology to have an opportunity independently to create and adjust our corporate system of the reporting and options of its evident display".

After end of pilot projects in the company could start development of own system of analytical indicators most of which precisely and fully would display a current status of business and allowed to keep track of dynamics of development. Emergence of new opportunities allowed the company to review in many respects the former reporting system and to develop essentially new set of indicators. So, in addition to monitoring of such traditional indicators as turnover and coefficient of marginal income, it was decided to control also percent of accomplishment of the sales plan that as it appeared, reflects current situation much more visually.

The main objective of a new system was to give an opportunity and to top management, and to ordinary users at a single glance to estimate a current status of process, to reveal potential problems and then to profoundly analyze these critical places. Different visual means — column charts, multi-colored diagrams, scales and indicators, with a possibility of further transition to the analysis of selective tables were for this purpose applied.

Besides, the single system of the reporting applied in scales of all company is designed to create "the uniform version of the truth", i.e. to provide to all employees access to the most complete, the reliable, consistent and unified information that will allow different services and departments to use uniform terminology and to provide bigger mutual understanding so and to increase efficiency.

The created reporting system has hierarchical character. mozhkhno to receive the most operational reports reflecting information "in real time" from Microsoft Dynamics AX.

However data on sales from POS systems, about stock balance, about orders and movements of goods get to the ERP system not at once, and after receiving and processing of the data packets arriving from shops, warehouses, the central distribution center once a day after change closing. Taking into account big geographical distribution of network (shops of the company are located across all territory of Russia, in different time zones), all these processes of information exchange happen asynchronously. All collection cycle, cleanings and information processing takes 12 hours.

Once a day information is unloaded from the ERP system to the data warehouse implemented on the basis of the Microsoft SQL Server. Transactions of cleaning and data translation, replacement of codes with text information, creations of data marts are in passing executed. All types of the reporting are tables, analytical cubes, diagrams — form through Microsoft SQL Server Reporting Services already on the basis of the data which are contained in storage. Information is relevant for the end of previous day that is quite sufficient for the comprehensive analysis and decision making. Thus the completeness, reliability and data consistency is provided, and processes of reporting become more productive. Besides, such approach allows to unload the ERP system, to reduce the number of users and to use additional resources for processing and data analysis.

Information on merchandising, purchases and sales comes to the relational data warehouse based on SQL Server of 2008. About 700 GB of data contain history in 5 years. Access for users is provided through two data marts. The first show-window is constructed based on OLAP of a cube, about 30 GB in size.

Most of users address it through Excel Pivot Tables. The second relational show-window stores about 300 GB in the denormalized form. To it the address is kept through reports of SQL Server Reporting Services. Reports are published on the internal portal based on SharePoint Server 2007.

The standard preconfigured reports and dashboards are available to employees on the corporate portal created on the basis of Microsoft SharePoint Server. The tree of reports contains more than 70 ready report forms on market share, key indicators, dynamics of sales, top lists, IT monitoring and other areas. Users can not only browse them from any point thanks to the web interface, but also save own settings for more effective work. Technologies of visualization Microsoft PerformancePoint Server are applied to evident data mapping.

Nikolay Zaytsev, Head of Department of IT of L'etual company: "Originally our managers, engineers and operators who got used to work "in the old manner' were skeptical about new, visual tools enough. However it turned out already soon that it is simply impossible to work without them".

Except the main storage based on data of the ERP system in the company also the storage of checks connected with the CRM system is created. On the basis of these data statistics of sales forms and also special algorithms of check of respect for technologies of sales and identification of cases of fraud are executed. Results of such processing allow to implement "feedback" with shops and already next day to send notices on the revealed violations in shops.

Nikolay Zaytsev, Head of Department of IT of L'etual company: "Daily our employees manage the movement of tens of thousands of SKU in hundreds of shops. In a tabular style to analyze this enormous amount of data very difficult, trudozatratno. It is impossible to avoid the errors caused by "a human factor". A new system gives to users the chance at a single glance to estimate the generalized, most important indicators, and already then, if necessary, to study data in tables in detail".

Separate big task is creation of a system for job analysis of the IT infrastructure. In the company there is a firm schedule of execution of operations — summary planning, formation of orders for a warehouse, assembly of these orders, their delivery in shops. Violation of this diagram would lead to serious losses and idle times of transport. Therefore it is very important to provide timely and uninterrupted processing of all arriving information. The data packets arriving from shops and warehouses (data on sales, stock replenishment requests, responses on readiness, etc.) are put in the corresponding queues on processing. These queues need to be coordinated, provided their continuous processing and to quickly resolve the arising critical situations. For this purpose the evident and convenient tool which allows to perform monitoring an IT infrastructure status is necessary for IT service.

By means of visualization tools of Microsoft PerformancePoint Server specialists can control a current status of a system (data on him are updated each 15 minutes) and even to predict development of the situation at the next several o'clock that allows to interfere, fix timely arising problems, to redistribute resources and "align" a situation. Access to such system is provided via the portal created by means of Microsoft SharePoint Server that allows to control a situation on remote points. Thus, with implementation of this system in the company could refuse duplicative team of shift engineers and cut number of the permanent servicing IT personnel by half. As in each timepoint in queues there are hundreds and even thousands of files, the convenient and evident tool allowing to estimate dynamics of their processing allowed to reduce many times time necessary for search and elimination of a problem.

The idea of separation of ERP and reporting system allowed "unload" the ERP system and gave new functional and analytical opportunities. Now in it about 100 users at the central office and 16 regional directorates work (not so much in scales of such business). The centralized structure saves the company from need to keep in branches and shops of specialists and technical means, partially support functions in regions can be transferred on outsourcing.

The IT department for the last 4.5 years underwent significant changes. Before its structure mainly formed from among system administrators, now in it there were much more analysts — IT specialists who apply and support means for the business analysis. Now the IT service totals about 55 people at the central office and the distribution center.

Actually system maintenance is performed by about 30 people (engineers, system administrators, database managers), and about 30 — 40% of IT department "non-core" specialists — people who provide delivery of information (timely data acquisition from shops and receipt it in a system) make, carrying out inventories (create requests and will organize process), operators (input and document handling, work with masterdanny).

Prospects

In the long term in the company expect further development of the solution using all means which Microsoft can offer. Regarding the analysis of a status of IT infrastructure, it is necessary to master new specialized tools which in a graphic type display a status of the Microsoft SQL Server, the current performance measures. Data on the status of a specific task (setting in queue on processing, the planned processing term, completion of processing, an error) are supposed to be published on a corporate portal that the interested users could trace a status of certain transactions.

Regarding optimization of a goods turnover it is going to automate status monitoring of stocks in shops on compliance to a commodity matrix (standards of stocks). The analysis will allow to control how planned and actual values deviate from each other on separate shops, brands, positions. The descending analysis will allow to make the assumptions of the reasons of such deviations, thereby increasing the speed of response to different changes and market fluctuations.

Nikolay Zaytsev, Head of Department of IT of L'etual company: "Are sure of our company that the development strategy IT should be obligatory for business focused. We hope not only that new data formats will be apprehended by business users, but also on the fact that from business new interesting challenges and offers will appear soon. The IT in modern retail trade is the not supporting process and operating. It is possible to tell that success of our company for 50% depends on commercial department, and for 50% of the implemented opportunities of information technologies. Without having IT support, to business, especially on the scale of "L'etual', it is impossible to function. We try to anticipate requirements".