Online Analytical Processing Queries Research Articles

Design and optimization of multidimensional data models for enhanced OLAP query performance and data analysis

This paper explores the design and optimization of multidimensional data models to enhance the query performance and data analysis capabilities of OLAP (Online Analytical Processing) systems. It delves into three prominent dimensional modeling techniques: Star Schema, Snowflake Schema, and Galaxy Schema, analyzing their impact on query complexity, data redundancy, storage requirements, and ease of maintenance. Additionally, it examines three aggregation strategiesPre-Aggregation, Dynamic Aggregation, and Hybrid Aggregationfocusing on their effectiveness in balancing query response time, storage efficiency, flexibility, and computational cost. The study further investigates performance optimization techniques, including query optimization, partitioning, and materialized views, providing case studies and experimental data to illustrate their benefits and challenges. The findings underscore the importance of tailored optimization strategies in OLAP systems to meet varying business needs and query patterns, highlighting the trade-offs between performance gains, storage requirements, and implementation complexity

Temporal Graph Cube

Data warehouse and OLAP (Online Analytical Processing) are effective tools for decision support on traditional relational data and static multidimensional network data. However, many real-world multidimensional networks are often modeled as temporal multidimensional networks, where the edges in the network are associated with temporal information. Such temporal multidimensional networks typically cannot be handled by traditional data warehouse and OLAP techniques. To fill this gap, we propose a novel data warehouse model, named <inline-formula xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink"><tex-math notation="LaTeX">$\mathsf {Temporal{\kern3.0pt}Graph{\kern3.0pt}Cube}$</tex-math></inline-formula> , to support OLAP queries on temporal multidimensional networks. Through supporting OLAP queries in any time range, users can obtain summarized information of the network in the time range of interest, which cannot be derived by using traditional static graph OLAP techniques. We propose a segment-tree based indexing technique to speed up the OLAP queries, and also develop an index-updating technique to maintain the index when the temporal multidimensional network evolves over time. In addition, we also propose a novel concept called <inline-formula xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink"><tex-math notation="LaTeX">$\mathsf {similarity{\kern3.0pt}of{\kern3.0pt}snapshots}$</tex-math></inline-formula> which shows a strong correlation with the efficiency of indexing technique and can provide a good reference on the necessity of building the index. The results of extensive experiments on two large real-world datasets demonstrate the effectiveness and efficiency of the proposed method.

Decision-Tree-Based Horizontal Fragmentation Method for Data Warehouses

Data warehousing gives frameworks and means for enterprise administrators to methodically prepare, comprehend, and utilize the data to improve strategic decision-making skills. One of the principal challenges to data warehouse designers is fragmentation. Currently, several fragmentation approaches for data warehouses have been developed since this technique can decrease the OLAP (online analytical processing) query response time and it provides considerable benefits in table loading and maintenance tasks. In this paper, a horizontal fragmentation method, called FTree, that uses decision trees to fragment data warehouses is presented to take advantage of the effectiveness that this technique provides in classification. FTree determines the OLAP queries with major relevance, evaluates the predicates found in the workload, and according to this, builds the decision tree to select the horizontal fragmentation scheme. To verify that the design is correct, the SSB (star schema benchmark) was used in the first instance; later, a tourist data warehouse was built, and the fragmentation method was tested on it. The results of the experiments proved the efficacy of the method.

Building a novel physical design of a distributed big data warehouse over a Hadoop cluster to enhance OLAP cube query performance

Improving OLAP (Online Analytical Processing) query performance in a distributed system on top of Hadoop is a challenging task. An OLAP Cube query comprises several relational operations, such as selection, join, and group-by aggregation. It is well-known that star join and group-by aggregation are the most costly operations in a Hadoop database system. These operations indeed increase network traffic and may overflow memory; to overcome these difficulties, numerous partitioning and data load balancing techniques have been proposed in the literature. However, some issues remain questionable, such as decreasing the Spark stages and the network I/O for an OLAP query being executed on a distributed system. In a precedent work, we proposed a novel data placement strategy for a big data warehouse over a Hadoop cluster. This data warehouse schema enhances the projection, selection, and star-join operations of an OLAP query, such that the system’s query-optimizer can perform a star join process locally, in only one spark stage without a shuffle phase. Also, the system can skip loading unnecessary data blocks when executing the predicates. In this paper, we extend our previous work with further technical details and experiments, and we propose a new dynamic approach to improve the group-by aggregation. To evaluate our approach, we conduct some experiments on a cluster with 15 nodes. Experimental results show that our method outperforms existing approaches in terms of OLAP query evaluation time.

Beyond TPC-DS, a benchmark for Big Data OLAP systems (BDOLAP-Bench)

Online Analytical Processing (OLAP) systems with Big Data support allow storing tables of up to tens of billions of rows or terabytes of data. At the same time, these tools allow the execution of analytical queries with interactive response times, thus making them suitable for the implementation of Business Intelligence applications. However, since there can be significant differences in query and data loading performance between current Big Data OLAP tools, it is worthwhile to evaluate and compare them using a benchmark. But we identified that none of the existing approaches are really suitable for this type of system. To address this, in this research we propose a new benchmark specifically designed for Big Data OLAP systems and based on the widely adopted TPC-DS benchmark. To overcome TPC-DS inadequacy, we propose (i) a set of transformations to support the implementation of its sales data mart on any current Big Data OLAP system, (ii) a choice of 16 genuine OLAP queries, and (iii) an improved data maintenance performance metric. Moreover, we validated our benchmark through its implementation on four representative systems.

OLAP Patterns: A pattern-based approach to multidimensional data analysis

Users of a business intelligence (BI) system employ an approach referred to as online analytical processing (OLAP) to view multidimensional data from different perspectives. Query languages, e.g., SQL or MDX, allow for flexible querying of multidimensional data but query formulation is often time-consuming and cognitively challenging for many users. Alternatives to using a query language, e.g., graphical OLAP clients, parameterized reports, or dashboards, are often not a full-blown alternative to using a query language. Experience in cooperative research projects with industry led to the following observations regarding the use of OLAP queries in practice. First, within the same organization, similar OLAP queries are repeatedly composed from scratch in order to satisfy similar information needs. Second, across different organizations and even domains, OLAP queries with similar structures are repeatedly composed from scratch. Finally, vague requirements regarding frequently composed OLAP queries in the early stages of a project potentially lead to rushed development in later stages, which can be alleviated by following best practices for OLAP query composition. In engineering, knowledge about best-practice solutions to frequently arising challenges is often documented and represented using patterns. In that spirit, an OLAP pattern describes a generic solution for composing a query that allows a BI user to satisfy a certain type of information need given fragments of a conceptual model. This paper introduces a formal definition of OLAP patterns as well as an expressive, flexible, and generally applicable definition language.

Improving the Performance of Multidimensional Clinical Data for OLAP using an Optimized Data Clustering approach

Medicine is a fresh way to utilize for curing, analyzing and detecting the diseases through data clustering with OLAP (Online Analytical Processing). The large amount of multidimensional clinical data is reduced the efficiency of OLAP query processing by enhancing the query accessing time. Hence, the performance of OLAP model is improved by using data clustering in which huge data is divided into several groups (clusters) with cluster heads to achieve fast query processing in least time. In this paper, a Dragon Fly Optimization based Clustering (DFOC) approach is proposed to enhance the efficiency of data clustering by generating optimal clusters from multidimensional clinical data for OLAP. The results are evaluated on MATLAB 2019a tool and shown the better performance of DFOC against other clustering methods ACO, GA and K-Means in terms of intra-cluster distance, purity index, F-measure, and standard deviation

An Approach for Retrieving Faster Query Results From Data Warehouse Using Synonymous Materialized Queries

The enterprise data warehouse stores an enormous amount of data collected from multiple sources for analytical processing and strategic decision making. The analytical processing is done using online analytical processing (OLAP) queries where the performance in terms of result retrieval time is an important factor. The major existing approaches for retrieving results from a data warehouse are multidimensional data cubes and materialized views that incur more storage, processing, and maintenance costs. The present study strives to achieve a simpler and faster query result retrieval approach from data warehouse with reduced storage space and minimal maintenance cost. The execution time of frequent queries is saved in the present approach by storing their results for reuse when the query is fired next time. The executed OLAP queries are stored along with the query results and necessary metadata information in a relational database is referred as materialized query database (MQDB). The tables, fields, functions, relational operators, and criteria used in the input query are matched with those of stored query, and if they are found to be same, then the input query and the stored query are considered as a synonymous query. Further, the stored query is checked for incremental updates, and if no incremental updates are required, then the existing stored results are fetched from MQDB. On the other hand, if the stored query requires an incremental update of results, then the processing of only incremental result is considered from data marts. The performance of MQDB model is evaluated by comparing with the developed novel approach, and it is observed that, using MQDB, a significant reduction in query processing time is achieved as compared to the major existing approaches. The developed model will be useful for the organizations keeping their historical records in the data warehouse.

