Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Expert tabular modeling techniques for building and deploying cutting-edge business analytical reporting solutions
Preview in Mapt

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

Derek Wilson

3 customer reviews
Expert tabular modeling techniques for building and deploying cutting-edge business analytical reporting solutions

Quick links: > What will you learn?> Table of content> Product reviews

eBook
$5.00
RRP $43.99
Save 88%
Print + eBook
$54.99
RRP $54.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$5.00
$54.99
RRP $43.99
RRP $54.99
eBook
Print + eBook

Frequently bought together


Tabular Modeling with SQL Server 2016 Analysis Services Cookbook Book Cover
Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
$ 43.99
$ 5.00
SQL Server 2016 Reporting Services Cookbook Book Cover
SQL Server 2016 Reporting Services Cookbook
$ 43.99
$ 5.00
Buy 2 for $10.00
Save $77.98
Add to Cart

Book Details

ISBN 139781786468611
Paperback372 pages

Book Description

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.

Table of Contents

Chapter 1: Introduction to Microsoft Analysis Services Tabular Mode
Introduction
Importing sample datasets
Understanding basic concepts
Chapter 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
Chapter 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
Chapter 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
Chapter 5: Administration of Tabular Models
Introduction
Managing tabular model properties
Managing perspectives
Managing partitions
Managing roles
Managing server properties
Managing Analysis Services memory
Chapter 6: In-Memory Versus DirectQuery Mode
Introduction
Creating a new DirectQuery project
Configuring DirectQuery table partitions
Testing DirectQuery mode
Chapter 7: Securing Tabular Models
Introduction
Configuring static row-level security
Configuring dynamic filter security
Chapter 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
Chapter 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
Chapter 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
Chapter 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

What You Will Learn

  • Learn all about Tabular services mode and how it speeds up development
  • Build solutions using sample datasets
  • Explore built-in actions and transitions in SSAS 2016
  • Implement row-column, and role-based security in a Tabular Data model
  • Realize the benefits of in-memory and DirectQuery deployment modes
  • Get up to date with the new features added to SQL Server 2016 Analysis Services
  • Optimize Data Models and Relationships Usage

Authors

Table of Contents

Chapter 1: Introduction to Microsoft Analysis Services Tabular Mode
Introduction
Importing sample datasets
Understanding basic concepts
Chapter 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
Chapter 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
Chapter 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
Chapter 5: Administration of Tabular Models
Introduction
Managing tabular model properties
Managing perspectives
Managing partitions
Managing roles
Managing server properties
Managing Analysis Services memory
Chapter 6: In-Memory Versus DirectQuery Mode
Introduction
Creating a new DirectQuery project
Configuring DirectQuery table partitions
Testing DirectQuery mode
Chapter 7: Securing Tabular Models
Introduction
Configuring static row-level security
Configuring dynamic filter security
Chapter 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
Chapter 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
Chapter 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
Chapter 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

Book Details

ISBN 139781786468611
Paperback372 pages
Read More
From 3 reviews

Read More Reviews

Recommended for You

SQL Server 2016 Reporting Services Cookbook Book Cover
SQL Server 2016 Reporting Services Cookbook
$ 43.99
$ 5.00
Expert Cube Development with SSAS Multidimensional Models Book Cover
Expert Cube Development with SSAS Multidimensional Models
$ 29.99
$ 5.00
Learning SQL Server Reporting Services 2012 Book Cover
Learning SQL Server Reporting Services 2012
$ 32.99
$ 5.00
SQL Server Analysis Services 2012 Cube Development Cookbook Book Cover
SQL Server Analysis Services 2012 Cube Development Cookbook
$ 35.99
$ 5.00
SQL Server Analysis Services 2012 Cube Development Cookbook Book Cover
SQL Server Analysis Services 2012 Cube Development Cookbook
$ 35.99
$ 5.00
Data Visualization Solutions for Beginners [Video] Book Cover
Data Visualization Solutions for Beginners [Video]
$ 124.99
$ 5.00