Star Schema Benchmark Research Articles

Benchmark of Market Cloud Data Warehouse Technologies

Over the past two decades, the way computing resources are been developed, deployed, upgraded, and applied changed dramatically, with more and more software and hardware solutions being transferred to cloud technologies. Data Warehouses (DW), defined as a way of organizing corporate data in an integrated manner over (sequential) time periods, "structured & disposed" in order to generate a "single data source", were also affected by the evolution, thus giving rise to the concept of Cloud Data Warehouse (CDW). This technology allows users to be more technologically free, as they do not need to spend time investing in software and hardware, they only pay for the resources they used and the infrastructure itself has greater flexibility and scalability. However, selecting the most suitable platform or technology for a CDW can be a complex task due to the large number of factors that can influence the decision and due to the existing offer in the market.The objective of this paper is to describe the process of benchmarking a set of CDW platforms, with the goal of analyzing and exposing each one’s performance results. These platforms are Snowflake, Google BigQuery, Amazon Redshift, and Azure Synapse. The metrics to be measured are data loading and query running time, and alias running times. For this benchmark, the dataset used was Star Schema Benchmark (SSB), a dataset based on the well-known TPC Benchmark™ H (TPC-H).

MoTrans-BDI: Leveraging the Beliefs-Desires-Intentions agent architecture for collaborative model transformation by example

Due to the growing interest in model-driven system development techniques, the efficient design of automated model transformations between heterogeneous models has become a major challenge in software development. While a number of specialized languages have been proposed, aiming at specifying model transformations, there is currently no matured foundation for specifying transformations between such models that are based on designers/experts collaboration in order to propose a solution that satisfies users’ requirements and application constraints. This transformation process is a complex task and must emulate how designers and experts with different perspectives behave and reflect about model transformations. In this paper, we propose a framework based on a novel approach for the specification and design of model transformations called MoTrans-BDI, which leverages Evolutionary Multi-Agent System (EMAS) to simulate designers’ expertise for the transformation of models. Our approach is based on the Belief-Desire-Intention (BDI) agent model and the Contract Net Protocol where agents’ beliefs feed from a series of transformation examples. The emphasis of using the specific model is the opportunity to produce a target model that may be composed of parts from different experts’ designs. We first experimentally evaluate MoTrans-BDI on twelve handmade UML2REL model transformation problems. All types of agents are able to produce perfect target models compared to human experts’ collaboratively-produced target models. Second, we empirically evaluate MoTrans-BDI’s proposal in terms of response time on Star Schema Benchmark (SSB) queries. An application of MoTrans-BDI in the case of UML2REL transformation is performed to stress and highlight the transformation process steps.

NewSQL Databases Assessment: CockroachDB, MariaDB Xpand, and VoltDB

Background: Relational databases have been a prevalent technology for decades, using SQL (Structured Query Language) to manage data. However, the emergence of new technologies, such as the web and the cloud, has brought the requirement to handle more complex data. NewSQL is the latest technology that incorporates the ability to scale and ensures the availability of NoSQL (Not Only SQL) without losing the ACID properties (Atomicity, Consistency, Isolation, Durability) associated with relational databases. Methods: We evaluated CockroachDB, MariaDB Xpand, and VoltDB with OSSpal methodology and experimentally using the Star Schema Benchmark (SSB). The scalability and performance capabilities of each database were assessed. Results: Applying the OSSpal methodology, the results showed that MariaDB Xpand outperformed CockroachDB and VoltDB. On the other hand, we concluded that with Star Schema Benchmark, CockroachDB had better scalability, while VoltDB had a faster query execution time. Conclusions: CockroachDB and VoltDB are the best performing databases in terms of scalability and performance.

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.

Orchestrating data placement and query execution in heterogeneous CPU-GPU DBMS

There has been a growing interest in using GPU to accelerate data analytics due to its massive parallelism and high memory bandwidth. The main constraint of using GPU for data analytics is the limited capacity of GPU memory. Heterogeneous CPU-GPU query execution is a compelling approach to mitigate the limited GPU memory capacity and PCIe bandwidth. However, the design space of heterogeneous CPU-GPU query execution has not been fully explored. We aim to improve state-of-the-art CPU-GPU data analytics engine by optimizing data placement and heterogeneous query execution. First, we introduce a semantic-aware fine-grained caching policy which takes into account various aspects of the workload such as query semantics, data correlation, and query frequency when determining data placement between CPU and GPU. Second, we introduce a heterogeneous query executor which can fully exploit data in both CPU and GPU and coordinate query execution at a fine granularity. We integrate both solutions in Mordred, our novel hybrid CPU-GPU data analytics engine. Evaluation on the Star Schema Benchmark shows that the semantic-aware caching policy can outperform the best traditional caching policy by up to 3x. Compared to existing GPU DBMSs, Mordred can outperform by an order of magnitude.

