Il-Yeol Song, College of Information Science & Technology, Drexel University

OLAP HW #3, Create Virtual cubes from existing cubes using proCube formulas, INFO 607

For this exercise, use the Actual cube created in HW1 to create a Budget and Variance cube. Note that in the real world a Budget cube would be created separately to allow for dynamic manual editing. For the purposes of learning how to create virtual cubes, we will create a virtual Budget cube using a proCube formula. Cubes created from formulas cannot be edited since the values represent a static view of another source.

A. Create a Virtual Budget cube.

  1. Open the FoodMart 2000 proCube database.
  1. Create a new slice for the Actual cube showing 1998 first quarter store sales of all stores using daily paper promotions for all American brand products (show each products sales and the sum for American brand and show each for month and the sum for the quarter). Display all American brand products and the months along rows and columns.

(Tip: While creating any subset of a hierarchy, for example Jan, Feb, Mar, Q1, Drag the aggregate (Q1) over first and click the “Drill Down” button located next the “Clear All” button. This will explode out all the detail members for that aggregate).

  1. Open the Cubes dialog box and enter “Budget” as a name for a new cube and click add.
  1. Highlight the Budget cube and select formulas.
  1. Write three formulas to place the 1998 store_sales, store_cost and unit_sales from the Actual cube into the Budget cube but at 110% higher for each value.
  1. Tile a new slice for the budget cube and edit the dimensions to appear as the Actual slice.
  1. Check the slices to confirm that the Budget figures are 110% of the Actual figures. Leave these two slices open.

B. Create a Virtual Reporting cube that allows a user to show data for Actual, Budget or the Variance of the two. (Before creating this new cube we will need to create a new dimension that will provide us with a means for selecting any of the three types. Hence we will call this new dimension, “Version”).

  1. Click on the Dimension button on the tool bar to view all existing dimensions in the database.
  1. Type “Version” and click Add… Once the new dimension appears in the list select it and click Edit.
  1. You will now create three new members for the Version dimension and define their hierarchy.
  1. Click the “Create a new member” button from the tool bar in this dialog box and enter “Actual” as its name and press enter.
  1. Repeat step 4 to create “Budget” and “Variance” members.
  1. Next drag Variance over to the right and place it against the Version dimension.
  1. Since Variance is defined by Budget minus Actual we will set each as a child in a hierarchy under Variance. Drag Actual then Budget over to Variance on the right to define this hierarchy.
  1. The formula we are applying is :

Variance = Budget – Actual

In order to ensure that the Variance member returns the difference between its two children, Budget and Actual in our Reporting cube, we need to “edit the weight” to one of the two members. Right click on Actual member and select Edit Weights.

  1. Type a -1 and press Enter. Your dialog box should appear as Fig. 1.

Fig. 1

  1. Close this box by clicking the check mark. Then close the Dimensions box by clicking Ok.
  1. Create a new virtual cube called “Reporting” in the Cubes dialog box.

step 11.

  1. Create a formula for this cube that assigns all members of the Actual cube to the Version dimension/Actual member.
  1. Create a second formula that assigns all members of the Budget cube to the Version dimension/Budget member.

step 12 and 13.

  1. Now Tile a new slice for the Reporting cube.

  1. You now have a new dimension, Version, assigned to this slice. Set the page for the Reporting cube slice as the same as the other two slices except add the version dimension to the Page list box and select Variance as the member to be displayed.

(see the figure above)

  1. Calculate the Reporting cube slice and confirm the values for variance against the Actual and Budget slices. (Later go back and check the Reporting cube for Actual and Budget data).
  1. Print out an Excel Spreadsheet for each of the three slices. Save the sheets and slices as HW3budget, HW3actual, and HW3variance.

Page 1 of 5