Named Sets

Grouping members ..

Workshop - Named Sets

While calculated members enable you to define new metrics by combining or transforming measures, business users often need to work with specific, reusable collections of dimensional members - such as top-performing territories, strategic customer segments, or key product categories. Creating these groupings ad-hoc in individual reports is inefficient and inconsistent. Named Sets in Mondrian schemas solve this problem by defining reusable collections of dimension members using MDX expressions, creating dynamic "bookmarks" that automatically update based on current data and remain consistently available across all reports and analyses.

In this hands-on workshop, you'll learn to create and deploy Named Sets that identify and group important subsets of your dimensional data. You'll start by testing Named Set logic using the WITH SET clause in MDX queries, validating that your TopCount formula correctly identifies the three highest-performing territories by sales. Then you'll permanently embed this Named Set into your Miniature Models schema, making it available as a reusable filter that report builders can leverage without understanding the underlying MDX complexity or needing to recreate the ranking logic.

What You'll Accomplish:

• Understand the difference between Named Sets (collections of members) and Calculated Members (single values)
• Use MDX Query mode to test Named Set formulas before embedding them in schemas
• Write WITH SET clauses to create temporary named sets for query testing
• Apply the TopCount function to rank and filter dimensional members by measure values
• Add Named Sets directly to Mondrian cube definitions using Schema Workbench
• Configure Named Set properties including name and formula specifications
• Publish schemas containing Named Sets to Pentaho BA Server
• Understand when Named Sets provide better solutions than report-level filters
• Create dynamic collections that automatically update as data changes

By the end of this workshop, you'll understand how Named Sets centralize common groupings and filtering logic within your semantic layer, ensuring that concepts like "Top 3 Territories," "Strategic Accounts," or "Key Product Lines" are defined consistently and available universally. Rather than requiring each report developer to write complex MDX or risk inconsistent implementations of the same business concept, you'll embed these definitions once in your schema where they're validated, maintained centrally, and automatically reflect current data conditions. This transforms your OLAP cubes from static structures into intelligent analytical platforms that encode business knowledge about what dimensional subsets matter most to your organization.

Prerequisites: Completion of Miniature Models and MDX Query workshops; Schema Workbench and Pentaho Server installed and configured; Understanding of MDX syntax and TopCount function; Familiarity with dimension hierarchies and member navigation

Estimated Time: 30 minutes

<!-- ============================================ -->
        <!-- ADD YOUR NAMED SET HERE - AFTER MEASURES     -->
        <!-- ============================================ -->
        <NamedSet name="Top 3 Territories">
            <Formula>TopCount([CUSTOMERS.Customers].[Territory].Members,3,[Measures].[Sales])</Formula>
        </NamedSet>
        
        <!-- If you have Calculated Members, they go after Named Sets -->
        
    </Cube>
</Schema>

Named Sets are particularly useful for:

• Top/Bottom N analysis: Top 10 Products, Bottom 3 Territories
• Strategic groupings: Key Accounts, Focus Markets, Priority Products
• Complex member selections: Members meeting multiple criteria
• Report simplification: Reducing complexity in frequently used queries
• Consistency: Ensuring the same business logic is applied across all reports

Named Sets vs. Calculated Members

Understanding the difference between Named Sets and Calculated Members is crucial:

Aspect	Named Sets	Calculated Members
Returns	Set of members	Single member/value
Purpose	Group members together	Calculate new values
Definition	WITH SET clause	WITH MEMBER clause
Example	Top 3 Territories by Sales	Profit (Sales - Cost)

Follow the guide below to add a Named Set to the Miniature Models Schema:

1. Test NS in MDX Query

MDX Query

1. Open the Miniature Models - original.xml schema.
2. To access MDX Query mode, from the menu select File > New > MDX Query.

MDX Query

1. Click Ok to Connect & Enter the following Query into the top pane:

Copy & paste formula

WITH SET [Top 3 Territories] AS
  TopCount(
    [CUSTOMERS.Customers].[Territory].Members,
    3,
    [Measures].[Sales]
  )

SELECT
  {[Measures].[Sales], [Measures].[Quantity Ordered]} ON COLUMNS,
  [Top 3 Territories] ON ROWS
FROM [Sales_FY2003_2005]

1. Click Execute.

Named Set MDX Query - Top 3 Territories

---

WITH SET Clause (Named Set Definition)

WITH SET [Top 3 Territories] AS

This creates a temporary named set called [Top 3 Territories] that exists only for this query.

---

TopCount Function

TopCount(
[CUSTOMERS.Customers].[Territory].Members,
3,
[Measures].[Sales]
)

Breaks down as:

Component	Explanation
TopCount()	MDX function that returns the top N items from a set
[CUSTOMERS.Customers].[Territory].Members	Gets ALL territory members from the Customers hierarchy
3	Returns the TOP 3 items
[Measures].[Sales]	Ranks territories by Sales (highest to lowest)

What it does: Finds all territories, sorts them by Sales amount (descending), and returns the top 3.

2. Add NS to Schema

Add NS to Schema

1. Open the Miniature Models - original.xml schema
2. Right-mouse click on the Sales_FY2003_2005 cube & select: Add Named Set.

Add Named Set - Top 3 Territories

1. Expand the Named Set & Enter the following details:

Attribute	Value
name	Top 3 Territories
formula	TopCount( [CUSTOMERS.Customers].[Territory].Members, 3, [Measures].[Sales] )

Named Set - Top 3 Territories

1. Select Formula & copy / paste Formula.

Enter Formula

1. Click Save and Publish.

x

x