What is a SSAS named set

As per msdn, a named set is a Multidimensional Expressions (MDX) expression that returns a set of dimension members.
We can define named sets and save them as part of the cube definition;
we can also create named sets in client applications.
We create named sets by combining cube data, arithmetic operators, numbers, and functions.
Named sets can be used by users in MDX queries in client applications and can also be used to define sets in subcubes. A subcube is a collection of crossjoined sets that restricts the cube space to the defined subspace for subsequent statements. Defining a restricted cube space is a fundamental concept to MDX scripting.
Named sets simplify MDX queries and provide useful aliases for complex, typically used, set expressions.
For example:
(1) We can define a named set called Large Resellers that contains the set of members in the Reseller dimension that have the most employees.
End users could then use the Large Resellers named set in queries, or we could use the named set to define a set in a subcube.
(2) We can create a named set called Top Ten Factories that contains the ten members of the Factories dimension that have the highest values for the Production measure.
Top Ten Factories can then be used in queries by end users.
For example, an end user can place Top Ten Factories on one axis and the Measures dimension, including Production, on another axis.

Named set definitions are stored in cubes, but their values exist only in memory.

To create a named set, use the New Named Set command on the Calculations tab of Cube Designer.

Lets look at an advance example of creating a named set on dimension Continent without Australia.

But we also need all option without Australia.
First create calculation:
CREATE MEMBER CURRENTCUBE.[DimContinent].[Source Key].[All Continents wo AUSTRALIA]
AS Aggregate
(
Except
( [DimContinent].[Source Key].[Source Key].members ,
{[DimContinent].[Source Key].[Source Key].&[AUSTRALIA]} )
),
VISIBLE = 1 ;
Now using the above created calculation we will create dynamic set:
CREATE DYNAMIC SET CURRENTCUBE.[Continents wo AUSTRALIA]
AS {
Except
( [DimContinent].[Source Key].[Source Key].members ,
{[DimContinent].[Source Key].[Source Key].&[AUSTRALIA]}
),[DimContinent].[Source Key].[All Continents wo AUSTRALIA]
} ;

Advertisements

One thought on “What is a SSAS named set

  1. Pingback: Named set on multiple dimension | Vikas D More

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s