Design and Creation of Multidimensional Cube Based on SQL Server 2000 Analysis Service

Zhang You

2003-01-01

Abstract:This paper introduces the function and framework of SQL Server Analysis Service,gives details of the whole process of multidimensional cube creation with Analysis Service,which includes creating data dimension,storing multidimensional cube,processing data explosion and analyze multidimensional cube.

What problem does this paper attempt to address?

Development of Multiply Dimensions in Data Warehouse

张迎春,向阳

DOI: https://doi.org/10.3969/j.issn.1000-3428.2005.03.040

2005-01-01

Abstract:With the analysis of the cubes and dimensions with Microsoft SQL Server 2000 Analysis Services, it will take not only plenty of time and work but also much money to rebuild the database once the system breaks down. An effective method is offered to solve the problem automatically if the cubes and dimensions are set up programmatically through the decision support objects (DSO) library with C#. A sample is given to prove it.
Requirement-Based Data Cube Schema Design

Dw Cheung,B Zhou,B Kao,Hj Lu,Tw Lam,Hf Ting

DOI: https://doi.org/10.1145/319950.319974

1999-01-01

Abstract:On-line analytical processing (OLAP) requires efficient processing of complex decision support queries over very large databases. It is well accepted that pre-computed data cubes can help reduce the response time of such queries dramatically. A very important design issue of an efficient OLAP system is therefore the choice of the right data cubes to materialize. We call this problem the data cube schemer design problem. In this paper we show that the problem of finding an optimal data cube schema for an OLAP system with limited memory is NP-hard. As a more computationally efficient alternative, we propose a greedy approximation algorithm cMP and its variants. Algorithm cMP consists of two phases In the first phase, an initial schema consisting of all the cubes required to efficiently answer the user queries is formed. In the second phase, cubes in the initial schema are selectively merged to satisfy the memory constraint. Pile show that cMP is very effective in pruning the search space for an optimal schema. This leads to a highly efficient algorithm. We report the efficiency and the effectiveness of cMP via an empirical study using the TPC-D benchmark. Our results show that the data cube schemas generated by cMP enable very efficient OLAP query processing.
Generating Multidimensional Schemata from Relational Aggregation Queries

Zheng Huo,Kerry Taylor,Xiuzhen Zhang,Suzhen Wang,Chaoyi Pang

DOI: https://doi.org/10.1007/s11280-019-00706-9

2019-01-01

World Wide Web

Abstract:Designing a multidimensional model is a non-trivial task: the requirements collected from senior managers can be inaccurate and difficult to use, especially when they are not database practitioners. On the other hand the pressing need for data warehousing comes from the fact that managers submit analytical summary queries against operational relational databases. In this paper, we view these analytical queries as a more reliable basis for multidimensional schema design. We first study how to use such queries to automatically generate measures, dimensions and dimension hierarchies and their representation in a star schema. We then use the results studying the schema evolution problem: If a new query Q′ can’t be answered by the schema previously built, how to develop a new schema from the old one so that query Q′ can be answered in the new schema. Finally, we consider rewrite OLAP queries on conventional database (multidimensional data warehouse) using materialized views in data warehouse. In our method we analyze the attributes from the given relational queries and the dependency relationships among them to design a schema in which each dimension is a lattice. The schema can answer the queries that it was generated from and the number of dimensions is minimal. Furthermore, the schema can answer many more queries that are similar to the given ones.
An Algebra for Online Analytical Processing Data Cube

Dongqing Yang

1999-01-01

Journal of Software

Abstract:Data cube is the central mechanism in multidimension database and online analytical processing (OLAP) based on multidimensional analysis. In this paper, an algebra for OLAP data cube is proposed, which supports multidimensional database and analysis. It can be the theoretical foundation of semantic specification of data warehousing and OLAP manipulations. Some applications of the new mathematical tool are presented as well to show the power of the contribution.
An Improved Storage Algorithm for Multidimensional Data Cube

Haitao He,Yanpeng Zhang,Lining Li,Jiadong Ren,ChangZhen Hu

DOI: https://doi.org/10.1109/icicic.2009.90

2009-01-01

Abstract:There are 2n views in a n-dimensional data cube, the more the number of views, the more the maintenance time of data cube will be. According to the hierarchical feature of dimension in data cube, An Improved Storage Algorithm For Multidimensional Data Cube (ISMDC) is proposed in this paper. Dimensions are divided into association dimensions and no-association dimensions. The conception of Association Tree Cube is brought forward. On the no-association dimension, hierarchical B+ tree is used to remove redundancy and to form dimension hierarchical encoding. On the association dimension, encoding of the no-association dimension which is composed of dimension hierarchical encodings, is used for indexing. Thus, the value of aggregation will be searched out effectively. The experimental results show that ISMDC has reduced the storage requirement, maintenance time and improved the efficiency of data cube pattern update and the OLAP queries.
A Multi-Dimensional Data Model and its Application

Xinfeng Tian,Zhanhuai Li,Yongge Liu

DOI: https://doi.org/10.1109/HPC.2000.843560

2000-01-01

Abstract:A data model and the relevant algebraic operations are proposed in this paper to provide a semantic foundation for the multi-dimensional database systems. The feature of this model is to treat all dimensions and measures symmetrically. It provides supports for achieving multiple hierarchies along a single dimension, for operating ad hoc aggregates, and for accomplishing multi-dimensional operations, which are closed in an application. In addition, we present an application, which is built up based on the above model, in the Object-Relational Database Management System (ORDBMS). The problems about creating a multi-dimensional data structure and defining the operations are discussed in this application. Some problems that need to be solved in the future are pointed out at the end of this paper.
Spatial Data Cube: Provides Better Support for Spatial Data Mining

YZ Zhang,K Xie,X Ma,D Xu,C Cai,S Tang

DOI: https://doi.org/10.1109/igarss.2005.1525227

2005-01-01

Abstract:Spatial data mining is a promising technique that deals with extraction of implicit knowledge or other interesting patterns from large amount of spatial data. Though most data mining systems work with data stored in flat files or operational database, it has been recognized that mining in a data warehouse usually result in better information. Because data are usually cleansed before they are stored into data warehouse. Furthermore, data warehouse provides data with different levels of summarization for the clients, which will lead to fruitful data mining. However, current techniques of data warehouse can not handle spatial data well. Both dimensions and measures in the data model of data warehouse are nonspatial data. In this paper, we propose a new data model called spatial data cube for data warehouse. Spatial data cube supports both spatial and nonspatial data. We also introduce how to construct a spatial data cube that can answer queries efficiently by selective materialization. We believe that the spatial data cube can provide better support for spatial data mining.
Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals

Jim Gray,Surajit Chaudhuri,Adam Bosworth,Andrew Layman,Don Reichart,Murali Venkatrao,Frank Pellow,Hamid Pirahesh

DOI: https://doi.org/10.48550/arXiv.cs/0701155

2007-01-26

Abstract:Data analysis applications typically aggregate data across many dimensions looking for anomalies or unusual patterns. The SQL aggregate functions and the GROUP BY operator produce zero-dimensional or one-dimensional aggregates. Applications need the N-dimensional generalization of these operators. This paper defines that operator, called the data cube or simply cube. The cube operator generalizes the histogram, cross-tabulation, roll-up, drill-down, and sub-total constructs found in most report writers. The novelty is that cubes are relations. Consequently, the cube operator can be imbedded in more complex non-procedural data analysis programs. The cube operator treats each of the N aggregation attributes as a dimension of N-space. The aggregate of a particular set of attribute values is a point in this space. The set of points forms an N-dimensional cube. Super-aggregates are computed by aggregating the N-cube to lower dimensional spaces. This paper (1) explains the cube and roll-up operators, (2) shows how they fit in SQL, (3) explains how users can define new aggregate functions for cubes, and (4) discusses efficient techniques to compute the cube. Many of these features are being added to the SQL Standard.

Databases
Programming of Automatic and Incremental Update of Data Cube in MS OLAP

XIANG Yang,WANG Qingda,ZHANG Yingchun

DOI: https://doi.org/10.3969/j.issn.1000-3428.2005.20.027

2005-01-01

