Sudi
Sudi

Reputation: 23

Mdx query for cube is returning different results for "With Member" if member identifier name is different

I am firing mdxquery to fetch different tuple set combination for different cells using "With Member". My requirement is to query tuples with multiple dimensionality in a single mdx query. As per my understanding member identifier is just an alias and it should not impact result of the query. But its returning different result set for the same query if i use separate member identifiers. The slicer where filter is also same. My requirement is that I have a set of tuples and each tuple has different members Dimensions/attributes combination and I want to fire only one query to get data for each of these tuple sets. So I am using "With Member" option which allows having named member sets having different dimensionality.

Query 1 , Query 2, Query 3 is almost same only the member name used is different for all of them. Any help or suggestions is highly appreciated.

Query 1: 
With 
MEMBER [Account].[9] AS '([Account].&[500],[Department].&[102])' 
MEMBER [Account].[1] AS '([Account_Text].[Account_Text_Level].&[-1],[Account].&[161])' 
MEMBER [Account].[6] AS '([Account].&[500],[Account_Group].&[214])' 
MEMBER [Account].[10] AS '([Account].&[1])' 
MEMBER [Account].[13] AS '([Account].&[2881],[Company].&[173])' 
MEMBER [Account].[4] AS '([Company].&[1],[Company_Text].&[-1],[Account].&[500],[Account_Group].&[215],[Account_Text].[Account_Text_Level].&[-1],[Department].&[103])' 
Select 
{[Account].[9],[Account].[1],[Account].[6],[Account].[10],[Account].[13],[Account].[4]} 
Dimension Properties [Parent_Unique_Name] On Columns From [Adventure Works] 
Where ([BusinessCenter].&[1],[CalculatedDim].&[1],[CostCenter].&[1],[Department].&[1],[Geography].&[1],[ICSegment].&[1],
[ProductLine].&[1],[Reporting].[Level 1].&[14],[Company].&[1],[Scenario].&[1],[Time].&[2014])


Query 2:
With 
MEMBER [Test9] AS '([Account].&[500],[Department].&[102])' 
MEMBER [Test1] AS '([Account_Text].[Account_Text_Level].&[-1],[Account].&[161])' 
MEMBER [Test2] AS '([Account].&[500],[Account_Group].&[214])' 
MEMBER [Test10] AS '([Account].&[1])' 
MEMBER [Test13] AS '([Account].&[2881],[Company].&[173])' 
MEMBER [Test4] AS '([Company].&[1],[Company_Text].&[-1],[Account].&[500],[Account_Group].&[215],[Account_Text].[Account_Text_Level].&[-1],[Department].&[103])' 
Select 
{[Test9],[Test1],[Test2],[Test10],[Test13],[Test4]} 
Dimension Properties [Parent_Unique_Name] 
On Columns From  [Adventure Works]  
Where ([BusinessCenter].&[1],[CalculatedDim].&[1],[CostCenter].&[1],[Department].&[1],[Geography].&[1],
[ICSegment].&[1],
[ProductLine].&[1],[Reporting].[Level 1].&[14],[Company].&[1],[Scenario].&[1],[Time].&[2014]
)

Query 3:

With 
MEMBER [Company].[9] AS '([Account].&[500],[Department].&[102])' 
MEMBER [Company].[1] AS '([Account_Text].[Account_Text_Level].&[-1],[Account].&[161])' 
MEMBER [Company].[6] AS '([Account].&[500],[Account_Group].&[214])' 
MEMBER [Company].[10] AS '([Account].&[1])' 
MEMBER [Company].[13] AS '([Account].&[2881],[Company].&[173])' 
MEMBER [Company].[4] AS '([Company].&[1],[Company_Text].&[-1],[Account].&[500],[Account_Group].&[215],[Account_Text].[Account_Text_Level].&[-1],[Department].&[103])' 
Select 
{[Company].[9],[Company].[1], [Company].[6],[Company].[10],[Company].[13],[Company].[4]} 
Dimension Properties [Parent_Unique_Name] On Columns From  [Adventure Works]  
Where ([BusinessCenter].&[1],[CalculatedDim].&[1],[CostCenter].&[1],[Department].&[1],[Geography].&[1],[ICSegment].&[1],
[ProductLine].&[1],[Reporting].[Level 1].&[14],[Scenario].&[1],[Time].&[2014],[Account].&[1])

