February 28, 2013

SQL Visualization in the Spreadsheet

SQL is a database query and programming language for retrieving, updating, and managing the data from relational database. SQL was certified to meet ANSI in 1986, and became an international standard in 1987. Nowadays, SQL becomes a basic requirement for every programmer. However, the advantages cannot obscure the disadvantage. SQL is especially designed for technical personnel. SQL syntax is highly abstract, the logic is hard to understand, and only those with strong technical background can grasp it. However, in the business office workspace, the non-technical users will usually need the query and process data by themselves. They hope there is a reporting tool with a technical requirement as low as almost zero, and a computing capability as strong as SQL tool.

For example, a secretary needs to prepare a latest list of big clients for a regularly scheduled meeting. The big clients are those who accounts for 50% of the total sales for the company the secretary is working with. Assign or request an IT team to handle it will cost a great deal of time to coordinate. Therefore, he decides to calculate all by himself. He can use the business language to describe the calculation process:

  • Filter by time: Filter out the data of the half year in the order table.
  • Group and summarize: Group the data by client, and the total sales of orders in the group will be the sales of clients.
  • Set the standard for comparison by calculating: The total sales value is the sales sum of all clients. Multiply the total sales by 0.5, and the result is the standard of comparison.
  • Sort by sales value: Sot the clients by sales descendingly.
  • Calculate the cumulative value: Sum up the sales one by one from the highest to the lowest. Suppose if there is a client in the 3rd place, then the cumulative value of this client is the sum of the three sales values among the top 3.
  • Filter out the big client: The calculation goal is to find the clients whose cumulative value is less than the standard of comparison. Suppose if the standard of comparison is greater than the cumulative value of the 5th clients but less than that of the 6th, then the top 5 clients can all be regarded as the big clients.

Previously, those business users who are not familiar with SQL cannot implement the above calculation. Nowadays, esCalc enables them to operate visually and calculate intuitively in their own business language and thinking pattern. For another example:
  • Find the clients whose annual sales are among the top 10 in every year.
  • Collect statistics on the newly opened retail stores, including: How many retail stores newly opened this year? How many retail stores have profited over one million dollars? Of these retail stores, how many of them have opened their businesses overseas?
  • How much does the sales increase compared with that of the previous month?

The above calculation problems are quite common in the modern business office workspace. In fact, these problems are just the combination of SQL filtering, grouping, summarizing, distinct, horizontal joining / vertical union, and other calculation methods.

Raqsoft makes great contribution for SQL visualization. For example, esCalc is the SQL without any requirement on the technical background of users. esCalc embodies and visualizes the calculation methods innovatively, enabling the business users to be same competent and capable as the IT technicians in some business computing.

Related News from Raqsoft:
Truth behind Ticket Purchasing Rush: Statistical Analysis Works
What Makes Self-service Statistical Computing Tools So Important?
Creative Spreadsheet Software for Data Processing in Development

1 comment:

  1. What about using the likes of MicroStrategy, Pentaho, QlikView, Cognos etc.?