July 29, 2012

esProc feature code 12: Align Grouping



  • The criteria of align grouping is not assigned by the original dataset but is from the external sources such as parameters, other datasets, and the set temporally organized by users.
  • For example, to check the insurance policy of top N sales person who get the least customer complaints, you can retrieve the list of sales person meeting the complaint conditions from the CRM system, and group these policy data in the policy system by this list.
  • Align grouping makes the criteria of grouping no longer limiting to the original dataset and thus improved the flexibility of grouping.
  • The align grouping belongs to the non- equivalence grouping, which supports the null set in the result of grouping. Here is a example:




Annotation 1 




Annotation 2 
                                       


July 27, 2012

esProc Feature Code 11: Reuse Group



  • Different from that of SQL, the grouping of esProc can be independent from the summarizing and the grouping result is reusable. This is compatible with the true meaning of grouping operation.
  • Based on the result of grouping for one time, multiple times of sorting, summarizing, and filtering can be implemented.
  • The reusable grouping is not only intuitive and easy-to-understand, but also convenient for step by step computation, enabling user to control the data more flexibly.
  • Since the grouping result is reusable, you are never required to group repeatedly before every statistics followed, and thus saved computation cost, improved the efficiency of computation.


Annotation 1 


Annotation 2 


Annotation 3 
                                     

July 25, 2012

Feature Code 10: Sets reference in field value



  • The esProc fields can also be used to store several records or sets. The field of the primary table in database usually corresponds to several records in the sub tables. Therefore, with the object reference, esProc can easily represent the correlations between the primary table and the sub table.
  • As illustrated below, the primary table Department table has an Employee field. The Employee field of every record point to the several records of sub table Employee table.
  • The cell A4 retrieved a record from Department table. Then, the statements followed can use A4.Employee to access to the set of records in the corresponding sub table of this record.
  • A4.Employee.avg(Age) represents the summarization of Age field of this set.



Annotation  1


Annotation  2 



Annotation  3 



Annotation  4 
      

July 22, 2012

Solve Complex Computations in the Report



Reporting tool is good at chart & form design, style of landscaping, query interface, entry & report, and export & print. It is one of the tools that are applied most extensively. However, there are quite often complex computations in the report, which raises a very high requirement for technical capabilities of report designers, and is one of the biggest barriers in report design. esProc can cooperate with Java reporting tools and solve with ease the complex computations in the report.


Case and Comparison

A company has a High Growth SalesMan of the Year report, which analyzes, mainly through sales data, the salesmen whose sales amount exceeds 10% for three consecutive months, and demonstrates the indices such as their sales amount(Sales Amount), sales amount link relative ratio(Amount LRR), client count(Client Count), and client count link relative ratio(Client LRR). The report pattern is shown in following table:

The main data source of the report is the “monthly sales data”: sales table, which stores the monthly sales record of the salesmen, with salesman and month being the primary key. The structure is shown in the following table:

It can be seen that the calculation of the name-list of the salesmen whose sales amount exceeds 10% for three consecutive months is the most complex part of this report. As long as this name-list is calculated out, it is possible to use the reporting tool to easily present the remaining part. Let’s compare how SQL statement and esProc respectively calculate this name-list.

SQL Solution

1 with A AS
2             (SELECT salesMan,month,amount/lag(amount)
3                    OVER (PARTITION BY salesMan ORDER BY month)-1 rising_range
4                     FROM sales),
5         B AS
6              (select salesMan,
7                     CASE WHEN rising_range>=1.1 AND
8                               lag(rising_range) OVER (PARTITION BY salesMan
9                                     ORDER BY month)>=1.1 AND
10                               lag(rising_range,2) OVER (PARTITION BY salesMan
11                                     ORDER BY month)>=1.1
12                     THEN 1 ELSE o END is_three_consecutive_month
13          FROM A)
14 SELECT DISTINCT salesMan FROM B WHERE is_three_consecutive_month=1
  1. 1-4 lines: Use SQL-2003 window function to obtain the ”rising_range” of the monthly sales amout LRR of each salesman, where, ”lag” seeks the sales amount relative to the preceding month. Here, ”WITH” statement is used to generate an independnet sub-query.
  2. 5-13 lines: Continue to use window function to seek ”is_three_consecutive_month_gains”, the symbol of consecutive gains of slaesmen in the each record, where, ”rising_ranges” of the recent three months are biggern than 1.1 at the same time, and this symbol is 1. Otherwise it equals to 0, and here the technique ”case when” is used. Finally, ”WITH” statement is still used to generate independent sub-query B.
  3. Line 14: According to the result in the preceding two steps, seek the salesmen meeting the reporting condition, namely, the record whose “is_three_consecutive_month_gains equals 1. Here it is necessary to use “distinct” to filter duplicate salesmen.

