SQL Spot – Part 3

The growth spurt of SQL Server 2016 and all its related dimensions is hard to follow. Yet with our SQL spot we try to gather some intel for all SQL Server enthusiasts. As always, we try to share some interesting articles, which crossed our paths recently, either professionally or out of personal interest. The gathered community information addresses a healthy BI perspective, ranging from business to technical insights on various topics: SQL Server news, Power BI, SQL Server BI stack, features, data science topics, accidental DBA stuff and interesting learning or study materials.

I am mainly a BI developer, being an accidental DBA sometimes. So  I like to pick up on the basics of things to help me out in occasions I’m forced to pick up the DBA role. Also, since SP1 a lot of new features became available on standard editions. It is a good idea to have some of the enterprise features’ fundamentals well understood. Tuning and maintenance will provide the basics and a starting point for data analysts to get some insight on the inner workings of SQL Server. All rights go out the great blogging community.

Business, releases, news and trivia

Business

• Why you should consider using SQL server 2016.
• If you mov to Azure, what will it cost and what the heck is a DTU?
• Why defining a data risk strategy for your organization is crucial.
• Read up on GDPR and how it will impact your organisation.
• Read how self-serve business intelligence can significantly decrease the data preparation cost, using the savings to let IT focus on bigger projects.
• The importance of knowing your organizational context and what we’re interested in defines how data can provide insights within our business
• Get a grip on today’s Analytics Industry Trends.
• How do you act when a data breach occurs?
• What does it mean to be a chief data officer?
• The growing need for SQL Server Hadoop

Releases

• Visual Studio 2017 is out there and Microsoft partnered with Redgate to include some nice features in the Enterprise edition.
• SQL Server Vnext CTP 1.4 is now available
• Managed disks have been released for production  in february 2017 to help you increase manageability of your Azure VMs. Deep dive into the key benefits and important details you will need to keep in mind.
• SQL Server 2016 Service Pack 1 CU2 is released.

Data stories

• How far are we from the super computer AI self-driven car?
• The immediate gap between shortage of deep learning skills and the different styles of deep learning and what you need to know about them.

Power BI

General

• Need some help explaining Power BI to your customers? Why not use this infographic for Power BI from an end-to-end perspective?
• Looking into using Power BI for your organization? What does Power BI really cost ?
• If you are a consultant with multiple Power BI subscriptions across multiple clients, then this small tip from Chris Webb is a must see.

Power BI

• A guide to using the new Amazon Redshift Connector.
• Get started with the publish to web feature and share your Power BI reports with ease within your blogs or sites. Get some good examples of this feature.
• Get introduced to the preview of using the Shape Map and the main difference with the Filled map, including pros and cons of using one or the other.
• Check out the new matrix and Custom Report Themes.
• The advanced color theme generator is a tool designed to aid in the selection of colors for Power BI color themes.
• Get your own Power Query Editor with intellisense using Notepad++.
• Announcing granular tenant settings in Power BI.
• Embedding Power BI dashboards into your applications.
• Filtering your Power BI dashboard using parameters through the URL.
• Using text to generate a word cloud.
• Prevew of the quick measures
• Power BI Gateways – March update
• Power BI Desktop – March feature summary
• Power BI Desktop – April feature summary

Excel BI

• All About the ranking function in Excel.
• Catching the slicer selection in Powerpivot (and power BI).
• A neat Power Query trick to swap a pivot table around.

DWH

Azure

• As a new feature within SQL Server 2016, the Temporal Table and its practical use within DWH environments is something we should pick up on.
• Microsoft announced that you can now directly import or export your data from Azure Data Lake Store (ADLS) into Azure SQL Data Warehouse (SQL DW) using external tables.

SSIS

• Get to know a rather unknown dm_execution_performance_counters function to help you track SSIS related performance counters, and how it can be utilized effectively within an environment
• Making the most of the SSIS catalog requires an understanding of how to access the information stored in the logging tables.
• The difference on Data Wrangling and ETL.

SSAS

General

• SSAS locking improvements in SSAS 2016 to improve locking issues during process commit phases for those companies with real time needs or necessity of processing during business hours.

SSAS vNext

• Have you ever authored a DAX query in SSMS using the MDX editor? With the new DAX Query Editor, you no longer need to do so.
• At the new Tabular 1400 compatibility level, a data source definition can include a native query and even a contextual M expression on top of the connection information, which opens interesting capabilities that didn’t exist previously and redefines to some degree the nature of a data source definition.

DAX

• Get a grip on context transition and expanded tables as they are important concepts in understanding DAX.
• Different techniques to retrieve multiple values from a lookup table in DAX, improving code readability and performance.

SSRS

• Check this video from Guy in a Cube on how to drill down from a mobile report to a paginated report.
• With the latest releases of Report Builder and SQL Server Data Tools – Release Candidate, you now have the ability to create native DAX queries against supported SSAS tabular data models.

Data science

General

• Why investing in data science can pay off big time.
• Here is a series on using Cortana analytics to help organizations transform data into intelligence leveraging machine learning and IoT techniques.
• Here are some nice 5 minute videos, introducing the business of deep learning concepts ranging from translating video speech into text for analysis, face recognition and building a driverless car.
• Understanding the basics of probability.
• The danger of unidentified biases in data science.

