Welcome!

Article

Solving Complex Computations

Reporting computation: selecting the salesmen whose sales amount exceeds 10% for three consecutive months

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.

esProc

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:

esProc

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

01 WITH A AS

02       (SELECT salesMan,month, amount/lag(amount)

03           OVER(PARTITION BY salesMan ORDER BY month)-1 rising_range

04           FROM sales),

05      B AS

06            (SELECT salesMan,

07                CASE WHEN rising_range>=1.1 AND

08                     lag(rising_range) OVER(PARTITION BY salesMan

09                          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 0 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

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.

About esProc: http://www.raqsoft.com/product-esproc

More Stories By Jessica Qiu

Jessica Qiu is the editor of Raqsoft. She provides press releases for data computation and data analytics.

Latest Stories
All organizations that did not originate this moment have a pre-existing culture as well as legacy technology and processes that can be more or less amenable to DevOps implementation. That organizational culture is influenced by the personalities and management styles of Executive Management, the wider culture in which the organization is situated, and the personalities of key team members at all levels of the organization. This culture and entrenched interests usually throw a wrench in the work...
SYS-CON Events announced today that Calligo, an innovative cloud service provider offering mid-sized companies the highest levels of data privacy and security, has been named "Bronze Sponsor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Calligo offers unparalleled application performance guarantees, commercial flexibility and a personalised support service from its globally located cloud plat...
SYS-CON Events announced today that Massive Networks will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Massive Networks mission is simple. To help your business operate seamlessly with fast, reliable, and secure internet and network solutions. Improve your customer's experience with outstanding connections to your cloud.
In his session at 20th Cloud Expo, Mike Johnston, an infrastructure engineer at Supergiant.io, discussed how to use Kubernetes to set up a SaaS infrastructure for your business. Mike Johnston is an infrastructure engineer at Supergiant.io with over 12 years of experience designing, deploying, and maintaining server and workstation infrastructure at all scales. He has experience with brick and mortar data centers as well as cloud providers like Digital Ocean, Amazon Web Services, and Rackspace. H...
FinTechs use the cloud to operate at the speed and scale of digital financial activity, but are often hindered by the complexity of managing security and compliance in the cloud. In his session at 20th Cloud Expo, Sesh Murthy, co-founder and CTO of Cloud Raxak, showed how proactive and automated cloud security enables FinTechs to leverage the cloud to achieve their business goals. Through business-driven cloud security, FinTechs can speed time-to-market, diminish risk and costs, maintain continu...
21st International Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Me...
IoT is at the core or many Digital Transformation initiatives with the goal of re-inventing a company's business model. We all agree that collecting relevant IoT data will result in massive amounts of data needing to be stored. However, with the rapid development of IoT devices and ongoing business model transformation, we are not able to predict the volume and growth of IoT data. And with the lack of IoT history, traditional methods of IT and infrastructure planning based on the past do not app...
SYS-CON Events announced today that DXWorldExpo has been named “Global Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Digital Transformation is the key issue driving the global enterprise IT business. Digital Transformation is most prominent among Global 2000 enterprises and government institutions.
SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
"Outscale was founded in 2010, is based in France, is a strategic partner to Dassault Systémes and has done quite a bit of work with divisions of Dassault," explained Jackie Funk, Digital Marketing exec at Outscale, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
SYS-CON Events announced today that Calligo has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Calligo is an innovative cloud service provider offering mid-sized companies the highest levels of data privacy. Calligo offers unparalleled application performance guarantees, commercial flexibility and a personalized support service from its globally located cloud platfor...
"At the keynote this morning we spoke about the value proposition of Nutanix, of having a DevOps culture and a mindset, and the business outcomes of achieving agility and scale, which everybody here is trying to accomplish," noted Mark Lavi, DevOps Solution Architect at Nutanix, in this SYS-CON.tv interview at @DevOpsSummit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"We were founded in 2003 and the way we were founded was about good backup and good disaster recovery for our clients, and for the last 20 years we've been pretty consistent with that," noted Marc Malafronte, Territory Manager at StorageCraft, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"DivvyCloud as a company set out to help customers automate solutions to the most common cloud problems," noted Jeremy Snyder, VP of Business Development at DivvyCloud, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
Kubernetes is an open source system for automating deployment, scaling, and management of containerized applications. Kubernetes was originally built by Google, leveraging years of experience with managing container workloads, and is now a Cloud Native Compute Foundation (CNCF) project. Kubernetes has been widely adopted by the community, supported on all major public and private cloud providers, and is gaining rapid adoption in enterprises. However, Kubernetes may seem intimidating and complex ...