Microsoft Business Intelligence Tools for Excel Analysts (2014)

PART I: Leveraging Excel for Business Intelligence

Chapter 2: PivotTable Fundamentals

In This Chapter

· Creating PivotTables

· Customizing PivotTable fields, formats, and functions

· Using slicers to filter data

· Understanding the internal Data Model

As you gain an understanding of Microsoft’s BI tools, it becomes clear that PivotTables are an integral part of delivering business intelligence. Whether you’re working with Power Pivot (Chapters 3 and 4), Power View (Chapter 5), or even Power Map (Chapter 6), you eventually have to utilize some form of PivotTable structure to make those tools deliver the final solution to your audience.

If you’re new to PivotTables, this chapter gives you the fundamental understanding you need to continue exploring Microsoft’s BI tool set. If you’re already familiar with PivotTables, we recommend you skim the “Understanding the Internal Data Model” section later in this chapter. The internal Data Model is a feature introduced in Excel 2013 that essentially allows Power Pivot to run natively in Excel.

You can find the example file for this chapter on this book’s companion Web site at www.wiley.com/go/bitools in the workbook named Chapter 2 Samples.xlsx.

Introducing the PivotTable

A PivotTable is a tool that allows you to create an interactive view of your source data (commonly referred to as a PivotTable report). A PivotTable can help transform endless rows and columns of numbers into a meaningful presentation of data. You can easily create groupings of summary items: For example, combine Northern Region totals with Western Region totals, filter that data using a variety of views, and insert special formulas that perform new calculations.

PivotTables get their name from your ability to interactively drag and drop fields within the PivotTable to dynamically change (or pivot) the perspective, giving you an entirely new view using the same source data. You can then display subtotals and interactively drill down to any level of detail that you want. Note that the data itself doesn't change, and is not connected to the PivotTable. A PivotTable is well suited to a dashboard because you can quickly update the view of your PivotTable by changing the source data that it points to. This allows you to set up both your analysis and presentation layers at one time. You can then press a button to update your presentation.

Anatomy of a PivotTable

A PivotTable is comprised of four areas: Values, Rows, Columns, and Filters, as shown in Figure 2-1. The data you place in these areas defines both the use and presentation of the data in your PivotTable. In the following sections, we discuss the function of each area.

Figure 2-1: The four areas of a PivotTable.

Values area

The Values area allows you to calculate and count the source data. It is the large rectangular area below and to the right of the column and row headings. In this example, the Values area contains a sum of the values in the Sales Amount field.

The data fields that you drag and drop here are typically those that you want to measure — fields, such as the sum of revenue, a count of the units, or an average of the prices.

Rows area

Dragging a data field into the Rows area displays the unique values from that field down the rows of the left side of the PivotTable. The Rows area typically has at least one field, although it’s possible to have no fields.

The types of data fields that you would drop here include those that you want to group and categorize, such as products, names, and locations.

Columns area

The Columns area contains headings that stretch across the top of columns in the PivotTable. In this example, the Columns area contains the unique list of business segments.

Placing a data field into the Columns area displays the unique values from that field in a column-oriented perspective. The Columns area is ideal for creating a data matrix or showing trends over time.

Filters area

At the top of the PivotTable, the Filters area is an optional set of one or more drop-down controls. The Filters area contains the Region field, and the PivotTable is set to show all regions.

Placing data fields into the Filters area allows you to change the views for the entire PivotTable based on your selection. The types of data fields that you’d drop here include those that you want to isolate and focus on; for example, region, line of business, and employees. Data fields dropped into this area are commonly referred to as filter fields.

Creating the basic PivotTable

Now that you have a good understanding of its structure, follow these steps to create your first PivotTable:

1. Click any single cell inside your source data (the table you use to feed the PivotTable).

2. On the Insert tab, click the PivotTable button's drop-down list and choose PivotTable.

The Create PivotTable dialog box opens, as shown in Figure 2-2.

Figure 2-2: The Create PivotTable dialog box.

3. Specify the location of your source data.

4. Specify the worksheet where you want to put the PivotTable.

