This post will introduce the Excel CUBESET function, a powerful tool for working with multi-dimensional data stored in a cube. The CUBESET function allows you to define a set of members or tuples in a cube hierarchy, based on one or more sets of members.
This set can then be used in other cube functions, such as CUBEMEMBER and CUBEVALUE, to retrieve data associated with the members in the set.
The function is an essential component of the data analysis capabilities in Excel and is an important tool for performing complex data analysis tasks and retrieving valuable insights from your data.
The Excel CUBESET function is only available in Microsoft Excel versions that support PowerPivot and the OLAP data model. This includes Excel 2013 and later versions for Windows, and Excel 2016 and later versions for Mac. If you are using an earlier version of Excel, you will not be able to use the CUBESET function.
Additionally, the CUBESET function requires a connection to a cube data source, so if your data source is not connected or if you don’t have access to a cube data source, you won’t be able to use the function.
How To Use CUBESET Function
The syntax for the Excel CUBESET function is:
=CUBESET(connection, set_expression)
Where:
- “connection” is the name of the connection to the cube data source.
- “set_expression” is a string that defines the set of members or tuples in the cube hierarchy.
The CUBESET function uses a set expression to define a set of members or tuples in the cube hierarchy. The set expression is a string that specifies the members you want to include in the set, based on one or more sets of members. The set expression can include multiple sets of members, each separated by a comma.
The CUBESET function Example
Here is an example of how to use the CUBESET function in Excel:
Step 1: To use the CUBESET function, you must first connect to a cube data source. Let’s assume you have a cube data source named “ThisWorkbookDataModel” that contains sales data for different Month, products.
Step 2: Define the set expression: To create a set of all members in the “product” dimension, you can use the following set expression: “[Table1 2].[Product].[All]“.
Step 3: To use the CUBESET function, enter the following formula in a cell:
=CUBESET("ThisWorkbookDataModel","[Table1 2].[Product].[All]","all product")
This formula creates a set of all members in the “Product” dimension from the “ThisWorkbookDataModel” data source.
Step 4: To retrieve the total sales for the members in the set, you can use the following formula:
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Sum of Sales]",CUBESET("ThisWorkbookDataModel","[Table1 2].[Product].[All]"))
This formula retrieves the total sales for all members in the set, which are all members in the “product” dimension.
When should you not use CUBESET in Excel?
There are several situations when you should not use the CUBESET function in Excel:
- No cube data source connection: If you do not have a connection to a cube data source, the CUBESET function will not work. The CUBESET function requires a connection to a cube data source in order to retrieve data.
- Non-MDX set expressions: The set expression used in the CUBESET function must be a valid MDX (Multidimensional Expressions) expression. If you use an invalid set expression, the CUBESET function will return an error.
- Inefficient set expressions: If you use an inefficient set expression in the CUBESET function, it can cause performance issues and slow down your workbook. It’s important to understand the structure of your cube data source and use an appropriate set expression to minimize performance issues.
What are the formulas similar to CUBESET in Excel?
Here are some similar formula to CUBESET in Excel:
- CUBEKPIMEMBER: Returns the unique name of a Key Performance Indicator (KPI) in the cube hierarchy.
- CUBEMEMBERPROPERTY: Returns the value of a property for a specified member in the cube hierarchy.
- CUBERANKEDMEMBER: Returns the nth, or ranked, member in a set, based on a specified measure.
- CUBESETCOUNT: Returns the number of items in a set.
- CUBEVALUE: Returns a value from a cube, based on a specified set of members.
Conclusion
The CUBESET function is a powerful tool for retrieving sets of data from a cube data source in Excel. The function allows you to retrieve data using a set expression, which provides a flexible and efficient way to retrieve data from a cube data source. However, the CUBESET function is not suitable for all situations and it is important to understand when to use and when not to use the function.