OLAP tools and business intelligence

In order to determine the business activities carried out by the business strategy and market strategy, enterprises must analyze and make decisions based on multiple reports and reports in the analysis and decision-making process of Bi projects. Most of the ideal marketing activities require sales reports for each business point, and performance charts for each product to be sold by season.

A large amount of accurate and easy-to-Judge data is required.

However, it is a huge workload for general employees or IT department employees as users. Because data analysis requires that necessary data information be obtained first, the data collection and data processing and calculation methods must be known in advance. Of course, it is also necessary to master some specialized knowledge about the database structure and data access language.

 

Therefore, all these tasks were previously completed by IT department personnel using OLAP products. The Information Management Department should design the report format according to the user's requirements, and then develop applications and create databases for the user's purpose.

 

What is an OLAP report tool?

 

In the report market, there is a strange phenomenon: the technical staff of the IT department is the department staff most unfamiliar with the use of report tools in all departments of the enterprise, but the report data comes from the IT department. The IT department often uses the OLAP concept to build a data model and create reports based on the data model. Therefore, for IT departments, report tools refer to the report Presentation Section of OLAP tools, such as crystal report.

 

In the eyes of business personnel who use reports, report tools represent the functions (typographical, computing, statistics, and graphics) of reports. Currently, only one product represents the report, is excel.

 

What are the biggest difficulties of OLAP report products?

 

Currently, the most difficult part of the report tool is not the report style (such as diagonal lines). Although the style is cumbersome, it is not essential. The most fundamental difficulty is that the business department knows the true meaning of the report representative, but does not know the report data statistical model. The IT department understands the description of the business department, it is difficult to understand the value of the report itself to set up a data statistics model on the Database End.

 

As a result, the report tool cannot take both of them into consideration. the OLAP report tool products have been balancing the data model design (OLAP) and the functions of the report itself.

 

At present, the production of OLAP report products is complex. What are the common symptoms of reports?

 

First, because the data statistics model created by the IT department is not fully adapted, the report production often needs to write code and prepare data (such as dozens or even hundreds of rows of SQL or stored procedures ), in addition, complicated sub-Table Joining is required. Even if many reports cannot be completed, you need to discuss the changes with the user, and the computing performance is poor.

 

Secondly, since the IT Department does not have a professional understanding of the report style when making reports based on the business department, most reports are dragged and edited, which makes it difficult to draw the report style.

 

Finally, the business department reports are frequently changed, leading to a lag in IT department model design and report production. The business department's work is limited and wasted time.

 

Therefore, under the current OLAP product design, Bi projects become a daily statistical system. The business model comes from consulting experts. During enterprise development, changes in business models cannot be quickly implemented due to OLAP tools, loss of confidence in Bi. It is no exaggeration to say that OLAP products are destroying bi.

 

OLAP error or user error?

 

This is a confusion! In fact, we can find the confused answer from the OLAP concept defined by Dr. E. F. codd. OLAP is the father of relational databases E. f. A dynamic data analysis model proposed by codd in 1993, which allows access to data aggregated and organized by commercial data sources in a multidimensional structure called Multidimensional Dataset. As a standard, OLAP, as a separate type of product, is clearly differentiated from the same host Transaction Processing (OLTP.

 

It is a bit esoteric, but not complex. There are only three basic concepts of OLAP: multi-dimensional observation, data drilling, and cube operations.

 

Multi-dimensional Perspective: we encounter various problems in our daily work. When we analyze the problem, we will consider the same phenomenon from multiple perspectives, sometimes we can make a comprehensive analysis from several perspectives. This is the most basic concept of OLAP analysis-flexible combination of multiple observations to observe the data, so as to discover the internal laws of the data.

 

OLAP divides data into two types of features: performance features, such as sales and gross profit in a sales analysis model, and angle features, such as the time period, product type, sales model, and sales region in the sales analysis. The former is the object to be observed, and the OLAP term is referred to as "metric data". The latter is the perspective of observation, and the OLAP term is referred to as "Dimension Data ".

 

If such a model is created, we can choose the product type based on the business needs, observe the sales data of each sales region (dimension by product type and sales region, measured by sales); or from the perspective of sales model, observe the sales data of each sales region (dimension by sales model and sales region, and measurement by sales ).

 

About data drilling: In the analysis process, we may need to further refine the existing data to get a more accurate understanding. This is the concept of data drilling in OLAP.

 

For example, in the sales analysis, when we make an analysis based on the product type and sales region and the sales volume, we may want to further observe the performance of different sales models of a product in each region. Then we can add a sales model dimension under the data dimension of the product category, to obtain the corresponding information.

 

About cube operations: the raw data size required for OLAP analysis is very large. An analysis model usually involves millions or tens of millions of data records. The analysis model contains multiple dimensions, these dimensions can be extracted and combined by the viewer. The result is time delay caused by a large number of real-time operations.

 

We can imagine a 10 million-Record Analysis Model. If we extract four dimensions for combined analysis at a time, the actual number of computations will reach the fourth power. This calculation will lead to dozens of minutes or even longer waiting time. If you adjust or increase or decrease the order of dimension combinations, It is a re-calculation process.

 

From the above analysis, we can draw a conclusion that if the problem of OLAP computing efficiency cannot be solved, OLAP will be a concept of no practical value. So how does a mature product solve this problem? This involves a very important technique in OLAP-data cube pre-calculation.

 

In an OLAP model, we should determine metric data and dimension data in advance. Once the two are determined, we can process the data in advance. Before the official release, perform the maximum clustering operation on the Data Based on dimensions. The calculation takes into account the combination of various dimensions. The calculation result generates a data cube and stores it on the server.

 

In this way, when the end user subscribes to this analysis model, the cube can be directly used for re-calculation based on the user's dimension selection and dimension combination to achieve real-time response.

 

Starting from the above three basic concepts of OLAP, we can find the problem in practice. The OLAP concept is not wrong, and the user is not wrong. The error lies in the current design ideas of OLAP products in the industry!

 

From the perspective of OLAP products, the "multidimensional perspective" changes from the user department, while the OLAP products adopted by the IT department convert the "multidimensional perspective" into a database design, however, to implement "cube operations", "metric data" and "Dimension Data" must be solidified in advance, which limits the business department's requirements for rapid changes in "multidimensional Angles, the Bi project becomes a daily Statistical Report Project, making OLAP analysis impossible.

 

OLAP products require a new generation of tools

 

The design philosophy of the next-generation OLAP tool should not focus on the functions of the report tool itself: IT departments should not make reports, only focus on OLAP functions, and do not need to present OLAP reports, reports are fully implemented by the business department. It is mainly based on the following two points:

 

I. according to the usage and usage of desktop reports, the number of employees in the business department is far greater than that in the IT department. Therefore, IT departments are unlikely to provide a report tool, to replace the desktop report tool used by the business department.

 

II. the meaning of the report must be interpreted by the business department with superb business knowledge. If the report is made by the IT department, errors in the knowledge transfer process will occur, which is the biggest problem in OLAP implementation.

 

Based on the above two points, the new-generation OLAP tool design philosophy is: how to make OLAP tools and Excel report tools can communicate seamlessly, there should be a "analysis perspective" technology, business Departments and IT departments can express each other in multiple dimensions. Let the business department "analyze" and "report" on its own, so that the IT department can use the OLAP concept to design basic data.