Design and implement OLAP solutions

Design and implement OLAP solutions

First Lecture Introduction
First, what is a data warehouse?

Data Warehouse is the warehouse of data! In a foreign language called data Warehouse, referred to as DW.

is not bang when down a piece of ah, or we change a professional point of view?

A data warehouse is a topic-oriented, integrated, relatively stable, data collection that reflects historical changes and contains business information to support management decisions.

Can you understand? I dare you to understand.

Forget it, don't call it real.

When you understand just take care, you will naturally understand what a data warehouse is.

There is a lot of data in the data Warehouse containing business information, but it is difficult to capture information from it, because the data warehouse generally has a large amount of data, it is cumbersome to organize the data.

OLAP system is to solve one of the goals.

First say what is OLAP, pronunciation oh le Puy. Online analysis processing, or can not understand it?

is to give you efficient access to the Data warehouse.

The corresponding one OLTP, called online transaction processing, is our usual database.

OLTP often has a large number of concurrent additions and modifications that change at any time, contain large amounts of data, and have complex structures.

When we load OLTP into the DW, it is generally no longer modified to update periodically as the data grows, rather than with frequently occurring transactions. The structure and security are also simplified to improve the efficiency of analytic queries rather than transactional processing.

There is also a data mart, called Data Mart, which is a special form of data warehousing, and a typical data mart contains a subset of enterprise data that is tailored to specific business functions for a particular topic.

You can think of a data mart as part of a data warehouse.

Second speaking OLAP Overview
OLAP technology enables data warehouses to respond quickly to repetitive and complex analytic queries, enabling data warehouses to be effectively used for online analysis. OLAP's multidimensional data model and data aggregation technology can organize and summarize large amounts of data so that data can be quickly evaluated using online analysis and graphical tools. When an analyst searches for answers or heuristics, it is often necessary to make further inquiries after getting answers to historical data queries. OLAP systems provide real-time support for analysts quickly and flexibly.

Typical OLAP applications include financial reporting, market analysis, marketing planning, customer service, and more.

At present, there are a lot of rich applications, such as banking, securities, telecommunications, production, sales industry, there are many success stories.

In OLAP, data is no longer stored in a relational structure, but in a multidimensional structure. No longer have the detail data, but only the information after roll up.

Microsoft's analysis service is a good OLAP system, not bragging about it, it processing speed, query speed, function complete, support massive data, and the amount of data after processing is very small, no data explosion problem.

Third Speaking Data Warehouse Structure

The basic structure of the Data Warehouse is the star schema and the snowflake schema, the stars structure and the snowflake structure.

There are two basic nouns to understand before understanding the structure: the fact table facttable and the Dimension tables dimension table.

What are facts and dimensions? I bought two pieces of chocolate in U-mart today, the price is 23.54￥.

This is a fact. The information contained therein is customer: me; date: Today; place: U-mart; product: Chocolate; quantity: 2; Price: 23.54￥. Customer, date, location, product is dimension dimension, quantity and price is measure value measure.

This is a star-shaped architecture.

The dimension table contains at least the dimensions of key and Name,key and name can be the same column. Dimensions may also have no dimension tables, such as a date dimension in this schema without a dimension table.

A cube schema There is only one fact table, but you can combine several base tables into a single view to make a fact table. (Cube's partition can use a different fact table, which is something.) )

Dimensions are hierarchical level, a dimension has at least two levels, and in most cases the members of the dimension are arranged in a pyramid-like layout, with the top always having a hierarchy of all, such as the Customer dimension (all Customers)-(customer)-two levels. In addition to the regular dimensions, dimensions include parent-child dimensions, virtual dimensions, and so on. The various dimensions are described in more detail later.

If a dimension has more than 2 levels, the dimension may have more than one dimension table, for example:

This is the snowflake structure.

From:http://dev-club.esnai.com/club/bbs/elist.asp?page=6&nofolder=&path=&boardid=23

Design and implement OLAP solutions