Data cube----dimension and OLAP

One of the previous articles-the Data warehouse multidimensional data model already provides a brief description of the definition and structure of an overly-dimensional model, as well as the concept of the fact table and the dimension table (Dimension table). Multidimensional data model, as a new logical model, gives the new organization and storage form of data, and the advantage of the analysis is the effective operation and processing based on the model, which is OLAP (On-line Analytical processing, online analytical processing).

Data cube

As for the data cube, it is important to note that the data cube is just an image of a multidimensional model. Cube itself is only three-dimensional, but the multidimensional model is not limited to three-dimensional model, can be combined with more dimensions, but on the one hand is more convenient to interpret and describe, but also to the thinking of imaging and imagination space, on the other hand is to distinguish with the traditional relational database of the two-dimensional table, so there is a data cube called. So this article is also the reference cube, that is, the multidimensional model to the three-dimensional way to represent and describe, in fact, Google image search "OLAP" There will be a lot of data cube pictures, here I drew a:

Olap

　　OLAP (On-line Analytical processing, online analytical Processing) is a collection of analysis-oriented operations implemented on the basis of multidimensional models of data warehouses. It can be compared with traditional OLTP (on-line Transaction processing, online transaction processing) to look at its characteristics:

OLAP and OLTP

Data processing Type	Oltp	Olap
Object oriented	Business Development Staff	Analysis decision makers
function implementation	Daily transaction Processing	Analytics-oriented decision making
Data model	Relational model	Multidimensional Models
Data volume	Several or dozens of records	Millions records
Type of operation	Query, insert, UPDATE, delete	Query-based

Types of OLAP

The first thing to declare is that the multidimensional data model and OLAP content presented here are basically based on ROLAP, because there are few other types of contact, and there is not much information involved.

MOLAP (Multidimensional)

　　That is, the storage model based on multidimensional array, is also the most primitive OLAP, but need to preprocess data to form multidimensional structure.

ROLAP (relational)

　　More common types of OLAP, described here and discussed are basically ROLAP type, can be seen from the multidimensional data model of the article, in fact, ROLAP is completely based on the relationship model for storage, but it is based on the needs of the analysis of the model structure and organization of the optimization, more conducive to OLAP.

HOLAP (Hybrid)

　　In the type of MOLAP and ROLAP, my understanding is that details of data are stored in ROLAP form, more convenient and flexible, and highly aggregated data are presented in MOLAP form, which is more suitable for efficient analytical processing.

In addition, there are Wolap (web-based OLAP), Dolap (Desktop OLAP), Rtolap (real-time OLAP), specifically to open the Wikipedia explanation--olap.

Basic operations for OLAP

We already know that OLAP operations are based on query-that is, the database's select operation, but the query can be very complex, such as a relational database-based query can be multi-table association, you can use the count, SUM, AVG and other aggregate functions. OLAP is based on a multidimensional model that defines some common analysis-oriented types of operations that are more intuitive.

Multidimensional analysis operations for OLAP include: drillthrough (drill-down), roll up (roll-up), slicing (Slice) , (Dice) and rotation (Pivot), the following is an example of the data cube above to explain each of the following:

　　drillthrough (Drill-down): Changes in the different levels of the dimension, from the upper layer down to the next level, or to split the aggregated data into more detailed data, such as by drilling through the second quarter of 2010, to see the second quarter of 2010 4, 5, 6 monthly consumption data, such as; Of course, you can also drill down to Zhejiang province to view Hangzhou, Ningbo, Wenzhou ... Sales data for these cities.

　　Roll up (roll-up): The inverse operation of drilling, that is, from fine-grained data to high-level aggregation, such as Jiangsu Province, Shanghai City and Zhejiang Province, the sales data summarized to view the Zhejiang-Shanghai area sales data, such as.

　　Slice (Slice): Select a specific value in the dimension for analysis, such as selecting only sales data for electronic products, or data for the second quarter of 2010.

　　Cut (Dice): Select data for a specific interval in a dimension or a specific value for analysis, such as sales data for the first quarter of 2010 through the second quarter of 2010, or for electronic products and commodities.

　　rotation (Pivot): That is, the position of the dimension of the interchange, like a two-dimensional table of the row and column conversion, through the rotation of the product and the geographical dimension of the interchange.

Benefits of OLAP

First of all, the advantage of OLAP is based on the Data Warehouse theme-oriented, integrated, retention history and non-changeable data storage, as well as multidimensional Model multi-view multi-level data organization, if the separation of these two points, OLAP will no longer exist, there is no advantage to say.

How data is presented

Data organization based on multidimensional model makes the presentation of data more intuitive, it is like the way we look at all kinds of things, we can find the different characteristics of things from multiple angles, and OLAP is applying this kind of ordinary thinking model to data analysis.

Query efficiency

Multidimensional model is based on the optimization of OLAP operations, such as the index based on the various dimensions, for some common queries built views, and so on, these optimizations make millions even billions of orders of magnitude of operations become handy.

The flexibility of analysis

We know that multidimensional data models can observe data from different angles and levels, and can be aggregated, subdivided, and selected using the various OLAP operations described above, thus improving the flexibility of analysis and the ability to subdivide and summarize data from different angles at different levels to meet the needs of different analyses.

Do not think that in fact, OLAP is not as complex as imagined, once the multidimensional data model is built, OLAP in the above is actually a very cool thing.

Source: http://webdataanalysis.net/web-data-warehouse/data-cube-and-olap/

Data cube----dimension and OLAP