e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) PRAGMATIC AND SCALABLE DEDUPLICATION TACTICS USING HEURISTIC PRUNING ALGORITHM Kari Venkatram Research Scholar, School of Computer Science and Engineering, Vellore Institute of Technology, Vellore – 632 014, Tamil Nadu, India, venkat_kari@yahoo.com Geetha Mary A Associate Professor, School of Computer Science and Engineering, Vellore Institute of Technology, Vellore – 632 014, Tamil Nadu, India, geethamary.a@gmail.com *Corresponding Author: Geetha Mary A Fax: (+91) 416-224-3092 Abstract Master data acquired from many legacy systems and heterogeneous sources, hence it’s possible to get duplicated and it influences the quality of the master data services such as all CRUD operations & other business services. The data deduplication process is one of the most important requirements for building comprehensive master data management (MDM). There are several custom and hybrid models available for data quality improvement, but can’t resolve all the quality issues such as data duplication. This article portraits, how duplication happens, and what are the measures to prevent duplicate data. The proposed solution - pragmatic and scalable approach to identify and eliminate the duplicate data from the repository. The solution is based on a heuristic pruning algorithm with a cosine similarity measure for eliminating data duplication. This algorithm yields better time O (𝟐𝒎𝒏 𝒎𝟐 ) complexity compared to the normal cosine algorithm. This approach is built with a two-step process, the first step is the deduplication process of existing data, and the second step is to prevent the duplicate data being entered into the real-time system. It is been observed with the proposed solution that, the quality of data is improved close to 90% as part of the deduplication process. Keywords- Deduplication, cosine similarity, heuristic pruning algorithm, data quality, master data management 1. Introduction to data quality in Master Data Master Data Management (MDM) is a comprehensive framework to create a master file with records of master items of an enterprise as a common point of reference. This file describes entities of business such as customers, distributors, products, locations, partners and employees, etc., MDM is critical for an enterprise/ organization to link all the referential data with each other into one file, called a master item[1]. This framework enables streamlining of data sharing effectively, uniformly, and consistently among master items [2] within MDM. The data quality in MDM is important to make the right decisions and achieve a competitive advantage as compared to the competitors. Poor quality of data will result in bad decisions [1]. The data quality issues arise from the data being ‘silos’ due to factors such as consistency, accuracy, completeness, timeliness, and relevancy, etc., [2]. If data quality is poor then it is practically useless and sometimes even dangerous. All the departments will suffer to produce accurate reports and predictions due to poor quality data. For instance, the Sales and Marketing department may lead to inefficient conversion of sales, the customer services department may maintain poor customer relationships and may churn out customers and damage the reputation of the organization. Quality issues lead to excessive usage of personnel and resources for inflexible or unresponsive customers. Business operations department will suffer with overloading data clean up and distraction of the compliance and associated challenges. This article aims to build the deduplication strategy for master data (depicted the process with Customer data) efficiently. Lack of an explicit data deduplication strategy in Customer Relationship Management (CRM) systems resulting in duplicate of customer data. There are many reasons for data being duplicated such as human errors by not searching for customer data before adding customer records (Name, location, accounts, deals, or leads), alerts DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 731 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) by the system are ignored by the person who is trying to insert data. The tendency is more for duplication of customer data due to digitization of businesses and as it will be using various devices to access their data. Another source of duplication is, data imported from various systems using different tools. The tool may not be efficient to eliminate duplicate records. Though the duplicate records are flagged as "potentially duplicate” while importing the data, users tend to ignore the alert and create a new record. Data integration among the ecosystem such as third party data providers, external data sources - website registration forms, partner portals, message brokers, or other applications may not validate the duplication of records against CRM. At times, external data may not be able to update CRM; this can cause new records in CRM even though it is identified as duplicate. Due to clerical issues, application limitations and issues in software tend to create duplicate records in the system. Higher quality data can be achieved with lower duplicates in the system and similarly higher the duplicates the lower data quality, also credibility of the reports will be less and business decisions will be affected due to poor quality. Duplicates above 15 percent of data is a very serious concern and can threaten the business. 2. Challenges and Scenarios for data duplication At times duplication of data may seem not important than the rest of other issues. Duplicate data can cause very serious consequences such as erroneous reports resulting in bad decisions and very expensive steps to correct it. Usually, data duplication happens in business across different departments due to the use of different software applications. For instance, customer and product data are often entered repeatedly into accounting, inventory, product management, customer relationship management software applications. The same data can be entered into different business applications of the enterprise causing duplicate information of the same customer. Invoicing and dispatch of products can go wrong with duplicate records. This can be highly expensive. The financial analysis also can go wrong. For example, promotional mailers may be sent multiple times to the same customer increasing postage/printing expenses, which can be very much avoided. Unnecessary data will be stored in systems. As per Gartner’s impact analysis on bad data management close to 25% revenue dip[3] happening due to duplicate data. A single source of truth (SSOT) is not possible due to data duplication. The profiling of the customers and analysing customer behaviour is not accurate with duplicate data. For instance, no transactions of one particular customer are considered then the ability to market the product/services is reduced and consequently, the customer care team’s service will be inefficient. Different data duplication scenarios are discussed in the subsequent sections. Data duplication scenarios: This section describes major data duplication scenarios with an example of customer data. The scenarios covered in this section are approximately 90% of total customer data duplication. If these scenarios are addressed, the quality of the customer data will improve close to 90% and produce good results. Name standardization: Customer data quality gets into a serious issue due to non- standardization of customer names. Nearly 5% of data duplication of customer names can happen if the system is not standardized. Customer names are accepted with all the alphanumeric, junk, special characters, etc. leading to confusion and creation of duplicate records. As shown in Table 1, a customer name Five Star technologies can be represented as many means. This name should be standardized by avoiding special characters and numbers in the attribute name. # 1 2 3 4 Customer Name Five Star Technologies 5 Star Technologies 5* Technologies ***** Technologies Table 1: Customer Names without standardization Master data ecosystem can be vulnerable to get duplicated due to the usage of certain abbreviations or with the usage of jumbled names. Here is the example of an organization name “Tech Mahindra Limited” in Table 2 are represented as given below. # 1 2 3 4 Customer Name Tech Mahindra Ltd Mahindra Tech Ltd TechM Ltd Tech Mahindra Limited Table 2: Customer Names with jumbling words and abbreviations DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 732 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) Names with additional spaces or missing spaces are also a very common scenario, which leads to duplication. As shown in Table 3, the company name “Tech Mahindra Ltd” is depicted differently with leading or trailing spaces. 1 2 3 4 # Customer Name Tech Mahindra Ltd Tech Mahindra Ltd Tech Mahindra Ltd Tech Mahindra Ltd Table 3: Customer name with some unwanted spaces The system should eliminate the extra spaces between the words and should be able to manage the jumbling of the words. Keying in customer addresses differently for different instances of the same customer also can lead to duplication. As depicted in Table 4, customer addresses are spread across four attributes differently. # 1 2 3 4 5 Name AT&T Istel AT&T Istel AT&T Istel AT&T Istel AT&T Istel Add1 227 227 Marsh wall 227 Marsh wall, Sovereign house 227 Sovereign house 227 Marsh wall Add2 Marsh wall Sovereign House, Polar Polar Marsh wall Sovereign house Add3 Sovereign house Add4 Polar Polar Polar Table 4: Customer address with multiple data fields These types of issues arise due to not following standard conventions across departments/applications of an organization. If geographic elements such as city, county, state, province, zip code, etc., are not standardized, lead to duplication of customer data. Here is an example is shown in Table 5.. Zimbabwe has no postal code system in place and hence the postal code is not mandatory required. However, some of the systems mandate the postal code. Users may enter some junk data, as it is mandatory in their application leads to duplicate records. # 1 2 3 Name The Wattle Company The Wattle Company The Wattle Company City Mutare Mutare Mutare State/ Province Manicaland Manicaland Manicaland Country Zimbabwe Zimbabwe Zimbabwe Zip Code 000263 263000 Table 5: Customer data without postal codes Some countries like Singapore may not have a state or province in their address. As shown in Table 6, sometimes users tend to fill some junk value or some details of other fields in the state/ province column. This will lead to the creation of a duplicate record. # 1 2 3 Name Arinso Singapore Pte Ltd Arinso Singapore Pte Ltd Arinso Singapore Pte Ltd City Singapore Singapore Singapore County Clemenceau avenue State/ Province UE Square Country Singapore Singapore Singapore Zip Code 239920 239920 239920 Table 6: Customer data without the State of Province Wrong information such as agent information or user-specific information is fed into some irrelevant attribute. This will cause wrong/duplicate records as shown in table 7. # 1 2 3 Customer Name A T &T ISTEL A T &T ISTEL A T &T ISTEL Address1 227 PO 123458382878 123458382878 Address2 MARSH WALL 227 MARSH WALL 227 MARSH WALL Address3 SOVEREIGN HOUSE SOVEREIGN HOUSE SOVEREIGN HOUSE Address4 POLAR POLAR POLAR Table 7: Customer data with some additional information in address fields There is another scenario where users can have multiple addresses with the same physical location. For example, one customer can have two or three sites each site in a different building in the same location. Tech Mahindra might have one physical location in Bahadurpally, Hyderabad and it might have two different buildings like IT-1 and IT-2. From each building, there can be different orders for goods and their delivery addresses are different. However, the customer remains the same. This is another scenario, which can create duplicate records. DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 733 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) One of the usual scenarios is the usage of phonetic names. This can create confusion while recording customer data and prone to duplication. Example - “Reliance Trends”. This can be written as “Reliance Trendz” by one user and can be written as “Reliance Trends” by another user. This will create a duplicate record. 3. Literature review and deduplication strategies Entity duplication is a very common and well-established problem in the industry especially textual corpora[4]. Suppose there are N entities such that, set of entities 𝑋 𝑥 |𝑥 ∈ 𝑅 for i from 1 to N. R is the complete set of data. Data duplication can be defined as below: Let consider the data set X, with an entity q in it, if the system tries to find its closest entity in X or closest entities (subset X′ ⊂ X) where the distance from the entity in X′ to the entity q is less than the threshold value. It approximates similarities among the entities[5] q and X′. There are several proposals by researchers for efficient similarity detections the important ones are discussed in this section. Charikar proposed an algorithm based on hash functions[6] called SimHash (SH) which uses random projections as a set of hash functions and creates an angle between two entities. This will work majorly for linearly separable data. Further, it was enhanced by Kulis et all for catering non linearly distributed data as well, this is called Kernelized Locality Sensitive Hashing[7]. Then Wang et all have enhanced it with an improvement in hashing which has its prior knowledge. This is called Semi-Supervised Hashing (SSH)[8]. Many others also proposed several studies on this through locality-sensitive hashing, spectral hashing, etc.[9]. Though there are several hashing functions for identifying text similarities, it is a unique problem to identify the similarities of business entities. These unique problems in entity similarities require a very specific customized algorithm to address such data duplication. Section 3 discussed various scenarios of how and why the customer data is duplicated and persist in databases. There are some existing solutions and strategies to deal with this. This process is generally called a deduplication process. Data deduplication is a process of eliminating duplicate data and restricting the data being duplicated. This process enables a preventive and corrective action also called data cleansing. Deduplication is used to identify duplicate records that refer to the same real-world entity and help us to merge the duplicate records through data linkages, record matching techniques[10]. In this process, all the relevant, accurate data will be identified by eradicating duplicate records. As discussed in section 3 on the various data duplication scenarios, it is critical to identify the right strategy for data deduplication. The deduplication strategies would be applied at various levels at OLTP and OLAP. The deduplication strategy should be considered on high priority, as it is a better option to reduce further duplication of the data in the ecosystem. There are several cleansing techniques/tools are available to integrate within the OLTP/source system. If applied, cleansing will happen at the source system and eliminate the duplicate data within the source system/initial layer, hence data duplication is avoided. Since the data at the source system is being cleansed, the quality of data is better. Organizations invest cleansing techniques at the source layer rather in downstream systems, as it is a better option. Source system cleansing has various benefits as downstream systems benefit from high-quality data. In case the deduplication strategy was done at the warehouse level or data lake level, the source databases or source systems will still be having duplicate records, eventually, all the downstream applications depending upon the source data/ source system will require to cleanse the data separately for better data quality. Though it is recommended to do the deduplication at the source level, there are certain challenges in implementing it at the source level, especially for high performance/real-time transactional applications. The actual challenge here is the real-time systems require an immediate response from the services where the deduplication service may not meet the required SLA, due to various business rules. In such cases, deduplication is tough at source level and it is advised to do it in the downstream systems such as data warehouses or data lakes. Deduplication processes are applied to different platforms and different domains across the enterprise areas to ensure the businesses get the best out of their data. It must be applied to SCM (Supply Chain Management), ERP (Enterprise Resource Planning), BI (Business Intelligence), and Customer Relationship Management (CRM) databases. SCM can support not only their data but also the network of supply chain business processes with reliable, practically applicable data. ERP is data-centric and normally designed around key business processes like financial management, materials management, human resources management, and inventory control management, etc. Hence, data quality plays a vital role in this area as well. BI- is the backbones of DSS (Decision Support Systems) of an enterprise. Hence, deduplication essential for achieving the goals of DSS. CRM has customer data, which is very sensitive and holds key information about customer preferences and their attributes. To build customer relationships strong and having a good partnership with customers, organizations must enhance the quality of customer data. The key to more efficient CRM is a unique view of the customer across the enterprise. DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 734 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) Above mentioned solutions and strategies are not comprehensive for the deduplication process across the enterprise, as it is applied at each business process level, hence it is not sufficient solution to address the duplication. So it is required to have a comprehensive solution to deal deduplication at a common level across the business processes. There are several proposals from researchers on the exploration of vector space model, engram, and similarity functions using the weight of the words with inverse document factory[11] (IDF). These approaches predominantly used for the deduplication of data with complex structures. There are several other approaches for evaluating the similarity scores/ranks for identifying duplicate records. The similarity of records is usually evaluated through vector space models[12] where the large fragmented text is constructed from different fields and calculate the similarity among the records using edit distance measure such as Levenshtein distance[13] (LD). The number of deletions, insertions, calculates distance, or substitutions required to transform Source String(S) into Target String (T). The distance between source and target represents the way the two strings are similar or dissimilar. In other words, shorter distance represents more similarity and vice versa. Another measure is Jaccard distance, which measures dissimilarity between source and target strings. This method is used to compare and find the proximity of the data among the data clusters[14]. Machine learning techniques are also used to identify similar records and eliminate them by Atlas systems, which performs mapping between records to integrate different data sources. Other approaches to document clustering, agglomerative hierarchical clustering, and k-means clustering to identify the similarities among the data. [15] All measures and articles, which were discussed above, are very generic solutions for identifying the similarity or dissimilarity in due course of the deduplication process. However these are not specific solutions designed for deduplication of an entity of master data, hence it is an essential need for a robust and scalable algorithm/ framework to address the entity deduplication of master data. 4. Proposed solutions for deduplication & strategies As discussed in the previous sections there are a lot of challenges in identifying duplicates in master data. For instance, a customer entity is built up with multiple text fields that are rapidly generated from different source systems and it can be achieved with text similarities/rank algorithms. The similarity coefficient represents the similarity between two documents or two strings or queries. There are few similarity coefficients such as the Dice coefficient, Jaccard coefficient, Pearson correlation coefficient, and cosine coefficient[16, 17]. The cosine similarity coefficient gives better results compared to others[16]. For attribute similarity evaluated several similarity measures below are the empirical analysis of the similarity measurement. Considered the following Levenshtein (LEV), Longest Common Subsequence (LCS), Jaro-Winkler (JWC), and Cosine Similarity (CS) in this study. Compared two entities with 6000 variations how different similarity algorithms perform concerning the accuracy, sensitivity, specificity, precision, and the false alarm was recorded. In our study, we conclude using the Cosine Similarity model for similarity calculations as it outperforms as compared to all other measures. Algorithm LEV LCS CS JWC TN 701 700 695 701 FP 1 2 7 1 FN 656 312 140 178 TP 4642 4986 5158 5020 Accuracy 0.8905 0.9477 0.9755 0.9697 Table 8: Attribute Similarity Evaluation for Entity A (TP: 5298, TN: 702) Data clustering is a commonly used technique that helps to organize the dataset by grouping them into semantically consistent buckets called clusters. It is achieved by identifying the similarities based on the similarity coefficient[18]. As depicted in Fig. 1, customer data (text data of name and address) will be fed into the system to create a hierarchal cluster. The preprocessing is an important step of the algorithm, which removes stop words, delimiters from the text data, and fed into the next phase. In this step, each word in the string – called the token will be passed through the synonyms repository. If any synonym is found, word will be replaced in preprocessing text as a token. This will continue to identify the synonyms based on the phonetics of sound using Soundex functions. The next phase is data bags, in this phase, each word in the record will be calculated and stored in data bags for further processing. The data will be stored as a table with the occurrence frequency of each word in the record. The next phase is the calculating distance between records with cosine similarity distance measure and identifies DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 735 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) the distance or score between two different records. Similar records grouped under one cluster. Duplicate records are grouped into one cluster and hence duplicates can be eliminated by liking or removed. Fig. 1. Clustering data flow As depicted in Fig. 2 - deduplication architecture, various upstream systems such as SCM, ERP, CRM, Internet content, etc. are the source systems to create customer data. All these customer records being created from various systems are pushed to a single source of truth (SSOT) called master data (Customer Master). Distributed messaging system (DMS such as Kafka) is a message broker of the ecosystem to push the data from various sources to MDM; simply DMS takes customer data from different producers and shares the messages to different consumers. Before giving it to the end consumers, the messages will be proposing a middle layerdistributed in-memory database (DIMD such as Apache Spark) help in processing the data at lightning speed. The processed data will be pushed/flown to other downstream databases or the distributed search engine (DSE such as Solr). The end-users consume the services from the downstream database or distributed search engine. All duplicate data will be eliminated in this distributed middle layer. Fig. 2. Deduplication architecture As shown in the architecture above the deduplication process happens in the distributed in-memory database (middle layer) of the architecture. Deduplication is achieved here with a two-step process. The first step is to onetime process to eliminate all the duplicates in the existing data. Fig. 3. One Time process for Data deduplication As depicted in Fig. 3, all duplicate data in the existing system will be eliminated with a one-time job. A matching algorithm based on cosine similarity measures will match the duplicate entities as described in section DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 736 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) 3. Heuristic pruning algorithm with Cosine similarity is used to cluster similar records with similarities over 90%. All the records (let say n) are loaded into Spark RDD/datastore after clustering into a set of unique groups. The unique groups (say m) will be less than or equal to n. Such clusters are used to link the related entities into one single customer hierarchy. The cosine similarity algorithm is built based on two significant terms such as Term Frequency (TF) and Inverse Document Frequency (IDF). Term Frequency also is known as TF, measures the number of times a term (word) occurs in a document. In any large document, the frequency of the terms will be much higher than what we usually think of. Hence, we need to normalize the document based on its size. The simplest way is to divide the term frequency by the total number of terms. For instance in a document, if the term “tech” term occurs ten times. The total number of terms in the document is 100. Then the normalized term frequency is 10 / 100 = 0.1. This is the way; the normalized term frequency is calculated. In some cases, normalized term frequency is calculated with log frequency weights as shown below: ∑ ɛ ⩀ 1 log 𝑡𝑓 , ) (1) Document frequency (df of term t) is the number of documents that contain term t. The inverse document frequency is used to measure document frequency across documents for the given term. To reduce the impact of common term vs rare terms across all documents, inverse document frequency can be used to normalize the impact. The logarithmically scaled inverse fraction of the documents that contain the word is obtained by dividing the total number of documents (N) by the number of documents containing the term (𝑑𝑓 ), additionally applying the logarithm to the quotient. Therefore, certain terms that occur too frequently have little power in determining the relevance, it gives a way to weigh down the effects of too frequently occurring terms. Besides, the terms that occur less in the document can be more relevant, it gives a way to weigh up the effects of less frequently occurring terms. Hence, logarithm helps in solving the problem of high and low frequent terms. So 𝑑𝑓 is an inverse measure of the informativeness of t, hence 𝑑𝑓 must be less than or equal to no of documents N. We can define the IDF (inverse document frequency) of term t as below[19] : 𝑖𝑑𝑓 = 𝑙𝑜𝑔 ( 𝑁 𝑑𝑓 ) (2) 𝑙𝑜𝑔 ( 𝑁 𝑑𝑓 ) used instead of 𝑁 𝑑𝑓 to reduce the effect of high frequency occurring terms which are not more relevant than low frequency occurring more relevant terms. This is relevant for two are more terms; however, the effect of idf on ranking for one-term queries is nothing. In this article only two documents are compared at any single point of time, hence idf has defaulted to 1. However, it is to be considered with real-time search scenarios. Tf-idf weighting: The tf-idf weight of a term is the product of its tf weight and its idf weight. To build the cosine similarity equation, it is required to solve the equation of the dot product of two vectors where it will measure the similarities between two documents. The Algorithm is defined as below: Every term in the document tf-idf is calculated. For the same words in other documents, calculate tf-idf for each word. There will be two vectors, i.e., the first vector is tf-idf weightage for the first document, and the second vector is tf-idf weightage for the next document. Now calculate the dot product of these two vectors, which is a scalar value. This scalar value is the relevancy or similarity of these two documents. The cosine similarity between two vectors (or two documents on the vector space) is a measure that calculates the cosine angle between them[20]. This metric is a measurement of orientation and not magnitude, it can be seen as a comparison between documents on a normalized space because the magnitude of each word of each document and the angle between the documents were considered of each count (tf-idf) of each document and the angle between the documents[21]. Cosine Similarity generates a metric to measure the similarity between two documents by looking at the angle instead of magnitude. If the cosine similarity for two different documents is “1” means that both the documents are in the same direction and the angle between them is 0 degrees whereas cosine similarity value is “-1”, then both the documents are in opposite direction and their angle is 90 degrees. Note that even if we had a vector pointing to a point far from another vector, they still could have a small angle and that is the central point on the use of cosine similarity, the measurement tends to ignore the higher term count on documents. Suppose we have a document with the word “sky” appearing 200 times and another document with the word “sky” appearing 50 times, the Euclidean distance between them will be higher but the angle will still be small because they are pointing to the same direction, this matters a lot while comparing the entities hence cosine similarity has been considered for this algorithm. Proposed heuristic pruning (HP) algorithm to cluster similar records into one group with low running time compared to regular cosine algorithm: Algorithm: Heuristic Pruning (HP) algorithm with the cosine similarity measure Input a set of entities with n attributes (E) DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 737 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) Output a set of entities with cluster id, overall score, attribute group-wise scores in it (𝐸 ) Comments Entity (E) is an input object with several attributes in it. Example E (𝑎 , 𝑎 , 𝑎 . . 𝑎 ) where E is an Entity may be customer 𝑎 : customer id, 𝑎 : name, 𝑎 : add1, 𝑎 : add2,.., …𝑎 : state, 𝑎 : country, 𝑎 : pin Require set of E = {𝐸 ,𝐸 , 𝐸 ..𝐸| | } 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. Start HP Algorithm {E} <- fetch entities from DB ThresholdValue <- 0.90 startRecord<-1 PartyID <-0 For each E in {E} entity <- dataList.next() If entity.a11 is not null dataList.remove() Repeat Else custData <- Concatenate all the columns (𝑎 , 𝑎 , 𝑎 . . 𝑎 ) splitCustData <- split custData compareUniqCustData <- unique words from splitCustData 𝑎 <- entity. 𝑎 𝑎 <- entity. 𝑎 entity. 𝑎 <- 1 For initial<- startRecord+1, dataList.size() nextEntity <- dataList.next() nextCustData <- Concatinate all the columns (𝑎 , 𝑎 , 𝑎 . . 𝑎 ) calculate the normalized term the frequency for all the words in compareUniqCustData vs custData fromCustVector <- Generate term frequency vector calculate the normalized term the frequency for all the words in compareUniqCustData vs nextCustData toCustVector <- Generate term frequency vector score <- calculate the dot product of fromCustVector X toCustVector If score > ThreshooldValue entity. 𝑎 ) <- 𝑎 ) entity. 𝑎 ) <- 𝑎 ) Repeat End If startRecord <- startRecord+1 End For End If End For Since this algorithm built based on the heuristic pruning algorithm, the running time of the algorithm is considerably less compared to the regular algorithm. The algorithm for next iteration prunes the records, which are already clustered, hence it reduces the number of records for next iteration onwards. Time complexity analysis of heuristic pruning (HP) algorithm can perform better than conventional algorithms. Initialization at the beginning of the algorithm can be done with a constant no of primitive operation per element and it takes O(n) times. There are two nested loops, which are controlled by start record, and the entire loop with a condition to resume the loop. The body of the outer loop would contribute the number of primitive DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 738 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) operations proportional to n and m, hence it takes O(n-m) times. The body of the inner loop is also controlled by start record which is incremented and controlled, which implies the statements in the inner loop contributes to 𝑂 n m 2 . times So total running time of the algorithm is given by the sum of all the above terms asymptotically would be: 𝑂 𝑛 𝑚 . Here n is the total number of entities and m is the total number of duplicate entities in the set of n entities. A regular algorithm running time asymptotically would be O(𝑛 ), so the running time for the proposed algorithm is improved by 2𝑚𝑛 𝑚 . After a time setup is done all the existing duplicate records are eliminated in the system; however in the online process if any new record is getting created or modified will be verified against the processed de-duplicated data with a similar algorithm to avoid duplications. Online Deduplication: As discussed earlier sections, the second step is to avoid generating duplicate entities when a new entity is being created or modified online. In this process, the same matching algorithm is used in conjunction with a search engine used for online deduplication. As depicted in Fig. 4, the source system’s user will search before creating a new or modifying the entity. If there is any entity matching the input, the search will fetch the data from the search engine based on the matching algorithm with the relevancy or score of the record. The highest matched record that scores over 90% will be returned if it is available otherwise a new record is created. Hence, it restricts the user in creating duplicate records from the online process. Fig. 4. Online Deduplication 5. Metrics and Analysis of proposed solution Experiment Settings: Master data samples used in this experiment are customer data of an industry leader. Customer data is taken such as customer name, address, and geographical elements. The data used in this experiment is the fair distribution from the entire data set, which is used for similarity function. The proposed solution is validated against the test dataset. We observed and assessed the model carefully for inferring the model quality. Evaluated the performance of the proposed solution with a good number of data with an adequate possible duplication of data within. Then ran this data with this proposed solution to identify the duplicate records based on heuristic pruning cosine similarity algorithm and observed that total records are consolidated into multiple groups/ clusters. Upon manual verification of thousands of records, we found that the clusters with cosine similarity accuracy are very high in this case and it is above 90%. Few scenarios where some of the special cases like post box numbers are given as NW-North West, SW – South West, etc., were not matched, however, these also can be addressed by improving the existing logic. As depicted in the following in Fig. 5, the customer entities are scattered across space with the original dataset and were distributed across space with lots of duplicates in it. There are 5000 entities are distributed to close to 5000 clusters. Fig. 5. Original Customer data with existing clusters DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 739 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) With the proposed HP algorithm, ran a one-time deduplication process on the original dataset and observed that quite a few records were consolidated into one group. As depicted in Fig. 6, after running the dedupe process observed that all the data points/ customers belong to one group are separable from other clusters. From this picture observed all 5000 entities are consolidated to 600 clusters. All similar entities are grouped and consolidated. Fig. 6. Customer data after running the dedupe process Data points after the deduplication process are depicted in the following Fig. 7 Observe the data points in the plot many of the data points are clustered together. It indicates that the records with high similarities are consolidated and consolidated records are duplicate records which can be eliminated or generate a hieratical entity. Fig. 7. Deduplicated data for a huge data set of customer data Fig. 8. Clustered data based on their similarity scores for 3 data sets The cluster shown in Fig. 7 seems to be cumbersome and difficult to interpret as all the data points/ entities are represented in the data space together. To have a better interpretation and understanding some entities clustered and reported in Fig. 8. Each sub figures, in Fig. 8, represents 3 sets of data points/ entities within a twodimensional space. Each set has depicted before the algorithm runs how the data is distributed, what was the actual data supposed to be clustered together and the third figure. depicts how the data is clustered. As per the Fig. 8, all three subsets are very neatly clustered the similar records as per the expectations. DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 740 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) We have evaluated the cluster’s quality with the following two measures: External measure: Compared the clustering of a set of data, which were prior or expert specified knowledge – with ground truth and found it is good in terms of clustering the data. The experiment seems to be found over 98% of data seems to be identified in the duplication process. Internal measure: Evaluated the cluster quality by using cluster dendrogram and silhouette plots. As shown in Fig. 9, the dendrogram - visual representation of the compound correlation data component clusters are formed by joining individual components. From this, it can be inferred that tightly correlated clusters are nearer the bottom of the dendrogram. This diagram is a convenient way to depict the pairwise similarities or dissimilarities among the objects. So here, the level at which branches merge and it seems to be similar within the cluster and dissimilar to other clusters. The quantitative evaluation of the quality of dendrograms is usually identified with Sum-Square Error (SSE) also called leaf node error and standard deviation of SSE. When analyzed SEE or Leaf node it is as small as close to 0.12, which means the abstraction of the quality is good. Similarly, the standard deviation of the SSE is also close to zero hence there is no low occurrence of under or over-abstraction. The other part of Fig. 10, depicted the Silhouettes plot - which offers a unique advantage dependency on the actual partition of the data points and independent of algorithm used for making clusters[22]. The average Silhouette width is 0.52 within the set. Generally, a large overall average silhouette width can be taken; in this case, this algorithm has considered being having a strong clustering structure. Fig. 9. Dendrogram As shown in the Fig. 10 - Silhouette plot the average silhouette coefficients is: 0.9872986700438008, and the one set of coefficients are given below for reference: 0.98862214, 0.97530403, 0.98816568, 0.98862214, 0.98699489, 0.98862214, 0.97530403, 0.98862214, 0.98873533, 0.98862214, 0.98699489, 0.98862214, 0.97782835, 0.99433632, 0.99433632, 0.99433632, 0.99433632, 0.99433632, 0.98934239, 0.97934875, 0.98957609, 0.98957609, 0.98957609, 0.97934875, 0.98957609, 0.97620826, 0.98957609, 0.98721515, 0.98957609 Fig. 10. Silhouette plot As per the above silhouette coefficients, it can be observed that the quality of the cluster is very good as the average coefficient is 0.98 it is a very accurate clustering model. 6. Discussion & review of the results Data Set: The Experimental evaluation discussed in the above sections is a structured customer data with 100000 of customer entities with customer name and address attributes. The article, shown the first 5000 records for better depiction in the diagrams otherwise all the records would be cumbersome and not legible to demonstrate. DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 741 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) Performance measure: The performance and effectiveness of a cluster are evaluated by two measures, such as Purity and F-measure[23].Precision is a measure of model relevancy. A recall is the measure of models completeness F-Measure[24] of a cluster for given class i, derived as 𝐹 𝑖, 𝑗 Where p(i,j) = (4) , , ∗ , , (3) r(i,j) = Here i is a class of cluster j and 𝑛 is a number of CDs in class i, and 𝑛 is a number of CDs in cluster j, 𝑛 is a number of CDs in cluster j and class i. From this entire cluster’s f-measure is calculated as below: 𝐹 ∑ (5) 𝑚𝑎𝑥 𝐹 𝑖, 𝑗 F-measure is a weighted harmonic mean – a conservative average, which is a combined measure that assesses the P/R trade-off. The purity of a cluster is defined as follows 𝑝𝑢𝑟𝑖𝑡𝑦 𝑗 𝑚𝑎𝑥 𝑛 (6) Similar total Custer purity[24] is Purity = ∑ ∗ 𝑝𝑢𝑟𝑖𝑡𝑦 𝑗 (7) As shown in table 9, for all three sets of data evaluated F1-scores are 1. All precision, recall values for all the tree sets are equals to one. Hence, the f-measure is very high. Hence, it is a very accurate model. F-measure = 𝐹 ∗ =1 Clusters F-measure is: 1 Purity is also calculated as 1 and hence the quality of the cluster is very high. Precision Recall F1-Score Support 1 1.00 1.00 1.00 8 2 1.00 1.00 1.00 7 3 1.00 1.00 1.00 13 Micro avg 1.00 1.00 1.00 28 Macro avg 1.00 1.00 1.00 28 Weighted avg 1.00 1.00 1.00 28 4 5 6 Micro avg Macro avg Weighted avg 7 8 9 Micro avg Macro avg Weighted avg 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 8 6 8 22 22 22 12 7 10 29 29 29 Table 9: Precision, recall, f1-score and support Both together high precision and high recall high accurate model. Hence it is concluded that this model is highly accurate. After analyzing these data, it has been observed that over 90% of duplicate records are clustered together and can be easily eliminated or linked into one hierarchal entity. Rest of the duplicates, which are small in number, were not identified by the algorithm as specific scenarios such as Post box number are given in the address as NW, SW, etc., these can be addressed by tuning the algorithm to the specific business cases DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 742 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) 7. Conclusion HP Algorithm is based on the cosine similarity measure, which is widely used for comparing text documents. The proposed algorithm used cosine similarities as it is easy to understand, easy to use, and also works based on the orientation rather magnitude. This article exemplifies the various reasons for data getting duplicated (customer data) and is different solutions existing in the current literature. There is no certain solution or a tool to eliminate the duplication of master data. The proposed solution with a heuristic pruning algorithm to identify and help to eliminate duplicate customer data. This solution has two steps, the first step is a time process to eliminate all duplicates in the existing data and the second step is preventing duplicate data from being created from online processing. This algorithm compares the record’s relevancy with each other record and gives the score of the similarity. All similar records having the minimum threshold values are grouped into a cluster. All records in one cluster belong to one customer hierarchy are to be considered as a single entity. The matching algorithm will be input for identifying the similarities and the cosine similarity algorithm takes key inputs from the matching algorithm and finds the similarity. The time complexity of the algorithm is less compared to the regular algorithm’s running time, the overall running time of the HP algorithm is reduced by 2nm-m2 (where n>>m.) running time. The quality of the cluster is evaluated using dendrogram’s quality evaluation techniques – SSE and Std (SSE). Silhouette coefficients are ~0.98, which is very accurate. Both cluster quality measures of F-measure and purity are very high (1), hence the quality of the cluster is very high and accurate. All duplicate scenarios discussed in section 3, were addressed with this algorithm. It seems above 90% of duplicate data and duplicate scenarios have been captured and addressed by this algorithm. Since it is built on a distributed computing framework and lambda architecture, it is a scalable and reliable solution. Since industries are spending heavily on the deduplication process and there are many interesting factors in this approach, our research group working on this to further develop and tune this algorithm on a distributed framework. 8. Conflicts of Interest The authors declare no conflict of interest. References [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13] [14] [15] [16] [17] [18] [19] [20] M. Spruit and K. Pietzka, "MD3M: The master data management maturity model," Computers in Human Behavior, vol. 51, pp. 10681076, 2015/10/01/ 2015. S. T. Ng, F. J. Xu, Y. Yang, and M. Lu, "A Master Data Management Solution to Unlock the Value of Big Infrastructure Data for Smart, Sustainable and Resilient City Planning," Procedia Engineering, vol. 196, pp. 939-947, 2017/01/01/ 2017. R. Bozeman, "Data Duplication and HubSpot:," in Dealing With Duplicates and the Impact They Have on Your BusinessubSpot’s, Ed., ed, Jul 11, 2018 G. S. Manku, A. Jain, and A. Das Sarma, "Detecting near-duplicates for web crawling," in Proceedings of the 16th international conference on World Wide Web, 2007, pp. 141-150: ACM. A. Schofield, L. Thompson, and D. Mimno, "Quantifying the Effects of Text Duplication on Semantic Models," in Proceedings of the 2017 Conference on Empirical Methods in Natural Language Processing, 2017, pp. 2737-2747. M. S. Charikar, "Similarity estimation techniques from rounding algorithms," in Proceedings of the thiry-fourth annual ACM symposium on Theory of computing, 2002, pp. 380-388: ACM. P. Jain, B. Kulis, J. V. Davis, and I. S. Dhillon, "Metric and kernel learning using a linear transformation," Journal of Machine Learning Research, vol. 13, no. Mar, pp. 519-547, 2012. J. Wang, S. Kumar, and S.-F. Chang, "Semi-supervised hashing for scalable image retrieval," 2010. Q. Jiang and M. Sun, "Semi-supervised SimHash for efficient document similarity search," in Proceedings of the 49th Annual Meeting of the Association for Computational Linguistics: Human Language Technologies-Volume 1, 2011, pp. 93-101: Association for Computational Linguistics. E. N. Borges, M. G. de Carvalho, R. Galante, M. A. Gonçalves, and A. H. F. Laender, "An unsupervised heuristic-based approach for bibliographic metadata deduplication," Information Processing & Management, vol. 47, no. 5, pp. 706-718, 2011/09/01/ 2011. S. Chaudhuri, K. Ganjam, V. Ganti, and R. Motwani, "Robust and efficient fuzzy match for online data cleaning," presented at the Proceedings of the 2003 ACM SIGMOD international conference on Management of data, San Diego, California, 2003. W. W. Cohen, "Data integration using similarity joins and a word-based information representation language," ACM Trans. Inf. Syst., vol. 18, no. 3, pp. 288-321, 2000. C. F. Dorneles, C. A. Heuser, A. E. N. Lima, A. S. d. Silva, and E. S. d. Moura, "Measuring similarity between collection of values," presented at the Proceedings of the 6th annual ACM international workshop on Web information and data management, Washington DC, USA, 2004. S. Niwattanakul, J. Singthongchai, E. Naenudorn, and S. Wanapu, "Using of Jaccard coefficient for keywords similarity," in Proceedings of the International MultiConference of Engineers and Computer Scientists, 2013, vol. 1. M. Steinbach, G. Karypis, and V. Kumar, "A comparison of document clustering techniques," in KDD workshop on text mining, 2000, vol. 400, pp. 525-526: Boston. V. Thada and V. Jaglan, "Comparison of jaccard, dice, cosine similarity coefficient to find best fitness value for web retrieved documents using genetic algorithm," International Journal of Innovations in Engineering and Technology, vol. 2, no. 4, pp. 202-205, 2013. A. Huang, "Similarity measures for text document clustering," in Proceedings of the sixth new zealand computer science research student conference (NZCSRSC2008), Christchurch, New Zealand, 2008, pp. 49-56. D. Melo et al., "Hierarchical Density-Based Clustering Based on GPU Accelerated Data Indexing Strategy," Procedia Computer Science, vol. 80, pp. 951-961, 2016/01/01/ 2016. F. S. Al-Anzi and D. AbuZeina, "Toward an enhanced Arabic text classification using cosine similarity and Latent Semantic Indexing," Journal of King Saud University - Computer and Information Sciences, vol. 29, no. 2, pp. 189-195, 2017/04/01/ 2017. P. Xia, L. Zhang, and F. Li, "Learning similarity with cosine similarity ensemble," Information Sciences, vol. 307, pp. 39-52, 2015/06/20/ 2015. DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 743 e-ISSN : 0976-5166 p-ISSN : 2231-3850 Kari Venkatram et al. / Indian Journal of Computer Science and Engineering (IJCSE) [21] M. Bilenko and R. J. Mooney, "Adaptive duplicate detection using learnable string similarity measures," presented at the Proceedings of the ninth ACM SIGKDD international conference on Knowledge discovery and data mining, Washington, D.C., 2003. [22] P. J. Rousseeuw, "Silhouettes: A graphical aid to the interpretation and validation of cluster analysis," Journal of Computational and Applied Mathematics, vol. 20, pp. 53-65, 1987/11/01/ 1987. [23] S. Eddamiri, E. M. Zemmouri, and A. Benghabrit, "An improved RDF data Clustering Algorithm," Procedia Computer Science, vol. 148, pp. 208-217, 2019/01/01/ 2019. [24] Z. Abedjan, "Improving RDF data with data mining," 2014. Authors Profile Kari Venkatram, Completed Masters in Software Engineering from BITS, Pilani. Perusing PhD School of Computer Science and Engineering, Vellore Institute of Technology, Vellore – 632 014, Tamil Nadu, India. His research interests are in Master Data Management and Big Data Analytics. He is working on some of the innovative approach to solve practical challenges in MDM with help of Artificial Intelligence and Machine Learning models along with Big data solutions. Geetha Mary A received her Ph.D. in Computer Science Engineering from Vellore Institute of Technology, Vellore, India. She has completed M.Tech in Computer Science and Engineering from VIT and B.E. from University of Madras, Tamil Nadu, India. She is working for VIT as Associate Professor. She was awarded Merit Scholarship for her best academic performance for the year 2007-2008 during her M.Tech study. She has authored more than 50 journal and conference papers. She has authored book chapters in the area of data mining and artificial intelligence. Her research interests include security for data mining, databases and intelligent systems. She works to empower healthcare management using computer science. Dr. Geetha Mary is associated with many professional bodies like IACSIT, CSTA and IAENG. DOI : 10.21817/indjcse/2022/v13i3/221303045 Vol. 13 No. 3 May-Jun 2022 744