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

Using ALL to calculate a percentage

In this recipe, you will use the ALL function in the denominator of a percentage calculation. This ensures that you see all records in the calculation you are performing.

Getting ready

Complete the steps in the Using ALL to remove filters recipe to create the initial calculation and understand how ALL ignores any filters.

How to do it...

On the CRASH_DATA_T table in the Grid view, select an empty cell under the CASENUMBER measure.

Enter the calculation in the formula bar:

        Pct_of_Crashes:=  
          COUNT(CRASH_DATA_T[CASENUMBER])/ 
        CALCULATE ( 
        COUNT(CRASH_DATA_T[CASENUMBER]), 
        ALL(CRASH_DATA_T) )

Once you have done this, hit Enter.
The result returns a 1. To change the format to a percentage, edit the Format in the Properties window to Percentage.
The result will now show 100.00 %.
Deploy the model to the server by selecting the Build menu and the Deploy Chapter_9_DAX.
Switch to SQL Server Management...

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

Using ALL to calculate a percentage

Getting ready

How to do it...