May 15, 2012

Analyze Data with No SQL Experience

“What is the sales value in this month? What is the increased percentage compared with the previous month? Which sales persons are among the top 10?” – The similar data analysis is mostly required in the sectors like commerce, finance, government, and scientific research. SQL usually is the only choice for such kind of analysis.
As a query language initially developed in early 70s, SQL has a relatively higher technical requirement on analysts. Lacking of advanced IT professionals is the root cause that hinders the data analysis from going any further.
esProc is a data analysis tool of the new generation. It not only provides all features of SQL, but also requires lower technical requirements. Even those developers without SQL expertise can use esProc to analyze the data by themselves.

Case Description:

A certain insurance company requires the following data analysis: How many newly-added polices this year? Of which how many policies are of the ”life insurance” ? How many policies valued above 500 thousand dollars?
The data are mainly stored in the insurance table of database, and the main fields are policyID, amount, time, type, and other fields. Some data are given below:

Of which ”policyID” is the serial number of policy, ”amount” is the policy amount, ”time” is the date of signing the policy, ”type” is the type of policy, and ”L” represents the “life insurance”.
Let’s check the SQL solution below.

SQL Solution

If using SQL to solve this type of problem, three SQL statements are required as given below:
  • SELECT COUNT(*) FROM insurance WHERE to_char (time,’yyyy’) = to_char (sysdate,’yyyy’);
  • SELECT COUNT(*) FROM insurance WHERE to_char (time,’yyyy’) = to_char (sysdate,’yyyy’) and type=’L’;
  • SELECT COUNT(*) FROM insurance WHERE to_char (time,’yyyy’) = to_char (sysdate,’yyyy’) and type=’L’ and amount>500000;

esProc Solution

For esProc, the computation is carried out in the cell. esProc user can just put a simple expression in each cell. Cells can make reference to each other with the cell name, and the analysis procedure is that the observing along with the thinking. The operation habit is almost the same to that of Excel®.


This problem is of the progressive style, and the later problem is the further explore on the previous one. The three problems are related mutually. As for esProc, the esProc user only need to process a bit based on the results of previous computation according to the business description.
SQL users are hard to perform the stepwise computation and the previous computation cannot be referenced. Therefore, each problem is a new problem to SQL user. With the further explore on the problem, the statement will become more and more complex, and finally the SQL user has to resort to the technical specialist to solve this problem. For example, based on the problem 3, seek the monthly increased percentage of the policies valued high.
SQL style is like this:

select amount, month,
(case when
prev_amount !=0 then (amount)/prev_amount
else 0
end) compValue
from ( select month, amount,
lag(amount,1,0) over(order by month) prev_amount
from (
select month,sum(amount)amount from(
SELECT amount,to_char(time,’MM’) month
FROM insurance
WHERE to_char (time,'yyyy') = to_char (sysdate,'yyyy') and type=’L’ and amount>500000
) group by month order by month

Not understand? Never mind. We can use esProc to implement the same computation:

A4: Group the results from the previous step by month.
A5: Sum the data of every month.
A6: Seek the increased percentage of this month, compared with the previous month. The “~” represents every month in A5, and “~[-1]” represents the previous month.
It is obvious that the technical requirements of esProc remain the same when exploring the problem. Even the business man or nontechnical person can always grasp the usage of such evident, easy-to-understand, and Excel®-like expression. On the contrary, in this case, the technical requirement of SQL statement will experience the exponential increase and soon reach the bottleneck of technical capability. Therefore, regarding the age-old and complex analysis tool of SQL, the analyst is in a complex that they both love and hate the SQL, and feels hard to grasp it.
esProc users can analyze data without SQL expertise and experience, and easily solve the tough problems that the SQL programmer encountered, thanks to the great features of esProc.

Feature: Excel® Operation Mode

The use habit of esProc is similar to that of Excel®. The analyst who are familiar with the Excel® can soon find that the commonness between esProc and Excel®: naming convention of functions, writing style of expressions, naming convention of cells, reference style of cells, and even the Special Paste, the auto-adjusting style after expression is pasted.
The analyst who is familiar with Excel® will soon get to love the operation mode of esProc. They will find that this easy change will give them powerful analysis ability.

Feature: Intuitive Analysis Style

With the technology advancement, the analyst can analyze the data with esProc that is more intuitive and understandable, no need to learn the SQL developed in the “Host/Command Line” age.
esProc provides various functions like cell, reuse, and indention with an intuitive analysis style, which enables the analyst to start form the business perceptive and analyze step by step following the natural train of thought as the human brains would do.

Feature: Professional Computation Ability

esProc is a professional tool for mass data analysis, not only having all analysis ability of SQL, but also allowing for the query, filtering, grouping, statistics operations, receiving parameters and performing the loop and branch judgment in the analysis procedure. Besides the database data, the data from Excel and Txt can also be analyzed directly.
In addition, esProc eradicates the disadvantages of SQL. It is a query language of the new generation over-performing SQL.

esProc Value

Rescue the unconscious project: SQL expertise and experience are not required to use esProc. The technical requirement on analyst is lowered. Therefore, the once halted analysis task can move on.
Save the operation cost: The relatively lower technical requirement means that the advanced developers are not a must for data analysis any more. In addition, the professional computation ability can offer the reference for decision-making in a much faster way. esProc can save the operation cost of enterprise in respect of money and time.
Concise analysis: esProc is ideal for those business-orienting analysts. With esProc, the information noise resulting from the involvement of technician can be avoided from the very beginning. The esProc user can represent the analysis objective more accurately and implement such analysis more concisely.
esProc is a powerful data analysis tool. It is the best choice for business people to analyze data.

No comments:

Post a Comment