Online Analytical Processing Systems 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

Expanding the intellectual capabilities of OLAP technology using neural networks

The article highlights the main characteristics, features and structure of Online Analytical Processing systems based on the same technology that perform online analytical processing of data. This technology allows analysts to explore and navigate a multidimensional indicator structure called an online analytical processing cube (data cube). Indicators (measures) of data cube play an important role in the decision-making process. To solve certain problems, these measures often need to be classified or grouped. Moreover, empty measures are common in data cube. This fact negatively affects strategic decision making. Unfortunately, online analytical processing itself is not well suited for classifying, clustering, and predicting empty measures of data cube in the presence of large data. In this regard, today there is a need to use new technologies to solve such problems. Such technologies include neural networks. The article discusses the problem of integrating online analytical processing and a neural network, showing the possibilities and advantages of such integration. It mentions that in the case of big data, the integration of OLAP and neural networks is very effective in solving problems of classification, clustering and empty measure prediction of data cube. An architectural and technological model for the integration of online analytical processing and neural networks is presented.

Forester: Approximate Processing of an Imperative Procedure for Query-Time Exploratory Data Analysis in a Relational Database

Query-time Exploratory Data Analysis (qEDA) is an increasingly demanding aspect of the data analysis process that entails visually and quantitatively summarizing, comprehending, and interpreting the primary characteristics of a dataset. Nowadays, an imperative procedure is popular in relational databases for EDA because it enables us to write multiple dependent declarative queries with imperative logic. As online analytical processing (OLAP) systems contain extremely large datasets, data scientists often need quick visualizations of data, using approximate processing of imperative procedures, before analyzing them in their entirety. We identify gaps in the existing techniques, in that they are unable to sample both declarative-dependent statements and control logic at the same time and perform multi-dependent sampling-based approximate processing within the permitted time in qEDA. Traditional approximate query processing (AQP) involves tuple sampling for a single query approximation and enables queries to be executed over arbitrary random samples of tables. However, available AQP methods cannot produce a further representative sample of the data distribution for the dependent statements to estimate accurately and quickly for multiple dependent statements. On the other hand, sampling control structures, like loops and conditional statements, are discussed separately, without regard to the imperative structure of statements in a procedure. In this study, we propose Forester, a novel agile approximate processing method for imperative procedures that performs imperative program-aware sampling, which includes both statements with control regions (i.e., branch and loop) and processes them approximately within the permitted time in qEDA. Our method produces more targeted samples for each relation, while maintaining the data and control flow of dependent queries and imperative logic and determining all the conditions for a relation across all the statements in the sample that guarantee the existence of relevant data for dependent data distribution. Utilizing a workload of multi-statement imperative procedures from the Transaction Processing Performance Council Decision Support (TPC-DS) database, our experiment demonstrates that Forester outperforms the existing system in sampling, producing minimum error, and improving response time.

Efficient Approximation Framework for Attribute Recommendation

Trend analysis is a fundamental type of analytical query in online analytical processing (OLAP) systems. In trend analysis, a key step is to identify k valuable attributes whose distributions in two subsets under different predicates significantly differ for further investigation, where the difference is measured by metric functions. However, the exact solution that involves scanning all records is prohibitively expensive, particularly when handling large datasets in the era of big data. To minimize unnecessary data access, the existing state-of-the-art solution TopKAttr adopts sampling to avoid the expensive data scan. However, their solution still has two main drawbacks. Firstly, their solution is tailored only for two limited metric functions: the Earth Mover distance and Euclidean distance, and cannot be generalized to more complicated metric functions. Besides, their solution still aims to return the exact top-k answers via the sampling method, which still causes high running costs as shown in our experiment. Motivated by these limitations, we propose a general approximation framework for attribute recommendation that efficiently returns the top-k attributes with theoretical guarantees while supporting an extensive range of metric functions, such as the Kolmogorov-Smirnov test (KS-test), Chebyshev distance, the Earth Mover distance, Euclidean distance, and with the potential to more metrics. The key to our framework is a new bound estimation strategy that can be applied to a wide spectrum of metrics, as we listed above. Based on our estimation framework, we further devise an efficient approximation algorithm with theoretical guarantees to answer the top-k queries, which is widely used in attribute recommendation. Extensive experiments on four real large datasets show that our framework gains up to an order of magnitude speed-up and consistently high accuracy compared to TopKAttr, providing a promising alternative for attribute recommendation in OLAP systems.

Parallelism-Optimizing Data Placement for Faster Data-Parallel Computations

Systems performing large data-parallel computations, including online analytical processing (OLAP) systems like Druid and search engines like Elasticsearch, are increasingly being used for business-critical real-time applications where providing low query latency is paramount. In this paper, we investigate an underexplored factor in the performance of data-parallel queries: their parallelism. We find that to minimize the tail latency of data-parallel queries, it is critical to place data such that the data items accessed by each individual query are spread across as many machines as possible so that each query can leverage the computational resources of as many machines as possible. To optimize parallelism and minimize tail latency in real systems, we develop a novel parallelism-optimizing data placement algorithm that defines a linearly-computable measure of query parallelism, uses it to frame data placement as an optimization problem, and leverages a new optimization problem partitioning technique to scale to large cluster sizes. We apply this algorithm to popular systems such as Solr and MongoDB and show that it reduces p99 latency by 7-64% on data-parallel workloads.

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.

Performance of NoSQL Graph Implementations of Star vs. Snowflake Schemas

Nowadays, the data used for decision-making come from a wide variety of sources which are difficult to manage using relational databases. To address this problem, many researchers have turned to Not only SQL (NoSQL) databases to provide scalability and flexibility for On-Line Analytical Processing (OLAP) systems. In this paper, we propose a set of formal rules to convert a multidimensional data model into a graph data model (MDM2G). These rules allow conventional star and snowflake schemas to fit into NoSQL graph databases. We apply the proposed rules to implement star-like and snowflake-like graph data warehouses. We compare their performances to similar relational ones focusing on the data model, dimensionality, and size. The experimental results show large differences between relational and graph implementations of a data warehouse. A relational implementation performs better for queries on a couple of tables, but conversely, a graph implementation is better when queries involve many tables. Surprisingly the performances of a star-like and snowflake-like graph data warehouses are very close. Hence a snowflake schema could be used in order to easily consider new sub-dimensions in a graph data warehouse.

Performance Analysis of Heuristic Miner and Genetics Algorithm in Process Cube: a Case Study

Databases that are processed in the form of Online Analytical Processing (OLAP) can solve large query loads that cannot be resolved by transactional databases. OLAP systems are based on a multidimensional model commonly called a cube. In this study, OLAP techniques are applied in process mining, a method for bridging analysis based on business process models with database analysis. Like data mining, process mining produces process models by implementing the algorithms. This study implements the heuristic miner algorithm compared with genetic algorithms. The selection of these two algorithms is due to the characteristics to be able to model the event log correctly and can handle the control-flow. The capability in handling control-flow including the ability to detect hidden task, looping, duplicate task, detecting implicit/explicit concurrency, non-free-choice, the ability to mine and exploiting time, overcoming noise, and overcome incompleteness. The results of conformance checking on the heuristic miner algorithm for all data, fitness values, position, and structure are 1, 0.495, and 1, while the results of the genetic algorithm are 0.977, 0.706 and 1. Both algorithms have good ability in modeling processes and have high accuracy. The results of the F-score calculation on the heuristic miner algorithm for all data is 0.622, while the result in the genetic algorithm is 0.820. It indicates that genetic algorithms have better performance in modeling event logs based on process cube.

An On-line Analytical Processing (OLAP) Aggregation Function for Rising Aspects in Collaboration and Social Networks

The overwhelming usage of social and collaboration networks provides the opportunity to analyze evolution of relationships among individuals, like celebrities or co-authors. Discovering such phenomenon in large complex networks is non-trivial due to their large sizes. In this situation, the aggregation functions used in OLAP, are useful to analyze the summarized data. OLAP has successfully proven its worth on multidimensional or complex networks. However, existing aggregations in the current OLAP systems do not produce versatile results in case of social and collaboration networks. This happens because said type of networks have structural connectivity/links among nodes, which cannot be considered by OLAP during its execution. In this situation, a useful discovery in terms of identifying pairs of nodes whose relationships is emerging in recent time, is missed. Such discovery of pairs of nodes is important for various applications such as targeted marketing, future joint partnerships and predicting future correspondence to name a few. In this study, we call such pairs as Rising_Pairs and propose an aggregation function for performing OLAP on network data whose historical information is maintained over a period of times. Using structural information, Rising_Pairs, our proposed aggregation function, discovers the strongly coupled pairs in a network data by emphasizing their recent interactions and attribute similarities. In this way, useful information related to strongly coupled pairs in a network is identified. To verify the effectiveness of our proposal, we implemented it on various types of real-world networks like Facebook, Digital Bibliography and Library Project (DBLP) and Global Positioning System (GPS) trajectory datasets and observed interesting patterns.

Generalization Driven Fuzzy Classification Rules Extraction using OLAM Data Cubes

An fuzzy classification rules extraction model for online analytical mining (OLAM) was explained in this article. The efficient integration of the concept of data warehousing, online analytical processing (OLAP) and data mining systems converges to OLAM results in an efficient decision support system. Even after associative classification proved as most efficient classification technique there is a lack of associative classification proposals in field of OLAM. While most of existing data cube models claims their superiority over other the fuzzy multidimensional data cubes proved to be more intuitive in user perspective and effectively manage data imprecision. Considering these factors, in this paper we propose an associative classification model which can perform classification over fuzzy data cubes. Our method aimed to improve accuracy and intuitive ness of classification model using fuzzy concepts and hierarchical relations. We also proposed a generalization-based criterion for ranking associative classification rules to improve classifier accuracy. The model accuracy tested on UCI standard database.

Micro-architectural analysis of OLAP

Understanding micro-architectural behavior is important for efficiently using hardware resources. Recent work has shown that in-memory online transaction processing (OLTP) systems severely underutilize their core micro-architecture resources [29]. Whereas, online analytical processing (OLAP) workloads exhibit a completely different computing pattern. OLAP workloads are read-only, bandwidth-intensive, and include various data access patterns. With the rise of column-stores, they run on high-performance engines that are tightly optimized for modern hardware. Consequently, micro-architectural behavior of modern OLAP systems remains unclear. This work presents a micro-architectural analysis of a set of OLAP systems. The results show that traditional commercial OLAP systems suffer from their long instruction footprint, which results in high response times. High-performance columnstores execute tight instruction streams; however, they spend 25 to 82% of their CPU cycles on stalls both for sequential- and random-access-heavy workloads. Concurrent query execution can improve the utilization, but it creates interference in the shared resources, which results in sub-optimal performance.

Football Player Decision Support System Baghdad-City as a Case Study

Decision support system (DSS) is an area of information systems (IS) discipline which focuses on supporting decision-making. DSS includes personal decision support systems, executive information systems, online analytical processing systems, data warehousing, business intelligence, and group support systems. This paper introduced the implementation of a DSS related to football players with a case study.

Using superimposed multidimensional schemas and OLAP patterns for RDF data analysis

Abstract The foundations for traditional data analysis are Online Analytical Processing (OLAP) systems that operate on multidimensional (MD) data. The Resource Description Framework (RDF) serves as the foundation for the publication of a growing amount of semantic web data still largely untapped by companies for data analysis. Most RDF data sources, however, do not correspond to the MD modeling paradigm and, as a consequence, elude traditional OLAP. The complexity of RDF data in terms of structure, semantics, and query languages renders RDF data analysis challenging for a typical analyst not familiar with the underlying data model or the SPARQL query language. Hence, conducting RDF data analysis is not a straightforward task. We propose an approach for the definition of superimposed MD schemas over arbitrary RDF datasets and show how to represent the superimposed MD schemas using well-known semantic web technologies. On top of that, we introduce OLAP patterns for RDF data analysis, which are recurring, domain-independent elements of data analysis. Analysts may compose queries by instantiating a pattern using only the MD concepts and business terms. Upon pattern instantiation, the corresponding SPARQL query over the source data can be automatically generated, sparing analysts from technical details and fostering self-service capabilities.

An integrated decision-making prototype based on OLAP systems and multicriteria analysis for complex decision-making problems

OLAP (Online Analytical Processing) system appears as a revolutionary technology that provides adequate analytic solutions for decision support. Using OLAP, analysts and policymakers are able to process and analyze data in an interactive, fast, and effective way according to several axes. This provides a clear vision of their business at any time and in real time. However, these systems suffer from certain limitations related to the consideration of both the multicriteria and fuzzy aspects of multidimensional data when making decisions. To overcome these limitations, we propose an integrated decision-making prototype based on OLAP system and multicriteria analysis (MCA) to generate a hybrid analysis process dealing with complex multicriteria decision-making situations. This proposed solution, based on our previous contributions, allows the analyst to extract data from OLAP data cube model and analyze them using OLAP operators and MCA methods.

Conceptual design and implementation of spatial data warehouses integrating regular grids of points

ABSTRACTSpatial online analytical processing (OLAP) and spatial data warehouse (SDW) systems are geo-business intelligence technologies that enable the analysis of huge volumes of geographic data. In the last decade, the conceptual design and implementation of SDWs that integrate spatial data, which are represented using the vector model, have been extensively investigated. However, the integration of field data (a continuous representation of spatial data) in SDWs is a recent unresolved research issue. Enhancing SDWs with field data improves the spatio-multidimensional analysis capabilities with continuity and multiresolutions. Motivated by the need for a conceptual design tool and relational online analytical processing (ROLAP) implementation, we propose a UML profile for SDWs that integrates a regular grid of points and supports continuity and multiresolutions. We also propose an efficient implementation of a ROLAP architecture.

An UML Profile and SOLAP Datacubes Multidimensional Schemas Transformation Process for Datacubes Risk-Aware Design

Spatial Data Warehouses (SDWs) and Spatial On-Line Analytical Processing (SOLAP) systems are new technologies for the integration and the analysis of huge volume of data with spatial reference. Spatial vagueness is often neglected in these types of systems and the data and analysis results are considered reliable. In a previous work, the authors provided a new design method for SOLAP datacubes that allows the handling of vague spatial data analysis issues. The method consists of tailoring SOLAP datacubes schemas to end-users tolerance levels to identified potential risks of misinterpretation they encounter when exploiting datacubes containing vague spatial data. It this paper, the authors further their previous proposal by presenting different formal tools to support their method: it is an UML profile providing stereotypes needed to add vague, risks and tolerance levels information on datacubes schemas plus the formal definition of SOLAP datacubes schemas transformation process and functions.

Contextualized Text OLAP Based on Information Retrieval

Current data warehousing and On-Line Analytical Processing (OLAP) systems are not yet particularly appropriate for textual data analysis. It is therefore crucial to develop a new data model and an OLAP system to provide the necessary analyses for textual data. To achieve this objective, this paper proposes a new approach based on information retrieval (IR) techniques. Moreover, several contextual factors may significantly affect the information relevant to a decision-maker. Thus, the paper proposes to consider contextual factors in an OLAP system to provide relevant results. It provides a generalized approach for Text OLAP analysis which consists of two parts: The first one is a context-based text cube model, denoted CXT-Cube. It is characterized by several contextual dimensions. Hence, during the OLAP analysis process, CXT-Cube exploits the contextual information in order to better consider the semantics of textual data. Besides, the work associates to CXT-Cube a new text analysis measure based on an OLAP-adapted vector space model and a relevance propagation technique. The second part is an OLAP aggregation operator called ORank (OLAP-Rank) which allows to aggregate textual data in an OLAP environment while considering relevant contextual factors. To consider the user context, this paper proposes a query expansion method based on a decision-maker profile. Based on IR metrics, it evaluates the proposed aggregation operator in different cases using several data analysis queries. The evaluation shows that the precision of the system is significantly better than that of a Text OLAP system based on classical IR. This is due to the consideration of the contextual factors.

Parallel Real-Time OLAP on Multi-Core Processors

One of the most powerful and prominent technologies for knowledge discovery in decision support systems is online analytical processing (OLAP). Most of the traditional OLAP research, and most of the commercial systems, follow the static data cube approach proposed by Gray et.al. and materialize all or a subset of the cuboids of the data cube in order to ensure adequate query performance. Practitioners have called for some time for a real-time OLAP approach where the OLAP system gets updated instantaneously as new data arrives and always provides an up-to-date data warehouse for the decision support process. However, a major problem for real-time OLAP is the significant performance issues with large scale data warehouses. The aim of our research is to address these problems through the use of efficient parallel computing methods. In this paper, we present a parallel real-time OLAP system for multi-core processors. To our knowledge, this is the first real-time OLAP system that has been parallelized and optimized for contemporary multi-core architectures. Our system allows for multiple insert and multiple query transactions to be executed in parallel and in real-time. We evaluated our method for a multitude of scenarios (different ratios of insert and query transactions, query transactions with different amounts of data aggregation, different database sizes, etc.), using the TPCDS “Decision Support” benchmark data set. As multi-core test platforms, we used an Intel Sandy Bridge processor with 4 cores (8 hardware supported threads) and an Intel Xeon Westmere processor with 20 cores (40 hardware supported threads). The tests demonstrate that, with increasing number of processor cores, our parallel system achieves close to linear speedup in transaction response time and transaction throughput. On the 20 core architecture we achieved, for a 100 GB database, a better than 0.25 second query response time for real-time OLAP queries that aggregate 25% of the database. Since hardware performance improvements are currently, and in the foreseeable future, achieved not by faster processors but by increasing the number of processor cores, our new parallel real-time OLAP method has the potential to enable OLAP systems that operate in real-time on large databases.

Spatial OLAP integrity constraints: From UML-based specification to automatic implementation: Application to energetic data in agriculture

Spatial OnLine Analytical Processing systems (SOLAP) are Business Intelligence technologies allowing efficient and interactive analysis of large spatial data cubes. In this type of systems, the correctness of analysis depends on the warehoused data quality, how aggregations are performed and how data cubes are explored. In this paper, we study quality control techniques (based on integrity constraints) related to exploration of spatial data cubes. We extend our Unified Modeling Language (UML) framework previously proposed with a UML profile allowing the conceptual design of several classes of exploration integrity constraints. We also propose a tool for their automatic implementation. We validate our proposal in the context of a real case study concerning the analysis of energetic farm indicators.

Closing Ontologies to Define OLAP Systems

Ontologies can be used in the construction of OLAP (On-Line Analytical Processing) systems. In such a context, ontologies are mainly used either to enrich cube dimensions or to define ontology based-dimensions. On the one hand, if dimensions are enriched using large ontologies, like WordNet, details that are beyond the scope of the dimension may be added to it. Even, dimensions may be obscured because of the massive incorporation of related attributes. On the other hand, if ontologies are used to define a dimension, it is possible that a simplified version of the ontology is needed to define the dimension, especially when the used ontology is too complex for the dimension that is being defined. These problems may be solved using one of the existing mechanisms to define ontology views. Therefore, concepts that are not needed for the domain ontology are kept out of the view. However, this view must be closed so that, no ontology component has references to components that are not included in the view. In this work, two basic approaches are proposed: enlargement and reduction closure.