MDX with multiple dimensions potential named set

Posted on 2011-02-23
Medium Priority
Last Modified: 2016-02-15
Hi all,

i want to create a named set where i have a set of dimensions which i want to include but a set of dimensions i want to exclude at the same time when applied to a measure.

So for example, If i had codes 1-10 as one dimension (CodeGroup) , and product codes 1-1000 for another dimension (ProductCode)
And we are dealing with sales dollars:

I want to be able to create a named set, which includes CodeGroup 1,
                                                                                         CodeGroup 2 but not ProductCode 221

how could i do this in a named set. and how would i code to exlude a product code for a given codegroup (as per above)

I think i have to use exists, and i'm guessing something like a join that i need to make to make the named set work.
Is there something like not exists?
Question by:adammet04
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 10

Expert Comment

ID: 34967775

I will try to send you an example in several hours time.... When you create a set you can list the dimension members you want and exclude the ones you don't want you combine different dimensionality members using crossjoin (*) you don't use join because relationships between them are defined in 'usage tab' in SSAS cube designer (if you use SSAS).

LVL 10

Accepted Solution

itcouple earned 2000 total points
ID: 34969028

I had quick go with that (I'm not an expert in that) and so far I able to create a set which excludes the values you want.

See below
with set test as (

SELECT  {[Measures].[Value]} on 0,
test on 1
FROM    [CubeName]

Open in new window

This will show the entire set so in select you can replace
test on 1
extract(test,[Dim1].[CodeGroup]) on 1

I'm not sure what measures will return (valeus) and how it will exactly behave but I hope that will bring it at least a step closer to what you are trying to achieve.


Author Comment

ID: 34992868
Thanks for this I will test. Ill also post what i ended up with as well, but i think the except keyword might be what i want. However this is a straight MDX query rather than generating a named set. Maybe that is all that is needed. I will see how i go.

Sorry for tardiness of reply.

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


Author Comment

ID: 34992910
apologies it is a set.. my bad.

Author Comment

ID: 34993554
Unfortunatly the code fragment did not work.

this is the message it returns.
"Two sets specified in the  function have different dimensionality."

I think i need to use cross join with except, but having trouble putting it together.

some more help please!

Author Comment

ID: 34994258
Actually i fixed it. I had multiple tuples, but didn't enclose them properly.

Thankyou for your help!

Author Closing Comment

ID: 34994260

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question