My first SQL Server 2016 POC

Very, very recently I did my first Proof Of Concept (POC) with SQL Server 2016. At an actual client of course 🙂
One of the first things I noticed, is that installing SQL Server 2016 is quite fast. Installing the database engine, SSIS, SSAS and SSRS is just a matter of minutes. In contrast with previous versions, not much time is lost installing the set-up files, which really makes a difference (especially if you go back and forth in the set-up wizard).

The goal of the POC was to showcase Power BI, with a SQL Server back-end. These are some of the new features that I immediately started using in SQL 2016 and which made me life a lot easier:

• Separate download installation of SSMS and SSDT. SSDT is now also backwards compatible for SSIS, but it didn’t really matter for the POC (just like incremental deployments, it was not needed).
• AutoAdjustBufferSize property of the SSIS data flow. Done with manually setting the Buffer Size and Buffer Max Rows. Just set this property to true and the data flow takes care of its own performance.
• Custom logging levels in the SSIS Catalog. Now I can finally define a logging level that only logs errors and warnings AND set it as the server-wide default level.
• The DROP TABLE IF EXISTS syntax. The shorter the code, the better 🙂
• Clustered columnstore indexes. Okay, they already exist since SQL Server 2014, but they’re more awesome and you can combine with traditional rowstore indexes. A 14.5 million row table with a 4.5GB size was reduced to a 400MB table. That’s a reduction of 90%!
• Better modelling experience in SSAS Tabular. Relationships now auto-detect the cardinality of the relationship when you drag one column on top of another. You no longer have to think about which column is the primary key and which one is  the foreign key and in which direction you have to drag the arrow. Tabular takes care of this for you. Cross-filtering on relationships is also very nice. And don’t forget the gazillion new DAX functions, like MEDIAN for example. The only issue I had is the combination of SSAS (Tabular) with Power BI. When you publish a Power BI Desktop report using the Tabular model as a source, everything works fine. But if you add a couple of new measures to the model, it can take a very long time before they show up in the Power BI Service. Power BI Desktop picks up the changes immediately, but not in the service. Hitting refresh doesn’t help (like you would expect, such as in an Excel PivotTable). It’s very similar to the caching issue about which I blogged earlier: Power BI Service error: “This visual has unrecognized fields”.
• And of course we can’t forget SSRS. A brand new web portal in HTML5. Mobile reports and KPIs. (I have to admit there’s a bit of a learning curve for me for the mobile report publisher. Creating a mobile report wasn’t as smooth as I hoped it would be). The new parameter pane in SSRS, new graph types and much better defaults for charts. It all just looks and feels better. Yes, I did mention new chart defaults. I guess they took a good look at Power BI:

Doesn’t that look nice? Okay, I admit, I made a few small changes:

• I made the line a bit thicker
• I changed the interval step of the secondary axis so the major gridlines would align with the ones from the primary axis

The only thing I would add to the defaults is a better formatting for the axis numbers. Thousand separators would be very welcome. Or maybe shorten the numbers to 5M instead of 5000000. Anyways, the charts look a lot better now in comparison with previous versions (you remember the bright blue and orange?). Welcome to the 21st century SSRS!

In short, it was a real pleasure working with SQL Server 2016. I hope there are more clients who will install SQL Server 2016 as well.