Building a database reference in an Excel worksheet is not a complex operation, even for an introductory user. For one, it is not necessary to write the database references entirely on your own: PowerOLAP® provides an Edit Formula Wizard to assist you with the process of building database reference formulas.

.

As discussed in the previous section, database references are used to maintain the bi-directional connection between Excel worksheets and PowerOLAP databases. Although PowerOLAP® automatically builds database reference formulas when you "Slice" data into an Excel worksheet, you may want to build additional formulas or edit existing formulas within the worksheet. Using the Edit Formula function, you can create a connection to a PowerOLAP database to return a single cell reference in your worksheet instead of an entire 'sliced' table. PowerOLAP's Edit Formula Wizard simplifies the task for you.

The following procedure will demonstrate how to build a database reference formula within an Excel worksheet that is linked to a PowerOLAP database. [In fact, this will involve clearing and restoring data that currently exists in the worksheet, but the procedure is the same if you are beginning in a blank cell.]

 

1.   Open an Excel worksheet connected to a PowerOLAP database.
We can use the worksheet from the last exercise as an example.

2.   Click on the first cell containing a database reference returning Cube Fact data. In the figure below, the first data cell containing a database reference is B9 (circled).

3.   Select a range of cells that display Cube data—for example,
B9:D16 (Quantity, January until Cost of Sales, August) in the preceding figure).

4.   Delete the Range of cells that was selected.

5.   Click in the first cell that you want to return Cube data
(in this case, cell B9).

6.   In the Excel ribbon, go to PowerOLAP tab and click the Formula command icon.
The Edit PowerOLAP Formula dialog box is opened:

 

The first item in the Edit PowerOLAP Formula dialog box is the Function text box (circled). There are two functions provided by the Edit Formula Wizard from the drop-down menu: OLAPReadWrite, and OLAPRead. The purpose of each is described below:

■    OLAPReadWrite :
- Here again we can choose a ReadWrite cell reference in our Excel worksheet.

 

■    OLAPRead :
- The OLAPRead function provides read-only access to a PowerOLAP Cube. Thus, an OLAPRead database reference formula returns data from a Cube into a worksheet cell, but does not allow you to enter data into the worksheet cell and send it back to the database.

 

The next items in the Edit PowerOLAP Formula dialog box are the Database and Cube text boxes. The Pick buttons each allow you to choose a name from a list of available databases and Cubes. This option is useful when the database and Cube names that you want to access do not appear in your Excel worksheet.

Next step is to pick a database and Cube.

7.   Click the Pick button next to the Database text box.
In the Select Database dialog box that appears, you are given a choice of available databases that can be connected to this worksheet.
Make a selection (in the example, …Avon Trading Company.olp), then click on OK.

8.   Do likewise after clicking the Pick button next to the Cube text box (e.g., pick Sales).

Note that the Dimensions section appears, and the Wizard has "intuitively entered the last two Members. In the example, circled below, Quantity has been entered for the Sales Account dimension, and January for Month. [This occurs because the cell occurs within a known data range—if this were a blank cell, say F9, the dialog box would be slightly different.]

9.   Next to Region, click on the Pick button.
The Select a Member dialog box appears—use this dialog box to pick a Member (e.g., USA). Click OK.

10. Next to Product, also click on the Pick button.
Select the desired Member (e.g., Aunt Re's Chicken). Click OK.

11. If these are correct references for the cell, click OK; otherwise you would use the Pick button to select remaining members.

The following figure illustrates an example of a completed Edit PowerOLAP Formula dialog box:

12. Click OK to close the dialog box and return to the worksheet.
You will see #RECALC in the cell, so click F9 to recalculate.
The Fact Data value that you specified in the previous steps appears.

13. You can now copy the cell, or "extend the series" to the relative range, e.g., copy B9 across to D9, and then down to D16. This is because PowerOLAP maintains this function in Excel (another example of how PowerOLAP enables users to "work with Excel the way they always do").

14. Press F9 to recalculate.

Note that other cells are now populated with a range of like database references, and they return Fact Data that exists in their coordinates in the PowerOLAP database.

 

 

 

Edit Formula Text

 

PowerOLAP^® allows you to modify database reference formula arguments. Using the Edit Formula Text command found on the PowerOLAP menu, you can change a single cell reference, or change the entire database reference.

1.   Open an Excel worksheet connected to a PowerOLAP database
(you can use the same worksheet from the previous example).

2.   Click on a cell containing a database reference formula,
e.g., cell B9 in the example worksheet.

3.   From the Excel ribbon, go to the PowerOLAP tab and select the  Formula command icon.
The Edit PowerOLAP Formula Wizard is opened, with the database reference cell (B1) active for editing:

 

As in the previous exercise, the dialog box displays the formula that references a single data point in a PowerOLAP database. In this dialog box, you can manually change the formula or use the Pick boxes to specify a different data point.

4.   Make changes to the formula as desired.

5.   Click OK to update and return to Excel.