Redmond Diary

By Andrew J. Brust

Blog archive

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 at 1:15 PM

comments powered by Disqus


  • Color Shards

    Sharing Data and Splitting Components in Blazor

    ASP.NET Core Version 3.1 has at least two major changes that you'll want to take advantage of. Well, Peter thinks you will. Depending on your background, your response to one of them may be a resounding “meh.”

  • How to Create a Machine Learning Decision Tree Classifier Using C#

    After earlier explaining how to compute disorder and split data in his exploration of machine learning decision tree classifiers, resident data scientist Dr. James McCaffrey of Microsoft Research now shows how to use the splitting and disorder code to create a working decision tree classifier.

  • Microsoft: Move from Traditional ASP.NET to 'Core' Requires 'Heavy Lifting'

    There are plenty of reasons to move traditional ASP.NET web apps -- part of the old .NET Framework -- to the new cross-platform direction, ASP.NET Core, but beware it will require some "heavy lifting," Microsoft says.

  • Purple Blue Nebula Graphic

    How to Compute Disorder for Machine Learning Decision Trees Using C#

    Using a decision tree classifier from a machine learning library is often awkward because it usually must be customized and library decision trees have many complex supporting functions, says resident data scientist Dr. James McCaffrey, so when he needs a decision tree classifier, he always creates one from scratch. Here's how.

  • Blazor's Future: gRPC Is Key

    Blazor guru Steve Sanderson detailed what Microsoft is thinking about the future of the revolutionary project that enables .NET-based web development using C# instead of JavaScript, explaining how gRPC is key, along with a new way of testing and a scheme for installable desktop apps.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events