Saving and Launching Databases from within Excel

 

The following explains how to save the work you have done in a dynamically connected Excel spreadsheet so that the changes are saved in the PowerOLAP database; and, following that, how to launch PowerOLAP® if you open an Excel spreadsheet that has a dynamic connection to PowerOLAP®.

Save Changes from a Worksheet

To save changes to a PowerOLAP database that you have made through an Excel worksheet (e.g., entering Fact Data), use the Save Current Database In Modeler command found on the PowerOLAP menu in Excel. The database will remain open after it has been saved:

1.   Make the appropriate or desired changes in the worksheet.

2.   From the Excel ribbon, go to the Add-Ins tab, click on the PowerOLAP command and select the Save Current Database in Modeler sub-command.

The current, open PowerOLAP database is saved to disk.

 Notes

Saving changes to a PowerOLAP database from the PowerOLAP menu in Excel does not save changes to linked worksheets. Worksheets must be saved by selecting the Save (As) command in Excel to save the worksheet as an updated XLSX file.

 

Launch a PowerOLAP® Database from Excel

 

Launch Modeler

In order to work with a dynamically connected Excel worksheet, you must have an open connection between Excel and PowerOLAP®. “Slicing to Excel” from PowerOLAP® creates this link automatically. By opening a previously saved Excel worksheet with this connection you can also re-establish this connection: the Modeler command found on the PowerOLAP menu commands in Excel can be used to re-establish the connection when the PowerOLAP® is closed or the link is closed.

If at any time you close a connection between Excel and PowerOLAP® (for example, if you close PowerOLAP® while the “connected” Excel worksheet is still open), you can re-establish the connection to Excel in three (3) ways, as follows:

  • From the Excel ribbon, go to the Add-Ins tab and select PowerOLAP command then Modeler or simply click on the Launch Modeler button to open.
  • In Excel, click F9 with the cursor positioned in any cell;    
    the connection will be established and all the data will be recalculated
  • In Excel, click F2 from the database reference cell (normally B1), and then press Enter. This will not recalculate the cells, but just reopen the connection to PowerOLAP

If other Excel worksheets are open that have a connection to the same PowerOLAP database, they too will be “re-connected” to the database.

 Important

if you have more than one Excel spreadsheet open, with two or more having a connection to different PowerOLAP databases, and you proceed with any of the above, only one PowerOLAP database will be opened (recall that only one PowerOLAP database may be opened at a time). The spreadsheets connected to database(s) that do not open will show #VALUE in OLAPReadWrite/OLAPTable/OLAPPivot cells—they will neither return nor accept values. Therefore, only open PowerOLAP® from Excel when you have only one spreadsheet open, or when the two or more spreadsheets that are open are connected to the same PowerOLAP database.