Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
eBook: $29.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 25%!
Print + free eBook + free PacktLib access to the book: $79.98    Print cover: $49.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters


  • A real-world guide to designing cubes with Analysis Services 2008
  • Model dimensions and measure groups in BI Development Studio
  • Implement security, drill-through, and MDX calculations
  • Learn how to deploy, monitor, and performance-tune your cube
  • Filled with best practices and useful hints and tips

Book Details

Language : English
Paperback : 360 pages [ 235mm x 191mm ]
Release Date : July 2009
ISBN : 1847197221
ISBN 13 : 9781847197221
Author(s) : Alberto Ferrari, Chris Webb, Marco Russo
Topics and Technologies : All Books, Enterprise Products and Platforms, Microsoft Servers, Enterprise, Microsoft, Microsoft SQL Server


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: Adding Transactional Data such as Invoice Line and Sales Reason
Chapter 6: Adding Calculations to the Cube
Chapter 7: Adding Currency Conversion
Chapter 8: Query Performance Tuning
Chapter 9: Securing the Cube
Chapter 10: Productionization
Chapter 11: Monitoring Cube Performance and Usage
  • 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
    • 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
      • Deployment
      • Processing
    • Summary
  • Chapter 3: Designing More Complex Dimensions
    • Grouping and Banding
      • Grouping
      • Banding
    • Slowly Changing Dimensions
      • Type I SCDs
      • Type II SCDs
        • Modeling attribute relationships on a Type II SCD
        • Handling member status
      • Type III SCDs
    • Junk dimensions
    • Ragged hierarchies
      • Parent/child hierarchies
      • Ragged hierarchies with HideMemberIf
    • Summary
  • Chapter 4: Measures and Measure Groups
    • Measures and aggregation
      • Useful properties of measures
        • Format String
        • Display folders
      • Built-in measure aggregation types
        • Basic aggregation types
        • Distinct Count
        • None
        • Semi-additive aggregation types
        • By Account
      • Dimension calculations
        • Unary operators and weights
        • Custom Member Formulas
      • Non-aggregatable values
    • Measure groups
      • Creating multiple measure groups
      • Creating measure groups from dimension tables
      • 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: Adding Transactional Data such as Invoice Line and Sales Reason
    • Details about transactional data
    • Drillthrough
      • Actions
      • Drillthrough actions
        • Drillthrough Columns order
        • Drillthrough and calculated members
      • Drillthrough modeling
        • Drillthrough using a transaction details 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 modelling 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-dates
      • Ratios over a hierarchy
      • Previous period growths
      • Same period previous year
      • Moving averages
      • Ranks
      • Formatting calculated measures
    • Calculation dimensions
      • Implementing a simple calculation dimension
      • Calculation dimension pitfalls and problems
        • 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
    • 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
        • 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
      • 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: Productionization
    • 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
    • Analysis Services data directory maintenance
    • 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
      • MDX Studio
    • Monitoring usage
      • Monitoring Usage with Trace Data
      • Monitoring usage with Performance Monitor counters
      • Monitoring usage with Dynamic Management Views
      • Activity Viewer
    • How to build a complete monitoring solution
    • Summary

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.

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

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.


- 3 submitted: last submission 29 Aug 2013

page 157 Typo 11 Sep 09
The sentence "As we have seen, a calculation dimension does not need not to have a relationshipwith any fact table." should be "As we have seen, a calculation dimension does not need to have a relationship with any fact table."
page 199 Technical 26 Nov 09 <br />should point to <br />
page 219 Technical/typo 21 Oct 09
[Account].[Account].[Account].MEMBERS*<br />[Date].[Date].[Date].MEMBERS*<br />[Department].[Departments].[Department Level 02].MEMBERS<br />Should be:<br />[Account].[Account].[Account].MEMBERS*<br />[Date].[Date].[Date].MEMBERS
page 302 technical 14 Aug 09
"An interesting Microsoft whitepaper about hardware optimization for a multi-terabyte<br />data warehouse and cube is available at"<br />The link doesn't go to the intended white paper. It should point to:<br /><br />

Errata type: Technical | Page number: 246, 247

The picture at page 247 has an error: the Bridge_ResellerUsers table shouldn’t have a primary key like it is indicated in the picture. Thus, the relational table should be defined either without a primary key or with a primary key composed by both ID_Reseller and ID_User.


Errata type: URLs | Page number: 157

The URL for Attribute overwrite discussion ( has been removed from the Internet.
The original URL for Chris Webb's BI Blog-tiny URL is Chris Webb's BI Blog-original URL.


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 Microsoft SQL Server 2008 Analysis Services +    SharePoint 2013 WCM Advanced 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
  • Create and configure an Analysis Services project in Business Intelligence Development Studio
  • Use the Dimension Wizard and the Dimension Editor to build dimensions
  • Create measure groups and associate them with dimensions
  • Add calculations to the cube, including implementing currency conversion and a date tool dimension
  • 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 2008 is an OLAP server that allows users to analyze business data quickly and easily. However, designing cubes in Analysis Services can be a complex task: it's all too easy to make mistakes early on in development that lead to serious problems when the cube is in production. Learning the best practices for cube design before you start your project will help you avoid these problems and ensure that your project is a success.

This book offers practical advice on how to go about designing and building fast, scalable, and maintainable cubes that will meet your users' requirements and help make your Business Intelligence project a success.

This book gives readers insight into the best practices for designing and building Microsoft Analysis Services 2008 cubes. It also provides details about server architecture, performance tuning, security, and administration of an Analysis Services solution.

In this book, you will learn how to design and implement Analysis Services cubes. Starting from designing a data mart for Analysis Services, through the creation of dimensions and measure groups, to putting the cube into production, we'll explore the whole of the development lifecycle.

This book is an invaluable guide for anyone who is planning to use Microsoft Analysis Services 2008 in a Business Intelligence project.

A hands-on guide to designing Analysis Services cubes


This is a practical tutorial for Analysis Services that shows readers how to solve problems commonly encountered while designing cubes, and explains which features of Analysis Services work well and which should be avoided. The book walks through the whole cube development lifecycle, from building dimensions, cubes and calculations to tuning and moving the cube into production.

Who this book is for

This book is aimed at Analysis Services developers who already have some experience but who want to go into more detail on advanced topics, and who want to learn best practices for cube design.

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