Advanced SQL Server 2012/2014 Analysis Services (SSAS) Performance & Best Practices

SSAS Training

Summary:	This course emphasises improving the quality of the end-user experience by equipping the technical support team (developers, DBAs, etc.) with the requisite SSAS skills to design, implement, monitor and tune: Dimension attributes Analysis Services query (MDX) performance Processing performance Aggregations performance Indexes Partitioning Etc.
Objectives:	Upon completion of this presentation, the participant should be able to diagnose SSAS performance problems and design and implement, monitor and tune per the facilities under ‘Core Topics’ in the outline below.
Audience:	Developers, DBAs and capacity planners.
Prerequisites:	A conceptual knowledge of MDX would be useful.
Format:	Lecture and hands-on (about 55%).

Core Topics

1. REVIEW OF SSAS BASICS

What is OLAP?
Multidimensional space
Client/server concepts
Multidimensional model
Cubes
Measures
The MDX role
Data warehouse creation/role
The Unified Dimensional Model (UDM)
MOLAP vs. ROLAP vs. HOLAP

2. ANALYSIS SERVICES ARCHITECTURE

Command execution
Memory management
Thread management
Query execution and MDX expressions
Query execution
Query execution and data retrieval

3. OPTIMISING QUERY PERFORMANCE

How to discover/determine query bottlenecks
How to use and interpret the SQL Server Profiler
How to diagnose query performance
How to optimise dimension design
How to optimise hierarchy design
How to maximise the usefulness of aggregations
How to interpret aggregations
How to determine aggregation candidates
How to use the Usage-Based Optimization Wizard
How aggregation impacts parent-child relationships
Learn-by-doing exercises

4. PARTITION & QUERY PERFORMANCE

Measure groups
Partition slicing
Aggregation design
Distinct Count design
Partition sizing
Learn-by-doing exercises

5. OPTIMISING MDX DESIGN & USAGE

What is MDX?
MDX queries
MDX expressions
MDX functions
Calculated members
Named sets
Common calculations and solutions
MDX query context and execution
Sorting and ranking
Optimising set operations
Optimising summation
Optimising scripts
Cell-by-cell mode vs. subspace mode
Iif function
Cache partial expression and cell properties
Varying attributes in set operations
Learn-by-doing exercises

6. TUNING SERVER RESOURCES

Threadpool/query/maxthreads
Threadpool/process/maxthreads
Memory heap vs. Analysis Services heap

7. THE SSAS PROCESSING ARCHITECTURE

XMLA commands
Processing options
Side-effects/ impact analysis
Processing engine
Lazy processing
Out-of-line binding
SSIS

8. CUBE PROCECSSING BASICS

Definition
Types of data changes
Dimension processing
Rebuilding dimensions
Incrementally processing dimensions
Rebuilding cubes
Refreshing cubes
Refreshing cubes incrementally
Automating the processing tasks
Push-mode processing
Learn-by-doing exercises

9. OPTIMISING PROCESSING PERFORMANCE

What is a processing job?
How to establish process baselines
Use of Windows Performance counters
How to use Profiler Trace to optimise queries that participate in processing
How to improve dimension processing performance
Dimension processing and commands
Learn-by-doing exercises

10. SOME PROCESSING BEST PRACTICES

Dimensions
Cubes
Parallelism
Troubleshooting
Co-location (SSAS + data warehouse, for example) on the same server

11. PARTITION PROCESSING & PERFORMANCE

Partition processing architecture
Partition processing commands
Partition processing best practices
Tuning the relational partition query
Controlling locking overhead

Duration: 3 Days

Cost: £1380 + VAT

Advanced SQL Server Training Scotland, Edinburgh, Glasgow, Inverness and Aberdeen