.png)
All available PowerOLAP® functions are listed below. The functions return a value or piece of information from the PowerOLAP® database to the Excel worksheet for your analysis. More description of their function and syntax is provided in the subtopics below.
Every PowerOLAP® function takes arguments, which are explained, albeit briefly, in the Excel Paste Function. If you click on a blank cell in a dynamically linked spreadsheet, and access PowerOLAP® Functions through the wizard, you will be guided to fill in text boxes to return the requested information.
2. OLAPAlias
6. OLAPCanAddDimensionAndCubes
7. OLAPChild
9. OLAPCube
10. OLAPCubeCount
11. OLAPCubeDimCount
13. OLAPCubeSecurity
14. OLAPCurrentUser
16. OLAPDimCount
17. OLAPDimension
19. OLAPDimMaxLevel
21. OLAPFactSecurityMemberCount
22. OLAPFactSecurityPrivileges
23. OLAPFactSecurityRangeMembers
28. OLAPFormula
29. OLAPGroup
30. OLAPGroupCount
31. OLAPIsChildOf
32. OLAPIsFormula
33. OLAPIsParentOf
35. OLAPMember
36. OLAPMemberCount
37. OLAPMemberFirst
38. OLAPMemberIndex
39. OLAPMemberLevel
40. OLAPMemberNext
43. OLAPMemberType
44. OLAPNavigate
45. OLAPNavigateRW
46. OLAPParent
47. OLAPParentCount
48. OLAPPivot
51. OLAPQuery
52. OLAPRead
53. OLAPReadWrite
54. OLAPRenameAlias
55. OLAPRenameMember
57. OLAPRoot
58. OLAPRootCount
59. OLAPSave
61. OLAPSubset
62. OLAPSubsetCount
63. OLAPSubsetMember
65. OLAPTable
66. OLAPUser
67. OLAPUserCount
68. OLAPUserGroup
70. OLAPUserInGroup
71. OLAPWeight
72. OLAPWrite
75. OLAPWriteTable
1. PowerOLAP® Functions
1.1 The Structure of Database Reference Formulas
Let's take a moment to look at a sample of what appears in the Excel Formula bar for one of these functions, e.g., OLAPReadWrite:
Note that with cell D8 selected, the Formula Bar shows the following:
OLAPReadWrite($B$1,$B$2,D$7,$C$3,$C$4,$A10,$C$5)
For the sake of getting the terminology straight, you should know that the entire expression is known as the Database Reference Formula—logically, since it appears in the Formula bar. OLAPReadWrite is the PowerOLAP Function. The letters and numbers appearing in the parentheses are known as the Argument they reference, as you might expect, other cells in the worksheet—establishing the coordinates, as it were, for the specific data point. The dollar sign ("$") fixes the column or row (letter or number, respectively) as an absolute reference within the worksheet; without the dollar sign, it exists as a relative reference.
If you were to double-click on a cell that returns an OLAPReadWrite figure—like the cell in question in the preceding figure—you would see the following occur within your worksheet:
.png)
All of the cells referenced in the argument would be highlighted, so you could quickly ascertain which are the cell's parametric components (Note that these highlighted cells are those listed in parentheses). In fact, the pointer ends up within the first cell in the argument (which is B1). The cell B1 is itself governed by a function (OLAPDatabase).
1.2 A Partial List of PowerOLAP® Functions
There are 74 PowerOLAP® Functions in all. The following is only a partial list, categorized below in three groups according to the results they yield: Data Transfer, Commands and Metadata Functions.
Data Transfer Functions
Command Functions
Metadata (or structure information) functions
Cube
Dimension
Members
Hierarchy
Alias
Subsets
For example, by selecting Insert Function command from the Excel main application menu, and scrolling down to PowerOLAP® in the Function category, you would receive the following information for OLAPAddMember, the first PowerOLAP® function listed:
.png)
Clicking OK would take you directly into the wizard, where a brief description exists as well, and where would you fill in the text boxes to complete the argument (shown in the following figure):
.png)