SQL Spot – Part 1

It has been going fast in 2016 for Microsoft BI. If we continue to move on like this, we’re in for a treat in 2017. However, more technical growth means more ground to cover and more knowledge to be gathered. In this blog series I want to share some interesting articles, which crossed my path in the past month, either professionally or out of personal interest. You can expect information ranging from business perspective to technical insights on various topics like SQL Server News, business perspectives, Power BI, SQL Server BI stack, features, data science, accidental DBA stuff and interesting learning or study materials. All rights go out the great blogging community.

I am mainly a BI developer being an accidental DBA sometimes. I like to pick up on the basics of things to help me out in occasions I’m forced to pick up the role. Also, with SP1 a lot of new features became available on standard editions as well. So, it is a good idea to have some of the enterprise’s feature fundamentals well understood. This month I tried to gather some highlights regarding SQL Server 2016 features or news you might have missed out on.

Business, releases and news

• Small shout out to Power BI last year. In 2016, Microsoft was in the leaders section of the Gartner Magic Quadrant. Why? Microsoft is the Coca Cola of BI.
• In case you missed it, the release of SQL Server 2016 SP1 is a major game changer. Have Kendra Little give you some more insight on the changes at hand, and why standard edition users have no excuses anymore to move on to 2016. Keep in mind that the limitations of lower editions still apply.
• Last month, Microsoft introduced SQL Server next version Community Technology Preview 1.1. SQL Server’s next version CTP 1.2 now available. As part of the rapid preview model, Microsoft is excited to announce that the next version of SQL Server (SQL Server v.Next) Community Technology Preview (CTP) 1.2 is available on both Windows and Linux.
• In a fast growing market, it is good to know what Microsoft BI can mean for you today.
• Planning on upgrading towards Windows Server/ SQL Server 2016? Be mindful, on the new Windows Server/ core licensing rules in relation to SQL Server’s rules when deciding on a new server architecture.
• Introducing SQL Server Premium Assurance extending your product support if needed.
• vNext and the buzz on SQL Server on Linux? Check out here how it works or watch our webinar replay one the SQL Server 2016 february update.
• The SQL Server Management Studio Version 16.5.2 is now available to download. The latest release from the SQL Server product group at Microsoft has just gone public and is available for download today.

Power BI

• Power BI feature summary for January.
• Don’ t miss the general availability of Power BI mobile phone reports.
• Introducing email subscriptions in Power BI: stay informed when it matters.
• Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available.
• Documenting your tabular or Power BI models.
• Push data to Power BI streaming datasets utilizing Microsoft Flow: Have Chris Webb show you how to put the Power BI REST API to good use by pushing data from Excel to Power BI using streaming datasets. He even shows you how to call Microsoft Flow, pushing a request and returning a response from the flow.
• Check out the new Power BI Content Pack for Azure Active Directory.
• Check this series on combining Machine Learning and Power BI.
• If you are using Jira for project management, bug and issue tracking, you will be pleased that Microsoft provided a JIRA content package for you. Check it out!
• Pragmatic Works is providing some excellent vids on using Power BI Custom visuals. Make sure to check it out and help your clients to visualize in many different formats.
• Visualizing SQL Server and R data in Power BI and SSRS hands on.

SSIS

• Have Tim Mitchell show you how to retrieve a list of files from FTP, using SSIS.
• SQL Server 2016 supports Always On for the SSIS Catalog. I know some clients who will be happy with this one.
• SQL Server 2016 supports Integration Services Incremental Package Deployment to an existing project or new project.
• Introducing reuse control flow across packages by using control flow package parts.
• The new always encrypted feature is also supported in the SSIS environment.
• Error Column Improvements for SSIS Data Flow.
• If you’re working with Azure sources, don’t miss out on the Azure Feature Pack for Integration Services.
• SSIS already supports the built-in R services in SQL Server. You can use SSIS, not only to extract data and load the output of analysis, but to build, run and periodically retrain R models.

SSAS

• SSAS Tabular v.next promises even more great new features such as the integration of the M language. Find out more here.
• Azure SSAS , why and when is it interesting for you. Kasper does a great job of summing them it up here.
• How to document your tabular or Power BI models.
• The appraised cross directional filtering has made its way to the SQL Server 2016 SSAS tabular mode.
• There are a lot of new functions for DAX, but I think the variables feature is really interesting in SQL Server 2016.
• Have Marco Russo help you out with monitoring your tabular and multidimensional models with SSAS Events Analyzer.
• SSAS Activity Monitor is an open source tool to monitor the current activity (sessions, transactions, connections, and locks) on an Analysis Services instance. It works for both Tabular and Multidimensional mode.

SSRS

• SQL Server Performance dashboards now made available through SSRS by the Tiger Team
• Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available
• Visualize SQL Server and R data in Power BI and SSRS hands on.

Data science

• Have our own Sammy Deprez tell you why you should invest in data science.
• Get started with SQL Server R services. Also, get a look behind the scenes and follow this new series on why and how about SQL Service R Services.
• Discover the why and how on deep learning inside SQL Server, bringing intelligence to the platform using R, machine learning functions and deep neural networks (DNNs).
• Get started with the basic fundamentals of Azure Data Lake.
• What you should understand from Azure SQL Data Warehouse: get a taste and feel to start playing with Azure DWH and Machine Learning.

Maintenance

• Check your system’s health using scripts, SSRS and Power BI provided by Microsoft’s Tiger Team’s Github toolbox.
• Not something new for the veterans, but the reward winning maintenance solution from Ola Hallengren, supporting automated backup strategy, index maintenance and more.

Security

• Now that Microsoft took away your doubt regarding upgrading that standard edition to 2016, providing it with some enterprise powers, let us get started on Always encrypted in SQL, a new way to encrypt columns in SQL Server 2016.

Tuning

• Get to know the Query Store functionality and its best practices. Also check out the series from Enrico van de Laar who has a lot more to say on the topic.
• Announcing Columnstore Indexes and Query Store support in Database Engine Tuning Advisor.
• Query completion is a prerequisite for the availability of an actual query plan, but with SQL 2016 and Live Query Statistics(LQS), you can already get information about in-flight query executions (see this blog post), which is especially useful for long running queries, and queries that run indefinitely and never finish.
• As an accidental DBA, I hadn’t even considered this one: Erik Darling has to say a thing or two on indexing temp tables.
• You usually only hear about the big ticket features when a new edition is released. Adam Mechanic points out some smaller interesting gems in SQL 2016 that you probably did not hear about.
• If you’re more of an accidental DBA like myself, then having Adam Machanic point out the 5 basic culprits in a query plan causing performance issues, might help you avoid to bring in the experts, allowing them to keep focused on the bigger issues.
• A new property in SQL 2016 execution plans to help us out: estimated number of rows to be read.
• With the release of SQL Server 2016 SP1, you can now use data compression. This article will give you an idea on the trade of in performance between page and row compression.
• Have Aaron Bertrand show you some bad habits and best practices when writing T-SQL.
• SQL Sentry did not just become Sentry One. They did it with a bang, making their award winning Plan Explorer, a free for all tool.

Learning

• If you’re into learning, you definitely picked up that Microsoft provided new streamlined certification paths. Best to read up on what will happen to your certificates.
• If you did not do it in 2016, get yourself ready and close your skill gap by enrolling in the Data Science Track in the Microsoft Professional Program.
• In addition to the same platform, you can get introduced to the Architecting Microsoft Azure Solutions.
• Expand your career in Azure with some massive online open courses (MOOC’s) offered by Microsoft.
• Have some fun with updating your visualization chart knowledge.

Vote of the month

• SSAS Azure: Support for multidimensional models