SQL Server Goes Columnar, Stays Relational
Readers of my
blog and/or
column know that I am a big fan of PowerPivot. And because of that, I was excited to see, both at June's Microsoft BI Conference (part of Tech Ed) and this week's
PASS Summit, that the PowerPivot technology will become more entrenched. What Microsoft showed in June was that the full-fledged SQL Server Analysis Services (SSAS) product would support the in-memory BI (IMBI) engine, known within Microsoft as VertiPaq.
This cool, columnar, in-memory technology will not be limited to personal and departmental BI solutions that live in Excel and SharePoint; instead, it will be available for Enterprise BI implementations too. As it should be.
But it gets cooler. What Amir Netz showed at PASS this week is that we will be able to open up an Excel workbook (that has a PowerPivot model in it) in the next version of the BI tools in Visual Studio, and have it reverse engineer the model, and then allow us to deploy the model to Analysis Services. Now we really have a workflow going, wherein "user-generated" BI solutions can be upsized to Enterprise ones, and in a way where the design can be refactored and optimized, rather than simply moved wholesale. It also means that even Enterprise BI pros can use PowerPivot as a tool for prototyping.
We're still not done. Not by a long shot. Because Netz, the father of Analysis Services, and his team have done something truly groundbreaking. They've voluntarily, indeed enthusiastically, made it possible for relational databases to take advantage of some of this cool IMBI technology. The next version of SQL Server, currently code-named "Denali" and available as a CTP1 release as of this week, will allow for the creation of so-called columnar indexes over relational databases.
In other words, the SQL and BI teams have worked together to implement VertiPaq's technology within the core SQL Server relational engine. This means the in-memory speed and high rates of compression currently implemented by PowerPivot, will also be available in relational databases. And the speed gains, while fastest in PowerPivot and the Denali version of SSAS, queried with MDX and DAX, will now benefit relational databases that are queried with SQL.
So now SQL Server will become a columnar database, but not in a way that makes it a niche product. Instead, it will continue to be the powerhouse it has grown to be, with mainstream appeal, and it will bring columnar technology to potentially all of its users. And while analytical work will still best be done with Analysis Services, the ability to perform aggregation queries directly against a data warehouse, with impressively fast response times, will be in mainstream users' hands too. I never thought BI for the masses would manifest itself this way. But the more I think about it, the more profoundly sensible the tactic seems.
I thought relational technology was completely mature and that all the action was on the BI side. The SQL Server team is showing us that such a dichotomy may be all wrong. The action is on the BI side and that, in turn, means relational database innovation continues.
Posted by Andrew J. Brust on 11/12/2010