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

comments powered by Disqus


  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

  • What's New for Python, Java in Visual Studio Code

    Microsoft announced March 2024 updates to its Python and Java extensions for Visual Studio Code, the open source-based, cross-platform code editor that has repeatedly been named the No. 1 tool in major development surveys.

Subscribe on YouTube