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:
=OLAPTable($B$1,$B$2,B7:R7,A8:A11,$C$3,$B$8) |
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:
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:
8. Click OK to update Excel with the new formula reference.
Your dynamically connected Excel spreadsheet will appear as in the following figure:
9. Go back to the PowerOLAP application, and select File icon and Save Database command.
The PowerOLAP database is saved but not closed.