T-SQL Tuesday #029 SQL Server 2012 Partying with Tabular and DAX

The T-SQL Tuesday topic this month is straightforward, asking us to write about our favorite feature of SQL Server 2012.  Nigel Sammy has invited us to a party that gives us a lot to talk about, making it hard to choose.

One of the overriding themes in the new release of SQL Server 2012 is the attempt to make the entry into Business Intelligence easier for people.  With the addition of features such as tabular models and DAX, analytic T-SQL functions, Power View, and columnstore indexing, the steep learning curve of BI has been reduced, and the promise of self-service BI gets closer.

The DAX functions added to tabular models is my vote for the most useful new feature in 2012.  It can be taken advantage of by almost any data architecture, whether it’s a dimensional model, report extract, or an OLTP DB.  The DAX functions allow us to dress up the model with fundamental measures that let developers and users create reports and other visualizations in a way that’s faster and cheaper for them.

DAX in Action

In the following image you’ll see a report table I imported into a tabular model from Adventure Works.  I quickly added a calculated column called expensive item, which is set to 1 if a sale is $50 or greater.  Then I added 4 typical but useful measures; sum sales, average sales,number sales, and number of expensive items sold.

For the Expensive_Item column, I created it with this expression

IF([SalesAmount] >= 50, 1, 0)

For the other measures I used these functions:

Sum_Sales:=sum([SalesAmount])
Average_Sales:=AVERAGE([SalesAmount])
Num_Expensive_Item:=SUM([Expensive_Item])
Num_Sales:=COUNTROWS(ReportExtract)

After a few minutes of working, I had a model that is useful,  provides basic functionality for reporting, and is one I can grow with.  It’s an easier entry point to reporting that requires fast performance and to Excel pivot tables than was available with prior versions of SQL Server.  Now you can add Power View to that list.

And the best part: the easier it is to implement, the more time we’ll have to party!