THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Scope of Named Set in MDX

I recently noted a behavior of named sets (specifically, the resolution order) that can be not so intuitive.

When you define a named set on the cube, it has the same behavior of a session-scoped named set (the one you create with CREATE SESSION SET statement into a MDX session).

When you define a query-scoped named set, apparently you use the same expression, but you obtain a different behavior.

A session-scoped named sets is resolved before the SELECT statement. A possible WHERE clause doesn't have effects on the SET expression. If you want to define the top 10 customers, a session-scoped named set gives you an absolute classification.

A query-scoped named set is solved inside the SELECT statement. If a WHERE clause is present, it restricts the context of the named set evaluation. In other words, the named set is resolved after the WHERE clause. If you use the same top 10 customers expression you could have used for a session-scoped named set, it would have a relative meaning in this form (i.e. defining the top 10 customers only for the products you filtered in the WHERE clause).

I would like to thank Octoni Simbolon that pointed me to this issue.

Published Sunday, December 24, 2006 6:07 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement