THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

xVelocity engines compared: VertiPaq vs ColumnStore #ssas #vertipaq #xvelocity #sql #tabular

During the last months I and Alberto worked in several projects using Analysis Services Tabular and we had to face real world issues, such as complex queries, large data volume, frequent data updates and so on. Sometime we faced the challenge of comparing Tabular performance with SQL Server. It seemed a non-sense, because even if the same core xVelocity technology is implemented in both products (SQL Server 2012 uses ColumnStore indexes, whereas Analysis Services 2012 uses VertiPaq), we initially assumed that the better optimization for the in-memory engine used by Analysis Services would have been always better than SQL Server.

However, we discovered several important things:

  • Processing time might be different and having data on SQL Server could make ColumnStore way faster for processing.
  • Partitioning in SQL Server might be much more effective for query performance than Analysis Services.
  • A single query can scale easily on more processor on SQL Server, whereas in Analysis Services the formula engine is single-threaded and could be a bottleneck for certain queries.
  • In case of a large workload with many concurrent users, storage engine cache in Analysis Services could be a big advantage over SQL Server, especially for scalability

As you can see, these considerations are not always obvious and you might be tempted to make other assumptions based on these information. Well, don’t do that. Before anything else, read the whitepaper VertiPaq vs ColumnStore Comparison written by Alberto Ferrari. Then, measure your workload. Finally, make some conclusion. But don’t make too many assumptions. You might be wrong, as we did at the beginning of this journey.

Published Wednesday, August 22, 2012 2:56 PM by Marco Russo (SQLBI)



kamesh peri said:

what are your thoughts on sql 2014 oltp column store vs ssas tabular

November 2, 2013 6:09 PM

Marco Russo (SQLBI) said:

In terms of performance, we'll have to run many tests again in order to update the whitepaper, but we'll do that only after RTM. In terms of features, you cannot really compare two products - Analysis Services add a metadata layer that you don't have in SQL. The goal of the whitepaper is just to compare performance.

November 3, 2013 1:49 AM

BI Beginner said:

This is a slightly out of context question for this particular topic ,but couldn't find one related to this.

I am working on a tabular model ,when i set the DirectQueryMode as "on"  in my model properties.And then try to import tables from the data source ,i get the following error.

DirectQuery error: All tables used when querying in DirectQuery Mode must be from a single relational Data Source.

I am using the same data source ,still i seem to be getting this error. Any idea as to what i might be missing out??Is there a chance this could be due to partitions in my data source???

December 24, 2013 2:22 AM

Marco Russo (SQLBI) said:

This is strange - consider that with DirectQuery only SQL Server is supported and only one partition per table (in Tabular) can be marked as DirectQuery partition.

December 24, 2013 2:30 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement