Hands-On SQL Server 2019 Analysis Services

Design and query tabular and multi-dimensional models using Microsoft's SQL Server Analysis Services

Steven Hughes

• Publisher: Packt Publishing
• Year: 2020
• ISBN:
  9781800201637
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Processing
• Index: Computer Science

Get up to speed with the new features added to Microsoft SQL Server 2019 Analysis Services and create models to support your business

Key Features

• Explore tips and tricks to design, develop, and optimize end-to-end data analytics solutions using Microsoft's technologies
• Learn tabular modeling and multi-dimensional cube design development using real-world examples
• Implement Analysis Services to help you make productive business decisions

Book Description

SQL Server Analysis Services (SSAS) continues to be a leading enterprise-scale toolset, enabling customers to deliver data and analytics across large datasets with great performance. This book will help you understand MS SQL Server 2019's new features and improvements, especially when it comes to SSAS.

First, you'll cover a quick overview of SQL Server 2019, learn how to choose the right analytical model to use, and understand their key differences. You'll then explore how to create a multi-dimensional model with SSAS and expand on that model with MDX. Next, you'll create and deploy a tabular model using Microsoft Visual Studio and Management Studio. You'll learn when and how to use both tabular and multi-dimensional model types, how to deploy and configure your servers to support them, and design principles that are relevant to each model. The book comes packed with tips and tricks to build measures, optimize your design, and interact with models using Excel and Power BI. All this will help you visualize data to gain useful insights and make better decisions. Finally, you'll discover practices and tools for securing and maintaining your models once they are deployed.

By the end of this MS SQL Server book, you'll be able to choose the right model and build and deploy it to support the analytical needs of your business.

What you will learn

• Determine the best analytical model using SSAS
• Cover the core aspects involved in MDX, including writing your first query
• Implement calculated tables and calculation groups (new in version 2019) in DAX
• Create and deploy tabular and multi-dimensional models on SQL 2019
• Connect and create data visualizations using Excel and Power BI
• Implement row-level and other data security methods with tabular and multi-dimensional models
• Explore essential concepts and techniques to scale, manage, and optimize your SSAS solutions

Who this book is for

This Microsoft SQL Server book is for BI professionals and data analysts who are looking for a practical guide to creating and maintaining tabular and multi-dimensional models using SQL Server 2019 Analysis Services. A basic working knowledge of BI solutions such as Power BI and database querying is required.

Section 1: Choosing Your Model

We kick off the book with an introduction to SQL Server 2019 and the process to install and configure the data engine, the Analysis Services engines, and the sample databases. When starting to work with SQL Server Analysis Services (SSAS), the developer or architect needs to choose the proper model to use. This section will clarify what SSAS is and the key differences between the models.

This section comprises the following chapters:

• Chapter 1, Analysis Services in SQL Server 2019
• Chapter 2, Choosing the SQL Server 2019 Analytic Model for Your BI Needs

Chapter 1: Analysis Services in SQL Server 2019

As you prepare to build your analytic models in SQL Server, you need to understand the basics about SQL Server Analysis Services (SSAS) including the purpose of the overall platform, with a basic understanding of the product. We will be exploring the origin of SSAS and its evolution into what we use today.

Upon completion of this chapter, you should understand where SSAS fits into the overall data analytics ecosystem. In this chapter, we're going to answer the following key questions about Analysis Services:

• What is SQL Server Analysis Services anyway?
• Why use SQL Server Analysis Services?
• What's new in SQL Server Analysis Services 2019?
• What are the tools used with SQL Server Analysis Services?
• One last thing – our sample data

In preparation for the rest of the book, the final section of the chapter discusses the tools we use. We will provide links and instructions for the installation procedures to prepare for the various examples and development used throughout the book. We will also walk through the restoration of the SQL Server database used to support our Analysis Services examples in later chapters.

What is SQL Server Analysis Services anyway?

SSAS is distributed as part of the SQL Server stack of tools. This stack has included a variety of tools over the years:

• SQL Server Management Studio
• Data Transformation Services
• SQL Server Integration Services
• SQL Server Reporting Services
• Data Quality Services
• Master Data Services
• Data Virtualization with PolyBase
• Big Data Clusters

Some of these options have been part of SQL Server for years, such as Integration Services, but some are brand new to SQL Server 2019, such as Big Data Clusters. Analysis Services has been in the product line for a long time, having been added in 1998.

SQL Server Analysis Services is not SQL Server

This statement may seem odd, but it is important to understand the place that Analysis Services has in the SQL Server stack and related Microsoft Business Intelligence (MSBI) ecosystem. As the list earlier in the chapter calls out, Analysis Services and many other products have been included in the purchase of SQL Server but are not a relational database management system (RDBMS).

The only component of SQL Server that has no official title is the relational data engine. It is simply referred to as SQL Server. SQL Server directly refers to the capability to store data in tables and use Transact-SQL or TSQL to interact with the data. Relationships, indexes, views, and stored procedures can be used in this engine and are commonly used for transactional systems and data warehouse solutions.

Analysis Services is designed to optimize data for analysis and reporting. Relational systems specialize in managing large amounts of transactions with good performance. Analytic solutions such as Analysis Services are designed to aggregate and query large amounts of data efficiently. While design methodologies such as star schemas are designed to optimize relational systems for analytic workloads, these schemas still require significant optimization within relational systems to match the performance of analytic solutions.

Relational versus analytic workloads

Relational workloads are typically normalized in relational database systems. Normalization involves using a lot of related tables to keep the data changes to a minimum. They are optimized to load data. Analytic workloads are denormalized using large flat tables with minimal relationships. This keeps the work of reading the data to a minimum.

The key takeaway here is that SQL Server refers to the relational database engine. Analysis Services is a separate data storage solution that is optimized for analytic and reporting workloads.

SQL Server Analysis Services through the years

SSAS has a long and interesting history. Personally, I started working with Analysis Services with its first release in 1998. Microsoft did not reinvent the wheel; they acquired another company to accelerate their introduction into analytic server tools or Online Analytical Processing (OLAP) servers. They started the process in 1996 and acquired Panorama Software's development team to begin the development of their new OLAP server product called OLAP Services 7.0, which was shipped with SQL Server 7.0 in 1998.

OLAP Services was a multidimensional database solution. Microsoft rebranded this to Analysis Services with its SQL Server 2000 release. They made significant changes to the multidimensional server and supporting technology in the 2005, 2008, and 2012 releases. The multidimensional server was designed to work with large-scale data on spinning disks. As hardware continued to improve, optimizations for the platform changed and we saw the shift to more memory-optimized solutions.

Enter Power Pivot with SQL Server 2008 R2 and Excel 2010. This is a significant turning point in the Analysis Services story. The Vertipaq compression engine was introduced to the MSBI set of offerings. While technically a part of SSAS, Power Pivot was released to Excel first. This columnar-based in-memory solution laid the groundwork for tabular models in Analysis Services, which is now the preferred option for working with Analysis Services.

Check out the following timeline. It illustrates the key points in the history of Analysis Services and related technologies:

Figure 1.1 – Over 20 years of Microsoft Analysis Services history

Microsoft has focused most of its attention on the tabular model technology in the most recent releases of Analysis Services. This technology is the heart of the Power BI products and is the only model type supported as a native Azure technology.

We will walk through the value of both types of models in Chapter 2, Choosing the SQL Server 2019 Analytic Model for Your BI Needs. Let's take a step back and look at why you would con...