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])
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]
Second tuple
select
[Measures].[Internet Sales Amount]
on columns,
([Date].[Calendar Year].&[2012],[Customer].[Country].&[United States])
on
rows
from [Adventure Works]
Third tuple
select
[Measures].[Internet Sales Amount]
on columns,
{
([Customer].[Gender].&[F],[Product].[Category].&[3])
}
on
rows
from [Adventure Works]
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]