Analytical Queries???
Posted at 3/03/2008 06:50:00 AM
Analysis – A breaking up of a whole into its parts to find out their nature (Websters New World Dictionary).
It seems strange to talk about analytical queries when the entire analysis must first be defined in order to create the SQL query. The analysis is really in the preparation, and the actual query becomes just another report generation process. If the analysis is not exactly what is needed, there is no way to know that until the report is done. Then the analysis/report process starts again or, all too frequently, the process is abandoned without a result.
The incremental query moves the analysis into the query process where it really belongs. The person who needs the result of the analysis can ask questions until the analysis is complete and the answer has been found.
For example, a retailer wants to prepare a list of customers for a promotion. They want the usual criteria like geographic and demographic selections and they want at least 5,000 good names and not more than 6,000.
The initial geographic and demographic questions are asked and there are 25,000 customers qualified. Now, the retailer can just take the first 5,000 names, or the quality of the list can be improved through some analysis, narrowing the list to the best 5,000—not simply the first 5,000.
With the incremental query process of Illuminate, they continue by asking for only customers who had previously purchased a specific type of product. Now the 25,000 names have been reduced to 10,000 better-qualified customers. Add another query step to select only those who shop on weekends. Now they have the 5,000 customers who precisely fit the profile that they want.
With the incremental approach, you ask for one qualification at a time and you can review the results between each step. The process just described would take a minute or two. In an RDBMS environment, reaching the same result would require either an extensive analytical process to define the final query or multiple sessions of: query design, examination of results and query redesign until the final result is reached.
Due to the cost of the old approach, the typical process will be to stop with the first query step and business user will just take 5000 off the list. The quality of the promotion will be compromised—but if the promotion planning is done on a weekly basis, there is no time for the alternative.
Result? - The business suffers due to the limits of technology.
It seems strange to talk about analytical queries when the entire analysis must first be defined in order to create the SQL query. The analysis is really in the preparation, and the actual query becomes just another report generation process. If the analysis is not exactly what is needed, there is no way to know that until the report is done. Then the analysis/report process starts again or, all too frequently, the process is abandoned without a result.
The incremental query moves the analysis into the query process where it really belongs. The person who needs the result of the analysis can ask questions until the analysis is complete and the answer has been found.
For example, a retailer wants to prepare a list of customers for a promotion. They want the usual criteria like geographic and demographic selections and they want at least 5,000 good names and not more than 6,000.
The initial geographic and demographic questions are asked and there are 25,000 customers qualified. Now, the retailer can just take the first 5,000 names, or the quality of the list can be improved through some analysis, narrowing the list to the best 5,000—not simply the first 5,000.
With the incremental query process of Illuminate, they continue by asking for only customers who had previously purchased a specific type of product. Now the 25,000 names have been reduced to 10,000 better-qualified customers. Add another query step to select only those who shop on weekends. Now they have the 5,000 customers who precisely fit the profile that they want.
With the incremental approach, you ask for one qualification at a time and you can review the results between each step. The process just described would take a minute or two. In an RDBMS environment, reaching the same result would require either an extensive analytical process to define the final query or multiple sessions of: query design, examination of results and query redesign until the final result is reached.
Due to the cost of the old approach, the typical process will be to stop with the first query step and business user will just take 5000 off the list. The quality of the promotion will be compromised—but if the promotion planning is done on a weekly basis, there is no time for the alternative.
Result? - The business suffers due to the limits of technology.
