Analytic SQL in SQL Server 2014/2016

Riadh Ghlala

• Publisher: Wiley-ISTE
• Year: 2019
• Print ISBN: 9781786304124
• eBook ISBN: 9781119649496
• Edition: 1
• Topic: Mathematics
• Subtopic: Probability & Statistics
• Index: Mathematics

Business Intelligence (BI) has emerged as a field which seeks to support managers in decision-making. It encompasses the techniques, methods and tools for conducting analytically-based IT solutions, which are referred to as OLAP (OnLine Analytical Processing). Within this field, SQL has a role as a leader and is continuously evolving to cover both transactional and analytical data management. This book discusses the functions provided by Microsoft® SQL Server 2014/2016 in terms of business intelligence. The analytic functions are considered as an enrichment of the SQL language. They combine a series of practical functions to answer complex analysis requests with all the simplicity, elegance and acquired performance of the SQL language. Drawing on the wide experience of the author in teaching and research, as well as insights from contacts in the industry, this book focuses on the issues and difficulties faced by academics (students and teachers) and professionals engaged in data analysis with the SQL Server 2014/2016 database management system.

1
Data Analysis Fundamentals with the SQL Language

1.1. Data at the heart of the information system

1.1.1. Introduction

The development of IT solutions becomes more complicated from one day to the next due to the ever-increasing complexity of information systems and the need to align business needs with these IT solutions. In every generation of information system, data remains the cornerstone of these IT solutions. This importance is due to the static aspect of data in real-world modeling and the ability to provide any processing requested by application developers to meet the needs of the business logic layer.

Figure 1.1 shows the position of data in the urbanization of information systems according to the Enterprise Architecture (EA) vision¹. This model puts two of the components on equal footing: data and applications.

Indeed, the role of data in meeting the needs of the business logic layer is essential, whether through the applications component or by direct access to the data component by means of a query language such as SQL.

Figure 1.1. Enterprise architecture

1.1.2. Mind map of the first section

This first section will be devoted to introducing the basics of databases, the SQL language and Microsoft SQL Server DBMS.

Figure 1.2 presents a mind map of the different concepts that will be covered in this section.

1.1.3. Concept of a database

A database (referred to as DB) is a collection of homogeneous data relating to a real-world domain (information system), stored in a structured way and with as little redundancy as possible to facilitate and accelerate access to these data later on.

These data must be accessible by different users, possibly simultaneously. Thus, the notion of database is generally coupled with that of computer networks in order to be able to share this information.

Figure 1.2. Mind map of the first section

The engineering process of a database goes through three essential steps: design, generation of the database schema in an appropriate formalization (in our case, the relational model) and finally implementation of this database.

Figure 1.3. Database engineering process

1.1.4. Database Management System (DBMS)

Database management consists of the implementation, operation and administration of the database. These tasks are performed by software using a set of commands written in a query language called SQL.

The range of DBMSs is very diverse. The choice of a DBMS is made according to several criteria: functionalities provided, cost, performance, security, etc.

Figure 1.4. Features of a DBMS

Figure 1.5. The main relational DBMSs

1.1.5. The relational model

In 1970, Edgar F. Codd, a researcher at IBM...

Table of contents

• Cover
• Table of Contents
• Introduction
• 1 Data Analysis Fundamentals with the SQL Language
• 2 Queries
• 3 Operators
• 4 Functions