Module 2 Assignment using Web PivotTable

The Module 2 Assignment provides experience with WebPivotTable, a web-based

application for business intelligence. Using WebPivotTable, you will customize a pivot table and
extend it to show more aggregated fields. WebPivotTable is a pure Javascript application that
can be used in any browser without plugins.

Before starting this assignment, you should read the Web pivot table tutorial. The
tutorial demonstrates the usage of WebPivotTable and the OLAP cube source used for this
assignment. It also demonstrates MDX to specify a pivot table. If you have not read the tutorial
yet, please refer to it prior to starting this assignment.

1. Load the Data Cube

a. Select the preloaded OLAP cube Microsoft Adventure Works (same cube used in
tutorial) from the Demo list to create the pivot table.

b. Take some time exploring the data cube before beginning on the details of the
assignment.

2. Create the Pivot Table

Create a customized pivot table that shows the following:

a. Expand the “Promotions” dimension in the Fields List.

b. Select “Promotions” field inside the “Promotions” dimensions in the Rows area.

c. Expand the “Delivery Date” dimension in the Fields List.

d. Select “Delivery [Link] of Year” in the Columns area. If it appears in the

Rows area, move it from the Rows to the Columns area.

e. Expand the Measures in the Fields List.

f. Select “Average Sales Amount” in the values area. To improve the layout, choose
No Fixed Header and Columns in the Grid Options tab.

g. Take a screen snapshot and paste it into a document as the snapshot for part 2
of the WebPivotTable assignment. The snapshot should show All Promotions and
All Periods with complete expansion on the rows and columns with Average
Sales Amount in the cells. The snapshot should show the pivot table areas (rows,
columns, filters, values, and measures), pivot table with a partial display of rows,
December 15, 2022 Module 2 Assignment using WebPivotTable Page |2

columns, and cells. If you used the no fixed header option, the row heading
should appear readable.

h. Keep the sheet open for the next part.

i. Separately, answer quiz questions 1 to 5.

3. Extend the Pivot Table

a. Add “Source Currency” level inside the Source Currency field from the Source Currency
dimension (third level) to the Filters area. Select it and choose “Add to Filters” so that
the Source Currency is placed directly in the Filters area. If it goes to the Rows area,
move it to the Filters area.

b. Create a filter such that the pivot table displays value only for “Canadian Dollar”
and “US Dollar”. You may need to navigate in the Label selection area to find the
US Dollar.

c. Take a screen snapshot and paste it into a document as the snapshot for the
WebPivotTable assignment. The snapshot should show a complete expansion of
All Periods and All Promotions with January Average Sales Amount in the cells.
The snapshot should show the pivot table areas (rows, columns, filters, values,
and measures), and pivot table with a partial display of rows, columns, and cells.
If you used the no fixed header option, the row heading should appear readable.

d. Keep the sheet open for the next part.

e. Separately, answer quiz questions 6 and 7.

4. Extend the Pivot Table with Filter Changes and Rollups

a. Modify the filter to show results only for the source Currency “EURO”. I suggest that you
clear the filters first before setting the new filter. Otherwise, the old filters remain.

b. Take a screen snapshot to show the SELECT statement in the MDX window. Paste it
into a document as a snapshot for the assignment. You need to take this snapshot
before closing the MDX window as WebPivotTable reverts to the MDX statement
consistent with the selected filter.

c. Collapse all reseller promotions except for Excess Inventory. Show only the
subcategories within the Excess Inventory item.

d. Take a screen snapshot and paste it into a document as the snapshot for the
WebPivotTable assignment. The snapshot should show the Average Sales
December 15, 2022 Module 2 Assignment using WebPivotTable Page |3

Amount for All Periods along with a subset of the expansion of All Promotions for
Excess Inventory promotions.

e. Select the Switch to Pivot Chart button to display the associated pivot chart.
Take a screen snapshot paste it into a document as a snapshot for the
Assignment 4. The snapshot should show the pivot chart along with the pivot
table areas (rows, columns, filters, values, and measures).

f. Separately, answer quiz questions 8 to 13.

5. Grading
After completing the assignment, you should take the quiz for the Module 2
Assignment. You may want to use the snapshots to answer the quiz questions. If you cannot
complete the assignment due to poor performance of WebPivotTable, you can use the
assignment solution to complete the assignment quiz.
December 15, 2022 Module 2 Assignment using WebPivotTable Page |4

Solution
First snapshot (Areas, pivot chart, Rows with expansion of promotions) with fixed
headers
December 15, 2022 Module 2 Assignment using WebPivotTable Page |5

First snapshot (Areas, pivot chart, Rows with expansion of promotions) without fixed
headers
December 15, 2022 Module 2 Assignment using WebPivotTable Page |6

Second snapshot (Source Currency filter field) with fixed headers

December 15, 2022 Module 2 Assignment using WebPivotTable Page |7

Second snapshot (Source Currency filter field) without fixed headers

Third snapshot with MDX SELECT statement

December 15, 2022 Module 2 Assignment using WebPivotTable Page |8

Fourth snapshot (partial collapse of Reseller promotions) with changed filter with fixed
headers.

Fourth snapshot (partial collapse of Reseller promotions) with changed filter without
fixed headers.
December 15, 2022 Module 2 Assignment using WebPivotTable Page |9
December 15, 2022 Module 2 Assignment using WebPivotTable P a g e | 10

Solution: fifth snapshot with pivot chart