The default location for the new PivotTable is New Worksheet. This means your PivotTable is placed in a new worksheet within the current workbook. If you want to add your PivotTable to an existing worksheet, select Existing Worksheet and specify the worksheet in which you want to place the PivotTable.

5. Click OK.

At this point, you have an empty PivotTable report on a new worksheet, with the PivotTable Field pane next to it, as shown in Figure 2-3. You find out how to populate your PivotTable using this pane in the next section.

Figure 2-3: The PivotTable Fields List pane.

Laying out the PivotTable

You can add fields to the PivotTable by dragging and dropping the field names to one of the four areas found in the PivotTable Fields list — Filters, Columns, Rows, and Values.

If you don’t see the PivotTable Fields List pane, right-click anywhere inside the PivotTable and select Show Field List. Alternatively, with your PivotTable selected, click the Field List icon in the Show group on the Options tab of the Ribbon.

Before you start dropping fields into the various areas, ask yourself two questions: “What am I measuring?” and “How do I want to see it?” The answers to these questions guide you in determining which fields go where.

Suppose you want to measure the dollar sales by market. You need to work with the Sales Amount and Market fields.

The best way to view that data is for the markets to go down the left side of the report and the sales amount to be calculated next to each market. You need to add the Market field to the Rows area, and the Sales Amount field to the Values area. Follow these steps to do so:

1. In the field list, select the Market field (see Figure 2-4).

Now that you have regions in your PivotTable, it’s time to add the dollar sales.

Figure 2-4: Select the Market field to add it to the field selector list.

Placing a check mark next to any field that is non-numeric (text or date) automatically places that field into the Rows area of the PivotTable. Placing a check mark next to any field that is numeric automatically places that field in the Values area of the PivotTable.

2. In the field list, select the Sales Amount field (see Figure 2-5).

Figure 2-5: Add the Sales Amount field.

When you add new fields, you may find it difficult to see all the fields in the box for each area. You can expand the PivotTable Fields List pane by clicking and dragging the borders of the pane.

As you can see, you have just analyzed the sales for each market in just a few steps! That’s an amazing feat considering you start with over 60,000 rows of data. With a little formatting, this modest PivotTable can become the starting point for a dashboard or report.

Modifying the PivotTable

Here’s the wonderful thing about PivotTables: For your data model, you can add as many analysis layers as you like by changing or rearranging the fields in your source data table. Say you want to show the dollar sales each market earned by business segment. Because your PivotTable already contains the Market and Sales Amount fields, all you have to add is the Business Segment field.

Click anywhere in your PivotTable to open the PivotTable Fields List pane and then select the Business Segment field to add it to the Rows area. Figure 2-6 shows what your PivotTable now looks like.

What if this layout doesn’t work for you? Maybe you want to see business segments listed at the top of the PivotTable results. No problem. Simply drag the Business Segment field from the Rows area to the Columns area. As shown in Figure 2-7, this instantly restructures the PivotTable to your specifications.

Figure 2-6: Adding a new analysis layer to your data model is as easy as selecting another field.

Figure 2-7: Your business segments are now column oriented.

Changing the PivotTable view

You may frequently be asked to produce reports for one particular region, market, product, and so on. Instead of spending hours building separate PivotTables for every possible scenario, you can leverage PivotTables to help create multiple views of the same data. For example, you can create a region filter in your PivotTable.

Click anywhere in your PivotTable to open the PivotTable Fields List pane and then drag the Region field to the Filters area. A drop-down control is added to your PivotTable, as shown in Figure 2-8. You can then use this control to view one particular region at a time.

Figure 2-8: Add the Region field to view data for a specific geographic area.

Updating your PivotTable

Your data may change and grow with newly added rows and columns. To update the PivotTable, right-click inside your PivotTable and select Refresh.

Sometimes, the source data that feeds your PivotTable changes in structure. For example, you may want to add or delete rows or columns from your data table. These types of changes affect the range of your data source, not just a few data items in the table.

In this case, a simple update of your PivotTable data won’t do. You have to update the range that is captured by the PivotTable. Here’s how:

1. Click anywhere inside your PivotTable.

