Hands-On SQL Server 2019 Analysis Services: Design and query tabular and multi-dimensional models using Microsoft's SQL Server Analysis Services

Cover
Title Page
Copyright and Credits
Dedication
About Packt
Foreword
Contributors
Table of Contents
Preface
Section 1: Choosing Your Model
Chapter 01: Analysis Services in SQL Server 2019
	What is SQL Server Analysis Services anyway?
		SQL Server Analysis Services is not SQL Server
		SQL Server Analysis Services through the years
	Why use SQL Server Analysis Services?
		Optimized for reporting and analytics
		Works great with Excel
		Organized with end users in mind
	What\'s new in SQL Server Analysis Services 2019?
		Multidimensional models in 2019
		Tabular models in 2019
	What are the tools used with SQL Server Analysis Services?
		SQL Server 2019 Developer edition
		SQL Server Management Studio (SSMS)
		Visual Studio with SQL Server Data Tools (SSDT)
	One last thing – our sample data
		Restoring the data warehouse backup
	Summary
Chapter 02: Choosing the SQL Server 2019 Analytic Model for Your BI Needs
	Technical requirements
	Understanding how we got here – two modes
	Discovering multidimensional model strengths and challenges
		Strengths of the multidimensional model
		Multidimensional model challenges
	Discovering tabular model strengths and challenges
		Strengths of tabular models
		Challenges with tabular models
	Understanding other differences that matter
		Partitioning
		Role-playing dimensions
		OLAP versus relational concepts
		Hardware requirements
	Choosing the model type for business-specific reasons
		Rapid development and change
		Cloud readiness
		Complex analysis
		Client tools
	Summary
	Further reading
Section 2: Building and Deploying a Multidimensional Model
Chapter 03: Preparing Your Data for Multidimensional Models
	Technical requirements
	A short primer on dimensional modeling
		Understanding the origin of dimensional modeling
		Defining dimensional modeling terms
		Key dimensional modeling concepts
		Common issues in relational dimensional models
		Planning dimensions and facts
	Designing and building dimensions and facts
		Column names – business-friendly or designer-friendly?
		Dimension tables
		Fact tables
		Indexing strategies
	Loading data into your star schema
		Staging your data
		SQL Server data loading methods and tools
	Using database views and data source views
		Data source views – pros and cons
		Database views as an interface layer
	Prepping our database for the multidimensional model
		Wide World Importers Sales
		Creating the views for the multidimensional model
	Summary
Chapter 04: Building a Multidimensional Cube in SSAS 2019
	Technical requirements
	Creating the Analysis Services project in Visual Studio
		Adding the SQL Server database connection to the project
		Adding the DSVs to the project
	Adding dimensions, attributes, and hierarchies
		Creating dimensions with the Dimension Wizard
		Adding hierarchies to your dimensions
		Processing the dimensions
		Updating our dimensions
	Adding cubes and measure groups
		Creating the cube and measure groups
		Reviewing the cube\'s structure and modifying measures
		Reviewing dimension usage
	Summary
Chapter 05: Adding Measures and Calculations with MDX
	Technical requirements
	Introducing MDX basics – SELECT, FROM, WHERE
		Understanding multidimensional query concepts
		Understanding query structure
		Building your first query
	Adding calculations and measures to your cube
		Using the calculations section
		Creating calculated measures
		Creating named sets
	Adding KPIs to our cube
		Understanding the basics of a KPI
		Building your KPI
	Exploring more MDX expressions
		Traversing hierarchies
		Exploring data with more MDX functions
		Creating more calculations with aggregation and math functions
		Working with time
	Summary
Section 3: Building and Deploying Tabular Models
Chapter 06: Preparing Your Data for Tabular Models
	Technical requirements
	Prepping data for tabular models
		Contrasting self-service and managed deployments
		Using a star schema data warehouse
		Using non-star schema databases
		Using nontraditional sources
	Data optimization considerations
	Prototyping your model in Excel with Power Pivot
	Summary
