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