Bi Road Learning Note 3--olap Cube Understanding examples

Why is OLAP generated?

With the development of database technology, the amount of data stored in database is transition from M-byte and g (Gigabit) bytes to T-byte and P-byte, meanwhile, the user's query needs are more and more complex, and the design is not only to query or manipulate one or several records in a relational table, Moreover, the data analysis and information synthesis of the thousands records in many times, the relational database can not fully meet this requirement.

Decision data is multidimensional data, multidimensional data is the main content of decision-making, OLAP is designed to support complex analysis operations, focus on decision-makers and senior management decision-making support, can be based on the requirements of analysts fast and flexible large data volume of complex query processing, and provide the query results to decision-makers in an intuitive manner so that they can accurately grasp the business situation of the enterprise or company, understand the needs of the object, and specify the correct plan.

Dimensions and metrics are the concepts in OLAP cube:
Dimension is equivalent to coordinate system on axis, such as time, department;--dimension table
Metrics are data that can be reflected in a report, such as sales;--Fact sheet

So what do OLAP do with these dimensions and metrics? In fact, it's easy to use different dimensions for different business needs .

For example, to show the company's sales in the first quarter of 2009, we need to analyze the measurement of sales from the time dimension;

If you want to show sales for a department, analyze sales from the Department dimension.

Of course, there are businesses like this: Showing sales of department A in the first quarter of 2009, it needs to be analyzed from two dimensions.

After the full installation of all the SQL Server components, use Books Online: ODS---ODS (Operational Data Store) is an optional part of the Data Warehouse architecture that has some of the features of the data warehouse and some features of the OLTP system, which is " Topic-oriented, integrated, current, or near-current, ever-changing "data."
DW---Data Warehouse, the English name is Warehouse, can be abbreviated as DW
DM---Data mining (minning)
OLAP---Online analytical processing, English name on-line Analysis processing, abbreviated to OLAP
OLTP---on-line Transaction processing online transaction processing system (OLTP) SQL Server BI:
SQL Server Enterprise Edition comes with three services: SQL Server Integration Service, SQL Server Analysis Service,sql Server Reporting service. These three services are for BI services, can be used alone, and can be used together.
Three services typically work around a data warehouse (Dateware House, or DW).
The general Data Warehouse is essentially an ordinary relational database, but it is designed specifically for the characteristics of BI.It is generally made up of fact tables and dimension tables. For example, in an ordinary e-commerce website, every purchase behavior forms a fact data, and the product (Big category, small category, price, etc.) that the fact data relate to, the customer (contact way, geographical location, etc.) is the dimension. This database, which consists of fact tables and dimension tables, can greatly facilitate future queries and analysis, and is of high performance (still dependent on design).
The SQL Server integration Service, which is used primarily to extract data incrementally from the original database (SQL Server/oracle/mysql/xml/excel, etc.), is cleaned, consolidated, and evaluated and loaded into the Data warehouse. The integration project can run as a job on a regular basis in SQL Server agent.
SQL Server Analysis Service, which is used primarily to analyze data in a data warehouse. The analysis development is primarily built on a multidimensional data model that can be stored in the SQL Server analysis Service or elsewhere after the model is established.
SQL Server Reporting Service, which can generate a report (Table/matrix/graph) After the data source is linked. You can use Analysis Service as a data source, or you can use any database directly as a data source.
In fact, the application of these three services is very flexible. The resulting report style is flexible, and reports can be exported to a variety of common formats (EXCEL,PDF,XML,WORD,TIFF, etc.).
--Reference: Http://blog.csdn.net/flyly88/article/details/5945981#comments

Bi Road Learning Note 3--olap Cube Understanding examples