The New Internal Data Model in Excel 2013

January 10th, 2013 by datapig Leave a reply »

I've been playing with Excel 2013 and finding some very cool features that I'd like to share. Today, I want to introduce you to the new Data Model in Excel 2013.

.

Excel 2013 introduces a new in-memory analytics engine called the Data Model, allowing you to work with and analyze disparate data sources. The idea behind the Data Model is simple. Let's say you have two tables – a Transactions table and an Employees table. The Transactions table has basic information about invoices (Invoice data, Sales Rep data, and Customer data). The Employees table has basic information like Sales Rep Number, Rep 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 Transactions table by the State field in the Employees 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 pull them into the internal data model and simply tell Excel how the two tables are related (both tables have an Employee Number field). The Excel Data Model will then build an analytical cube based on that employee number relationship. You can then expose the data through a pivot table, or even better, a "PowerView" dashboard (PowerView is a new feature in Excel 2013 that we'll cover in another post).

.

There are several ways to fill and use your Data Model. In this post, I'll give you one method.

.

Again, let's say I have several related tables (Transactions, Employees, and Locations).

The first thing I'll do is convert each table to a defined Table.

I place my cursor inside the data table and select Insert->Table.

This will activate a dialog box where I can specify the range and press OK.

Excel will then turn that range into a defined Table that the internal Data Model can recognize.

.

Just to keep things clean, I'll change the Table Name to something appropriate and easy to remember. In this case, Locations.

.

After doing this for all my tables, I can add each one to the data model individually by going to the Data Tab in the Ribbon and selecting Connections. This opens the Workbook Connections dialog box.

.

In the screenshot below, you'll see the ThisWorkbookDataModel connection and you'll also see that I've already added two defined tables (Employees and Transactions). 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.

To add my defined "Locations" table, I'll choose the Add to the Data Model option.

.

Excel activates the Existing Connections dialog box where I can find and select my target table (from the Tables tab). Clicking the Open button will add it to the Data Model.

.

At this point I have all three tables in my Data Model.

Ok…so now what?

Well, as I mentioned before, you can use the data model as the source for your PivotTables and PowerView dashbords. In this walkthrough, let's use the Data Model to create a PivotTable.

.

I open the Create PivotTable dialog box, and choose to "Use an external data source".

.

When I click Choose Connection, the Existing Connections dialog box opens.

Here, I can go to the Tables tab and select the "Tables in Workbook Model" connection.

.

.

Excel immediately creates a pivot table that contains all the tables in my Data Model. It then informs me that Relationships need to be created between the tables.

.

I create my relationships, telling Excel how each table relates to another.

For example, in this screenshot, you see that the Transaction table has a Sales_Rep field. It is related to the Employees table via the Employee_Number field.

.

Once I create all the appropriate relationships, I have a single pivot table that effectively uses data from multiple tables, allowing me to create cross-table analysis.

.

.

Now, I must say that this very basic example can't begin to express the usefulness of this new feature. In addition to defined Tables, you can add other sources to the Data Model: SQL Server Tables, Access database Tables, Oracle tables, dynamic text files, etc. If you can create a connection to it you can add it to the data model.

.

The ability to merge disparate data sources into one analytical engine is huge. Stated simply, Excel analysts using Excel 2013 will have the power and functionality of analytical cubes at their fingertips (built into Excel).

.

The complicating factor is the idea of building relationships between the tables in your data model. I'll have more on that in a future post.

Advertisement

13 comments

  1. ThomasWilson says:

    Pivot tables can be a great time saver for presenting custom charts or tables that relate to a lot of data.

  2. Hamilton says:

    Thanks for this post. Seems a lot like Powerpivot stuff going on here.

  3. Bob R says:

    Mike – Sounds awesome. Do the PivotTables have the same power – or more – as regular PivotTables? In other words, is it a whole lot better than the PivotTable functionality previous Excel versions had with data from multiple ranges?
    Bob

  4. Jared says:

    DP,
    After upgrading to 2013 and spending a few hours with Microsoft help I found out that Powerview is only available on Office Pro Plus which requires volume licensing. Does that sound right to you?

    Thanks

  5. Jordan says:

    Pivot tables are such a fantastic reporting tool and time saver, thanks for the post

  6. Welcome, every person!

    Listed here at Askfmtracker.web, we attempt to give our consumers with the very best attainable support.
    As of now we are the only doing work support discovered on the internet!

    We hope to continue to provide our consumers with the
    fasters and most correct benefits!

    You wont go away here without a hundred% satisfaction

    Thank you for the rapidly service! It was a pleasure working with you!

    This is by significantly, the ideal tracker online!
    If you would like to download the cost-free ask.fm tracker, basically decide on the download tab at the best of
    the web page, or click on on the graphic to the remaining!

    The request fm tracker wors on any computer, Home
    windows, MAC or anything at all else you have! It even supports
    cellular!
    Tutorial:

    one. Down load the software from the link under and extract the .ZIP to your Desktop.

    2.Operate the Inquire fm IP tracker.
    three. Fill In the information the system asks for
    4. Press ‘ Track IP ‘ and wait around
    5. The IP will be discovered!!
    6. Enjoy!!

  7. Malini says:

    I have been using the distinct count feature for a while now but I would really like to be able to use the group functions. This is disabled when you add the range to the Data Model to get the distinct count. For example, if you want to count distinct member and group them by age, it cannot be done.

    Is there something I am missing here? Any help/advice is appreciated.

  8. datapig says:

    Malini: When you add a range to the data model, you are essentially creating an OLAP pivot table.

    One of the limitations is that the Group By function becomes unavailable.

  9. Rick says:

    I notice when creating a table from data that is connected to external data, it states that the external data connection must be removed. My spreadsheet gets its data externally each day, is there a way to use this internal data model and retain external connection to the data source?

  10. Trombone4 says:

    Why not simply import the data from an SQL cube?

  11. Rajesh says:

    There is no ThisWorkBookDataModel inbuilt. Please correct.

Leave a Reply