# Named Sets {% hint style="warning" %} #### 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 {% endhint %} ```xml TopCount([CUSTOMERS.Customers].[Territory].Members,3,[Measures].[Sales]) ``` {% hint style="info" %} 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 {% endhint %} #### 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: {% tabs %} {% tab title="1. Test NS in MDX Query" %} {% hint style="info" %} #### MDX Query {% endhint %} 1. Open the Miniature Models - original.xml schema. 2. To access MDX Query mode, from the menu select File > New > MDX Query.

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

``` 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] ``` 4. Click Execute.

*** **WITH SET** Clause (Named Set Definition) ```mdx WITH SET [Top 3 Territories] AS ``` This creates a **temporary named set** called `[Top 3 Territories]` that exists only for this query. *** **TopCount** Function ```mdx 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. {% endtab %} {% tab title="2. Add NS to Schema" %} {% hint style="info" %} #### Add NS to Schema {% endhint %} 1. Open the Miniature Models - original.xml schema 2. Right-mouse click on the Sales\_FY2003\_2005 cube & select: Add Named Set.

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

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

4. Select Formula & copy / paste Formula.

5. Click Save and Publish. x x {% endtab %} {% endtabs %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://academy.pentaho.com/schema-workbench/schema-workbench/mdx-query/named-sets.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.