Monthly Archives: April, 2012

SQL Saturday #131 is the Place to Be

I’ll be presenting on April 28 at my 4th SQL Saturday this year in Phoenix, Arizona.  As I thought about what to write it struck me that this is SQL Saturday number 131.  That’s a lot of events in just a few years!  It made me wonder if there are people who work with SQL Server who have never heard of SQL Saturday.  I don’t think that could be possible, so this post is really just a reminder to people to get registered and go.  It’s clearly the place to be and thing to do; the total number of planned SQL Saturdays just grew to 155 with one in Peru having just been announced for this summer.

60 Sessions

In Phoenix (Chandler) this weekend there are 10 tracks and 60 sessions total, so there has to be something for everyone.  I can’t imagine that there’s a SQL topic I can think of that isn’t being covered by an expert in the field, and with so many great presenters descending upon Arizona this weekend the networking is going to be a killer!

I have 3 sessions to deliver.  One is an original topic I will be co-presenting with friend and client Jeff Renz called The 2012 Data Warehouse Architecture Debate.  This session will discuss the pros and cons of dimensional modeling and data vault data warehouses and will be presented in the spirit of a debate since it’s an election year.  I’m also presenting Real-time Data Warehouse and Reporting, a session I’ve done before several times that gets me the best feedback of any session I do, so it seems people like it.  The other session I’m presenting is Fast-track to BI Analytics with SQL Server 2012 showing you a fast way to get to Business Intelligence with SQL Server 2012.  If their are barriers to establishing BI technology in your organization, this session is a great way to see how you can overcome these obstacles using  new features introduced in SQL Server 2012 such as tabular models, columnstore indexing and analytic functions.

See you there!

Advertisements

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!