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!