Tutorial: PivotTable data analysis using a Data Model in Excel 2013

In less than an hour, you can build a PivotTable report in Excel that combines data from multiple tables. The first part of this tutorial steps you through data import and exploration. In the second half, you’ll use the Power Pivot add-in to refine the data model that lives behind the report, learning how to add calculations, hierarchies, and optimizations for Power View reporting.

Let’s start by importing some data.

  1. Download sample data (ContosoV2) for this tutorial. See Get sample data for DAX and Data Model tutorials for details. Extract and save the data files in a location that’s easily accessible, such as Downloads or My Documents.
  2. In Excel, open a blank workbook.
  3. Click Data > Get External Data > From Access.
  4. Go to the folder that contains the sample data files and select ContosoSales.
  5. Click Open. Because you are connecting to a database file that contains multiple tables, the Select Table dialog appears so that you can choose which tables to import.

Select Table dialog

  1. In Select Table, check Enable selection of multiple tables.
  2. Choose all of the tables and click OK.
  3. In Import Data, click PivotTable Report and click OK.

 Note    You might not have realized it yet, but you’ve just created a data model. The model is a data integration layer that’s created automatically when you import or work with multiple tables simultaneously in the same PivotTable report.

The model is mostly transparent in Excel, but you can view and modify it directly using the Power Pivot add-in. In Excel, the presence of a data model is evident when you see a collection of tables in the PivotTable Fields list. There are several ways to create a model. See Create a Data Model in Excel for details.

Explore data using a PivotTable

Exploring data is easy when you drag fields to the Values, Columns, and Rows areas on the PivotTable field list.

  1. In the field list, scroll down until you find the FactSales table.
  2. Click SalesAmount. Because this data is numeric, Excel automatically places SalesAmount in the Values area.
  3. In DimDate, drag CalendarYear to Columns.
  4. In DimProductSubcategory, drag ProductSubcategoryName to Rows.
  5. In DimProduct, drag BrandName to Rows, placing it beneath subcategory.

Your PivotTable should look similar to the following screen.

PivotTable showing sample data

With minimal effort, you now have a basic PivotTable that includes fields from four different tables. What made this task so simple were the pre-existing relationships among the tables. Because table relationships existed in the source, and because you imported all the tables in a single operation, Excel could recreate those relationships in the model.

But what if your data originates from different sources, or is imported at a later time? Typically, you can incorporate new data by creating relationships based on matching columns. In the next step, you’ll import additional tables and learn the requirements and steps for creating new relationships.

Add more tables

Learning how to set up table relationships requires that you have some additional, unconnected tables to work with. In this step, you’ll get the remaining data used in this tutorial by importing one additional database file and pasting data from two other workbooks.

Add product categories

  1. In the workbook open a new sheet. You’ll use it to store additional data.
  2. Click Data > Get External Data > From Access.
  3. Go to the folder that contains the sample data files and select ProductCategories. Click Open.
  4. In Import Data, select Table, and click OK.

Add geography data

  1. Insert another sheet.
  2. From the sample data files, open Geography.xlsx, place the cursor in A1, and then press Ctrl-Shift-End to select all of the data.
  3. Copy the data to the clipboard.
  4. Paste the data into the empty sheet you just added.
  5. Click Format as Table, choosing any style. Formatting the data as a table lets you name it, which will come in handy when you define relationships in a later step.
  6. In Format As Table, verify that My table has headers is selected. Click OK.
  7. Name the table Geography. In Table Tools > Design, type Geography in Table Name.
  8. Close Geography.xlsx to clear it from your workspace.

Add store data

  • Repeat the previous steps for the Stores.xlsx file, pasting its contents into an empty sheet. Name the table Stores.

You should now have four sheets. Sheet1 contains the PivotTable, Sheet2 contains ProductCategories, Sheet3 contains Geography, and Sheet4 contains Stores. Because you took the time to name each table, the next step, creating relationships, will be much simpler.

Use fields from the newly imported tables

You can immediately begin using fields from the tables you just imported. If Excel cannot determine how to incorporate a field into the PivotTable report, you’ll be asked to create a table relationship that associates the new table with one that is already part of the model.

  1. At the top of PivotTable Fields, click All to view the complete list of available tables.
  2. Scroll to the bottom of the list. That’s where you’ll find the new tables you just added.
  3. Expand Stores.
  4. Drag StoreName to the Filters area.
  5. Notice that Excel prompts you to create a relationship. This notification occurs because you’ve used fields from a table that is unrelated to the model.
  6. Click Create to open the Create Relationship dialog.
  7. In Table, choose FactSales. In the sample data you’re using, FactSales contains detailed sales and cost information about Contoso’s business, as well as keys to other tables, including store codes that are also present in the Stores.xlsx file you imported in the previous step.
  8. In Column (Foreign), choose StoreKey.
  9. In Related Table, choose Stores.
  10. In Related Column (Primary), choose StoreKey.
  11. Click OK.

