Solved

MDX with multiple dimensions potential named set

Posted on 2011-02-23
7
1,167 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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