Using Excel as a Front End with PowerOLAP

Selecting a Page Member to View in Excel

1. Double-click the Page/Member cell (e.g., cell C3 for the United States). The “Select a Member” dialog box appears. Note the two tabs (circled): you can find Members based on where they appear in the dimensional hierarchy or in the Member list (this tab is selected below).

2. Select Canada.

3. Click “OK”, and then press “F9” to calculate and automatically update the worksheet.

The worksheet now shows data for the new Page Member, Canada. You can repeat this means of selection via the “Select a Member” dialog, which PowerOLAP has made available in Excel, to pick other countries in the Regions dimension. [Were this a four-, five- , etc. dimensional Cube, you could pick any number of Page members to view, multiplying your potential sheaf of reports manyfold!]

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.

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

2. Type “100000”.

3. Press “Enter”, and then press “F9” (if Excel is set to Manual calculation) to recalculate and update the worksheet:

Now, to see the dynamic connection back to the PowerOLAP cube:

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

2. 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. The OLAPTable functionis one of many functions you can use to dynamically link data between a worksheet and a PowerOLAP database. The other PowerOLAP functions for creating a worksheet from a Slice are: OLAP ReadWrite and OLAPPivot.

NOTE: You can not write into Aggregate member spreadsheet cells or cells governed by a Cube formula, just as in a Slice).

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.

Defining Cube References

The bi-directional connection shown so far is OLAPTable function (shown in cell A6). In fact, when you click on cell A6 in the worksheet, you will see in Excel’s formula bar the single formula that references all the worksheet cells that connect to data in the PowerOLAP cube:

=OLAPTable($B$1,$B$2,B5:R5,A6:A9,$C$3)

The next exercise will demonstrate how to define a Cube reference that returns a pertinent value into a cell by using the OLAPRead function.

1. Select cell D13 in Excel in the current worksheet. In this cell, you will define a Cube reference formula that shows the Gross Profit for United States in February (in order to make a quick visual comparison to Gross Profit for Canada, which appears in cell D9).

2. From the Excel menu bar, select PowerOLAP > Edit Formula. The Edit PowerOLAP Formula dialog box is displayed:

3. Select OLAPRead from the drop-down menu (to the right of “Function”).

4. Press the “Pick” button,  (next to Database). The current database, which contains the value you want, is pre-selected.

5. Click “OK”. You are returned to the Edit PowerOLAP Formula dialog box.

6. Press the “Pick” button,  (to the right of Cube). The Select Cube dialog box is displayed. Likewise, this Cube contains the value you want to be referenced into the Excel cell, D13.

7. Click “OK”. Again, you are returned to the Edit PowerOLAP Formula dialog box. Now you have defined several of the parameters of the Cube reference formula.

Note that the Dimensions area now displays text boxes for you to enter choices for the Months, Accounts and Regions dimensions. In fact, February has been “pre-selected” for you. If you wanted another Months member for your formula, you would press the “Pick” button to make a different selection. Since you do want to select February data, continue to the Accounts and Regions dimensions. Use the “Pick” button and the corresponding Select A Member dialog boxes to choose data for Gross Profit and United States, respectively.

After you have made these choices, the Edit PowerOLAP Formula dialog box will look as follows:

8. Click “OK” to update Excel with the new formula reference. Your dynamically connected Excel spreadsheet will appear as in the following figure:

You now have a ready view of the February, Gross Profit for United States within a dynamically connected spreadsheet that shows figures for Canada. Now, whenever any of these values changes, it will be reflected in this worksheet. You can save the current database, with changes you have made, from within Excel.

9. Select PowerOLAP > Save Current Database in Modeler. The PowerOLAP database is saved but not closed.

Closing a Database

The Close Database command located on the File menu in PowerOLAP closes an open database. When you have completed work within one database, you may still wish to work with another database. You must first close the currently open database before opening another database.

1. Select File > Close Database. If any Slices are open, PowerOLAP will prompt you to save them before closing.

2. Clicking “Yes” will save all database changes to disk and close the database file. Clicking “No” will close the database file without saving any changes made to the database. In either case, all open Slices will be closed along with the database.

As noted earlier, you can save and close any dynamically connected worksheet as a normal XLS file. Upon opening such a worksheet, when you press F9, PowerOLAP launches, and a spreadsheet system with OLAP cubes behind it, is ready for online, optimized planning / analysis / reporting.