Machine learning

• Understanding the KNN (K Nearest Neighbours) concept and using it in R Code and how to use it to do predictions.
• Understanding the concept of market basket analysis and using int in R Code and Power BI.
• An introduction to Azure machine learning.
• The main Azure ML environment and its essential components.

R

• Get an understanding on how neural networks work and how you can train them using R
• Using RevoScaleR package in Power BI
• For the next time you play scrabble, look at this R analysis.
• The mental overhead you’ll need to deal with as you learn base R and the tidyverse packages.

Maintenance

Internals

• Get introduced to PolyBases, the enhancements introduced in 2016 and its different use cases.
• An important note on setting up TempDB correctly on installing SQL Server.
• Some gotchas on moving data from one filegroup to another.

Backup and recovery

• Lessons to be learned from the big Gitlab outage: take care of your backups.
• In SQL Server 2016, several improvements to Backup to URL were made, including the ability to use block blobs in addition to page blobs, and the ability to create striped backups (if using block blobs).
• Here is a script to help and get the status of running backup and restore in SQL Server.
• Backing up SQL Server on Linux using Ola Hallengrens Maintenance Solution.

Storage

• Maintaining a database is an important job of the DBA. One of the many maintenance tasks is ensuring that the disk does not fill up and your files are able to grow over time.

Health Practices

• Check on the most profound reasons for changing the cost parallelism threshold, and why it should not be set to the default of 5.
• As of SQL 2016 it is now possible to store and manipulate JSON objects within the otherwise relational structured environment. Get an overview on various functionalities of using JSON in SQL 2016 and whenever it is appropriate to use JSON within SQL.
• It is always a good idea to do some basic diagnostics whether entering a new environment or having a need to review things. The diagnostic information queries from Glenn Berry might help you out.
• Updating SQL Server Statistics , and part 2
• Introduction to partitioning.
• Switching recovery Models.
• Free Microsoft SQL Server on VMware best practices guide.

Index

• Get a good insight on how bad Bookmark Lookups can be, in relation to performance.
• The good, the bad and the ugly on indexed views and data modifications.
• Why might SQL server choose a non-clustered index over a clustered index and busting the performance myth that a clustered index is by default better.
• How to estimate compression savings with columnstore indexes.

Troubleshooting

• Some free tools from IDERA that might help you out on your quest for finding issues faster.
• Maybe these SQL Server help scripts from Rolf Tesmer can help you out on your day to day Accidental DBA operations.

Infrastructure

• Deployments can be made easier, in many different ways, by using Docker and Windows Containers; but how do you set about working with them?
• Get an insight on some container scenarios for your different Environments.
• Containers, more containers, … CONTAINERS!
• Using SQL Client Aliases to connect to containers that are listening on a custom port.
• Get a grip on Azure Networking

SQL

• Hidden Gem TRY_CONVERT to troubleshoot data type conversion
• Why the No lock hint on CTE s and views is an exception, certainly not a standard.

Security

• Here is a nice script on auditing access to your SQL Servers and finding out who can do harm.
• Always consider in your analysis is the cost of an upgrade versus the cost of a data security breach. New data Privacy and Security Features in SQL 2016.
• SQL Server principals , database roles and permissions, server fixed role permissions
• Tracking user activity using extended events.

Tuning

Internals

• Query Store, what it is, how do you get it running, and what you can use it for.
• Get information on which stat and respective histogram steps cover your predicate, in the scope of the table and column. With the latest release of SQL Server 2016 SP1 CU2, they added a new Dynamic Management Function (DMF) dm_db_stats_histogram, that completes sys.dm_db_stats_properties to help you out.
• Plan caching and its side effects in SQL Server.
• An introduction to Wait statistics in SQL Server and how to group them helping you think in the right direction.
• Explaining some common compute scalar operators in the query plan during inserts operations.
• Get an introduction on how the plan cache works , how to avoid plan cache pollution and how to create plan cache stability, helping memory usage.
• Using I/O statistics and client statistics to help you tune your queries.

Index Tuning

• Get an idea on the negative effects on bookmark lookups and how they can be avoided. In addition understand the tipping point on when SQL server will scan a table ignoring your non clustered index, not using an index seek because bookmark lookups become too expensive.
• Update on why indexes are also important for Update and Delete Queries, reducing the amount of rows that might need to be locked.
• SQL product team has made significant implements in columnstore index functionality, supportability and performance within SQL Server 2016. SQL Server product team has created a large set of blogs across multiple columnstore index scenarios.

In-memory optimization

• Highlighting the fairly wide gap in functionality between clustered columnstore indexes for on-disk tables compared to memory-optimized tables

SQL skilles

• Efficiently retrieving date time difference in different parts (years, months and days, ….)
• The basics of indexing
• Using temporary table structures correctly
• Parsing Denormalized strings using the STRING_SPLIT function SQL Server 2016
• All about indexing Foreign Keys

Learning

Courses

• Dive into azure data lake store with this introduction step by step course.
• One of the most fun things I did last year was getting introduced to BIML and automating SSIS development were possible.
• With the information included in this Deep learning course, the strategic decision maker will be able to connect deep learning with their particular industry and business.

Vids

• Another nice Channel 9 series – SQL Unplugged

General

• Helping you to become fluent in R