PivotTable Fundamentals - Microsoft Business Intelligence Tools for Excel Analysts - page 38

Understanding the Internal Data Model
Excel 2013 introduces several new BI features that we discuss in this topic, 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 ana-
lyze 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 inter-
nal 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.