This section concerns what may be the most important end-user feature of PowerOLAP®—the product's use of Excel as a front end. We will consider the PowerOLAP Add-In Menu and Commands; the different functions for creating a worksheet (OLAPReadWrite, OLAPTable and OLAPPivot; the functions that appear in a worksheet (OLAPDatabase and OLAPMember); entering data through Excel; and generally working through Excel as a sophisticated user would—including the many ways of changing data views, and the use of database reference formulas that will enable to bring virtually any and all PowerOLAP data into Excel.
|
As you know PowerOLAP® application allows you to create a dynamically connected bi-directional Excel worksheet from a Slice of a Cube. It is dynamic in that the worksheet data values reflect data updates and changes made in PowerOLAP® application. PowerOLAP® software creates the worksheet with a single click, establishing a bi-directional link with Excel. The connection between the two applications is bi-directional in that you may update and add data either from the Excel worksheet or from within a PowerOLAP slice. |
To demonstrate the ease of creating an Excel worksheet from a PowerOLAP slice:
1. Open (or create) a Slice in PowerOLAP.
In this exercise we will use the Sales Accounts by Months slice in the Sales cube, from the Section Working with Slices.
2. From the PowerOLAP ribbon go
to the Slice tab and in the Worksheet control group click on
the Worksheet icon or Create
Excel Worksheet command and select a worksheet output (e.g.,
OLAPTable).
Alternatively, you may simply hit F8.
PowerOLAP launches Excel if Excel is not already open, and displays the newly created worksheet (see following figure).
.png)
An Excel spreadsheet created in this manner can be saved as an XLS file (and, as you will see, maintains all of Excel's functionality). The connection between Excel and PowerOLAP is established automatically when you launch the XLS file and recalculate (hit F9). The connection works through database reference formulas within the cells of the worksheet, which reference dimension intersection points within the Cube—the figures you see.
Click on cell A7 to see the reference formula in the formula bar (assuming reads OLAPTable).
.png)
An Excel worksheet created from a Slice can be manipulated just like any other worksheet. The database references built by PowerOLAP can be moved and copied throughout the worksheet, just as you can copy and move Excel formulas within a normal Excel worksheet.
As an added advantage of Excel's connection to PowerOLAP®, a single Excel worksheet can be used as a template to enter and/or report data across a number of Members or even Cubes contained within a database. Excel formats remain in place when you change views, thus saving you time and effort in producing reports. If you used Excel graphing tools to generate a graph of your worksheet, that graph will update automatically as well. Using the Microsoft Web Publishing Wizard, you can publish Excel worksheet updates to your company Intranet.
PowerOLAP® offers total flexibility in working with Excel. You can build a bi-directional database reference in any cell in a worksheet. Thus, you can compose complex views of your business in a single worksheet.
If you use the OLAP Exchange® component of PowerOLAP®, you can set this feature to update Excel as transactions are recorded in an underlying relational, transaction-processing database system, guaranteeing that you work with the most updated information at your desktop— i.e., in Excel.