Migration from one ERP system to another

Very often, the company’s transition to a new ERP system does not stop the financial costs of the purchase and completion, but the need to migrate from the old system. They have hundreds or even thousands of users who carry out their business processes in one program, and somehow they need to be transferred all to another without any stop for the business.

In recent years, we have come this way several dozen times, and we have already developed the least painful way to do this.

Difficulties

The day when Bison honored your village with his presence, became for you the most important thing in life. But for me it was Tuesday.

- Bayson, Street Fighter

The transition process is often complicated by the fact that many especially impressionable employees of the customer are in a state of panic for the first time, since for them this is most likely the first migration in their career. For us, this is an ordinary event, which, as a rule, takes place according to the same scenario.

When any person exchanges one product for another, the first thing he notices is that he was taken from him and does not yet realize what he received . Therefore, often the first reaction is: “what nonsense you slipped me here - do as it was.” In practice, we had a situation where one user complained that he was not comfortable with the new action, which was done in the old system in five transitions between forms. But he had it honed to such an automatism that for a person it really was at first faster to do it than to do two new, but much simpler actions.

Another difficulty is that during the transition, customers like to change business processes to their vision. This should be treated with great care, as the current “as is” processes are guaranteed to work. The processes embedded in the product, as a rule, also work, as they are used by other customers. But new inventions may in theory look beautiful, but in practice they will run into some trifle, because of which the whole process can stall.

Scenarios

In practice, there are two main scenarios for moving from one system to another:

Momentary

The date of the transition to the new system is determined. All functions are developed for unloading data from the old system and loading it into a new one. On day X, migration starts at night, and in the morning all users with all business processes begin to work in the new system in a new way.

Everything is beautiful on paper. But in practice, there are at least four categories of problems:

Training . At the time of transition, as a rule, many trained users completely forget what they were taught. They begin to poke everything, and as a result they stop their work with the words “nothing works”. This problem can be solved with the help of first-line support staff who can help at this moment. But there will be so many such requests on the first day of the transfer that it will be impossible to fulfill them with the available number of employees. Or you need to have a way to somehow quickly expand their number during the transfer, which is also quite difficult to do.
Improvements and errors . As a rule, a new ERP system brings with it new business processes (otherwise why change it). It is clear that immediately before implementation they were somehow driven away, but at the time of a real “battle check” a huge amount of nuances usually comes out, because of which often the processes cannot be executed. Accordingly, again the number of such improvements with immediate priority is growing like a snowball, and there is a sharp need for programmer resources that physically will not be able to close such a number of requests.
Data . Usually, the developers of the old system are not very eager to help with uploading data (for them this is generally a sad event of losing a client), and if they help, the quality of the upload leaves much to be desired. And inconsistencies, as a rule, are found in the first days of operation of a new system. As a result, it is required to correct the unloading, and somehow reload the data, taking into account already changed during operation.
Performance . During the testing process, it is not so easy to reproduce the completely “real” load on the system. If the basic functionality of the product is usually already tested and shows normal performance parameters, then “personal” improvements to the client may, under certain circumstances, “sit down” the server. This, in turn, also requires programmer resources for optimization.

When all four of these problems occur on the first day or week of the transition at the same time, business losses cannot be avoided. Therefore, such a scheme works effectively only on small projects. For example, when the number of users is less than a hundred and the processes are not critical.

It is clear that you can spend more resources on training and testing the system immediately before the transition, and then there will be less problems. But in life, the customer’s employees usually relate to these processes “sleeves” and rely on “maybe”. Or they’re simply mistaken, expecting that a certain process will be able to earn in a certain setting, but in fact, it would seem that minor nuances will cut everything down.

Gradual

With this approach, a horizontal (by process) or vertical (by user) division of the system into parts takes place, which are implemented in turn one after another. This allows you to spread all of the above problems in time and solve them as they become available with less power.

Further, I will describe the process of ERP system migration based on the open and free lsFusion platform for the retail chain of stores, since we specialize in this industry. But we applied the same principle to clients from other areas.

Data reading

Typically, for companies that support the old system, the news of replacing their program and losing a customer is a big blow. Some fall into an inadequate state and begin to threaten the customer with an immediate stop of support. Naturally, this never works, and can never reverse the decision. However, it is rarely possible to agree with those who support the old system that they provide tables where all the data necessary for the transition is stored.

First, you need to determine in which DBMS the necessary information is stored and how to access it. Over the past few years, we have had to deal with the following DBMSs with which old systems worked:

Access Since the platform is written in Java, it was possible to dig out an ancient library that knew how to connect to mdb (though only for reading). It worked very peculiarly, since despite the filters it read the entire table, but nevertheless it was enough. We were lucky that the customer’s IT service knew the database structure quite well and told us what and where is stored.
MS SQL The old system was the Axapta 2000s. We were set up access to the test system, where we launched the GUI and SQL Profiler. Then we took turns in directories, and looked at what requests Axapta generates. It was easy to understand from them where exactly this or that information is stored.
Visual Foxpro It was all simple, since there was a transition from our legacy systems and it was not difficult to get information. The only difficulty was to find a Java library that can work not with DBASE tables, but with Visual Foxpro format.
Oracle Transition from the supermag boxed program. We worked according to a scheme similar to the Axapta scheme: GUIs and queries in SQL Developer for the most recent queries. The base structure and interfaces there were certainly much simpler than in Axapta, so there were no big problems.
Progress / OpenEdge . Transition from the boxed Gestori program. Given that the DBMS, to put it mildly, is not at all popular, we did not find any profilers. However, there was a dump in the text files. This made it possible to simply go into the GUI of the test server and search through text files to search for data that is visible on the screen. Fortunately, on the official website of this DBMS there is an opportunity to download the JDBC driver, and make queries to the SQL database. Then it was a matter of technology.

Thanks to such approaches, I never had to turn to the developers of the old system, which significantly saved customers' expenses, since usually they simply rolled out huge amounts of money for cooperation.

Master data

It all starts with importing master data. In particular, for retail trade, imports of directories of groups of goods, goods, contractors and stores are the first to be realized. With a gradual transition, there are two approaches to importing master data:

One-time data import, and then double entry into the old and new system.
Continuous incremental updating of data from the old to the new system.

There are cases when the first approach is used, but it is too time-consuming and highly susceptible to the human factor with the probability of errors. Therefore, we always use only the second option.

Ideally, the old system could accumulate changes and somehow communicate that it needs to be reimported. But usually no one will change or modify anything in the old system. Therefore, the only working scheme that we use is as follows:

There is a request to the directory of the old system, which reads all the data from it.
The read data is compared with the current in the new system and changes are detected.
Changes are written in a single transaction to the new system.

The code on the platform for importing the product directory will look like this:

importItems ' ' { 
      

        
        
        
      

     NEWSESSION { 
      

        
        
        
      

     LOCAL file = FILE (); 
      

        
        
        
      

     //      
      

        
        
        
      

     READ 'jdbc:sqlserver://localhost;databaseName=items;User=import;Password=password@SELECT id, name FROM items' TO file; 
      

        
        
        
      

     
      

        
        
        
      

     LOCAL id = STRING [ 20 ] ( INTEGER ); 
      

        
        
        
      

     LOCAL name = ISTRING [ 100 ] ( INTEGER ); 
      

        
        
        
      

     //     
      

        
        
        
      

     IMPORT TABLE FROM file() TO id, name; 
      

        
        
        
      

     
      

        
        
        
      

     //   
      

        
        
        
      

     FOR id( INTEGER i) AND NOT item(id(i)) NEW b = Item DO { 
      

        
        
        
      

     id(b) <- id(i); 
      

        
        
        
      

     } 
      

        
        
        
      

     
      

        
        
        
      

     //   
      

        
        
        
      

     FOR id(Item b) = id( INTEGER i) DO { 
      

        
        
        
      

     name(b) <- name(i); 
      

        
        
        
      

     } 
      

        
        
        
      

     
      

        
        
        
      

     //  ,     
      

        
        
        
      

     DELETE Item b WHERE b IS Item AND NOT [ GROUP SUM 1 BY id( INTEGER i)](id(b)); 
      

        
        
        
      

     
      

        
        
        
      

     APPLY ; 
      

        
        
        
      

     } 
      

        
        
        
      

     }

This will work as follows:

READ considers the entire directory with the code and name in memory
IMPORT will write all data to local properties (namely, temporary tables in the database)
The first cycle will create all the goods that are not yet in the new database (search by product code). Despite the fact that FOR is written there, it compiles into a single database query that will only work with temporary tables.
The second cycle will update the fields for all products (including newly created ones). In this case, only the changed values will be written to temporary tables.
The third action DELETE detects all the goods that are in the new database, but not in the read data and will delete them.
APPLY will start the transaction and write all the changes to the database based on the temporary tables generated by the previous commands.

In fact, this action at the time of launch completely synchronizes the old and the new directory. It does not store any incremental information, so it can be restarted at any time, even if the previous exchange was completed with errors.

Since all actions are compiled into SQL queries without any iteration, this is all done fairly quickly and safely. Typically, exchanging a directory of goods of 100-200 thousand records took a couple of minutes. At the same time, since PostgreSQL is versioned, no blocking of user work occurs at this time.

In the same way, suppliers' prices, assortment matrices, order schedules and other information necessary for work are constantly synchronized. Here, however, a problem may arise that the domain logic in the old system does not coincide with the new domain logic. For example, in our system we have a history of matrix versions and the concept of product levels inside a matrix. If in the old system the current assortment of stores is stored flat as a boolean for the store and goods, then one matrix is created for each store with exactly one level.

Most often, we enable the action to synchronize all directories with the old system once an hour, while providing the user with the opportunity to start the exchange manually.

Process

In retail, we usually divide the translation process vertically between stores and simultaneously horizontally from store processes to office processes.

