Search code examples
ssasmdxcube

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])

Solution

  • 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