MDMS is an add-on solution for Microsoft Dynamics NAV powered by IT.integro and certified by Microsoft. It supports data synchronization and standardization in multi-subsidiary companies. In most cases, MDMS is incorporated into the Global Template that is used as an extension of the standard Microsoft Dynamics NAV functionality designed to fulfill the needs of multiple subsidiaries operating within a single corporate group. Such an extension is deployed in the subsidiaries in the course of a global roll-out of the system.
Many multi-subsidiary companies face daily problems with data structure and communication. Their subsidiaries use their own databases and different systems, which makes it impossible to communicate effectively. This has business drawbacks in many operational areas. The lack of standardized data management triggers problems with monitoring subsidiary performance, as well as applying uniform pricing and distribution policies. Processes become more and more inefficient. In order to eliminate such processes, IT.integro has developed Master Data Management System (MDMS).
Organizations whose subsidiaries work on different databases face typical difficulties:
These obstacles cannot be avoided as long as subsidiaries manage their data independently. The simplest solution is to create transparent processes of information exchange and management. MDMS is a tool facilitating this process.
The central database is developed to create and manage cards of:
Within the solution, subsidiaries do not create additional cards locally. This option is blocked. Cards can only be created in a central database, which is accessible to all subsidiaries. Data stored in a central database is replicated to subsidiaries. Data editing is only possible in a central database, which ensures its full unification. Some data which is not set up centrally is managed locally in each company separately. The scope of replication as well as editing rights are fully customizable.
There are several ways of distributing data:
Via e-mail – sent to companies which do not work with any ERP system or to single individuals (such as local dealers, representatives). The system generates Excel worksheets containing lists of items that have been modified. Files are sent via e-mail and information included there is entered manually into local systems.
The master data management process is based on the synchronization of values in selected tables between different companies in a system. However, taking into account the system’s architecture, it is quite clear that it is necessary to use a separate database for each subsidiary. However, the use of multiple databases makes the synchronization process more complex. However, the databases do not have to be physically separate databases: they can be set up as separate companies in Microsoft Dynamics NAV. The structure is always based on a central database connected with several local databases.
To manage data stored in these databases efficiently and enable effective data interchange, it is necessary to standardize and synchronize data. The benefits are obvious – when uniform item or customer naming and numbering or a uniform chart of accounts are used, it is easier to create e.g. accurate OLAP reports. Master data standardization is a prerequisite for efficient document exchange between organizational entities.
Obviously, it is possible to map item numbers, account numbers etc. between databases. However, it is a tedious, time-consuming and error-prone task. Therefore, if companies exchange documents, or manage intercompany purchase or sales transactions within a group, it is recommended to perform master data standardization as soon as possible.
At a certain level, all local subsidiaries develop similar policies for data consistency, but sticking to such policies is a real challenge; otherwise we would not encounter so many data inconsistencies.
Large companies usually delegate whole departments to data maintenance tasks. Their goal is to set up new cards, create technical documentation and enter data into a database. Moreover, such teams are responsible for data cleansing. This is also a significant challenge as a repository of material cards can consist of hundreds of thousands of records, which means that employees have to look through the lists, pore over Excel files over months to identify duplicates, redundancies, inconsistencies in numbering and if needed, modify invalid cards and codes. This is a huge time investment, which cannot be avoided before the system is set up for data synchronization.
With the Master Data Management System, which imposes specific standards, the data quality enhancement process is much simpler. After implementing such standards, it is easier to set up the system for data synchronization, which is the final stage of the data quality upgrade process.
When setting up customer cards, standardization should be ensured at the level of countries, cities, zip codes, customer and vendor groups. To harmonize customer data properly, other cards such as country cards should be standardized, too.
It is worth noting that after changing one document, other modifications are required. This is a cascade effect. Therefore, it is recommended to look at data setup from a global perspective. Therefore, a global chart of accounts can be used to prepare special NAV structures for generating specific views from a local level. A global chart of accounts can be easily changed into a local one, but not vice-versa.
In most cases, a common chart of accounts structure can be set up for all subsidiaries. However, it can be necessary to set up specific groups for a chart of accounts in order to meet local legal regulations. For example, in some countries, VAT does not exist. Therefore, in countries where VAT is obligatory a special group needs to be created to identify accounts as VAT accounts. This group will be marked as a local one. This means that a company chart of accounts may include both global and local data. No matter whether local or global, all data is managed from one central database.
For this reason, such accounts are also created in a central database. Generally, this issue can be approached in two different ways: a global chart of accounts can include all accounts used or it can comprise only globally managed accounts. In the first case, it is easier to monitor all accounts used within the company and use a new local account created for a specific subsidiary to other local subsidiaries if needed. In the latter case, the headquarters do not intervene in subsidiaries’ policies on local accounts and only global accounts are replicated into the central database.
This means that subsidiaries add, post and analyze local accounts only in a local database and when such data is retrieved for global analysis, only the summary of the local chart of accounts is analyzed. It is the customer who decides which of the two approaches is the most accurate solution for their company.
It is also possible for a multi-subsidiary company to have all accounts added to its global chart of accounts in order to gain insight into the needs of local subsidiaries. However, this solution is effective only for subsidiaries with similar requirements. If multiple subsidiaries from different regions of the world are involved, incorporating all accounts into the global chart of accounts will definitely be counterproductive.
When creating a global chart of accounts, it is worth remembering that some countries such as Slovakia, France, Portugal, Greece and Spain impose very strict requirements on companies to provide standardized financial reports, which in turn secures compliance with legal and accounting regulations. Therefore, it is crucial to communicate with local CAOs to establish the best practices for the global chart of accounts, to find a balance between global and local needs.
For example, the Polish Accounting Act specifies the structures of the balance sheet and P&L Account; in France, the chart of accounts is statutorily obligatory and local subsidiaries are obliged to export files into a specific format file for tax authorities. The file must contain accounts that are compliant with this schema. For one of our recent global NAV roll-out projects, we used a global chart of accounts in which a field with a local account number was added for each account – so-called Field No. 2. With this enhancement, several statutory reports that have to be issued periodically or when required by tax authorities are modified when printed based on No. 2. For the purpose of tax audits, documents are printed in the correct format as if all daily postings were done in the same way. However, such a workaround was not acceptable for all CAOs in local subsidiaries.
At this point, it is worth mentioning that in group projects there might be a clash between requirements of local subsidiaries and headquarters, which makes them put pressure upon their implementation partner. The headquarters would usually advocate a standardized chart of accounts, which ensures benefits such as standardized reporting and the elimination of mapping and processing of local reports.
The tool used for master data management is only a part of the whole process which leads towards implementation and the maintenance of data consistency. Before the tool is used, it is necessary to adopt policy for data numbering and cleansing. After the implementation of Master Data Management System for Microsoft Dynamics NAV is completed, a separate process has to be performed to apply all numbering changes.
It is obvious that item number is a Microsoft Dynamics NAV parameter that is used for unique item identification. The definition of rules for creating numbers which will be assigned to items is a prerequisite for item consistency and master data standardization, which has to precede the standardization process. With transparent rules implemented, employees are able to find a specific item in a database based on its number.
The process is shown in the figure below:
When building a numbering structure, it is necessary to apply a method which will ensure that all existing and potential items can be identified with numbers. For this purpose, it is recommended to divide the number into sections. However, this entails some limitations and discipline. It is worth considering whether the number of characters in the project section is sufficient to be used for all projects (taking into account all potential ones). The limit of 20 characters has been used for NAV for a long time and it is not likely to change. With this numbering method, the user can limit how many number series are used. This means that manual number assignment is active, which may result in errors. To prevent errors, a number validation procedure should be developed. Finally, consideration should be given to the possible exceptions and how these can be managed.
Item number assignment is usually managed by the technical department. In the case of international companies, the entire procedure is supervised by the technical department at the headquarters. However, if multiple items are managed in a company, individual subsidiaries may be their sole or major producers, which makes them product experts. A unified numbering standard used throughout the entire group will ensure that detailed enquiries concerning items are forwarded to a respective subsidiary based on item number tracking. Knowing an item number, e.g. its subtype, it is easier to identify in which subsidiary, the item is stored or manufactured
The possibility of creating and modifying item information in one single subsidiary within the entire group of companies solves the problem with multiple item numbers faced by many global companies. As each subsidiary creates separate item cards in their local databases, various numbers are assigned to the same item.
In the case of one of our customers, very effective item numbering procedures were already in place before implementing Master Data Management System for Microsoft Dynamics NAV. These established standards were maintained within each subsidiary even after the MDMS solution was implemented. Data cleansing was of importance, too. The group was constantly expanding by acquiring new companies, which became their subsidiaries. As a result, there were more and more units and each of them used their own item numbering standards, which differed from those ones enforced at the headquarters. After deploying MDMS, they had no choice but to adopt the same numbering standards as those used by the head office.
During project work, a special mechanism within MDMS was developed to support Microsoft Dynamics NAV users in proper item numbering. The tool includes documents pinpointing with segments of the item number corresponding to its individual properties.
Data migration including data cleansing is always a very time-consuming and disliked process. Our experience in Microsoft Dynamics NAV implementations shows that users have a very hard time when dealing with all data migration and cleansing-related tasks. However, the involvement and efforts of users are needed to perform tedious work that is hard to automate. The best way to get it done is to start data cleansing as soon as possible.
Data migration and cleansing are not error-free processes. All errors not detected during data migration or system deployment need to be corrected, which means that data cleansing continues even after the system goes live. It may also happen that the amount of data is so large that in-depth cleansing before system go-live is impossible. Therefore, it is automatically continued once the system is up and running.
No doubt, implementing a global solution in a large, well-developed company is an enormous change. This change makes employees more proactive. They usually exchange information about the roll-out, prospective problems and challenges on their own. They also come up with ideas related to new inventory numbering or master data management systems for Microsoft Dynamics NAV. As a result, when they system is implemented in subsequent subsidiaries, employees are often mentally prepared for the roll-out They may be well aware that a special method for defining item numbers is used and they know how to put it into practice. In addition, all new items have been created correctly by the subsidiaries since the introduction of specific numbering standards in the entire organization. Even though legacy items stored in these subsidiaries before data cleansing still have numbers that are inconsistent with the company’s numbering rules, a large proportion of them will be cleansed when the new system is run.
It is often the case that subsidiaries initiate the change process themselves. The reason for such a bottom-up initiative is the fact that many other group members are already using a standardized inventory numbering policy. Since other major business partners communicate with one another using the same “language”, the subsidiaries start changing their numbering policy just to streamline communication with other subsidiaries in the group.
All in all, the conclusion is that the more subsidiaries in the group work on the standardized ERP system, the greater the awareness of subsequent subsidiaries. Data cleansing is probably the most time-consuming and challenging process during each roll-out. No doubt, it takes a lot of user involvement to complete it successfully. If employees of a given subsidiary start data cleansing on their own before the roll-out, they can easily realize how important the process is. The vast majority of cleansing-related work is completed before the roll-out.
The implementation of the master data management tool we offer is one of the steps towards implementing and maintaining the consistency of data in an international organization. The tool has been designed to manage master data, and its implementation is based on the assumption that data has been standardized and cleansed. Due to data standardization, this tool eliminates inconsistencies in the process of adding items into a central database or local databases. However, the purpose of the tool is not to enforce master data consistency, if this data has not been standardized and cleared before.
The problem of item renaming does not apply to all ERP systems. Changes in primary keys of database records can affect different systems in different ways. For instance, changing an item number in Microsoft Dynamics NAV (Navision) will automatically enforce changes in many other item-related areas, such as item ledger entries, document lines and production BOMs. Thus, this operation is very time-consuming and can cause database overloads. When the number of the same item is changed in multiple databases and companies working on these databases exchange transactions, it may turn out that one of these companies (transaction parties) have already gone through the renaming process, while the others are still using the “old” number.
If item cards used by a group of companies are stored in the MDMS central database, it is necessary to send relevant information to all local databases when items are renamed. This will ensure consistency when subsidiaries start to exchange items.
The number is the only parameter that uniquely identifies items in the Microsoft Dynamics NAV. If the item number was changed only in the MDMS central database, a new item will be created in the local databases while the “old” one still exists. As a result, two item cards would be maintained in the local databases with different numbers related to the same product.
Choosing the right time to carry out the renaming operation is of paramount importance. The process of item renaming can be a heavy burden for the local databases and thus using Microsoft Dynamics NAV during this operation is not recommended. The procedure may be hindered if subsidiaries are located on different continents – it is more difficult to choose the right time to initiate the procedure. Still, our experience shows that this approach works best even for such scenarios.
To avoid the problem discussed above, relevant information is sent to local databases immediately after the renaming of the item in the MDMS central database. The “old” item number is blocked throughout business hours and then at night, inventory renaming takes place. The item can be unblocked again, however. The best method for inventory renaming is implementing the change in the MDMS central database first, and then sending the relevant information to all local databases overnight.
Master Data Management System plays an important role in projects that IT.integro delivers internationally as a tool which supplements the standard functionality of Microsoft Dynamics NAV in the scope of master data synchronization.
Generally, Microsoft Dynamics NAV used by a multi-subsidiary company is installed on multiple databases (local databases), within which many companies can be created. NAV Group Projects Methodology supports the implementation of a uniform setup and data standard in all databases. With this setup, financial reporting and consolidation (OLAP cube), which is based on data retrieved from local Microsoft Dynamics NAV instances, is much easier. In order to maintain data consistency in this architecture, a central database is required, within which data cards (such as item data, customer cards, prices lists, production BOMs) will be created and modified for standardization purposes. The Master Data Management System architecture shown above plays a critical role, because it supports the process of creating, modifying master data and subscribing it to databases.
We are often asked how data synchronization is performed if such an architecture is implemented. In this case, the transportation layer tool we recommend ensures many benefits. This transportation layer is based on files stored in a specific location in a network folder. As files are used instead of Web services, the physical location of the subscriber’s database is of no importance. Access to a central database is based on the permissions for accessing the network location. For more information, please see the video on Master Data Management System. The video describes one of the main solution concepts - Receiver.
Due to the insufficient quality of Internet connections between remote locations, it is generally not possible to run all Microsoft Dynamics NAV instances on one server. Therefore, for such locations as Brazil, India, China or Saudi Arabia, specific servers are deployed. Obviously, only one central database should be used to ensure that it is a “reliable source of master data within the group”; for this reason such a database is deployed only on one server. The Strategy Phase of the NAV Group Projects Methodology provides answers to many questions on the group project by specifying, for example, where the central database should be located. Local databases can be subscribed to the central database within the same server or from other servers as shown in the diagram below.
There is a tendency to treat the Master Data Management System module as a magic wand which is used to sort out inconsistent data stored in various Microsoft Dynamics NAV companies and databases and to prevent inconsistency drawbacks. However, this approach results from misconception about how the module works. The module only enables the synchronization of data that was previously cleansed and standardized (as described above). It is just an element in the change process which is initiated when an organization embarks on data restructuring and management. Some customers, however, go beyond the objective of data synchronization, intending to implement complete master data management. For such customers, IT.integro recommends a separate project. The most important steps of such a project involve:
In global companies it is possible to effectively reduce slow-moving inventory in multiple warehouses. Slow-moving inventory in one warehouse may be required in other subsidiary. If a group of companies starts collecting data about those products, there is a good chance of stock reduction in their global quantity.
Having access to reports on slow-moving inventory, Subsidiary D purchases the required item from Subsidiary B, which enables stock reduction from the group perspective.
When a Subsidiary D needs a given item, as shown in the figure below, it may turn out that the item is available in the warehouses of another subsidiary (in this case Subsidiary B). If the group of companies uses Master Data Management System for ERP solution, it is possible to create in the central database a special list of slow-moving items in the warehouses, including information about their quantities.
Obviously, in companies in which this solution was implemented, it was possible to ignore the message and purchase the item, even though it was in a slow-moving inventory in another warehouse. However, the strategy of most companies – stock reduction – remains the same.
The process is not fully automatic and there must one employee at the headquarters supervising the whole procedure. The problem is that it is hard to adopt one single standard, according to which ERP system Microsoft Dynamics NAV could automatically classify slow-moving inventory (e.g. based on how long they linger in the warehouse). Some items may be needed in a given subsidiary, even though they had been stored in its warehouse for a long time. The person managing the process at the headquarters must monitor the inventory in individual subsidiaries on a regular basis and determine which items should be added to the list of slow-moving stock. For example, headquarters employee selected 10 slow-moving items that, in his opinion, should be placed on the list. After consultation with the subsidiary, it transpired that 2 items were still needed by sellers and 3 other items would probably be sold soon. Ultimately, only 5 items could be added to the list.
Once the slow-moving inventory is complete, a report in MS Excel is created and placed in the central database. It is then forwarded to all subsidiaries, for example, on a monthly basis. The report may be useful when a subsidiary places an order for a given item. If it is found in the report, the ERP system (in this case Microsoft Dynamics NAV – Navision) will automatically display a warning message with a list of other locations in which the item is available. The message appears because every time a purchase order is entered, the ERP system (Microsoft Dynamics NAV) checks whether the ordered item can be found in the report.
Store replenishment is a similar example. The system searches slow moving inventory and suggests moving them to subsidiaries which need them. At this point, it is worth mentioning\a company which faced a problem before implementing the solution for managing inventory in multiple warehouses. The company managed to negotiate with the vendor the possibility of returning the products lingering in the store. However, after the first return of the product, the vendor inquired why some of the stores return the item while others order the same item.
Utilizes the MDMS system, provided by IT.Integro, to facilitate central control of all finance master data, customer & vendor master data, system settings, and items related master data. We have used this system since June 2017. This system has helped Kleen-Tex to control our master data and to keep it consistent between the databases. Due to the high volume of intercompany transactions we have, this his helped a lot in stream lining the documents between systems. We are currently testing the new version of MDMS that will give us additional functionality, such as direct SQL to SQL replication, possibility to delete in efficient way, and other features. We are happy to comment on those features after we have time to test them and use them in a productive setting.
I must say the design that went into this addon is extremely clean and one of the best I’ve seen! Your team has done wonderful work here!