Creating Data Models in PowerPivot

Create Data Models PowerPivot

Creating data models in PowerPivot is an important step because if you want to work with any data, you’re going to have to know to work with data models.

However, if data models sounds way too advanced, then check out this Pivot Tables for Beginners video tutorial.

Pivot Table Excel download file

The next thing your should do is download the above exercise file. By using the above Excel exercise file, which has been created by importing data from an Access database, you can follow the steps below.

The Excel file consists of 4 sheets, that can be seen below, we will first need to connect these together into a data model. Therefore, before you start connecting, it would be a good idea to install PowerPivot.

Excel Tabs

Creating Data Models in PowerPivot – Step by Step

Ensure that you have PowerPivot Installed or Enabled

Try the following:

  1. First, open Excel and then click on the PowerPivot tab.
    (If you can’t find the PowerPivot tab then you may have to install it)
    1. File – Options – Add ins – Com Add ins (from drop down menu)
      If you want to know more then check out this how to install PowerPivot tutorial.
Install PowerPivot as an Add in

Now that you’ve installed PowerPivot then you can continue.

  1. Firstly, click on the FactInternetSales tab. (This has a table called FactInternetSales).
  2. Secondly, Click anywhere on the Table.
  3. Finally, from the Power Pivot Tab, click Add to Data Model.
    Add to Data Model
  4. As a result the FactInternetSales Table has been added to the data model.
  5. Now swap back to Excel and do the same for the following fields.
    • DimProduct
    • DimProductSubCategory
    • DimProductCategory
  6. As a result the tabs at the bottom of the PowerPivot window should now look like.
    Tabs at the bottom of PowerPivot
  7. Next, it’s time to create the data model. So click on the Diagram View Button at the top of the screen.
  8. Further, in the diagram view, you need to arrange the tables to look like.

    Diagram View in PowerPivot
  9. and, using the mouse, drag from ProductKey in the DimProduct Table to ProductKey in the FactInternetSales table. A line will appear between the two.
  10. Do the same for:
    • DimProductDimProductSubcategory with ProductSubcategoryKey
    • DimProductSubcategory DimProductCategory with ProductCategoryKey
  11. Your Diagram view should now be completed and, in addition, you have created your first data model in PowerPivot.
    Creating Data Models in PowerPivot

Create a PivotTable from the PowerPivot Data Model

In addition to creating our data model we should also create a PivotTable. Because at the moment it may seem that you’ve done a lot of work for not much come-back. Therefore, let’s create a Pivot Table that would ordinarily be impossible to do without VLookups. As a result you will see why PowerPivot is a powerful tool.

  1. First of all, click on the PivotTable button, which is at the top of the screen.
    Create PivotTable Button in PowerPivot
  2. Then Click on OK. (You should ensure that the New Worksheet button is selected).
  3. Now you can create a PivotTable from separate tables:
    • Drag Sales Amount from the FactInternetSales table to Values
    • Drag EnglishProductCategoryName from the DimProductCategory to Rows
    • Format the Sales Amount numbers as Accounting (Right click – Value Field Settings – Number Format – Accounting then OK out of the boxes)
  4. There you go! You see miracles do happen.
Pivot Tables from Separate Excel Sheets using PowerPivot

In conclusion, knowing how to not only import data from Excel to create data models is a vital skill. As a result you will be able to create PivotTables that just weren’t possible before. Now, you’ve added Tables to your data model you can’t use regular calculated fields, you have to use DAX formulas.