Abstract:MS OLAP service,a data warehouse software produced by Microsoft Corp is a time-consuming task to update cube completely.Although a cube can be updated by an incremental method manually in MS OLAP analysis manager,but it is so difficult and bothersome that only those being able to use MDX can accomplish it.This article gives a practical method of automatic and incremental update for data cube,and gives the programming sample developed in C#.
Holistic Cube Analysis: A Query Framework for Data Insights

Xi Wu,Shaleen Deep,Joe Benassi,Fengan Li,Yaqi Zhang,Uyeong Jang,James Foster,Stella Kim,Yujing Sun,Long Nguyen,Stratis Viglas,Somesh Jha,John Cieslewicz,Jeffrey F. Naughton

2023-07-01

Abstract:Many data insight questions can be viewed as searching in a large space of tables and finding important ones, where the notion of importance is defined in some adhoc user defined manner. This paper presents Holistic Cube Analysis (HoCA), a framework that augments the capabilities of relational queries for such problems. HoCA first augments the relational data model and introduces a new data type AbstractCube, defined as a function which maps a region-features pair to a relational table (a region is a tuple which specifies values of a set of dimensions). AbstractCube provides a logical form of data, and HoCA operators are cube-to-cube transformations. We describe two basic but fundamental HoCA operators, cube crawling and cube join (with many possible extensions). Cube crawling explores a region space, and outputs a cube that maps regions to signal vectors. Cube join, in turn, is critical for composition, allowing one to join information from different cubes for deeper analysis. Cube crawling introduces two novel programming features, (programmable) Region Analysis Models (RAMs) and Multi-Model Crawling. Crucially, RAM has a notion of population features, which allows one to go beyond only analyzing local features at a region, and program region-population analysis that compares region and population features, capturing a large class of importance notions. HoCA has a rich algorithmic space, such as optimizing crawling and join performance, and physical design of cubes. We have implemented and deployed HoCA at Google. Our early HoCA offering has attracted more than 30 teams building applications with it, across a diverse spectrum of fields including system monitoring, experimentation analysis, and business intelligence. For many applications, HoCA empowers novel and powerful analyses, such as instances of recurrent crawling, which are challenging to achieve otherwise.

Databases,Distributed, Parallel, and Cluster Computing,Programming Languages
Star Cube -An Approach to Implementing Data Cube Efficiently

李盛恩,王珊

2004-01-01

Abstract:A data cube of n dimensions has 2n views. The more the views, the more the maintenance time of data cube. By dividing dimensions of a data cube into partition dimensions and no-partition dimensions, a data cube can be transformed to a star cube, which includes a summary table and views consisting of partition dimensions. Star cube uses prefix and tuple sharing technology to reduce the storage requirement, computation time and maintenance time. By confining the size of a fragment to one I/O unit, the query response time of star cube is almost the same as data cube. The results of experiments also show that star tube is a promising way to implement data cube.
Data Warehouse Enhancement: A Semantic Cube Model Approach

Shi-Ming Huang,Tung-Hsiang Chou,Jia-Lang Seng

DOI: https://doi.org/10.1016/j.ins.2006.12.022

IF: 8.1

2007-01-01

Information Sciences

Abstract:Many data warehouse systems have been developed recently, yet data warehouse practice is not sufficiently sophisticated for practical usage. Most data warehouse systems have some limitations in terms of flexibility, efficiency, and scalability. In particular, the sizes of these data warehouses are forever growing and becoming overloaded with data, a scenario that leads to difficulties in data maintenance and data analysis. This research focuses on data-information integration between data cubes. This research might contribute to the resolution of two concerns: the problem of redundancy and the problem of data cubes' independent information. This work presents a semantic cube model, which extends object-oriented technology to data warehouses and which enables users to design the generalization relationship between different cubes. In this regard, this work's objectives are to improve the performance of query integrity and to reduce data duplication in data warehouse. To deal with the handling of increasing data volume in data warehouses, we discovered important inter-relationships that hold among data cubes, that facilitate information integration, and that prevent the loss of data semantics.
XML Based Data Cube and X-OLAP

Xiaoling Wang,Yisheng Dong

DOI: https://doi.org/10.3969/j.issn.1003-7985.2001.02.002

