OLAP 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 Hyperion Essbase Spreadsheet Add-in to change values, enter formulas, and format data. Hyperion 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, Hyperion 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, Hyperion 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 Hyperion 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 Hyperion 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 Hyperion 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.

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

   Figure 161. P&L Worksheet After Retrieve & Lock
   

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

   Hyperion Essbase changes the affected data values.

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

   Hyperion Essbase updates the server and unlocks the data blocks.

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

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