The CUBEVALUE function in Excel is used to fetch aggregated data from an OLAP (Online Analytical Processing) cube. It’s especially useful when working with data in a model built using Power Pivot or when connected to a SQL Server Analysis Services (SSAS) cube. Here’s how to use the CUBEVALUE function:
Syntax
CUBEVALUE(connection, [member_expression1], [member_expression2], ...)
- connection: A text string of the name of the connection to the data model or OLAP cube.
- member_expression1, member_expression2, …: Optional. A set of member expressions or tuples that represent the coordinates of the data in the cube. It could be a single member, a set, a tuple, or a defined name.
Steps to Use CUBEVALUE
- Establish Connection: Ensure that your Excel workbook is connected to an OLAP cube or data model. This is often done via the Data tab in Excel using the “Get Data” feature.
- Identify the Connection String: This is usually the name of the connection established in the Excel workbook. It appears in the Connection Manager under “Existing Connections.”
- Select Members or Tuples: Decide which members or dimensions from your cube you want to aggregate. These are often hierarchy names from the source like time periods or specific product categories.
- Formula Setup: Use the CUBEVALUE function in a cell:
Example 1: Sum of sales for a specific product category in a specific year:
=CUBEVALUE("YourConnection", "[Measures].[Sales Amount]", "[Product].[Category].&[Bikes]", "[Date].[Calendar Year].&[2023]")
Example 2: Total sales without specific dimensions:
=CUBEVALUE("YourConnection", "[Measures].[Total Sales]")
- Resolve Errors:
- Ensure all members are valid and exist in the cube.
- Check if connection strings are correct.
- If you get a `#N/A`, it usually means one of the expressions can’t be found or there’s an issue with the connection.
Tips
- Use the CUBE functions like CUBEMEMBER and CUBEMEMBERPROPERTY to validate your members and retrieve specific properties.
- Names of dimensions, hierarchies, and measures are usually case-sensitive.
- Play with the Data Model settings and check for hierarchies that are built into the model for better filtering.
By following these guidelines, you can effectively utilize the CUBEVALUE function to perform powerful data analysis in Excel using OLAP cubes.
Unlock Your Potential
Excel
Access
Power BI
