Data Warehouse Design and Implementation - Research Proposal Example

?DATA WAREHOUSE DESIGN AND IMPLEMENTATION Data Warehouse Design and Implementation Affiliation In the past few years, business intelligence has turned out to be a buzzword for the reason that it offers an excellent support for decision making. However, at the present business organizations have a wide variety of database systems such as online analytical processing, data warehousing and data mining systems. In this scenario, data warehousing offers effective tools for maintenance, storage and retrieval of data. Additionally, the online analytical processing systems offer a technique to produce ad-hoc queries beside the data-warehouse in an attempt to respond to significant business questions. In the same way, data mining offers a wide variety of techniques and algorithms for finding knowledge in a huge bulk of corporate data. In addition, in order to develop a data warehouse system, we need to put into operation a reliable technology structure where corporate operational data can be managed effectively with real and enterprise-wide aspects and to get into reorganization of a handful application policies to offer a high quality system. However, the implementation and design of a data warehouse system has occasionally been a very big challenge in theory as well as in practice (Charles, 2010; Turban, Leidner, McLean, & Wetherbe, 2005; Olamendy, 2010). This paper discusses some of the important aspects related to the design and implementation of a data warehouse system. The basic purpose of this research is to investigate some of the important aspects such as theories, initiatives, models and techniques for better and improved data warehouse system design and implementations. Introduction A data warehouse is normally recognized as an integrated and time-varying set of information and data that is basically used for strategic decision making through OLAP (online analytical processing) methods. It is fundamentally a large size database that stores integrated, historical, and combined data taken from independent, heterogeneous and distributed information sources. Though it is usually considered that business warehouse design is a non-trivial issue and global data models and snow?ake or star representation are related in this perspective, however hardly any technique exists to date for applying similar methods from an operational database (Charles, 2010; Husemann, Lechtenborger, & Vossen, 2000; Laudon & Laudon, 1999; Inmon, 2002). Without a doubt, there are many standards and methods to implement and design a relational data model for a business system which are flexible to design and implement data warehouse technology based modeling, however they cannot be employed as a natural method, consequently data warehouse development modeling is novel regulation that is improving with the passage of time. Basically, the starting point for the design and development of the data-warehouse environment is the data model. In absence data model, the implementation of a data warehouse is almost impossible. In this scenario, the data model works like a roadmap for technology based system development (Charles, 2010; Husemann, Lechtenborger, & Vossen, 2000; Laudon & Laudon, 1999; Inmon, 2002). Moreover, in order to develop a data warehouse system, we need to form a reliable technology based environment where organization’s operational data will be maintained effectively in an enterprise and integrated view and to get into recognition a handful accomplishment policy to offer a high quality system. However, the implementation and design of a data warehouse system frequently is a very complex issue in theory as well as practice (Charles, 2010). This paper will present a detailed analysis of data warehouse design and implementation. This research will cover some of the major aspects that need to be considered while designing implementing models of the data warehouse. Data Warehouse Design This section discusses some of the approaches and methods those can be adopted for the data warehouse design which looks like the customary database design procedure. This section basically deals with the conceptual design stage. However, various researches such as (Husemann, Lechtenborger, & Vossen, 2000; Leonard, 2009; Inmon, 2000) outlined 4 sequential stages of data warehouse design that are similar to the traditional database design procedure. Given below are the major stages of data warehouse design: Requirements analysis and specification In system design the operational entity relationship representation offers fundamental information to decide the multidimensional analysis power, where we suppose that how easily a depiction is accessible to the data warehouse developers and designers. In addition, in this stage company domain specialists choose deliberately related operational database features and identify the reason to make use of them as elements and/or events. Additionally, for every attribute it is essential for the data warehouse designers to make a decision whether it holds elective data or not. In this scenario, additional matching system development requirements in the form of complex derived procedures are incorporated. Hence, the resultant data warehouse requirements specification holds a tabular catalog of attributes with their multidimensional principle. Conceptual design In data warehouse design stage the conceptual design stage transforms semi-formal business requirements specification into a formal theoretical multidimensional design. In addition, this transformation results in a graphical multidimensional representation that includes fact plans with their associated dimension and measures hierarchies. For every aspect of a fact system the summarize-ability limitation are represented in a tabular form. Logical design The logical design stage of a data warehouse system transforms the conceptual system design representations into a logical one with respect to the proposed logical data model (frequently multidimensional or relational). In this scenario, the logical representations are produced from change control and management process that simply refers to the developed theoretical diagrams and summarize-ability restrictions. Physical design This stage of data warehouse design encompasses a detailed procedure that ends in a physical implementation of the logical plan with respect to the particular characteristics of the target database structure, comprising physical optimization methods for example normally recognized indexing policies, partitioning etc., and OLAP related modifications similar to relational de-normalization of data warehouse dimension tables, preaggregations or related utilize of bitmap indexes. Dimensional Modeling In case of data warehouse design and implementation the dimensional modeling is a modeling process which is used to arrange business dimensions as well as aspects that are examined with magnitude in an attempt to run high performance inquiries. At a high level of understanding, the data warehouse holds an integrated analysis of data that result from data in the developed systems supporting a wide variety of business procedures. In addition, between these operational systems and the data warehouse, there is a significant element recognized as the staging region. In this region, the operational data collected from different sources have to be enhanced as well as changed into a format that is appropriate to be stored in the data warehouse storage (Olamendy, 2010; Charles, 2010). In addition, a database in a data-warehouse is an extremely de-normalized arrangement comprising two major elements: the primary aspect is a central table, which is as well recognized as fact table, which holds transaction data and it is controlled by the 2nd kind of components, which are recognized as the dimension tables that hold master data or referential static data. Additionally, in case of data warehouse design related conceptual association between these two major elements is that the dimensions explain the facts. In this scenario, this detailed data model of proportions and fact tables is recognized as the dimensional model it can be graphically explained like a star schema that identifies a multidimensional database (Olamendy, 2010; Charles, 2010). Moreover, in a data warehouse multidimensional database can be built using conventional relational database management systems or dedicated multidimensional DBMS optimized for similar arrangements. In a business data warehouse, the data model is basically made of data marts or a sub-set of data warehouse star-schemas, where every data mart is a particular system’s fact table controlled by dimension tables including data collected from several departments of functional areas (Olamendy, 2010; Charles, 2010). Conceptual Model Selection According to (Phipps & Davis, 2002), in order to effectively implement a data warehouse there is need to effectively design the system. In this scenario, a system development team needs to keep in mind a variety of system design and development aspects. For instance, in OLTP arena, Entity/Relationship supported models have been most extensively used and in OLAP arena data warehouse dimensional modeling is very common. Basically, an ER model of the system refers to a graphical demonstration of system entities and their associations to each other. Basically, the entity relationship model has been developed to offer the dimensional functionality required for DWH. However, dimensional models systematize data supported on the policy of a business. Additionally, dimensional models are based on the thought that a company’s facts are fundamental and the data opens out around it. In this scenario, the most common dimensional model is the Star model; further differences are the Snowflake model and the Dimensional Fact Model (Phipps & Davis, 2002; Peralta, 2002). In addition, logical and conceptual schemas for data warehouses can be formed through dimensional and ER based models. In fact, it is probable that the conceptual schema can be in one format and the logical in another. For instance, ER modeling used for the sake of conceptual schemas and dimensional for the physical and logical data warehouse designs. Additionally, the Star schema is well-known for both logical and conceptual modeling of data warehouse, however it cannot be the most excellent alternative for end users as it does not express roll-up or drill-down paths in the overall design. In view of the fact that the transformation from ME/R to Star form is simple so we can make use of ME/R model for the DWH conceptual representation and design and the Star model for logical DWH design development (Phipps & Davis, 2002; Peralta, 2002). Ontologies Based Design Ontology based design is another most important technique for DWH design and development. According to (Pardillo & Mazon, 2011) the ontologies appear to be an excellent solution for the reason that they are common conceptualization of a system domain, demonstrating shared DWH technology based on knowledge. In view of the fact that the system development representations are regulatory, hence ontologies are significant, which demonstrates that models state requirements regarding the system to be (for example what and how should it be constructed) and ontologies explain the implementation domain without any change. Additionally, ontologies facilitate autoimmunization supporter through model-driven DWH development for the reason that they offer methods to properly identify the semantics of a domain on which design models can be implemented. However, many researchers believe that ontologies can be employed for data warehouse system development as data arrangements depend on a specified context to describe their real semantics, as well as ontologies offer the vital contextual knowledge applicable to interpret semantics. For example, the fact sales can be demonstrated as: (Pardillo & Mazon, 2011; NETWORK INFERENCE. INC., 2005) (i) A set of proceedings and a specialization of company activity and commercial tasks (ii) The set of Offering-For-Sale procedures comprises actions in which an agent presents one or more things intended for sale to one or more agents. In addition, in the context of data warehousing, sales can refer to both cumulative and punctual sales. Basically, if sales are formed as a fact of a data warehouse and developers find a wide variety of sales' facts in data sources with the similar functional dependencies, they cannot be able to automatically choose that one to be taken without recognizing semantic knowledge. Moreover, in every case, its management and analysis requires diverse treatments (Pardillo & Mazon, 2011; NETWORK INFERENCE. INC., 2005). Enterprise data model to Dimensional model Transformation In data warehouse design the enterprise data model refers the arrangement of business entities and its fundamental associations, attributes, keys, subtypes and business rules using the ER with normalization methods. Additionally, enterprise data model offers a complete analysis of data model employed for DWH systems to be incorporated into the data warehouse. Additionally, this significant model related to subject part is supported by the basic operational information systems. In this scenario, the level of success of the bigger enterprise data model is of small fear to the development of the DWH (Olamendy, 2010; Informatica Professional Services, 2008). In addition, once the corporate data model is presented, developers are able to make various changes with the purpose of building dimensional model of the DWH as stated below: (Olamendy, 2010; Informatica Professional Services, 2008) Deduction of simple operational data Accumulation of suitable derived data Alteration of associations Development of array of data Moreover, it is amazing to note that these changes are a critical to the design of a DWH; however their implementation is mostly determined through the business needs of the decision support systems (Olamendy, 2010; Informatica Professional Services, 2008). Need for Quality DWH Design According to (Singh & Singh, 2010) the quality of information in DWH depends on three aspects: Quality of data Quality system applications Quality of DWH Design schema Additionally, the design of DWH very much controls the quality of analysis that is probable with data in it. Hence, developers should adequate attention to the aspects of DWH schema design. However, a number of issues for example gradually changing dimensions, quickly modifying elements and multivalued scope etc. In addition, in DWH a flawed design schema influences harmfully corporate data and information production quality. Given below some of the major design issues and quality aspects those need to be considered for the development of more efficient and effective system design: (Singh & Singh, 2010) Incomplete requirement Lack of concurrency in business rules Less effective selection of dimensional modeling Late recognition of altering dimensions Late incoming problems Inappropriate choice of record granularity Imperfect recognition of facts/dimensions Incapability to support database schema Conclusion At the present, data has become an essential element of business organization. In fact, the success of the business organizations completely depends on the effective use of data. For this purpose, they use different tools technologies. These technologies allow them to make effective use of this data. In this scenario, data warehouses merge, collect, organize, and summarize this data consequently it can be used for business decisions. However, data warehouses have been employed for several years to support business decision making. In addition, data warehousing methods and practices are well recognized, extensively accepted, flourishing and not controversial. However, for the successful implementation of data warehouse there is a dire need for more accurate and efficient system design and development. The major aspect of data warehousing is data and is efficient design. In fact, the business clients are familiar with what data they need and how they want to make use of it. In this scenario, it is essential for the organizations to decide what business data is they would need, position sources for the corporate data, and systematize the data in a corporate working and operational dimensional model that outlines the business requirements. In system design and implementation the dimensional modeling and development of the data warehouse design, is not an secret science or art; it is a mature process that systematizes data in a simple, straightforward, and insightful illustration of the method business decision makers want to analyze and view their data. Reference Charles, J. (2010, October 25). Designing and implementing a Data Warehouse. Part 4. Retrieved November 04, 2012, from http://johnolamendy.wordpress.com/2010/10/25/designing-and-implementing-a-data-warehouse-part-4/ Husemann, B., Lechtenborger, J., & Vossen, G. (2000). Conceptual DataWarehouse Design. Proceedings of the International Workshop on Design and Management of DataWarehouses (DMDW’2000), (pp. 1-11). Stockholm, Sweden. Informatica Professional Services. (2008, November 17). Enterprise Data Warehouse Implementation. Retrieved November 04, 2012, from http://www.informatica.com/Images/11013_1709_enterprise-data-warehouse-implementation.pdf Inmon, W. H. (2000). Buiildiing the Data Warehouse: Gettiing Started. Retrieved November 04, 2012, from http://inmoncif.com/inmoncif-old/www/library/whiteprs/ttbuild.pdf Inmon, W. H. (2002). Building the Data Warehouse, 3rd edition. New York: Wiley. Laudon, K. C., & Laudon, J. P. (1999). Management Information Systems, Sixth Edition (6th ed.). New Jersey: Prentice Hall. Leonard, E. M. (2009). Design and Implementation of an Enterprise Data Warehouse. Retrieved November 06, 2012, from http://epublications.marquette.edu/cgi/viewcontent.cgi?article=1118&context=theses_open NETWORK INFERENCE. INC. (2005). Ontologies and Data Warehousing. Retrieved November 05, 2012, from http://me.jtpollock.us/pubs/2005.10-Whitepaper_DataWarehouse.pdf Olamendy, J. C. (2010, September 10). Designing and implementing a Data Warehouse: Part I. Retrieved November 05, 2012, from C-SharpCorner: http://www.c-sharpcorner.com/uploadfile/john_charles/designing-and-implementing-a-data-warehouse-part-i/ Pardillo, J., & Mazon, J.-N. (2011). Using Ontologies for the Design of Data Warehouses. International Journal of Database Management Systems ( IJDMS ), Volume Issue 2, 73-87. Peralta, V. (2002). Data Warehouse Logical Design from Multidimensional Conceptual Schemas. Retrieved November 02, 2012, from http://www.fing.edu.uy/inco/grupos/csi/esp/Proyectos/dwd_csic2002/Publics/%5BPer03%5D.pdf Phipps, C., & Davis, K. C. (2002). Automating Data Warehouse Conceptual Schema Design and Evaluation. DMDW 2002 , 23-32. Singh, R., & Singh, K. (2010). A Descriptive Classification of Causes of Data Quality Problems in Data Warehousing. IJCSI International Journal of Computer Science Issues, Volume 7 Issue 3, 41-50. Turban, E., Leidner, D., McLean, E., & Wetherbe, J. (2005). Information Technology for Management: Transforming Organizations in the Digital Economy . New York: Wiley. Read More