SQL Server 2017 has some significant new features being introduced, but will it make much of a difference for BI professionals? That’s the refrain on everyone’s mind for every new release of SQL Server, and, really, for any product. Is it worth the pain and hassle of upgrading? We’re looking at the top new features in SQL 2017 and what they mean for the BI professional.
The big news, which is perhaps the most surprising development, is the announcement of SQL Server 2017 on Linux. Who would have ever thought that Microsoft would release a flagship server product on Linux? Well, for BI professionals, its applicability is going to be niche as it only includes the database engine and SQL Server Integration Services (SSIS) components. So, for a primarily Linux shop, it can be used in conjunction with Power BI to host an enterprise data warehouse. It can also be used as an ETL server running primarily SSIS, particularly in conjunction with the new SSIS scale-out feature. However, SQL Server 2017’s lack of Analysis Services (SSAS) and Reporting Services (SSRS) support means it cannot function as a standalone BI server. It also lacks PolyBase and Stretch support for a hybrid environment. However, to clients, it’s virtually indistinguishable from SQL Server on Windows. While it’s not ready for most BI environments just yet, it’ll be good to keep an eye on it for future releases.
Data scientists will appreciate the new Graph processing features added to the database engine. Graph processing uses the concept of nodes and edges to model objects and their relationships. For instance, it would be useful in establishing flight routes or in analyzing social network activity. The node and edge tables implicitly link together. Those tables are used with specialized queries to simplify certain unwieldy and inefficient SQL queries. In any case, using the Graph processing features should simplify and accelerate analysis of complex or recursive relationships.
While there’s no single SSAS Tabular development that stands out, it has a grab bag of smaller features added. Here are the two biggest: Object-level security is the headline. Administrators can now set permissions on tables and columns in addition to the row-based security introduced earlier. That can completely prevent users from seeing or accessing certain tables or columns. With both row and object security, administrators can provide a robust security model. SSAS 2017 also incorporates the Get Data and Power Query/M features from Power BI. That will allow a tabular model to reference a much larger variety of data sources directly and perform quite a bit of ETL on its own. For some users, it may allow them to use SSAS tabular in environments where a data warehouse is not available. With these additions, there’s rarely a need to implement a multidimensional model.
The final important feature is another one for the data scientists. SQL Server R Services now becomes SQL Server Machine Learning Services. Why the name change? R Services needed a rename now that it also supports Python in addition to R. Python is one of R’s principal alternatives in the data science world, and now users do not have to learn R if they are already using Python. Machine Learning Services integrates the SQL database engine with the external R and Python engines, increasing the storage performance of R or Python scripts. It also allows those R and Python scripts to be embedded in SQL scripts and stored procedures. For a more in-depth blog post on this feature, check out this blog post.
Now, those three are not SQL 2017’s only new features. Here are a few quick highlights of the rest:
- Automatic database tuning can now detect and fix potential performance issues.
- Master Data Services has performance increases throughout.
- SSIS added Scale Out support to Always On support, allowing for high performance and high availability in clustered environments.
- Object-level security in Tabular allows administrators to restrict users’ access to tables, columns and measures.
- Increased support for DAX in SSRS and SSDT
So, where does that leave the IT decision makers? Should they make the jump and endure the pain that upgrading entails? For BI purposes, it appears that the big winners with this release are the data scientists. Python and Graph processing could significantly improve their workflows. For others, the decision might be a little murky.
Still having trouble deciding? Our team at BlueGranite can help you develop a strategic plan, including relevant SQL Server features, to match your analytic goals. Contact us for more details!