2001-01-01

Abstract:Data warehouse provides storage and management for mass data, but data schema evolves with time on. When data schema is changed, added or deleted, the data in data warehouse must comply with the changed data schema, so data warehouse must be re-organized or re-constructed, but this process is exhausting and wasteful. In order to cope with these problems, this paper develops an approach to model data cube with XML, which emerges as a universal format for data exchange on the Web and which can make data warehouse flexible and scalable. This paper also extends OLAP algebra for XML based data cube, which is called X-OLAP.
Turning Multidimensional Big Data Analytics into Practice: Design and Implementation of ClustCube Big-Data Tools in Real-Life Scenarios

Alfredo Cuzzocrea,Abderraouf Hafsaoui,Ismail Benlaredj

2024-07-26

Abstract:Multidimensional Big Data Analytics is an emerging area that marries the capabilities of OLAP with modern Big Data Analytics. Essentially, the idea is engrafting multidimensional models into Big Data analytics processes to gain into expressive power of the overall discovery task. ClustCube is a state-of-the-art model that combines OLAP and Clustering, thus delving into practical and well-understood advantages in the context of real-life applications and systems. In this paper, we show how ClustCube can effectively and efficiently realizing nice tools for supporting Multidimensional Big Data Analytics, and assess these tools in the context of real-life research projects.

Databases
Meta Galaxy: A Flexible and Efficient Cube Model for Data Retrieval in OLAP

Jie Zuo,Changjie Tang,Lei Duan,Yue Wang,Liang Tang,Tianqing Zhang,Jun Zhu

DOI: https://doi.org/10.1007/978-3-642-03996-6_22

2009-01-01

Abstract:OLAP is widely used in data analysis. The existing design models, such as star schema and snowflake schema, are not flexible when the data model is changed. For example, the task for inserting a dimension may involve complex operations over model and application implementation. To deal with this problem, a new cube model, called Meta Galaxy, is proposed. The main contributions of this work include: (1) analyzing the shortcoming of traditional design method, (2) proposing a new cube model which is flexible for dimension changes, and (3) designing an index structure and an algorithm to accelerate the cube query. The time complexity of query algorithm is linear. The extensive experiments on the real application and synthetic dataset show that Meta Galaxy is effective and efficient for cube query. Specifically, our method decreases the storage size by 95.12%, decreases the query time by 89.89% in average compared with SQL Server 2005, and has good scalability on data size.
Constructing Cube Blocks Effectively for Stream Data Analysis

Lizheng Jiang,Dongqing Yang,Shiwei Tang,Xiuli Ma,Dehui Zhang

DOI: https://doi.org/10.1109/WAIMW.2006.10

2006-01-01

Abstract:With the rapid growth of WWW, many applications based on web are generating tremendous amount of data. Analyzing and mining such data will be important for administrators and other users. Because these data are increasing continuously and rapidly, retrieving these data efficiently and effectively are challenging tasks. In this paper, we propose a new Cube Block model to organize multi-dimensional data efficiently for online analysis and mining. The main idea of Cube Block model is to split the whole data set into blocks by dividing time into exponential time frames. In each block, we omit individual time identifiers of data items and aggregate duplicate data items to one data cell. Cube Block model uses the approximating method to get historical data in any time interval. The approximation accuracy is guaranteed by an upper bound and a lower bound. For the implementation of Cube Block model, we develop the CB-Builder system to scan data and construct cube blocks. CB-BuilderÂ¿s algorithm complexity (runtime and space cost) is analyzed in this paper. Experiments on synthetic data sets demonstrate that Cube Block model with its implementation CB-Builder is an effective and efficient tool for web application data analysis and mining.
A Clustered Dwarf Structure to Speed Up Queries on Data Cubes

Yukun Bao,Fangling Leng,Daling Wang,Guanding Yu

DOI: https://doi.org/10.5626/jcse.2007.1.2.195

2007-01-01

Journal of Computing Science and Engineering

