A Comprehensive Analysis of Materialized Views in a Data Warehouse Environment

In a data warehouse (DW) environment, when the operational environment does not posses or does not want to inform the data about the changes that occurred, controls have to be implemented to enable detection of these changes and to reflect them in the DW environment. The main scenarios are: i) the impossibility to instrument the DBMS (triggers, transaction log, stored procedures, replication, materialized views, old and new versions of data, etc) due to security policies, data property or performance issues; ii) the lack of instrumentation resources on the DBMS; iii) the use of legacy technologies such file systems or semi-structured data; iv) application proprietary databases and ERP systems. In another article [1], we presented the development and implementation of a technique that was derived for the comparison of database snapshots, where we use signatures to mark and detect changes. The technique is simple and can be applied to all four scenarios above. To prove the efficiency of our technique, in this article we do comparative performance tests between these approaches. We performed two benchmarks: the first one using synthetic data and the second one using the real data from a case study in the data warehouse project developed for Rio Sul Airlines, a regional aviation company belonging to the Brazil-based Varig group. We also describe the main approaches to solve the detection of changes in data origin.KeywordsPerformance TestData WarehouseOrigin AreaCleaning ManagementView MaintenanceThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

In this paper, we briefly examine the background to data warehousing, and the rationale for implementing such a system. While there are several issues involved in the implementation of a data warehouse project, this paper concentrates on the aspect of developing a corporate data model. A particular design process for an enterprise data model proposed by [SCHE92] is examined. A model is then presented for sharing data across applications on a common platform, such as a data warehouse, using the design process outlined. The basis of the argument in favour of data sharing in this manner is firstly, interfaces will already have been defined between the operational database systems and the data warehouse. Secondly, all the data from the operational systems already populate the data warehouse tables. Thirdly, the process of preparing data for migration to the data warehouse will already have solved many of the problems associated with enterprise data modeling, such as determining exactly where a data item resides and what exactly it means. Finally, data sharing can be achieved without having to develop micro-level models of the whole enterprise.

Maintaining and evolving data warehouses is a complex, error prone, and time consuming activity. The main reason for this state of affairs is that the environment of a data warehouse is in constant change, while the warehouse itself needs to provide a stable and consistent interface to information spanning extended periods of time. In this paper, we propose a modeling technique for data warehousing, called anchor modeling, that offers non-destructive extensibility mechanisms, thereby enabling robust and flexible management of changes in source systems. A key benefit of anchor modeling is that changes in a data warehouse environment only require extensions, not modifications, to the data warehouse. This ensures that existing data warehouse applications will remain unaffected by the evolution of the data warehouse, i.e. existing views and functions will not have to be modified as a result of changes in the warehouse model.

During the development of the data warehouse (DW), too much data is transformed, integrated, structured, cleansed, and grouped in a single structure that is the DW. These various types of changes could lead to data corruption or data manipulation. Therefore, DW testing is a very critical stage in the DW development process.A number of attempts were made to describe how the testing process should take place in the DW environment. In this paper, I will state briefly these testing approaches, and then a proposed matrix will be used to evaluate and compare these approaches. Afterwards, I will highlight the weakness points that exist in the available DW testing approaches. Finally, I will describe how I will fill the gap in the DW testing in my PhD by developing a DW Testing Framework presenting briefly its architecture. Then, I will state the scope of work that I am planning to address and what type of limitations that exist in this area that I am expecting to experience. In the end, I will conclude my work and state possible future work in the field of DW testing.

In order to facilitate efficient query processing, the information contained in data warehouses is typically stored as a set of materialized views. Deciding which views to materialize represent a challenge in order to minimize view maintenance and query processing costs. Some existing approaches are applicable only for small problems, which are far from reality. In this paper we introduce a new approach for materialized view selection using Parallel Simulated Annealing (PSA) that selects views from an input Multiple View Processing Plan (MVPP). With PSA, we are able to perform view selection on MVPPs having hundreds of queries and thousands of views. Also, in our experimental study we show that our method provides a significant improvement in the quality of the obtained set of materialized views over existing heuristic and sequential simulated annealing algorithms.

