Microsoft Excel 365 Bible

CHAPTER 32Introducing Power Pivot

IN THIS CHAPTER

• Getting started with Power Pivot
• Linking to Excel data
• Managing relationships
• Loading data from other data sources

Recognizing the importance of the business intelligence (BI) revolution and the place Excel holds within it, Microsoft has made substantial investments in improving Excel's BI capabilities, specifically focusing on Excel's self-service BI capabilities and its ability to manage and analyze information better from the increasing number of available data sources.

The key product of this endeavor is Power Pivot. With Power Pivot comes the ability to set up relationships between large, disparate data sources and to merge data sources with hundreds of thousands of rows into one analytical engine within Excel.

In this chapter, you'll get an overview of those capabilities, exploring the key features, benefits, and capabilities of Power Pivot.

Understanding the Power Pivot Internal Data Model

At its core, Power Pivot is essentially a SQL Server Analysis Services engine made available through an in-memory process that runs directly within Excel. The technical name for this engine is the xVelocity analytics engine. However, in Excel, it's referred to as the internal data model.

Every Excel workbook contains an internal data model, which is a single instance of the Power Pivot in-memory engine. The following sections outline how to leverage the Power Pivot internal data model to import and integrate disparate data ...