Behind the scenes, Excel is building a Data Model that can be used throughout the workbook in any number of PivotTables, PivotCharts, or Power View reports. Fundamental to this model are table relationships that determine navigation and calculation paths used in a PivotTable report. In the next task, you’ll create relationships manually to connect the data you just imported.

Add relationships

You can systematically create table relationships for all new tables that you import. If you’re sharing the workbook with colleagues, having predefined relationships will be appreciated if they don’t know the data as well as you do.

When creating relationships manually, you will work with two tables at a time. For each table, you’ll choose columns that tell Excel how to look up related rows in another table.

Relate ProductSubcategory to ProductCategory

  1. In Excel, click Data > Relationships > New.
  2. In Table, choose DimProductSubcategory.
  3. In Column (Foreign), choose ProductCategoryKey.
  4. In Related Table, choose Table_ProductCategory.accdb.
  5. In Related Column (Primary), choose ProductCategoryKey.
  6. Click OK.
  7. Close the Manage Relationships dialog.

Add categories to the PivotTable

Although the Data Model has been updated to include additional tables and relationships, the PivotTable isn’t using them yet. In this task, you’ll add ProductCategory to the PivotTable Fields list.

  1. In PivotTable Fields, click All to show tables that exist in the Data Model.
  2. Scroll to the bottom of the list.
  3. In the Rows area, remove BrandName.
  4. Expand Table_DimProductCategories.accdb.
  5. Drag ProductCategoryName to the Rows area, placing it above ProductSubcategory.
  6. In PivotTable Fields, click Active to verify that the tables you just used are now actively used in the PivotTable.

Checkpoint: Review what you learned

You now have a PivotTable that includes data from multiple tables, several of which you imported in a subsequent step. To pull this data together, you had to create table relationships that Excel uses to correlate the rows. You learned that having columns that provide matching data is essential for looking up related rows. In the sample data files, all of the tables include a column that can be used for this purpose.

Although the PivotTable is functional, you’ve probably noticed several things that could be improved. The PivotTable Fields List seems to have extra tables (DimEntity) and columns (ETLLoadID) that are unrelated to Contoso’s business. And, we still haven’t integrated the Geography data.

Next up: View and extend your model with Power Pivot

In the next series of tasks, you’ll use the Microsoft Office Power Pivot in Microsoft Excel 2013 add-in to extend the model. You’ll find that you can create relationships more easily using the Diagram View the add-in provides. You’ll also use the add-in to create calculations and hierarchies, hide items that shouldn’t be appearing in the field list, and optimize the data for additional reporting.

 Note    The Power Pivot in Microsoft Excel 2013 add-in is available in Office Professional Plus. See Power Pivot in Microsoft Excel 2013 add-in for more information.

Add Power Pivot to the Excel ribbon by enabling the Power Pivot add-in.

  1. Go to File > Options > Add-Ins.
  2. In the Manage box, click COM Add-ins> Go.
  3. Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then click OK.

The ribbon now has a Power Pivot tab.

Add a relationship using Diagram View in Power Pivot

  1. In Excel, click Sheet3 to make it the active sheet. Sheet3 contains the Geography table you imported earlier.
  2. On the ribbon, click Power Pivot > Add to Data Model. This step adds the Geography table to the model. It also opens the Power Pivot add-in, which you’ll use to perform the remaining steps in this task.
  3. Notice that the Power Pivot window shows all the tables in the model, including Geography. Click through a couple of tables. In the add-in, you can view all of the data that your model contains.
  4. In the Power Pivot window, in the View section, click Diagram View.
  5. Use the slide bar to resize the diagram so that you can see all objects in the diagram. Notice that two tables are unrelated to the rest of the diagram: DimEntity and Geography.
  6. Right-click DimEntity and click Delete. This table is an artifact from the original database and is not needed in the model.
  7. Zoom in on Geography so that you can view all of its fields. You can use the slider to make the table diagram bigger.
  8. Notice Geography has GeographyKey. This column contains values that uniquely identify each row in the Geography table. Let’s find out if other tables in the model also use this key. If they do, we can create a relationship that connects Geography to the rest of the model.
  9. Click Find.
  10. In Find Metadata, type GeographyKey.
  11. Click Find Next several times. You’ll notice the GeographyKey shows up in the Geography table and in the Stores table.
  12. Reposition the Geography table so that it is next to Stores.
  13. Drag the GeographyKey column in Stores to the GeographyKey column in Geography. Power Pivot draws a line between the two columns, indicating the relationship.

