Online Analytical Processing for Oracle (OLAP)

The concept of online analytical processing (OLAP) was first proposed by the father of the relational database, E.f.codd, in 1993, and he also presented 12 guidelines on OLAP. OLAP has aroused a great deal of response, OLAP as a class of products and online transaction processing (OLTP) clearly distinguished.

Today's data processing can be broadly divided into two broad categories: online transaction processing OLTP (on-line transaction processing), online analytical processing OLAP (On-line Analytical Processing). OLTP is the main application of the traditional relational database, mainly basic and daily transaction processing, such as bank transaction. OLAP is the main application of Data Warehouse system, support complex analysis operation, focus on decision support, and provide intuitive and understandable query results. The following table lists the comparisons between OLTP and OLAP.

tr>

	OLTP	olap
user	operator, low-level manager	> Decision makers, senior management
function	daily exercise Handle	analysis decision
db Design	application-oriented	theme-oriented
data	> Current, newest detail, two-dimensional, discrete	Historical, aggregated, multidimensional integrated, unified
Access	read/write dozens of records	Read on millions record
	simple transaction	complex query
number of users		Hundreds of
	100GB-TB

OLAP is a kind of software technology that enables analysts, managers, or executives to access information quickly, consistently and interactively from multiple perspectives to gain a deeper understanding of the data. The goal of OLAP is to satisfy the decision support or meet the specific query and report requirements in multidimensional environment, and its technical core is the concept of "dimension".

"Dimension" is the angle that people observe the objective world, and it is a kind of high-level classification. "Dimensions" generally contain hierarchical relationships, which can sometimes be quite complex. By defining several important attributes of an entity as multiple dimensions (dimension), the user can compare the data on different dimensions. Therefore, OLAP can also be said to be a collection of multidimensional data analysis tools.

Basic Multidimensional Analysis operations for OLAP include drillthrough (roll up and drill down), slices (slice) and dice (dice), and rotation (pivot), drill across, drill through, and so on.

• Drill-through is to change the dimension of the level, transformation analysis of granularity. It consists of an upward drilling (roll up) and a downward drill (drill down). Roll up is a dimension that summarizes low-level detail data to a higher level of aggregated data, or reduces the number of dimensions, while drill down is the opposite, from summarizing data to detail data to observing or adding new dimensions.
• Slicing and cutting are the distributions of the metric data on the remaining dimensions after the selected value on a part of the dimension. If the remaining dimension is only two, it is a slice; if there are three, it is diced.
• Rotation is the direction of the transformation dimension, that is, rearranging dimension placement (for example, row and column swaps) in a table.

OLAP has a variety of implementation methods, depending on how the data stored in different ways can be divided into ROLAP, MOLAP, HOLAP.

ROLAP represents an OLAP implementation based on relational databases (relational OLAP). The relational database is the core of the multidimensional data representation and storage. ROLAP divides the multidimensional structure of multidimensional databases into two types of tables: a fact table for storing data and dimension keywords, and a dimension table that uses at least one table for each dimension to hold descriptive information about dimensions, member categories, and so on. The dimension table and the fact table are linked together by a primary key and an external keyword, forming a "star pattern". For complex dimensions, multiple tables can be used to prevent redundant data from consuming too much storage space, which is called "Snowflake mode".

MOLAP represents an OLAP implementation (multidimensional OLAP) based on a multidimensional data organization. The core of multidimensional data organization, that is, MOLAP uses multidimensional arrays to store data. Multidimensional data in the storage will form a "cubic block (cube)" Structure, in MOLAP "cubic block" of "rotation", "cut", "slice" is the main technology to produce multidimensional data reports.

HOLAP represents an OLAP implementation (Hybrid OLAP) based on mixed data organizations. If the low-level is a relational type, the high-level is multidimensional matrix type. This approach has better flexibility.

There are other ways to implement OLAP, such as providing a dedicated SQL Server that provides special support for SQL queries for some storage modes, such as Star, Snowflake.

OLAP tools are online data access and analysis for specific issues. It analyzes, queries and reports on data in multidimensional ways. The dimension is a specific angle that people observe data. For example, when an enterprise considers the sales of a product, it usually takes a deep look at the sales of the product from different angles of time, region, and product. The time, the region and the product here are the dimensions. The multidimensional arrays of these dimensions and the measured indices are the basis of OLAP analysis, which can be formalized as (Dimension 1, Dimension 2, ..., dimension n, metric), such as (region, time, product, sales). Multidimensional Analysis refers to the multidimensional form of the data organized by slicing (Slice), Cut (Dice), drilling (Drill-down and roll-up), rotation (Pivot) and other analytical actions in order to analyze the data, so that users can from multiple angles, multi-angle to observe the data in the database To get an in-depth understanding of the information contained in the data.

According to the different organization of the comprehensive data, the common OLAP is mainly based on the multidimensional database of MOLAP and two kinds of ROLAP based on the relational database. MOLAP organizes and stores data in multidimensional ways, while ROLAP uses existing relational database techniques to simulate multidimensional data. In Data Warehouse application, OLAP application is generally the front-end tool of data Warehouse application, and OLAP tools can be used with data mining tools and statistic analysis tools to enhance the function of decision analysis.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/