Preface

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.

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.

What this book covers?

Chapter 1, Analysis Services in SQL Server 2019, introduces Analysis Services and the steps to install the tools used in the rest of the book.

Chapter 2, Choosing the SQL Server 2019 Analytic Model for Your BI Needs, presents multidimensional and tabular models of Analysis Services and provides reasons to choose between them.

Chapter 3, Preparing Your Data for Multidimensional Models, outlines how to organize data into star schemas to support multidimensional models.

Chapter 4, Building a Multidimensional Cube in SSAS 2019, covers the necessary steps to build the multidimensional model, complete with facts, dimensions, and partitions.

Chapter 5, Adding Measures and Calculations with MDX, illustrates how to expand a multidimensional model with measures and calculations created with MDX and how to query a multidimensional model with MDX.

Chapter 6, Preparing Your Data for Tabular Models, covers the steps required to build a Power Pivot model in Excel and prep data for work with tabular models.

Chapter 7, Building a Tabular Model in SSAS 2019, shows how to build tabular models using standard refresh, Direct Query, and Power Query, and how to import a Power Pivot model to create a tabular model.

Chapter 8, Adding Measures and Calculations with DAX, elucidates the steps to query a model with DAX and expand the model using DAX to create measures, columns, and calculated tables.

Chapter 9, Exploring and Visualizing Your Data with Excel, highlights the use of Excel to visualize the data from the models after the models have been created.

Chapter 10, Creating Interactive Reports and Enhancing Your Models in Power BI, focuses on the use of Power BI to create modern reports using both multidimensional and tabular models.

Chapter 11, Securing Your SSAS Models, engages in imparting knowledge and implementing various patterns to secure multidimensional and tabular models.

Chapter 12, Common Administration and Maintenance Tasks, provides steps to manage models by helping in understanding the backup and restore operations, scaling options, and performance techniques.

To get the most out of this book

This book is designed for those of you who are experienced working with data and analysis in their business. Most readers should have some experience with Excel or Power BI. Analysis Services is the next level of data analysis that uses the power of SQL Server 2019. This book will give you hands-on guidance, from installing tools to the basics of maintenance and operations.

In the first chapter of the book, we will walk through the installation of the SQL Server tools. However, you should plan to get Excel and Power BI Desktop downloaded so that you build reports and visualizations on the models you will create in the later chapters.

If you are using the digital version of this book, we advise you to type the code yourself or access the code via the GitHub repository (link available in the next section). Doing so will help you avoid any potential errors related to copy/pasting of code.

Download the example code files

You can download the example code files for this book from GitHub at https://github.com/PacktPublishing/hands-on-sql-server-2019-analysis-services. In case there's an update to the code, it will be updated on the existing GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!

Download the color images

We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here:https://static.packt-cdn.com/downloads/9781800204768_ColorImages.pdf.

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "In my case, the account name is NT ServiceMSOLAP$DOWSQL2019TAB."

A block of code is set as follows:

USE [Master]

CREATE LOGIN [NT ServiceMSOLAP$DOWSQL2019TAB] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

Bold: Indicates a new term, an important word, or words that you see on screen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Once you have the user in place, choose Impersonate Service Account and click Connect."

Tips or important notes

Appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [email protected].

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in, and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Reviews

Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!

For more information about Packt, please visit packt.com.