[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MDX with multiple dimensions potential named set

Posted on 2011-02-23
7
Medium Priority
?
1,249 Views
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?
0
Comment
Question by:adammet04
[X]
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
7 Comments
 
LVL 10

Expert Comment

by:itcouple
ID: 34967775
Hi

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).

Regards
Emil
0
 
LVL 10

Accepted Solution

by:
itcouple earned 2000 total points
ID: 34969028
Hi

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 (
[Dim1].[CodeGroup].[CodeGroup],
	Except([Dim2].[ProductCode].[ProductCode],[Dim2].[ProductCode].&[ProductCode221])
)

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
with
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.

Regards
Emil
0
 
LVL 3

Author Comment

by:adammet04
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.

0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 3

Author Comment

by:adammet04
ID: 34992910
apologies it is a set.. my bad.
0
 
LVL 3

Author Comment

by:adammet04
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!
0
 
LVL 3

Author Comment

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

Thankyou for your help!
0
 
LVL 3

Author Closing Comment

by:adammet04
ID: 34994260
Thankyou!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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