MDX Query Returning Hierarchy is defined in axis already

SELECT  (UNION ({[KPI Metadata].[KPI Name].&[itemMatchedOffers]},
{[KPI Metadata].[KPI Name].&[TotalOffers]})         )  
ON COLUMNS,
 
non Empty
(


 [Collective].[Merchant Name].CHILDREN
 
 
 )
ON ROWS FROM [kpi]
where ([Collective].[Active].&[1],
[Source].[Source Name].&[DAProdExtract],
[Time].[Date].&[2010-04-15T00:00:00],
([Collective].[Merchant Name].[ Hiccups Childrens Boutique] ,
[Collective].[Merchant Name].[1 Stop Camera] ,
[Collective].[Merchant Name].[1 World Sarongs] ,
[Collective].[Merchant Name].[101Phones.com] ,
[Collective].[Merchant Name].[123Inkjets])
)
bksajiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
The question is why is this an error? The [Collective] dimension appears on both the ROWS axis and the slicer axis. I am not so good with MDX as to be able to give you a working version of the query off the top of my head and I only have a couple of minutes this morning, but I think the [Collective] portion of the WHERE needs to move to ROWS.

For the [Collective].[Merchant Name] members in WHERE I think they need to become a set that replaces [Collective].[Children] on ROWS. I think the [Collective].[Active].&[1] can be done with EXISTS({<[Collective].[Merchant Name] Set>}, [Collective].[Active].&[1]) -- that would become the ROWS set.
 
0
grzegorzsCommented:
You should move [Collective] members from slicer (WHERE) to rows axis.

best regards
0
Megan BrooksSQL Server ConsultantCommented:
If you move [Collective].[Active].&[1] to ROWS then it will become part of the output.

Actually, for this type of query that doesn't require higher-level summary totals you can simply move your filters to sub-selects. The multi-axis restriction doesn't apply when the duplications occur across different subcubes. I was rushing yesterday, and most queries that I have been writing lately can't use subcubes because of the summary comparison values that they must calculate, and I overlooked the obvious. The redesigned query would look something like the code snippet below. (Note: I have not tried to test this query; it might contain syntax errors)
I have simply taken each filter set and moved it to its own sub-select of the form
(SELECT (filter set) ON COLUMNS FROM inner sub-select)
I have not included a WHERE clause in the main query. It is usually not necessary when using sub-selects. It can be re-introduced, if needed, for dimensions that do not appear on COLUMNS or ROWS in the main query.

SELECT  (UNION ({[KPI Metadata].[KPI Name].&[itemMatchedOffers]},
{[KPI Metadata].[KPI Name].&[TotalOffers]})         )  
ON COLUMNS,
 
non Empty
(
 [Collective].[Merchant Name].CHILDREN
) ON ROWS FROM 
	(SELECT 
	   ([Collective].[Merchant Name].[ Hiccups Childrens Boutique] ,
		[Collective].[Merchant Name].[1 Stop Camera] ,
		[Collective].[Merchant Name].[1 World Sarongs] ,
		[Collective].[Merchant Name].[101Phones.com] ,
		[Collective].[Merchant Name].[123Inkjets]) ON COLUMNS FROM
		   (SELECT ([Collective].[Active].&[1]) ON COLUMNS FROM 
		      (SELECT ([Source].[Source Name].&[DAProdExtract]) ON COLUMNS FROM
		         (SELECT ([Time].[Date].&[2010-04-15T00:00:00]) ON COLUMNS FROM [kpi]
		         )
		      )
		   )
		)
     )

Open in new window

0
bksajiAuthor Commented:
I have said that Where Condition is Passed as a Parameter. So I used the Following Query and Passed the Whole Set Inside the STRTOSET as a Parameter and ended up with the Required Solution
SELECT  (UNION ({[KPI Metadata].[KPI Name].&[itemMatchedOffers]},
{[KPI Metadata].[KPI Name].&[TotalOffers]})         )  
ON COLUMNS,
 
non Empty
(


STRTOSET("{[Collective].[Merchant Name].[ Hiccups Childrens Boutique] ,
[Collective].[Merchant Name].[1 Stop Camera] ,
[Collective].[Merchant Name].[1 World Sarongs] ,
[Collective].[Merchant Name].[101Phones.com] ,
[Collective].[Merchant Name].[123Inkjets]}")
 
 
 )
ON ROWS FROM [kpi]
where ([Collective].[Active].&[1],
[Source].[Source Name].&[DAProdExtract],
[Time].[Date].&[2010-04-15T00:00:00]
)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.