CUBESET function

The CUBESET function in Excel is used to define a set of members or tuples by sending a set expression to the cube (OLAP or data model). It is commonly used in conjunction with other cube functions, such as CUBERANKEDMEMBER and CUBEVALUE, to extract and analyze data from an OLAP cube or Power Pivot data model. The CUBESET function is particularly useful for generating dynamic reports that summarize specific sets of data, such as a set of top-selling products, regions, or employees.

Syntax

CUBESET(connection, set_expression, , [sort_order], [sort_by])

Parameters

• connection: The name of the connection to the OLAP cube or data model. This is typically a text string representing the data source connection in your workbook (e.g., “SalesData”).
• set_expression: A text string that defines a set of members or tuples within the cube. This can be a direct set expression (like a list of product names) or a more complex MDX (Multidimensional Expressions) query.
• caption (optional): A text string that specifies the name to display in the cell where the set is shown. If omitted, no caption is displayed.
• sort_order (optional): Specifies how to sort the members of the set. The options are:
  • 0: No sorting (default).
  • 1: Ascending (alphabetical or numerical order).
  • -1: Descending (reverse alphabetical or numerical order).
• sort_by (optional): A text string that represents a measure or tuple to sort the set by. If you want to sort the set by a specific measure, you can specify that measure here.

Usage Example

Let’s say you have a cube connection named “SalesData”, and you want to create a set of top products or regions.

1. To define a set of all products:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "All Products")
   

2. To create a set of all regions:

   =CUBESET("SalesData", "[Geography].[Region].Members", "All Regions")
   

3. To create a set of top-selling products (by sales), assuming you have a sales measure:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "Top Products", -1, "[Measures].[Sales]")
   

   This creates a descending order set of products based on the Sales measure.

4. To create a set of top 5 products based on profit, sorted in descending order:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "Top 5 Products", -1, "[Measures].[Profit]")
   

Sorting Sets in CUBESET

The CUBESET function allows sorting of members in the set by specifying the sort_order and sort_by parameters.

• sort_order: Specifies how you want the set to be sorted (ascending or descending).
• sort_by: Indicates which measure or tuple to use for sorting. This allows sorting based on numerical data such as sales, profit, or any other relevant measure.

For example, to create a set of regions sorted by Revenue in ascending order:

=CUBESET("SalesData", "[Geography].[Region].Members", "Regions by Revenue", 1, "[Measures].[Revenue]")

Working with the Set in Other Functions

Once you define a set with CUBESET, you can use it with other cube functions like CUBERANKEDMEMBER or CUBEVALUE to retrieve data related to the members of that set.

For example, after defining a set of products, you can use CUBERANKEDMEMBER to retrieve the top product:

=CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "Top Products"), 1)

You can also use CUBEVALUE to return the sales of a set of products:

=CUBEVALUE("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members"), "[Measures].[Sales]")

Key Uses

1. Dynamic Reporting: The CUBESET function is a powerful tool for creating dynamic reports that allow you to group members or tuples from the cube based on certain criteria, such as product categories, regions, or periods.
2. Top-N Analysis: You can define sets based on the top N or bottom N items by sorting members on a measure, such as sales or profit, to generate reports that highlight the best or worst performers.
3. Working with Complex MDX Expressions: CUBESET supports complex MDX (Multidimensional Expressions) queries, which allows advanced users to retrieve very specific subsets of data from a cube or OLAP data source.

Common Errors

• #NAME?: This error occurs if the connection name is incorrect, or if cube functions are not supported in your version of Excel.
• #N/A: This error appears if the set expression is invalid or does not return any members. It may also occur if the sorting expression (sort_by) is not valid in the cube.

Practical Example

Let’s say you are working with a cube that contains sales data by product, and you want to create a report that lists the top 5 products based on sales. Here’s how you could do it:

1. Define the set of all products:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "All Products")
   

2. Sort the set by sales and retrieve the top 5 products:

   =CUBESET("SalesData", "[Product].[Product Name].Members", "Top 5 Products", -1, "[Measures].[Sales]")
   

3. Use CUBERANKEDMEMBER to display the top 1 product:

   =CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "Top Products"), 1)
   

4. Use CUBEVALUE to return the sales for the top-ranked product:

   =CUBEVALUE("SalesData", CUBERANKEDMEMBER("SalesData", CUBESET("SalesData", "[Product].[Product Name].Members", "Top Products"), 1), "[Measures].[Sales]")
   

Summary

The CUBESET function is a versatile and powerful Excel tool that allows you to define a set of members or tuples from an OLAP cube or data model. It enables you to sort, rank, and filter members dynamically, making it highly useful for reporting and analysis. Whether you are creating top-N reports, ranking regions by revenue, or grouping members based on custom criteria, CUBESET provides a robust solution for handling complex data sets in Excel.