How to Use Cube Functions in Excel
Excel provides several cube functions to aggregate and analyze data stored in OLAP (Online Analytical Processing) cubes, which are multi-dimensional data structures used for data analysis. Cube functions allow Excel to access data from an Analysis Services data source without using a Pivot Table.
Sometimes, you cannot use PivotTables to submit reports directly, or you must fill in data in a company-specified format. In these cases, use cube functions.
Here are several cube functions:
CUBEVALUE
Returns an aggregated value based on a set of members or tuples.
Syntax: =CUBEVALUE(connection, member_expression1, member_expression2, …)
- connection is a text string of the name of the connection to the cube
- member_expression is a text string that evaluates to a member within the cube
Examples:
- =CUBEVALUE (“Sales”, “[Measures].[Profit]”, “[Time].[2022]”, “[All Product].[Beverages]”)
- =CUBEVALUE ($A$1, “[Member].[Profit]”, D212, $A23)
CUBEMEMBER
Returns a member or tuple.
Syntax: =CUBEMEMBER (connection, member_expression, )
- Connection is a text string of the name of the connection to the cube
- member_expression is a text string that evaluates to a member within the cube
- Caption is a text string that is displayed in the cell instead of the caption. If defined, it is displayed.
Example:
=CUBEMEMBER (“Sales”, “[months].[september]”)
CUBEKPIMEMBER
Retrieves a Key Performance Indicator (KPI) property.
Syntax: =CUBEKPIMEMBER (connection, kpi_name, kpi_property, )
- Connection is a text string of the name of the connection to the cube
- Kpi_name is a text string of the name of the kpi in the cube
- Kpi_property is the Kpi component returned
- “KPIValue” actual value
- “KPIGoal” target value
- “KPIStatus” status
- “KPITrend” trend value
- “KPIWeight” relative weight
- “KPICurrentTimeMember” temporary content
Examples:
- =CUBEKPIMEMBER (“Sales”,”MySalesKPI”, 1)
- =CUBEKPIMEMBER (“Sales”,”MySalesKPI”, KPIGoal,”Sales KPI Goal”)
CUBERANKEDMEMBER
Returns the nth, or ranked, member in a set.
CUBERANKEDMEMBER function syntax
Syntax: =CUBERANKEDMEMBER(connection, set_expression, rank, )
- Connection is a text string of the name of the connection to the cube
- Set_expression the text string of the set expression.
- Rank is an integer used to determine the highest value to return. If the value of this parameter is 1, the highest value is returned. If the value is 2, the second highest value is returned, and so on.
- Caption is optional. It is a string of text to display in the cube cells.
Examples:
- =CUBERANKEDMEMBER(“Sales”,$D$4,1, “Top Month”)
- =CUBERANKEDMEMBER(“Sales”,CUBESET(“Sales”, “Summer”, “[2022].[June]”, “[2022].[July]”, “[2022].August]”), 3, “Top Month”)
CUBESET
Defines a calculated set of members or tuples by sending a set expression to the cube on the server. This creates the set and returns it to Microsoft Excel.
Syntax: CUBESET(connection, set_expression, , [sort_order], [sort_by])
- Set_expression is A text string of a set expression that results in a set of members or tuples.
- Caption is a text string that is displayed in the cell instead of the caption. If one is defined, it is displayed.
- Sort_order is the type of sort, if any, to perform.
Examples:
- =CUBESET(“Finance”,”Order([Product].[Product].[Product Category].Members,[Measures].[UnitSales],ASC)”,”Products”)
- =CUBESET(“Sales”,”[Product].[All Products].Children”,”Products”,1,”[Measures].[Sales Amount]”)
- =CUBESET(“Sales”,”Filter([Product].[Level_1].[Level_1].Members, Right([Product].[Level_1].CurrentMember.Name, 2)=””_Wisconsin””)”)
- =CUBESET(“ThisWorkbookDataModel”,(A1,A2),”Product1 & Product2″)
The set expression must be written in the proper syntax, which may vary depending on the cube provider. You may need to consult the cube provider’s documentation for more information.
CUBESETCOUNT
Returns the number of items in a set.
Syntax: =CUBESETCOUNT(set)
Where Set is a text string of a Microsoft Excel expression that evaluates to a set defined by the CUBESET function.
Examples:
- =CUBESETCOUNT (A3)
- =CUBESETCOUNT (CUBESET (“Sales”,”[Product]. [All Products].Children”,”Products”, 1,”[Measures]. [Sales Amount]”))
Building set expressions within cube functions requires following the specific syntax defined by your OLAP cube provider. These syntax variations can sometimes be complex. For detailed guidance on proper set expression syntax, refer to the documentation provided by your cube provider.
It is possible to create formulas using nested cube functions. For example you can create something like:
=CUBEVALUE(“DataModel”,”MyMeasure”,”Date”,”MyFilter”,CUBESET(“DataModel”,”MyFilter2″))
CUBEMEMBERPROPERTY
Retrieves specific properties of members or tuples within a cube, providing you with additional information about your data.
Syntax: CUBEMEMBERPROPERTY(connection, member_expression, property_name)
- connection: This is a text string that specifies the name of the connection to the cube. It defines which OLAP cube you are working with.
- member_expression: This text string evaluates to a member within the cube. It represents the member or tuple for which you want to retrieve a specific property.
- property_name: Property name to retrieve (e.g., “NAME”, “UNIQUENAME”, “CAPTION”, “DISPLAYINFO”, “PARENT_UNIQUE_NAME”, “LEVEL_UNIQUE_NAME”, “LEVEL_NUMBER” or “LNUM”).
Example:
=CUBEMEMBERPROPERTY(“Sales”, “[Product].[All Products]”, “CAPTION”)
Upon pressing Enter, the cell would display the caption of the specified member, which might be something like “All Products”.
These functions retrieves and analyze data stored in an OLAP cube, providing you with more powerful and flexible tools for data analysis. To use these functions, you must have an OLAP cube connected to your workbook, and you must have the necessary permissions to access the cube data.
Leave a Reply