Improved View Selection Algorithm Using SOM and 0/1 Knapsack

A data warehouse is designed for answering analytical queries. These queries are usually long, complex and ad hoc in nature and their response time is high when processed against an exponentially growing data warehouse. Materialising views has been found to be an effective tool for reducing this response time. All views cannot be materialised on account of resource constraints. Further, optimal view selection is shown to be an NP-complete problem. This necessitates selection of an appropriate set of views, from amongst all possible views that reduce the query response time. Most of the view selection algorithms are greedy or evolutionary. In this paper, a differential evolution view selection algorithm (DEVSA) that selects the Top-K views from a multi-dimensional lattice is proposed. Further, it is shown that DEVSA when compared to the greedy, genetic and memetic-based view selection algorithms selects comparatively better quality views for higher dimensional datasets.

A data warehouse is a central repository of historical data designed primarily to support analytical processing. These analytical queries are exploratory, long and complex in nature. Further, the rapid and continuous growth in the size of data warehouse increases the response times of such queries. Query response times need to be reduced in order to speedup decision making. This problem, being an NP-Complete problem, can be appropriately dealt with by using swarm intelligence techniques. One such technique, i.e. the set-based particle swarm optimization (SPSO), has been proposed to address this problem. Accordingly, a SPSO based view selection algorithm (SPSOVSA), which selects the Top-K views from a multidimensional lattice, is proposed. Experimental based comparison of SPSOVSA with the most fundamental view selection algorithm shows that SPSOVSA is able to select comparatively better quality Top-K views for materialization. The materialization of these selected views would improve the performance of analytical queries and lead to efficient decision making.

ABSTRACTThe traditional query optimization method is to reduce the size of the intermediate relationship as much as possible when calculating the multi relation connection. It does not take into account the mass of data in the data warehouse, which is read mainly and the fact table is generally indexed often cannot obtain the disadvantage of the optimal effect, and puts forward the data warehouse suitable for frequent additions and deletions query environment. Multi connection query optimization in data warehouse, MCQODW) method: By using multiple linear regression algorithm to write the detection sequence of data, and then to optimize the data warehouse multi connection query without changing the sequence of other query detection sequences, in order to reduce repeated calculation. Theoretical analysis and experimental results show that the algorithm reduces significantly in the data warehouse multi join query optimization and execution time, and the method is effective.

A data warehouse contain a large amount of historical data to support decision making. The response time for decision making queries, which are usually long and complex, is high when processed against a large data warehouse This problem can be addressed by identifying and storing the relevant and required information as materialized views so that most future queries can be answered using them without accessing the large data warehouse. The approach presented in this paper attempts to identify such information using the queries posed in the past as they can serve as useful indicators of the queries likely to be posed in future. The approach uses queries posed in the past to create subject specific domains in a data warehouse. This is followed by identifying the frequent queries from amongst them. These frequent queries provide information that is highly likely to be accessed by future queries. Materialized views constructed using these queries would be capable of answering most future queries and thereby facilitate in decision making.

Exploratory data analysis tries to discover novel dependencies and unexpected patterns in large databases. Traditionally, this process is manual and hypothesis-driven. However, analysts can come short of patience and imagination. In this paper, we introduce Claude, a hypothesis generator for data warehouses. Claude follows a 2-step approach: (1) It detects interesting views, by exploiting non-linear statistical dependencies between the dimensions and the measure. (2) To explain its findings, it detects local patterns in these views and describes them with SQL queries. Technically, we derive a model of interestingness from fundamental information theory. To exploit this model, we present aggressive approximations and heuristics, allowing Claude to be fast and more accurate than state-of-art view selection algorithms.

Data warehouse is an inseparable part of modern business but the amount of data it stores is huge and growing so fast, the queries of the business leaders it processes have become very sophisticated as well. Data warehouse needs to response to those queries with high quality data having less response time but the major challenge for that is the scale of data and nature of the queries involved. It’s very hard to access the required data set with minimum response time in data processing as the data warehouse needs to perform analytical operation on a large set of data analyzing sophisticated queries. This paper introduces a new data warehousing schema that constructs a multidimensional relation tree hierarchy, changing the traditional use of relational database, with partitioned database in its every level instead of any individual data. This schema allows data warehouse to partition and process a large data part by part in a small batch to minimize the response time and stores summarized data to increase data availability in query processing. Allowing easy and fast access to the required set of data, this schema improves the performance and the decision making ability of the data warehouse.

A view selection algorithm takes as input a fact table and computes a set of views to store in order to speed up queries. The performance of view selection algorithm is usually measured by three criteria: (1) the amount of memory to store the selected views, (2) the query response time and (3) the time complexity of this algorithm. The two first measurements deal with the output of the algorithm. No existing solutions give good trade-off between amount of memory and queries cost with a small time complexity. We propose in this paper an algorithm guaranteeing a constant approximation factor of queries response time with respect to the optimal solution. Moreover, the time complexity for a D-dimensional fact table is O (D * 2D) corresponding to the fastest known algorithm. We provide an experimental comparison with two other well known algorithms showing that our approach also gives good performance in terms of memory.

The contemporary landscape of data warehousing is undergoing a revolutionary transformation propelled by the integration of Artificial Intelligence (AI). This paper explores the intersection of AI and data warehousing, unraveling the dynamics that fuel this revolution and examining the profound implications for businesses and data management practices. The traditional paradigm of data warehousing has relied on structured data and predefined schemas, limiting its adaptability to the dynamic nature of modern datasets. The advent of AI injects a new dimension, enabling data warehouses to evolve into intelligent, adaptive entities capable of handling diverse data types, volumes, and velocities. This abstract encapsulates the essence of the research, delving into key themes that define the AI-driven revolution in data warehousing. The paper begins by surveying the historical trajectory of data warehousing, highlighting the challenges posed by the increasing complexity and heterogeneity of contemporary data sources. As businesses grapple with unstructured data, streaming data, and the need for real-time insights, the limitations of traditional data warehousing architectures become apparent. The introduction of AI-driven solutions revolutionizes data warehousing in several dimensions. Machine learning algorithms are harnessed for automating data integration, cleansing, and transformation processes, mitigating the manual labor associated with traditional ETL (Extract, Transform, Load) methods. Deep learning techniques, such as neural networks, unlock the potential to uncover complex patterns within massive datasets, enhancing predictive analytics and decision support capabilities. Moreover, the abstract explores the role of AI in enabling self-optimizing data warehouses. Adaptive query optimization, automated indexing, and real-time performance tuning emerge as pivotal components, ensuring that data warehouses evolve in response to changing workloads and user patterns. Ethical considerations and responsible AI practices within the context of data warehousing are also addressed. The abstract concludes by underlining the transformative impact of AI-driven solutions on the efficiency, agility, and strategic value of data warehousing, offering a glimpse into the future where intelligent data warehouses play a central role in shaping data-driven enterprises.

One of the important research and technological issues in data warehouse performance is the optimization of analytical queries. Most of the research have been focusing on optimizing such queries by means of materialized views, data and index partitioning, as well as various index structures including: join indexes, bitmap join indexes, multidimensional indexes or index-based multidimensional clusters. These structures neither well support navigation along dimension hierarchies nor optimize joins with the Time dimension, which in practice is used in the majority of analytical queries. In this chapter we overview the basic index structures, namely: a bitmap index, a join index, and a bitmap join index. Based on these indexes, we show how to build another index, called Time-HOBI, for optimizing queries that address the Time dimension and compute aggregates along dimension hierarchies. We further discuss the extension of the index with additional data structure for storing aggregate values along the hierarchical structure of the index. The aggregates are used for speeding up aggregate queries along dimension hierarchies. Furthermore, we show how the index is used for answering queries in an example data warehouse. Finally, we discuss its performance-related characteristics, based on experiments.

From the Publisher: Data warehouses have captured the attention of practitioners and researchers alike. But the design and optimization of data warehouses remains an art rather than a science. This book presents a comparative review of the state of the art and best current practice of data warehouses. It covers source and data integration, multidimensional aggregation, query optimization, update propagation, metadata management, quality assessment, and design optimization. Also, based on results of the European Data Warehouse Quality project, it offers a conceptual framework by which the architecture and quality of data warehouse efforts can be assessed and improved using enriched metadata management combined with advanced techniques from databases, business modeling, and artificial intelligence. For researchers and database professionals in academia and industry, the book offers an excellent introduction to the issues of quality and metadata usage in the context of data warehouses.

Schema versioning in data warehouses: Enabling cross-version querying via schema augmentation

Data warehouse store historical records on which analysis queries are executed. Data warehouse are populated from the transaction database through a process called as Extract Transform and Load (ETL). Extract is to identify the transaction database records to be moved to the data warehouse. Transform is to make the records normalized to suit the data warehouse environment. Load is to actually store the records in the data warehouse. Today business wants to perform real time analysis of data. Availability of updated records in the data warehouse is necessary for real time analysis. This could be achieved through the process of near real time (ETL), i.e., to improve existing ETL process. Improvement to existing ETL could be achieved in many ways such as to increase the frequency of loads, to identify relevant changes that are required for analysis and move only those changes. Only through increasing the frequency of load would not be useful in many cases. Hence we should identify the changes and then move if they are useful for analysis making it smart ETL. In this paper we study three such techniques. First one is to create a replica of dimension table in data warehouse and move the changes to the replica this reduces the query response time. Second is to load the data in parallel so that loading time could be reduced. Third is to identify changes and trigger the loading process. We have used the first approach to create replica and loaded the data in parallel and observed that by loading in parallel not only does the loading time reduces but also the query response time. Then in the first approach is the query response time reduces to a certain limit methods suggest to move the data from replica dimensions to the original dimensions. Here we bring in right time trigger to move instead of just the query response time. We found that when we have a combined approach to query response time and right time trigger the number of moves are reduced.

Query optimization has been an active area of research ever since the first relational systems were implemented. In the last few years, research in the area has experienced renewed impulse, thanks to new development like data warehousing. In this article, we overview some of the recent advances made in complex query optimization. This article assumes knowledge of SQL and relational algebra, as well as the basics of query processing; in particular, the user is assumed to understand cost optimization of basic SQL blocks (Select-Project-Join queries). After explaining the basic unnesting approach to provide some background, we overview three complementary techniques: source and algebraic transformations (in particular, moving outerjoins and pushing down aggregates), query rewrite (materialized views), new indexing techniques (bitmap and join indices), and different methods to build the answer (online aggregation and sampling). Throughout this article, we will use subquery to refer to a nested SQL query and outer query to refer to an SQL query that contains a nested query. The TPCH benchmark database (TPC, n.d.) is used as a source of examples. This database includes (in ascending size order) tables Nation, Customer (with a foreign key for Nation), Order (with a foreign key for Customer), and Lineitem (with a foreign key for Order). All attributes from a table are prefixed with the initial of the table’s name (“c_” for Customer, and so on).

Chapter 9 - A Data Warehouse Strategy for on-Demand Multiscale Mapping

Data is an important asset and a fundamental requirement for building valuable information for organizations. Association of Information Systems Students of Unsika (Himsika) as a university organization provides many events to develop student’s academic and professional skills. A post-event evaluation through a feedback survey was conducted and stored in Google Sheets spreadsheet format. However, the current analysis process using spreadsheets lacks standardization, making it difficult to compare satisfaction rates over time and between events. Additionally, the lack of standardization leads to semi-structured data on spreadsheets, with varying question formats and meanings. To address these limitations, implementing a centralized data warehouse is proposed as a solution. The data warehouse would provide a structured and standardized approach to analyzing event feedback, enabling better comparisons and evaluation of management quality within Himsika. The research aims to design a data warehouse that supports multidimensional analysis. As a way to simplify and optimize analytical queries, the data structure is standardized in the data warehouse. The Four-step Dimensional Design method is applied in designing dimensional modeling on the data warehouse, consisting of four stages including selecting the business process, declaring the grain, identifying the dimensions, and identifying the facts. The design process resulted in 4 dimensions of events, dim_instances, dim_degree_programs, and dim_professions, and a fact table called fact_rates_by_responses. Overall, the proposed data warehouse and dimensional modeling approach aim to enhance the analysis and evaluation of Himsika’s events.