Exercise: Create a clean data model, and a pivot table based on it

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the Wise Owl course listed below!

Category ==> Power BI - Excel 2013  (25 exercises)
Topic ==> PowerPivot data models  (1 exercise)
Level ==> Relatively easy
Course ==> PowerPivot / Excel Power BI
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

If you haven't already done so, run the SQL script in the above folder in SQL Server Management Studio to generate a database (not for commercial use or copying) called MAM.

In a new workbook, in PowerPivot import from this MAM database the following tables:

  • tblCentre
  • tblPos
  • tblStore
  • tblTown
  • tblTransaction

The aim of this exercise is to create the following pivot table:

Pivot table of average quantity by town

This pivot table shows that Sutton Coldfield and Burnley are the towns with the highest average quantity purchased.

 

To help you do this, create the following data model:

Simple data model

The data model should contain well-named tables, and no unnecessary details. See if you can also import only the bare minimum of fields that you need to get the pivot table to work.

 

Whoops!  We've forgotten to add in the tblProduct table.  Go back into PowerPivot and add this table, but only importing products where the full price is £15 or more.

You'll also need to create a relationship between the product and transaction tables, and may also need to change the properties of the transaction table to add in the ProductId column so that you can link to it.

Amend your data model and pivot table so that they look like this:

Amended data model

The revised data model, with the product name field.

 

Amend your pivot table so that it shows the number of transactions, sorted by town name:

Number of transactions by town

The (blank) column shows all of the transactions which don't have a matching product (because we didn't import it).

Save this workbook as Data models are fun, and close it down. 

This page has 0 threads Add post