O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PowerPivot for Business Intelligence Using Excel and SharePoint

Book Description

PowerPivot comprises a set of technologies for easy access to data mining and business intelligence analysis from Microsoft Excel and SharePoint. Power users and developers alike can create sophisticated, online analytic processing (OLAP) solutions using PowerPivot for Excel, and then share those solutions with other users via PowerPivot for SharePoint. Data can be pulled in from any of the leading database platforms, as well as from spreadsheets and flat files.

PowerPivot for Business Intelligence Using Excel and SharePoint is your key to mastering PowerPivot. The book takes a scenario-based approach to showing you how to collect data, to mine that data through insightful analysis, and to draw conclusions that drive business performance. Each chapter in the book is focused on a specific challenge that you'll encounter when using PowerPivot. Each chapter takes you through a solution technique that's been proven in the real world.

  • Covers the leading technology for bringing data analytics to the desktop

  • Presents real-world solutions to real-world scenarios

  • Written by a Microsoft Virtual Technical Specialist (VTS) for Business Intelligence

What you'll learn

  • Install and verify the PowerPivot software

  • Integrated existing, available data to deliver business intelligence

  • Leverage Time Intelligence to report change over time

  • Write Data Analysis Expressions (DAX) to create custom measures

  • Identify and implement solutions for role-playing dimensions

  • Recognize and work-around PowerPivot's missing features

Who this book is for

PowerPivot Solutions for Excel and SharePoint is aimed at information workers and data analysts who typically use Excel to drive business decisions. The book shows how you can apply PowerPivot to problems typically addressed through complicated and arcane spreadsheet techniques. Business people without the time and interest in learning Excel arcane will especially appreciate how PowerPivot enables them to easily create models and perform analysis far in advance of anything they could do using Excel alone.

Table of Contents

  1. Title Page
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Author
  6. About the Technical Reviewer
  7. Acknowledgments
  8. CHAPTER 1: Getting Started with PowerPivot for Excel
    1. Filling the Gap with PowerPivot for Excel
    2. What You Will Need
    3. The 64-bit Decision
    4. Installing the Add-In
    5. A Brief Tour of PowerPivot for Excel
    6. A Trivial Test Case
    7. The Test Report
    8. Summary
  9. CHAPTER 2: Hello World, PowerPivot Style
    1. The Business Scenario
    2. Assembling the Solution
    3. Behind the Scenes in PowerPivot for Excel
    4. Summary
  10. CHAPTER 3: Combining Data Sources
    1. The Business Scenario
    2. Summary
  11. CHAPTER 4: Data Analysis Expressions
    1. Fundamentals of DAX
    2. Calculated Columns
    3. Table Relationships
    4. Calculated Measures from Multiple Data Sources
    5. Filter Context
    6. Summary
  12. CHAPTER 5: A Method to the Madness
    1. Justification for a Method
    2. Principles
    3. Practices
    4. Summary
  13. CHAPTER 6: Installing PowerPivot for SharePoint
    1. Getting Started
    2. Existing SharePoint 2010 Installation
    3. Virtual Success
    4. Running PowerPivot for SharePoint on Windows 7
    5. Fun with PowerPivot and SharePoint
    6. Potential Errors
    7. Summary
  14. CHAPTER 7: Collaboration, Version Control, and Management
    1. Sharing Solutions
    2. Publishing
    3. Web Usage Scenarios
    4. PowerPivot Management Dashboard
    5. Summary
  15. CHAPTER 8: PowerPivot As a Data Source
    1. PowerPivot As a Data Source for PowerPivot
    2. PowerPivot Management Dashboard As a Data Source
    3. PowerPivot and PerformancePoint Dashboards
    4. Summary
  16. CHAPTER 9: PowerPivot and SQL Server Reporting Services
    1. Consuming SSRS As a PowerPivot Data Source
    2. Using PowerPivot As an SSRS Data Source
    3. Summary
  17. CHAPTER 10: PowerPivot and Predictive Analytics
    1. Introducing the Data Mining Add-In for Excel
    2. Installing the Data Mining Add-In
    3. Locating the Table Analysis Tools
    4. Creating a SSAS Connection
    5. Preparing Data with PowerPivot
    6. Mining Some Data
    7. Taking Data Mining to the Cloud with Predixion Software
    8. Summary
  18. CHAPTER 11: Tips, Tricks, and Traps
    1. PowerPivot Annoyances
    2. User Experience for PowerPivot Solutions
    3. Tuning PowerPivot Performance
    4. Summary
  19. Index