Upvotes: 0

Views: 260

Answers (1)

MoazRub
MoazRub

Reputation: 2911

My answer is based on your comment.

I have a set of tuples and each tuple has different members and I want to fire only one query to get data for each of these tuple sets. So I am using "With Member" option which allows having named member sets having different dimensionality. – Sudi

You problem deals with the concept of dimensionality and hierarchility. Hierarchility says that all tuples in a set should contain the same dimension-hierarchies, Dimensionality says they must be in the same order. So if a tuple is missing a member you use its default member in the query, to satisfy the property. This will not effect the result as this is what the query processor does for each dimesnion that you leave out in your tuple. Your query will be like.

Select 
{
([Account].&[500], [Department].&[102],                       [Account_Text].[Account_Text_Level].defaultmember, [Account_Group].[YourHierarchy].defaultmember, [Company].[YourHierarchy].defaultmember,[Company_Text].[YourHierarchy].defaultmember),
([Account].&[161], [Department].[Youhierarchy].defaultmember, [Account_Text].[Account_Text_Level].&[-1],         [Account_Group].[YourHierarchy].defaultmember, [Company].[YourHierarchy].defaultmember,[Company_Text].[YourHierarchy].defaultmember),
([Account].&[500], [Department].[Youhierarchy].defaultmember, [Account_Text].[Account_Text_Level].defaultmember, [Account_Group].&[214]                       , [Company].[YourHierarchy].defaultmember,[Company_Text].[YourHierarchy].defaultmember),
([Account].&[1],   [Department].[Youhierarchy].defaultmember, [Account_Text].[Account_Text_Level].defaultmember, [Account_Group].[YourHierarchy].defaultmember, [Company].[YourHierarchy].defaultmember,[Company_Text].[YourHierarchy].defaultmember),
([Account].&[2881],[Department].[Youhierarchy].defaultmember, [Account_Text].[Account_Text_Level].defaultmember, [Account_Group].[YourHierarchy].defaultmember, [Company].&[173]),
([Account].&[500], [Department].&[103],                       [Account_Text].[Account_Text_Level].&[-1],         [Account_Group].&[215]                       ,[Company].&[1]                          ,[Company_Text].&[-1])
} 
Dimension Properties [Parent_Unique_Name] On Columns 
From [Adventure Works] 
Where 
([BusinessCenter].&[1],[CalculatedDim].&[1],
[CostCenter].&[1],[Department].&[1],
[Geography].&[1],[ICSegment].&[1],
[ProductLine].&[1],[Reporting].[Level 1].&[14],
[Company].&[1],[Scenario].&[1],[Time].&[2014])

Edit: based on comment below

Take a look at the

select 
[Measures].[Internet Sales Amount]
on columns,
([Date].[Calendar Year].&[2012],[Product].[Category].&[1])
on 
rows 
from [Adventure Works]

Result enter image description here

Second tuple

select 
[Measures].[Internet Sales Amount]
on columns,
([Date].[Calendar Year].&[2012],[Customer].[Country].&[United States])
on 
rows 
from [Adventure Works]

Result: enter image description here

Third tuple

select 
[Measures].[Internet Sales Amount]
on columns,
{
([Customer].[Gender].&[F],[Product].[Category].&[3])
}
on 
rows 
from [Adventure Works]

Result enter image description here

Now lets combine them

select 
[Measures].[Internet Sales Amount]
on columns,
{
([Date].[Calendar Year].&[2012],[Product].[Category].&[1],[Customer].[Country].defaultmember,[Customer].[Gender].defaultmember),
([Date].[Calendar Year].&[2012],[Product].[Category].defaultmember,[Customer].[Country].&[United States],[Customer].[Gender].defaultmember),
([Date].[Calendar Year].defaultmember,[Product].[Category].&[3],[Customer].[Country].defaultmember,[Customer].[Gender].&[F])
}
on 
rows 
from [Adventure Works]

Result enter image description here

Upvotes: 2

Related Questions