9. DAX Syntax and Calculations | Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Book Overview & Buying
Table Of Contents

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By : Derek Wilson

3.7 (3)

Buy this Book

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

3.7 (3)

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.

Preface

What this book covers

What you need for this book

Who this book is for

Sections

Conventions

Reader feedback

Customer support

Free Chapter

1. Introduction to Microsoft Analysis Services Tabular Mode

Introduction

Importing sample datasets

Understanding basic concepts

2. 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

3. 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

4. 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

5. Administration of Tabular Models

Introduction

Managing tabular model properties

Managing perspectives

Managing partitions

Managing roles

Managing server properties

Managing Analysis Services memory

6. In-Memory Versus DirectQuery Mode

Introduction

Creating a new DirectQuery project

Configuring DirectQuery table partitions

Testing DirectQuery mode

7. Securing Tabular Models

Introduction

Configuring static row-level security

Configuring dynamic filter security

8. 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

9. 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

10. 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

11. 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

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

Using ALL to remove filters

How to do it...

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access