# The Agile Desktop BI Tool

The BI (Business Intelligence) refers to the intelligence and the ability to strengthen the business competitive edges: report presentation, reporting result calculation, OLAP analysis, business data calculation, and data mining and predication. Among these, there are the program-oriented heavyweight system and the business personnel-oriented agile desktop tools. esCalc is a representative of the latter.

esCalc boasts 4 features: first, a professional reporting tools to generate various statistics tables and charts; second, a business data calculator with the advanced model and intelligent tabular data calculation ability beyond Excel; third, the true OLAP that remedies the seven drawbacks of traditional OLAP, and is capable of providing the concrete decision basis for the practical situation of business; forth, it can serve as the calculator for reporting results, and further process the results of reporting tools.

esCalc is an agile Business Intelligence tool. Not having to rely on the IT, business personnel can perform the business calculation all by themselves. Facing the ever changing demand, esCalc can provide the basis for quick response and decision. It is able to decompose a complex and ambiguous calculation goal into several simple and intuitive steps.

### Typical Case

A sales director with a pharmaceutical manufacturer checked the Monthly Product Sales report, and found that the sales were on the downside in the consecutive 3 months. He took an assumption that this was related to the sales policy taking effect a few months ago. Because it takes quite a lot time to coordinate with the technician and fully understand the sales policy, the sales director asked his assistant to find the true cause to this phenomenon. The core reason to the situation is that the analysis goal is rather obscure. Only those familiar with the business could make the judgment based on the data characteristics, let alone proposing a clear train of thought for IT to solve the problem. This is the typical BI case.

The assistant made an initial assumption that the sales decline was the result of the declining number of orders. He imported the report result to esCalc and calculated out the number of orders through filtering, summarizing, and other operations. He found that the number of orders did not drop sharply, that is to say, his initial assumption was not correct.

Then, he made the second assumption that the number of big orders were on the decline, while the number of small orders was climbing. Through the further calculation on the tabular data, he found that his second assumption was correct. However, that was not enough. A more effective basis, the composition of big clients in the recent months as an example, was required to make a decision. To do so, he retrieved the Customer table from the Oracle database where the CRM is located, and joined it with the Orders. Through calculating, he found that the orders from community hospitals and other clients alike in the recent 3 months dropped sharply.

Finally, the assistant examined the community hospital specific sections of the current sales policy, and revisited several typical big clients. His findings ultimately reveal: owing to the latest defective sales policy, the number of orders from the community hospital and other big clients alike was declining.

At last, with esCalc, he prepared a report to illustrate the changing tendency of big clients with tables and charts. The job was well done, and he completed the task assigned by his superior.

Similar situations also include:

*Prepare some temporary report for a retailer according to the marketing change*

*Find the months since 1999 in which the sales of those months are above the average sales of the respective years*

*Analyze the reason why the customer complaint was rising in the recent weeks*

*Based on the reporting result from a telecommunications company, analyze the average contribution of clients during the Christmas holiday.*

As an agile BI tool, esCalc is characterized by:

**Support for the common BI features:**

esCalc is fit for those business personnel to prepare the static report by themselves, even if they are inexperienced in IT. With a grid-style reporting design interface, esCalc supports the rapid reporting, the business-personnel-oriented data processing, and high fidelity report preparation. In other words, it is able to keep reports consistent in the stages of design, preview, pagination & printing, and export. esCalc users can use various statistics chart and detailed parameter settings.

For example, make a product sales situation report for specific products to present the monthly sales of 3 products, and their link relative ratio, and monthly year-on-year growth.

esCalc supports the calculation on the result of common reporting tools. Excel and the plain txt are the export formats supported by most reporting tools. esCalc can import and calculate them directly; supports the data pasting from the report result directly on the clipboard; provides the calculator-style operation for business personnel; is able to conduct any process on data, and the calculation between steps can be transited smoothly.

For example, the reporting tools generate the below report result:

Find the big clients who account for 60% of the total sales of the company based on the above data. The result is as follows:

esCalc supports the true OLAP analysis. esCalc is able to perform the interactive analysis freely and visually, decompose and simplify the obscure analysis goal. It provides the basic analysis methods that are both simple and easy to use. Then, lots of advanced analysis can be implemented through the free combination. With an Excel-style, esCalc becomes relatively easier to understand and learn. Moreover, esCalc also provides a range of powerful advanced functions to solve the complex problems regarding OLAP.

