Book Image

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By : Derek Wilson
Book Image

Tabular Modeling with SQL Server 2016 Analysis Services Cookbook

By: Derek Wilson

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.

Related Content you might be interested in

Current Title:

Small book image
Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
Derek Wilson
Jan, 2017 | 372 pages
Small book image
Hands-On SQL Server 2019 Analysis Services
Steve Hughes
Oct, 2020 | 474 pages
Small book image
Hands-On Business Intelligence with DAX
Ian Horne
Jan, 2020 | 402 pages
Small book image
Data Modeling with Microsoft Excel
Bernard Obeng Boateng
Nov, 2023 | 316 pages
Table of Contents (18 chapters)
Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
Tabular Modeling with SQL Server 2016 Analysis Services Cookbook
Credits
Credits
About the Author
About the Author
About the Reviewer
About the Reviewer
www.PacktPub.com
www.PacktPub.com
Customer Feedback
Customer Feedback
Preface
Preface
Free Chapter
1
Introduction to Microsoft Analysis Services Tabular Mode
Introduction to Microsoft Analysis Services Tabular Mode
Introduction
Importing sample datasets
Understanding basic concepts
2
Setting up a Tabular Mode Environment
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
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
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
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
In-Memory Versus DirectQuery Mode
Introduction
Creating a new DirectQuery project
Configuring DirectQuery table partitions
Testing DirectQuery mode
7
Securing Tabular Models
Securing Tabular Models
Introduction
Configuring static row-level security
Configuring dynamic filter security
8
Combining Tabular Models with Excel
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
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
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
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

Modeling data with Power Pivot

Getting ready

Follow the steps in the Importing data with Power Pivot recipe to get your Excel environment ready.

How to do it...

  1. Click on the Manage icon from the Power Pivot menu button to bring up the data you just loaded.

  2. Click on a blank cell to create a new measure called Count_of_Crashes, Count_of_Crashs:=COUNT([CASENUMBER]), and then press Enter.

  3. You will now have a new measure added to your model.

  4. Next switch to the Diagram View in the upper right corner to show the tables loaded in the model.

  5. Now you need to create relationships between the tables to enable DAX calculations to work properly. Drag the LIGHT column from LIGHT_T to the LIGHT column in CRASH_DATA_T. Then do the same for WEATHER, from WEATHER_T to WEATHER in the CRASH_DATA_T table to create the relationships.

  6. Next create a Pivot Table chart to show the data. Select PivotTable, then Two Charts (Horizontal), and then select New Worksheet on the next window.

  7. Finally, you are ready to create...

Previous Section
End of Section 9
Next Section