CUBEMEMBERPROPERTY function

The CUBEMEMBERPROPERTY function in Excel is used to retrieve the value of a specified property for a member from an OLAP cube or a data model, such as SQL Server Analysis Services (SSAS). Member properties are attributes of members in a dimension, such as a product’s color, category, or any other custom property defined in the cube.

Syntax

CUBEMEMBERPROPERTY(connection, member_expression, property)

Parameters

• connection: The name of the connection to the cube or data model, usually a text string that represents a connection in your workbook.
• member_expression: A string that defines a member in the cube, typically referencing a member from a dimension or hierarchy.
• property: A string representing the name of the property you want to retrieve for the member. This could be a standard property (like "KEY", "NAME", "CAPTION") or a custom property defined in the cube.

Usage Example

Assume you have a cube connection named “SalesData” and you want to retrieve a member property such as the Color or Category of a product in the Product dimension.

1. To retrieve the color of a specific product:

   =CUBEMEMBERPROPERTY("SalesData", "[Product].[Product Name].&[101]", "Color")
   

2. To retrieve the category of a specific product:

   =CUBEMEMBERPROPERTY("SalesData", "[Product].[Product Name].&[102]", "Category")
   

3. To retrieve the key of a specific year:

   =CUBEMEMBERPROPERTY("SalesData", "[Date].[Year].&[2023]", "KEY")
   

4. To retrieve the caption (name) of a specific region:

   =CUBEMEMBERPROPERTY("SalesData", "[Geography].[Region].&[East]", "CAPTION")
   

Key Uses

1. Retrieve Member Attributes: The CUBEMEMBERPROPERTY function is ideal for pulling additional attributes of members that go beyond just the member name or key. For example, you might want to display the color, size, or category of a product next to its name in a custom report.
2. Custom Reporting: This function can be used to build custom reports where additional descriptive information about members is needed, such as including detailed information about geographical regions, products, or time periods in your report.
3. Combining with Other Cube Functions: Like other cube functions, CUBEMEMBERPROPERTY is often used in conjunction with CUBEMEMBER or CUBEVALUE to create detailed reports that combine member information and data from an OLAP cube.

Common Properties

Here are some common standard properties you might use in the CUBEMEMBERPROPERTY function:

• KEY: Returns the unique key of the member.
• NAME: Returns the name of the member.
• CAPTION: Returns the display caption of the member.
• UNIQUE_NAME: Returns the unique name of the member in the cube.

You can also use custom properties that are specific to the cube you’re working with, such as Color, Size, Region, or any other attributes defined by the cube.

Common Errors

• #NAME?: This error occurs if the connection name is incorrect or if Excel doesn’t support cube functions in your version.
• #N/A: This error may appear if the member expression is invalid or the specified property does not exist for the member in the cube.

Tips for Using CUBEMEMBERPROPERTY

• Ensure Correct Property Names: Always make sure that the property name you provide is exactly as defined in the cube. If you’re not sure of the available properties for a member, you can explore the cube through your OLAP tool or data model documentation.
• Use with CUBEMEMBER: Often, you will want to retrieve both the member and its property. You can use CUBEMEMBER to display the member and CUBEMEMBERPROPERTY to display additional properties of that member in adjacent cells.
• Custom Properties: If the cube contains custom properties, you can extract and display them alongside the cube data, giving you more detailed insights into the members.

Example Scenario

Let’s say you’re working with a product catalog stored in a cube and each product has attributes like Color and Size. You want to create a report in Excel that displays the product name, its color, and its size.

You could use:

1. CUBEMEMBER to retrieve the product name.
2. CUBEMEMBERPROPERTY to retrieve the Color and Size properties for each product.

=CUBEMEMBER("SalesData", "[Product].[Product Name].&[101]")         ' To retrieve the product name
=CUBEMEMBERPROPERTY("SalesData", "[Product].[Product Name].&[101]", "Color")  ' To retrieve the product's color
=CUBEMEMBERPROPERTY("SalesData", "[Product].[Product Name].&[101]", "Size")   ' To retrieve the product's size

Summary

The CUBEMEMBERPROPERTY function is a powerful tool in Excel for retrieving detailed information about members in an OLAP cube or data model. It helps you pull attributes like color, size, or category, as well as standard properties such as the member’s key or name, allowing for rich, customized reporting.