Create a Data Model in Excel

 Important    This feature isn’t available in Office on a Windows RT PC. Want to see what version of Office you're using?

A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables, PivotCharts, and Power View reports.

Most of the time, you’ll never even know the model is there. In Excel, a Data Model is visualized as a collection of tables in a Field List. To work with the model directly, you’ll need to use the Microsoft Office Power Pivot in Microsoft Excel 2013 add-in.

When importing relational data, creating a model occurs automatically when you select multiple tables:

  1. In Excel, use Data > Get External Data to import data from Access or another relational database that contains multiple related tables.
  2. Excel prompts you to select a table. Check Enable selection of multiple tables.

    Select Table dialog
  3. Select two or more tables, click Next, and Finish.
  4. In Import Data, choose the data visualization option you want, such as a PivotTable in a new sheet, and build your report.

You now have a Data Model that contains all of the tables you imported. Because you selected the PivotTable report option, the model is represented in the Field List that you’ll use to build the PivotTable report.

PivotTable Fields list

What can you do with this model? You can use it to create PivotTables, PivotCharts, and Power View reports in the same workbook. You can modify it by adding or removing tables, and if you use the Power Pivot add-in, you can extend the model by adding calculated columns, calculated fields, hierarchies, and KPIs.

When creating a Data Model, the visualization option is important. You want to choose PivotTable Report, PivotChart, or Power View Report for data visualization. These options allow you to work with all of the tables collectively. Had you chosen Table instead, each imported table would be placed into a separate sheet. In this arrangement, the tables can be used individually, but using all of the tables together requires a PivotTable, PivotChart, or Power View report.


 Notes 

  • Models are created implicitly when you import two or more tables simultaneously in Excel.
  • Models are created explicitly when you use the Power Pivot add-in to import data. In the add-in, the model is represented in a tabbed layout, where each tab contains tabular data. See Get data using the Power Pivot add-in to learn the basics of data import using a SQL Server database.
  • A model can contain a single table. To create a model based on just one table, select the table and click Add to Data Model in Power Pivot. You might do this if you want to use Power Pivot features, such as filtered datasets, calculated columns, calculated fields, KPIs, and hierarchies.
  • Table relationships can be created automatically if you import related tables that have primary and foreign key relationships. Excel can usually use the imported relationship information as the basis for table relationships in the Data Model.
  • For tips on how to reduce the size of a data model, see Create a memory-efficient Data Model using Excel 2013 and the Power Pivot add-in.
  • For further exploration, see Tutorial: PivotTable data analysis using a Data Model in Excel 2013.

 Tip    Your workbook has data, but do you know whether it contains a Data Model? You can quickly determine model status by opening the Power Pivot window. If data appears in the tabs, a model exists. More about Find out which data sources are used in a workbook data model.

Use a Data Model in another PivotTable, PivotChart, or Power View report

An Excel workbook can contain only one Data Model, but that model can be used repeatedly throughout the workbook.

  1. In Excel, click Insert > PivotTable.
  2. In Create PivotTable, click Use an external data source, and then click Choose Connection.
  3. In Existing Connection, click Tables.
  4. Under This Workbook Data Model, Tables in Workbook Data Model is selected by default. Click Open, and then click OK. A PivotTable Fields list appears, showing all of the tables in the model.

Add existing, unrelated data to a Data Model

Suppose you’ve imported or copied lots of data that you want to use in a model, but did not check the Add this data to the Data Model box during import. Pushing new data into a model is easier than you think.

  1. Start with the data that you want to add to the model. It can be any range of data, but using a named range works best.
  2. Highlight the cells you want to add, or if the data is in a table or named range, place the cursor in a cell.
  3. Use one of these approaches to add your data:
  • Click Power Pivot > Add to Data Model.
  • Click Insert > PivotTable, and then check Add this data to the Data Model in the Create PivotTable dialog box.

The range or table is now added to the model as a linked table. To learn more about working with linked tables in a model, see Add Data by Using Excel Linked Tables in Power Pivot.

Refine and extend the Data Model in the Power Pivot add-in

In Excel, Data Models exist to amplify and enrich the reporting experience, particularly when that experience includes PivotTables or other report formats that are intended for data exploration and analysis. While important, Data Models are purposely kept in the background so that you can focus on what you want to do with them.

But suppose working directly on the model is exactly what you want to do. Knowing that the Field List is based on a model, maybe you want to remove tables or fields because they aren’t useful in the list. Perhaps you want to view all of the underlying data that the model provides, or add KPIs, hierarchies and business logic. For all of these reasons and more, you’ll want to modify the Data Model directly.

To modify or manage the Data Model, use the Power Pivot add-in. The add-in is part of the Office Professional Plus edition of Excel 2013, but not enabled by default. More about Start Power Pivot in Microsoft Excel 2013 add-in.

Differences between a table in Power Pivot and a table in a sheet

In Power Pivot, you cannot add a row to a table by directly typing in a new row like you can in an Excel worksheet. But you can add rows by using Paste commands and by refreshing data.

Data in an Excel worksheet is often variable and ragged: that is, one row might contain numeric data, and the next row might contain a graphic or string of text. In contrast, a table in Power Pivot is more like that in a relational database, in which each row has the same number of columns and most columns contain data.

Use the Data Model in Power View

A Data Model is used as the basis of a Power View report. Using the Power Pivot add-in, you can apply optimizations to the model that improve Power View reporting. Optimizations include: specify a default field list, choose representative fields or images to uniquely identify specific rows, or specify how rows with repeating values (such as employees or customers with the same name) are handled in a reporting application.

  1. Create a Data Model by importing several related tables.
  2. Click Power Pivot > Manage to open the Power Pivot window.
  3. Select a table and apply optimizations:
  1. Click Advanced > Default Field Set. Choose the fields that should appear automatically on a Power View report when you click on the parent table. See Configure Default Field Set for Power View Reports for details.
  2. Click Advanced > Table Behavior. Optimize for report layouts that group data. Grouping is subject to default behaviors that sometimes produced unintended results, such as consolidating rows that should be listed separately. See Configure Table Behavior Properties for Power View Reports for details.
  3. Click Advanced > Data Category. Some report visualizations are specific to types of data. For example, if you have a time or date based table, you can assign a Date category that helps Power View with time-based visualizations.
  1. Repeat for other tables.
  2. In Excel, click Insert > Power View to start a new report. See Power View: Explore, visualize, and present your data for more information.
 
 
Maikakapit sa:
Excel 2013, Power Pivot in Excel 2013