January 7, 2013

OLAP Revolutionary: From Train to Car



The traditional OLAP tools are just the “OLAP in its narrowest senses” or “subset of OLAP”. It is neither intuitive, nor fast or flexible. They can only be used to analyze the existing model. Faced with the fast-changing requirements, they need IT pros to build a new model, which will often let the valuable business opportunities slip away.

Let me explain this to you in a metaphor you will understand: To go holidays from Salt Lake City to Las Vegas, you can take advantage of the existing railway that is quite convenient for tourism. However, travel by train becomes an inconvenient choice if holidaying in Yellowstone National Park because the railway is not available yet. Even if anyone starts to pave the railway, the snow may start to fall when it gets ready.

In view of this, we need a car or a vehicle can be driven away by average person to go right way, so that we will not miss the beautiful sunrise over Yellowstone Lake in summer.
“Generalized OLAP” or “True OLAP” must have the capability to remedy seven drawbacks of traditionalOLAP tools, revolutionize the OLAP from train to car, and implement the true essence of On Line Analytical Processing (OLAP).


1. Modeling is not a must
In processing business data, the true OLAP tools do not need modeling first to kick off the analysis. The true OLAP tools should be equipped with an excellent OLAP engine to transform data to a pattern for business personnel to operate on directly. 

Assume that a telecommunications enterprise needs to analyze its earnings. The analyzer should be allowed to watch the business data directly. Based on the characteristics of the existing data, extract the data directly and analyze immediately, without having to setting up a complex analytical model first. For example, users are allowed to analyze the earnings on the basis of date, region, client gender, client’s occupation, credit ratings, and other dimensions; If the “sales outlet” required for analysis is not in the existing database but in the Excel, then analyzers must be allowed to consolidate the data from various sources for computation, needless to modify the model from infrastructure.

The OLAP tool not requiring modeling simplifies the implementation procedure, shortens the work cycle, and avoids the expensive upfront cost.

2. Business personnel can use it independently
The true OLAP tool should be such a tool that business personnel can use it independently. Business can take it for granted that the assistance from IT is not necessary, not requiring the code/script/SQL statements or a complex modeling procedure.

The business personnel should be able to review the business data by themselves, and extract these data to OLAP tool through a wizard that is simple and easy to understand. Through Ctrl-C + Ctrl-V, they can also copy data from Excel or text file to OLAP tools. The analysis interface should be easy for those business personnel to learn and grasp quickly as long as they know how to use the day-to-day office software like Excel. The data should be presented in the analysis interface intuitively, instead of the abstract data structure. Allow the business personnel to transform and calculate in the most intuitive way, instead of writing the abstract analysis scripts. The analysis result must also be presented in the interface intuitively, and business personnel can plot the chart easily. At last, the analysis result can be exported or printed out by business personnel without distortion.

If business personnel can use the OLAP tool all by themselves, then the involvement of IT pros will become unnecessary and great human cost can thus be saved. More importantly, business personnel are the core and spirit of OLAP analysis. They have the expertise in their respective trade. They know best of the goal to analyze, and their analysis conclusion is trustworthy, which can truly satisfy the actual needs of business. 

3. Sufficient computational capability
The true OLAP tools allow users to operate the tools independently without compromising the computational ability. They must be brave in making innovations with a more advanced architecture, always user-friendly with a computational ability more sufficient and flexible than the traditional OLAP. This computational ability can handle the grouping at will, filtering, inter-row computation, set computation, and consolidate the related business data. It can also be used for the following computational goal that the traditional OLAP tools can hardly handle:

To find the workshop whose defective rate continuous to drop for 3 months in a row;
To make statistics on students whose score on each course is above B;
To count the days the newly-launched product takes to reach the 1/3 of the total sales in the first month since the new product enters the market;

The computational capability powerful enough will greatly improve the flexibility of OLAP tools. Analyzers will be able to analyze freely without having to resort to the tools from the 3rd party, and give full play to their talents in a broader space.

4. Flexible interactive analysis capability
Data analysis is an interactive procedure requiring the perfect step-by-step computing mechanism. Confronting the ambiguous computational goal, users need to take the reasonable guess according to the characteristics of data and then verify/falsify the assumption.

For example: Why the sales of this month rise sharply? Users can start by checking the increases of orders:

1.Users retrieve and review the order data and they may find: OrderID, order date, sales person ID, amount, etc. 

2.Users get to realize that they can simply compare the respective number of orders in the current and previous months to determine whether more orders were placed. He/she filters out the data respectively for the current and the previous months, and hides other data. Then, group by month.

3. Because there are only data of the recent 2 months, users can simply drag the scroll bar slider to find that number of orders placed in the recent 2 months almost remains the same.

 This indicates that the root cause is not the sharp increase in the number of orders, and the assumption for the first time is not correct. 

4.Users may think that the root cause is the increase of large orders, i.e. orders valued at a great amount of money. To check this, you may need to compare the number of large orders in these 2 months.

5.To classify the large orders, users decide to multiply the average amount or value of orders in these 2 months by 300% as the criteria to filter out the large orders. Users calculate out the result and store this resulting data in the analysis interface temporarily.

