OLAP learning and instance building, and OLAP learning instance Building

OLAP learning and instance building, and OLAP learning instance Building

OLAP learning and instance Building

Theoretical preparation:

I. OLAP

1. What is OLAP?

OLAP (On-LineAnalysis Processing) is a fast analysis technology that shares multidimensional information. OLAP uses multidimensional database technology to enable users to observe data from different angles; OLAP is used to support complex analysis operations and focuses on the decision-making support for management personnel. It can meet the requirements of analysts for fast and flexible Big Data repeat complex queries, the query results are displayed in an intuitive and easy-to-understand form to assist in decision-making.

2. Related Concepts

(1) dimension

It is a specific angle for people to observe data. It is a type of attribute set that considers the problem to form a dimension (such as time dimension and geographical dimension ).

(2) Level)

A specific angle (I .e. a dimension) of people's observation data can also have different descriptions (such as time dimension: date, month, quarter, and year ). That is, the dimension level.

(3) Member (Member)

A value of a dimension is the description of the position of a data item in a dimension. ("A month or a day of a year" is a description of the time dimension ).

(4) Measurement)

The value of a multi-dimensional array, such as "salary of a month or a day of a year ".

(5) drilling (Drill-up and Drill-down)

Change the dimension level and the granularity of the analysis. Drill-up refers to summarizing low-level data to high-level summary data or reducing dimensions. drill-up refers to the opposite. It refers to penetrating summarized data into details or adding new dimensions.

(6) slice and cut surface

After you select a value for a certain dimension, you are concerned about the distribution of measurement data on the remaining dimension. If there are only two remaining dimensions, the slice is used. If there are three or more dimensions, the slice is used.

(7) Rotating

Is to change the direction of the dimension, that is, to reschedule the placement of the dimension in the table (such as row and column Interchange)

(8) Star Mode

A fact table consists of fact tables and dimension tables. A fact table includes all the foreign keys and measurements of the analysis dimension. A dimension table corresponds to the analysis angle and contains description and classification information in addition to the primary key.

(9) snowflake Mode

Sometimes, the definition of dimension tables becomes complicated. For example, for product dimension, the dimension table must be divided by product type, some special products, and other brands, product brands and product categories are not the same. Therefore, a single dimension table is not an ideal solution. You can use the following method. This data model is called the snowflake model.

2. Mondrian Learning

2.1 Mondrian Architecture

1) underlying database

2)Storage layerDatabase (data warehouse) Creation

The process of converting the original underlying database into a star or snowflake model

3)Dimension LayerSchema file (key part)

The data warehouse at the storage layer is converted into a schema file, which can be accessed through schema-workbench or handwriting.

4)Presentation LayerCompile a jsp file to show that it is displayed at the presentation layer provided by July.

July is the TagLib of Mondrian performance layer,

July is based entirely on JSP + TagLib;

Another thing that July may not be used to is that it is completely based on taglib rather than the familiar MVC pattern.

However, it can easily display multi-dimensional data to end users.

 

The following is the Mondrian architecture diagram provided on the official website. It is clear that the entire project consists of the underlying database, the data warehouse at the storage layer, the schema file at the dimension layer, and the presentation layer.

Mondrian provides a query API for the client.

So far, Mondrian does not have a common API for OLAP queries. Therefore, Mondrian provides its private API.

Even so, a person who often uses JDBC will also find it familiar. The difference is that it uses the MDX query language, not the SQL

The following java snippet shows how to connect to Mondrian, execute a query, and finally print the result.

 importmondrian.olap.*;  import java.io.PrintWriter;     Connectionconnection = DriverManager.getConnection("Provider=mondrian;"      +"Jdbc=jdbc:odbc:MondrianFoodMart;"+"Catalog=/WEB-INF/FoodMart.xml;",null,false);     Query query =connection.parseQuery("SELECT {[Measures].[Unit Sales], [Measures].[StoreSales]} on columns," +" {[Product].children} on rows "    +"FROM[Sales] " +"WHERE ([Time].[1997].[Q1], [Store].[CA].[SanFrancisco])");     Result result =connection.execute(query);     result.print(newPrintWriter(System.out));

Similar to JDBC, a Connection is created by DriverManager. A Query object is similar to a JDBC Statement. It is created by passing an MDX Statement. the Result object is similar to the JDBC ResultSet, except that it stores multi-dimensional data.

You can view the javadoc In the Mondrian help document to obtain