Expert Cube Development with SSAS Multidimensional Models

Expert Cube Development with SSAS Multidimensional Models
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 6%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Design SQL Server Analysis Services cubes, dimensions, and calculations from the experts!
  • Add calculations to the cube, including implementing currency conversion and a date tool dimension
  • Use security to control access to data in your cube

Book Details

Language : English
Paperback : 402 pages [ 235mm x 191mm ]
Release Date : February 2014
ISBN : 1849689903
ISBN 13 : 9781849689908
Author(s) : Chris Webb, Alberto Ferrari, Marco Russo
Topics and Technologies : All Books, Enterprise

Table of Contents

Chapter 1: Designing the Data Warehouse for Analysis Services
Chapter 2: Building Basic Dimensions and Cubes
Chapter 3: Designing More Complex Dimensions
Chapter 4: Measures and Measure Groups
Chapter 5: Handling Transactional-Level Data
Chapter 6: Adding Calculations to the Cube
Chapter 7: Adding Currency Conversion
Chapter 8: Query Performance Tuning
Chapter 9: Securing the Cube
Chapter 10: Going in Production
Chapter 11: Monitoring Cube Performance and Usage
Appendix: DAX Query Support
  • Chapter 1: Designing the Data Warehouse for Analysis Services
    • The source database
      • The OLTP database
      • The data warehouse
      • The data mart
    • Data modeling for Analysis Services
      • Fact tables and dimension tables
      • Star schemas and snowflake schemas
      • Junk dimensions
      • Degenerate dimensions
      • Slowly Changing Dimensions
      • Bridge tables or factless fact tables
      • Snapshot and transaction fact tables
      • Updating fact and dimension tables
      • Natural and surrogate keys
      • Unknown Members, key errors, and NULLability
    • Physical database design for Analysis Services
      • Multiple data sources
      • Data types and Analysis Services
      • SQL queries generated during cube processing
        • Dimension processing
        • Dimensions with joined tables
        • Reference dimensions
        • Fact dimensions
        • Distinct Count measures
        • Indexes in the data mart
      • Usage of schemas
      • Naming conventions
      • Views versus the Data Source View
    • Summary
  • Chapter 2: Building Basic Dimensions and Cubes
    • Multidimensional and Tabular models
    • Choosing an edition of Analysis Services
    • Setting up a new Analysis Services project
    • Creating data sources
    • Creating Data Source Views
    • Designing simple dimensions
      • Using the New Dimension wizard
      • Using the Dimension Editor
        • Adding new attributes
        • Configuring a Time dimension
        • Creating user hierarchies
        • Configuring attribute relationships
    • Building a simple cube
      • Using the New Cube wizard
      • Project deployment
      • Database processing
    • Summary
  • Chapter 3: Designing More Complex Dimensions
    • Grouping and banding
      • Grouping
      • Banding
    • Modeling Slowly Changing Dimensions
      • Type I SCDs
      • Type II SCDs
        • Modeling attribute relationships on a Type II SCD
        • Handling member status
      • Type III SCDs
    • Modeling junk dimensions
    • Modeling ragged hierarchies
      • Modeling parent/child hierarchies
      • Ragged hierarchies with HideMemberIf
    • Summary
  • Chapter 4: Measures and Measure Groups
    • Measures and aggregation
      • Useful properties of measures
        • FormatString
        • DisplayFolders
      • Built-in measure aggregation types
        • Basic aggregation types
        • DistinctCount
        • None
        • Semi-additive aggregation types
        • ByAccount
      • Dimension calculations
        • Unary operators and weights
        • Custom Member Formulas
      • Non-aggregatable values
    • Measure groups
      • Creating multiple measure groups
      • Creating measure groups from dimension tables
        • MDX formulas versus pre-calculating values
      • Handling different dimensionality
      • Handling different granularities
      • Non-aggregatable measures – a different approach
      • Using linked dimensions and measure groups
      • Role-playing dimensions
    • Dimension/measure group relationships
      • Fact relationships
      • Referenced relationships
      • Data mining relationships
    • Summary
  • Chapter 5: Handling Transactional-Level Data
    • Details about transactional data
    • Drillthrough
      • Actions
      • Drillthrough actions
        • Drillthrough columns order
        • Drillthrough and calculated members
      • Drillthrough modeling
        • Drillthrough using a transaction detail dimension
        • Drillthrough with ROLAP dimensions
        • Drillthrough on alternate fact table
        • Drillthrough recap
    • Many-to-many dimension relationships
      • Implementing a many-to-many dimension relationship
      • Advanced modeling with many-to-many relationships
      • Performance issues
  • Summary
  • Chapter 6: Adding Calculations to the Cube
    • Different kinds of calculated members
    • Common calculations
      • Simple calculations
      • Referencing cell values
      • Aggregating members
      • Year-to-date calculations
      • Ratios over a hierarchy
      • Previous period growths
      • Same period previous year
      • Moving averages
      • Ranks
      • Formatting calculated measures
    • Calculation dimensions
      • Implementing a simple calculation dimension
      • The Time Intelligence wizard
        • Attribute overwrite
        • Limitations of calculated members
      • Calculation dimension best practices
    • Named sets
      • Regular named sets
      • Dynamic named sets
    • Summary
  • Chapter 7: Adding Currency Conversion
    • Introduction to currency conversion
      • Data collected in a single currency
      • Data collected in a multiple currencies
    • Where to perform currency conversion
    • The Add Business Intelligence wizard
      • Concepts and prerequisites
      • How to use the Add Business Intelligence wizard
        • Data collected in a single currency with reporting in multiple currencies
        • Data collected in multiple currencies with reporting in a single currency
        • Data stored in multiple currencies with reporting in multiple currencies
    • Measure expressions
      • DirectSlice property
      • Writeback
    • Summary
  • Chapter 8: Query Performance Tuning
    • Understanding how Analysis Services processes queries
    • Performance tuning methodology
    • Designing for performance
    • Performance-specific design features
      • Partitions
        • Why partition?
        • Building partitions
        • Planning a partitioning strategy
        • Unexpected Partition scans
      • Aggregations
        • Creating an initial aggregation design
        • Usage-Based Optimization
        • Monitoring partition and aggregation usage
        • Building aggregations manually
        • Common aggregation design issues
    • MDX calculation performance
      • Diagnosing Formula Engine performance problems
      • Calculation performance tuning
        • Tuning algorithms used in MDX
        • Using calculated members to cache numeric values
        • Tuning the implementation of MDX
    • Caching
      • Formula cache scopes
      • Other scenarios that restrict caching
      • Cache warming
        • The CREATE CACHE statement
        • Running batches of queries
    • Scale-up and Scale-out
    • Summary
  • Chapter 9: Securing the Cube
    • Sample security requirements
    • Analysis Services security features
      • Roles and role membership
      • Securable objects
      • Creating roles
      • Membership of multiple roles
      • Testing roles
    • Administrative security
    • Data security
      • Granting Read Access to Cubes
      • Cell security
      • Dimension security
        • Visual Totals
        • Restricting access to Dimension Members
        • Applying security to Measures
      • Dynamic security
        • Dynamic dimension security
        • Dynamic security with stored procedures
      • Dimension security and parent/child hierarchies
    • Dynamic cell security
    • Accessing Analysis Services from outside a domain
    • Managing security
    • Security and query performance
      • Cell security
      • Dimension security
      • Dynamic security
    • Summary
  • Chapter 10: Going in Production
    • Making changes to a cube in production
    • Managing partitions
      • Relational versus Analysis Services partitioning
      • Building a template partition
      • Generating partitions in Integration Services
    • Managing processing
      • Dimension processing
      • Partition processing
      • Lazy Aggregations
      • Processing reference dimensions
      • Handling processing errors
      • Managing processing with Integration Services
      • Push-mode processing
      • Proactive caching
    • SSAS Data Directory maintenance
    • Performing database backup
    • Copying databases between servers
    • Summary
  • Chapter 11: Monitoring Cube Performance and Usage
    • Analysis Services and the operating system
      • Resources shared by the operating system
        • CPU
        • Memory
        • I/O Operations
      • Tools to monitor resource consumption
        • Windows Task Manager
        • Performance counters
        • Resource Monitor
      • Analysis Services memory management
        • Memory differences between 32 bit and 64 bit
        • Controlling the Analysis Services Memory Manager
        • Out of memory conditions in Analysis Services
      • Sharing SQL Server and Analysis Services on the same machine
    • Monitoring processing performance
      • Monitoring processing with trace data
        • SQL Server Profiler
        • ASTrace
        • XMLA
        • Flight Recorder
      • Monitoring processing with Performance Monitor counters
      • Monitoring processing with Dynamic Management Views
    • Monitoring query performance
      • Monitoring queries with trace data
      • Monitoring queries with Performance Monitor counters
      • Monitoring queries with Dynamic Management Views
    • Monitoring usage
      • Monitoring usage with trace data
      • Monitoring usage with Performance Monitor counters
      • Monitoring usage with Dynamic Management Views
      • Activity Viewer
    • Building a complete monitoring solution
    • Summary
  • Appendix: DAX Query Support
    • Implementation details
      • Mapping Multidimensional objects to Tabular concepts
      • Unsupported features
      • New functionality in Analysis Services
    • Connecting Power View to a Multidimensional model
    • Running DAX queries against a Multidimensional model
      • Executing DAX queries
      • DAX queries and attributes

