SQL Spot – Part 4

It is all about the data these days, and that is exciting. Let’s just be clear about the reality of things. The increasing growth of data technologies is on an all-time high. Yet with our SQL Spot we try to gather some intel for all SQL Server enthusiasts, to feed you professionally or just your common hungry personal interest. The community information gathered addresses a healthy BI data science perspective, ranging from business to technical insights on various topics: SQL Server News, Power BI, SQL Server BI stack, features, Data Science topics and Accidental DBA stuff focused on basic maintenance and tuning advise. We’ll finish with some interesting learning or study materials.

All rights go out the great blogging community.

Business, releases, news and trivia

Business

• Microsoft’s marketing material is now officially referring Vnext as SQL Server 2017
• Microsoft data amp event announcements
• More on new features in SQL Server 2017
• All about costs, choosing processors and licensing….
• 5 Rules for successful self-service analytics
• 3 reasons why data scientist remains the top job in America
• Is a cloud first approach the way to go for your organization ?
• What Is the Proper Role of Consultants in Agile / Self-Service BI?
• Read how video analytics and security system integration come into play when we dig deeper into big data across multiple sources to quickly extract critical event details or detect trends over a specific period of time.
• Preparing for a new era in privacy regulation, The General Data Protection Regulation (GDPR). For those impacted by GDPR, a newly fresh available whitepaper by ‘MSFT. Also check out this article on what you need to know.
• Utilizing real-time streaming tools, such as the Azure IoT Hub or Event Hub, can give organizations quick insights into their data and allow for more accurate and quick decision making.
• 5 ways Microsoft helps you do amazing things in the Cloud

Releases and news

• Download and start playing SQL Server 2017 CTP 2.0
• SQL Updates Newsletter – March 2017
• SQL Server 2014 SP2 Cumulative Update 5
• SQL Server 2017 to add Python Support

Power and Excel BI

General

• The Importance of finding, understanding and trusting the data in thriving towards an insight driven organization

Power BI

• Power BI Mobile apps feature summary – March 2017
• Power BI Desktop feature summary – April 2017
• Manually retrieve information regarding Power BI activities and follow up on your Power BI return of investment (ROI)
• Have more charts by Writing R in Power BI
• One Data Set, Multiple Reports; To a centralized Power BI model multiple developers environment.
• Demonstrating the flexibility and enhanced functionality that becomes available by using R within Power BI
• 3 Helpful Power BI Features You Might Not be Using
• Embed PowerApps in Power BI dashboards or your website
• Arcgis Maps in Power BI, simple to use, a lot of insight.
• Announcing the new Power BI Video Gallery
• The Network Navigator Power BI Custom Visual will help you find links between different attributes in a dataset.
• Explaining the Power BI Admin Role (VID)
• Dynamic Filtering with Power BI (VID)

Excel BI

• How to Calculate Correlation in Excel with the Data Analysis ToolPac

M

• How to Calculate Correlation in Excel with the Data Analysis ToolPac

DWH

General

• What is the place of the traditional DWH in today’s fast growing industry of live data streams, technical advances, cloud services and more…
• Dimensional Models in the big data Era

SSIS

• Help, I need to dynamically unpivot data within the Data Flow Task. How do I solve this within SSIS? This problem is also known as the dynamic number of input columns problem.
• Data Wrangling Concepts: Latency and Staleness
• SSISDB Reporting with Power BI

SSAS

General

• Whitepaper: DirectQuery in Analysis Services 2016
• If you’re building reports in Power BI against SSAS Multidimensional cubes then you may have encountered situations where the formatting on your measures disappears.

Tabular

• Using and Optimizing DirectQuery in Power BI and SSAS Tabular

DAX

• The Unichar() Function And How To Use It In Measures For Data Visualisation
• How DAX resolves column and measure names, providing best practices to avoid conflicts.
• Filtering tables in DAX

SSRS

• SSRS SQL Server Performance Dashboards. Follow up SQL Server Performance Baselining Reports for Enterprise Monitoring

