Entering Data from Within Excel

You can enter data into a PowerOLAP database using an Excel worksheet. This has great applicability in forecasting, planning and budgeting systems that use PowerOLAP^®. All data entered into a worksheet is automatically updated using one of PowerOLAP’s functions (OLAPTable has been shown here), each of which maintains a “bi-directional, dynamic spreadsheet connection” between PowerOLAP^® and Excel.

To enter data via an Excel worksheet:

1.   Select a cell at the intersection of Detail members, such as cell F9, which is the cell at the intersection of April, Net Sales.

2.   Type 100000.

3.   Press Enter, and then press F9 key (if Excel is set to Manual calculation) to recalculate the worksheet. The worksheet appears as below:

Now, to see the dynamic connection, you need to return to the PowerOLAP cube.

4.   Return to the PowerOLAP Accounts by Months slice (showing Canada as the Page Member).

5.   Press F9 to update PowerOLAP.
The Slice appears as follows:

The data you entered in the Excel worksheet is now reflected in the PowerOLAP database. Because PowerOLAP’s function connecting to the worksheet (OLAPTable, in this case) is bi-directional, you can enter data in either Excel or PowerOLAP and select F9 to update. (Note you can not write into Aggregate member spreadsheet cells or cells governed by a Cube formula, just as in a Slice).

 Important

The strength and power of the spreadsheet connection to PowerOLAP cubes are central to the use of the product: PowerOLAP^® “disburdens” Excel of its calculation tasks—hierarchies/ Aggregate weights/formulas are calculated in PowerOLAP’s engine, across specifiable multidimensional data ranges; further, PowerOLAP^® relieves users/organizations of the difficulties of maintaining hundreds or more linked spreadsheets.