How to Use the CUBEMEMBER Function in Excel
Today, we will explore the CUBEMEMBER function available in Excel and Google Sheets. This function plays a crucial role in fetching a specific member or a tuple from a cube in an Online Analytical Processing (OLAP) database.
Overview
The CUBEMEMBER function is structured as follows:
CUBEMEMBER(connection, member_expression) - connection: A string that denotes the name of the OLAP cube from which data is to be retrieved.
- member_expression: A string specifying the distinctive name, key, or caption that identifies the member within the cube.
Example Tasks
Below are several practical scenarios illustrating how the CUBEMEMBER function can be effectively employed:
Task 1: Retrieve a Specific Member
In this example, we aim to extract the sales amount for the member ‘Beverly Hills’ from an OLAP cube named ‘SalesData’.
| Connection | Member Expression | Result |
|---|---|---|
| ‘SalesData’ | ‘[Store].[City].[Beverly Hills]’ | =$CUBEMEMBER(“SalesData”, “[Store].[City].[Beverly Hills]”) |
Task 2: Retrieve a Tuple
This example demonstrates retrieving sales data for the combination of the member ‘Beverly Hills’ and the month ‘January’ from the ‘SalesData’ OLAP cube.
| Connection | Member Expression | Result |
|---|---|---|
| ‘SalesData’ | ‘([Store].[City].[Beverly Hills], [Time].[Month].[January])’ | =$CUBEMEMBER(“SalesData”, “([Store].[City].[Beverly Hills], [Time].[Month].[January])”) |
Task 3: Retrieve All Members of a Hierarchy
Here, we look to fetch all members of the ‘Product Category’ hierarchy within the ‘SalesData’ OLAP cube.
| Connection | Member Expression | Result |
|---|---|---|
| ‘SalesData’ | ‘[Product].[Category].[All]’ | =$CUBEMEMBER(“SalesData”, “[Product].[Category].[All]”) |
The examples provided showcase the versatility of the CUBEMEMBER function in Excel and Google Sheets for accessing precise members or tuple combinations from OLAP cubes. It’s important to tailor the connection and member expressions to align with the specific requirements of your data model and cube architecture.
More information: https://support.microsoft.com/en-us/office/cubemember-function-0f6a15b9-2c18-4819-ae89-e1b5c8b398ad