TSQL Tuesday #026 – Second Chances

This month’s T-SQL Tuesday #026 hosted by David Howard asked people to take on a wide open task, to write about any of the previous 25 topics.  Having so many to choose from should have made it easy, but instead it was harder.  I could have done something with each topic since it seems that reading the old blogs only sparked more new ideas.

The Future

Instead of going to the past, I decided to go to the future and write about the new features in SQL Server 2012 that will help us with aggregation, a subject that should be close on any BI developer’s mind.  I chose the topic because of T-SQL #016 by Jes Borland on Aggregations since there are going to be some interesting new functions to help us aggregate better.

Windows in T-SQL

The Windowing functions (which have nothing to do with Windows) in the upcoming version of SQL Server let us do in one query Analytic calculations that include sliding aggregations, standard deviations, and more.  This way we can compare averages, sums, and other numbers using preceding and current rows in a much cleaner, simpler way than we can today.

SELECT OrderDateKey, DailySales,
WeekAvgCurrent = AVG(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
WeekAvg1WeekAgo = AVG(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 13 PRECEDING AND 7 PRECEDING),
WeekDiff = AVG(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) – AVG(DailySales)    OVER (ORDER BY OrderDateKey ROWS BETWEEN 13 PRECEDING AND 7 PRECEDING),
WeekStdDev = STDEV(DailySales)
OVER (ORDER BY OrderDateKey ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
FROM (SELECT f.OrderDateKey, SUM(SalesAmount) as DailySales
                  FROM FactInternetSales f
                  inner join DimDate dd on dd.DateKey = f.OrderDateKey
                  where dd.CalendarYear = 2003
                  group by f.OrderDateKey) as DailyTable
order by OrderDateKey

The query produces this result from AdventureWorksDW. Only the first 12 rows are shown and I modified the column titles slightly for readability:

Order Date Key	Daily Sales	This Week Avg	Last Week Avg	Difference Week-to-Week	This Week Std Dev
20030101	12444.75	12444.75	NULL	NULL	NULL
20030102	19703.48	16074.12	NULL	NULL	5132.7
20030103	13519.61	15222.62	NULL	NULL	3917.584
20030104	18628.97	16074.21	NULL	NULL	3623.873
20030105	13497.29	15558.82	NULL	NULL	3343.268
20030106	4363.125	13692.87	NULL	NULL	5461.919
20030107	14623.17	13825.77	NULL	NULL	4998.41
20030108	15733.28	14295.56	12444.75	1850.814	5001.517
20030109	18141.69	14072.45	16074.12	-2001.67	4748.503
20030110	15558.27	14363.69	15222.62	-858.929	4771.408
20030111	13976.53	13699.05	16074.21	-2375.15	4386.78
20030112	13254.69	13664.39	15558.82	-1894.43	4389.597

The results show you that we can get the average sales for the last 7 days, the sales for the 7 days before that, compare the two to get the difference between the last two weeks, and the standard deviation for the week.

All in one fairly simple straightforward query. Very powerful.

SQL Saturday #104 Rips and Shreds

SQL Saturday #104 took place this weekend and was four times bigger than last year.  This shows the type of effort the organizers put into creating a great event (it also showed on their tired faces).  Held in picturesque Colorado Springs, this SQL Saturday has one of the more unique venues in that it’s held at a ‘fun center’ that has parties and games for children (and youthful adults).  At times you think you might see a magician or a clown in the ‘Top 10 SQL Blunders’ session.

Many thanks have to go to Chris Shaw (T | B) and Jeremy Lowell (B) who put in countless hours to make the event so successful.  They brought in excellent sponsors, put together an all-star lineup of speakers, had good prizes, good food, and numerous activities including a ski trip.  Other tireless volunteers included Gabriel Villa (B|T), Andrew Dykstra (T), Rebecca Mitchell (B|T) and several others.

My Session

My session on Columnstore Indexes was well attended, had good audience participation and great questions.  Thanks to everyone who came.  I hope I was able to help you take a small step in getting ready for the coming of SQL Server 2012.

The People

My favorite part?  Talking to people.  There’s nothing better to get your juices flowing and head spinning than having spirited discussions on tech and non-tech subjects with Bill Pearson (T), Jim Murphy (B|T), Jeff Renz (T), Jason Horner (B|T) and so many others.

SQL Saturday #104 in Colorado Springs

I’ve uploaded my slide deck and scripts for my presentation Using Columnstore Indexes in SQL Server 2012 that I’ll be delivering at 11am in Colorado Springs tomorrow (January 7, 2011).  To download the presentation material click here.  If you’re undecided as to what sessions to attend this should give you a good idea as to what I’ll be talking about.  When SQL Server 2012 is released, Columnstore Indexes will provide BI Architects and DBAs with another alternative for fast query response, but they are not appropriate for every situation.  My session will give you a good understanding of when it’s right to use this feature and how to prepare your data models when the time comes.

Using Columnstore Indexes in SQL Server 2012

 
Columnstore Indexes in SQL Server 2012 will allow you to significantly improve the processing time of common data warehousing queries without creating cubes, aggregated tables, or other techniques normally used to improve performance. This session will show how to implement this new type of index in SQL Server and demonstrate their advantages compared to traditional solutions. Carlos will also discuss the scenarios for which columnstore indexes should be implemented to provide powerful but flexible BI solutions.

Click here for a blog I wrote last week with more details about my session. I’ll see you in the Springs!