Version 0.99
1
Data Warehouse Engineering Process (DWEP) with U.M.L. 2.1.1. Edwar Javier Herrera Osorio,
[email protected] Universidad Nacional de Colombia
Abstract— The integration process is part of the “Business Intelligence”, it´s composed [1]: (i) The sources of data, (ii) The processes of extraction, transformation and loading (ETL), and (iii) the area storage. Lujan [3, 4] proposed in his doctoral thesis using the Data Warehouse Engineering Process methodology (DWEP). This is an implementation of the Unified Process to the process of generating the “data warehouse”, but its devices are developed in UML version 1.4. The version of UML 2.1.1. [3] Expands the number of diagrams used to facilitate the faces Unified Process, this paper a development DWEP upgrade to the current version of UML. Lujan[3,4] proposed the use of fifteen (15) artifacts for the development of the data warehouse, with this update added five (5) new diagrams to facilitate implementation of this methodology.
we briefly present some of the most important related work and point out the main shortcomings. In Section 3, present the DWEP and the new diagrams. Finally, we present the main contributions and the future work in Section 4. II.
RELATED WORK
Recent years have developed several methodologies for the development of data warehouses which defines the following levels of abstraction [7]: Conceptual, logical and physical. Conceptual Data Model: Represents the interactions between the entities and relationships. This model is closer to real world problems to solve. Highlights the following patterns in the data warehouse: Model Multidimensional / ER (Sapia) [8], model Star / ER (Tryfona) [9], GOLD model (Trujillo) [5, 10], model Husemann [11], YAM2 model [12].
Index Terms— Data warehouse, UML, Unified process.
I.
INTRODUCTION
T
he data warehouse (DW) is one of the components of the “intelligence business”, Bill Inmon defines it: “... A data warehouse is a subject-oriented, integrated, timevariant, nonvolatile collection of data in support of management’s decisions...” [1], and Ralph Kimball: “… the Data Warehouse is a collection of data in the form of a database that stores and organizes information that is extracted directly from operational systems (sales, production, finance, marketing, etc..) and external data…”[2]. Building a DW is a challenging and complex task because a DW concerns many organizational units and can often involve many people. Lujan proposed at the 2004 [3,4] Data Warehouse Engineering Process (DWEP), a methodology for building the data warehouse based on the Unified Modeling [5] and the Unified Process (UP) [6], which allows the user to tackle DW all design stages, from the operational data sources to the final implementation and including the definition of the ETL (Extraction, Transformation, and Loading) processes and the end users' requirements. The rest of the paper is structured as follows. In Section 2,
Logical data model: The objective of the logical data model is to describe in as much detail as possible, without considering how they will be physically in the database. Is this model includes entities, relationships and their interaction, the data types of all attributes of each entity, the definition of primary and foreign keys, definition of the extraction, transformation and loading (ETL), among other activities. Physical Data Model: The physical data model includes all the specification of all tables and columns, following the business rules to determine the design of the data warehouse. In this model, you write the code to create tables, views, integrity rules, multidimensionality consultations. On the other hand are the different methodologies for the development of data warehouses [3, 5, 13, 14, 15, and 16], most shortcomings: do not include a visual modeling language, not to propose a series of steps or phases, or based on an application (for example, the star diagram of relational databases). In 2005, Lujan proposed a methodology based on the Unified Process (Data Warehouse Engineering Process DWEP), which is based on UML version 1.4. The DWEP propose a collection of artifacts for standardization. In conclusion DWEP claim upgrade to version 2.1.1. of UML which gives us more devices to implement the “data warehouse”.
Version 0.99
2 The DWEP proposes the use of fifteen (15) devices for the development of data warehouse, with this update added five (5) new diagrams to facilitate implementation of this methodology. In Table 1 one can observe the twentieth DWEP with UML diagrams 2.1.1. Requirement: During this workflow, end users specify the measures and add more interesting, dimensional analysis, queries used to generate periodic reports and frequency of updating the data. DWEP use of use cases.
Figure 1 DWEP [5].
III.
DATA WAREHOUSE ENGINEERING PROCESS
Lujan in a doctoral thesis [5] presents a Data Warehouse Engineering Process (DWEP) based on the unified process. The UP is a methodology for software development proposed by OMG [17], its main features are: it is iterative, is addressed by the use cases is based on stages of development, using UML as a graphical language models [18 and 19].
Analysis: The purpose of this workflow is to improve the structure and requirements from the requirements stage. This step documents the incumbent systems that feed the data warehouse. DWEP proposed use the Source Conceptual Schema(SCS), Source Conceptual Object Schema (SCOS) Source Logical Schema (SLS), Source Logical Communications Schema (SLCS) y Source Physical Schema (SPS). Source Conceptual Object Schema (SCOS, view figure 4): In DWEP object diagrams depict instances and their relationships at a point in time. You create a DWEP object diagram to: Explore “real-world” examples of objects and the relationships between them.
The DWEP is composed of four phases [5 and 20]: inception, design, construction and transition (view Fig. 1).
Explain complex relationships between classes to people who find class diagrams too abstract.
Workflows DWEP
Become input into creating a SCS diagram.
In general terms the UP, workflow is a set of activities in a given area resulting in the construction of artifacts (a text, a diagram, a web page, code in programming language, etc.).
Table 1 DWEP 2.1.1 Diagrams
Version 0.99
3 T V:Products
Miam i:Cities
001:Orders
Sony:Customer
Design: At the end of this workflow, the structure is defined in the data warehouse. The main result of this workflow is the conceptual model of the data warehouse.
Radio:Products
Play Statio
The DWEP proposes the use Data Warehouse Conceptual Schema (DWCS), Client Conceptual Schema (CCS),el Data Mapping (DM), Data Warehouse State Machine Schema (DWMSS) and the Data Warehouse Activity Schema (DWAS).
TV2:Products
002:Orders
Radio2:Products
Figure 4 Source Conceptual Objects Schema
Source Logical Communications Schema (SLCS, view figure 5): Are used to explore the dynamic nature of your software. Source Logical Communications Schema show the message flow between objects in an object-oriented application, and also imply the basic associations (relationships) between tables. Communication diagrams are often used to: Provide a bird’s-eye view of a collection of collaborating objects, particularly within a real-time environment. Allocate functionality to classes by exploring the behavioral aspects of a system. Model the logic of the implementation of a complex operation, particularly one that interacts with a large number of other objects. Explore the roles that objects take within a system, as well as the different relationships in which they are involved when in those roles.
Data Warehouse State Machine Schema (DWMSS, view figure 6). Data warehouse State Machine Schema depict the dynamic behavior of an entity based on its response to events, showing how the entity reacts to various events based on its current state. Create a Data warehouse State Machine Schema to Explore the complex behavior of a class, actor, subsystem, or component. Model real-time systems.
Figure 6 Data Warehouse State Machine Schema :Cities
1: Read_table
2: Read_table
:Customer
Job System 3: Read_table
4: Read Table
:Orders
:Products
Figure 5 Source Logical Communications Schema
Data Warehouse Activity Schema (DWAS view figure 6): Data Warehouse activity Schema are the object-oriented equivalent of flow charts and data-flow diagrams from structured development. Data Warehouse activity Schema are used to explore the logic of: A complex operation. A complex business rule. A single use case. Several use cases, A business process. Concurrent processes. Software processes.
Version 0.99
4 work correctly. More specifically, the effects of the tests are: Planning the evidence needed to design and implement the tests by creating test cases and perform tests and analyze results of each test. Workflows for maintenance and development post are not in the unified process and only part of the engineering process of the data warehouse.
Figure 6 Data Warehouse activity schema[21]
Implementation: During this workflow, the data warehouse is built: The physical structure of the data warehouse is built, start to receive data in computer systems operations, is tuned for optimized performance, among other tasks. The process proposed as unified engine components diagram. The DWEP propose use: Data Warehouse Physical Schema (DWPS), Data Warehouse Logical Schema (DWLS), Client Logical Schema (CLS), Client Physical Schema (CPS), Data Warehouse Secuence Schema (DWSS) and ETL Process. Data Warehouse Sequence Schema (DWSS, View figure 7): Data Warehouse Sequence Schema are a dynamic modeling technique, as are UML communication diagrams. Data Warehouse Sequence Schema are used to: Validate and flesh out the logic and completeness of a usage Scenario. Explore your design because they provide a way for you to visually step through invocation of the operations defined by the classes. Give you a feel for which classes in your application are going to be complex, which in turn is an indication that you may need to draw state machine diagrams for those classes. Detect bottlenecks within an object-oriented design.
Relacional DB:Customer
DWTemporalSpace:Customer
Maintenance: Unlike most systems, the data warehouse is a process that feeds constantly. The purpose of this workflow is to define the loading and updating processes necessary to maintain the data warehouse. This workflow starts when building the data warehouse and is delivered to end users, but does not have an end date. During this study, end users may have new needs, such as new downloads, which triggers the beginning of a new iteration with the requirements of workflow. Revisions post development: This is not a workflow of development activities, but a review process to improve future projects. If we keep track of time and effort invested in each stage is useful in estimating time and needs to generate the requirements for future developments. IV.
CONCLUSION
Developing a DW is a complex, expensive, time consuming, and prone to fail task. Different DW models and methods have been presented during the last few years. However, none of them addresses the whole development process in an integrated manner. In this documents, we have presented our DWEP based on the UML and the UP, which addresses the analysis and design of both the DW back-stage and front-end. For this task, we have extended the UML in order to accurately represent the different parts and properties of a DW.
DW:Customer
Sales manager
Following our approach, we design a DW as follows:
extract(Parameter) Transform(Parameter)
Load(Parameter)
We use UML to model the data sources of the DW at the conceptual level. The use of the same notation (UML) for designing the different DW models and the corresponding transformations in an integrated manner.
Figure 7 Data Warehouse Secuence Schema
Tests: The aim of this work is to verify the application to
The use of the UML importing mechanism, which guarantees the designer that each element is defined once, because the same element can be used in different models.
Version 0.99
5 REFERENCES
[1] W. Inmon, Building the data warehouse. Wiley, 2002. [2] R. Kimball and M. Ross, The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling. Wiley, 2002. [3] S Lujan and J Trujillo. A Data Warehouse Engineering Process. Advances in Information Systems, Springer Berlin / Heidelberg, Volume 3261/2005 , pp. 14–23. [4] S Lujan , Data WareHouse Desig with UML, PHD. Thesis, Universidad de Alicante, 2005 [5] Object Management Group (OMG): Unified Modeling Language Specification 2.0, Internet: http://www.omg.org/technology/ documents/ modeling_spec_catalog.htm#UML. 2009 . [6] Jacobson, I., Booch, G., Rumbaugh, J.: The Unified Software Development Process. Object Technology Series. Addison-Wesley . 1999. [7] Steel,T.B.,Jr. (Chairman): ANSI/X3/SPARC Study Group on Data Base Management Systems Interim Report; ACM SIGMOD FDT, Vol. 7, No. 2, 1975. [8] C. Sapia, M. Blaschka, G. Hofling, and B. Dinter. Extending the E/R Model for the Multidimensional Paradigm. In Proceeding of the 1ST International Workshop on Data Warehouse and Data Mining (DWDM’98), volumen 1552 of Lecture Notes in computer Science, pages 105-116, Singapore, November 19- 20 199. Springer- Velang. [9] N. Tryfona. F. Busborg, and J.G. Christiansen. starER: A Conceptual Model for Data Warehouse Desing. In proceedings of the ACM 2nd international Workshop on Data Warehousing and OLAP (DOLAP`99), pages 3-8, Kansas City, USA, November 6 1999. ACM. [10] J. Trujillo. The GOLD model: An Object Oriented multidimensional data model for multidimensional database, Symposium on Applied Computing Proceedings of the 2000 ACM, symposium on Applied computing- Volume 1, Italy, pages 346-350, 2000. ACM. [11] B. Husemann, J. Lechtenborger, G. Vossen, Conceptual Data Warehouse Desing, Proceeding of the International Workshop on Design and Management of Data Warehouses (DMDW’2000), StockHolm, Sweden. [12] A. Abello, J. Samos, and F. Saltor. YAM2 (Yet Another Multidimensionañ Model): An extension of UML. In International database Engineering applications Symposium (IDEAS’02), pages 172-181, Edmoton Canada, July 17-19 2002. IEEE Computer Society. [13] Kimball, R.: The Data Warehouse Toolkit. John Wiley & Sons (1996) (Last edition: 2nd edition, John Wiley & Sons, 2002). [14] Giovinazzo, W.: Object-Oriented Data Warehouse Design. Building a star schema. Prentice-Hall, New Jersey, USA (2000) [15] Cavero, J., Piattini, M., Marcos, E.: MIDEA: A Multidimensional DataWarehouse Methodology. In: Proc. of the 3rd Intl. Conf. on Enterprise Information Systems (ICEIS’01), Setubal, Portugal (2001) 138–144
[16] Moody, D., Kortink, M.: From Enterprise Models to Dimensional Models: A Methodology for Data Warehouse and Data Mart Design. In: Proc. of the 3rd Intl. Workshop on Design and Management of Data Warehouses (DMDW’01), Interlaken, Switzerland (2001) 1–10 [17] [21] Object Management Group (OMG). Unifie Modeling Language (UML), version 2.0, consultado marzo de 2008 Internet: http://www.uml.org/ [18] Booch Grady, Rumbaugh Jim, Jacobson Ivar, “UML, El lenguaje unificado de modelado”, consultado en internet http://www.itescam.edu.mx/principal/sylabus/ fpdb/recursos/r25380.PDF [19] Fuentes Lidia, Vallecillo Antonio. “Una Introducción a los Perfiles UML, Consultado en Internet” http://www.lcc.uma.es/~av/Publicaciones/04/ UMLProfiles-Novatica04.pdf. [20] Jacobson, Ivar; Booch, Grady; Rumbaugh, James. “El proceso unificado de desarrollo de software.”, Addison Wesley. Madrid, ES. 2000. 438 p [21] Veronika Stefanov, Beate List, Birgit Korherr. “Extending UML 2 Activity Diagrams withc Business Intelligence Objects” Edwar Javier Herrera Osorio (05/10/1977), systems engineer, Universidad Distrital 2004, specialist in database development, foundation university of Bogotá Jorge Tadeo Lozano, 2007, Master candidate in systems engineering and computer 2008, Universidad de Colombia.
