Advanced proCube Worksheet Functions

Advanced proCube Worksheet Functions

proCube uses a separate add-in, which is installed during the proCube installation, to provide numerous, extremely powerful functions for:

  • Reading from a proCube database

  • Writing data to a database

  • Querying a database

The Function Add-In is launched each time you open Excel. Excel functions can be accessed by selecting the Paste Function button on the Excel toolbar.

For a description of the available Excel paste functions, go to Excel Insert Functions.

The following exercises demonstrate two advanced worksheet functions. The first function is used to query a proCube database (using OLAPCube), and the second function is used to create a dynamic link from a “blank” worksheet (i.e., one without any previous links to proCube) using OLAPOpen.

Examples of advanced worksheet functions

  1. Open an Excel worksheet connected to a proCube database.

  2. Select any empty worksheet cell.

  3. Click fx (Insert Function) to open the Insert Function dialog.

  4. From the category drop-down, select proCube.Excel.api to display its functions.

insertfunctionolapcube.png

Figure 1.     Insert Function Dialog

  1. Highlight OLAPCube and click OK to open the Function Arguments dialog.

functionargumentsdialogsalescube.png

Figure 2.    Function Arguments Dialog - Sales Cube

  1. Click in worksheet cell B1 containing the server and database reference to insert the full path of the server and database into the Database field.

  2. Click in worksheet cell B2 to pick up the cube index number and insert it into the OCube field.

Your Function Arguments dialog should look like the figure shown above.

  1. 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 ERROR in the selected cell. To use proCube Functions in Excel, you must have a connected open database or, as shown in the following exercise, you must use the OLAPOpen function to open the link.

Creating a link to a database from Excel

To use proCube Functions in Excel, you must have a connected open database or, as shown in the following exercise, you must use the OLAPOpen function to open the link.

If you are following this guide sequentially, close any open proCube databases and close proCube. In Excel, close any open worksheets.

To create a link to a proCube database from Excel using the OLAPOpen Function:

  1. Create a new worksheet in Excel.

  2. Click in cell B1 to place the OLAPOpen function.

  3. Click fx to open the Insert Function dialog.

  4. Highlight and double-click OLAPOpen to open the Function Arguments dialog. The function pop-up appears.

  5. In the Server field, enter the name of the server

  6. In the Database field, enter Acme Trading Company.

  7. In the Provider field, enter "" (two double quotes).

Your Function Arguments dialog should appear as follows:

functionargumentsdialogdatabaselink.png

Figure 3.    Function Argument Dialog

  1. Click OK.

  2. Press F9. proCube will launch and the database you specified will open.

If you attempt to open a database that has been “secured,” proCube will launch, and you will be prompted to enter your Password.  Once you have done so, the database you specified will open.

The proCube database you have opened has a direct connection to Excel. You can now use any of the Excel functions from the list that follows to read information, based on data from proCube, into a specific cell. You can even use these functions to create macros in Excel that will update your worksheet(s) as the Metadata within proCube changes (for example, as new members are added to a dimension within proCube).

Excel insert functions

All available proCube Excel paste functions are listed below. These functions operate according to the same logic described in the preceding two examples of the OLAPCube and OLAPOpen. The functions return a value or piece of information from the proCube database to the Excel worksheet for your analysis.

Table 1.       Excel Insert Functions in proCube

Insert Function

Description

Format

OLAPAlias

Returns the alias from the specified alias Group at index.

Argument: (database, dimension, group, index)

OLAPAliasGroup

Returns the name of the nth alias Group for the specified dimension.

Argument: (database, dimension, n)

OLAPAliasGroupCount

Returns the number of alias Groups for the specified dimension.

Argument: (database, dimension)

OLAPAliasToMember

Returns the member of the dimension for the alias in the alias Group.

Argument: (database, dimension, group, alias)

OLAPCanAddDimensionsAndCubes

Returns True if the user or Group can add dimensions or cubes in the current database.

Argument: (database, user/group)

OLAPChild

Returns the nth child of the specified member in the specified dimension.

Argument: (database, dimension, member, n)

OLAPChildCount

Returns the number of children of the specified member in the specified dimension.

Argument: (database, dimension, member)

OLAPCube

Returns the name of the nth cube in the currently open database.

Argument: (database, n)

OLAPCubeCount

Returns the number of cubes in the currently open database.

Argument: (database)

OLAPCubeDimCount

Returns the number of dimensions in the specified cube.

Argument: (database, cube name)

OLAPCubeDimension

Returns the nth dimension in the cube.

Argument: (database, cube, n)

OLAPCubeSecurity

Returns the security privileges for the user or Group for the cube in the current database.

Argument: (database, cube, user/group)

OLAPCurrentUser

Returns the name of the current user in the current database.

Argument: (database)

OLAPDimCount

Returns the number of dimensions in the currently open database.

Argument: (database)

OLAPDimension

Returns the name of the nth dimension in the currently open database.

Argument: (database, n)

OLAPDimensionSecurity

Returns the security privileges for the user or groupfor the dimension in the current database.

Argument: (database, dimension, user/group)

OLAPDimMaxLevel

Returns the highest-level number in the dimension.

Argument: (database, dimension)

OLAPFactSecurityMember

Returns the indexed member for the cube, range and dimension in the current database.

Argument: (database, cube, RangeIndex, …)

