How to Use the CUBESET Function in Excel
Today we are going to delve into the CUBESET function, available in both Microsoft Excel and Google Sheets. CUBESET is an advanced function that enables you to define and retrieve a set of members or tuples from a cube on the server back into your spreadsheet.
Basic Syntax
The CUBESET function shares a similar syntax across both Microsoft Excel and Google Sheets:
CUBESET(connection, set_expression, caption, hierarchy_expression, [member_property])- connection: This is your link to an external data source, typically an OLAP cube, within your workbook.
- set_expression: A string that specifies a set of members or tuples by sending this expression to the cube on the server.
- caption: (Optional) A label that you assign to the resultant set for easier identification.
- hierarchy_expression: (Optional) An MDX (Multidimensional Expressions) string that defines how members or tuples are returned.
- member_property: (Optional) This additional string expression can be used to fetch specific properties of a member.
Example Usage
Let’s go through a few examples to clearly understand how the CUBESET function operates.
Example 1: Basic CUBESET Function
Here, we’ll demonstrate a simple use of the CUBESET function to pull a data set from an OLAP cube.
| Data | Formula |
|---|---|
| Set Name | =CUBESET(“OLAPDB”,”[Product].[Category].MEMBERS”,”Product Categories”) |
In this scenario, we connect to an OLAP database named “OLAPDB” and extract all members from the product category hierarchy, naming the set “Product Categories.”
Example 2: CUBESET with Hierarchy Expression
This example incorporates a hierarchy expression to refine our CUBESET function further.
| Data | Formula |
|---|---|
| Set Name | =CUBESET(“OLAPDB”,”TOPCOUNT([Product].[Category].MEMBERS, 5, [Measures].[Sales Amount])”,”Top 5 Product Categories by Sales”) |
In this case, the function not only retrieves product categories but also selects the top 5 categories based on sales amounts, facilitated by the hierarchy expression “TOPCOUNT([Product].[Category].MEMBERS, 5, [Measures].[Sales Amount]).”
Using the CUBESET function in Excel or Google Sheets, extracting specific sets of data from OLAP cubes becomes straightforward and customizable. Try varying the set and hierarchy expressions to tailor your data extraction to meet your specific needs.
More information: https://support.microsoft.com/en-us/office/cubeset-function-5b2146bd-62d6-4d04-9d8f-670e993ee1d9