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 currently consulting, 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 a 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 :). These 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 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 the different result sets depending on how you use the brackets
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
No comments:
Post a Comment