Data Model Walkthrough

Have you heard people talking about Power Pivot and the data model, but you aren’t exactly sure what they are or how to use them? Well, in this article, we’ll use them to build a basic PivotTable.

Before we jump into Excel, here’s some super-fast background. Although Excel is used for many different things, workbooks are often used for reporting. We frequently aggregate and summarize data that comes from somewhere else. That is, we export data from some system, like an accounting system, and then summarize it with a PivotTable. Workbooks used to summarize exported data can potentially be improved by using Power Pivot and the Data Model.

A quick note about names: Excel users may wind up using various names—for example, Power Pivot, the Data Model, DAX and Power BI—to describe the same underlying technology. For the purposes of this article, we’ll use Data Model to describe the data tables, relationships and formulas that are collectively used as the source of a PivotTable. We’ll use Power Pivot to refer to the Excel user interface that allows us to define the Data Model. DAX is the formula engine and Power BI is a standalone desktop application and service that provides an option outside of Excel to use the technology.

Not all versions of Excel have Power Pivot, and a complete list of those in which it’s available can be found online from Microsoft.

Walkthrough

We’ll create a simple report that can easily be created with a traditional PivotTable, but we’ll build it using Power Pivot instead. This example is designed to walk through the basic mechanics but the technology and capabilities go far beyond this simple example. Download the SampleFile.xlsx if you’d like to work along.

Step 1: Activate Power Pivot

First, we need to activate the Power Pivot add-in, which depends on the version of Excel you have. For example, if you have Excel 2016 for Windows, you just click the green Manage Data Model icon in the Data ribbon tab. The first time you click it, you’ll see a dialog asking you to enable it (Figure 1).

Figure 1

You can also go into the Excel Options dialog, select Add-ins, COM Add-ins and check Microsoft Power Pivot for Excel. You may need to download and install the free Power Pivot add-in from Microsoft (see link above). Either way, once you’ve activated Power Pivot, you’ll have a new Power Pivot tab in your Excel ribbon.

Step 2: Load the Data Model

Now, we need to get one or more tables loaded into the Data Model. There are numerous ways to do this depending on the location, including using Power Query to grab and shape the data, loading a standard Excel table or using a direct connection to a database or other external data source. For now, we’ll load a single Excel table into the Data Model. We do this by selecting any cell within the Table and then selecting Power Pivot > Load to Data Model.

At this point, we’ll see the table added to the Data Model and we can view it in the Power Pivot window (Figure 2).

Figure 2

The Power Pivot window is our primary interface to the Data Model. If you need it, you can add additional tables (Home > Get External Data), define relationships (Design > Relationships), create calculated columns (Design > Columns), define or create a date table (Design > Calendars) and much more. In this example we’re keeping it simple; we’ll just build a PivotTable that summarizes the amount by account.

Step 3: Create the PivotTable

We click the Home > PivotTable command in the Power Pivot window and select New Worksheet in the resulting Create PivotTable dialog.

Building the PivotTable is performed using the same steps used to build traditional PivotTables, namely, by inserting fields into the desired layout areas. In our case, we’ll insert the Account field into the Rows area by checking the field’s checkbox, and we’ll insert the Amount field into the Values area by checking its checkbox. The resulting report is shown in Figure 3.

If needed, we can open the Power Pivot window by clicking the Power Pivot > Manage command within Excel.

This is a simple example designed to introduce the Data Model and the Power Pivot window. The technology that lies within will help you build reports from large tables, multiple tables, and write advanced calculated columns and measures. Hopefully, this article provides an easy intro—and you decide to get in and explore it further. And remember, Excel rules!