ProRes: Proactive re-selection of materialized views

Materialized View Selection is one of the most studied problems in the database field, covering SQL and NoSQL technologies as well as different deployment infrastructures (centralized, parallel, cloud). This problem has become more complex with the arrival of data warehouses, being coupled with the physical design phase that aims at optimizing query performance. Selecting the best set of materialized views to optimize query performance is a challenging task. Given their importance and the complexity of their selection, several research efforts both from academia and industry have been conducted. Results are promising ? some solutions are being implemented by commercial and open-source DBMSs ?, but they do not factor in the following properties of nowadays analytical queries: (i) largescale queries, (ii) their dynamicity, and (iii) their high interaction. Studies to date fail to consider that complete set of properties. Considering the three properties simultaneously is crucial regarding today?s analytical requirements, which involve dynamic and interactive queries. In this paper, we first present a concise state of the art of the materialized view selection problem (VSP) by analyzing its ecosystem. Secondly, we propose a proactive re-selection approach that considers the three properties concurrently. It features two main phases: offline and online. In the offline phase, we manage a set of the first queries based on a given threshold ? by selecting materialized views through a hypergraph structure. The second phase manages the addition of new queries by scheduling them, updates the structure of the hypergraph, and selects new views by eliminating the least beneficial ones. Finally, extensive experiments are conducted using the Star Schema Benchmark data set to evaluate the effectiveness and efficiency of our approach.

The case for phase-aware scheduling of parallelizable jobs

Parallelizable jobs typically consist of multiple phases of computation, where the job is more parallelizable in some phases and less parallelizable in others. For example, in a database, a query may consist of a highly parallelizable table scan, followed by a less parallelizable table join. In the past, this phase-varying parallelizability was summarized by a single sub-linear speedup curve that measures a job’s average parallelizability over its entire lifetime. Today, however, modern systems have fine-grained knowledge of the exact phase each job is in at every moment in time. Unfortunately, these systems do not fully leverage this real-time feedback when scheduling parallelizable jobs. Theory has failed to produce practical phase-aware scheduling policies, and thus scheduling in current systems is largely heuristic.A phase-aware scheduling policy must decide, given its knowledge of each job’s current phase, how many servers or cores to allocate to each job in the system at every moment in time. This paper provides the first stochastic model of a system processing parallelizable jobs composed of phases. Using our model, we derive an optimal phase-aware scheduling policy that minimizes the mean response time across jobs. Our provably optimal policy, Inelastic-First (IF), gives strict priority to jobs that are currently in less parallelizable phases. We validate our theoretical results using a simulation of a database running queries from the Star Schema Benchmark. We compare IF to a range of policies from both systems and theory, and show that IF reduces mean response time by up to a factor of 3.

FlexPushdownDB

Modern cloud databases adopt a storage-disaggregation architecture that separates the management of computation and storage. A major bottleneck in such an architecture is the network connecting the computation and storage layers. Two solutions have been explored to mitigate the bottleneck: caching and computation pushdown. While both techniques can significantly reduce network traffic, existing DBMSs consider them as orthogonal techniques and support only one or the other, leaving potential performance benefits unexploited. In this paper we present FlexPushdownDB (FPDB) , an OLAP cloud DBMS prototype that supports fine-grained hybrid query execution to combine the benefits of caching and computation pushdown in a storage-disaggregation architecture. We build a hybrid query executor based on a new concept called separable operators to combine the data from the cache and results from the pushdown processing. We also propose a novel Weighted-LFU cache replacement policy that takes into account the cost of pushdown computation. Our experimental evaluation on the Star Schema Benchmark shows that the hybrid execution outperforms both the conventional caching-only architecture and pushdown-only architecture by 2.2X. In the hybrid architecture, our experiments show that Weighted-LFU can outperform the baseline LFU by 37%.

Comparative Analysis of Decision Tree Algorithms for Data Warehouse Fragmentation*

One of the main problems faced by Data Warehouse designers is fragmentation.Several studies have proposed data mining-based horizontal fragmentation methods.However, not exists a horizontal fragmentation technique that uses a decision tree. This paper presents the analysis of different decision tree algorithms to select the best one to implement the fragmentation method. Such analysis was performed under version 3.9.4 of Weka, considering four evaluation metrics (Precision, ROC Area, Recall and F-measure) for different selected data sets using the Star Schema Benchmark. The results showed that the two best algorithms were J48 and Random Forest in most cases; nevertheless, J48 was selected because it is more efficient in building the model.

