esCalc – the True OLAP
esCalc is the true OLAP because it implements the true meaning of OLAP.
OLAP is actually intended for the below situation: Confronting the vague analysis goal, business personnel can study the current business data to make the reasonable assumption. Then, following the most intuitive and straightforward train of thought, calculate and analyze step by step to verify/falsify every assumption to solve the complicated business analysis target. Let’s illustrate it with the below example:
Typical Example
For example: Recently, a company developed a new sales policy in the recent 3 months, and the assistant to the sales director needs to analyze the impact of the new policy on the sales result.
Suppose the first assumption is: The recent sales achievements should be better than that in the past. He needs to test and verify this assumption. Firstly, he needs to retrieve the order data, filter out the data of recent 6 months, then build the hierarchy by month, make statistics on the monthly sales, and finally develop the line chart. In the chart, it is indicated that the sales firstly dropped and later jumped in the recent 3 months, and then kept rising rapidly to a level close but not over the sales reached before the recent 3 months. It indicates that the 1st assumption is not correct.
Then, he continues to take another assumption that this is just the result of increasing number of orders, and the total sales are not increasing. To do so, he continues to compute the number of orders placed in each month on the basis of these data. As assumed, the number of orders increases sharply, far more than the number in the first 3 months. He deduces that this is due to the declining number of large orders while number of small orders increases. Therefore, he sort the orders in the first 3 months with the orders ranking the top 10% on the list as the criteria for large orders, and the relatively 80% orders on lower position as the criteria for small orders. By sorting the data of the recent 3 months by the criteria of large orders and small orders respectively, he found that the number of large orders drop sharply at first and then decline gradually, while the number of small orders rise sharply.
Finally, he prints the Analytical Chart and presents his findings of analysis: The sales value keep climbing rapidly and is presently close but still below the level before implementing the new sales policies. It is expected that the sales will exceed the original level in the near future; The growth is driven by the small orders, indicating that the current sales policy is not suitable for gaining large orders; Please reconsider the sales policy of large orders.
The above is the typical analysis procedure of esCalc. There are also quite a few examples:
- Find the outstanding stocks in the recent 3 months
- Analyze the box office of a new released movie
- Find out the reason why the customer complaints are rising
Why the sales of wine product in this month rise sharply
…
Regarding the true OLAP problem above, it is hard for the traditional OLAP tool to solve, while esCalc can handle it easily, thanks to its features below:
Free and intuitive analysis
In esCalc, users can view the data institutively and straightforwardly, and then decide on the analysis in the steps followed. The result of analysis is clear and visible. Considering the result, the interactive analysis can be carried on further till reaching the satisfactory conclusion. In the above example, retrieve data from the business database as shown below:
Through viewing the data, it is found that filtering the data first and only keeping the data within the 6 months is convenient for the next steps of sales comparison of the latest 3 months and the first 3 months. The filtered data is shown below:
Once filtered, you may find that the data of month is not provided. Therefore, a computed column can be added before the expected action of grouping by month and counting. esCalc users only need to write a formula once and the formula will be copied to the cell of the corresponding business position, as shown in the blow figure:
With the Month calculation column, you can group by it, as shown in the below figure:
After grouping, you can calculate the summarized value, as shown below:
To facilitate the viewing, users can collapse the levels to only display the summarized value, as shown in the below figure.
The above procedure of interactive analysis can be carried on until reaching a satisfactory result.
The interactive computation of esCalc is characterized by its free and intuitive style. Being “free” indicates that users can perform any operations on the current analysis result, regardless ” whether group first and then filter is easier than filter first and then group or not”;Being “intuitive and straightforward” refers that users can process the data following the natural thinking pattern, not requiring to translate the business algorithm for the computer.
The interactive computation can decompose and simplify the vague analysis goal, and offer an easy means to solve the multi-step computation, so as to solve the complex business computation, and provide the true decision basis.
The prompt and strong data analysis method
In esCalc, there are not so many basic analytical methods. However, these analytical methods are all of the “factor method”, that is, they are all capable to form the enough advanced analysis methods. This is just the same thing as combining three primary colors to generate any other colors and The Four Arithmetic Operations as the basic of other advanced algorithms. In addition to the above mentioned filter and group methods, it also includes:
Sort: when sorting the data, for example, sort the data of order by date in ascending or reverse order.
Distinct: get the unique value, for example, filter the duplicate orders to make every order unique.
Align: align the data according to the self-defined rule and build the hierarchy. Take the employee attendance data for example, you can input several names of person you are interested in. The result of Align method is to only keep the data of these people, and display these data in hierarchy. For any person exists in the input but not found in the attendance information, it can be displayed because he probably made a full attendance.
Join: Join 1 table to another table horizontally according to the business relation. As shown in the below figure:
The action of salary data join performance data is shown in the above figure. Although the salary is relative fixed, the performance usually varies in each month. Therefore, to calculate the actual income, users usually need to join both. Because the sorting order and number of rows of performance data are usually different to that of the salary data, it is relatively cumbersome to join manually. The traditional OLAP tools cannot provide a simpler way. In esCalc, everything goes smoothly in the natural thinking pattern: If joining the data of the 2 sections by Dept and Name, then set the Dept and Name as the criteria for joining (called as ”Master cell”), and then simply click Join.
In addition, there are expand method to expand the set according to members, locate method to locate on conditions in case too much data, and union, annex, and other methods. Most methods provide the easy mode not requiring any settings and the advanced mode allowing for the specific configurations.
As the tool tailored for business personnel, esCalc refines these basic analytical methods for users to learn and grasp rapidly. Users are only required to grasp a few analytical methods to perform the OLAP analysis freely.
Simple and Understandable Excel Style
Excel is the common software that is grasped by most business personnel. As a OLAP tool tailored for business personnel, esCalc adopts such simple and understandable style.
In the above figure, the one on the left is Excel, and the one on the right is esCalc. esCalc has many similarities to Excel: Both of them taking the letter as column header, and number as the column ID for their grid. Both of them allow for referencing to other cell with the cell name from any cell. Their users can group or expand data, set the color, font, and other styles or copy the format with the format painter.
As the OLAP software, esCalc provides the more powerful step-by-step computation. Formulas in homo-cell will be copied automatically and implemented in groups. For information on the difference, please refer to other documents.
esCalc is of the Excel-alike style with Excel advantage of being simple and easy-to-learn for business personnel to familiarize themselves with the esCalc operation methods as well as the functions and syntax of esCalc. Therefore, esCalc is the true OLAP for business personnel.
Sufficient Advanced Functions
esCalc provides a plenty of powerful advanced functions to solve the complex problems, such as: Comparison on year-on-year basis, link relative ratio, set operation, ranking and row number calculations.
For example, calculate the players who are among the top 5 in every game. The current data is as follows:
Simply input “={A3}” in E2, and the top 5 players of each game will be calculated out in E2, E8, and E14. Input “={E2}.isect()” in E1, and then the players who are among the top 5 in every game will be calculated automatically. In which, the function “isect” is to calculate the intersection of sets, and “{E2}” is a set to indicate “cells shares the same meaning with E2 regarding business”(homo-cell by name), that is, E2,E8,and E14.
Similar functions also include:
diff():Calculate the difference set of a group of data, for example, calculate the employee who made a full attendance in this quarter. You can calculate through the formulas like [set of employees, employee who ever absent in the 1st month, employee who ever absent in the 2nd month, employee who ever absent in the 3rd month].diff() to calculate.
proportion():Calculate the proportion of each member, for example, the proportion of each department in the total expenditure. You can calculate it through formulas like [department A’s expenditure, department B’s expenditure].proportion().This can also be represented with homo-cells like {E2}. proportion().
Other advanced functions include:sum(~*~) to calculate the sum of squares of a certain group of data; cumulate() to calculate the cumulative value of a group of data;ord(), calculate the relative row number in the calculation hierarchy, ranki() calculate the ranking of a number in a group of numbers.
There are also lots of similar advanced functions. These simple and understandable functions are specially designed for the business personnel to solve the complex problem conveniently, and solve the tough problem of traditional OLAP easily.
esCalc is capable to conduct the free and intuitive analysis interactively, prompt and strong data analysis method, an Excel style of easy to both study and understand, with abundant number of advanced functions. It is the true OLAP for business personnel to grasp rapidly and analyze freely, and is capable to provide the basis for timely and professional policy making.