Microsoft® PowerPivot for Excel® 2010

Table of Contents

1. Microsoft® PowerPivot for Excel 2010: Give Your Data Meaning
2. Dedication
3. A Note Regarding Supplemental Files
4. Preface
   1. Marco Russo
   2. Alberto Ferrari
5. Acknowledgments
6. Introduction
   1. Who Is This Book For?
   2. System Requirements
   3. About the DVD
      1. What’s on the DVD
   4. Errata & Book Support
   5. We Want to Hear from You
   6. Stay in Touch
7. 1. First Steps with PowerPivot
   1. Working with Classic Excel PivotTables
   2. Working with PivotTables in PowerPivot
      1. Importing Data
      2. Querying Data
   3. Summary
8. 2. PowerPivot at Work
   1. Using the PivotTable to Produce Reports
      1. Formatting Numbers
      2. Hiding or Removing Useless Columns
      3. Adding Calculated Columns
      4. Adding Measures
   2. Adding More Tables
   3. Working with Dates
   4. Refreshing Data
   5. Using Slicers
   6. Summary
9. 3. Introduction to DAX
   1. Understanding Calculation in DAX
      1. DAX Syntax
      2. DAX Data Types
      3. DAX Operators
      4. DAX Values
   2. Understanding Calculated Columns and Measures
      1. Calculated Columns
      2. Measures
         1. Choosing Between Calculated Columns and Measures
   3. Handling Errors in DAX Expressions
      1. Conversion Errors
      2. Arithmetical Operations
         1. Empty or Missing Values
      3. Intercepting Errors
   4. Common DAX Functions
      1. Statistical Functions
      2. Logical Functions
      3. Information Functions
      4. Mathematical Functions
      5. Text Functions
      6. Date and Time Functions
   5. Summary
10. 4. Data Models
    1. Understanding Data Models
       1. Following the Standard Excel Method
       2. Discovering the PowerPivot Way
       3. What Is a Data Model?
    2. Understanding Physical and Logical Data Models
       1. Normalization and Denormalization
       2. Empty Values
    3. Understanding How and When to Denormalize Tables
       1. The PowerPivot Query Designer
       2. When to Denormalize Tables
    4. Complex Relationships
    5. Understanding OLTP and Data Marts
       1. Data Marts, Facts, and Dimensions
       2. Star Schemas
       3. Querying the Data Warehouse
    6. Discovering Advanced Types of Relationships
       1. Role-Playing Relationships
       2. Many-to-Many Relationships
    7. Summary
11. 5. Loading Data and Models
    1. Understanding Data Sources
    2. Loading from a Database
       1. Loading from a List of Tables
       2. Loading Relationships
       3. Selecting Related Tables
       4. Loading from a SQL Query
       5. Loading from Views
    3. Opening Existing Connections
    4. Loading from Access
    5. Loading from Analysis Services
       1. Using the MDX Editor
       2. Handling of Keys in the OLAP Cube
    6. Using Linked Tables
    7. Loading from Excel Files
    8. Loading from Text Files
    9. Loading from the Clipboard
    10. Loading from a Report
    11. Loading from a Data Feed
    12. Loading from SharePoint
    13. Summary
12. 6. Evaluation Context and CALCULATE
    1. Understanding Evaluation Context
       1. Filter Context in a Single Table
       2. Row Context in a Single Table
       3. Adding Filters to a Filter Context for a Single Table
       4. Removing Filters from a Filter Context for a Single Table
       5. Row Context with Multiple Tables
       6. Filter Context with Multiple Tables
       7. Modifying Filter Context for Multiple Tables
       8. Final Considerations for Evaluation Context
    2. Understanding the CALCULATE Function
    3. Understanding the EARLIER Function
    4. Summary
13. 7. Date Calculations in DAX
    1. Working with a Dates Table
       1. How to Build a Dates Table
       2. Working with Multiple Dates Tables
       3. Differentiating Columns in Multiple Dates Tables
       4. Calculating Working Days
    2. Aggregating and Comparing over Time
       1. Year-to-Date, Quarter-to-Date, and Month-to-Date
       2. Periods from the Prior Year
       3. Difference over Previous Year
       4. Simplifying Browsing with a Period Table
    3. Closing Balance over Time
       1. Semiadditive Measures
       2. OPENINGBALANCE and CLOSINGBALANCE Functions
       3. Updating Balances by Using Transactions
    4. Summary
14. 8. Mastering PivotTables
    1. Understanding Different Types of PivotTables
       1. File Size
       2. Handling Slicers
       3. Flattened PivotTable
       4. Comparing Features
    2. Custom Sorting in PivotTables
    3. Computing Ratios and Percentage in PivotTables
    4. Aggregating Data Without Using Sum
    5. Creating Dashboards
    6. Using Complex Queries as Linked Tables
    7. Performing Analysis of Old and New Data Together
    8. Defining Sets
    9. Creating Dynamic Sets with MDX
    10. Creating Sets of Measures with MDX
    11. Summary
15. 9. PowerPivot DAX Patterns
    1. Calculating Ratio and Percentage
       1. Calculating Ratio on a Single Denormalized Table
       2. Calculating Ratio on Multiple Normalized Tables
    2. Computing Standard Deviation
    3. Ranking over a Measure
       1. Calculating Ranking on a Single Denormalized Table
       2. Calculating Ranking in Multiple Normalized Tables
    4. Computing ABC and Pareto Analyses
       1. ABC Analysis with a Single Denormalized Table
       2. ABC Analysis with Multiple Normalized Tables
       3. ABC with Denormalized Attributes on Normalized Tables
    5. Event in Progress
    6. Summary
16. 10. PowerPivot Data Model Patterns
    1. Banding
       1. Banding with Band Expansion
       2. Banding with Basic DAX
       3. Banding with CALCULATE
    2. Performing Courier Simulation
       1. Loading the Main Table
       2. Adding Courier Information
       3. Using DAX to Resolve Complex Relationships
       4. Using Many-to-Many Relationships
    3. Summary
17. 11. Publishing to SharePoint
    1. SharePoint 2010 and PowerPivot Integration
       1. PowerPivot Gallery
    2. Publishing an Excel Workbook
    3. PowerPivot Data Refresh
    4. Summary
18. A. DAX Functions Reference
    1. Statistical Functions
    2. Logical Functions
    3. Information Functions
    4. Mathematical Functions
    5. Text Functions
    6. Date and Time Functions
    7. Filter and Value Functions
    8. Time Intelligence Functions
19. B. Biography
20. Index
21. About the Authors
22. Copyright