A Benchmark for Suitability of Alluxio over Spark

Big data applications play an important role in real time data processing. Apache Spark is a data processing framework with in-memory data engine that quickly processes large data sets. It can also distribute data processing tasks across multiple computers, either on its own or in tandem with other distributed computing tools. Spark’s in-memory processing cannot share data between the applications and hence, the RAM memory will be insufficient for storing petabytes of data. Alluxio is a virtual distributed storage system that leverages memory for data storage and provides faster access to data in different storage systems. Alluxio helps to speed up data intensive Spark applications, with various storage systems. In this work, the performance of applications on Spark as well as Spark running over Alluxio have been studied with respect to several storage formats such as Parquet, ORC, CSV, and JSON; and four types of queries from Star Schema Benchmark (SSB). A benchmark is evolved to suggest the suitability of Spark Alluxio combination for big data applications. It is found that Alluxio is suitable for applications that use databases of size more than 2.6 GB storing data in JSON and CSV formats. Spark is found suitable for applications that use storage formats such as parquet and ORC with database sizes less than 2.6GB.

One size does not fit all: accelerating OLAP workloads with GPUs

GPU has been considered as one of the next-generation platforms for real-time query processing databases. In this paper we empirically demonstrate that the representative GPU databases [e.g., OmniSci (Open Source Analytical Database & SQL Engine, https://www.omnisci.com/platform/omniscidb, 2019)] may be slower than the representative in-memory databases [e.g., Hyper (Neumann and Leis, IEEE Data Eng Bull 37(1):3–11, 2014)] with typical OLAP workloads (with Star Schema Benchmark) even if the actual dataset size of each query can completely fit in GPU memory. Therefore, we argue that GPU database designs should not be one-size-fits-all; a general-purpose GPU database engine may not be well-suited for OLAP workloads without careful designed GPU memory assignment and GPU computing locality. In order to achieve better performance for GPU OLAP, we need to re-organize OLAP operators and re-optimize OLAP model. In particular, we propose the 3-layer OLAP model to match the heterogeneous computing platforms. The core idea is to maximize data and computing locality to specified hardware. We design the vector grouping algorithm for data-intensive workload which is proved to be assigned to CPU platform adaptive. We design the TOP-DOWN query plan tree strategy to guarantee the optimal operation in final stage and pushing the respective optimizations to the lower layers to make global optimization gains. With this strategy, we design the 3-stage processing model (OLAP acceleration engine) for hybrid CPU-GPU platform, where the computing-intensive star-join stage is accelerated by GPU, and the data-intensive grouping & aggregation stage is accelerated by CPU. This design maximizes the locality of different workloads and simplifies the GPU acceleration implementation. Our experimental results show that with vector grouping and GPU accelerated star-join implementation, the OLAP acceleration engine runs 1.9×, 3.05× and 3.92× faster than Hyper, OmniSci GPU and OmniSci CPU in SSB evaluation with dataset of SF = 100.

ATrie Group Join: A Parallel Star Group Join and Aggregation for In-Memory Column-Stores

This article presents a new holistic and efficient approach to big data analysis. We introduce a new parallel algorithm, known as <italic xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">ATrie Group Join (ATGJ)</i> , that integrates join, grouping and aggregation operations to accelerate big data analytical workloads in in-memory column-stores. ATGJ performs a single scan of the fact columns and uses a mixture of data and task parallelism for the optimal use of computing resources. It uses a novel technique to perform group-by and aggregation realising the grouping attributes as a tree shaped deterministic finite automation known as <italic xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">Aggregate Trie</i> or <italic xmlns:mml="http://www.w3.org/1998/Math/MathML" xmlns:xlink="http://www.w3.org/1999/xlink">ATrie</i> . ATrie facilitates grouping of attributes and processing of data in tight loops that significantly improves the performance on modern hardware. Unlike other competing algorithms, use of ATrie avoids the creation of multiple data structures with the increasing number of dimension tables and grouping attributes. Also, we demonstrate that ATGJ performs efficiently even when the ATrie becomes bushy. We evaluated the algorithm using Star Schema Benchmark (SSBM) to show that it is significantly faster and scales better than other algorithms for the number of concurrent threads, the number of group-by attributes, the data set size and the query complexity.

Random access with a distributed Bitmap Join Index for Star Joins

Indices improve the performance of relational databases, especially on queries that return a small portion of the data (i.e., low-selectivity queries). Star joins are particularly expensive operations that commonly rely on indices for improved performance at scale. The development and support of index-based solutions for Star Joins are still at very early stages. To address this gap, we propose a distributed Bitmap Join Index (dBJI) and a framework-agnostic strategy to solve join predicates in linear time. For empirical analysis, we used common Hadoop technologies (e.g., HBase and Spark) to show that dBJI significantly outperforms full scan approaches by a factor between 59% and 88% in queries with low selectivity from the Star Schema Benchmark (SSB). Thus, distributed indices may significantly enhance low-selectivity query performance even in very large databases.

Distributed ATrie Group Join: Towards Zero Network Cost

The combination of powerful parallel frameworks and on-demand commodity hardware in distributed computing has made both analytics and decision support systems canonical to enterprises of all sizes. The unprecedented volumes of data stacked by companies present challenges to process analytical queries efficiently. This data is often organised as star schema, in which star join and group-by are ubiquitous and expensive operations. Although parallel frameworks such as Apache Spark facilitate join and group-by, the implementation can only process two tables at a time and fail to handle the excessive network communication, disk spills and multiple scans of data. In this paper, we present Distributed ATrie Group Join (DATGJ), a fast distributed star join and group-by algorithm for column-stores. DATGJ uses divide and broadcast-based joining technique where the fact table columns are partitioned equally and fast hash table (FHT) for each dimension table are broadcasted. This technique helps it avoid cross communication between workers and disk spills. DATGJ performs a single scan of partitioned fact table columns and use FHT to join data. FHT uses Robin Hood hashing with the upper limit on number of probes and achieve significant speed up during join. DATGJ performs group-by and aggregation leveraging progressive materialisation and realising grouping attributes as a tree shaped deterministic finite automation known as Aggregate Trie or ATrie. We evaluated our algorithm using Star Schema Benchmark (SSBM) to show that it is 1.5X to 6X faster than the most prominent approaches while having zero data shuffle and consistently perform well with addition of resources and in memory-constrained scenarios.

Fusion OLAP: Fusing the Pros of MOLAP and ROLAP Together for In-Memory OLAP

OLAP models can be categorized with two types: MOLAP (multidimensional OLAP) and ROLAP (relational OLAP). In particular, MOLAP is efficient in multidimensional computing at the cost of cube maintenance, while ROLAP reduces the data storage size at the cost of expensive multidimensional join operations. In this paper, we propose a novel Fusion OLAP model to fuse the multidimensional computing model and relational storage model together to make the best aspects of both MOLAP and ROLAP worlds. This is achieved by mapping the relation tables into virtual multidimensional model and binding the multidimensional operations into a set of vector indexes to enable multidimensional computing on relation tables. The Fusion OLAP model can be integrated into the state-of-the-art in-memory databases with additional surrogate key indexes and vector indexes. We compared the Fusion OLAP implementations with three leading analytical in-memory databases. Our comprehensive experimental results show that Fusion OLAP implementation can achieve up to 35, 365, and 169 percent performance improvements based on the Hyper, Vectorwise, and MonetDB databases, respectively, for the Star Schema Benchmark (SSB) with scale factor 100.

PosDB: An Architecture Overview

PosDB is an engine of a disk-based column-store DBMS designed for processing OLAP queries in a shared nothing environment. It is written completely from scratch and aims to become a platform for studying the distributed query processing in column-stores. This paper presents the first comprehensive description of the system. The presentation begins with the history of column-stores in order to clarify the reasons of their success. Next, the creation of a new system is justified, and an overview of its architecture is given. Finally, all its components are described in detail. Currently, query execution in PosDB is based on the Volcano model with block-oriented processing and late materialization. Various physical operators have been developed for relational operations such as join, aggregation, and selection. Some auxiliary operators were developed to support intraquery parallelism and network communication. Data distribution is achieved using horizontal range partitioning and data replication. The current version of PosDB can execute all queries from the Star Schema Benchmark in both centralized and distributed environments.

Efficient Analytical Queries on Semantic Web Data Cubes

The amount of multidimensional data published on the Semantic Web (SW) is constantly increasing, due to initiatives such as Open Data and Open Government Data, among others. Models, languages, and tools, that allow obtaining valuable information efficiently, are thus required. Multidimensional data are typically represented as data cubes and exploited using online analytical processing (OLAP) techniques. The RDF Data Cube Vocabulary, also denoted QB, is the current W3C standard to represent statistical data on the SW. Given that QB does not include key features needed for OLAP analysis, in previous work we have proposed an extension, denoted QB4OLAP, to overcome this problem without the need of modifying already published data. Once data cubes are appropriately represented on the SW, we need mechanisms to analyze them. However, in the current state-of-the-art, writing efficient analytical queries over SW data cubes demands a deep knowledge of standards like RDF and SPARQL. These skills are unlikely to be found in typical analytical users. Further, OLAP languages like MDX are far from being easily understood by the final user. The lack of friendly tools to exploit multidimensional data on the SW is a barrier that needs to be broken to promote the publication of such data. This is the problem we address in this paper. Our approach is based on allowing analytical users to write queries using what they know best: OLAP operations over data cubes, without dealing with SW technicalities. For this, we devised CQL (standing for Cube Query Language), a simple, high-level query language that operates over data cubes. Taking advantage of structural metadata provided by QB4OLAP, we translate CQL queries into SPARQL ones. Then, we propose query improvement strategies to produce efficient SPARQL queries, adapting general-purpose SPARQL query optimization techniques. We evaluate our implementation using the Star Schema benchmark, showing that our proposal outperforms others. The QB4OLAP toolkit, a web application that allows exploring and querying (using CQL) SW data cubes, completes our contributions.

The role of user requirements in data repository design

Requirements engineering plays a crucial role in the development process of an information system as it aims at providing a complete and accurate requirement specification. In the life cycle of a Data Repository (\({\mathcal {D}}{\mathcal {R}}\)) such as a database or a data warehouse, the requirements are mainly used to define the conceptual model once they have been identified from the informal specification. In this paper, we study the interest of requirements in the other phases of the \({\mathcal {D}}{\mathcal {R}}\) life cycle. As the data integration problem, handled in the Extract, Transform, Load (ETL) phase, comes from the heterogeneity of requirements, we introduce a requirement integration framework based on ontologies and a generic model to unify the used vocabularies and requirement languages. Then we propose an approach to check the consistency of the requirements, w.r.t. the integrity constraints defined on the logical schema using the formal B method. We also show that requirements help define appropriate access structures such as indexes and materialized views to optimize SQL queries of a \({\mathcal {D}}{\mathcal {R}}\). Our approach is based on transformation rules that identify important queries that will be executed on a \({\mathcal {D}}{\mathcal {R}}\) directly from the requirements. The experiments conducted on the Star Schema Benchmark (SSB) confirm the interest of this approach for the selection of different optimization structures. Finally, we present the OntoReqTool that implements the previous functionality on top of the OntoDB/OntoQL platform.

Query Interaction Based Approach for Horizontal Data Partitioning

With the explosion of data, several applications are designed around analytical aspects, with data warehousing technology at the heart of the construction chain. The exploitation of this data warehouse is usually performed by the use of complex queries involving selections, joins and aggregations. These queries bring the following characteristics: (1) their routinely aspects, (2) their large number, and (3) the high operation sharing between queries. This interaction has been largely identified in the context of multi-query optimization, where graph data structures were proposed to capture it. Also during the physical design, the structures have been used to select redundant optimization structures such as materialized views and indexes. Horizontal data partitioning (HDP) is another non-redundant optimization structure that can be selected in the physical design phase. It is a pre-condition for designing extremely large databases in several environments: centralized, distributed, parallel and cloud. It aims to reduce the cost of the above operations. In HDP, the optimization space of potential candidates for partitioning grows exponentially with the problem size making the problem NP-hard. This paper proposes a new approach based on query interactions to select a partitioning schema of a data warehouse in a divide and conquer manner to achieve an improved trade-off between the optimization algorithm's speed and the quality of the solution. The effectiveness of our approach is proven through a validation using the Star Schema Benchmark (100 GB) on Oracle11g.

Comparing SQL and NoSQL approaches for clustering over big data

Data mining is the process of discovering patterns in large datasets. With the exponential growth of available information, new machine learning, statistics and other analytics techniques have to be developed to solve the processing needs required to do such analysis fast enough to be used successfully. In this study, techniques like cluster analysis are used over generated data in order to do customer segmentation, and the system performance is evaluated by measuring the processing time. The data used in the current paper is generated using the Star Schema Benchmark (SSB). Our main goal is to find a scalable solution to run data mining over a decision support benchmark. Four different systems will be tested: single node MySQL, MySQL cluster, Apache Mahout and R. By running MySQL cluster and Mahout, each system distributed by four nodes, the paper compares the performance of k-means run in parallel. MySQL and R will allow for comparison of this kind of execution against methods running on a single machine, both on relational and non-relational systems.