Data science

General

• Fact and fiction when it comes to non-relational data modeling in big data and NoSQL scenarios
• Two very important statistical concepts explained – the sampling distribution and central limit theorem

Machine learning, AI, Deep learning

• AI, Deep Learning, and Financial Services
• Artificial Intelligence, Machine Learning and Deep learning defined
• Machine Learning and Data Transformation in R
• Real-time data science, machine learning, and exploration over globally distributed data in Azure DocumentDB with APACHE Spark
• It is important that a chosen Machine Learning algorithm is able to mimic the actual behaviour of data. Over Fitting and Under Fitting your machine learning model.

Azure Data Lake Analytics

• U-SQL Defined

R, Python, JSON,…

• Using Json in SQL
• Using SQL Server JSON for Change Tracking
• Normal approximation to binomial distribution using T-SQL and R

Cognitive Services

• How we can use Microsoft Cognitive Services to run a sentiment analysis on text data
• Power BI and Microsoft Cognitive Services to extract Key Phrases from Text
• Making R functions easy to use for DBA’s in a Stored Procedure.

Maintenance

Internals

• All about Collations
• The surprising behavior of trailing spaces
• Temporal Tables – What are they and how do you set one up?
• Performance Myths : Table variables are always in-memory ?
• What causes Lock Escalation ?
• Will Optimize for adhoc workloads option save memory?

Backup and recovery

• Whether it’s possible to attach or restore a database to an earlier version of SQL Server ?
• Transaction log growth during BACKUP

Storage

• Working with the Azure BLOB Storage Service

Health Practices

• How to set up statistics maintenancefor your Azure databases using Azure Automation. And how to generate an alert when things go south.
• Learn how to use Brent Ozar’s SP_Blitz solution to check the health of your system and get a prioritized to do list.

Infrastructure

• Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc
• Creating an Azure Runbook to pause, schedule and resume your Azure data.

SQL

• Using DDL Triggers to log what is happening to your Database
• Using Extended properties to hide tables.
• Querying the SQL Server Error Log

Security

• Azure Threat Detection provides a new layer of security, which enables customers to detect and respond to potential threats as they occur by providing security alerts on anomalous activities.
• Improve the management of sensitive data and synthesize more realistic data. Six different approaches to data masking.
• Power BI and Data Security – Sharing Data
• Power BI and Data Security – Row Level Security

Tuning

Internals

• When you’re working with query plans and see a filter, it could very well be there for a good reason
• Query Hash Values, Plan Guides and the Query Store
• Does a TempDB spill mean statistics are out of date?
• A look into selectivity and cardinality estimation for predicates on COUNT(*) expressions
• For SQL Server 2017 and Azure SQL Database, the Microsoft Query Processing team is introducing a new set of adaptive query processing improvements to help fix performance issues that are due to inaccurate cardinality estimates. Improvements in the adaptive query processing space include batch mode memory grant feedback, batch mode adaptive joins, and interleaved execution. In this post, we’ll introduce batch mode adaptive joins.
• What are Dark Queries and how to find them ?

Health practices

• SQL SERVER – 5 Tips to Optimize SQL Server Application Performance
• Tips on IO troubleshooting

Index Tuning

• Today’s SQL Server indexes for the better developer
• ColumnStore, ColumnStore, ColumnStore !!!
• Is it ever worth adding indexes to table variables?
• When Should you Add an Index?
• Can index maintenance hurt performance? (VID)
• Is it really bad statistics that is hurting your performance? (VID)

SQL skills

• Different ways to replace ISNULL() in a WHERE clause that uses only literal values?
• Ordered Set Functions in SQL 2017
• There are several ways to calculate multiple counts in a single query, but which is fastest?

Career and learning

Courses

• edX: SQL Server Analysis Services: Developing a Tabular Data Model

Vids

• Get rid of those T-SQL Bad Habits

General

• Take a step back and look at your career once in a while