Expert Cube Development with Microsoft SQL Server 2008 Analysis Services -- Table of Contents -- Expert Cube Development with Microsoft SQL Server 2008 Analysis Services -- Credits -- About the Authors -- About the Reviewers -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code and database for the book -- Errata -- Piracy -- Questions -- 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 -- 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 -- 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 -- 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 -- 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 -- 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 -- 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 -- 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 named sets to avoid recalculating set expressions.

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 -- 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 -- 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 -- 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 -- Index.