Online Analytical Processing System Research Articles

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.

Implementation of Data Mining Technology in Bonded Warehouse Inbound and Outbound Goods Trade

For the taxed goods, the actual freight is generally determined by multiplying the allocated freight for each KG and actual outgoing weight based on the outgoing order number on the outgoing bill. Considering the conventional logistics is insufficient to cope with the rapid response of e-commerce orders to logistics requirements, this work discussed the implementation of data mining technology in bonded warehouse inbound and outbound goods trade. Specifically, a bonded warehouse decision-making system with data warehouse, conceptual model, online analytical processing system, human-computer interaction module and WEB data sharing platform was developed. The statistical query module can be used to perform statistics and queries on warehousing operations. After the optimization of the whole warehousing business process, it only takes 19.1 hours to get the actual freight, which is nearly one third less than the time before optimization. This study could create a better environment for the development of China's processing trade.

CirroData: Yet Another SQL-on-Hadoop Data Analytics Engine with High Performance

This paper presents CirroData, a high-performance SQL-on-Hadoop system designed for Big Data analytics workloads. As a home-grown enterprise-level online analytical processing (OLAP) system with more than seven-year research and development (R&D) experiences, we share our design details to the community about how to achieve high performance in CirroData. Multiple optimization techniques have been discussed in the paper. The effectiveness and the efficiency of all these techniques have been proved by our customers’ daily usage. Benchmark-level studies, as well as several real application case studies of CirroData, have been presented in this paper. Our evaluations show that CirroData can outperform various types of counterpart database systems in the community, such as “Spark+Hive”, “Spark+HBase”, Impala, DB-X/Y, Greenplum, HAWQ, and others. CirroData can achieve up to 4.99x speedup compared with Greenplum, HAWQ, and Spark in the standard TPC-H queries. Application-level evaluations demonstrate that CirroData outperforms “Spark+Hive” and “Spark+HBase” by up to 8.4x and 38.8x, respectively. In the meantime, CirroData achieves the performance speedups for some application workloads by up to 20x, 100x, 182.5x, 92.6x, and 55.5x as compared with Greenplum, DB-X, Impala, DB-Y, and HAWQ, respectively.

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.

Building a contextual dimension for OLAP using textual data from social networks

Due to the continuous growth of social networks the textual information available has increased exponentially. Data warehouses (DW) and online analytical processing (OLAP) are some of the established technologies to process and analyze structured data. However, one of their main limitations is the lack of automatic processing and analysis of unstructured data (specifically, textual data), and its integration with structured data. This paper proposes the creation, integration and implementation of a new dimension called Contextual Dimension from texts obtained from social networks into a multidimensional model. Such a dimension is automatically created after applying hierarchical clustering algorithms and is fully independent from the language of the texts. This dimension allows the inclusion of multidimensional analysis of texts using contexts and topics integrated with conventional dimensions into business decisions. The experiments were carried out by means of a freeware OLAP system (Wonder 3.0) using real data from social networks.

An evaluation of analytical queries on CPUs and coupled GPUs

SummaryRecently, the mainstream hardware vendors such as Intel and AMD have made significant efforts to integrate the central processing unit (CPU) and the graphics processing unit (GPU) into a single chip, which forms a coupled CPU‐GPU architecture. Data transfer between the CPU and the GPU through a Peripheral Component Interconnect Express bus is eliminated on this architecture, which provides new opportunities for database community to optimize query processing. Because of the lack of comprehensive evaluation of database systems on coupled CPU‐GPU platforms, it is difficult for academic and industry researchers to make appropriate decisions on improvement and optimization directions. In this paper, we conduct an extensive experimental study to evaluate an online analytical processing system on Intel and AMD machines. The performance difference is measured and analyzed when executing queries on integrated GPUs and multicore CPUs. The impacts of various parameters, data sizes, and optimization techniques on performance are also investigated. The results provide preliminary insights into database query and operator behaviors on state‐of‐the‐art coupled CPU‐GPU architectures.

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 Online Analytical Processing for Hotspots Distribution Based on Socio-economic Factors in Riau Province Indonesia