Chris Webb

Chris Webb ( has been working with Microsoft Business Intelligence tools for 15 years in a variety of roles and industries. He is an independent consultant ( and trainer ( based in the UK, specializing in SQL Server Analysis Services, MDX, DAX, Power Pivot, and the whole Power BI stack. He is the co-author of MDX Solutions with Microsoft SQL Server Analysis Services 2005 and SQL Server Analysis Services 2012: The BISM Tabular Model. He is a regular speaker at user groups and conferences, and blogs about Microsoft BI at

Alberto Ferrari

Alberto Ferrari  ( is a consultant and trainer who specializes in Business Intelligence with the Microsoft BI stack. He spends half of his time consulting for companies who need to develop complex data warehouses, and the other half in training, book writing, conferences, and meetings. He is a SQL Server MVP and a SSAS-Maestro.

He is a founder, with Marco Russo, of, where they publish whitepapers and articles about SQL Server Analysis Services technology. He co-authored several books on SSAS and PowerPivot.

Marco Russo

Marco Russo is a Business Intelligence consultant and mentor. His main activities are related to data warehouse relational and multidimensional design, but he is also involved in the complete development lifecycle of a BI solution. He has particular competence and experience in sectors such as financial services (including complex OLAP designs in the banking area), manufacturing, gambling, and commercial distribution. Marco is also a book author, and in addition to his BI-related publications, he has authored books about .NET programming. He is also a speaker at international conferences such as TechEd, PASS Summit, SQLRally, and SQLBits. He achieved the unique SSAS Maestro certification and is also a Microsoft Certified Trainer with several Microsoft Certified Professional certifications.

Code Downloads

Download the code and support files for this book.

Submit Errata

Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

Sample chapters

You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

Frequently bought together

Expert Cube Development with SSAS Multidimensional Models +    Haskell Data Analysis Cookbook =
50% Off
the second eBook
Price for both: $43.05

Buy both these recommended eBooks together and get 50% off the cheapest eBook.

What you will learn from this book

  • Build a data mart suitable for use with Analysis Services
  • Work with a thread pool effectively
  • Create and configure an Analysis Services project in SQL Server Data Tools
  • Use the Dimension Wizard and the Dimension Editor to build dimensions
  • Create measure groups and associate them with dimensions
  • Design cubes and dimensions and also implement common calculations in MDX
  • Explore the security model, including dimension security and cell security, and implement dynamic security
  • Tune queries to get the best possible performance
  • Automate processing and partition creation
  • Monitor your cube to see who's actually using it

In Detail

Microsoft's SQL Server Analysis Services 2012 Multidimensional is an OLAP server that allows end users to analyze large amounts of business data quickly and easily, using a variety of tools including Excel, and to create their own reports without IT involvement. It is part of the Microsoft SQL Server suite of tools and has been used widely on Business Intelligence projects for the last 15 years; it is now the most commonly used OLAP server in the world.

Expert Cube Development with SSAS Multidimensional Models will teach you tips and tricks for cube design from some of the most experienced Analysis Services experts in the world. Topics covered include designing dimensions and cubes, implementing common MDX calculations, security and performance tuning.

The book takes you through the whole lifecycle of Analysis Services cube development, from how data warehouse design affects your cube, through cube and dimension design to performance tuning and monitoring. Some previous experience of Analysis Services is assumed – the focus is on best practices and design patterns.

The book starts off with a discussion of data warehouse design and how it relates to Analysis Services. Following on from that, best practices relating to Analysis Services cube and dimension design are covered, including topics such as optimizing dimension attribute relationships and handling fact tables that contain data at different granularities.

Next, the book looks at using MDX to implement common business calculations, and then moves on to more advanced problems like currency conversion calculations and creating a date tool dimensions.

Finally security, performance tuning, automation of administrative tasks and monitoring usage and performance each get their own chapter and subject such as dynamic security roles, using Profiler to diagnose query performance problems and automating partition creation are also dealt with.


An easy-to-follow guide full of hands on examples of real-world Analysis Services cube development tasks. Each topic is explained and placed in context, and for the more inquisitive reader, there also more in-depth details of the concepts used.

Who this book is for

If you are an Analysis Services cube designer wishing to learn more advanced topic and best practices for cube design, this book is for you.You are expected to have some prior experience with Analysis Services cube development.

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software