Whenever your PivotTable is selected, the Ribbon activates the PivotTable Tools tabs for you to work with your PivotTable.

2. On the Analyze tab, click the Change Data Source button.

The Change PivotTable Data Source dialog box opens, as shown in Figure 2-9.

Figure 2-9: Select the new range that feeds your PivotTable.

3. Change the range selection to include any new rows or columns.

4. Click OK.

Customizing Your PivotTable

PivotTables often need to be tweaked to achieve the look and feel you want. In this section, we cover some of the ways that you can customize your PivotTables to suit your dashboard’s needs.

Changing the PivotTable layout

Excel 2013 gives you a choice in the layout of your data in a PivotTable. The three layouts, shown side by side in Figure 2-10, are Compact Form, Outline Form, and Tabular Form. Although no layout stands out as being better than another, most people prefer the Tabular Form layout because it’s easiest to read and most people who have seen PivotTables are used to it.

The layout you choose not only affects the look and feel of your reporting mechanisms, but it may also affect the way you build and interact with any dashboard models based on your PivotTables.

Changing the layout of a PivotTable is easy. Follow these steps:

1. Click anywhere inside your PivotTable.

2. On the Design tab of the Ribbon, click the Report Layout icon’s drop-down menu and choose the layout you like.

Renaming the fields

Every field in your PivotTable has a name. The fields in the rows, columns, and filter areas inherit their names from the data labels in your source data. For example, the fields in the Values area are given a name, such as Sum of Sales Amount.

Figure 2-10: The three layouts for a PivotTable report.

You might prefer the name Total Sales instead of a default name like Sum of Sales Amount. If so, you can change a field name using the following steps:

1. Right-click any value within the target field and select Value Field Settings.

For example, if you want to change the name of the field Sum of Sales Amount, right-click any value under that field.

The Value Field Settings dialog box opens, as shown in Figure 2-11.

Figure 2-11: Use the Custom Name box to change the name.

2. Type the new name in the Custom Name box.

3. Click OK.

If you use the same name of the data label that you specified in your source data, you receive an error. In our example, if you try to rename the Sum of Sales Amount field to Sales Amount, you get an error message. To avoid this, you can add a space to the end of any field name. Excel considers Sales Amount (followed by a space) to be different from Sales Amount. This way you can use the name you want, and no one will notice any difference.

Formatting numbers

You can format numbers in a PivotTable to fit your needs (such as currency, percent, or number). You control the numeric formatting of a field using the Value Field Settings dialog box. Here’s how:

1. Right-click any value within the target field and choose one of the following:

· To format a value field: Select Value Field Settings, which opens the Value Field Setting dialog box.

· To format a number field: Select Number Format, which opens the Format Cells dialog box.

2. Indicate the number format you want, just as you normally would on your worksheet.

3. Click OK.

After you set a new format for a field, the applied formatting remains even if you refresh or rearrange your PivotTable.

Changing summary calculations

When you create your PivotTable, Excel, by default, summarizes your data by either counting or summing the items. You can also choose other functions, such as Average, Min, and Max. In all, 11 options are available:

· Sum: Adds all numeric data.

· Count: Counts all data items within a given field, including numeric-, text-, and date-formatted cells.

· Average: Calculates an average for the target data items.

· Max: Displays the largest value in the target data items.

· Min: Displays the smallest value in the target data items.

· Product: Multiplies all target data items.

· Count Nums: Counts only the numeric cells in the target data items.

· StdDevP and StdDev: Calculates the standard deviation for the target data items. Use StdDevP if your data source contains the complete population. Use StdDev if your data source contains a sample of the population.

· VarP and Var: Calculates the statistical variance for the target data items. Use VarP if your data contains a complete population. If your data contains only a sampling of the complete population, use Var to estimate the variance.

To change the summary calculation for any given field, follow these steps:

1. Right-click any value within the target field and select Value Field Settings.

The Value Field Settings dialog box opens. Refer to Figure 2-11.

2. Select the type of calculation you want to use from the list of calculations.

3. Click OK.

