Problems of reference data normalization and their solutions
When operating accounting systems, users often complain about the “mess” in directories: there are too many elements, they are duplicated, it is unclear which element to use, some of them are not used at all. At the same time, users cannot answer the question of who created these elements and why. If there is a need to reflect, for example, a new fact of economic activity, the user often “follows the path of least resistance” – he creates a new directory element. Despite the fact that the element was already registered for these purposes earlier, the user does not know about its presence or does not want to deal with the old “legacy” and the problem is becoming more and more aggravated.
My name is Alexey Byrko, I am a consultant-expert in the 1C department of KORUS Consulting and in my work I often encounter the need to normalize master data on projects. I will share my experience, why this happens and what to do about it.
Why is NSI normalization needed?
The lack of normalized master data leads not only to an increase in the volume of reference books and the database, but also to a decrease in the speed of processing and filing of documents, an increase in the number of errors (the first thing that comes to hand is chosen) and, as a consequence, to distortion of accounting data and reporting. For example, if counterparties are duplicated and one buyer is selected in a shipment transaction, and the advance is allocated to another, then the debt cannot “collapse” (the advance is offset), the balance is distorted (assets and liabilities increase by the amount of the required offset). If the item is duplicated, this can lead not only to negative balances (income for one item and expense for another), but also to a distortion of the cost of sales of inventories. With any method of estimating write-offs (average or FIFO), the cost of the expense is calculated according to the item. If there are two item items instead of one, this may lead to a distortion in the calculation of the average cost or the selection of costs for the wrong batch.
Why do duplicate master data appear?
Duplicates of master data elements may appear due to problems with the organization, when the company does not have regulations and someone responsible for maintaining the directory or there are no regulations on the use of elements. Typically, such regulations contain a target classifier with a description of situations for selecting the corresponding element. The second reason for the appearance of duplicates is the unsorted “legacy” in terms of master data, for example, when several databases are merged into one without unifying the directories. The third reason is the lack of correct mechanisms for synchronizing directories and/or compliance with the “one entry point” rule when working with directories in several information databases in a circuit.
What to do if you encounter such a problem
It's never too late to normalize NSI! Moreover, both in an operating information system and when introducing a new one, but the normalization of each directory should be approached individually.
Let's highlight the main stages:
Determine the purpose of the directory and the order in which its elements are used by analyzing the business processes in which this directory is used.
Define unique parameters to identify a directory element. Unique identification parameters are determined based on the analysis of the target requisite composition of the directory element. To determine identifiers, attributes/sets of attributes are selected whose values uniquely identify a single element. For example, unique parameters can be code, article number, etc.
Determine the required set of target directory elements and create a target classifier. There can be several approaches to compiling a target classifier. So, there should be enough elements in the directory:
To generate management and regulated reporting used at the enterprise (including taking into account its optimization and development). This requires an analysis of target reporting and its indicators from the point of view of the economic meaning of the indicator and the method of generating the indicator. During the analysis of target forms, a target classifier must be built, the elements of which should be sufficient, but not redundant. If a reporting indicator can be obtained by selecting a combination of several analytics, then it is not advisable to combine two in one classifier (if there is a second one). For example, reporting requires detailed sales data (by Product Type) at different VAT rates. To solve this problem, there is no need to duplicate the elements of the Product Types classifier at different VAT rates, but to use a second VAT Rates classifier in parallel; to reflect each sales transaction, use the values from two classifiers simultaneously.
For current work, i.e. the target classifier must include the elements used, taking into account the exclusion of duplicates. For example, the classifier “Counterparties”, “Items”, etc. When constructing such a directory, you can rely on the list of elements used, excluding duplicates, by unique identifiers or by the purpose of the added element.
Create mappings of existing and target elements that can be further used to ensure comparability of historical data or, if necessary, load balances into a new information system.
Performing the following steps varies depending on the information systemwhere normalization is planned – a new system during its implementation or into an existing one.
To normalize elements in the current information system need to:
Bring the existing directory in line with the target one:
1.1. Determine the elements that remain and add new elements to the directory.
1.2. Mark for deletion or archive “unnecessary” elements, limiting their use.
Regarding the procedure for working with accounting balances as of the start date of using the new normalized directory and turnover for “old” non-normalized elements, there may be options:
2.1. Leave the remainder “as is”; in subsequent operations, close the remainder on the incorrect element and do not use the more “incorrect” element. For example, leave the balance for an “incorrect” item item as is, write off the balance in the next write-off/sale transactions, and then use only the normalized classifier.
2.2. Reclass the remainder and transfer it to the target “correct” elements.
2.3. Transfer revolutions from “incorrect” (unused) elements to target ones using mappings.
*P. 2.3 – has a limitation, depending on the volume of turnover to be transferred and the period for which the data needs to be transferred. For example, if the period for changes is closed, then the transfer of turnover may lead to an unacceptable change in data in the previous period.
To normalize directories when implementing a new information system follows:
Fill in the classifier in the new system in accordance with the target values.
Enter the balances into the new system for new elements in accordance with the mappings (clause 4).
Below I will give examples of the sequence of actions for normalizing some classifiers.
Example 1. The “Cash flow items” classifier is not unified
The directory is intended to classify the cash flows of an enterprise, both planned (budgetary, forecast, operational) and actual. It is used both in budgets, the payment calendar (planned data), and when posting the fact of outgoing payments and receipts. The unique identification parameter of such a directory can be the “Element Code”.
For determining the minimum set of elements used you should analyze the cash flow reporting indicators: managerial – “Cash Budgets”, “Payment Calendar” and others, regulated – “Cash Flow Statement”, IFRS reporting. Based on reporting indicators, create a target list of “Cash Flow Items”.
For ensuring data comparability reporting for past and new periods, make mappings.
The volume of cash flow transactions is usually significant; transactions are contained in the past, incl. periods that are “closed” for editing, so it is not practical to transfer turnover from previous periods to new target elements. It is also important to take into account that it is not the balances under “Items” that are subject to analysis, but the turnover for the period. Accordingly, leftovers are also not transferred. Therefore, it is advisable not to do anything with data for past periods in the context of the historical reference book “Cash Flow Items”.
To generate reports for a period containing both data from the previous classifier and from the new oneas well as for reporting containing the dynamics of changes in indicators for several periods, it is required to use prepared mapping.
When normalizing the directory in an existing information system it is necessary to transfer “irrelevant” elements to the archive and limit their use in new movements. When implementing a new information system, immediately enter the target classifier without “irrelevant” elements.
Example 2. Elements of the “Counterparties” classifier are duplicated
The “Counterparties” directory is intended for registering counterparties – buyers, suppliers, creditors, etc. It is used when registering purchase, sale, payment transactions, for operational planning – the formation of orders for suppliers and customers, as well as requests for the expenditure of funds and the generation of reports according to plan and actual, incl. consolidated.
The unique identification parameters of the directory can be:
INN and KPP (for Russian counterparties).
Identification number in the country of registration and country of registration (for foreign counterparties).
TIN (for individual entrepreneurs)
Full name and telephone/e-mail (for counterparties – individuals).
To define target elements identifiers for existing directory elements should be analyzed if they are populated. Based on what, determine the list of duplicated and unusable elements. If the elements are not identifiable, there are no balances/turnovers for them or were in distant periods, then it is advisable to completely exclude these elements from the target classifier. In these situations, because algorithms are formalized, automated processing can be used. If the identifiers are not filled in or filled in incorrectly and elements cannot be excluded automatically, then they must be matched manually.
Based on the result of the analysis of the existing classifier and the compiled target classifier, it is required create mappings of target elements and duplicates. In the context of this reference, mappings are applicable if there are balances/turnovers on the “extra” elements that need to be transferred to the target elements.
When normalizing a directory in an existing database Depending on each specific situation, a decision is made on:
transferring the balance of mutual settlements to the target counterparty.
Transferring turnover to the target counterparty. This is acceptable if the period is open for changes. The transfer of turnover may require a change in the counterparty both in the documents that form the turnover themselves (documents of payments, receipts, sales, etc.), and in related documents (orders, applications for payment, etc.) and reference books (contractors' agreements, bank accounts of counterparties, etc.).
Maintaining the balance unchanged until it is repaid with the previous (irrelevant) counterparties.
Placing in the archive all “extra” irrelevant elements and limiting their use in new movements, except for paying off the opening balance.
When implementing a new information system balances/turnovers, if necessary, should be immediately registered on the target elements according to the mapping.
What to do to prevent duplicates from appearing in master data again
Normalization of directories is a long process, mostly requiring manual labor in the construction of target classifiers, mappings, and alignment of the existing directory in accordance with the target classifier. In view of this, it is advisable to carry out work on normalizing directories in the existing information system constantly until cases with duplicates become obsolete. At the same time, the organizational reasons for the emergence of new duplicates should be eliminated.
Creation of regulations for the establishment of new elements. The regulations should include a definition of the approach to registering new elements and changing existing ones, namely:
defining a “master system” for entering directory elements if there are several interacting systems within the automation perimeter (defining a “single entry point” where a directory element can be “born”).
Fixation of the circle of persons responsible for registering and updating the directory.
Fixation of the mandatory requisite composition of directory elements.
Determining who is responsible for approving a directory element.
Carrying out control procedures for the presence of duplicates when introducing new elements.
Minimum set of functions that can be automated wherein:
granting only individual users the rights to register/change elements.
Determination of required details, first of all, identification parameters.
Checking the correctness of the format of the entered data when registering new elements. For example, checks for compliance with the format of TIN, KPP, etc.
Checking the completion of an element using general classifiers when registering new elements. For example, using the service for checking the counterparty according to the Federal Tax Service, the All-Russian Classifier of Banks, the Address Classifier, etc.
Conducting a search among existing (non-archived) elements by identifiers when registering a new element. So that if existing elements are detected, not to create new ones, but to offer to use existing ones.
Prohibition of creating a directory element in all systems except the master system.
In the “1C: Holding Management” configuration, the “Master Data Management” subsystem, in addition to the described functions, allows you to use “Requests for changes to master data”. If the user needs to create a new directory element, he registers it in the system, taking into account system checks and controls. The element is registered as “not active”. Based on the created element, the user creates an “Application for changing master data” and sends it for approval. Approval is possible both using the approval route with parallel/sequential approval by several users with transition according to conditions, etc., and without, when the “Application” goes directly to a single approver. After the “Application” is approved, the new directory element becomes “active”. Changes to a previously created and approved element are made only through the “Application”, and not directly in the directory. In this case, the information is updated in the directory element only after the “Application” has been approved.
Creation of regulations for the use of existing elements. The regulations must include a description of the rules for selecting specific elements in certain business transactions. To simplify the work of users, you can systematically limit the selection of directory elements in documents. For example, only by foreign counterparties – in export and import transactions, cash flow items with the type of transaction corresponding to the document transaction, for “Requests for spending funds”, “Write-offs of non-cash funds”, “Cash outgoing orders”, etc. – this will narrow the list of choices, simplify the user’s work, reduce the number of errors and time for creating a document.
From my own experience, I can say that it is advisable to “treat” the cause: take measures so that the reference data are not duplicated and remain as standard as possible. Well, if you missed something, duplicates still showed up, you need to correct it… and the sooner the better.
