CUBEMEMBER: Excel Formulae Explained

Excel, a powerful tool in the world of data analysis, offers a plethora of functions and formulae to simplify complex calculations and data manipulations. One such function is the CUBEMEMBER function. This function is part of Excel's suite of cube functions, which are designed to work with data stored in OLAP cubes or data models.

Understanding the CUBEMEMBER Function

The CUBEMEMBER function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It is used to return a member or tuple in a cube hierarchy. Members can be thought of as items within a data category in an OLAP cube. For instance, in a sales data cube, the members of the 'Product' hierarchy might include 'Bicycles', 'Accessories', and 'Clothing'.

The CUBEMEMBER function can be used in Microsoft Excel versions starting from 2007. It is commonly used in Excel reports and dashboards that are connected to an OLAP cube or Tabular data model in Power Pivot. The function helps in fetching specific elements from the cube, based on the defined criteria.

Function Syntax

The syntax for the CUBEMEMBER function in Microsoft Excel is:

CUBEMEMBER(connection, member_expression, [caption])

Here, 'connection' refers to the worksheet connection to the OLAP cube, 'member_expression' describes the member or tuple in the cube hierarchy, and 'caption' is an optional argument that defines a label for the returned member.

How to Use the CUBEMEMBER Function

Step 1: Establish a Connection

The first step to using the CUBEMEMBER function is to establish a connection with the OLAP cube. This is done through the 'Data' tab in Excel. You can connect to an OLAP cube by selecting 'From Other Sources' in the 'Get External Data' group and then choosing 'From Analysis Services'.

Once the connection is established, it can be used as the first argument in the CUBEMEMBER function.

Step 2: Define the Member Expression

The member expression is a string that identifies a member in the cube hierarchy. It is usually in the form of a unique name, such as '[Product].[Bicycles]'. The unique name should be enclosed in square brackets.

The member expression can also be a tuple. A tuple is a combination of members from different hierarchies, such as '[Product].[Bicycles], [Time].[2019]'. This would return the sales of bicycles in 2019.

Step 3: Specify the Caption (Optional)

The caption is an optional argument that can be used to provide a label for the returned member. If the caption is not specified, Excel will use the member's default caption.

For instance, if you use the member expression '[Product].[Bicycles]' and the caption 'Bike Sales', the CUBEMEMBER function will return 'Bike Sales' instead of 'Bicycles'.

Examples of CUBEMEMBER Function

Let's consider a few examples to understand the application of the CUBEMEMBER function.

Assume we have a connection named 'SalesCube' to an OLAP cube that contains sales data. Here are a few ways we could use the CUBEMEMBER function:

  1. =CUBEMEMBER("SalesCube", "[Product].[Bicycles]") - This formula would return the member 'Bicycles' from the 'Product' hierarchy.
  2. =CUBEMEMBER("SalesCube", "[Product].[Bicycles]", "Bike Sales") - This formula would return the member 'Bicycles' from the 'Product' hierarchy, but it would be labeled as 'Bike Sales'.
  3. =CUBEMEMBER("SalesCube", "[Product].[Bicycles], [Time].[2019]") - This formula would return the sales of bicycles in 2019.

Common Errors and Solutions

While using the CUBEMEMBER function, you might encounter some common errors. Here are a few and their solutions:

  • #NAME? - This error occurs when Excel does not recognize the text in the formula. Ensure that the function name and arguments are spelled correctly.
  • #N/A - This error is displayed when the specified member or tuple does not exist in the cube. Check the member expression for any errors.
  • #VALUE! - This error occurs when the wrong type of argument or operand is used. Make sure that the connection is a string and the member expression identifies a member in the cube.

In conclusion, the CUBEMEMBER function is a powerful tool for retrieving specific data from an OLAP cube. With a proper understanding of its syntax and usage, you can leverage this function to create dynamic and interactive reports and dashboards in Excel.

Take Your Data Analysis Further with Causal

If you're impressed by the capabilities of the CUBEMEMBER function in Excel, you'll be excited to discover what Causal can do for your data analysis needs. As a modern alternative to traditional spreadsheets, Causal is specifically designed for number crunching and data visualization. Whether you're into modelling, forecasting, or scenario planning, Causal simplifies the process and enhances your ability to create interactive dashboards. Experience the ease of data visualization and the power of interactive presentations with Causal. Sign up today for free and elevate your data analysis game.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.