In this task, you learned a new technique for adding tables and creating relationships. You now have a fully integrated model, with all tables connected and available to the PivotTable in Sheet1.

 Tip    In Diagram View, several table diagrams are fully extended, showing columns like ETLLoadID, LoadDate, and UpdateDate. These particular fields are artifacts from the original Contoso data warehouse, added to support data extraction and loading operations. You don’t need them in your model. To get rid of them, highlight and right-click the field, and click Delete.

Create a calculated column

In Power Pivot, you can use Data Analysis Expressions (DAX) to add calculations. In this task, you’ll calculate total profit, and add calculated column that references data values from other tables. Later, you’ll see how to use referenced columns to simplify your model.

  1. In the Power Pivot window, switch back to Data View.
  2. Rename the Table_ProductCategories accdb table to a friendlier name. You’ll be referencing this table in the following steps and a shorter name will make the calculations easier to read. Right-click the table name, click Rename, type ProductCategories, and then press Enter.
  3. Select the FactSales table.
  4. Click Design > Columns > Add.
  5. In the formula bar above the table, type the following formula. AutoComplete helps you type the fully qualified names of columns and tables, and lists the functions that are available. You can also just click the column and Power Pivot adds the column name to the formula.

= [SalesAmount] - [TotalCost] - [ReturnAmount]

  1. When you have finished building the formula, press Enter to accept the formula.

Values are populated for all the rows in the calculated column. If you scroll down through the table, you will see that rows can have different values for this column, based on the data that is in each row.

  1. Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type Profit, and then press Enter.
  2. Now select the DimProduct table.
  3. Click Design > Columns > Add.
  4. In the formula bar above the table, type the following formula.

= RELATED(ProductCategories[ProductCategoryName])

The RELATED function returns a value from a related table. In this case, the ProductCategories table includes the names of product categories, which will be useful to have in the DimProduct table when you build a hierarchy that includes category information. For more information about this function, see RELATED Function (DAX).

  1. When you have finished building the formula, press Enter to accept the formula.

Values are populated for all the rows in the calculated column. If you scroll down through the table, you will see that each row now has a Product Category Name.

  1. Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type ProductCategory, and then press Enter.
  2. Click Design > Columns > Add.
  3. In the formula bar above the table, type the following formula, and then press Enter to accept the formula.

= RELATED(DimProductSubcategory[ProductSubcategoryName])

  1. Rename the column by right-clicking CalculatedColumn1 and selecting Rename Column. Type ProductSubcategory, and then press Enter.

Create a hierarchy

Most models include data that is inherently hierarchical. Common examples include calendar data, geographical data, and product categories. Creating hierarchies are useful because you can drag one item (the hierarchy) to a report instead of having to assemble and order the same fields over and over.

  1. In Power Pivot, switch to Diagram View. Expand the DimDate table so that you can more easily see all of its fields.
  2. Press and hold Ctrl and click the CalendarYear, CalendarQuarter and CalendarMonth columns (you will need to scroll down the table).
  3. With the three columns selected, right-click one of them and click Create Hierarchy. A parent hierarchy node, Hierarchy 1, is created at the bottom of the table, and the selected columns are copied under the hierarchy as child nodes.
  4. Type Dates as the name for your new hierarchy.
  5. Add the FullDateLabel column to the hierarchy. Right-click FullDateLabel and select Add to Hierarchy. Choose Date. FullDateLabel contains a full date, including year, month and day. Verify that FullDateLabel appears last in the hierarchy. You now have multilevel hierarchy that includes year, quarter, month, and individual calendar days.
  6. Still in Diagram View, point to the DimProduct table, and then click the Create Hierarchy button in the table header. An empty hierarchy parent node appears at the bottom of the table.
  7. Type Product Categories as the name for your new hierarchy.
  8. To create hierarchy child nodes, drag the ProductCategory and ProductSubcategory onto the hierarchy.
  9. Right-click ProductName and select Add to Hierarchy. Choose Product Categories.

Now that you know a couple of different ways to create a hierarchy, let’s use them in the PivotTable.

  1. Go back to Excel.
  2. In Sheet1 (the sheet that contains the PivotTable), remove the fields in the Rows area.
  3. Replace them with the new Product Categories hierarchy in DimProduct.
  4. Similarly, replace CalendarYear in the Columns area with the Dates hierarchy in DimDate.

When you explore the data now, it’s easy to see the benefits of using hierarchies. You can independently expand and close different areas of the PivotTable, providing more control over how available space is used. Furthermore, by adding a single hierarchy to both Rows and Columns, you get rich and immediate drill down, without having to stack multiple fields to get a similar effect.

Hide columns

Now that you’ve created a Product Categories hierarchy and placed it in DimProduct, you no longer need DimProductCategory or DimProductSubcategory in the PivotTable Fields list. In this task, you’ll learn how to hide extraneous tables and columns that are taking up room in the PivotTable Fields list. By hiding the tables and columns, you improve the reporting experience without affecting the model that provides data relationships and calculations.

You can hide individual columns, a range of columns, or a whole table. Table and column names are grayed out to reflect that it is hidden to reporting clients that consume the model. Hidden columns are grayed out in the model to indicate their state, but remain visible in the Data View so that you can continue to work with them.

  1. In Power Pivot, make sure Data View is selected.
  2. In the tabs at the bottom, right click DimProductSubcategory and select Hide from Client Tools.
  3. Repeat for ProductCategories.
  4. Open DimProduct.
  5. Right-click the following columns and click Hide from Client Tools:
  • ProductKey
  • ProductLabel
  • ProductSubcategory
  1. Multi-select adjacent columns. Start with ClassID and continue to ProductSubcategory at the end. Right-click to hide them.
  2. Repeat for other tables, removing IDs, keys, or other details that you won’t use in this report.

Switch back to Excel to Sheet 1 with the PivotTable Fields list to see the difference. The number of tables is reduced and DimProduct includes only those items that you’re more likely to use when analyzing sales.

Create a Power View report

PivotTable reports are not the only type of report that benefits from a Data Model. Using the same model you just built, you can add a Power View sheet to try out some of the layouts it provides.

  1. In Excel, click Insert > Power View.

 Note    If this is the first time you’re using Power View on this machine, you’re prompted to enable the add-in and install Silverlight first.

  1. In Power View Fields, click the arrow next to the FactSales table, and click SalesAmount.
  2. Expand the Geography table and click RegionCountryName.
  3. In the ribbon, click Map.
  4. A map report appears. Drag a corner to resize it. On the map, blue circles of varying size indicate sales performance for different countries or regions.

Optimize for Power View reporting

Making a few small changes to your model will result in more intuitive responses when designing a Power View report. In this task, you will add web site URLs for several manufacturers, and then categorize that data as a Web URL so the URL address shows up as a link.

As a first step, add URLs to your workbook.

  1. In Excel, open a new sheet and copy these values:
ManufacturerURL    ManufacturerID
http://www.contoso.com Contoso, LTD
http://www.adventure-works.com Adventure Works
http://www.fabrikam.com Fabrikam, Inc.
  1. Format the cells as a table, and then name the table URL.
  2. Create a relationship between URL and the table that contains manufacturer names, DimProduct:
  1. Click Data > Relationships. The Create Relationship dialog appears.
  2. Click New.
  3. In Table, select DimProduct.
  4. In Column, select Manufacturer.
  5. In Related Table, select URL.
  6. In Related Column (Primary), select ManufacturerID.

To compare before and after results, start a new Power View report and add FactSales | SalesAmount, dimProduct | Manufacturer, and URL | ManufacturerURL to a report. Notice that the URLs show up as static text.

Rendering a URL as an active hyperlink requires categorization. To categorize a column, you’ll use Power Pivot.

  1. In Power Pivot, open URL.
  2. Select ManufacturerURL.
  3. Click Advanced > Reporting Properties > Data Category: Uncategorized.
  4. Click the down arrow.
  5. Select Web URL.
  6. In Excel, click Insert > Power View.
  7. In Power View Fields, select FactSales | SalesAmount, dimProduct | Manufacturer, and URL | ManufacturerURL. This time, the URLs show up as actual hyperlinks.

Other Power View optimizations include defining a default field set for each table and setting properties that determine whether rows of repeating data are aggregated or listed independently. See Configure default field set for Power View reports and Configure table behavior properties for Power View reports for more information.

Create calculated fields

In the second task, Explore data using a PivotTable, you clicked on the SalesAmount field in the PivotTable Fields list. Because SalesAmount is a numeric column, it was automatically placed into the Values area of the PivotTable. Sum of SalesAmount was then ready to calculate sales amounts for whatever filters are to be applied. In this case, no filters at first, but then CalendarYear, ProductSubcategoryName, and BrandName.

