Expert Cube Development with SSAS Multidimensional Models -- Table of Contents -- Expert Cube Development with SSAS Multidimensional Models -- Credits -- About the Authors -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why subscribe? -- Free access for Packt account holders -- Instant updates on new Packt books -- 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 -- Downloading the color images of this 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 -- Dimension tables -- Fact tables -- Usage of schemas -- Naming conventions -- Views versus the Data Source View -- Summary -- 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 -- 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 -- 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 -- 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 -- 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 -- 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 -- 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 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 -- The 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 -- 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 -- 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 -- 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 -- A. 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 -- Index.