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

Importing sample datasets

For the examples in this chapter you will import two sets of data to create tables inside the model. This first table is a list of all states in the United States plus the District of Columbia. The second data set is a short list of famous landmarks.

Note

These examples are available at https://github.com/derekewilson/SSAS_2016_Tabular_Model.

Getting ready

This example assumes you have a working tabular mode server and SQL Server Data Tools installed.

How to do it...

Open Visual Studio, select File and then New Project.
On the next screen select Analysis Services Tabular Project to create a new Analysis Service tabular project.
Select Model from the menu and Import from Data Source.
Select Excel File from the bottom of the Table Import Wizard and click Next.
Browse to the location of the US States.xlsx file. Check the Use first row as column headers box.
Select the Service Account to specify a user that has access to the data source. Click Next.
Review the Source Table information and click Finish.
Click Close after successfully importing the data.
Now repeat steps 4-8 and import into the data the Famous Landmarks.csv file.

How it works...

Let's review what was done in the previous steps for this first recipe. In steps 1 and 2 we created a new Tabular Model project and selected the option to import data. Then in steps 3 and 4 we selected the source data type of Excel and chose the file to import that included a list of the States in the USA. During step 5 we selected how the data source can be accessed via user security. In step 6, we reviewed the import process and started loading the data. In step 7 we were able to see that the data was successfully imported. Then you repeated the process to import the Famous Landmarks file. You now have two data tables loaded into your model that Tabular mode can use.

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

Importing sample datasets

Note

Getting ready

How to do it...

How it works...