What you really did was create an implicit calculated field, making it easy to analyze sales amounts from the FactSales table against other fields such as product category, region, and dates. Implicit calculated fields are created by Excel when you drag a field to the Values area or when you click on a numeric field, like you did with SalesAmount. Implicit calculated fields are formulas that use standard aggregation functions such as SUM, COUNT, and AVERAGE, created automatically for you.

There are other types of calculated fields too. You can create explicit calculated fields in Power Pivot. Unlike an implicit calculated field, which can only be used in the PivotTable they were created, explicit calculated fields can be used in any PivotTable in the workbook, or by any report that uses the Data Model as a data source. With explicit calculated fields, created in Power Pivot, you can use AutoSum to automatically create calculated fields using standard aggregations, or you can create your own by using a formula created using Data Analysis Expressions (DAX).

As you can imagine, creating calculated fields can help you analyze your data in immense and powerful ways, so let’s get started learning how to create them.

Creating calculated fields in Power Pivot is easy when you use AutoSum.

  1. In the FactSales table, click on the Profit column.
  2. Click Calculations > AutoSum. Notice a new calculated field named Sum of Profit was automatically created in the cell in the Calculation Area directly beneath the Profit column.
  3. In Excel, in Sheet1, in the field list, in FactSales, click Sum of Profit.

That’s it! That is all it takes to create a calculated field using a standard aggregation in Power Pivot. As you can see, in just a couple of minutes, you created a SUM of Profit calculated field and added it to the PivotTable, making it easy to analyze profits depending on the filters applied. In this case, you see Sum of Profit filtered by the Product Category and Dates hierarchies.

But, what if you need to do some more detailed analysis, like number of sales for a particular channel, product, or category? For that you will need to create another calculated field that counts the number of rows, one for each sale in the FactSales table, depending on the filters applied.

  1. In the FactSales table, click on the SalesKey column.
  2. In Calculations, click the down arrow on AutoSum > Count.
  3. Rename the new calculated field by clicking on Count of SalesKey in the calculation area and then in the formula bar, change Count of SalesKey to just Count, and then press Enter. Unlike calculated columns, calculated field names are included as part of the DAX formula.
  4. In Excel, in Sheet1, in the field list, in FactSales, click Count.

Notice a new column, Count is added to the PivotTable showing the number of sales depending on the filters applied. Just like with the Sum of Profit calculated field, you see Count filtered by the Product Category and Dates hierarchies.

Let’s create another. This time, you will create a calculated field that calculates the percentage of total sales for a particular context or filter. However, unlike the previous calculated fields you created by using AutoSum, this time you will manually enter a formula.

  1. In the FactSales table, in the Calculation Area, click an empty cell. Tip: The top left cell is great place to start placing your calculated fields. It makes them easier to find. You can move around any calculated field in the Calculation Area.
  2. In the formula bar, type and use IntelliSense to create the following formula: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))
  3. Press ENTER to accept the formula.
  4. In Excel, in Sheet1, in the field list, in FactSales, click Percentage of All Products.
  5. In the PivotTable, multi-select the Percentage of All Products columns.
  6. On the Home tab, click Number > Percentage. Use two decimal places to format each new column.

What this new calculated field does is calculate the percentage of total sales for a given filter context. In this case, our filter context is still the Product Category and Dates hierarchies. You can see for instance, computers as a percentage of total product sales have increased over the years.

Creating formulas for both calculated columns and calculated fields will be fairly easy for you if you are familiar with creating Excel formulas. Whether you are familiar with Excel formulas or not, a great place to learn the basics of DAX formulas is by stepping through the lessons in QuickStart: Learn DAX Basics in 30 Minutes.

Save your work

Save your workbook so that you can use it with other tutorials or further exploration.

Next steps

Although you can easily import data from Excel, it’s often faster and more efficient to import using the Power Pivot add-in. You can filter the data that you’re importing, excluding columns you won’t need. You can also choose whether to use a query builder or query command to retrieve the data. As a next step, learn about these alternate approaches: Get data from a data feed in Power Pivot and Import Data from Analysis Services or Power Pivot.

Power View reporting is designed to work with Data Models similar to the one you just built. Read on to learn more about the rich data visualizations that Power View brings to Excel: Start Power View in Excel 2013 and Power View: Explore, visualize, and present your data.

Try enhancing your Data Model to make better Power View reports by following this Tutorial: Optimize your Data Model for Power View reporting

 
 
Applies to:
Excel 2013, Power BI for Office 365, Power Pivot in Excel 2013