esProc Solution

A B
1 =sales.group(salesMan).(~.sort(month))
2 ==A1.select(??) =0
3 =~.pselect(B2=if(amount/amount[-1]>=1.1,B2+1,0):3)>0
4 =A2.(salesMan)
A1: Group the data according to salesman. Each group is all the data of a salesman, which is sorted by month in ascending order.
       A2: Refer to the calcualtion result of the preceding step, and select the group that meets the condition from A1. The condition comes from the last cell of A1 operation area, namely, Cell B3. Both B2 and B3 belong to A1 operation area. By writing the condition step by step in many cells, it is possible to reduce the difficulty.
       B3: Conditional judgment. If the LRR of three consecutive months within the group is bigger than 1.1, then this group of data meets the condition. Here “amount [-1]” is for the data of preceding record relative to the data of the current record, amount/amount [-1] represents a LRR comparsion. The pselect() is used to obtain the serial number within the group, and whenever meeting the first piece of data within the group that meets the condition, pselect() immediately returns the serial number and stops repeated calculations.
       A4: Obtain the serial number of the salesmen in A2, and this result is returned through JDBC to the reporting tool for use.

Comparison

The method to calculate this case “stepwise” will be very clear, so it is relatively suitable for stored procedure. But report developers often cannot add stored procedure in the database at their discretions, so it is generally still necessary to use SQL statement to solve the problem. For general SQL-92 statement to solve this type of problem, it will be very troublesome. By using here the SQL-2003 standard that is not extensively used, it is possible to reduce the difficulty. Even so, it is still necessary to face large paragraphs of difficult-to-understand SQL. For common report developers, it is no doubt a huge challenge.
       It is more agile and easy for esProc to solve this type of calculation. esProc provides an expression formula using grid style similar to Excel®, which naturally proceeds by steps. Cells can refer to calculation result one another, which saves the great efforts of complex nested queries as well as unnecessary and scrambled variables definition. esProc also provides functions on the calculations of mass data, such as relative position, serial number reference, and step-by-step calculation after grouping, which can greatly simplify calculation procedure.
       From the above, it is obvious that esProc is better at solving the complex computation in the report.

Feature: a JDBC Interface

esProc is a product with pure JAVA® structure and provides JDBC interface for JAVA reporting tools to conveniently call it. The structure schematic is as follows:

In the system adopting JAVA reporting tool, it is possible for esProc to conduct complex computation, multiple-datasource operation, and dirty data source collation. Then, the reporting tool can obtain the result returned from esProc via JDBC in the form of an access to the database. Finally, the reporting tool can be used to present the data.

Feature: Computational Capabilities Over-perform SQL

esProc is a tool specially designed to calculate mass data, and has SQL statement and stored procedure the capability to. On the one hand, it conducts query, filter, grouping, and statistics just as SQL statement does; on the other hand, it can also conduct loop and branch judgment on analysis process just as stored procedure does.
       In fact, SQL statement and stored procedure, which are also mass data calculation tools, have some obvious defects: Stepwise mechanism is incomplete, set-lization is incomplete, and there are lacks of serial number and reference. So in the report where is complex computation, designing a few lines of SQL statement tends to become very difficult, and also has very high requirement for technical capabilities of designers.
esProc overcomes the defects of SQL statement and can comfortably cope with the complex computation in the report.

Leave a Reply

If you have any question, please leave a reply below!

July 17, 2012

