PowerOLAP's add-in to Excel provides numerous, extremely powerful functions for reading data from a PowerOLAP database; writing data to a database; or querying a database to retrieve Meta Data information. These functions can be accessed by selecting the Insert Function button on the Formulas tab of the Excel ribbon. (This button is identical to the PowerOLAP toolbar button, Edit Formula, but is on the Excel toolbar.)
Notes
A list of all available Excel functions, and a description of their use,
appears at the end of this topic —List Excel Functions.
The following exercises concern two advanced worksheet functions: the OLAPCube function for querying a PowerOLAP database; and to the OLAPDatabase function, for creating a dynamic link from a "blank" worksheet, i.e., one without any previous links to PowerOLAP®.
The general procedure for accessing advanced functions is detailed in the following topics.
Examples of Advanced Worksheet Functions
To access the PowerOLAP® Excel function/s:
1. Open an Excel worksheet connected to a PowerOLAP database.
2. Select any empty worksheet cell.
3. Select the Insert Function
button (found beside Formula Editor bar), .png)
, on the
Excel toolbar.
The Insert Function dialog box will appear on-screen.
4. Select PowerOLAP from the "Or select a category" drop-down box (it appears at or near the bottom of the list).
.png)
5. Choose a function from the Function name list box (for example, OLAPCube).
6. Click OK.
The Function Arguments pop-up dialog box appears:
.png)
7. Type in the <full path to the database name> or select it from a cell (e.g., B1) in the worksheet.
Tip
You can drag the pop-up in order to view the sheet underneath, or you can
click on the .png)
button
on the right end of the text field to shrink the pop-up box and select
the cell reference.
8. Click in the n text box and type in the <Cube index number> (for example, 1) or select a cell in the worksheet, for example, containing a Cube index numbers.
The pop-up updates and returns the relevant OLAPCube name at the bottom of the dialog box. In the figure below, the function has determined that Cube 1 in the selected database is Sales.
.png)
9. Click OK to update and return to Excel.
The selected cell now contains the result of the function expression. You can see the cell formula in Excel's formula bar above the sheet (in the example above, you will see the Cube name Sales in the formerly blank cell). Changing the formula's index number—the number after the comma in the formula—will cause a different Cube name to be shown in the cell. Changing the index number to a number greater than the total index numbers for that variable in the database will return #VALUE! Error in the selected cell.
Important
Remember to use PowerOLAP® Functions in Excel, you must have a connection
to an open database or, as shown in the following exercise, you must use
the OLAPDatabase function to open a connection.
To create a link to a PowerOLAP database from Excel using the OLAPDatabase Function:
(If you are following this manual sequentially, close any open PowerOLAP databases and close PowerOLAP®. In Excel, close any open worksheets.)
1. Create a new worksheet in Excel.
2. Select a cell in which to place the OLAPDatabase function (e.g., cell B1).
3. Select the Insert Function button from the Formulas tab of the Excel ribbon.
4. In the Insert Function dialog box, select and double-click OLAPDatabase. The Function Arguments pop-up dialog box appears.
5. In the Server text box, type the server location (if you are not using a Server, type ""—instructing the function not to use a Server).
6. In the database text box, type the
database name
(for this example, type Avon Trading Company).
7. In the file text box, type the full
path to the file, for this example, type
C:\Program Files (x86)\PowerOLAP\Examples\Avon Trading Company.olp.
(NOTE: If you are using the server option, type the path to the location
of the file on the Server.). The dialog appears as below:
.png)
8. Click OK. PowerOLAP will launch, and the database you specified will be open.
Notes
If you are attempting to open a database that has been "secured,"
PowerOLAP® will launch, and you will be prompted to enter your
Password. Once you have done so, the specified database will open.
The PowerOLAP database you have opened has a direct connection to Excel. You can now use any one the Excel functions from the list that follows to read information, based on data from PowerOLAP®, into a specific cell or into an array of cells.
Using the procedures described in the preceding pages, you can quickly generate Excel worksheets that contain only the specific Meta Data and Fact Data that is relevant to your analysis. This has enormous implications for your construction of reports. Instead of creating and being limited to the data the way it appears in a "sliced worksheet," you can customize a dynamic Excel spreadsheet(s) so that it contains any and all information from a PowerOLAP Cube, and make it appear any way you determine in the spreadsheet environment.
All available PowerOLAP® functions are listed below. These functions operate according to the same logic described in the preceding two examples of the OLAPCube and OLAPDatabase. The functions return a value or piece of information from the PowerOLAP database to the Excel worksheet for your analysis. The lists below are screen captures of the Function Name list box in its entirety. More description of their function and syntax is provided in the section covering Advanced Reference Materials. Further, the online Help contains expanded explanations and examples of these functions.
.png)
PowerOLAP® Excel Functions
NOTE: The full range of Excel Functions is explained in detail in the ONLINE HELP for PowerOLAP® under the Excel Functions topic of the Advanced Functions.