For example, to achieve the obscure goal of recommending a blanket of stocks worthy of portfolio, esCalc users can find out the second line high-quality stock that is below 8 times of P/E by calculating the blue chip keep rising for consecutive 5 days. For the latter one, firstly, get the stock code, stock trade date, and closing price, as shown in the below figure:

Then, through a series of calculations, get the consecutive rising days of each stock, for example, the stocks in the below figure:

At last, fold the levels, and sort by the longest consecutive rising days to get the desired results:

esCalc has a more intelligent calculation ability than Excel. esCalc has a overall calculation model, and supports the auto-paste and intelligent parse; It is especially fit for the post-grouping data processing; It can also greatly simplify the data calculation between 2 worksheets, such as vertical and horizontal joins ; It also provides a great many of advanced functions to handle the tabular data, and is able to confront the complicated calculations.

For example, calculate the proportion of population of each state in each year. For the table in the below chart, in D3, just enter the formula “=C3/C2”, then the formula will be automatically pasted to D4 – D8 and D10 – D15. In the procedure of copying, the formula was parsed intelligently, for example, in D10, the denominator became C9 intelligently. The figure is shown below:

### Agile installation deployment:

The size of esCalc installer is only dozens of MB. esCalc only requires a few clicks to install and run. It is a Java application running on the Windows desktop, is able to run on most desktop computers independently, not having to deploy the additional server.

The whole interface is shown below:

Data calculation section is shown below:

esCalc is especially designed for business personnel without technical background to install it easily in the common office environment and is able to use once installed all by themselves.

esCalc supports various mainstream databases, including MSSQL, Oracle, Access, MySQL, DB2, and Sybase. It also supports the local data files, for example, Txt, Log, tab, and other text files; Excel 97, Excel 2010, and the Excel of other versions. esCalc also supports the interactive calculation between various data sources, such as the calculation between Oracle and Excel.

### Agile operation experiences:

Excel is the software that is common to most business personnel, and this style of being both easy to learn and understand for esCalc. The comparison between these 2 spreadsheets is shown below:

The one on the left is Excel, and the one on the right is esCalc. They are similar to each other in many respects. However, esCalc is more powerful in calculation. It innovatively visualizes and objectivises the calculation methods of SQL, empowering the business personnel to calculate as capable as the IT technicians with a stronger IT background. These methods cover all basic SQL functions, to name just a few, grouping, summarizing, sorting, distinct operation, and horizontal/vertical join.

For example, to perform filtering operation, you can select Filter from the right-click menu, as shown in the below figure:

To filter out the data greater than “07/01/2010”, you only need to set as follows:

The result is as follows:

The step-by-step calculation is the key feature of esCalc that converts the complex calculation goal to a procedure of solving several simple questions. Users can view the data visually 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 further carried out till reaching the satisfactory conclusion. Excel is the half step-by-step style. For the same calculation, the Excel users will have to make a detour to reach the same calculation goal.

For example, to collect statistics on the months in which the monthly average temperature is higher than the average temperature of the respective year. Once the data is grouped by year, it will be like this:

At this point, perform the further operations on the result, that is, filter the month by the average temperature. For esCalc, there is only one intuitive step required to get the result, through one-right-click on the C4 cell and select filter, and setting the filter condition “Cell value” as “>=C3”, as shown in the below figure:

The result is as follows:

For the same calculation with Excel, users will have to repeat a same procedure on each group of data respectively: Filter out the data of the year, filter by temperature, copy to the new Sheet, merge the data, and restore to the original data. Therefore, for N groups of data, Excel users will have to perform 5N steps.

### Agile formula functions:

esCalc provides the agile formulas and functions, so that the business personnel can easily represent the relatively complex calculations, such as comparison on year-on-year basis, link relative ratio, set operations, ranking and row number calculations.

For example, calculate the players whose rankings are among the top 5 in every game. The data available 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, then the players whose rankings 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”(homocell by name), that is, E2,E8,and E14.

It also has the similar function like:

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.

There are also other advanced functions available, such as the 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() to calculate the relative row number in the calculation hierarchy, and ranki() to calculate the ranking of a certain number in a group of numbers.

All in all, esCalc supports the common BI functions, and is business-personnel-oriented in the respect of installation deployment, operation experience, and formula function. It is the agile Desktop BI software.