Excel 2019 Bible

783

C H A P T E R

35

Introducing Power Pivot

IN THIS CHAPTER

Getting started with Power Pivot

Linking to Excel data

Managing relationships

Loading data from other data sources

R

ecognizing 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 capabili-

ties, 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 rela-

tionships between large, disparate data sources and to merge data sources with hundreds of thou-

sands of rows into one analytical engine within Excel.

Starting with the release of Excel 2013, Microsoft has incorporated Power Pivot directly into Excel.

This means the powerful capabilities of Power Pivot are available to you right out of the box!

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. This section outlines how to leverage the Power Pivot internal data model

to import and integrate disparate data sources.

Activating the Power Pivot Ribbon

The Power Pivot Ribbon interface is available only when you activate it. It’s important to note that

the Power Pivot add-in does not install with every edition of Office. For example, if you have the

Office Home edition, you will not be able to see or activate the Power Pivot add-in; thus, you will

not have access to the Power Pivot Ribbon interface.

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.