CUBESET

The CUBESET function is used in Excel to create a calculated set of members or tuples by sending a set expression to the cube on an external Analysis Services server.

Syntax

=CUBESET(connection, name, caption, expression)

connection The connection to an external data source that returns a set.
name The unique name for the set.
caption The caption to display for the set.
expression The Multidimensional Expressions (MDX) expression that returns the set from the cube.

About CUBESET

In the realm of Excel data analysis, CUBESET proves to be an invaluable tool for extracting specific sets of data from Multidimensional OLAP (Online Analytical Processing) cubes residing on external Analysis Services servers. By leveraging the power of this function, users can articulate precise set expressions in MDX to tailor the returned data according to their analytical requirements with finesse and efficiency. This capability facilitates the acquisition of targeted data subsets to fuel sophisticated business insights and decision-making processes. Therefore, if you find yourself delving into the intricacies of multidimensional data analysis, CUBESET emerges as a dependable companion for crafting bespoke data selections within an OLAP environment.

Examples

Suppose you are working with an external Analysis Services cube that stores sales data across different regions. To create a set of sales values above a certain threshold for further analysis, you could use the following CUBESET formula:

=CUBESET("SalesCubeConnection", "HighSalesSet", "Sales over $1000", "FILTER([Sales].[Region].[Region].Members, [Measures].[Sales Amount] > 1000)")

Questions

What role does the CUBESET function play in multidimensional data analysis?

The CUBESET function serves as a pivotal tool in multidimensional data analysis by allowing users to define specific sets of data through MDX expressions, thereby enabling targeted extraction of data subsets from external Analysis Services cubes.

How is the set expression constructed within the CUBESET function?

The set expression within the CUBESET function is constructed using Multidimensional Expressions (MDX), a query language for defining and manipulating multidimensional data in OLAP environments. This MDX expression outlines the criteria for the desired data subset.

Can I name and provide a caption for the set created with the CUBESET function?

Yes, when using the CUBESET function, you can assign a unique name and a descriptive caption to the set created, enhancing clarity and identification of the specified data subset.

In which scenarios can the CUBESET function be particularly beneficial?

The CUBESET function shines in scenarios where users need to extract tailored subsets of data from multidimensional OLAP cubes hosted on external Analysis Services servers for detailed analysis, reporting, and decision-making purposes.

Related functions

CUBEVALUE
CUBEMEMBER
CUBERANK
CUBEMIN
CUBEMAX
CUBESETCOUNT
CUBEELEMENT
CUBEXTRACT
CUBESCHEMA