A single blank cell causes Excel to count instead of sum. If all the cells in a column contain numeric data, Excel chooses Sum. If just one cell is either blank or contains text, Excel chooses Count. Be sure to pay attention to the fields that you place into the Values area of the PivotTable. If the field name starts with Count Of, Excel counts the items in the field instead of summing them.

Suppressing subtotals

Each time you add a field to your PivotTable, Excel adds a subtotal for that field. However, there may be times when subtotals don’t make sense or hinders your PivotTable report. For example, Figure 2-12 shows a PivotTable where the subtotals hide the data.

Figure 2-12: Subtotals sometimes muddle the data you’re trying to show.

Here's how to remove subtotals:

· Remove all subtotals at one time: Click anywhere inside your PivotTable. On the Design tab, click the Subtotals drop-down list and select Do Not Show Subtotals. Figure 2-13 shows a report without any subtotals.

Figure 2-13: A report without subtotals.

· Remove the subtotals for only one field: Right-click any value within the target field and choose Field Settings. Select None under the Subtotals area and click OK.

· Remove grand totals: Right-click anywhere in your PivotTable and select PivotTable Options. On the Totals & Filters tab, deselect the Show Grand Totals for Rows and Show Grand Totals for Columns options. Click OK.

Hiding and showing data items

A PivotTable summarizes and displays all the information in your source data. However, you may want to inhibit certain data items from being included in your PivotTable summary. In these situations, you can choose to hide a data item.

In terms of PivotTables, hiding doesn’t mean preventing the data item from displaying on the dashboard; hiding a data item also prevents it from being factored into the summary calculations.

The PivotTable shown in Figure 2-14 shows sales amounts for all Business Segments by Market. Suppose you want to show totals without taking sales from the Bikes segment into consideration.

Figure 2-14: We want to remove Bikes from this analysis.

You need to hide the Bikes segment. Deselect Bikes from the Business Segment drop-down list (see Figure 2-15).

Figure 2-15: Removing the check mark from the Bike items hides the Bikes segment.

Click OK, and the PivotTable instantly recalculates, leaving out the Bikes segment. As shown in Figure 2-16, the Market total sales now reflect the sales without Bikes.

Figure 2-16: Segment analysis without the Bikes segment.

You can just as quickly reinstate all hidden data items for the field. Choose Select All from the Business Segment drop-down list. (Refer to Figure 2-15.) All the segments are now showing again.

Hiding or showing items without data

By default, your PivotTable shows only items that have data. This may cause unintended problems for your data.

Look at Figure 2-17, which shows a PivotTable with the SalesPeriod field in the Rows area and the Region field in the Filters area. Note that the Region field is set to (All), and every sales period appears in the report.

Figure 2-17: All sales periods are showing.

If you display only Europe in the filter area, a portion of all the sales periods now show (see Figure 2-18).

Figure 2-18: Filtering for the Europe region hides some of the sales periods.

But displaying only those items with data could cause trouble if you plan on using this PivotTable as the source for your charts or other dashboard components. With that in mind, it isn’t ideal if half the year disappears each time a customer selects Europe.

Here’s how you can prevent Excel from hiding pivot items without data:

1. Right-click any value within the target field and choose Field Settings.

In this example, the target field is the SalesPeriod field.

2. On the Layout & Print tab, select Show Items with No Data.

3. Click OK.

All the sales periods appear whether the selected region had sales that period or not, as shown in Figure 2-19.

Figure 2-19: All sales periods display even if there is no data.

Now that you’re confident that the structure of the PivotTable is locked, you can use it as the source for all charts and other components in your dashboard.

Sorting your PivotTable

By default, items in each pivot field are sorted in ascending order based on the item name. Excel enables you to change the sort order of the items in your PivotTable.

Like many actions you can perform in Excel, there are several different ways you can sort data within a PivotTable. The easiest way, and the way that’s used most often, is to apply the sort directly in the PivotTable. Right-click any value within the target field (the field you need to sort), choose Sort, and then select the sort direction. The changes take effect immediately and remain while you work with your PivotTable.

Understanding Slicers

Slicers allow you to filter your PivotTable, similar to the way Filter fields filter a PivotTable. As discussed in the “Anatomy of a PivotTable” section earlier in this chapter, Filter fields are those placed in the Filters area, allowing your users to interactively filter for specific data items. As useful as Filter fields are, they have a couple of drawbacks.

· Filter fields are not cascading filters. Filters don’t work together to limit selections when needed. Look at the left side of Figure 2-20, for example. You can see that the Region filter is set to North. However, the Market filter still allows you to select markets that are clearly not in the North region (California, for example). Because the Market filter is not in any way limited based on the Region Filter field, you could select a market that yields no data because it is not in the North region.

Slicers respond to one another. Shown on the right side of Figure 2-20, the Market slicer visibly highlights the relevant markets when the North region is selected. The rest of the markets are muted, signaling they are not part of the North region.

Figure 2-20: Default PivotTable Filter fields do not work together (left); slicers work together to show relevant data items (right).

· Filter fields don’t provide an easy way to tell what exactly is being filtered when you select multiple items. The left side of Figure 2-21 shows an example of this. The Region filter has been limited to three regions: Midwest, North, and Northeast. However, the Region filter shows (Multiple Items). By default, Filter fields show (Multiple Items) when you select more than one item. The only way to tell what has been selected is to click the drop-down list. You can imagine the confusion on a printed version of this report, where there is no way to click to see which data items make up the numbers on the page.

When selecting multiple items in a slicer, you can easily see what’s been chosen. On the right side of Figure 2-21, you can see that the PivotTable is being filtered by the Midwest, North, and Northeast regions — no more (Multiple Items).

Figure 2-21: Filter fields show the words “(Multiple Items)” when multiple selections are made (left); slicers do a better job at displaying multiple items (right).

Creating a standard slicer

To create a slicer, follow these steps:

1. Place your cursor anywhere inside your PivotTable.

2. On the Analyze tab, click the Insert Slicer button.

The Insert Slicers dialog box opens.

3. Select the dimensions you want to filter.

4. Click OK.

After the slicers are created, click the filter values to filter your PivotTable. As shown in Figure 2-22, clicking Midwest in the Region slicer not only filters your PivotTable, but the Market slicer responds by highlighting the markets that belong to the Midwest region.

Figure 2-22: Select the dimensions you want filtered using slicers.

To select multiple values, press the Ctrl key while selecting the filters.

To clear the filtering on a slicer, simply click the Clear Filter icon on the target slicer (see Figure 2-23).

Figure 2-23: Clearing the filters on a slicer.

Formatting slicers

If you’re using slicers in a dashboard, you’ll want to do some formatting to make sure your slicers match the theme and layout of your dashboard. Here are a few common formatting adjustments you can make to your slicers.

Size and placement

A slicer behaves like a standard Excel shape object in that you can move it around and adjust its size by clicking it and dragging its position points. Or right-click the slicer and select Size and Properties.

You can then adjust the size of the slicer in the Format Slicer pane shown in Figure 2-24. You can specify how the slicer should behave when cells are shifted and specify whether the slicer should be shown when printed.

Figure 2-24: Use the Slicer Format pane to adjust how the slicer behaves in relation to the worksheet it’s on.

Data item columns

By default, all slicers are created with one column of data items. You can change this by right-clicking the slicer and selecting Size and Properties. In the Position and Layout section of the Format Slicer pane, you can specify the number of columns in the slicer. Adjust the number to 2 (as shown in Figure 2-25) and the data is displayed in two columns, adjust the number to 3 and the data is displayed in three columns, and so on.

Slicer color and style

To change the color and style of your slicer, click it, and then select a style from the Slicer Style gallery on the Slicer Tools Options tab. The default styles available suit a majority of dashboards, but if you want more control over the color and style of your slicer, you can click the New Slicer Style button on the lower left-hand corner. You can then specify the detailed formatting of each part of the slicer.

Other slicer settings

Right-click your slicer and select Slicer Settings to open the Slicer Settings dialog box. With this dialog box, you can control the look of your slicer’s header, how your slicer is sorted, and how filtered items are handled.

With minimal effort, your slicers can be integrated nicely into your dashboard layout. Figure 2-26 shows how two slicers and a chart work together as a cohesive dashboard component.