The first step is to choose one store where the transfer to the new system will begin. Imports current balances and store prices from the old system in the form of incoming documents with the corresponding quantities and prices:

Source code example

importInventory ' ' { 
      

        
        
        
      

     NEWSESSION { 
      

        
        
        
      

     LOCAL file = FILE (); 
      

        
        
        
      

     //      
      

        
        
        
      

     READ 'jdbc:sqlserver://localhost;databaseName=items;User=import;Password=password@SELECT idSku, quantity, idDoc, dateDoc, idDetail FROM inventory' TO file; 
      

        
        
        
      

     
      

        
        
        
      

     LOCAL idSku = STRING [ 20 ] ( INTEGER ); 
      

        
        
        
      

     LOCAL quantity = NUMERIC [ 16 , 3 ] ( INTEGER ); 
      

        
        
        
      

     //  idDoc, dateDoc  idDetail -           
      

        
        
        
      

     //        ,        
      

        
        
        
      

     LOCAL idDoc = STRING [ 20 ] ( INTEGER ); 
      

        
        
        
      

     LOCAL dateDoc = DATE ( INTEGER ); 
      

        
        
        
      

     LOCAL idDetail = STRING [ 20 ] ( INTEGER ); 
      

        
        
        
      

     //     
      

        
        
        
      

     IMPORT TABLE FROM file() TO idSku, quantity, idDoc, dateDoc, idDetail; 
      

        
        
        
      

     
      

        
        
        
      

     //     
      

        
        
        
      

     FOR idDoc( INTEGER i) AND NOT receipt(idDoc(i)) NEW r = Receipt DO { 
      

        
        
        
      

     id(r) <- idDoc(i); 
      

        
        
        
      

     } 
      

        
        
        
      

     
      

        
        
        
      

     //     
      

        
        
        
      

     FOR id(Receipt r) = idDoc( INTEGER i) DO { 
      

        
        
        
      

     date(r) <- dateDoc(i); 
      

        
        
        
      

     } 
      

        
        
        
      

     
      

        
        
        
      

     //  ,    
      

        
        
        
      

     FOR idDetail( INTEGER i) AND NOT receiptDetail(idDetail(i)) NEW d = ReceiptDetail DO { 
      

        
        
        
      

     id(d) <- idDetail(i); 
      

        
        
        
      

     } 
      

        
        
        
      

     
      

        
        
        
      

     
      

        
        
        
      

     //     
      

        
        
        
      

     FOR id(ReceiptDetail d) = idDetail( INTEGER i) DO { 
      

        
        
        
      

     receipt(d) <- receipt(idDoc(i)); 
      

        
        
        
      

     quantity(d) <- quantity(i); 
      

        
        
        
      

     } 
      

        
        
        
      

     
      

        
        
        
      

     //     ,       
      

        
        
        
      

     DELETE Receipt r WHERE id(r) AND NOT [ GROUP SUM 1 BY idDoc( INTEGER i)](id(r)); 
      

        
        
        
      

     DELETE ReceiptDetail d WHERE id(d) AND NOT [ GROUP SUM 1 BY idDetail( INTEGER i)](id(d)); 
      

        
        
        
      

     
      

        
        
        
      

     //         
      

        
        
        
      

     APPLY ; 
      

        
        
        
      

     } 
      

        
        
        
      

     }

A few weeks before the start of the store, import of the implementation from POS systems is connected. This is necessary so that there are historical data for the formation of orders in the first days of work.

Very often, the process of own production is transferred a little later than the main processes. In this case, the unloading into the old system of documents on the movement of raw materials and the import of finished products from there are implemented.

On the day of the transfer, import starts at night, and then both the customer support staff of the customer and our first support line are sent to the store. They help users get started in the new system without even having to take pre-training. In this case, usually for some time, changing documents in the old system does not close, as people are mistaken, and they need the ability to correct errors. After a couple of weeks, the balance is re-imported, and then a ban on changes in the old system is already in place.

After all the problems described above are identified and resolved at the first store, after about a couple of weeks another 2-3 stores are transferred. Again an iteration of identifying and fixing problems. And then all the others are translated very quickly, depending on the resources of the customer support service of the client. All this time, master data continues to be entered in the old system so that stores can function on the old system.

Then, when all stores are transferred, then either gradually or all of them immediately turn off imports from the old system, and users begin to enter master data into the new system.

Summary information from the old and new systems is obtained either in the accounting department or in the BI system, where both systems are uploaded at the same time. There you can get summary reports for the interval, which includes the operating time in the old and new systems.

Conclusion

Despite the apparent complexity of the transition from one system to another, having walked this path several dozen times, you understand that with a debugged scheme, everything is actually not so scary. More problems arise with the fact that most of the client’s employees are quite conservative and, at the slightest opportunity, demand to do “as it was”. And here it is very important that the customer has a person with sufficient authority who can compare old and new processes, determine which ones are more optimal and be able to “break” employees. Or modify the process according to the “as it was” scheme, if it turns out the other way around.

All Articles