SQL being a simplified and very powerful query language, has various pitfalls, when it comes to report generation capabilities of the system. However, we have OLAP-based systems and data warehouses, which provide a better way to analyze data and uses a multi-dimensional data model for complex and ad-hoc queries with a rapid execution time. OLAP systems require dimensions, and fact tables, ie, building a data warehouse, which is feasible for a medium or large sized organization but not for small applications. OLAP provides various operations like :

• Slicing and Dicing (selection conditions and attributes)
• Roll up and Drill down (to navigate along various levels of detail)
• Pivot (rotate the representation)

What if we could impart the same capabilities to all the applications ? I think we can. But how ?The SQL queries can be stated as very specialized and geeky language, and its not easy for everyone to understand the aspects of SQL language and RDBMS. And often criticized for its execution times as the amount of joins increase. Also, people higher in the management hierarchy, the more work they want to do with the data, and the less technical skills they have (mostly, but not all). Lets check out, how the specialized syntax model can help in extending the capabilities of SQL to provide OLAP operations.

System Building Blocks

1. The user sends a specialized syntax query to the Specialized syntax query processor.
2. The Specialized syntax query processor, parses the query and generates the SQL query to be given to thDBMSe . It also, checks for post-processing related information, and sends it to the Query Post-processor.
3. The DBMS, takes the SQL query and returns the result set to the Query Post Processor.
4. The Query Post-processor, takes the result-set from the DBMS and the post-processing related information from the Specialized Syntax Query Processor and processes the result-set.
5. The result-set is then returned back to the user.

Slicing and Dicing Operations

The slicing and dicing operations implementation are not much of a hassle to implement, as they can be implemented by just using the SELECT and WHERE clause in SQL.

Slicing can be done as : get all authors having tracks (name = "Database Engineering");

Dicing can be done as : get all authors (county =  "India") having tracks (name = "Database Engineering");

Roll-up and Drill-down Operations

SQL provides native support for GROUP BY clause, which, however, is only useful for aggregate functions. For example, if we want a summarized list of all tracks and the papers in each of them. Something like :

But when we query something like :

SELECT paper.id, count(*) AS "Total Papers", tracks.name
FROM paper,tracks
WHERE paper.tracks_id = tracks.id
GROUP BY tracks.id

But, however, this query generates results something like :

Hence, we can use something like:

get all paper details categorize by tracks with count

In the above query, the query will be generated to get all the papers in all the tracks and the result-set will be passed to post-processor. The post-processor shall then merge the rows having saming tracks and give the count.

In this manner, the roll up and drill down operations can be implemented, with the help of post-processor, which gives a good presentation of the result set to the user and extend the existing capabilities of the SQL query.

This method would provide a better insight of data to the user.