Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By : Derek Wilson

Buy this Book

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By: Derek Wilson

Buy this Book

Overview of this book

SQL Server Analysis Service (SSAS) has been widely used across multiple businesses to build smart online analytical reporting solutions. It includes two different types of modeling for analysis services: Tabular and Multi Dimensional. This book covers Tabular modeling, which uses tables and relationships with a fast in-memory engine to provide state of the art compression algorithms and query performance. The book begins by quickly taking you through the concepts required to model tabular data and set up the necessary tools and services. As you learn to create tabular models using tools such as Excel and Power View, you’ll be shown various strategies to deploy your model on the server and choose a query mode (In-memory or DirectQuery) that best suits your reporting needs. You’ll also learn how to implement key and newly introduced DAX functions to create calculated columns and measures for your model data. Last but not least, you’ll be shown techniques that will help you administer and secure your BI implementation along with some widely used tips and tricks to optimize your reporting solution. By the end of this book, you’ll have gained hands-on experience with the powerful new features that have been added to Tabular models in SSAS 2016 and you’ll be able to improve user satisfaction with faster reports and analytical queries.

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Credits

About the Author

About the Reviewer

www.PacktPub.com

Customer Feedback

Preface

Free Chapter

Introduction to Microsoft Analysis Services Tabular Mode

Introduction

Importing sample datasets

Understanding basic concepts

Setting up a Tabular Mode Environment

Introduction

Installing and configuring a development environment

Installing Visual Studio 2015

Installing SQL Server Data Tools (SSDT)

Interacting with SQL Server Data Tools

Configuring a workspace server

Configuring SSAS project properties

Tabular Model Building

Introduction

Adding new data to a tabular model

Adding a calculated column

Adding a measure to a tabular model

Changing model views

Renaming columns

Defining a date table

Creating hierarchies

Understanding and building relationships

Creating and organizing display folders

Deploying your first model

Browsing your model with SQL Server Management Studio

Browsing your model with Microsoft Excel

Working in Tabular Models

Introduction

Opening an existing model

Importing data

Modifying model relationships

Modifying model measures

Modifying model columns

Modifying model hierarchies

Creating a calculated table

Creating key performance indicators (KPIs)

Modifying key performance indicators (KPIs)

Deploying a modified model

Administration of Tabular Models

Introduction

Managing tabular model properties

Managing perspectives

Managing partitions

Managing roles

Managing server properties

Managing Analysis Services memory

In-Memory Versus DirectQuery Mode

Introduction

Creating a new DirectQuery project

Configuring DirectQuery table partitions

Testing DirectQuery mode

Securing Tabular Models

Introduction

Configuring static row-level security

Configuring dynamic filter security

Combining Tabular Models with Excel

Introduction

Using Analyze in Excel from SSMS

Connecting to Excel from SQL Server Data Tools

Using PivotTables with tabular data

Using the timeline filter with pivot tables

Analyzing data with Power View

Importing data with Power Pivot

Modeling data with Power Pivot

Adding data to Power Pivot

Moving Power Pivot to SSAS via Management Studio

Moving Power Pivot to SSAS via SQL Server Data Tools

DAX Syntax and Calculations

Introduction

Understanding DAX formulas

Using the AutoSum measure in Visual Studio

Creating calculated measures

Creating calculated columns

Using the IF function

Using the AND function

Using the SWITCH function

Using the CONCATENATE function

Using the LEFT Function

Using the RELATED function

Using the RELATEDTABLE function

Using EVALUATE in DAX queries

Filtering based on a value

Filtering a related table

Using ALL to remove filters

Using ALL to calculate a percentage

Using the SUMMARIZE function

Adding columns to the SUMMARIZE function

Using ROLLUP with the SUMMARIZE function

Working with Dates and Time Intelligence

Introduction

Creating a date table in Visual Studio

Using the CALENDAR function

Modifying the date table with the YEAR function

Modifying the date table to include month data

Using the NOW and TODAY functions

Using the DATEDIFF function

Using the WEEKDAY function

Using the FIRSTDATE function

Using the PARALLELPERIOD function

Calculating Year over Year Growth

Using the OPENINGBALANCEMONTH function

Using the OPENINGBALANCEYEAR function

Using the CLOSINGBALANCEMONTH function

Using the CLOSINGBALANCEYEAR function

Using the TOTALYTD function

Using Power BI for Analysis

Introduction

Getting started with Power BI desktop

Adding data to Power BI reports

Visualizing the crash data with Power BI

Editing visualization properties in Power BI

Adding additional visualizations to Power BI

Adding a slicer to Power BI

Using analytics in Power BI

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Testing DirectQuery mode

By running an SQL Server profiler trace, you can see exactly what is happening when a DirectQuery mode query is executed. In this recipe, you will use SQL Server Management Studio to execute a query and trace the results:

Connect to your model using SQL Server Management Studio.
Drag SURFACE_CONDITION, MAJOR_CAUSE, and Count_of_Crashes to the query window:

How it works...

Since the QueryMode property is set to DirectQuery, the tabular engine is accessing the data from the SQL Server Engine tables. The actual query can be seen when you use SQL Server Profiler to trace the query on the SQL Server Database. In this instance, the query results are sent back to SSAS tabular mode and then presented to the end user tool:

As you can see, the tables being queried are the base tables, and they are being accessed using T-SQL syntax not DAX or MDX.

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By : Derek Wilson

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By: Derek Wilson

Overview of this book

Related Content you might be interested in

Current Title:

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Hands-On SQL Server 2019 Analysis Services

Hands-On Business Intelligence with DAX

Data Modeling with Microsoft Excel

Testing DirectQuery mode

How it works...