Chapter 07: Building a Tabular Model in SSAS 2019
	Technical requirements
	Creating the solution and first tabular model
		Importing data into your model
		Marking the date table
		Adding hierarchies to the model
		Adding some measures to our model
		Managing partitions in tabular models
	Creating a tabular model with DirectQuery
		Creating a new tabular model project
		Converting the new model to DirectQuery
		Adding sample partitions to the DirectQuery model
		Enhancing your DirectQuery model
		Previewing the data with Excel
	Creating a tabular model on transactional data
	Importing a Power Pivot model into Analysis Services
	Deploying and processing your completed models
	Summary
Chapter 08: Adding Measures and Calculations with DAX
	Technical requirements
	Understanding the basics of DAX
	Adding columns and measures to the tabular model
		Creating item calculations
	Creating measures with the CALCULATE function
	Working with time intelligence and DAX
	Creating calculated tables
		Creating a delivery date table to support role playing
		Creating a filtered row set calculated table
		Creating a summary calculated table
		Creating a composite calculated table
	Creating calculation groups
	Creating KPIs
		Understanding the components in a tabular model KPI
		Building your KPI
	Querying your model with SQL Server Management Studio and DAX
	Summary
Section 4: Exposing Insights while Visualizing Data from Your Models
Chapter 09: Exploring and Visualizing Your Data with Excel
	Technical requirements
	Connecting Excel to your models
		Connecting to the multidimensional model
		Connecting to the tabular model
	Building visualizations with your models
		Understanding the PivotTable Fields panel
		Creating a PivotTable
		Adding a PivotChart
		Adding slicers
		Adding timelines
	Building and enhancing an Excel dashboard
		Moving the PivotTable and the filter
		Updating the Employee slicer
		Adjusting the other PivotTable
		Cleaning up our dashboard design
	Advanced design with CUBE functions
		Adding PivotTables to a new sheet
		Converting the PivotTable to formulas
		Formatting the new fields
	Sharing your Excel dashboards with others
		Checking your capabilities
		Checking your credentials
		Deploying your workbook
	Summary
Chapter 10: Creating Interactive Reports and Enhancing Your Models in Power BI
	Technical requirements
	Creating Power BI visualizations using live connections
		Connecting to data sources in Power BI
		Live connecting to the multidimensional model
		Live connecting to the tabular model
		Building our Power BI report with multidimensional data
		Building our Power BI report with tabular data
	Understanding live connections and import for Power BI with SSAS models
	Adding measures to Power BI when using tabular models and live connections
		Creating a new measure
	Deploying your Power BI report to a Power BI workspace
	Summary
Section 5: Security, Administration, and Managing Your Models
Chapter 11: Securing Your SSAS Models
	Technical requirements
	Reviewing security settings for SSAS
		Opening the security settings for the server
		Allowing anonymous access to your model
		Understanding advanced security properties
		Setting security properties in msmdsrv.ini
	Setting up user roles in servers and databases
		Adding members to the server administrator role
		Adding a read-only role to the multidimensional model
		Adding a read-only role to the tabular model
	Implementing data security in multidimensional models
		Adding customer roles with dimension hierarchy security
		Adding sales group security with dynamic dimension security
		Adding inventory group security with cell-level security
	Implementing data security in tabular models
		Creating a role to limit access to a customer
		Adding dynamic security for sales territories to the tabular model
	Summary
Chapter 12: Common Administration and Maintenance Tasks
	Technical requirements
	Understanding the languages
	Backing up and restoring SSAS databases
		Restoring your databases
	Processing or refreshing the data in your models
		Processing multidimensional models and their components
		Processing tabular models and their components
	Scaling your models
		Understanding processing and query servers
		Choosing scaling out versus clustering
		Synchronizing your databases
	Discovering how your models are performing
		Using Dynamic Management Views
		Using SQL Server Profiler
	Reviewing other maintenance tasks or tools
		Warming multidimensional models
		Using usage-based optimization with multidimensional models
		Removing unused fields from your tabular models
		Using open source tools to support tabular models
	Summary
Other Books You May Enjoy
Index