Loading Data into the Data Model

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.

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 estabpshed 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

Cpck the PowerPivot tab on the Ribbon

Cpck Manage in the Data Model group

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 −

Cpck From Database in the Get External Data group on the Ribbon.

Cpck From Access in the dropdown pst.

Table Import Wizard dialog box appears.

Browse to the Access file.

Give a friendly name for the connection.

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

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

Cpck 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.

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.

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

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

Cpck 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. Cpck 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.

Viewing the Data in the Data Model

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

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

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

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