Loading Data into the Data Model


Advertisements

You can load data from different types of data sources into the data model. For this, you can find various options in the Get External Data group on the Ribbon in the Power Pivot window.

Various Options

As you can observe, you can load data from databases, or from data services or several other types of data sources.

When you load data from a data source into the data model, a connection will be established with the data source. This enables data refresh when the source data changes.

Initiating with a New Data Model

In this section, you will learn how to model the data for profit and loss analysis. The data for analysis is in a Microsoft Access database.

You can initiate a new data model as follows −

  • Open a new Excel workbook
  • Click the PowerPivot tab on the Ribbon
  • Click Manage in the Data Model group
Manage Power Pivot

The Power Pivot window appears. The window will be blank as you have not yet loaded any data.

Loading Data from Access Database into the Data Model

To load the data from the Access database, carry out the following steps −

  • Click From Database in the Get External Data group on the Ribbon.
  • Click From Access in the dropdown list.
Access

Table Import Wizard dialog box appears.

  • Browse to the Access file.

  • Give a friendly name for the connection.

Table Import Wizard
  • Click the Next button. The next part of the Table Import Wizard appears.

  • In the Table Import Wizard, select the option – Select from a list of tables and views to choose the data to import.

choose how to imprt the data
  • Click the Next button. The next part of the Table Import Wizard appears as shown in the following screenshot.

  • Select all the tables.

  • Give friendly names to the tables. This is necessary because these names appear in the Power PivotTables and hence should be understood by everyone.

Finance Data

Choosing the Columns in the Tables

You might not require all the columns in the selected tables for the current analysis. Hence, you need to select only those columns that you selected while shaping the data.

  • Click the Preview & Filter button. The next part of the Table Import Wizard -Preview of the selected table - appears.

Preview Selected Table
  • As seen in the above screenshot, the column headers have check boxes. Select the columns you want to import in the selected table.

  • Click OK. Repeat the same for the other tables.

Importing Data into the Data Model

You are at the last stage of loading data into the data model. Click the Finish button in the Table Import Wizard. The next part of the Table Import Wizard appears.

The importing status will be displayed. The status finally displays Success when data loading is complete.

Importing

Viewing the Data in the Data Model

The imported tables appear in the Power Pivot window. This is the view of the data model

Circulation Area Tabs and Data

You can observe the following −

  • Each of the tables appear in a separate tab.
  • The tab names are the respective table names.
  • The area below the data is for the calculations.

Viewing the Connection Name

Click the Existing Connections in the Get External Data group. Existing Connections dialog box appears as shown in the following screenshot.

Existing Connections

As seen in the above screenshot, the connection name given appears under PowerPivot Data Connections.

Advertisements