MDX Functions (Dimension)
Dimension functions in MDX (Multidimensional Expressions) allow you to navigate and manipulate dimensions within your cube. These functions are crucial for selecting members, levels, and sets based on their relationships within dimension hierarchies.
Key Dimension Functions
DIMENSION()
Returns the specified dimension object.
DIMENSION( [Cube_Name.]Dimension_Name )
HIERARCHY()
Returns the specified hierarchy object.
HIERARCHY( [Cube_Name.]Dimension_Name.Hierarchy_Name )
LEVEL()
Returns the specified level object.
LEVEL( [Cube_Name.]Dimension_Name.Hierarchy_Name.Level_Name )
MEMBER()
Returns the specified member object. This is a fundamental function for referencing specific points within a dimension hierarchy.
MEMBER( [Cube_Name.]Dimension_Name.Hierarchy_Name.Level_Name.Member_Name )
Or a shortcut for member unique name:
[Dimension_Name].[Hierarchy_Name].[Member_Name]
CHILDREN()
Returns a set containing the children of a specified member.
CHILDREN(Member_Expression)
Example:
SELECT
{Children([Geography].[Country].&[USA])} ON COLUMNS
FROM [SalesCube]PARENT()
Returns the parent member of a specified member.
PARENT(Member_Expression)
Example:
SELECT
{Parent([Geography].[State].&[California])} ON COLUMNS
FROM [SalesCube]ANCESTOR()
Returns the ancestor of a specified member at a specified level.
ANCESTOR(Member_Expression, Level_Expression)
Example:
SELECT
{Ancestor([Geography].[City].&[Los Angeles], [Geography].[Country])} ON COLUMNS
FROM [SalesCube]Siblings()
Returns a set containing the siblings of a specified member, including the member itself.
SIBLINGS(Member_Expression)
Example:
SELECT
{Siblings([Product].[Category].&[Electronics])} ON ROWS
FROM [SalesCube]MEMBER_KEY()
Returns the unique key of a member.
MEMBER_KEY(Member_Expression)
MEMBER_NAME()
Returns the name of a member.
MEMBER_NAME(Member_Expression)
MEMBER_CAPTION()
Returns the caption of a member.
MEMBER_CAPTION(Member_Expression)
Navigating Hierarchies
Dimension functions are often used in conjunction with set functions to create complex selections. For example, you can get all direct children of a specific member, or find the top-level ancestor of any member.
FIRSTCHILD()
Returns the first child of a specified member.
FIRSTCHILD(Member_Expression)
LASTCHILD()
Returns the last child of a specified member.
LASTCHILD(Member_Expression)
FIRSTSIBLING()
Returns the first sibling of a specified member.
FIRSTSIBLING(Member_Expression)
LASTSIBLING()
Returns the last sibling of a specified member.
LASTSIBLING(Member_Expression)
Example Usage
Here's an example demonstrating how to retrieve the total sales for all direct subcategories of 'Bicycles' in the 'Product' dimension.
WITH MEMBER Measures.SubcategorySales AS
SUM(
CHILDREN([Product].[Category].&[Bikes]),
Measures.[Sales Amount]
)
SELECT
{Measures.SubcategorySales} ON COLUMNS
FROM [SalesCube]
WHERE [Date].[Fiscal Year].&[FY2023]