Derivation of initial data warehouse structure by mapping operational database on transaction patterns

Abstract

Data warehouses improve the quality of integrated information in the organization for decision-making. The data for the data warehouse comes from online transaction systems. Typically, an involved process of analysis precedes the actual design phase of a data warehouse [1]. The analysis process becomes more difficult because of the costs involved in hiring experienced staff and the privacy issues arising from the use of external consultants [2]. Peter Coad's transaction pattern [3] is a higher-level description of a generic business process (or a template) that has helped in the analysis and design of a wide range of business domains. Thesis of this paper is that the mapping of the operational databases on the transaction pattern facilitates the derivation of initial data warehouse structure. During the mapping process, the relationships, roles and attributes of the players defined by the transaction pattern help us in identifying the instances of the pattern in the database. Through these instances, we can then derive the initial data warehouse structure i.e. the attributes of the fact and dimension table(s). The data warehouse structure thus derived reduces the need for an extensive information analysis of the needs of the user and the dependency on the experienced personnel for data warehouse development. This paper proposes a three-step derivation methodology that is illustrated using a case study of an organization's operational database.