Creating an Excel Worksheet


PowerOLAP® enables you to create an Excel worksheet using the parameters defined by an active Slice—the Dimensions you put into Pages, Columns and Rows in your PowerOLAP slice become Pages, Columns and Rows in Excel. The worksheet can then be used as a normal Excel worksheet that utilizes all features and functions available with Excel, all the while maintaining its link to PowerOLAP®. If you enter data in an Excel worksheet that is dynamically connected to PowerOLAP® and switch to a PowerOLAP Slice, the new data will be reflected in PowerOLAP after recalculating. Likewise, any data entered into PowerOLAP® will be reflected in a dynamically connected worksheet.

A dynamic data connection can be established between PowerOLAP® and an Excel worksheet one of four ways. The connection types are: OLAPReadWrite, OLAPTable, OLAPPivot or OLAPWriteTable. These can be selected from the PowerOLAP Options dialog, on the General tab. This setting is important when you want to create an Excel worksheet from a PowerOLAP slice.

The four types of Excel connections determine the way data is written to and retrieved from the PowerOLAP database. The settings influence the speed of recalculations and updates to the data, and therefore, PowerOLAP performance. These settings also determine flexibility in manipulating the data, and they factor into the way data can be physically displayed on the Excel spreadsheet. Until now, we have shown PowerOLAP slices with the default setting for creating an Excel worksheet, OLAPTable. Now that we are working in Excel, more explanation of these options is necessary.

The ensuing topics explain the five options for creating an Excel worksheet.