Excel as a Database: How to Create a Data Model in Excel 2013

Many blogs, including some originating from our friends in the rainy Pacific Northwest, have referred to the “new” data models in Excel 2013. I’m not sure “new” is quite the right word. “Useful”, absolutely. “Interesting”, most definitely. But what we are really seeing here is not so much new as it is another step in the evolution of Power Pivot. No longer content in its role as add-in, the Power Pivot engine is becoming an integral part of Excel. Make no mistake about it, Power Pivot is still an Add-In and you could decide not to load it if you wished. However, the core functionality, is more tightly integrated into Excel, and often one does not need to open the Power Pivot user interface at all. The so-called Excel “data model” is actually a Power Pivot database consisting of Excel worksheet tables linked to Power Pivot tables. In other words, the linking of Excel tables to Power Pivot tables that proved so useful in Excel 2010 has simply been made automatic when we create a “data model”.

As with all things Excel, there is more than one way to create a data model. We will take a look at the most general.

How to Create a Data Model

To start with, any worksheet data you wish to include in a data model must be converted into an Excel table, which as you have done many times before, is accomplished by selecting a cell in the data range and choosing Table from the Insert ribbon menu. We’ll work with the same UN trade data that we used earlier to illustrate linked tables. Start by turning the worksheet data into tables; we’ll do this for data ranges on the “Commodities Trade” worksheet and the”Group” worksheet.  It is much easier to pick a good table name now than it is to change it later. Do it now! Simply go to the Design tab of the Table ribbon and replace “TableN” with a suitable name. I’ll choose the name CommodityTrade for the table created from a copy of the raw data in the sample workbook. For illustrative purposes we’ll use as our second table the groups of nations introduced in the previous installment. We’ll call it Groups.

Once both data ranges have been converted into tables, click the Connections button on the Data ribbon. Once the Connections dialog box appears, you will have to click the down arrow next to the “Add” button and select “Add to data model…” This will have to be repeated for as many Excel tables as you wish to be added to your data model. Though it is absolutely not necessary, you might wish to go the Power Pivot ribbon and click on the Manage button; this will bring up the Power Pivot window and you should see the tables you selected. If you prefer, you can go ahead and use the Power Pivot UI to create relationships between the tables. This is not necessary, though, and relationships can be created as needed using the conventional pivot table interface.

How to Create a Data Model in Excel 2013
Adding an Excel table to a workbook data model

From the Insert ribbon tab we’ll select “Pivot Table” as usual. Since we will not be using the Excel table but rather the table in our Power Pivot database, so be sure to choose “Use an external data source” in the Create Pivot Table dialog. On the tables tab, choose the entry for the data model rather than an individual table. Once the familiar pivot table apparatus appears, select GroupName from the Groups table and Trade Value (US$) from the CommodityTrade table. If you have not already defined a relationship between the two tables the Create button should appear. Ensure that the foreign key Reporter ISO from the CommodityTrade tables is appropriately paired with the primary key Country Code from the Groups table.

How to Create a Data Model in Excel 2013
Ensure that “Use an external data source” is selected.

If we attempt to create a pivot table including labels from the Group table, we will get a warning that relationships may need to be defined:

How to Create a Data Model in Excel 2013
Relationship May Be Needed

Clicking “CREATE…” takes us to the Create Relationship dialog.

How to Create a Data Model in Excel 2013
Creating a relationship between the CommodityTrade table and the Groups table.

After filtering the blanks, that is, the countries that do not belong to either G7 or ASEAN, and formatting for currency, your results should look like this:

How to Create a Data Model in Excel 2013
Results-01

As long as we are making pivot tables, let’s make a slightly more interesting one by looking at the ASEAN nations’ trade with North America for individual commodity sectors.

How to Create a Data Model in Excel 2013
Typographical problems in worksheet data show up as inappropriate groups in the pivot table.

Something’s Gone Horribly Wrong! (Seems to happen a lot!)

Welcome to the world of real data; it’s not quite the Northwind database, is it? In our results we not only see odd characters appearing in the commodity name, but what is clearly a single category appears twice with different punctuation, each with its own total. Very likely this problem has arisen because different typists entered the data. English-speakers sometimes type “mate” with an acute accent on the “e” (e.g. “maté”). Unfortunately when such characters are coded as ANSI characters, their representation may not be consistent. This, by the way, is why the rules of relational database design were created to begin with. Without strict controls, data such as these sneak in and wreak havoc on later analyses. Making matters worse, Excel has no natural provision for dealing with such issues. To learn how to straighten up this mess without too much extra effort, have a look at my post on how to use SQL and Power Pivot to repair data.

To learn more about Power Pivot, attend Learning Tree’s 2-day course, Power Pivot for Excel: Mining Data for Business Intelligence. Attend at one of our many centers or online from home or office.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.