Solved

MDX with multiple dimensions potential named set

Posted on 2011-02-23
7
1,156 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
  • 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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now