I just watched the video below regarding different use cases and customer experiences with columnstore indexes in SQL Server, particularly SQL 16+. If you’re interested in hybrid transactional-analytical (HTAP) workloads and haven’t upgraded or if you just want a great overview of the technology it’s well worth the 60 minutes.
If you don’t watch it, the essential takeaway is that with the performance and functionality improvements to clustered and non-clustered columnstore indexes (CCI, NCCI) in SQL 16′ such as updateable NCCIs, there are many new scenarios which are great candidates for these indexes. You would normally associate columnstore indexes with large fact tables in a read only data warehouse but now, with minimal intervention or maintenance, you can dramatically improve the performance of many existing mixed workloads (OLTP & OLAP) as well.
At the very end of the video someone asks the question “Should you still use SSAS when you have columnstore technologies?” This topic came up at MS Ignite as well last year and here’s the issue: “Are you talking about Import (“cached”) or DirectQuery SSAS?”.
Let’s say you’ve dramatically improved performance of your SQL queries (and other metrics such as CPU utilization) via columnstore indexes. Excellent. It’s completely understandable if you don’t want to move the data to an SSAS server and manage the refresh/movement process this entails (and SSAS itself). You of course would also lose the real-time access to inserts and updates on the SQL index. This is a very big, important question I can’t go too far into in this post so I’ll just say this:
- SSAS provides much more than query performance.
- A user friendly interface (the model), relationships, hierarchies, KPIs, role security, perspectives, custom drill through – a rich metadata layer of business logic.
- An analytical language of DAX to simplify and extend your BI queries
- You should at least evaluate DirectQuery mode in SSAS Tabular 2016+
- In short, you can get the benefits of both SSAS and your columnstore index. SQL queries will be passed to your relational database and you’ll have semantic layer on top to define business logic and better analyze the data.
- If you have Enterprise Edition (required for DirectQuery) and you have referential integrity in your SQL database such that your SSAS DirectQuery model can send inner join queries, then this could be a great option
- As more hybrid workloads take advantage of columnstore indexes I hope SSAS DirectQuery is considered as a potentially great complement.
- In short, you can get the benefits of both SSAS and your columnstore index. SQL queries will be passed to your relational database and you’ll have semantic layer on top to define business logic and better analyze the data.
Successful Deployments of Columnstore Index in SQL 16:
Insight Quest 
One comment