The OLAPReadWrite function provides the bi-directional connection between the Excel worksheet and PowerOLAP database on a cell-by-cell basis. When the Excel worksheet is created from a Slice, formulas are written to each individual cell within a table on the worksheet, independent of surrounding cells. With each recalculation, the PowerOLAP database reads and writes data updates back and forth between each cell in the worksheet and the database.
It is advantageous to use OLAPReadWrite when you need to be able to move cells, rows and columns to different locations on the worksheet; to insert blank rows and columns into the table of data; and to use an Excel formula that incorporates the OLAPReadWrite function to return values within a cell. OLAPReadWrite may be less desirable when your data range is large, because it will slow performance. Additionally, although database reference formulas remain intact in worksheet cells even when data is typed over them, if you delete the cell contents, the reference formulas in the cells are deleted too (unless there is security over the cells in place). Typed values do not overwrite the references. In short, if you need full manipulative powers over the data in the Excel spreadsheet, and can sacrifice some speed and performance, OLAPReadWrite is the appropriate setting.
Before moving on to introduce the third type of dynamic connection—OLAPPivot—we can look at the difference between using OLAPTable and OLAPReadWrite in the creation of a worksheet, with the demonstration that follows.
OLAPTable and OLAPReadWrite—Differences
At the start of this chapter, you clicked on the OLAPTable cell A7 in the Excel worksheet and saw a formula governing the entire sliced array of Fact Data from the PowerOLAP slice. Let us now see what happens when using OLAPReadWrite to create a Slice worksheet in Excel.
1. Return to the PowerOLAP main application and access the Options dialog by going to the Home tab and selecting Options command in the Edit control group of the PowerOLAP ribbon.
2. In the General tab, go to the Create Excel Worksheet Using OLAP section and select OLAPReadWrite from the Excel Function drop down menu.
.png)
3. Press F8; Alternatively you may go to Slice tab and click on the Worksheet command.
.png)
Note, first, that there is NO OLAPTable function in the worksheet.
4. Click on cell B9.
Note the OLAPReadWrite reference formula in the formula bar:
.png)
This time, instead of a single OLAPTable formula governing the entire array of Fact Data, there are reference formulas in each data cell of the worksheet individually referencing back to PowerOLAP.
You could click on any cell containing data in the worksheet and see a like formula (preceded by OLAPReadWrite) because, when OLAPReadWrite has been used to create a worksheet, each cell contains its own database reference to a data point in the PowerOLAP database.
One important result of using OLAPReadWrite is that you can copy the formula to other cells and it will reference properly back to PowerOLAP. Likewise, you are able to insert rows and columns into a worksheet and the data remains undisturbed because the references remain intact. It is this flexibility in worksheet manipulation that makes OLAPReadWrite an attractive option when creating worksheets from PowerOLAP to Excel.
What are the practical implications of these options for your purposes of creating a worksheet?—i.e., what is the preferred method, OLAPTable or OLAPReadWrite? In addressing this issue, there are three considerations to keep in mind:
Both OLAPTable and OLAPReadWrite maintain a bi-directional connection to the PowerOLAP database; you can enter numbers through the worksheet and those figures will be updated in the database. [This is true for the OLAPPivot connection, which will be discussed shortly.]
OLAPTable does not allow you to move individual cells, nor does it allow you to copy or "extend a series" of a formula, across a range of cells.
OLAPTable is the fastest means of creating a dynamically connected Excel worksheet from a PowerOLAP slice. This will be particularly noticeable when you have a large Slice, with many data points: whereas OLAPReadWrite must load a formula in every cell, OLAPTable (because it uses one formula to reference all data points in the PowerOLAP database) can perform the "Slice to worksheet" in a fraction of the time.
OLAPDatabase and OLAPMember
Having examined OLAPTable and OLAPReadWrite and the means by which they send Fact Data to a worksheet—an array for OLAPTable, cell-by-cell for OLAPReadWrite—let's look at the other functions in a spreadsheet.
.png)
In the first few rows, formulas have been automatically built to communicate the following information:
Row 1: Full path and database name.
.png)
If you click on cell B1, the following appears in the formula bar:
.png)
The OLAPDatabase is the function that establishes the dynamic connection between PowerOLAP and Excel—thus, when you open a worksheet with an OLAPDatabase function and hit F9, PowerOLAP is launched and all data referenced in the worksheet updates.
Row 2: Cube name.
.png)
The Cube from which the Slice was created is displayed, as above.
Rows 3 onward: Page Dimension/Members, followed by Column and Row Dimensions
.png)
Beginning in row 3, as the preceding figure shows, the Page Dimensions are shown, and the specific Member for that Dimension (column 3). Thus, in the example worksheet, you can tell that the Region dimension appears, and the Member USA is being shown; likewise, the Product dimension appears, and the Member Aunt Re's Chicken is shown. (This Dimension-Page Member would continue were there more Dimensions in the Cube.) The last two Dimensions are shown, as the cells indicate Along Columns and Along Rows.
Returning to the Page Members for a moment, if you click on cell C3, you will see the following in the formula bar:
.png)
This OLAPMember function selects the single Page Member to view in the worksheet. As you will see several pages forward—in the topic, Changing Data Views from within Excel, this function enables you to instantly select different Page Members, as though you were leafing through a report, with each "page" a different Member of the Dimension (e.g., for Region, a report for USA, Canada, Belgium, etc., or for Product, a report for Aunt Re's Chicken, Charlie's Fire Sauce, etc).
Next, we will tackle the third method of creating/maintaining a dynamic connection between PowerOLAP® and Excel—the OLAPPivot function.