Expert Cube Development with SSAS Multidimensional Models

Expert Cube Development with SSAS Multidimensional Models

by Chris Webb, Alberto Ferrari, Marco Russo
Released February 2014
Publisher(s): Packt Publishing
ISBN: 9781849689908

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

For Analysis Service cube designers this is the hands-on tutorial that will take your expertise to a whole new level. Written by a team of Microsoft SSAS experts, it digs deep to optimize your Business Intelligence capabilities.

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 SQL Server Analysis Services 2012 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.

What You Will Learn

  • 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

Table of contents

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

Product information

  • Title: Expert Cube Development with SSAS Multidimensional Models
  • Author(s): Chris Webb, Alberto Ferrari, Marco Russo
  • Release date: February 2014
  • Publisher(s): Packt Publishing
  • ISBN: 9781849689908