Figure 2-25: Adjust the Number of Columns property to display the slicer data items in more than one column.

Figure 2-26: With a little formatting, slicers can be made to adopt the look and feel of your overall dashboard.

Controlling multiple PivotTables with one slicer

Another advantage you gain with slicers is that each slicer can be tied to more than one PivotTable. That is, any filter you apply to your slicer can be applied to multiple PivotTables.

To connect your slicer to more than one PivotTable, right-click the slicer and select Report Connections. In the Report Connections dialog box, place a check mark next to any PivotTable that you want to filter using the current slicer. Click OK when you're done.

At this point, any filter you apply to your slicer is applied to all the connected PivotTables. Controlling the filter state of multiple PivotTables is a powerful feature; especially in dashboards that run on multiple PivotTables.

Creating a Timeline Slicer

The Timeline slicer works in the same way a standard slicer does in that it lets you filter a PivotTable using a visual selection mechanism instead of the old Filter fields. The difference is the Timeline slicer is designed to work exclusively with date fields, providing an excellent visual method to filter and group the dates in your PivotTable.

To create a Timeline slicer, your PivotTable must contain a field where all the data is formatted as a date. It’s not enough to have a column of data that contains a few dates. All the values in your date field must be a valid date, and formatted as such.

To create a Timeline slicer, follow these steps:

1. Place your cursor anywhere inside your PivotTable.

2. On the Analyze tab, click the Insert Timeline icon.

The Insert Timelines dialog box opens, showing you all the available date fields in the chosen PivotTable.

3. Select the date fields for which you want to create the timeline.

4. Click OK.

After you create your Timeline slicer, you can filter the data in your PivotTable using this dynamic data selection mechanism. Figure 2-27 shows how selecting Mar, Apr, and May in the Timeline slicer automatically filters the pivot chart.

Figure 2-27 also illustrates how you can expand the slicer range with the mouse to include a wider range of dates in your filtered numbers by clicking and dragging the pull handles at the edges of the selected months.

Want to quickly filter your PivotTable by quarters? Well that’s easy with a Timeline slicer. Click the time period drop-down list and select Quarters. As shown in Figure 2-28, you also have the option of switching to Years, Months, or Days, if needed.

Timeline slicers are not backward-compatible, meaning they are only usable in Excel 2013. If you open a workbook with Timeline slicers in Excel 2010 or previous versions, the Timeline slicers are disabled.

Figure 2-27: Click a date selection to filter your PivotTable.

Figure 2-28: Quickly switch between Years, Quarters, Months, or Days.

Understanding the Internal Data Model

Excel 2013 introduces several new BI features that we discuss in this book, such as Power Pivot, Power Map, and Power View. These features run on the internal Data Model found in Excel 2013. The internal Data Model is an in-memory analytics engine that allows you to store disparate data sources in a kind of OLAP cube within Excel. OLAP is a category of data warehousing that allows you to mine and analyze vast amounts of data with ease and efficiency. Every workbook has one internal Data Model that allows you to analyze disparate data sources like never before.

The idea behind the Data Model is simple. Say you have two tables — an Orders table and a Customers table. The Orders table has basic information about invoices (Customer Number, Invoice Date, and Revenue). The Customers table has basic information like Customer Number, Customer Name, and State.

If you wanted to analyze revenue by state, you would have to join the two tables and aggregate the Revenue field in the Orders table by the State field in the Customers table.

In the past, to do this you would have to go through a series of gyrations involving VLookups, SumIfs, or other formulas. With the Excel 2013 data model, however, you can simply tell Excel how the two tables are related (they both have a customer number) and then pull them into the internal Data Model. The Excel Data Model then builds an analytical cube based on that customer number relationship and exposes the data through a PivotTable. With the PivotTable, you can create the aggregation by state with a few clicks of the mouse.

Building out your first Data Model

Imagine you have the Transactions table. On another worksheet, you have a Generators table that contains location information about each generator. See Figure 2-29.

Convert your data ranges to tables

The first step in building your data model is to convert your separate data ranges to named Excel tables. Converting a range to a table ensures that the internal Data Model recognizes it as an actual data source.