Hotspot data analysis will generate more useful information if the data are combined with socio-economics condition. The relation between hotspots and socio-economic conditions is important to be explored in order to identify the human factors that may cause forest fires. In the previous research, a spatial data warehouse and Spatial Online Analytical Processing (SOLAP) system for hotspots distribution were developed. However, the system does not provide other useful information such as socio-economy to analyse the cause of forest fires. The purpose of this study is to create socio-economy dimensions in the spatial data warehouse system.

HaoLap: A Hadoop based OLAP system for big data

In recent years, facing information explosion, industry and academia have adopted distributed file system and MapReduce programming model to address new challenges the big data has brought. Based on these technologies, this paper presents HaoLap (Hadoop based oLap), an OLAP (OnLine Analytical Processing) system for big data. Drawing on the experience of Multidimensional OLAP (MOLAP), HaoLap adopts the specified multidimensional model to map the dimensions and the measures; the dimension coding and traverse algorithm to achieve the roll up operation on dimension hierarchy; the partition and linearization algorithm to store dimensions and measures; the chunk selection algorithm to optimize OLAP performance; and MapReduce to execute OLAP. The paper illustrates the key techniques of HaoLap including system architecture, dimension definition, dimension coding and traversing, partition, data storage, OLAP and data loading algorithm. We evaluated HaoLap on a real application and compared it with Hive, HadoopDB, HBaseLattice, and Olap4Cloud. The experiment results show that HaoLap boost the efficiency of data loading, and has a great advantage in the OLAP performance of the data set size and query complexity, and meanwhile HaoLap also completely support dimension operations.

Multidimensional modeling and analysis of large and complex watercourse data: an OLAP-based solution

This paper presents the application of Data Warehouse (DW) and On-Line Analytical Processing (OLAP) technologies to the field of water quality assessment. The European Water Framework Directive (DCE, 2000) underlined the necessity of having operational tools to help in the interpretation of the complex and abundant information regarding running waters and their functioning. Several studies have exemplified the interest in DWs for integrating large volumes of data and in OLAP tools for data exploration and analysis.Based on free software tools, we propose an extensible relational OLAP system for the analysis of physicochemical and hydrobiological watercourse data. This system includes: (i) two data cubes; (ii) an Extract, Transform and Load (ETL) tool for data integration; and (iii) tools for OLAP exploration. Many examples of OLAP analysis (thematic, temporal, spatiotemporal, and multiscale) are provided. We have extended an existing framework with complex aggregate functions that are used to define complex analysis indicators. Additional analysis dimensions are also introduced to allow their calculation and also for purposes of rendering information. Finally, we propose two strategies to address the problem of summarizing heterogeneous measurement units by: (i) transforming source data at the ETL tier, and (ii) introducing an additional analysis dimension at the OLAP server tier.

Query Processing on OLAP System with Cloud Computing Environment

The conventional researches on a distributed On-Line Analytical Processing (OLAP) system have been in hardship to be adapted to real business environment. However, the recent spread of Cloud PaaS (Platform as a Services) provides new chances in the field of a distributed OLAP. OLAP query execution costs many minutes by its enormous data and OLAP query properties. On the other hand, MOLAP has fast responses. But it has a physical space limit to materialize all cells in possible combinations. Therefore, MOLAP is unsuited to analyze large data. In this paper, to provide not only flexibility and expandability of ROLAP but also the speediness of MOLAP, the cloud server architecture is proposed which shares clients’ cube cache by P2P and manages central index on cube data on P2P nodes. In this paper, each node acts as not only a P2P duplicator of aggregated data cubes but also a hybrid server to process queries on sub-cubes. Also, the requests on server are confined to non-aggregated areas and multiple client nodes exchange data simultaneously and asynchronously in the proposed Cloud P2P OLAP. In particular, data are retrieved from physically or logically adjacent nodes. With time series properties, the volume of requested data is minimized and the reuse of past cache data is focused. While central managed P2P has blocked the extendibility, Cloud P2P OLAP guarantees the performance and the extendibility. While Grid OLAP should keep self-distributed system, Cloud use services as much as used by public services. Therefore, Cloud P2P OLAP solves a lot of theoretical limits of conventional distributed OLAP.

Parallel online spatial and temporal aggregations on multi-core CPUs and many-core GPUs

With the increasing availability of locating and navigation technologies on portable wireless devices, huge amounts of location data are being captured at ever growing rates. Spatial and temporal aggregations in an Online Analytical Processing (OLAP) setting for the large-scale ubiquitous urban sensing data play an important role in understanding urban dynamics and facilitating decision making. Unfortunately, existing spatial, temporal and spatiotemporal OLAP techniques are mostly based on traditional computing frameworks, i.e., disk-resident systems on uniprocessors based on serial algorithms, which makes them incapable of handling large-scale data on parallel hardware architectures that have already been equipped with commodity computers. In this study, we report our designs, implementations and experiments on developing a data management platform and a set of parallel techniques to support high-performance online spatial and temporal aggregations on multi-core CPUs and many-core Graphics Processing Units (GPUs). Our experiment results show that we are able to spatially associate nearly 170 million taxi pickup location points with their nearest street segments among 147,011 candidates in about 5–25s on both an Nvidia Quadro 6000 GPU device and dual Intel Xeon E5405 quad-core CPUs when their Vector Processing Units (VPUs) are utilized for computing intensive tasks. After spatially associating points with road segments, spatial, temporal and spatiotemporal aggregations are reduced to relational aggregations and can be processed in the order of a fraction of a second on both GPUs and multi-core CPUs. In addition to demonstrating the feasibility of building a high-performance OLAP system for processing large-scale taxi trip data for real-time, interactive data explorations, our work also opens the paths to achieving even higher OLAP query efficiency for large-scale applications through integrating domain-specific data management platforms, novel parallel data structures and algorithm designs, and hardware architecture friendly implementations.

GCUBE Indexing

Spatial Online Analytical Processing System involves the non-categorical attribute information also whereas standard Online Analytical Processing System deals with only categorical attributes. Providing spatial information to the data warehouse (DW); two major challenges faced are;1.Defining and Aggregation of Spatial/Continues values and 2.Representation, indexing, updating and efficient query processing. In this paper, we present GCUBE(Geographical Cube) storage and indexing procedure to aggregate the spatial information/Continuous values. We employed the proposed approach storing and indexing using synthetic and real data sets and evaluated its build, update and Query time. It is observed that the proposed procedure offers significant performance advantage.

A Novel Association Rule Hiding Approach in OLAP Data Cubes

Data mining services require exact input data for their outcomes to be significant, but privacy concerns may influence users to provide fake information. We study here, with respect to mining association rules, whether or not users can be confident to provide correct information by ensuring that the mining process cannot, with any reasonable degree of certainty, breach their privacy. A data warehouse stores current and historical records consolidated from multiple transactional systems. Protecting data warehouses is of rising interest, particularly in view of areas where data are sold in pieces to third parties for data mining studies. In this case, current normal data warehouse security techniques, like data access control, may not be easy to impose and can be in effective. As an alternative, this paper proposes a data perturbation based approach, to provide privacy preserving in association rule mining on data cubes in a data warehouse. In order to conceal association rules and save the utility of transactions in data cubes, we select Genetic Algorithm to find optimum state of modification. In our approach various hiding styles are applied in different multi-objective fitness functions. To cope with the multi-objective functions, Pareto-front ranking strategy has been applied for obtaining the non-dominated solutions front. First objective of these functions is hiding sensitive rules and the second one is keeping the accuracy of transactions in data cube. After sanitization process we test the sanitization performance by evaluation of various criterions. The major feature is that the proposed strategy does not affect the functionality of the On-Line Analytical Processing system. Finally our experimental results show its effectiveness and feasibility.

Constructing a water quality 2.0 OLAP system in Taiwan

Abstract Obtaining water suitable for human consumption is an important issue in both agricultural and industrialized countries. In areas with limited water supplies, water should be carefully managed by all users instead of by only the water agency. Therefore, a water monitoring system should allow interested monitors, such as government economists, researchers and public users, to participate in managing the water quality. However, most water information is distributed in formats that are not convenient for a general holistic analysis. Moreover, interpreting the output of environmental systems involves specialized domain knowledge that is not well understood by the public. How to integrate all of the available data to provide useful knowledge and understandable conclusions to users of different backgrounds is an important issue. This paper presents a water quality 2.0 OLAP system designed for the South Water Resources Bureau (TSWRB) of Taiwan. The concept of Web 2.0 has been adopted to integrate qualified data resources. In addition, an On-Line Analytical Processing (OLAP) system was used to analyze data from distributed resources to increase the understanding of water quality information of non-experts. This system provides a framework for all users to collaborate in monitoring water quality and in protecting limited water resources.

Using OLAP Systems to Manage Environmental Risks in Dambovita County

University of Edinburgh, School of Informatics, EH8 9AB, Edinburgh, Scotland, United Kingdom ; cristinutza0107@yahoo.com Abstract. This paper discusses the problem of multidimensional databases, with the purpose of demonstrating the efficient support offered by them for the management of OLAP processing and motivation of decisions, especially the ones related to environmental risks. The OLAP (On-Line Analytical Processing) systems that implement or use multidimensional databases are oriented towards decision makers and allow them to an easily process, sectioning and grouping of data, as well as the visualization of data from different perspectives, for the discovery of the factors that would influence a certain behavior. OLAP systems offer advanced statistical analysis facilities, such as the one based on time series or forecasts, which are based on predefined models. The calculation of the environmental risk offers an image of the degree of air pollution due to specific polluting factors, obtained especially as result of the industrial processes that have taken place in the Targoviste, in Dambovita area. An OLAP system, consisting of decision support components, was implemented as part of the PN2 three years research project: “Sustainable Management System of Resources Used for Monitoring and Evaluating the Environmental Risks in Order to Prevent the Negative Effects and to Manage Crises Situations - MEMDUR”, code D11-037/18.09.2007. Keywords: environmental risks, multidimensional databases, OLAP systems, SOLAP architecture, MEMDUR project.

A mathematical model of the OLAP cubes

In 1993, E. Codd introduced a concept of an OLAP (online analytical processing) system, which includes 12 rules of representing data for the user. As can be seen from the name, such systems are designed for analyzing data in an interactive mode. Hence, the basic goal of OLAP means is to represent large amount of data in the form convenient for the end-user analysis. Currently, the representation of data in the form of multidimensional cubes is a de facto standard of user work with large amount of data. In this paper, basic concepts of the OLAP systems are introduced and, then, formalized in terms of the lattice theory. In the frame-work of this formalization, the optimality (in terms of the amount of data stored) of the representation of the OLAP cubes by closed lattices or by quotient lattices (which are equivalent to the former) is proved.

A call detail records data mart: Data modeling and OLAP analysis

In order to succeed in the market, telecommunications companies are not competing solely on price. They have to expand their services based on their knowledge of customers' needs gained through the use of call detail records (CDR) and customer demographics. All the data should be stored together in the CDR data mart. The paper covers the topic of its design and development in detail and especially focuses on the conceptual/logical/physical trilogy. Some other design problems are also discussed. An important area is the problem involving time. This is why the implication of time in data warehousing is carefully considered. The CDR data mart provides the platform for Online Analytical Processing (OLAP) analysis. As it is presented in this paper, an OLAP system can help the telecommunications company to get better insight into its customers' behavior and improve its marketing campaigns and pricing strategies.

Improving the performance and functionality of Mondrian open‐source OLAP systems

AbstractFor a long time, the design of relational databases has focused on the optimization of atomic transactions (insert, select, update or delete). Currently, relational databases store tactical information of data warehouses, mainly for select‐like operations. However, the database paradigm has evolved, and nowadays on‐line analytical processing (OLAP) systems handle strategic information for further analysis. These systems enable fast, interactive and consistent information analysis of data warehouses, including shared calculations and allocations. OLAP and data warehouses jointly allow multidimensional data views, turning raw data into knowledge. OLAP allows ‘slice and dice’ navigation and a top‐down perspective of data hierarchies. In this paper, we describe our experience in the migration from a large relational database management system to an OLAP system on top of a relational layer (the data warehouse), and the resulting contributions in open‐source ROLAP optimization. Existing open‐source ROLAP technologies rely on summarized tables with materialized aggregate views to improve system performance (in terms of response time). The design and maintenance of those tables are cumbersome. Instead, we intensively exploit cache memory, where key data reside, yielding low response times. A cold start process brings summarized data from the relational database to cache memory, subsequently reducing the response time. We ensure concurrent access to the summarized data, as well as consistency when the relational database updates data. We also improve the OLAP functionality, by providing new features for automating the creation of calculated members. This makes it possible to define new measures on the fly using virtual dimensions, without re‐designing the multidimensional cube. We have chosen the XML/A de facto standard for service provision. Copyright © 2008 John Wiley & Sons, Ltd.