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

Data transformation

The last section explained several very useful techniques of data exploration. In our real-world example, we explored the data and decided that we need to transform it before we continue working with it. In this section, we will make all desired transformations. The most comfortable way of making transformations is to use T-SQL, because all transformations that we need are rather straightforward.

The first of the transformations is just a join of both source tables. This will eliminate records from the SourceData.Actions table that do not have contracts, and it will also create a base for further analytical datasets. The following statement joins both tables together:

select
    contracts.PhoneId
    , contracts.IsCorporate
    , contracts.CitySize
    , actions.ActionRoute
    , actions.DateAndTime
    , actions.Units
    , actions.Unit
    , actions.RecomputeUnits...
Previous Section
End of Section 5
Next Section