With the growth of information technology and computer networks, there is a vital need for optimal design of distributed databases with the aim of performance improvement in terms of minimizing the round-trip response time and query transmission and processing costs. To address this issue, new fragmentation, data allocation, and replication techniques are required. In this paper, we propose enhanced vertical fragmentation, allocation, and replication schemes to improve the performance of distributed database systems. The proposed fragmentation scheme clusters highly-bonded attributes (i.e., normally accessed together) into a single fragment in order to minimize the query processing cost. The allocation scheme is proposed to find an optimized allocation to minimize the round-trip response time. The replication scheme partially replicates the fragments to increase the local execution of queries in a way that minimizes the cost of transmitting replicas to the sites. Experimental results show that, on average, the proposed schemes reduce the round-trip response time of queries by 23% and query processing cost by 15%, as compared to the related work.

In Data warehouse (DW) environment, Extraction-Transformation-Loading (ETL) processes constitute the integration layer which aims to pull data from data sources to targets, via a set of transformations. ETL is responsible for the extraction of data, their cleaning, conforming and loading into the target. At the beginning of DW project, one of the most important tasks is to construct conceptual design of ETL processes. Several solutions have been proposed to support ETL designer in this task. Nevertheless few methods have accompanied these proposals. In this paper, we present a method for modeling and organizing ETL processes based on our experience in real world. The proposed approach takes four inputs and produces a conceptual model of ETL processes using a graphical notation of our framework KANTARA.

Efficient storage and handling of data stored in a peer-to-peer (P2P) network, proves vital for various applications such as query processing and data mining. This paper presents a distributed, scalable and robust layered overlay (L-overlay) to index and manage multidimensional data in a dynamic P2P network. The proposed method distinguishes between the data and peer layers, with efficient mapping between the two. The data is organized such that semantically similar data objects are accessed hastily. Grid and tree structures are proposed for the peer layer. As application examples of L-overlay in query processing and data mining, k-nearest neighbors query processing and distributed Naive Bayes classification algorithms, are proposed. We show the effectiveness of our scheme in static and dynamic environments using simulation. L-overlay is shown to be more efficient than SSW, an available semantic overly, in terms of maintenance and query processing costs.

Random sampling is a popular technique for providing fast approximate query answers, especially in data warehouse environments. Compared to other types of synopses, random sampling bears the advantage of retaining the dataset’s dimensionality; it also associates probabilistic error bounds with the query results. Most of the available sampling techniques focus on table-level sampling, that is, they produce a sample of only a single database table. Queries that contain joins over multiple tables cannot be answered with such samples because join results on random samples are often small and skewed. On the contrary, schema-level sampling techniques by design support queries containing joins. In this paper, we introduce Linked Bernoulli Synopses, a schema-level sampling scheme based upon the well-known Join Synopses. Both schemes rely on the idea of maintaining foreign-key integrity in the synopses; they are therefore suited to process queries containing arbitrary foreign-key joins. In contrast to Join Synopses, however, Linked Bernoulli Synopses correlate the sampling processes of the different tables in the database so as to minimize the space overhead, without destroying the uniformity of the individual samples. We also discuss how to compute Linked Bernoulli Synopses which maximize the effective sampling fraction for a given memory budget. The computation of the optimum solution is often computationally prohibitive so that approximate solutions are needed. We propose a simple heuristic approach which is fast and seems to produce close-to-optimum results in practice. We conclude the paper with an evaluation of our methods on both synthetic and real-world datasets.KeywordsSampling FractionReference TableSchema GraphReference ProbabilitySpace ConsumptionThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Schema merging is the process of incorporating data models into an integrated, consistent schema from which query solutions satisfying all incorporated models can be derived. The efficiency of such a process is reliant on the effective semantic representation of the chosen data models, as well as the mapping relationships between the elements of the source data models. Consider a scenario where, as a result of company mergers or acquisitions, a number of related, but possible disparate data marts need to be integrated into a global data warehouse. The ability to retrieve data across these disparate, but related, data marts poses an important challenge. Intuitively, forming an all-inclusive data warehouse includes the tedious tasks of identifying related fact and dimension table attributes, as well as the design of a schema merge algorithm for the integration. Additionally, the evaluation of the combined set of correct answers to queries, likely to be independently posed to such data marts, becomes difficult to achieve. Model management refers to a high-level, abstract programming language designed to efficiently manipulate schemas and mappings. Particularly, model management operations such as match, compose mappings, apply functions and merge, offer a way to handle the above-mentioned data integration problem within the domain of data warehousing. In this research, we introduce a methodology for the integration of star schema source data marts into a single consolidated data warehouse based on model management. In our methodology, we discuss the development of three (3) main streamlined steps to facilitate the generation of a global data warehouse. That is, we adopt techniques for deriving attribute correspondences, and for schema mapping discovery. Finally, we formulate and design a merge algorithm, based on multidimensional star schemas; which is primarily the core contribution of this research. Our approach focuses on delivering a polynomial time solution needed for the expected volume of data and its associated large-scale query processing. The experimental evaluation shows that an integrated schema, alongside instance data, can be derived based on the type of mappings adopted in the mapping discovery step. The adoption of Global-And-Local-As-View (GLAV) mapping models delivered a maximally-contained or exact representation of all fact and dimensional instance data tuples needed in query processing on the integrated data warehouse. Additionally, different forms of conflicts, such as semantic conflicts for related or unrelated dimension entities, and descriptive conflicts for differing attribute data types, were encountered and resolved in the developed solution. Finally, this research has highlighted some critical and inherent issues regarding functional dependencies in mapping models, integrity constraints at the source data marts, and multi-valued dimension attributes. These issues were encountered during the integration of the source data marts, as it has been the case of evaluating the queries processed on the merged data warehouse as against that on the independent data marts.

