Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

All the Aggregate Data you Crave with Grouping Sets in SQL Server 2008

Hole of Penon d'Ifache - Calpe, Looking W. Sierra Berna, background R(W)

As reporting requirements increase, it seems that aggregate functions have thankfully risen to the occasion concurrently. To maintain its competitive edge as Staples' best Canadian vendor, BaldGorilla, where I’m consulting actually, has been able to fulfill the most demanding deadlines thanks to the query results produced from Grouping Sets.  This is a new facet of the typical Group By clauses most database administrators have become accustomed to, prior to this version of SQL Server.

Straight to the point, all one has to remember is to include the grouped columns in brackets after the Group By Grouping  Sets (SelectCol1, SelectCol2,..) clause to fully enjoy what limited cube and rollup functionalities we have seen in previous versions of this database management system. Of course, Grouping Sets are not a replacement for denormalising these data and creating cubes in a true data warehouse, however went it comes to satisfying requirements under tight project deadlines for each iteration(s) of reporting deliverables for paramount decision support systems, this functionality is, without question, your overall rollup value blessing.

Actually, you might feel overwhelmed with all the extra lines of grouped sum values, therefore I have been cutting the result sets up into several tables as to not root confusion for the client – or my fellow developers and I JThese temporary table slices (in the end dumped into actual regularly pre-populated tables), are in actual fact partitions of the Grouping Sets, which have made it easy for our designers to create many intelligent decision support graphs.  These summative data tables can combined with yet another improved SQL 2008 application component that we have gotten quite used to over the past five years - Reporting Services.  The analysis fashioned thanks to Grouping Sets, is limited business intelligence without the prerequisite of building a (usually quite dear) fully fledged online analytical processing system.

References:

http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/

http://www.databasejournal.com/features/mssql/article.php/3790436/Grouping-with-SQL-Server-2008.htm - explains thoroughly how to use the brackets effectively

http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx

http://weblogs.sqlteam.com/derekc/archive/2008/01/31/60478.aspx

Comments

Posted by Hugo Shebbeare on 1 April 2009
Posted by Anonymous on 26 June 2009

Pingback from  Dot Net Usergroup in Montreal Presentation notes - Grouping Sets in SQL Server 2008 - The Database Hive for SQL Server DBAs

Leave a Comment

Please register or log in to leave a comment.