You are previewing SQL Server Analysis Services 2012 Cube Development Cookbook.
O'Reilly logo
SQL Server Analysis Services 2012 Cube Development Cookbook

Book Description

If you prefer the instructional approach to a lot of theory, this cookbook is for you. It takes you straight into building data cubes through hands-on recipes, helping you get to grips with SQL Server Analysis Services fast.

  • Develop Business Intelligence solutions using a multi-dimensional model as well as a tabular model

  • Explore cube maintenance with partitions and design effective aggregations, as well as analyzing options for scaling analytics solutions

  • Includes recipes for administering, securing, monitoring, and troubleshooting Analysis Services solutions

  • In Detail

    Microsoft SQL Server is a relational database management system. As a database, it is a software product whose primary function is to store and retrieve data as requested by other software applications. SQL Server Analysis Services adds OLAP and data mining capabilities for SQL Server databases. OLAP (online analytical processing) is a technique for analyzing business data for effective business intelligence.

    This practical guide teaches you how to build business intelligence solutions using Microsoft’s core product – SQL Server Analysis Services. The book covers the traditional multi-dimensional model which has been around for over a decade as well as the tabular model introduced with SQL Server 2012.

    Starting with comparing MultiDimensional and tabular models – discussing the values and limitations of each, you will then cover the essential techniques for building dimensions and cubes. Following on from this, you will be introduced to more advanced topics, such as designing partitions and aggregations, implementing security, and synchronizing databases for solutions serving many users.

    The book also covers administrative material, such as database backups, server configuration options, and monitoring and tuning performance. We also provide a primer on MultiDimensional eXpressions (MDX) as well as Data Analysis expressions (DAX) languages.

    This book provides you with data cube development techniques, and also the ongoing monitoring and tuning for Analysis Services.

    Table of Contents

    1. SQL Server Analysis Services 2012 Cube Development Cookbook
      1. Table of Contents
      2. SQL Server Analysis Services 2012 Cube Development Cookbook
      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
          2. Errata
          3. Piracy
          4. Questions
      8. 1. Introduction to Multidimensional Data Model Design
        1. Introduction
        2. The business value of Business Intelligence
        3. Challenges and barriers of effective BI
        4. Overcoming BI challenges and barriers
        5. Choosing multidimensional or Tabular Models
        6. Star- or Snowflake-relational schema
        7. A sample scenario for choosing the Snowflake schema
      9. 2. Defining Analysis Services Dimensions
        1. Introduction
        2. Defining data sources
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Defining data source views
          1. Getting started
          2. How to do it...
        4. Defining entity relationships in DSV
          1. How to do it...
        5. Extending data source views
          1. How to do it...
        6. Creating named calculations and queries
          1. How to do it...
        7. Creating simple dimensions
          1. Getting ready
          2. How to do it...
          3. How it works...
        8. Building dimension hierarchies
          1. How to do it...
          2. There's more
            1. Setting dimension properties
        9. Setting essential attribute properties
          1. How to do it...
        10. Browsing dimension data
          1. Getting ready
          2. How to do it...
          3. How it works...
        11. Sorting the attributes
          1. How to do it...
        12. Customizing advanced attribute properties
          1. How to do it...
          2. How it works...
        13. Creating parent-child dimensions
          1. How to do it...
          2. How it works...
          3. There's more...
        14. Creating the date and time dimensions
          1. How to do it...
          2. There's more...
      10. 3. Creating Analysis Services Cubes
        1. Introduction
        2. Defining measure groups and measures
          1. Getting ready
          2. How to do it...
        3. Setting measure properties
          1. How to do it...
          2. There's more...
        4. Browsing the cube data
          1. How to do it...
        5. Dimension usage with measure group
          1. How to do it...
        6. Examining cube file structures
          1. How to do it...
          2. There's more...
        7. Partitioning strategies
          1. How to do it...
            1. Creating partitions through the wizard
            2. Creating partitions through AMO
            3. Partition storage mode options
        8. Defining partition slice
          1. How to do it...
        9. Merging partitions
          1. How to do it...
        10. Defining aggregation designs
          1. Using BIDS Helper for customizing aggregations
          2. How to do it...
          3. There's more...
        11. Distinct count measure groups
          1. How to do it...
          2. There's more...
        12. Enabling write-back feature
          1. How to do it...
        13. Deployment options
          1. How to do it...
      11. 4. Extending and Customizing Cubes
        1. Introduction
        2. Defining calculated measures
          1. Getting ready
          2. How to do it...
        3. Defining named sets
          1. How to do it...
        4. Defining drillthrough actions
          1. How to do it...
          2. How it works...
        5. Defining URL actions
          1. How to do it...
        6. Defining reporting actions
          1. How to do it...
        7. Defining key performance indicators
          1. How to do it...
        8. Defining perspectives
          1. How to do it...
        9. Defining translations
          1. How to do it...
        10. Defining measure expressions
          1. How to do it...
      12. 5. Optimizing Dimension and Cube Processing
        1. Introduction
        2. Understanding dimension processing options
          1. How it works...
        3. Learning about basic dimension processing
          1. Getting ready
          2. How to do it...
          3. How it works...
        4. Learning advanced dimension processing options
          1. How to do it...
        5. Using out-of-line bindings for dimension processing
          1. How to do it...
        6. Dealing with partition processing options
          1. How to do it...
          2. How it works...
        7. Using SQL Server Integration Services to process Analysis Services objects
          1. How to do it...
        8. Monitoring and tuning processing performance
          1. How to do it...
      13. 6. MDX
        1. Introduction
        2. Returning data on the query axes
          1. Getting ready
          2. How to do it...
        3. Limiting the query output
          1. How to do it...
        4. Sorting the query output
          1. How to do it...
        5. Defining query level calculations and named sets
          1. How to do it...
        6. Navigating dimension hierarchies
          1. How to do it...
        7. Working with the Time dimensions
          1. How to do it...
        8. MDX script's functionality
          1. How to do it...
        9. Monitoring and tuning MDX queries
          1. How to do it...
          2. There's more...
      14. 7. Analysis Services Security
        1. Introduction
        2. Managing instance-level administrative security
          1. How to do it...
        3. Managing database-level security
          1. How to do it...
        4. Managing cube-level security
          1. How to do it...
        5. Managing dimension hierarchy-level security
          1. How to do it...
          2. How it works...
        6. Implementing dynamic dimension security
          1. How to do it...
          2. There's more...
        7. Implementing cell-level security
          1. How to do it...
          2. There's more...
      15. 8. Administering and Monitoring Analysis Services
        1. Introduction
        2. SSAS instance configuration options
          1. How to do it...
          2. How it works...
          3. There's more...
        3. Creating and dropping databases
          1. Getting ready
          2. How to do it...
        4. Monitoring SSAS instance using Activity Viewer
          1. How to do it...
          2. How it works...
        5. Monitoring SSAS instance using DMVs
          1. How to do it...
          2. How it works...
          3. There's more...
        6. Cancelling a session
          1. How to do it...
        7. Checking whether cubes are accessible
          1. How to do it...
        8. Checking SSAS object sizes programmatically
          1. How to do it...
          2. There's more...
            1. Scaling out SSAS solutions
        9. Backup and restore
          1. How to do it...
          2. How it works...
        10. Synchronizing databases
          1. How to do it...
          2. How it works...
        11. Detaching and attaching databases
          1. How to do it...
          2. How it works...
      16. 9. Using Tabular Models
        1. Introduction
        2. Creating a Tabular Model
          1. Getting ready
          2. How to do it…
          3. There's more…
        3. Working with data sources and loading data
          1. How to do it…
          2. There's more…
        4. Modeling the data
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        5. Creating a hierarchy
          1. How to do it…
          2. There's more…
        6. Creating a calculated measure
          1. Getting ready
          2. How to do it…
          3. See also
        7. Creating a calculated column
          1. How to do it…
          2. There's more…
          3. See also
        8. Creating a KPI
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
        9. Analyzing your model in Excel
          1. How to do it…
          2. There's more…
          3. See also
        10. Deploying Tabular Models
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
          5. See also
        11. Scripting Tabular Models using XMLA
          1. Getting ready
          2. How to do it…
          3. There's more…
        12. Processing Tabular Models
          1. How to do it…
          2. How it works…
          3. There's more…
          4. See also
        13. Partitioning Tabular Models
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
        14. Implementing perspectives
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
        15. Implementing security in Tabular Models
          1. Getting ready
          2. How to do it…
          3. How it works…
          4. There's more…
        16. Automating Tabular Model processing
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
      17. 10. DAX Calculations and Queries
        1. Introduction
        2. Combining tables using calculated columns
          1. Getting ready
          2. How to do it…
          3. There's more…
        3. Adding a calculated column
          1. Getting ready
          2. How to do it…
        4. Creating measures
          1. Getting ready
          2. How to do it…
            1. Creating AutoSum measures
            2. Creating calculated measures
        5. Testing a Tabular Model in Excel
          1. How to do it…
            1. Understanding Row Context
            2. Understanding Filter Context
        6. Using the CALCULATE function
          1. How to do it…
            1. Test the Measures in Excel
            2. Working with Time Intelligence
        7. Querying a Tabular Model
          1. Getting ready
          2. How to do it…
          3. There's more…
          4. See also
      18. 11. Performance Tuning and Troubleshooting Tabular Models
        1. Introduction
        2. Understanding usability limits
          1. Getting ready
          2. How to do it…
          3. There's more…
        3. Optimizing and managing a model's design
          1. Managing memory usage
        4. Diagnosing performance issues
          1. Processing and query-related performance
        5. Using performance tools
          1. Task Manager and Resource Monitor
          2. Dynamic Management Views
          3. BISM server memory report
        6. Investigating query performance with SQL Server Profiler
          1. How to do it…
          2. There's more…
          3. See also
      19. A. Miscellaneous Analysis Services Topics
        1. Working with non-SQL Server data sources
          1. SSAS data structures
          2. Transaction isolation levels
          3. Processing performance issues
        2. Common yet confusing SSAS errors
          1. Binding is too small
          2. Attribute key cannot be found
          3. Undefined column name
          4. DSV does not contain definition for an xyz column
          5. Operation is cancelled
          6. File is corrupted
          7. Error encountered in the transport layer
          8. Internal error or unexpected exception
          9. Deadlock
        3. Dimension properties
          1. See also
        4. Performance considerations for many-to-many dimension relationships
        5. DirectQuery with Tabular Models
          1. Data sources
          2. Security
          3. Design limitations
          4. Client restrictions
      20. Index