To integrate various Linked Datasets, data warehousing and live query processing provide two extremes for optimized response time and quality respectively. The first approach provides very fast responses but with low-quality because changes of original data are not immediately reflected on materialized data. The second approach provides accurate responses but it is notorious for long response times. A hybrid SPARQL query processor provides a middle ground between two specified extremes by splitting the triple patterns of SPARQL query between live and local processors based on a predetermined coherence threshold specified by the administrator. Considering quality requirements while splitting the SPARQL query, enables the processor to eliminate the unnecessary live execution and releases resources for other queries. This requires estimating the quality of response provided with current materialized data, compare it with user requirements and determine the most selective sub-queries which can boost the response quality up to the specified level with least computational complexity. In this work, we propose solutions for estimating the freshness of materialized data, as one dimension of the quality, by extending cardinality estimation techniques. Experimental results show that we can estimate the freshness of materialized data with a low error rate.KeywordsFreshness estimationHybrid SPARQL QueryingRDF Data WarehouseView Materialization

The approaches to data warehouse design are based on the assumption that source data are known in advance and available. While this assumption is true in common project situations, in some peculiar contexts it is not. This is the case of the French national project for analysis of energetic agricultural farms, that is the case study of this paper. Here, the above-mentioned methods can hardly be applied because source data can only be identified and collected once user requirements indicate a need. Besides, the users involved in this project found it very hard to express their analysis needs in abstract terms, i.e., without visualizing sample results of queries, which in turn would require availability of source data. To solve this deadlock we propose ProtOLAP, a tool-assisted fast prototyping methodology that enables quick and reliable test and validation of data warehouse schemata in situations where data supply is collected on users' demand and users' ICT skills are minimal. To this end, users manually feed sample realistic data into a prototype created by designers, then they access and explore these sample data using pivot tables to validate the prototype.

Big data represents a new technology for managing data with high velocity, volume, variety and contributes to creating value for companies. As quoted in1, capturing all queries made on the company website or from customer support calls, emails or chat lines, regardless of their outcome, may have significant value in identifying emerging trends. The Big Data Era has largely contributed in accelerating the development strategic plans issued from governments and research organisms, coving the management, exploitation and analysis of these data by taking into account the different V's of Big Data. Among these plans, we can cite for instance the development of: (a) large-scale platforms (ex. data-clusters, distributed data clusters), (b) Software Defined Environments (SDE) (ex. IBM SDE), (c) advanced programming paradigms (ex. map-reduce, Spark, etc.), Data Analytics Tools (Rapid Miner, Google Fusion Tables, Solver), (d) Visualization tools (Google Chart, Tableau, Oracle Visual Analyzer), and (h) high quality and valuable Knowledge Bases (KB), constructed either by academicians (e.g., Cvc, DBpedia, Freebase, and YAGO) and industrials (e.g., Google Knowledge Graph, Facebook Knowledge Graph, Amazon Knowledge Graph, Credit Rating Agencies, Enterprise Knowledge Base, etc.). In this talk, we would like to foster the creation of a think tank dedicated to getting the best from Big Data V's and the efforts related to it to revisit our research activities without compromising them. In this talk, we would like to share the experience conducted with our Model and Data Engineering Team of the LIAS Laboratory at ISAE-ENSMA, which aims at the design of data warehousing applications. Based on the literature, this design is based on two main approaches: (i) a supply-driven approach (also called data-driven) that starts with an analysis of operational data sources in order to identify all the available data and (ii) a user-driven approach (also known as requirement-driven or goal-orientated) which stems from the determination of the information requirements of different business users. Several studies and experiments show that resorting to these two approaches entails a high risk for companies, since some functional requirements cannot be satisfied. This is due to the lack of relevant data in sources. In parallel, reference studies have identified the crucial role of knowledge bases (KB) for analytical tasks, by offering analysts more entities (people, places, products, etc.). The availability of a huge, high quality valuable KB is an asset for data warehousing designers and decision-makers to construct/exploit a valuable data warehouse. So, faced with this situation, we here present a value-driven approach that revisits the traditional life cycle of the design of data warehouses, by considering KB as an external resource. These different phases are illustrated via the YAGO KB.