OLAPFactSecurityMemberCount

Returns the number of members for the cube, range and dimension in the current database.

Argument: (database, cube, RangeIndex, …)

OLAPFactSecurityPrivileges

Returns the privileges of the point for the User/Group and cube in the current database.

Argument: (database, cube, user/group, k1, …)

OLAPFactSecurityRangeMembers

Returns the member list for the User/Group, cube and range in the current database.

Argument: (database, cube, …)

OLAPFactSecurityRule

Returns the text of the security range for the cube in the current database.

Argument: (database, cube, index)

OLAPFactSecurityRuleCount

Returns the number of security ranges for the cube in the current database.

Argument: (database, cube)

OLAPFactSecurityRuleName

Returns the name of the security range for the cube in the current database.

Argument: (database, cube, index)

OLAPFactSecurityStatus

Returns the status of the range in the cube in the current database.

Argument: (database, cube, index)

OLAPGroup

Returns the specified Group in the current database.

Argument: (database, index)

OLAPGroupCount

Returns the number of Groups in the current database.

Argument: (database)

OLAPIsChildOf

Returns true if the specified child belongs to the specified parent in the specified dimension.

Argument: (database, dimension, parent, child)

OLAPIsParentOf

Returns true if the specified child belongs to the specified parent in the specified dimension.

Argument: (database, dimension, child, parent)

OLAPIsSecureDatabase

Returns True if the current database is secure.

Argument: (database)

OLAPMember

Returns the nth member in the dimension.

Argument: (database, dimension, n)

OLAPMemberCount

Returns a count of the number of members in the dimension.

Argument: (database, dimension)

OLAPMemberFirst

Returns the first member in the specified dimension.

Argument: (database, dimension)

OLAPMemberIndex

Returns the index number of the member in the dimension.

Argument: (database, dimension, member)

OLAPMemberLevel

Returns the level number of the member in the dimension.

Argument: (database, dimension, member)

OLAPMemberNext

Returns the next member in the dimension after the specified member.

Argument: (database, dimension, member)

OLAPMemberProperty

Returns the property value for this member in this Group.

Argument: (database, dimension, member, group)

OLAPMemberToAlias

Returns the Alias from the specified alias Group for the member in the dimension.

Argument: (database, dimension, group, member)

OLAPMemberType

Returns the type of the member in the specified dimension.

Argument: (database, dimension, member)

OLAPOpen

Opens the specified database.

Argument: (server, database, file)

OLAPParent

Returns the nth parent of the specified member in the specified dimension.

Argument: (database, dimension, member, n)

OLAPParentCount

Returns the number of parents of the specified member in the specified dimension.

Argument: (database, dimension, member)

OLAPPivot

Builds a slice over the specified range for this cube.

Argument: (database, cube, range, zeroRows, zeroColumns)

OLAPPropertyGroup

Returns the name of the nth Property Group for this dimension.

Argument: (database, dimension, index)

OLAPPropertyGroupCount

Returns the number of Property Groups in the specified dimension.

Argument: (database, dimension)

OLAPRead

Returns the value of the cube based upon the parameters k1 to kn.

Argument: (database, cube, k1, k2, …, kn)

OLAPReadWrite

Returns the value of the cube based upon the parameters k1 to kn; and enables data entered in worksheet to be set back to database.

Argument: (database, cube, k1, k2, …, kn)

OLAPRoot

Returns the nth root of the specified dimension.

Argument: (database, dimension, n)

OLAPRootCount

Returns the number of roots in the specified dimension.

Argument: (database, dimension)

OLAPSave

Saves the specified database.

Argument: (server, database, file)

OLAPSliceSecurity

Returns the security privileges for the user or groupfor the slice in the current database.

Argument: (database, slice, user/group)

OLAPSubset

Returns the nth subset of the specified dimension in the currently open database.

Argument: (database, dimension, n)

OLAPSubsetCount

Returns the number of subsets in the specified dimension.

Argument: (database, dimension)

OLAPSubsetMember

Returns the nth member in the specified subset of the currently open database.

Argument: (database, dimension, subset, n)

OLAPSubsetMemberCount

Returns the number of members in the specified subset of the currently open database.

Argument: (database, dimension, subset)

OLAPTable

Creates a table of values from the cube based upon the parameters k1 to kn; also enables writing data from the worksheet to in the database.

Argument: (database, cube, k1, k2, …, kn)

OLAPUser

Returns the specified user the current database.

Argument: (database, index)

OLAPUserCount

Returns the number of users in the current database.

Argument: (database)

OLAPUserGroup

Returns the current user in the Group in the current database.

Argument: (database, group, index)

OLAPUserGroupCount

Returns the number of users in the Group in the current database.

Argument: (database, group)

OLAPUserInGroup

Returns the current user in the Group in the current database.

Argument: (database, group, user)

OLAPWeight

Returns the weight of the child relative to the parent in the dimension.

Argument: (database, dimension, parent, child)

OLAPWrite

Writes a value to a point in the specified cube.

Argument: (database, cube, value/member1, value/member2, …)

OLAPWriteAliasToCube

Returns the alias name if successfully written to the cube.

Argument: (database, dimension, aliasgrpName, …)

OLAPWriteMemberToCube

Returns the member name if successfully written to the cube.

Argument: (database, dimension, …)

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk