Going All In with Excel 2013

  • Article

PowerPivot for Excel, first introduced with SQL Server 2008 R2 in April of 2010, opened groundbreaking new options to bring BI to a broad audience. Where traditional heavy-weight BI solutions comprised of ETL pipelines, data warehouses, and cubes once ruled, PowerPivot changed the game by empowering information workers to build their own BI solutions in the familiar Office application environment of Excel. With Excel 2010 on the desktop, all it takes is to download and install the PowerPivot add-in free of charge and you can turn data into actionable insights and find the answers you need without having to wait weeks and months for an IT department to build a solution for you. And with Excel 2013, we are pushing the boundaries even further—partly by moving the entry point for creating data models to Excel so that users can natively create and enjoy the power of a data models and partly by offering deeper integration of the PowerPivot functionality.

 

Integration Points

Let’s take a step back and briefly look at the PowerPivot add-in for Excel 2010. With Office 2010, PowerPivot for Excel debuted as a standalone add-in, downloadable from https://www.microsoft.com/en-us/bi/powerpivot.aspx. As part of the add-in, PowerPivot installs the xVelocity in-memory analytics engine, which provides the foundation to create tabular data models and import hundreds of millions of rows of data from multiple data sources. The PowerPivot add-in extends the data model whenever you add new tables, create relationships between tables, add calculated columns, create measures, and so forth. Excel then transparently queries the data model when performing analysis using PivotTables and PivotCharts. In Excel 2010, the PowerPivot add-in provides its own modeling window and field list separate from the Excel user interface.

With Excel 2013, this picture changes significantly. The xVelocity in-memory analytics engine now ships with Excel and so does the PowerPivot add-in. It’s important to distinguish between these two integration points because they affect at different levels how Excel provides BI features and capabilities to the user:

  • xVelocity Integration    The xVelocity engine, now natively integrated into Excel, offers support for additional types of data sources as well as a new user interface for creating and managing relationships. These improvements put together enable information workers to import data from multiple tables directly from the Data tab whereupon Excel essentially generates the Data Model under the covers. If tables are not imported in a single operation or if you want to mash up data from multiple tables in the current workbook, you can stay in the native Excel user interface and create relationships. This is a noteworthy improvement in terms of enabling information workers to perform analysis across multiple tables natively in Excel. The generated data model is seamlessly exposed though the Field List—and other UI elements to manage relationships and connections - enabling the user to naturally consume the model when building PivotTables and PivotCharts.
  • PowerPivot Add-In Integration   The PowerPivot add-in comes into play if you want to further enrich the data model. The native Excel user interface does not fully expose the actual data model to the user, the design goal for Excel being to hide the potential complexity of a data model for the non-expert user. Exposing the logical data model to the user remains the purpose of the add-in’s dedicated modeling window.

 

Comparison of modeling capabilities in Excel and PowerPivot

How do all the pieces play together? Well, now that Excel installs the xVelocity engine and is generating the data model out of the box, the PowerPivot add-in no longer needs to operate with a separate engine or data model. The PowerPivot add-in can simply assume that the xVelocity engine exists and can work directly against the data model as a shared Excel object. In this way, the PowerPivot add-in has shifted from being a separately installed add-in, required for any user who wants perform analysis across multiple tables, to a build-in Excel feature offering advanced BI modeling capabilities for the more experienced or demanding user who wants to further enrich the data model.

So what can the user do natively in Excel versus using the PowerPivot add-in?

Using the xVelocity engine without the PowerPivot add-in:

Import millions of rows from multiple data sources:

  • Relationships that exist in the external data source can be detected and imported.
  • Filtering during multi-table import is not supported, meaning the user will not be able to filter to row and column level granularity as part of the import operation

Import OData data feeds

Create relationships between data from different sources, and between multiple tables

Create implicit calculated fields (previously called ‘implicit measures’) – calculations created automatically when you add a numeric field to the Values drop zone of the Field List

Manage data connections

 

Using the xVelocity with the PowerPivot add-in:

Row and column level filtering during data import:

  • The user can import data from multiple tables in both Excel and PowerPivot, but when importing data in PowerPivot, you can filter out unnecessary data to import just a subset.
  • You can also use the PowerPivot Add-In to later apply filters to tables you already imported in Excel

Rename tables and columns as you import data in PowerPivot

Manage the model and create relationships using drag and drop in Diagram View. Relationships created in Excel can be modified in PowerPivot and vice versa

Apply formatting to columns and calculations to be persisted as the user creates PivotTables, Charts and Power View reports

Enrich the data model using Data Analysis Expressions (DAX) to author your own calculations

Define key performance indicators (KPIs)

Create user-defined hierarchies to use throughout the workbook

Define perspectives to present different subsets of the model to various users when users connects to the model after the workbook is deployed to PowerPivot for SharePoint and to limit the display of model objects to the modeler in the PowerPivot window making it easier to work with larger data models

As mentioned, both the xVelocity engine and the PowerPivot add-in ship with Excel. However, there is one important difference to be aware of. The xVelocity engine is enabled by default. The PowerPivot add-in, on the other hand, is not. Before you can enjoy the richness of the PowerPivot add-in in Excel 2013, you need to enable it in the COM Add-In Manager.

Happy modeling!