Conceptual modeling is widely recognized to be the necessary foundation for building a database that is well-documented and fully satisfies the user requirements. In particular, from the designer point of view the availability of a conceptual model provides a higher level of abstraction in describing the warehousing process and its architecture in all its aspects. Typically conceptual models rely on a graphical notation that facilitates writing, understanding, and managing conceptual schemata by both designers and users. The Entity/Relationship (E/R) model (Chen, 1976) is widespread in the enterprises as a conceptual formalism to provide standard documentation for relational information systems; nevertheless, as E/R is oriented to support queries that navigate associations between data rather than synthesize them, it is not well-suited for data warehousing (Kimball, 1998). Actually, the E/R model has enough expressivity to represent most concepts necessary for modeling a Data Warehouse (DW); on the other hand, in its basic form, it is not able to properly emphasize the key aspects of the multidimensional model, so that its usage for DWs is expensive from the point of view of the graphical notation and not intuitive (Rizzi, 2006). Some designers claim that star schemata are expressive enough for conceptual modeling. Actually, a star schema is just a (denormalized) relational schema, so it merely defines a set of relations and integrity constraints. Using star schema for conceptual modeling is like starting to build a complex software by writing the code, without the support of any static, functional, or dynamic model, which typically leads to very poor results from the points of view of adherence to user requirements, maintenance, and reuse. For all these reasons, in the last few years the research literature has proposed several original approaches for modeling a DW, some based on extensions of known conceptual formalisms (e.g. E/R, Unified Modeling Language (UML)), some based on ad hoc ones. Remarkably, a comparison of the different models made by Abello (2006) pointed out that, abstracting from their graphical form, the core expressivity is similar, thus proving that the academic community reached an informal agreement on the required expressivity. This paper discusses the expressivity of an ad hoc conceptual model, the Dimensional Fact Model (DFM), in order to let the user verify the usefulness of a conceptual modeling step in DW design. After a brief listing of the main conceptual model proposals, the basic and advanced features in DW conceptual modeling are introduced and described by examples. Finally, the current trends in DW conceptual modeling are reported and the conclusions are drawn.

Research in data warehousing and OLAP has produced important technologies for the design, management and use of information systems for decision support. Much of the interest and success in this area can be attributed to the need for software and tools to improve data management and analysis given the large amounts of information that are being accumulated in corporate as well as scientific databases.However, even though the high maturity of these technologies, new data needs or applications currently run at companies not only demand more capacity, but also new methods, models, techniques or architectures to satisfy these new needs. Some of the hot topics in data warehouses (DWs) include distributed DWs, advanced OLAP for business intelligence, web warehouses, DWs for new applications such as XML documents, stream data, spatial or GIS data or biomedical data. Moreover, there are other aspects very developed in other software areas such as security or quality, which still remain uncovered by current design methods or technologies for DWs.Like the previous successful DOLAP workshops held in conjunction with CIKMs, the eighth edition of the Workshop on Data Warehousing and OLAP (DOLAP'05) aims to synergistically connect the research community and industry practitioners. It provides an international forum where both researchers and practitioners can share their findings in theoretical foundations, current methodologies, and practical experiences. This year, DOLAP'05 will be specially focused on new research directions and emerging application domains in the areas of data warehousing and OLAP.This year, we received papers from 18 different countries distributed over all continents such as The Netherlands, France, Spain, Israel, Korea, USA, Canada and Argentine. We received 31 submissions and, after a careful review, only 12 papers were selected by the Program Committee, making an acceptance rate of 38.7%.These proceedings contain the papers selected for presentation at the workshop. The accepted papers were presented in 5 sessions: (i) querying OLAP databases, (ii) data warehouse models, (iii, iv) data warehouse design, and (v) query processing and view maintenance. A keynote address was given by Jens Lechtenburger on Schema transformations. We hope that these proceedings will serve as a valuable reference for data warehousing and OLAP researchers and practitioners.