1. Click anywhere inside the Transactions data table and press Ctrl+T.

The Create Table dialog box opens.

2. Ensure that the range for the table is correct and click OK.

3. On the Table Tools Design tab, type a name in the Table Name field.

Choose a name that you can recognize as belonging to the table when adding it to the internal Data Model.

4. Repeat Steps 1 through 3 for each of the data ranges you want to import into the internal Data Model.

In this scenario, you want to also convert the Generators table to a named Excel table.

Figure 2-29: Two tables: transactions by generator number (top) and location information on each generator (bottom).

Add your tables to the internal Data Model

Each 2013 workbook has an internal Data Model that (by default) is exposed as a connection called ThisWorkbookDataModel when you add data sources to it. You can add your newly created tables to the internal Data Model using the Workbook Connections dialog box.

1. Click the Data tab on the Ribbon and click the Connections button.

2. In the Workbook Connections dialog box, click the drop-down arrow next to the Add button and select Add to the Data Model.

Excel opens the Existing Connections dialog box shown in Figure 2-30.

Figure 2-30: Select a table to add and click Open.

3. Click the Tables tab, select your first table, and then click Open.

4. Repeat Steps 2 and 3 for each table you want added to the internal Data Model.

After adding all your tables, the Workbook Connections dialog box shows a connection called ThisWorkbookDataModel, listing all the data sources associated with it.

Any changes made to the tables (such as adding or deleting records or columns) are automatically captured in the internal Data Model. There’s no need to perform any sort of refresh action.

Build relationships for the tables in the internal Data Model

Although your data now exists in the internal Data Model, Excel does not inherently know how your tables relate to one another. For example, both tables have a column called Generator_ID (see Figure 2-29). This column is the key that connects the two tables, allowing you to match transactions with customer location. You have to explicitly define this relationship before Excel recognizes how to handle the data in the Data Model.

1. Click the Data tab on the Ribbon and click the Relationships button.

The Manage Relationships dialog box opens.

2. Click the New button.

The Create Relationship dialog box opens, as shown in Figure 2-32.

3. Select the tables and fields that define the relationship.

In Figure 2-31, the Transactions table has a Generator_ID field. It is related to the Generators table via the Generator_ID field.

4. Click OK.

Figure 2-31: Create the relationships between your tables, defining each table and the associated fields.

You return to the Manage Relationships dialog box where you can add, delete, and edit relationships.

In Figure 2-31, notice that the lower right-hand drop-down list is called Related Column (Primary). The term Primary means that the internal Data Model uses this field from the associated table as the primary key. Every relationship must have a field you designate as the primary key. Primary key fields are necessary in the Data Model to prevent aggregation errors and duplications. In that light, the Excel Data model must impose some strict rules around the primary key. You cannot have any duplicates or null values in a field being used as the primary key. So the Generators table (in the scenario in Figure 2-31) must have all unique values in its Generator_ID field; with no blanks or null values. This is the only way Excel can ensure data integrity when joining multiple tables.

Using your Data Model in a PivotTable

After you have filled your internal Data Model, you can start using it. Later, of course, you learn how to leverage it with Power View (Chapter 5).

Follow these steps to leverage the Data Model in PivotTables to analyze the data within:

1. Click the Insert tab and click the PivotTable icon.

2. In the Create PivotTable dialog box, select the Use an External Data Source option and click the Choose Connection button.

The Existing Connections dialog box opens.

3. Click the Tables tab and select Tables in Workbook Data Model. Click Open.

4. Click OK in the Create PivotTable dialog box.

After the PivotTable is created, the PivotTable Fields list shows each individual Table in the internal Data Model (see Figure 2-32).

Figure 2-32: PivotTables that use the internal Data Model as the source show all the tables within the Data Model.

With a Data Model-driven PivotTable, you have the ability to merge disparate data sources into one analytical engine. Figure 2-33 demonstrates how you can build a view using data fields from the different tables in the Data Model.

Figure 2-33: With a Data Model-driven PivotTable, you can analyze data using the fields for each table in the Data Model.