Building a Lung and Ovarian Cancer Data Warehouse.

ObjectivesDespite the collection of vast amounts of data by the healthcare sector, effective decision-making in medical practice is still challenging. Data warehousing technology can be applied for the collection and management of clinical data from various sources to provide meaningful insights for physicians and administrators. Cancer data are extremely complicated and massive; hence, a clinical data warehouse system can provide insights into prevention, diagnosis and treatment processes through the use of online analytical processing tools for the analysis of multi-dimensional data at different granularity levels.MethodsIn this study, a clinical data warehouse was developed for lung cancer data, which were kindly provided by the United States National Cancer Institute. Lung and ovarian cancer data were imported in specific formats and cleaned to remove errors and redundancies. SQL server integration services (SSIS) were used for the extract-transform-load (ETL) process.ResultsThe design of the clinical data warehouse responds efficiently to all types of queries by adopting the fact constellation schema model. Various online analytical processing queries can be expressed using the proposed approach.ConclusionsThis model succeeded in responding to complex queries, and the analysis of data is facilitated by using online analytical processing cubes and viewing multilevel data details.

Online analytical processsing on graph data

Online Analytical Processing (OLAP) comprises tools and algorithms that allow querying multidimensional databases. It is based on the multidimensional model, where data can be seen as a cube such that each cell contains one or more measures that can be aggregated along dimensions. In a Big Data scenario, traditional data warehousing and OLAP operations are clearly not sufficient to address current data analysis requirements, for example, social network analysis. Furthermore, OLAP operations and models can expand the possibilities of graph analysis beyond the traditional graph-based computation. Nevertheless, there is not much work on the problem of taking OLAP analysis to the graph data model. This paper proposes a formal multidimensional model for graph analysis, that considers the basic graph data, and also background information in the form of dimension hierarchies. The graphs in this model are node- and edge-labelled directed multi-hypergraphs, called graphoids, which can be defined at several different levels of granularity using the dimensions associated with them. Operations analogous to the ones used in typical OLAP over cubes are defined over graphoids. The paper presents a formal definition of the graphoid model for OLAP, proves that the typical OLAP operations on cubes can be expressed over the graphoid model, and shows that the classic data cube model is a particular case of the graphoid data model. Finally, a case study supports the claim that, for many kinds of OLAP-like analysis on graphs, the graphoid model works better than the typical relational OLAP alternative, and for the classic OLAP queries, it remains competitive.

Improve HR Decision-Making Based On Data Mart and OLAP

The organizations always need to manage their operations, process the data electronically, and find a platform that help them to support their strategic decisions. The success of the human resource department can reflect overall organizational success. The human resource department professionals try to ensure finding the right person at the right time for the job that fits the person according to skills and qualifications. This task needs a platform that supports making the right decision based on historical managerial information. The data mart is a departmental based decision support system that used departmental data to help decision-makers to support short term decisions. Human resource (HR) data mart is the base stone for building an enterprise data warehouse. The paper presents the implementation process of HR data mart starting from implementing data mart schema to online analytical processing (OLAP) reports. The data mart is implemented on retired employees’ data of Basra Oil Company for over 15 years. A human resource data mart can provide a base platform to perform a different analysis operation to support the right decisions. Different OLAP reports are implemented to help analysts and decision-makers to get the answers for their questions as OLAP queries. Two categories of reports are implemented offline reports using Microsoft Excel Pivot Table 2010 and web OLAP reports using SQL Server Reporting Service 2014 (SSRS). The tools used to implement data mart vary from SQL Server Management Services (SSMS) 2014, SQL Server Integration Service 2014 (SSIS), SQL Server Analytical Service 2014 (SSAS), SQL Server Reporting Service 2014 (SSRS), SQL Server Data Tools 2013 (SSDT), and Microsoft Excel Pivot Table 2010.

UnifyDR: A Generic Framework for Unifying Data and Replica Placement

The advent of (big) data management applications operating at Cloud scale has led to extensive research on the data placement problem. The key objective of data placement is to obtain a partitioning (possibly allowing for replicas) of a set of data-items into distributed nodes that minimizes the overall network communication cost. Although replication is intrinsic to data placement, it has seldom been studied in combination with the latter. On the contrary, most of the existing solutions treat them as two independent problems, and employ a two-phase approach: (1) data placement, followed by (2) replica placement. We address this by proposing a new paradigm, CDR , with the objective of c ombining d ata and r eplica placement as a single joint optimization problem. Specifically, we study two variants of the CDR problem: (1) CDR-Single , where the objective is to minimize the communication cost alone, and (2) CDR-Multi , which performs a multi-objective optimization to also minimize traffic and storage costs. To unify data and replica placement, we propose a generic framework called UnifyDR , which leverages overlapping correlation clustering to assign a data-item to multiple nodes, thereby facilitating data and replica placement to be performed jointly. We establish the generic nature of UnifyDR by portraying its ability to address the CDR problem in two real-world use-cases, that of join-intensive online analytical processing (OLAP) queries and a location-based online social network (OSN) service. The effectiveness and scalability of UnifyDR are showcased by experiments performed on data generated using the TPC-DS benchmark and a trace of the Gowalla OSN for the OLAP queries and OSN service use-case, respectively. Empirically, the presented approach obtains an improvement of approximately 35% in terms of the evaluated metrics and a speed-up of 8 times in comparison to state-of-the-art techniques.

StreamingCube: Seamless Integration of Stream Processing and OLAP Analysis

Many organizations require real-time analysis of their business data streams to make instantaneous decisions. Data streams are often multidimensional and at the lowest level of abstraction, yet analysts are interested in their multilevel interactive analyses across several dimensions. Online analytical processing (OLAP) is a technique whose utility has been proven for such an analysis. Traditionally, OLAP over streams has been achieved by coupling a stream processing engine (SPE) with an OLAP engine. However, for many organizations, this is not an effective solution as it results in lower performance, resource wastage, and increased complexity and maintenance costs. Hence, we present StreamingCube, which is a unified framework for stream processing and OLAP analysis. The idea of a unified framework is supported by the observation that the incremental computation in stream processing and the maintenance of the materialized view of OLAP have many similarities. To seamlessly integrate the SPE and OLAP, as well as to maintain the OLAP lattice vertices incrementally, a cubify operator is introduced. StreamingCube supports two types of queries, namely, continuous queries (CQs) and OLAP queries. To demonstrate the effectiveness of the proposed framework, a detailed experimental evaluation is presented herein.

Knowledge Graph OLAP

A knowledge graph (KG) represents real-world entities and their relationships. The represented knowledge is often context-dependent, leading to the construction of contextualized KGs. The multidimensional and hierarchical nature of context invites comparison with the OLAP cube model from multidimensional data analysis. Traditional systems for online analytical processing (OLAP) employ multidimensional models to represent numeric values for further analysis using dedicated query operations. In this paper, along with an adaptation of the OLAP cube model for KGs, we introduce an adaptation of the traditional OLAP query operations for the purposes of performing analysis over KGs. In particular, we decompose the roll-up operation from traditional OLAP into a merge and an abstraction operation. The merge operation corresponds to the selection of knowledge from different contexts whereas abstraction replaces entities with more general entities. The result of such a query is a more abstract, high-level view – a management summary – of the knowledge.

Enabling instant- and interval-based semantics in multidimensional data models: the T+MultiDim Model

Time is a vital facet of every human activity. Data warehouses, which are huge repositories of historical information, must provide analysts with rich mechanisms for managing the temporal aspects of information. In this paper, we (i) propose T+MultiDim, a multidimensional conceptual data model enabling both instant- and interval-based semantics over temporal dimensions, and (ii) provide suitable OLAP (On-Line Analytical Processing) operators for querying temporal information. T+MultiDim allows one to design typical concepts of a data warehouse including temporal dimensions, and provides one with the new possibility of conceptually connecting different temporal dimensions for exploiting temporally aggregated data. The proposed approach allows one to specify and to evaluate powerful OLAP queries over information from data warehouses. In particular, we define a set of OLAP operators to deal with interval-based temporal data. Such operators allow the user to derive new measure values associated to different intervals/instants, according to different temporal semantics. Moreover, we propose and discuss through examples from the healthcare domain the SQL specification of all the temporal OLAP operators we define.

OLAP parallel query processing in clouds with C‐ParGRES

SummaryThe advent of big data technologies has changed the way many companies manage their data. Several companies moved their data to the cloud using the concept of database‐as‐a‐service (DBaaS). Moving databases to the cloud presents several challenges related to flexible and scalable management of data. Although some of these companies migrated to NoSQL databases, most still rely on relational databases in the cloud to manage data, especially data that is critical to the decision making process. Online analytical processing (OLAP) queries take a long time to be processed, thus demanding high‐performance capabilities from their associated database systems to get results in a feasible time. In this article, we propose a middleware solution that can be deployed in any cloud provider, named C‐ParGRES, which explores database replication and interquery and intraquery parallelism to efficiently support OLAP queries in the cloud. C‐ParGRES is an extension of ParGRES, an open‐source database cluster middleware for high‐performance OLAP query processing in clusters. C‐ParGRES exploits cloud capabilities such as on‐demand resource provisioning and elasticity. In addition, C‐ParGRES can create multiple and independent virtual clusters for different database and users. We evaluate C‐ParGRES with two real‐world OLAP applications, both from the Brazilian Institute of Geography and Statistics. Results show that C‐ParGRES is a cost‐effective solution for OLAP query processing in the cloud.

Approximate Query Processing: What is New and Where to Go?

Online analytical processing (OLAP) is a core functionality in database systems. The performance of OLAP is crucial to make online decisions in many applications. However, it is rather costly to support OLAP on large datasets, especially big data, and the methods that compute exact answers cannot meet the high-performance requirement. To alleviate this problem, approximate query processing (AQP) has been proposed, which aims to find an approximate answer as close as to the exact answer efficiently. Existing AQP techniques can be broadly categorized into two categories. (1) Online aggregation: select samples online and use these samples to answer OLAP queries. (2) Offline synopses generation: generate synopses offline based on a-priori knowledge (e.g., data statistics or query workload) and use these synopses to answer OLAP queries. We discuss the research challenges in AQP and summarize existing techniques to address these challenges. In addition, we review how to use AQP to support other complex data types, e.g., spatial data and trajectory data, and support other applications, e.g., data visualization and data cleaning. We also introduce existing AQP systems and summarize their advantages and limitations. Lastly, we provide research challenges and opportunities of AQP. We believe that the survey can help the partitioners to understand existing AQP techniques and select appropriate methods in their applications.

Integration of FPGAs in Database Management Systems: Challenges and Opportunities

In the presence of exponential growth of the data produced every day in volume, velocity, and variety, online analytical processing (OLAP) is becoming increasingly challenging. FPGAs offer hardware reconfiguration to enable query-specific pipelined and parallel data processing with the potential of maximizing throughput, speedup as well as energy and resource efficiency. However, dynamically configuring hardware accelerators to match a given OLAP query is a complex task. Furthermore, resource limitations restrict the coverage of OLAP operators. As a consequence, query optimization through partitioning the processing onto components of heterogeneous hardware/software systems seems a promising direction. While there exists work on operator placement for heterogeneous systems, it mainly targets systems combining multi-core CPUs with GPUs. However, an inclusion of FPGAs, which uniquely offer efficient and high-throughput pipelined processing at the expense of potential reconfiguration overheads, is still an open problem. We postulate that this challenge can only be met in a scalable fashion when providing a cooperative optimization between global and FPGA-specific optimizers. We demonstrate how this is addressed in two current research projects on FPGA-based query processing.

HypDB

On line analytical processing (OLAP) is an essential element of decision-support systems. However, OLAP queries can be biased and lead to perplexing and incorrect insights. In this demo, we present HypDB, the first system to detect, explain and resolve bias in OLAP queries. Our demonstration, shows several examples of OLAP queries from real world datasets that are biased and could lead to statistical anomalies such as Simpson's paradox. Then, we demonstrate step-by-step how HypDB: (1) detects whether an OLAP query is biased, (2) explains the root causes of the bias and reveals illuminating insights about the domain and the data collection process and (3) eliminates the bias via query rewriting and generates decision-support insights.

CobWeb Multidimensional Model and Tag-Cloud Operators for OLAP of Documents

There is an explosion in the amount of textual documents that have been generated and stored in recent years. Effective management of these documents is essential for better exploitation in decisional analyses. In this context, the authors propose their CobWeb multidimensional model based on standard facets and dedicated to the OLAP (on-line analytical processing) of XML documents; it aims to provide decision makers with facilities for expressing their analytical queries. Secondly, they suggest new visualization operators for OLAP query results by introducing the concept of Tag clouds as a means to help decision-makers to display OLAP results in an intuitive format and focus on main concepts. The authors have developed a software prototype called MQF (Multidimensional Query based on Facets) to support their proposals and then tested it on documents from the PubMed collection.