Il-Yeol Song, College of Information Science & Technology, DrexelUniversity

OLAP HW #2, Analyze OLAP data with Excel and use ProCube Formulas, INFO 607

For this exercise use the proCube supplied sample database, Acme Trading Company.

A. Using Excel with proCube.

1. Open Acme Trading Co. database file found in the proCube Examples folder.

2. Click the New Slice button and select the Financial Data Cube. A slice of the pre-built cube is generated.

3. Change the dimensions to specify the following and click the Calculate button:

  • Page: Version-Actual; Year-1998; Region-USA
  • Columns: Month-(subset of Jan, Feb, Mar, Q1, Apr, May, Jun, Q2)
  • Rows: Account-All

4. Check the Edit -> Options menu to confirm that the “Read Write” option is selected under Preferences – Create Excel Worksheet Using OLAP. This function will allow a user to create an Excel worksheet that can act as an interface to the cube. The user will be able to utilize all of Excel’s features to create customized reports and dashboards and even dynamically write back to a cube.

5. Click the Create Excel Worksheet button on the tool bar to load an Excel worksheet.

6. From Excel you may change the dimension members and recalculate the worksheet. Double click on the cell containing USA for the Region dimension. A dialog box allowing you to select a member for that dimension from a hierarchy will appear. Select Canada and click OK to close. Press F9 to recalculate the worksheet for this new dimension. (This feature will allow a user to change just the dimension members specified for the page and not the columns or rows).

8. Make the same change in proCube to confirm the data is consistent with Canada.

9. Test proCube’s functionality from within Excel by changing the value of cell B21 (Jan – Other Income) from 0 to 100 and press F9. Note the change in calculations that affect other cells.

11. To further test how Excel and proCube work together dynamically leave the value 100 in cell B21 and returning to proCube and press the recalculate slice button and note how proCube has incorporated the new value. (Note: changes such as this cannot be applied to aggregate members for example members who have already been calculated as a Sum).

12. Leaving the current slice open, click the new slice button and select Financial Data Assumption's cube.

13. From the Window menu select tile to view both slices together.

14. Edit the dimensions of the Financial Data Assumption’s cube to match the Financial Data cube. Leave both slices open.

B. Using proCube’s Formula functionality.

For the puposes of this exercise we will write a simple formula to transfer a value from one cube to another.

1. Click the Cube icon and highlight the Financial Data cube by clicking once on it and then click the formulas button (or double click Financial Data cube).

Examining the first formula;

//Revenue from Margin Cube

Details and {"Account.Revenue"}=

Margin.["Product.Total Product", "Margin Account.Revenue"];

  • The first line is a comment explaining the source of Revenue for the Financial cube is produced from the Margin’s cube.
  • proCube formulas have a left hand side and right hand side separated by an equals sign.
  • The left hand side in braces {…} determines which member of the current open cube will be affected.
  • The right hand side in brackets […] determines what cube and member will supply data to the current open cube.
  • Formulas can be applied as an “Internal Cube Reference” retrieving a value from within the cube where the formula is applied.
  • Formulas can apply a “Cross Cube Reference” retrieving a value from another cube.
  • The translation of the above formula is that the Financial Data cube’s Account dimension Revenue member (detail only) is supplied by the Margin cube’s Account dimension Revenue member for total products in the Product dimension of the Margin cube. (Placing “All” at the beginning of the formula means that the member will be applied to the cube’s aggregates and details).
  • The order of precedence for formulas appearing in the dialog box is from top to bottom so this first formula is applied before the second formula in the list and so on.
  • Math can also be applied as in the case of the 3rd formula for calculating Margin%

2. We will build a formula to retrieve values from the Financials Data Assumptions Cube (which has a dimension of the same name) “Other Expenses” member and place that value into the Financials Cube under “Other Expense” member. Create a space at the top of the formulas by positioning the cursor in front the first line of code and pressing the enter key twice. Next up arrow twice back up to the top line.

3. Use the key pad buttons {…} and […] to help set up the formula. First click {…}.

4. The Build Range {…} dialog box shows the available dimensions where a value may be placed. Select the Account dimension and place a check on “other expense” (the value’s destination). Note how the left hand side of our formula is now shown in the bottom list box. Click Ok to close this box.

5. Next place an equals sign in our formula (or use the key pad) and click the […] button.

6. Now from the Cube combo box at the top select the cube from which we will retrieve the value. Select the Financial Data Assumptions Cube.

7. Next select the Financial Data Assumptions dimension on the left and other expense (income) to the right. Note how the right hand side of our formula is now shown in the bottom list box. Click Ok to close.

8. All formulas must be terminated with a semi-colon. Place a semi-colon and the end of the new formula and click Ok to close. Close the Cubes dialog box to save the formula. (Note any syntax errors will be flagged before the box can be closed). The full formula should appear as follows:

All and {"Account.Other expense"}="Financial Data Assumptions".["Financial Data Assumptions.Other expenses (income)"];

9. Click the “create new slice” button and select the Financial Data Cube. Use the tile option to show this third slice. Edit the dimensions to be the same as the previous slice.

10. When multiple slices are tiled in the window only one slice at a time is highlighted. Therefore any act to calculate the slice will only affect the highlighted slice. The newest slice which you just edited should be highlighted. Click the calculate slice button (or press F9). Note how the values (0.02) that appeared in the Financial Data Assumptions cube, other expenses member now appears in the latest Financial Data cube slice.

11. Highlight the original Financial Data cube slice and press F9 again. This slice should now also be updated with the same value.

11. Click the create Excel Spreadsheet button to view a worksheet with the new values for the Financial Data slice.

12. Save the Excel spreadsheet as “HW2” and print out this worksheet.

13. In proCube, click the “Save Slice” button for the same Financial Data cube slice. Save the slice as HW2. From the Slice menu select Close to close out this slice. The other two slices do not need to be saved. Close each slice without saving.

14. Remember that the Acme Trading Company proCube database is still open despite closing all the slices. From the File menu select “Save Database” and close out proCube.

Page 1 of 7