OLAPWriteTable

The OLAPWriteTable is the enhanced version of the OLAPTable function in that it can function as a regular OLAPTable but it can also be used to create a formula in Excel which will actually recalculate or change the particular data point in PowerOLAP^{®} but it will still retain the formula in the corresponding cell in Excel. The OLAPWriteTable consists of four modes: (1) *Display Cube Values*, (2) *Use Excel Formulas*, (3) *Commit formula values to Cube*, and (4) *Clear formulas and display cube values*.

To access the different OLAPWriteTable modes, simply double click on the cell containing the OLAPWriteTable function. This action will bring up a dialog box which drops down to the different modes available and users can easily select the desired option/mode to be used. By default, the OLAPWriteTable function is set to *Display Cube Values* mode.

The advantage of using this function is that it enables a user to work in multiple modes. On top of being able to enjoy the functionalities of working with an Excel worksheet that is dynamically linked to a database, a user also is provided with an alternative option for populating a corresponding database through the use of Excel formula driven values, instead of having to define them from within the PowerOLAP application.

This function will be extremely beneficial to those users who are not familiar or who do not have an adequate knowledge and understanding of the cube formula creation in a PowerOLAP database. By setting the OLAPWriteTable to be in the ‘*Use Excel Formulas Mode*‘ (2nd Mode), any user can perform simple formula calculation directly within Excel and push the result back to the PowerOLAP database.

One of the parameters of this function is it needs to be flagged as either “1” ,”2″, “3” or “4” which will dictate or determine the mode that will be used by the OLAPWriteTable function.

Mode 1: Display Cube Values

OLAPWriteTable: In Display Cube Values Mode

Mode 2: Use Excel formulas

OLAPWriteTable: Excel Formula Mode

Mode 3: Commit formula Values to Cube

OLAPWriteTable: Commit Formula Values to Cube Mode

Mode 4: Clear formulas and display cube values

OLAPWriteTable: Clear Formulas and Display Cube Values Mode

Mode 5: Use Excel fomulas and commit values

To demonstrate OLAPWriteTable functionality and how the different modes works

1. Open a slice in the appropriate database.

In the following example, a slice is created out of the *Sales* cube from the PowerOLAP sample database called *Avon Trading Company*.

2. From the PowerOLAP ribbon go to the **Slice** tab, then in the Worksheet control group, click on the **Worksheet** drop down command button and select **OLAPWriteTable** option.

Using OLAPWriteTable: **Display Cube value Mode (1)**

3. Click on the cell corresponding to *Quantity* for the month of *January* (cell **B9** in the screenshot) and change the value to **100**.

Using OLAPWriteTable: **Use Excel Formulas Mode (2)**

4. Double click on the **OLAPWriteTable** function and pick ‘**Use Excel Formulas**‘ option from the dialog that appears.

5. Go to cell **F8** and type **1.1** (equivalent to 110 %).

6. In the cell corresponding to sales Quantity for February (cell **B10** in the screenshot), type the formula equal to Sales ** Quantity for January * 1.1** (make sure that in the formula, the cell corresponding to 1.1 should be formatted to be an absolute reference).

7. Press **F9** or recalculate the worksheet.

Using OLAPWriteTable: **Commit formula Values to Cube Mode (3)**

8. Copy and paste the formula from cell **B10** to the cells corresponding to *Quantity* for *March* until *December* (in the example, paste to cell **B11 to B20**).

9. Press **F9** or recalculate the worksheet.

10. Double click on the **OLAPWriteTable** function and pick ‘**Commit formula Values to Cube**‘ option from the dialog that appears.

Using OLAPWriteTable: **Clear formulas and display cube values (4)**

11. Double click on the **OLAPWriteTable** function and pick ‘**Clear formulas and display cube values**‘ option from the dialog that appears.

12. Press **F9** or recalculate the worksheet