SQL Spot – Part 6

Summer holidays are coming up: perfect timing to take that moment of peace and quiet you had been waiting for and catch up on some of the most recent SQL updates with our monthly SQL Spot. And once again, our thanks to the neverending support and input of  SQL blogging community!

Business, releases, news and trivia

Business

• How the rise of IoT challenges business everywhere and the importance of being IoT.
• If you are planning or thinking on moving to Azure then it is good to know about DTU’s and here is some thought what it might be in the future.
• VIDS on how block chaining might radically transform our economy. Explained simplified.
• What Power BI Premium is and why you should care about it
• Three ways the cloud can help your business: Grow Faster, Stay Protected, Spark innovation
• What does Power BI Premium mean for you?
• Blockchain has emerged as a significant focus for industry, particularly in areas that have special interests in transaction security.
• The Candidate profile for a data scientist

Releases and news

• A first note on the new feature Graph Databases

Power and Excel BI

Power BI

• Integrate Power BI into Your Application: Part 1 – Register your App
• How to better organize your measures
• Arcgis Maps for Power BI Summit Session
• Planning on Power BI Enterprise Deployment White Paper
• Data Privacy Settings In Power BI/Query, Part 3: The firewall error prevention
• Data Privacy Settings In Power BI/Power Query, Part 2: Preventing Query Execution
• Power BI Report Server Now generally available
• Exposing M code and Query metadata in your Power BI file.
• Creating custom tooltips
• Query Folding And Writing Your Own SQL Queries In Power Query/Power BI/Excel Get & Transform
• 5 visual design best practices
• Optimizing your Power BI data models
• Using Regular Expressions in Power BI leveraging R
• How to dynamically determine distances between two geographic points
• Introducing the DotPlot visualization
• All on Power BI Security
• Filtering Errors in Power BI
• Announcing Data Refresh APIs in the Power BI Service
• How to embed your Power BI dashboards, reports and tiles
• VID on how to Create Power BI Reports in Power BI Server
• VID on How to use multiple URL filters
• VID on Taking Power BI visualization to the next level
• VID on using power flow in power BI to do some real time data monitoring
• VID on Self Service BI and Enterprise Reporting Options
• VID on a custom visual – Rotating Tile
• VID on a custom visual – Route Map
• VID on a custom visual – Infographic Designer
• VID on a custom visual – Enlighten World Flag Slicer
• VID on using Power BI and Microsoft Flow to do real time data monitoring
• VID on how to purchase Power BI Premium
• Lots and lots of really cool new features that I had not seen before at the Power BI Insights Summit
• Power BI Desktop June Feature Summary

Excel BI

• A really useful new feature was added to Get & Transform (the functionality previously known as Power Query) in the latest updates for the Office 365 click-to-run version of Excel 2016: the ability to export and import ODC files containing M queries.
• How to quickly find hard coded values in Excel formulas and fix them to increase management of your Excel files.
• How Pivot Tables and Slicers bring flexibility to your data visualization needs
• Query folding and writing your own SQL in M
• Power Query pivot Swap
• VID on Modern Excel Powerpivot Awareness Campaign, Pilot Episode and Ten Things Data can do you for you.

M and DAX

• SUM and SUMX functions and COUNT and COUNTX functions
• Defining Variables in DAX Queries

DWH

General

• Data Warehouse vs. Data Lake Technology: Different Approaches to Managing Data

On-premise

• Introducing Microsoft Data Warehouse Fast Track for SQL Server 2016 and Data Warehouse Fast Track Reference Guide for SQL Server 2016

Azure SQL DWH

• Identity feature now available in SQL Data Warehouse

SSIS

• Deleting a package from the SSIS Catalog is still a pain.
• Download JSON Data with SSIS
• Developing Metadata Design Patterns in BIML
• SSIS Scale Out is available in SQL Server vNext CTP1
• Check out this ETL best practices series
• Cases of poor metadata hygiene
• Retrieve A List Of Files From FTP Using SSIS

SSAS

General

• Automated partitioning for SSAS tabular and SSAS Azure using TOM (tabular object model) available in SQL 2016.

Azure AS

• Automated partitioning for SSAS tabular and SSAS Azure using TOM (tabular object model) available in SQL 2016.

SSRS

• Powershell commands for reporting services

Data science

General

• The Data Science Virtual Machine (DSVM) is a customized VM image on Microsoft’s Azure cloud built specifically for doing data science.
• Introducing Azure Cosmos DB
• The art of data visualization

Machine learning, AI, Deep learning

• Machine Learning Prediction Scenario
• Microsoft Machine Learning Services within SQL Server 2017. A walkthrough on how it comes to be.
• Myths and Realities of Deep Learning and why we will all use it
• Data Science Walkthrough with SQL Server 2017 and Microsoft Machine Learning Services
• Azure Machine Learning Part-8 Tune parameters
• Azure Machine Learning Part 9 Cross Validation

R, Python, JSON,…

• There is usually more than one way in R
• SQL Server R Services: The basics
• Real time predictions with Microsoft R Server
• Plot SQL Server CPU Usage With R
• Interfacing with APIs using R: the basics

Maintenance

Internals

• Side effects of queries that only read data and never perform data changes
• Endpoint Owners back to basics
• Thought: What if SQL Server could adapt?

Monitoring and auditing

• SQL Azure Blob Auditing Basic Power BI Dashboard
• SQL Server Diagnostics (Preview)
• Monitoring Azure Cloud and Hybrid Environments
• The quick way to check for failed agent jobs
• Handling SQL Server Deadlocks With Event Notifications

Backup and recovery

• What’s new with differential backups in SQL Server 2017
• Doing a Point-in-Time Restore in Azure SQL DB
• The Three T’s of backups

Storage

• SQL Server 2016 has a new configuration to control the auto-growth of multiple files in the same filegroup
• Data Lake Details: what is it, why use it
• Renaming database files

Index

• Columnstore Indexes Performance Counters

Health Practices

• What a delete, detach and offline operation does and when to use which
• All on Databases and DevOps
• Does Separating Data and Log Files Make Your Server More Reliable?

Infrastructure

• Azure SQL databases in logical servers, elastic pools, and managed instances

SQL

• Rolling back temporal table data after a wrong insertion.
• Having the results of a stored procedure into an undefined temp table…
• What’s wrong with delete exists
• New T-SQL functions in SQL 2017

Security and quality

• Data Privacy settings in Power BI Query
• All on Power BI Security

Tuning

Internals

• Why Your Biggest Query Plans Don’t Show Up in Some DMVs
• The query store gives us a novel way of identifying those queries that are causing performance problems when they are parameterized by SQL Server for reuse
• The curious case of plan regression
• Trivial optimized Execution plans, simple parameterization, and ColumnStore indexes being executed in RowStore mode.
• Graph Database with T-SQL in 2017

Indexing

• To Cluster or non-cluster columnstore indexes
• What’s an index and how do they work exactly
• Finding tables that can benefit from Clustered Columnstore Index

In-memory optimization

• Testing DML statements in in-memory OLTP

Health practices

• Automated tuning and the future of performance tuners

Language

• Aliasing your tables and odd performance problems
• Use WITH clause in OPENJSON to improve parsing performance
• Using the REVERSE function and when to use it

Career and learning

Thoughts

• What can you do with a career in data science?
• Occupational Hazard – Is Your Job at Risk of Automation?

Courses

• Vids: Microsoft Power BI Insights Summit
• Books: free E-book on R in Power BI