May 23, 2013

Why Plug-and-Use Desktop BI Software Is So Powerful?

The basic function of a calculator is computing, which can be as simple as the four arithmetic operations and also as complex as the calculation for the next move in chess with Deep Blue. Among these, esCalc is a desktop data calculation tool for the business users to handle the occasional, complex, and business-related data computation. Such tools are named as the desktop BI software.

For example, a stock analyst is required to recommend some stocks to the clients urgently. Among all calculations involved, the analyst needs to find shares from more than 20 daily trading stocks which had risen on 5 consecutive days in previous month. He opens this desktop BI software and imports the daily data of the more than 20 stocks. He continuously monitors and analyzes the data, works out the outline of algorithms, and then groups, summarizes, sorts, filters and takes other possible operations to calculate the results with some simple formulas. At last, he gets the result and makes the recommendation successfully.   

The similar calculations also include:

  • For all clients of the insurance company, what’s the average insurance price of those who bought the basic insurance first and then bought the additional insurance?
  • In the 3 months with the most client complaints, find out the top 3 products with the highest defective rate.
  • For the top N sales persons who achieved the 50% of the total sales for the company, what are their respective sales proportions to that of their respective sales team?

esCalc is the plug-and-use desktop BI software with the powerful computational capability for business users to grasp easily, owing to the following advantages:

Typical Desktop Application

The installer of esCalc is only dozens of MB. The installation procedure only requires a few clicks and can be run immediately after installation. As a JAVA application running on the Windows desktop, esCalc can run on most office computers independently without having to deploy the extra server additionally. 

esCalc resembles Excel in UIs that is easy to learn and grasp. The overall interface is shown as the below figure:

The Data Computation section is shown below:

esCalc is especially designed for the business users without technical background. It can be installed in a common working environment easily, and be used once installed.

Various Data sources Support

esCalc supports various databases, including MSSQL, Oracle, Access, MySQL, DB2, Sybase, and other mainstream databases. In facts, esCalc supports any databases with JDBC and ODBC drivers.

Besides the access to the database in the LAN, esCalc also supports the access to the local data file, such as txt, log, tab, other text files, and the Excel 97~2010 spreadsheets.

esCalc also supports the interactive calculations between various data sources, for example, to store the basic information like the company name, the contact information, and the company industry in the database, and to store the follow-up visit to some clients by sales persons in another Excel spread sheet.  With esCalc, you can merge the two pieces of data easily to form a follow-up visit log classified by the company industry. Even if their Number of clients and the Sort Criteria differ to each other, esCalc can handle it with easy.

The office environment of business personnel is sometimes rather complex, such as the CRM, MIS, ERP, DSS or performance environments, supply chain management, and other application systems. esCalc supports various data sources and interactive calculations and is capable to handle the complex office environment.

Step-by-step Calculation

The step-by-step calculation can decompose the complex computational goal into several simple steps and complete a seemingly complicated great goal by solving each simple small problem.

Still with the above example, to calculate the stock rising for consecutive 5 days, you can group by stock first, and then calculate the daily increment of the stock. An increment greater than 0 indicates the stock is rising. Based on this judgment, the consecutive rising days of each stock can be calculated out. Lastly, the longest consecutive rising days of each stock can be calculated through filtering and sorting. The details are shown below:

Step 1: Import the stock data from txt file, as shown in the below figure: 

Step 2: Filter out the data in the previous month by date. Suppose it is June in 2011, as shown in the below figure:

Step 3: Set the level by the stock code:

In the above figure, the newly-built level is in the red block on the left, and in the red circle on the right is a stock. This row is the summery row.

Step 4: Sort the transaction data of each stock by Date in ascending order.

Step 5: Calculate the daily growth rate of each stock. In this step, a calculation column D needs to be added, and a formula =(C4[A2]-C3[A2])/C3[A2] is entered to the D4. The formula will be pasted to the related cells automatically, as shown in the below figure:

Step 6: Compute the consecutive rising days. In this step, a computation column E needs to be added, and then the formula in E4: =if(D4>0,E3+1) to be entered. The result is shown in the below figure:

Step 7: Calculate the longest days of a certain stock rising consecutively by inputting the formula like max({E4}) in E2. The result is shown below:

Step 8: Fold the summary row by clicking on the level number 1 on the left, as shown in below figure:

Step 9: Sort by the longest consecutive rising days, as shown in below figure:

In the above figure, 5 stocks keep rising for 5 consecutive days in June 2011, which are American Express Co., The Boeing Co., Citigroup, Inc., General Motors Corp, and Coca-Cola Co. It is certain that the data can also be filtered by the longest consecutive rising days, as shown in the below figure:

To solve the relatively complex computational goal in this case, we decompose the computational goal into 9 steps of simple operation or formula computing.

The step-by-step computation allows users to decompose, simplify, and ultimately solve problems in a rather visual train of thoughts. Owing to this, business users can also solve some complex data computing problems by themselves.

Adequate data computational capability

esCalc is powerful enough to handle the various computational task in the daily office work.

Function as SQL in every aspect

With the same computational capability as SQL, esCalc can be used to filter, group, sort, and perform the distinct, union, join, and other equivalent actions of SQL.  Please refer to the menu shown in the below figure:

No technical background required

SQL can only be grasped by the professional technician, while esCalc represents SQL functions with graphics and decomposes it into several steps so that even the business users without technical experiences can handle it easily. For example, in the step 3 of “building levels by stock code”, esCalc does not require any complex coding, and users only need to set it up in the menu easily.

Alternatively, the default shortcut menu can also be used, as shown in below figure:

Because SQL does not support the step-by-step calculation, SQL solution to this case will be lengthy, error-prone, and hard-to-understand statements. Obviously, it is hard for the business users to grasp.

Computational capability beyond Excel

The Excel and other tools alike do not provide the auto copy and intelligent adjustment functions. The similar functions can only be implemented with a great amount of manual operations. For example, the longest consecutive rising days of each stock, esCalc users can enter the formula =max({E4}) in E2 directly, as shown in the below figure:

After entering the formula in E2, all homocells of E2 will be populated with the formula, for example, E94. By comparison, Excel does not provide the auto-copy function, and you will have to conduct it manually. In addition to the auto-copy, esCalc also supports the intelligent adjustment function, for example, the formula in E94 will be adjusted to =max({E96}) to meet the business logics, as shown in the below figure:

 In addition, the homo-cell model maintains the business relations between esCalc cells, so that the true grouping is implemented, and the grouped data can be further processed. This is hard to be implemented with the Excel and other tools alike, for example, in the step 4 of the above case, “sort the dealing data of each stock by Date ascendingly”. For Excel, ungrouping is required to sort the data by stock & date and then group. However, with esCalc, you can sort directly to implement it.

Multiple computational functions

esCalc can be used to perform the complex computations related to sequence numbers. For example, still in the above example, calculate the rankings of closing price of the end of previous month on the basis of the result in step 8.

Firstly, calculate the closing price in the end of month, then append a new column F, and enter the new formula in F2: ={C3}.m(-1), as shown in the below figure:

Then calculate the rankings. Append the new column G, and input the formula ={F2}.ranki(F2) in G2, as shown in the below figure:

As can be seen from above, 9N12584 (i.e. IBM) has the highest closing prices among these shares of more than 20.

esCalc can also perform the intersection, union, compliment, and other set operations, for example, compute the stocks that are among the last 10th (cheaper) by closing price and rising for consecutive 4 days. esCalc can also be used to perform the inter-row computations such as monthly year-on-year comparison and the link relative ratio comparison, for example, compute the stock price moving averages in the 5 days.

In conclusion, esCalc is a typical desktop application which is able to support multiple data sources and step-by-step computation with sufficient data computational capability. It is the data computation tool for business users to handle the various computational problems in the daily office work easily.