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 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
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.
Table Import Wizard dialog box appears.
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.
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.
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.
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.
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
Click 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.
