This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	PowerPivot (72 exercises)
Topic ==>	PowerPivot data models (7 exercises)
Level ==>	Relatively easy
Subject ==>	Power BI training

From the MAM database, import 6 tables and use them to show quantity sold by town.

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.

Before you can do this exercise, you'll need to download and unzip this file .

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:

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:

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: