Two main problems arise in modelling data warehouse structures. The first consists in establishing an adequate representation of dimensions in order to facilitate and to control the analysis operations. The second relates to the modelling of various types of architecture. Research work dedicated to the first problem has been conducted, and adequate solutions have been proposed. The second problem has not received so much attention. However, there is a need to apprehend complex structures interconnecting dimensions and facts in various ways. In this paper, we propose a model through which dimensions at different levels can be shared between different facts and various relationships between these facts can be described. Using this model, we then define the notion of well-formed warehouse structures.
A review of literature pertaining to data warehouse implementations over the last eight years has been undertaken. It was found that the views of data warehouse practitioners in particular have changed over the period’s pre and post 2000, to the extent that fewer authors place any emphasis on the need for a clear business purpose before embarking on a data warehouse project. Notably, pre 2000, there was no reference to the need to deliver a return on data warehouse investment, but today it appears to be critical that data warehouses prove their financial value. However, data warehouses do have strategic and therefore long term value for an organization. The case study review in this paper supports the notion of strategic alignment but it is the mapping of their experiences to the configuration school of strategic management that explains the degree of success.
Geographic Information Systems (GIS) showed their insufficiencies in front of complex requests for decision-makers. Resulting of the association of the databases and the decision-making systems the decisional data processing was developed since the beginning of the 90th as a new way. The decisional databases thus emerged in order to answer the specific needs for OnLine Analytical Processing (OLAP) and data mining. Extensions were made to make appropriate the analysis and the algorithms to specificities of the handled spatial data. This paper describes the modeling and implementation of Spatial Data Mart (SDM). We define a formal framework for the progressive construction of spatial data warehouses by assembling these SDM. Our approach includes a meta model for SDM construction. The construction is done in accordance with the UML meta model. After the validation step, construction is followed by an automatic generation of the spatial data mart in Spatial Oracle. A CASE tool, called CASME (Computer Aided Spatial Mart Engineering), constitutes the interface through which the user will have to carry out the process.
Defining data placement and allocation in the disk subsystem can have a significant impact on data warehouse performance. However, our experiences with data warehouse implementations show that the database storage layout is often subject to vague or even invalid assumptions about I/O performance trade-offs. Clear guidelines for the assignment of database objects to disks are a very common request from data warehouse DBAs and consultants. We review best practices suggested by storage and database vendors, and present two sets of performance measurements that compare storage layout alternatives and their implications. The first set used a TPC-H benchmark workload with DB2 UDB, the other a star schema/star join scenario with IBM Red Brick Data Warehouse.
This paper has been supported by a grant from the State Secretary of Education and Universities (Spanish Ministry of Education, Culture and Sport).
In former Releases of the Oracle Database, multidimensional data types did not exist. In typical Warehouse situations, especially if more advanced analytics was needed, an additional database had to be chosen to store and analyze the data. These databases offered a lot functionality for advanced analytics like what-if analysis, statistical queries and so on and an excellent query performance due to their optimized physical storage concept. But, on the other hand, these databases had some issues regarding size, integration and manageability. In Oracle 9i Release 2, both technologies have been integrated in one database to achieve both optimal results for relational and multidimensional data . This document describes how the multidimensional Data types are stored in the database and could be accessed using SQL and PL/SQL.
Data Warehouse-(DW) logical design often start with a conceptual schema and then generates relational structures. Applying this approach implies to cope with two main aspects: (i) -mapping the conceptual model structures to the logical model ones, and (ii) -taking into account implementation issues, which are not considered in the conceptual schema. This paper addresses this second aspect and presents a formalism that allows the DW designer to specify design guidelines which express design strategies related with implementation requirements. Through these guidelines the designer states high level manners to cope with different design problems, for example: managing complex and big dimensions, dimension versioning, different user profiles accessing to different attributes, high summarized data, horizontal partitions of historical data, generic dimensionality and non-additive measures. This work is part of a DW logical design environment, where the design guidelines are specified through a graphical editor and then automatically processed in order to build the logical schema.
On-Line Analytical Processing (OLAP) is a powerful method for analysing large data warehouse data. Typically, the data for an OLAP database is collected from a set of data repositories such as e.g. operational databases. This data set is often huge, and it may not be known in advance what data is required and when to perform the desired data analysis tasks. Sometimes it may happen that some parts of the data are only needed occasionally. Therefore, storing all data to the OLAP database and keeping this database constantly up-to-date is not only a highly demanding task but it also may be overkill in practice. This suggests that in some applications it would be more feasible to form the OLAP cubes only when they are actually needed. However, the OLAP cube construction can be a slow process. Thus, we present a system that applies Grid technologies to distribute the computation. As the data sources may well be heterogeneous, we propose an XML language for data collection. The user’s definition for a OLAP new cube often includes selecting and aggregating the data. In our system this computation is distributed to the computers that store the original data. This reduces the network traffic and speeds up the computation that is now performed in parallel. The sub results are sent back to the ’collecting server’. Usually, the results do not arrive simultaneously. However, the collecting server starts to process a sub result immediately after it has arrived. Therefore, there is no need to wait that all sub result are received. We have implemented a prototype for the system. The implementation applies Spitfire software and Mobile Analyzer technology. They both are Grid based products applying Grid Security Infrastructure.
The input data for a data warehouse, coming from operational systems, are not immediately ready for loading into data warehouse. It may need cleaning and integration with other data. Moreover, the input data must be transformed and translated into a format more suitable for analytical purposes. This paper presents a generalised platform for population of data warehouses named Data Warehouse Population Platform (DWPP), a set of modules whose aim is to resolve typical aspects arising during the transformation and loading vast amount of data into data warehouse. Using DWPP modules application developers have to add-on only domain specific transformation logic and integrate all in a new Population System. In Telecom Italia Lab we have realised several Population Systems based on DWPP. The Case Study section describes one of them: Population System for Mobile Network Traffic Data Warehouse.
Data warehousing has captured the attention of practitioners and researchers for a long time, whereas aspects of data quality is one of the crucial issues in data warehousing. Still, ensuring high level data quality is one of the most expensive and time-consuming tasks to perform in data warehousing projects. Many data warehouse projects are discontinued due to insufficient data quality. The following article describes an approach for managing data quality in data warehouse systems through a metadata based data quality system. The results are integrated in a comprehensive management approach and are based on practical experiences within a Swiss bank.
The popularity of data warehouses for analysis of data has grown tremendously, but much of the creation of data warehouses is done manually. We propose and illustrate algorithms for automatic conceptual schema development and evaluation. Our creation algorithm uses an enterprise schema of an operational database as a starting point for source-driven data warehouse schema design. Candidate conceptual schemas are created using the ME/R model, extended to note where additional user input can be used to further refine a schema. Our evaluation algorithm follows a user-driven requirements approach that utilizes queries to guide selection of candidate schemas most likely to meet user needs. In addition, we propose a guideline of manual steps to refine a conceptual schema to suit additional user needs, for example, the level of detail needed for date fields. The algorithms are illustrated using the TPC-H Benchmark schema and queries. Our algorithms provide a foundation for a software tool to create and evaluate data warehouse conceptual schemas.
Extraction-Transformation-Loading (ETL) tools are pieces of software responsible for the extraction of data from several sources, their cleansing, customization and insertion into a data warehouse. In this paper, we focus on the logical design of the ETL scenario of a data warehouse. Based on a formal logical model that includes the data stores, activities and their constituent parts, we model an ETL scenario as a graph, which we call the Architecture Graph. We model all the aforementioned entities as nodes and four different kinds of relationships (instance-of, part-of, regulator and provider relationships) as edges. In addition, we provide simple graph transformations that reduce the complexity of the graph. Finally, in order to support the engineering of the design and the evolution of the warehouse, we introduce specific importance metrics, namely dependence and responsibility, to measure the degree to which entities are bound to each other.
Date Warehousing is a powerful tool for supporting decision-making processes in modern corporations. However, developing a Data Warehouse (DW) is a complex and costly activity. It requires strategies, which should be specific to the characteristics and needs of the organization where it will be introduced. This work presents a DW development methodology suitable to organizational environments which need Data Warehousing support as a strategic requirement, but have to use the internal staff which has no expertise in DW development. Moreover, a strategy for creating and managing metadata in an integrated way to the DW development process is proposed, in order to capture the organizational knowledge and minimize the problems which can be caused by the mobility of employees within and between organizations.
A methodology recently proposed to improve processing of star queries on data warehouses is the clustering and indexing of fact tables using their multidimensional hierarchies [DRSN98, MRB99, KS01]. Due to this improved organization schema, processing of aggregation star queries changes dramatically creating new optimization opportunities. An important optimization technique is the so-called pre-grouping transformation. Although this transformation is expected to improve the query-processing plan in most cases, there are several cases where it is not beneficial. In this paper we attempt to apply a cost-based method for the optimal application of the pre-grouping transformation. Taking into consideration the special characteristics of our domain we identify the most suitable algorithms for the operations related to pre-grouping and derive detailed cost formulas for them. When proper statistical information is available the method can decide (1) whether or not to use the pre-grouping transformation and (2) which combination of algorithms to use for the various operations involved.
The challenge of extending database systems for decision support applications has been thetopic of much recent research—a very incomplete list of previous work includes [11, 8, 12, 4,10, 5]. Yet, there is no generally accepted solution for the problem, which remains a criticalone, since the inability of current DBMSs to support data mining applications is well-testedand clearly documented [12].Our research approach in addressing this difficult problem is motivated by the observa-tion that aggregate functions provide the linchpin for most decision support computations;moreover inductive discovery from large data sets can be viewed as the process of aggregat-ing low level data into statistical summaries of semantic significance. Therefore, the ATLaSsystem designed at UCLA [2] allows end-users to define new powerful aggregate functionsby writing them in SQL. The same mechanism can be used to define new table functionsin ATLaS, whose name stands for Aggregate & Table Language and System. ATLaS is thesuccessor of the AXL system described in [15].These SQL-based native extension mechanisms turn ATLaS into a powerful and flexiblesystem for advanced data-intensive applications, including applications from many domainsthat are not supported well by current Object-Relational database systems, which stillsuffer from limited extensibility. In fact, the only extensibility mechanism now providedby Object-Relational systems relies on nonnative datablades—i.e., on external functionsdefined in a procedural language and imported into SQL.ATLaS is very effective at expressing decision support tasks: we demonstrate this prop-erty by the efficient implementation of several functions, such as rollups, datacubes, clas-sifiers, and frequent item sets for association rules [2]. The performance of these functionsexpressed in ATLaS is typically within 30% of the performance of the same algorithmscoded in C/C++. To achieve this level of performance, ATLaS supports various optimiza-tion techniques, and the ability of manipulating in-memory tables in SQL. In fact, attributesof reference type in such tables allow the efficient support of data structures, such as tries,that are instrumental in implementing data mining algorithms, such as Apriori [1, 7].The stream-oriented computation model used by ATLaS contrasts with the computa-tion model based on ‘blocking’ semantics that is normally used for aggregates in currentdatabase systems. Thus, online aggregates [9], time-series queries [13], sliding-window ag-gregates, approximate aggregates, and continuous queries [3] are naturally supported inATLaS. Furthermore, important properties of an ATLaS program, such as blocking behav-ior and monotonicity, can be easily inferred from the syntactic structure of the program [14].
User participation has become paramount for any successful data warehouse initiative. In this paper, we present MD2, a tool based on the dimensional data modeling approach, which facilitates the user participation in the development of a data warehouse application. MD2 assists users on identifying their analytical needs in order to help data warehouse designers to better specify business requirements and latter translate them into appropriate design elements. The tool is supported by a data repository that helps gather metadata used to build the application dimensional schema and to specify reports that can be generated from the data warehouse and visualized through a Web interface.