6.Users will filter the current orders by the criteria of large orders, and then calculate the number of large orders placed in each month. Through comparison, users may find the number of large orders in this month is far more than that of the previous month. Based on this, they can conclude that the sharp increase of sales value is the result of number of large orders climb in this month.

The practical situation could be more complex. For example, you will need to lower the standard of large orders if yielding too few hits of large orders. If yielding almost the same number of orders, then you should raise the standard of orders properly to keep on the investigation. In addition, only the increase of large order is not sufficient to support the decision making. Further analysis is still required to dig out the root cause, such as changing of the sales policy, professional training, the new sales person is highly capable.

All in all, the true OLAP should be able to solve the complex business computation, and provide the true basis for decision making. To do so, it needs to provide users with the perfect step-by-step computation capability and the flexible interactive analysis capability.

5. Quick in reacting to change
The true OLAP does not require modeling or multi-department collaboration. Faced with the changing business demand, business personnel can start analysis immediately. They can rely on the flexible interactive analysis ability and sufficient computational ability to give the most professional analysis findings in the shortest time, as a basis for enterprise decision making in a timely and accurate manner.

Assume that a toy manufacturer needs the below information before Christmas: Of the top 100 cities, which cities need more retail outlets immediately? At this point, analyzer may find that population data of cities is unavailable. If using the traditional OLAP tools, users may have to coordinate between multiple departments and work with Project Managers, Database Administrators, and programmers. Several weeks may be taken to reach the analysis conclusion. Due to this reason, the businesses are very likely to miss out the valuable business opportunities. The true OLAP tools can operate much faster:

1.Download population data from census bureau or Wikipedia, and export these data to the analysis interface of OLAP tool.

2.Carry out the analysis immediately, and sort the data by population, filter out the top 100 cities for future use.

3.Retrieve the data of collaborating retail outlets from the business database, group these data by cities, and count the number of retail outlets in each cities.

4.Sort the cities by the number of retail outlets. Then, align the prepared population data of the top 100 cities to the current data.

5.Compute the difference between 2 rankings, that is, subtract the rankings by number of outlets from the rankings of population. Filter out the cities with the great difference. For example, if the difference is above 10, then the results are those cities to which you should allocate more sales effort. If sales resources are insufficient while too many cities are filtered out, then the difference should be widened. If the difference is minor, all within 3 as an example, then the number of outlets in each city is reasonable, not requiring any increment in investment.

For the true OLAP, the above-mentioned analysis goal only takes several hours or tens of minutes. With minimum workload, the computational goal can be achieved in a relatively short period of time. Meeting the ever-changing demand rapidly will enable the enterprise to seize the business opportunities quickly and poise in the advantageous position in the intense competition.

6. Grid analysis model
For business personnel, the commonest data in their work is from the 2-dimensional spreadsheet, such as: The table in database, sheet in Excel, and CSV-formatted text file. As the analysis tool for business personnel, the true OLAP should provide the 2-dimensional analysis interface for users, that is, grid analysis model. It must be characterized as shown below:

The data introduced to the analysis interface should be the same data viewed from the business system.

The data in cell should be referenced and calculated directly according to its column and row numbers, saving the efforts of business personnel to define the variables. 

Users should be able to perform the data query, duplicate filtering, data layering, sorting, or summarizing actions by selecting the menu items. In particular, the true OLAP tools should provide the flexible and step-by-step computation, instead of the “semi-free” like Excel. For example, on the layered data, users are allowed to perform the data query, filtering, layering, and other actions over again.

In order to minimize the workload and potential errors of business personnel, the copy & paste of formulas should be implemented according to the business rule, while not the Excel style of which the business relation between cells is detached and not represented. 

Various tabular data can be merged and aligned easily. For example, for cities whose populations are among the top 100, to list their names and rankings, you can align the city name to the rankings of cities and its respective retail outlets by number.

All in all, the true OLAP tools should provide the analyzer with the simple and easy-to-understand grid analysis model. Analyzers can thus analyze the data freely according to their most intuitive business experience, not requiring any complex 3D thinking pattern.

7. OLAP of low risk
With the true OLAP tool, business personnel can complete the analysis without dependence on IT pros. With the familiar data in their daily business, analyzers only need to devise the most natural analysis procedure and use the most straightforward operation features, leverage the sufficient computational ability and the flexible interactive analysis ability to reach the analysis conclusions most suitable for the business imperative. 

Therefore, compared with the traditional one, the true OLAP will be more rapid and accurate to provide the basis for decision. In this way, the potential risk will be much lower. The low risk can prevent project failure, protect the enterprise investment, and win the long-term interests and initiative for user. 

esCalc is a just true OLAP tool indeed. The true OLAP remedies the seven drawbacks of traditional OLAP tools, and revolutionize the OLAP from train to car.

Original post URL: http://it.toolbox.com/blogs/data-analytics/olap-revolutionary-from-train-to-car-54425

Related News from Raqsoft:

Instant Computing of esProc Brings Flexibility to Analysts
Script Computation of esProc Optimizes Analysts'Benefits
Various Data Environments Support of esProc Makes Statistical Computing more Flexible


1 comment: