Spreadsheet Add-in User's Guide for Excel

Updating Data on the Server

Data values are changed frequently in applications that involve planning, budgeting, and forecasting. After you retrieve data into the worksheet, you can use Essbase Spreadsheet Add-in to change values, enter formulas, and format data. Essbase is also designed to permit multiuser, concurrent database access and update. Depending on your security privileges, you may be able to modify all data values or a certain subset of values. To recognize cells to which you have read/write access, apply a visual cue, or style, to the cells. For more information, see "Applying Styles to Data Cells". To update data from a worksheet, you must lock the database area that contains the values that you want to change. Locking prohibits other users from changing the data that you want to update. Other users can retrieve locked data but cannot lock or change the data. You have exclusive update rights to that area.

Note:

You cannot update attribute-related data on the server because attribute data is always calculated dynamically and, hence, is not saved.

You can lock data values in three ways:

• The Retrieve & Lock command retrieves data into the worksheet while locking the corresponding data area on the server. When you perform a subsequent retrieval, Essbase automatically unlocks the previous data values.

  Note:

  The Retrieve & Lock command is not supported with Dynamic Time Series members.

• The Lock command locks information that you have already retrieved. When you perform a subsequent retrieval, Essbase automatically unlocks the previous data values.
• The Update Mode check box in the Mode tab of the Essbase Options dialog box automatically locks the corresponding database area for each retrieval.

To update the server with data values from the worksheet, use the Send command on the Essbase menu. After updating the server, the Send command automatically unlocks data (unless you are in Update Mode). You must disable Update Mode to stop locking blocks automatically.

You can unlock data blocks in two ways:

• The Unlock command unlocks all blocks that you have locked.
• The server automatically unlocks data blocks that have been locked for the maximum time allowed as defined by the Essbase system administrator. Automatic unlock ensures that blocks are not locked for extended periods of time.

P&l.xls, a sample Excel file installed as part of the default Essbase installation, illustrates how to update data on the server.

To view the P&l.xls worksheet:

1. Select File > Open.
2. From the \Essbase\client\sample directory, open the P&l.xls file.
3. Select Essbase > Retrieve & Lock.

   Essbase retrieves data and locks the appropriate area of the database.

   Figure 159. P&L Worksheet After Retrieve & Lock
   
   
   

4. Change the value for Sales in Jan (cell B12) to 4000 and press Enter.

   Essbase changes the affected data values.

5. Select Essbase > Send to update the server with the new values.

   Essbase updates the server and unlocks the data blocks.

   Figure 160. P&L Worksheet After Sending New Values to the Server
   
   
   

6. Close P&l.xls without saving it.

Note:

Essbase provides a worksheet update logging facility that tracks and logs all data updates sent from Essbase Spreadsheet Add-in to the server. The Essbase system administrator enables this facility for extra protection against data loss. For more information, see the Database Administrator's Guide or contact the Essbase system administrator.