Share via

SQL Server Analysis Service 2016 CTP 2.3 DirectQuery in action

  • Article

In my previous blog post “What’s new in Microsoft SQL Server Analysis Services Tabular models in SQL Server 2016 CTP 2.3” I introduced some of the enhancements coming to SQL Server Analysis Services Tabular –and many more will be coming in the next CTPs. In this article, I’d like to show you what the combination of Super DAX and improved DirectQuery could mean to you.

For illustration, I loaded a dataset with 220 million rows in a fact table and several other dimension tables into a relational SQL Server 2016 CTP 2.3 database, I then enabled columnstore indexes on all the tables. The hardware was a Lenovo W530 laptop, so no server hardware by any means. Your mileage might vary, but I would imagine much better performance on a proper server with lots of RAM.

Next, I built a Tabular model in DirectQuery mode on top of this relational database by using SSDT for Visual Studio 2013 (this version also works with SQL16). For comparison, I installed two instances of Analysis Services, SQL Server 2014 and SQL Server 2016 CTP 2.3, and deployed the same model to both. All running on the same laptop.

Finally, I built a report by using Power BI Desktop. I connected to the SQL Server 2014 SSAS instance and started building my report:

Looks beautiful doesn’t it :). I added some realistic visuals like a matrix as well as these are usually the worst for performance.

Now, let’s take a look at a SQL profiler trace to see what is generated when I refresh the entire report:

The complete refresh of the report took 3 minutes and 34 seconds. 15 source queries were sent to SQL Server.

Let’s see what happens when I use the same report against my SQL Server 2016 CTP 2.3 instance:

The same report refreshed in10 seconds instead of 3 min and 34 seconds, a whopping 20 times faster! And Analysis Services only generated five SQL queries! 

That is a significant improvement you can start experiencing today. You will see the most improvement when using Power BI Desktop and Power BI service together with the Analysis Services connector but improvements can also be expected when using Power View in Excel and SharePoint. And as mentioned in my previous blog post, we are planning to ship even more performance updates in the next SQL Server 2016 CTPs.

So start using SQL Server 2016 CTP2.3 today and see for yourself! Here are the steps you can try out:

Use SuperDAX and the new DirectQuery with an existing model:

  1. Download SQL Server 2016 CTP2.3 here.
  2. Install Analysis Services from the 2016 setup on your development machine. You can install it as a new instance so you can run it side by side with SQL Server 2014.
  3. Backup the database you want to use and restore it to the SQL Server 2016 CTP instance.
  4. Point Power BI, Power BI Desktop, or any other client tool to the restored database.
  5. You can now enjoy improved performance with your tabular model.

To develop a new model using the new DAX functions or DirectQuery functionality:

  1. Download SQL Server 2016 CTP2.3 here.
  2. Install Analysis Services from the 2016 setup on your development machine. You can install it as a new instance so you can run it side by side with SQL Server 2014.
  3. Use SSDT for Visuals Studio 2013 and point the workspace database to the newly installed SQL 2016 instance.
  4. Open SSDT and then open your Tabular model solution.
  5. Start using the new DAX expressions.
  6. Deploy to your newly installed SQL Server 2016 CTP instance.
  7. Be advised that the new DAX expressions will not work with previous versions of Analysis Services, SQL Server 2016 is required.

I hope you are excited about the upcoming SQL Server 2016 release. This is just the beginning. More will come over the next couple of months, but I’m repeating myself. Stay tuned!

Comments

  • Anonymous
    September 09, 2015
    Kasper, this is very impressive! Is there any more info out on "super dax"? Should we expect similar performance gains with vertipaq storage?

  • Anonymous
    September 09, 2015
    Hi Bill, You can find more information in the previous blog post: blogs.msdn.com/.../sql-server-analysis-service-2016-ctp-2.3-directquery-in-action.aspx You will see performance gains with VertiPaq when using a tool that uses SuperDAX like Power BI desktop but they will not be as pronounced as for DirectQuery. Thanks, Kasper

  • Anonymous
    September 10, 2015
    Nice

  • Anonymous
    September 11, 2015
    How does this compare to running Analysis Services 2016 with SQL Server 2014?

  • Anonymous
    September 13, 2015
    Nice post. We have also ctp 2.3 and direct query works perfectly, but when we try to use it with Power BI SSAS connector with partial luck... It shows model in Power BI, but there are no measure groups / measures in them. Just trying ;)

  • Anonymous
    September 14, 2015
    HI GG, I am sorry I don't know the answer to that, I can only imagine it will be a bit slower as the SQL team has made improvements to the columnstore index in SQL 16 as well. Thanks, Kasper

  • Anonymous
    September 14, 2015
    Hi Pavel, Thanks for sharing, can you please file a ticket with Power BI. What you are describing is not expected behavior to me and should just work. Thanks, Kasper