Microsoft Office Tutorials and References
In Depth Information
Understanding the Internal Data Model
Understanding the Internal Data Model
Excel 2013 introduces a new in-memory analytics engine called the internal Data Model. Every
workbook has one internal Data Model that allows you to work with analyze disparate data sources
like never before.
The idea behind the Data Model is simple. Let’s say you have two tables — a Customers table and an
Orders 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 want to analyze revenue by state, you must join the two tables and aggregate the Revenue
field in the Orders table by the State field in the Customers table.
In the past, you would have to go through a series of gyrations involving VLOOKUPs, SUMIFs, or other
formulas. With the new Excel 2103 data model, however, you can simply tell Excel how the two tables
are related (in this case, they both have a customer number) and then pull them into the internal
Data Model. The Excel Data Model will then build an internal analytical database based on that
customer number relationship and expose the data through a pivot table. With the pivot table, you
can create the aggregation by state with a few clicks of the mouse.
Building out your first data model
Imagine that you have the Transactions table you see in Figure 17-1. On another worksheet, you have
a Generators table (see Figure 17-2) that contains location information about each generator.
Figure 17-1: This table shows transactions by generator number.
Figure 17-2: This table provides location information on each generator.
Search JabSto ::

Custom Search