IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
International Journal of Research in Information Technology (IJRIT)
www.ijrit.com
ISSN 2001-5569
Study of Data Warehouse Modeling and its different design approaches Dr. Saurabh Shrivastava1, Ms. Palak Vaish2 1
Professor, Department of Mathematical Sciences and Computer Applications, Bundelkhand University Jhansi, Uttar Pradesh, India
[email protected] 2
Research Scholar, Mewar University Chittorgarh, Rajasthan, India
[email protected]
Abstract Data warehouse is basically an integrated, non-volatile, time-variant collection of data to enable strategic decisionmaking through online analytical processing techniques (OLAP) and online transaction processing (OLTP). It is derived from operational data through processes like requirements identification, analysis, design of dimensional model, testing and maintenance. In order to make a successful and useful data warehouse, design phase is most critical.Though multidimensional data models and star schema are relevant for warehouse design, but they fail in case of operational data as multidimensional modeling involves complex design techniques. This paper would deal with issues regarding models for designing data warehouse, their interoperability, applications and design for new architectures. Keywords-multidimensional modeling, data warehouse design, OLAP, OLTP
1. Introduction The business organizations need to store huge amount of data both past and current for which they need a well organized database that can not only facilitate the transaction processing but also a help in evaluating future strategy and decision support. Data warehouses store huge amount of information from multiple data sources which is used for query and analysis [40]. Therefore, the data is stored in the multidimensional (M D) structure [31] which stores information into dimensions and facts. The database design consists of following five phases•
Analysis of Operational systems- in which the information concerning the pre- existing operational system is collected through the designer and people involved in managing the information system. It produces logical or conceptual schemes of either the whole or part of the information system as output.
•
Requirements Collection and Filtering- It involves the users and designer of the DW, and produces in output the specifications concerning the choice of facts and dimensions etc. Requirement gathering can happen as Joint Application Development (JAD) sessions where multiple people are talking about the project scope in the same meeting. or as one-to-one meetings. Identification of user requirements also involve other details such as data source identification,
Dr. Saurabh Shrivastava, IJRIT
536
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
hardware sizing information, training requirements and most importantly, a concrete project plan indicating the finishing date of the data warehousing project. •
Conceptual Design- Conceptual design aims at deriving an expressive and implementationindependent conceptual schema for the DW, according to the chosen conceptual model, starting from the user requirements and from the structure of the source databases.
•
Logical Design- takes as input conceptual schema and creates a corresponding detailed logical schema on the chosen platform by considering some set of constraints. Logical design takes the conceptual schema and creates a corresponding logical schema on the chosen platform by considering some set of constraints (e.g., query answering time or concerning disk space).
•
Physical Design- considers indexing and allocation issues related to the tools chosen for implementation. It addresses all the issues specifically related to the suite of tools chosen for implementation – such as indexing and allocation (e.g., [23]).
Multidimensional modeling requires specialized design techniques that is somewhat similar to traditional database design methods [5] as shown in table 1 TABLE 1. DATABASE DESIGN METHODS Step Analysis Operational System
Input Information
Output Database schemes
Requirements collection
Database scheme
Data warehouse specifications
Conceptual design
Data warehouse specifications
Conceptual schema
Logical design
Conceptual schema
Logical schema
Physical Design
Logical schema
Physical schema
of
A data warehouse is an integrated database that meets all the demands of different business organizations. It is a subject oriented database as it gives information about a particular subject rather than the companies’ ongoing process. Data warehouses are also time stamped in nature as all data stored within the data warehouse is identified with a particular time period. Data once stored in the data warehouse is never removed, only transaction and addition of data is possible which helps the management to get a consistent picture of the business. Data warehouses are prevalently characterized by OLAP workload and it provides the users with applications and data access tools that are appropriate for their needs. Data warehouses represent the latest great paradigm of database management as the earliest data management systems were hierarchical, and were used primarily for archival purposes but with the adoption of relational database systems, operational applications like online transaction processing (O.L.T.P.), for example, to operate networks automated teller machine are possible now. So, Data warehouses now commonly run on client/server networks of personal computers and more powerful server machines. These latest systems are used for online analytical processing (O.L.A.P.). Dr. Saurabh Shrivastava, IJRIT
537
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
2. Data Warehouse Architecture Architecture of data warehousing systems has many diversifications varying from an ODS (operational data store), multiple data marts, small number of data sources to dozens of data sources. In general, all data warehouse systems have the following layers: • • • • • • • • •
Data Source Layer Data Extraction Layer Staging Area ETL Layer Data Storage Layer Data Logic Layer Data Presentation Layer Metadata Layer System Operations Layer
Data Source Layer It represents the different data sources that enter data into the data warehouse. Data source like relational database, plain text file, other types of database, Excel file, etc., can all act as a data source. Many different types of data can be a data source: •
Operations — such as HR data, sales data, product data, marketing data, systems data, inventory data etc.
•
Internal market research data.
•
Web server logs with user browsing data.
All these data sources together form the Data Source Layer.
Data Extraction Layer At this layer data gets pulled from the data source into the data warehouse system. Staging Area This is where data is prior to being scrubbed and transformed into a data warehouse / data mart. ETL Layer This layer is where data cleansing happens and data gains its logic applied to transform the data from a transactional nature to an analytical nature. Data Storage Layer This is where the cleansed and transformed data occurs. On the basis of scope and functionality, 3 types of entities can be found here: data warehouse, data mart, and operational data store (ODS). In an organization, you may have just one of the three, two of the three, or all three types. Dr. Saurabh Shrivastava, IJRIT
538
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
Data Logic Layer This is where business rules are stored which do not affect the underlying data transformation rules, but do affect what the report looks like. Data Presentation Layer This refers to the information that reaches the users. which can be in a form of an emailed report that gets automatically generated and sent every day, a graphical /tabular report in a browser, or an alert that warns users of exceptions, among others. Usually an OLAP Tool is used in this layer. Metadata Layer This is where information about the data stored in the data warehouse system is stored for example- a logical data model. A metadata tool is often to used to manage metadata. System Operations Layer This layer includes information on how the data warehouse system operates, such as ETL job status, system performance, and user access history.
Fig.1 Data Warehouse Architecture
After the tools and team personnel selections are made, the data warehouse design can begin. The following are the typical steps involved in the data warehousing project cycle. •
Requirement Gathering
•
Physical Environment Setup
•
Data Modeling
Dr. Saurabh Shrivastava, IJRIT
539
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
•
ETL
•
OLAP Cube Design
•
Front End Development
•
Report Development
•
Performance Tuning
•
Query Optimization
•
Quality Assurance
•
Rolling out to Production
•
Production Maintenance
•
Incremental Enhancements
3. Conceptual data warehouse modeling Conceptual modeling is a high level of abstraction of warehousing process and architecture in all its aspects so as to achieve independence of implementation issues. It is mainly recognized as an essential foundation for building a database that is well-documented and fully satisfies the user requirements since, it relies on a graphical notation that facilitates writing, understanding, and managing conceptual schemata by both users and designers. A conceptual data model identifies the highest-level relationships between the different entities. Some of its features are: • • •
No attribute is specified. Includes the important entities and the relationships among them. No primary key is specified.
Fig.2 Conceptual Data Model The only information shown via the conceptual data model is the entities that describe the data and the relationships between those entities. No other information is shown through the conceptual data model. Dr. Saurabh Shrivastava, IJRIT
540
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
In the literature, conceptual modeling for DWs has been tackled from mainly two points of view so far:
•
Multidimensional modeling-
Multidimensional modeling represents data under the metaphor of a cube whose cells store events that occurred in the business domain. Using the multidimensional model for DWs has a two-fold benefit. On the one hand, it supports performance improvement as its simple structure allows designers to predict users’ intentions and on the other hand it is close to the way of thinking of data analyzers and, therefore, it helps users understand data. The existing approaches may be framed into three categories: extensions to UML (e.g., [1, 38]), extensions to the Entity-Relationship model (e.g, [10, 6]), and ad hoc models (e.g., [21, 3]). While all models share the basic concepts of the multidimensional model to be represented, they significantly differ in representing more advanced concepts such as many-to-many associations, irregular hierarchies, and additivity. •
Modeling of ETL-
The focus is to model the ETL process either from the static [7], functional [32] or the dynamic [20] point of view. Though the research work in it is less mature than that on multidimensional modeling, yet it will prove to improve the overall reliability of the design process and on reducing its duration.
4. Problems with Conceptual Modeling Though a lot of work has been done in the field of conceptual modeling, there are still some issues open, as detailed in the following:-
4.1 No fixed standard All vendors propose their own proprietary standards as there are no fixed standards for conceptual design so far because of following reasons- (i) commercial CASE tools currently enable designers to directly draw logical schemata automatically, (ii) no agreement from both the research and industrial communities about which are the most relevant multidimensional properties to be modeled and (iii) the translation from conceptual to logical is incomplete although the conceptual models devised are semantically rich as some of the modeled properties cannot be expressed in the target logical models.
4.2 Security Among the different aspects of security in conceptual design for data warehouse, confidentiality (i.e., ensuring that only authorized or privileged users can access data) is particularly relevant, because business information is very sensitive and can be discovered by executing a simple query. Unfortunately, the classical security model used in transactional databases – centered on tables, rows, and attributes is unsuitable for DWs. Thus, an appropriate model should replace the classical security model focusing on the main concepts of multidimensional modeling – such as dimensions, facts, and measures – and tightly integrated with the conceptual model adopted. Information security should be considered not in isolation but during all stages of the development life-cycle i.e. from requirement analysis to implementation and maintenance. Despite of having ADAPTed UML[42] and UML extension [9], there are still security issues for which following measures must be taken like assigning privileges and roles to users to avoid conflicts between different authorization rules, methodology for transforming security models from the conceptual level into the logical level, and then into concrete implementations in target commercial platforms and devise a Dr. Saurabh Shrivastava, IJRIT
541
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
reliable and flexible security model that comprehensively considers all the components of the warehousing architecture, including ETL and data sources.
5. Logical Modeling Once the conceptual modeling is done, logical design starts in which conceptual schema is transformed into a logical schema which is then optimized to be implemented on a chosen target system. In multidimensional modeling, where target database systems are relational implementations, the so-called snowflake star, and constellation schemata are widely accepted by various vendors to manage data cubes. Several efficient multidimensional data structures such as dwarfs [47, 48], condensed cubes [45, 2], and QC-Trees have been proposed to manage data cubes. Expressive logical models help to achieve a streamlined design process that guarantees quality criteria, reduction of sparsity [12, 35]), avoidance of inconsistent queries, control over null values, and seriously takes security into account. It fills the gap between advanced conceptual data models and relational or multidimensional implementations of data cubes. A logical data model describes the data in as much detail as possible, irrespective to how they will be physical implemented in the database. Features of a logical data model include: • • • • •
The primary key for each entity is specified. Includes all entities and relationships among them. Normalization occurs at this level. All attributes for each entity are specified. Foreign keys (keys identifying the relationship between different entities) are specified.
The steps for designing the logical data model are as follows: 1. 2. 3. 4. 5.
Specify primary keys for all entities. Find the relationships between different entities. Find all attributes for each entity. Resolve many-to-many relationships. Normalization.
Dr. Saurabh Shrivastava, IJRIT
542
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
Fig.3 Example of Logical Data Model
6. Differences between Conceptual and Logical data model •
In a logical data model, all attributes within an entity are specified whereas no attributes are specified in a conceptual data model.
•
In a logical data model, primary keys are present, whereas in a conceptual data model there is no primary key.
•
Relationships between entities are specified using primary keys and foreign keys in a logical data model whereas in a conceptual data model, the relationships are simply stated, not specified. Thus we simply know that two entities are related, but we do not specify what attributes are used for this relationship.
7. Physical Modeling Physical data model represents how the model will be implemented in the database. A physical database model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables. Its features are: • • • • •
On the basis of user requirements denormalization may occur. All tables and columns are specified. Foreign keys are used to identify relationships between tables. Physical data model is different for different RDBMS. For example, data type for a column may be different between SQL Server and MySQL. Physical data model may be quite different from the logical data model due to physical considerations.
The steps for physical data model design are as follows: Dr. Saurabh Shrivastava, IJRIT
543
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
• • • •
Convert entities into tables. Convert relationships into foreign keys. Convert attributes into columns. Modify the physical data model based on physical constraints / requirements.
8. Differences between Logical and Physical data model •
Data type for each column is specified. Data types can be different depending on the actual database being used.
•
Entity names are now table names.
•
Attributes are now column names.
Fig.4 Example of Physical Data Model TABLE 2. COMARISON OF DATA WAREHOUSE MODELS Feature
Conceptual Logical
Entity Names
✓
✓
Entity Relationships
✓
✓
Physical
Attributes
✓
Primary Keys
✓
✓
Foreign Keys
✓
✓
Table Names
✓
Column Names
✓
Column Data Types
✓
Dr. Saurabh Shrivastava, IJRIT
544
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
9. Design for new data warehouse architectures and applications Advanced architectures for business intelligence are emergingto support new kinds of applications, possibly involving new and more complex data types•
Web Warehousing
Web warehouses are data warehouses that collect Web data. The main challenges in this field are how to automate the process of conceptual design when some or most data sources reside on the Web and how to integrate heterogeneous web sources. Some attempts have been made in this direction, mainly aimed at the design of the Web warehouse driven by frequent user queries [15,18] and by data quality and building a conceptual schema from XML data [27, 4]. •
Spatial Data Warehouse-
They are characterized by a strong emphasis on spatial data, coming in the form of spatial measures or spatial dimensions. All existing conceptual models support basic modeling of a spatial dimension like most business DWs include a location data represented in an alphanumeric format or a geographic hierarchy built on customers [67, 57]. Finally, as concerns design methods, adequate solutions for properly moving from conceptual to logical schemata through spatial information must be devised. •
Distributed Data Warehousing
in distributed data warehousing a new phase needs to be added to the design method: the one for designing the distribution, from both the physical points and architectural view. Distribution is particularly useful in contexts where new data marts are often added, typically because of company mergers or acquisitions. • Business Process Monitoring It provides Real-time data warehousing and an integrated view of an enterprise as here information is ready and complete not later than required by the decision-making process. BPM architectures typically include dashboards for inferenceengines and viewing key performance indicators (KPIs) for managing business rules with the aim of giving the decision maker an accurate and timely picture of the business.
10. Conclusion In this paper an attempt has been made to study different designs of data warehouse based on their framework, architecture, schema used and techniques. Data warehouses are the most important decision support systems and are thus exploited using analysis techniques like OLAP multidimensional queries and complex SQL queries. There are a number of challenges in developing a data warehouses like presence of redundant and inconsistent data, data selection from multiple source systems, complexity and volume of data, and the non availability of comprehensive testing bed. New techniques for indexing, database design, interactive querying and view maintenance is needed to support the development of data warehouses. By the study, it can be finally interpreted that object oriented multidimensional approach is the one of the best [17] as it satisfies all the criteria required for data warehouse design and is more adaptable to changes in user requirements, star and snowflake schemas are also efficient for data warehousing design and UML is easy to model all real world objects.
References [1] A. Abell´o, J. Samos, and F. Saltor. YAM2: a multidimensional conceptual model extending UML. Information Systems, 31(6):541–567, 2006. [2] B. Chen, L. Chen, Y. Lin, and R. Ramakrishnan, Prediction cubes. In Proc. VLDB, pages 982–993, 2005. Dr. Saurabh Shrivastava, IJRIT
545
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
[3] B. H¨usemann, J. Lechtenb¨orger, and G. Vossen. Conceptual data warehouse design. In Proc. DMDW, pages 3–9, 2000. [4] B. Vrdoljak, M. Banek, and S. Rizzi. Designing web warehouses from XML schemas. In Proc. DaWaK, pages 89– 98, 2003. [5] Chaudhuri, S, Dayal Umeshwar, ”An Overview of Data Warehousing and OLAP Technology” , in ACM Sigmod Record, March 1997 [6] C. Sapia, M. Blaschka, G. H¨ofling, and B. Dinter. Extending the E/R model for the multidimensional paradigm. In Proc. ER Workshop on Data Warehousing and Data Mining, pages 105–116, 1998. [7] D. Calvanese, G. De Giacomo, M. Lenzerini, D. Nardi, and R. Rosati, Information integration: Conceptual modeling and reasoning support. In Proc. CoopIS, pages 280–291, 1998. [8] D. Quass, A. Gupta, I. S. Mumick, and J. Widom Making views self-maintainable for data warehousing. In Proc. Int. Conf. on Parallel and Distributed Information Systems, pages 158–169, 1996. [9] E. Fernandez-Medina, J. Trujillo, R. Villaroel, and M. Piattini, Extending UML for designing secure data warehouses. In Decision Support Systems,. In press, 2006. [10] E. Franconi and A. Kamble (2004), A data warehouse conceptual data model. In Proc. SSDBM, pages 435–436. [11] J. Han. Towards on-line analytical mining in large databases. ACM SIGMOD Records, 27(1):97–107, 1998. [12] J. Lechtenb¨orger and G. Vossen. Multidimensional normal forms for data warehouse design. Information Systems, 28(5):415–434, 2003. [13] J. Park and C. Hwang. A design and practical use of spatial data warehouse. In Proc. IEEE Int. Geoscience and Remote Sensing Symp., pages 726–729, 2005. [14] J. Trujillo, S. Luj´an-Mora, and E. Medina. The Gold model case tool: An environment for designing OLAP applications. In Proc. ICEIS, pages 699–707, 2002. [15] J. Zhang, T. W. Ling, R. Bruckner, and A. M. Tjoa. Building XML data warehouse based on frequent patterns in user queries. In Proc. DaWaK, pages 99–108, 2003. [16] J. Zubcoff and J. Trujillo. Extending the UML for designing association rule mining models for data warehouses. In Proc. DaWaK, pages 11–21, 2005. [17] Luj´n-Mora S., Trujillo J., and Song, I., “A UML profile for multidimensional modeling in data warehouses,” Data Knowl. Eng. 59(3) 725–769.
[18] L. I. Rusu, J. W. Rahayu, and D. Taniar. A methodology for building XML data warehouses. Int. Jour. of Data Warehousing and Mining, 1(2), 2005. [19] L. Zhang, Y. Li, F. Rao, X. Yu, Y. Chen, and D. Liu. An approach to enabling spatial OLAP by aggregating on spatial hierarchy. In Proc. DaWaK, volume 2737, pages 35–44, 2003. [20] M. Bouzeghoub, F. Fabret, and M. Matulovic. Modeling data warehouse refreshment process as a workflow application. In Proc. DMDW, 1999 [21] M. Golfarelli, D. Maio, and S. Rizzi. The Dimensional Fact Model: A conceptual model for data warehouses, Int. Journ. of Coop. Inf. Syst., 7(2-3):215–247, 1998. [22] M. Golfarelli, V. Maniezzo, and S. Rizzi. Materialization of fragmented views in multidimensional databases. Data & Knowledge Engineering, 49(3):325–351, 2004. [23] M. Golfarelli and S. Rizzi. A methodological framework for data warehouse design. In Proc. DOLAP, pages 3–9, 1998. Dr. Saurabh Shrivastava, IJRIT
546
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
[24] M. Golfarelli and S. Rizzi. WAND: A CASE tool for data warehouse design. In Proc. ICDE, pages 7–9, 2001. [25] M. Kaya and R. Alhajj. Fuzzy OLAP association rules mining based novel approach for multiagent cooperative learning. In Int. Conf. on Industrial & Engin. Appl. of AI & Expert Syst., pages 56–65, 2004. [26] McKendric Joseph, A NEW DIMENSION TO DATA WAREHOUSING: 2011 IOUG DATA WAREHOUSING SURVEY, Produced by Unisphere Research, a Division of Information Today, Inc. September 2011. [27] M. R. Jensen, T. H. Møller, and T. B. Pedersen. Converting XML DTDs to UML diagrams for conceptual data integration. Data & Knowledge Engineering, 44(3):323–346, 2003. [28] Ming Chuan Wu and Alejandro P. Buchmann, “Research issues in data warehousing”, 1996. [29] M. Scotch and B. Parmano. SOVAT: Spatial OLAP visualization and analysis tool. In Proc. HICSS, 2005. [30] OMG. Common warehouse metamodel specification. http://www.omg.org/, 2004. [31] Ponniah, P, Data Warehousing Fundamentals: A Comprehensive Guide for IT Professionals, pp 402, 2001. [32] P. Vassiliadis, A. Simitsis, and S. Skiadopoulos. Conceptual modeling for ETL processes. In Proc. DOLAP, pages 14–21, 2002. [33] R. Kirkg¨oze, N. Katic, M. Stolda, and A. M. Tjoa. A security concept for OLAP. In Proc. DEXA, pages 619–626, 1997. [34] S. Chaudhuri, Data mining and database systems: Where is the intersection? IEEE Data Engineering Bulletin, 21(1):4–8, 1998. [35] S. Gardner. Building the data warehouse. Comm. ACM, 41(9):52–60, 1998. [36] S. Jajodia and D. Wijesekera. Securing OLAP data cubes against privacy breaches. In Proc. IEEE Symp. on Security and Privacy, pages 161–178, 2004. [37] S. Luj´an-Mora and J. Trujillo. A comprehensive method for data warehouse design. In Proc. DMDW, 2003. [38] S. Luj´an-Mora, J. Trujillo, and I. Song. A UML profile for multidimensional modeling in data warehouses. In Data & Knowledge Engineering, 2006. In press. [39] Stefano Rizzi, Matteo Golfarelli,D.Maio, “A Survey on Temporal Data Warehousing”, International Journal of Data Warehousing & Mining, 5(1), 1-17, January-March 2009 [40] Stefano Rizzi, Matteo Golfarelli,D.Maio, ”The Dimensional Fact Model:A Conceptual Model for Data Warehouses.” International Journal of Cooperative Information Systems(IJC IS),7(2-3):215-247, 1998. [41] S. Rizzi et al. Towards a logical model for patterns. In Proc. ER, pages 77–90, 2003. [42] T. Priebe and G. Pernul. A pragmatic approach to conceptual modeling of OLAP security. In Proc. ER, pages 311– 324, 2000. [43] T. Vetterli, A. Vaduva, and M. Staudt. Metadata standards for data warehousing: Open Information Model vs. Common Warehouse Metamodel. ACM SIGMOD Records, 3(23), 2000. [44] W. Lehner, J. Albrecht, H. Wedekind, “Normal forms for multidimensional databases,” Proc. 10th SSDBM 1998, 63–72. [45] W. Wang, H. Lu, J. Feng, and J. X. Yu. Condensed cube: An efficient approach to reducing data cube size. In Proc. ICDE, pages 155–165, 2002. [46] X. Chen and I. Petrounias, Mining temporal features in association rules. In Proc. PKDD, pages 295–300, 1999. Dr. Saurabh Shrivastava, IJRIT
547
IJRIT International Journal of Research in Information Technology, Volume 2, Issue 9, September 2014, Pg. 536-548
[47] Y. Sismanis, A. Deligiannakis, Y. Kotidis, and N. Roussopoulos. Hierarchical dwarfs for the rollup cube. In Proc. DOLAP, pages 17–24, 2003. [48] Y. Sismanis and N. Roussopoulos. The complexity offully materialized coalesced cubes. In Proc. VLDB, pages 540–551, 2004.
Dr. Saurabh Shrivastava, IJRIT
548