Feature Code 9: Records reference in field value

  • In esProc, field value can not only be used to store the simple number and character string, but also point to the complex data type like record. This kind of field is usually called Foreign Key.
  • In esProc, you can directly use "." symbol to reference the record to which the foreign key is pointing.
  • In the below example, there are 4 records in A6. Of which the foreign key Dept is pointing to the records of Department table. If accessing to the Manager ID related to Dept, then you can just compose A6.Dept.Manager.
  • The object reference makes the access to foreign key a very simple thing. The business team can thus analyze and solve problems from the business perspective rapidly and intuitively.
  • The advantages of esProc are prominent when encountering the multi-nest foreign keys. Because SQL does not support the object reference, it is very complicated to represent the multi-nest foreign key association.





                                        Tips 1

Tips 2 


Tips 3 


Tips 4

July 16, 2012

Feature Code 8: Cross-row Computing In Loop

  • Relative Locating refers to locating to a position that is a certain number of cells/groups before or after the current records/groups. This kind of data computation is usually the typical SQL puzzles. However, esProc solves it easily.
  • For example, to compute the month-on-month increase of sales volume of each month compared with that of previous month, you can simply use sales.(amount-amount[-1]) to represent it. Of which the relative number is represented as [n].
  • Just another example, to find 3 months with the highest sales volume and their respective month-on-month increases.
  • The Comparing with Last Period, Moving Average, and Add Up are the typical applications of relative locating. Please find the below example:




Annotation 1 


Annotation 2 

July 13, 2012

Feature Code 7: Locate by Serial Numbers



  • The Being Ordered refers to the data is stored in a certain order. Every piece of data and every member have their absolute or relative numbers. You can access the data with these serial numbers and operate on the data conveniently, such as locating, ranking, or sorting.
  • With serial numbers, you can access to the member of sets more easily.
  • Get the sales value field of the last record: sales.m(-1).(amount)
  • Get records whose sales value are over 1000: sales.pselect@a(amount>1000)
  • Get the record with the highest sales amount: sales.pmax@a(amount)


Annotation 1 

                              Annotation 2 

                                  Annotation 3



                                  Annotation 4



                                  Annotation 5


July 11, 2012

Feature Code 6: Dissociative record objects



  • The dissociative record refers to the records free from the physical table in form. The analyst can take the individual record as an object to fetch value, compute, and modify, just similar to the access to an object.
  • The dissociative record enables the analyst to focus on the concerned data and save them from the interference, so that the faster and more convenient data computation can be achieved.
  • The dissociative record is the embodiment of stepwise computation. For SQL, the feature of dissociative record is unavailable.




annotate 1 


annotate 2

annotate 3 

annotate 4 

annotate 5 

July 9, 2012

Feature Code 5: Omnipresent Sets - Provides the Perfect Set Operations




          Omnipresent sets

  • Ubiquity of sets
The set is a widely-applied basic data type. For example, the order paid in this quarter, the list of premium clients (set A1), and the Top 500 enterprise (set B1).
  • Provides the perfect set operations
esProc provides the set operation (intersection, union, compliment, and other operations) that can materialize the abstract basic data, convert them to the business understandable language, and carry out the stepwise computations according to the business requirements. In this way, the complexity is greatly reduced. For example, you can directly use A1^B1 to select out the Top 500 clients among those premium clients.
       By compassion, the stepwise computation is not favored and thus the set is also unsupported by SQL. The high level language cannot support the set operation directly, requiring programmers to implement a great many lines of codes.






















July 5, 2012

Feature Code 4: Instant Computing With esProc

With esProc, you can code, run, and return the result instantly. Therefore, the interactive analysis can be implemented easily. Here is an example for you. Enjoy!







July 4, 2012

Feature Code 3: Neat and clear codes



  • esProc cells are of the natural delimiters and indentions
  • With the cell as natural delimiter, the alignment of constants is effortless
  • With the cell as natural indention, the loop/branch statements have got the natural and clear scope
  • The cell-style code enables a more clear and easy-to-read multi-nest, you will never get caught up in an awkward situation of messy levels of nesting (such as the typical bracket pitfalls of }}}} in Java). 




The loop body is marked with red rectangle 8.







The statements marked with green rectangle is executed when the condition of the if statement is true.





The statements marked with blue rectangle are executed when the condition of the if statement is not true.