A Novel Framework and Model for Data Warehouse Cleansing

ABSTRACT Data cleansing is a process that deals with identification of corrupt and duplicate data inherent in the data sets of a data warehouse to enhance the quality of data. This paper aims to facilitate the data cleaning process by addressing the problem of duplicate records detection pertaining to the „name‟ attributes of the data sets. It provides a sequence of algorithms through a novel framework for identifying duplicity in the „name‟ attribute of the data sets of an already existing data warehouse. The key features of the research includes its proposal of a novel framework through a well defined sequence of algorithms and refining the application of alliance rules [1] by incorporating the use of previously existing and well defined similarity computation measures. The results depicted show the feasibility and validity of the suggested method. Keywords Data warehouse, data cleansing, fuzzy logic, data mining. 1. INTRODUCTION A data warehouse is a subject-oriented, integrated, non - volatile and time- variant collection of data in support of management‟s decisions [2]. It is the central point of data integration for business intelligence. An enterprise generally runs numerous operational systems which is a major source of data, together with external agencies, for its data warehouse. These operational systems store data by individual applications and these applications work more or less on similar entities. For example, in a telecommunication company, applications like customer billing system , customer credit verifying system, customer personal information management system etc. would have many attributes in common, for example - customer name, addresses , but these common attributes may or may not be represented in exactly same formats in their associated databases. In such a scenario, when data is integrated for storage in a data warehouse, unintentional duplication of records created from millions of data entries from heterogeneous sources can hardly be avoided. This leads to redundancies in the data and degradation of its quality. Data Warehouse suffers from dirty data. Dirty data can be understood as the data which is not consistent with already residing data of data warehouse [3,4]. For e.g. missing data can be considered as dirty. The data that is unknown or not clear at the time of data entry is also considered dirty data. Data warehouses, after their construction by merging and amalgamation of numerous operational systems, are also updated periodically to store/accommodate new data. The possibility and probability of dirty data induction increases with more and more regular updates. Even the most carefully and cautiously planned updating cannot give a full-proof protection from dirty data [5].The presence of dirty data leads to many serious problems because data warehouse is used for decision making and strategic planning of an organization. Data cleansing is the most obvious and also, the only viable solution that can address these problems. Cleaning of data is an activity which detects redundancy or unwanted data and corrects it for increasing the quality of the data .The need, therefore, is of an automated data cleaning tool [6] with capabilities of maintaining as well as improving data quality in the data warehouse. It also requires general framework in [7] for data cleaning process as well as some methods that can be used to address the problem like statistical outlier detection, pattern-matching, clustering and data mining techniques. „Name‟ field as given in [8] is one of the most common attribute which is used in query processing. Finding and matching personal names is at the core of an increasingly large no. of applications: from text and web mining, information retrieval, search engines to de-duplication and data linkage systems. A large amount of data in most warehouses pertains to people. Hence, personal names are often encountered being used as search criteria thus, the significance and importance of the personal name field cannot be stressed too strongly. The problems in the name field arises because of several reasons ranging from spelling variation for personal names, use of nicknames, change of names with time etc. It also presents an overview of a comprehensive no. of name matching techniques and their experimental results. In some cases, it is not easy to find out whether a name variation is a different spelling as in [9] of the same name or a different name altogether. There are various types of variations which are: • Spelling variations: These generally include interchanged or mislaid script due to typographical errors, substitute letters (as in Rajiv and Rajeev), supplementary letters (such as Smythe).Basically such variation does not influence the phonetic configuration of the name but then also causes problems in respective names. These variations mainly