Grouping Sets in the Relational Database as an Alternative to Cubes in Analysis Services

If you're familiar with the basic concepts of Business Intelligence (BI) and Analysis Services (AS), you might have already realized that there’s great power in the new grouping-sets features in SQL Server 2008, as well as the new MERGE statement. For those customers who need to dynamically analyze aggregates, slicing and dicing data, and don't want to take the extra step of implementing cubes in AS but rather stick to the relational data warehouse or data mart, there are now richer analytical capabilities in the relational database. Each dimension hierarchy in AS can be represented by a ROLLUP subclause, and basically by crossing multiple ROLLUP subclauses, you can mimic what a cube in AS gives you. For example, suppose you have three dimensions: Time (AllTime, OrderYear, OrderMonth, OrderDay), Product (AllProducts, Category, Subcategory, ProductName), and Customer (AllCustomers, Country, Region, City, CustomerName), as well as measures called Qty and Val that you need to aggregate (say, calculate the sum). You can produce the relational alternative to a basic cube with the aforementioned dimensions and measures with the following query:

SELECTGROUPING_ID(OrderYear, OrderMonth, OrderDay,Category, Subcategory, ProductName,Country, Region, City, CustomerName) AS grp_id,OrderYear, OrderMonth, OrderDay,Category, Subcategory, ProductName,Country, Region, City, CustomerName,SUM(Qty) AS TotalQty, SUM(Val) AS TotalValFROM ‹join_fact_and_dimension_tables›GROUP BY GROUPING SETS (ROLLUP(OrderYear, OrderMonth, OrderDay),ROLLUP(Category, Subcategory, ProductName),ROLLUP(Country, Region, City, CustomerName) ); 

As I demonstrate in the main article, you can materialize the result set in a table using a SELECT INTO query, and cluster the table by the grp_id column plus individual attributes. You can then handle incremental updates by using the new MERGE statement as long as the measures are additive. Note that AS provides much more sophistication, scalability, and optimization benefits than you can achieve in the relational database alone. Again, for those customers who don't implement cubes in AS, there's now an alternative to basic cubes.