Defining Cube References

The bi-directional connection shown so far is OLAPTable function (shown in cell A6). In fact, when you click on cell A6 in the worksheet, you will see in Excel’s formula bar the single formula that references all the worksheet cells that connect to data in the PowerOLAP cube:

The OLAPTable function is one of many functions you can use to dynamically link data between a worksheet and a PowerOLAP database. (The other PowerOLAP^® functions for creating a worksheet from a Slice—OLAPReadWrite, OLAPPivot and OLAPWriteTable—and their differences, are discussed in the PowerOLAP^® User Manual)

The next exercise will demonstrate how to define a Cube reference that returns a pertinent value into a cell by using the OLAPRead function.

1.   Select cell D13 in Excel in the current worksheet.
In this cell, you will define a Cube reference formula that shows the Gross Profit for United States in February (in order to make a quick visual comparison to Gross Profit for Canada, which appears in cell D11).

2.   From the Excel ribbon, go to Add-Ins tab and select PowerOLAP command then Edit Formula.
The Edit PowerOLAP Formula dialog box is displayed:

3.   Select OLAPRead from the top drop-down menu (to the right of Function).

4.   Press the Pick button,  (next to Database).  
The Select Database dialog box is displayed, as below:

The current database—which contains the value you want—is selected.

5.   Click OK. You are returned to the Edit PowerOLAP Formula dialog box.

6.   Press the Pick button,  (to the right of Cube).
The Select Cube dialog box is displayed. Likewise, this Cube contains the value you want to be referenced into the Excel cell, D13.

7.   Click OK.
Again, you are returned to the Edit PowerOLAP Formula dialog box. Now you have defined several of the parameters of the Cube reference formula, as shown in this detail of the dialog box:

Note that the Dimensions area now displays text boxes for you to enter choices for the Months, Accounts and Regions dimensions. In fact, February has been “pre-selected” for you. If you wanted another Months member for your formula, you would press the Pickbutton to make a different selection. Since you do want to select February data, continue to the Accounts and Regions dimensions. Use the Pick button and the corresponding Select A Member dialog boxes to choose data for Gross Profit and United States, respectively.

After you have made these choices, the Edit PowerOLAP Formula dialog box will look as follows:

8.   Click OK to update Excel with the new formula reference.  
Your dynamically connected Excel spreadsheet will appear as in the following figure:

You now have a ready view of the February, Gross Profit for United States within a dynamically connected spreadsheet that shows figures for Canada. Now, whenever February, Gross Profit for United States (or for that matter, Canada) changes, it will be reflected in this worksheet.

You can save the current database, with changes you have made, from within Excel.

9.   Go back to the PowerOLAP application, and select File icon and Save Database command.
The PowerOLAP database is saved but not closed.