Book Image

Hands-On Data Science with SQL Server 2017

By : Marek Chmel, Vladimír Mužný
Book Image

Hands-On Data Science with SQL Server 2017

By: Marek Chmel, Vladimír Mužný

Overview of this book

SQL Server is a relational database management system that enables you to cover end-to-end data science processes using various inbuilt services and features. Hands-On Data Science with SQL Server 2017 starts with an overview of data science with SQL to understand the core tasks in data science. You will learn intermediate-to-advanced level concepts to perform analytical tasks on data using SQL Server. The book has a unique approach, covering best practices, tasks, and challenges to test your abilities at the end of each chapter. You will explore the ins and outs of performing various key tasks such as data collection, cleaning, manipulation, aggregations, and filtering techniques. As you make your way through the chapters, you will turn raw data into actionable insights by wrangling and extracting data from databases using T-SQL. You will get to grips with preparing and presenting data in a meaningful way, using Power BI to reveal hidden patterns. In the concluding chapters, you will work with SQL Server integration services to transform data into a useful format and delve into advanced examples covering machine learning concepts such as predictive analytics using real-world examples. By the end of this book, you will be in a position to handle the growing amounts of data and perform everyday activities that a data science professional performs.

Related Content you might be interested in

Current Title:

Small book image
Hands-On Data Science with SQL Server 2017
Marek Chmel | Vladimír Mužný
Nov, 2018 | 506 pages
Small book image
SQL Server 2019 Administrator's Guide
Vladimir Muzny | Marek Chmel
Sep, 2020 | 522 pages
Small book image
SQL Server 2017 Machine Learning Services with R
Julie Koesmarno | Tomaž Kaštrun
Feb, 2018 | 338 pages
Small book image
SQL Server 2017 Administrator's Guide
Vladimir Muzny | Marek Chmel
Dec, 2017 | 434 pages
Table of Contents (14 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Get in touch
Free Chapter
1
Data Science Overview
Data Science Overview
Introducing data science
Data science domains
Summary
2
SQL Server 2017 as a Data Science Platform
SQL Server 2017 as a Data Science Platform
Technical requirements
SQL Server evolution
SQL Server Services and their use with data science
Summary
3
Data Sources for Analytics
Data Sources for Analytics
Technical requirements
Getting data from databases
Importing flat files
Working with XML data
Working with JSON
External data with PolyBase
Summary
4
Data Transforming and Cleaning with T-SQL
Data Transforming and Cleaning with T-SQL
Technical requirements
The need for data transformation
Database architectures for data transformations
Transforming data
Denormalizing data
Using views and stored procedures
Performance considerations
Summary
Questions
5
Data Exploration and Statistics with T-SQL
Data Exploration and Statistics with T-SQL
Technical requirements
T-SQL aggregate queries
Ranking, framing, and windowing
Running aggregates
Summary
Questions
6
Custom Aggregations on SQL Server
Custom Aggregations on SQL Server
Technical requirements
Overview of SQLCLR
Creating CLR aggregations
Limitations and performance considerations
Summary
Questions
7
Data Visualization
Data Visualization
Technical requirements
Data visualization – preparation phase
Power BI Report Server
SQL Server Reporting Services
Summary
8
Data Transformations with Other Tools
Data Transformations with Other Tools
Technical requirements
Categorization, missing values, and normalization
Using Integration Services for data transformation
Using R for data transformation
Using Data Factory for data transformation
Summary
Questions
9
Predictive Model Training and Evaluation
Predictive Model Training and Evaluation
Technical requirements
Preparing SQL Server
Creating data structures
Deploying, training, and evaluating a predictive model
Summary
Questions
10
Making Predictions
Making Predictions
Technical requirements
Reading models from a database
Submitting values to an external script
Using the PREDICT keyword
Making the predictive model self-training
Summary
Questions
11
Getting It All Together - A Real-World Example
Getting It All Together - A Real-World Example
Technical requirements
Assignment and preparation
Data exploration
Data transformation
Training and using predictive models for estimations
Summary
Questions
12
Next Steps with Data Science and SQL
Next Steps with Data Science and SQL
Data science next steps
Next steps with SQL Server
Data science in the cloud
Summary
13
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think

Using Data Factory for data transformation

So far, we have used on-premise technologies, such as SSIS or R. This short section steps out of an on-premises environment. As a growing amount of data is stored in the cloud, Microsoft introduced a cloud-based technology, Azure Data Factory (ADF), which is a technology intended for the following tasks:

  • Data acquisition from a wide set of data sources, including on-premise data sources (E phase of ETL processes)
  • Data transformations using several languages (T phase of ETL processes)
  • Publishing data for further usage (L phase of ETL processes)

Reviewing the preceding bullet list, we can say that ADF is a cloud-based SSIS. Here, we also define sources of data, data manipulation, and the data storage destination. However, the technology background is completely different and terminology also differs from SSIS.

ADF is provided in two versions...
Previous Section
End of Section 6
Next Section