Abstract:Dwarf is a highly compressed structure, which compresses the cube by eliminating the semantic redundancies while computing a data cube. Although it has high compression ratio, Dwarf is slower in querying and more difficult in updating due to its structure characteristics. We all know that the original intention of data cube is to speed up the query performance, so we propose two novel clustering methods for query optimization: the recursion clustering method which clusters the nodes in a recursive manner to speed up point queries and the hierarchical clustering method which clusters the nodes of the same dimension to speed up range queries. To facilitate the implementation, we design a partition strategy and a logical clustering mechanism. Experimental results show our methods can effectively improve the query performance on data cubes, and the recursion clustering method is suitable for both point queries and range queries.
Dynamic construction of user defined virtual cubes

Dehui Zhang,Shaohua Tan,Dongqing Yang,Shiwei Tang,Xiuli Ma,Lizheng Jiang

DOI: https://doi.org/10.1007/11780991_25

2006-01-01

Abstract:OLAP provides an efficient way for business data analysis. However, most up-to-date OLAP tools often make the analysts lost in the sea of data while the analysts usually focus their interest on a subset of the whole dataset. Unfortunately, OLAP operators are usually not capsulated within the subset. What's more, the users' interests often arise in an impromptu way after the user getting some information from the data. In this paper, we give the definition of users' interests and propose the user-defined virtual cubes to solve this problem. At the same time, we present an algorithm to answer the queries upon virtual cube. All the OLAP operators will be encapsulated within this virtual cube without superfluous information retrieved. Experiments show the effectiveness and efficiency of the virtual cube mechanism.
Dcube: Cube On Dirty Databases

Guohua Jiang,Hongzhi Wang,Shouxu Jiang,Jianzhong Li,Hong Gao

DOI: https://doi.org/10.1007/978-3-642-14246-8_49

2010-01-01

Abstract:In the real world databases, dirty data such as inconsistent data, duplicate data affect the effectiveness of applications with database. It brings new challenges to efficiently process OLAP on the database with dirty data. CUBE is an important operator for OLAP. This paper proposes the CUBE operation based on overlapping clustering, and an effective and efficient storing and computing method for CUBE on the database with dirty data. Based on CUBE, this paper proposes efficient algorithms for answering aggregation queries, and the processing methods of other major operators for OLAP on the database with dirty data. Experimental results show the efficiency of the algorithms presented in this paper.
Multidimensional query and analysis system of college finance

PENG Cheng,TONG Qiu-li

DOI: https://doi.org/10.16208/j.issn1000-7024.2012.05.066

2012-01-01

Abstract:To query financial data from multiple dimensions,support analysis and decision,a financial analyzer based on the MDX(multidimensional expression) technology is presented to realize the function of establishing multidimensional data set from source database,querying financial data from multiple dimensions based on MDX,and analysis of result data.To assist management and support decision making,modules are designed on saving results in Excel,user and group management,permission settings,table nickname settings,etc.Finally,a case study of query is presented to compare with SQL,results indicate the proposed analyzer is better in efficiency and help decision.

Design and Creation of Multidimensional Cube Based on SQL Server 2000 Analysis Service

Development of Multiply Dimensions in Data Warehouse

Requirement-Based Data Cube Schema Design

Generating Multidimensional Schemata from Relational Aggregation Queries

An Algebra for Online Analytical Processing Data Cube

An Improved Storage Algorithm for Multidimensional Data Cube

A Multi-Dimensional Data Model and its Application

Spatial Data Cube: Provides Better Support for Spatial Data Mining

Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals

Programming of Automatic and Incremental Update of Data Cube in MS OLAP

Holistic Cube Analysis: A Query Framework for Data Insights

Star Cube -An Approach to Implementing Data Cube Efficiently

Data Warehouse Enhancement: A Semantic Cube Model Approach

XML Based Data Cube and X-OLAP

Turning Multidimensional Big Data Analytics into Practice: Design and Implementation of ClustCube Big-Data Tools in Real-Life Scenarios

Meta Galaxy: A Flexible and Efficient Cube Model for Data Retrieval in OLAP

Constructing Cube Blocks Effectively for Stream Data Analysis

A Clustered Dwarf Structure to Speed Up Queries on Data Cubes

Dynamic construction of user defined virtual cubes

Dcube: Cube On Dirty Databases

Multidimensional query and analysis system of college finance