MDX query returning duplicate rows in SSAS tabular mode cube

Question

I'm completely new to SSAS cubes and terminologies (members, hierarchies, e.t.c) and MDX queries but i have started my journey to learn this stuff, so apologies if my question is very clear.

     SELECT NON EMPTY { } ON COLUMNS, { 
     [Suggestions].[Parent_id].[Parent_id] *--.ALLMEMBERS * 
     [Suggestions].[id].[id] * --.ALLMEMBERS * 
     [Suggestions].[Sugg - #].[Sugg - #] *-- .ALLMEMBERS * 
     [Suggestions].[Sugg - Assigned].[Sugg - Assigned] *  --.ALLMEMBERS * 
     [Suggestions].[Sugg - Assigned to].[Sugg - Assigned to]* --.ALLMEMBERS * 
     [Suggestions].[Sugg - Status].[Sugg - Status] *--.ALLMEMBERS   
    
     --[Parent_Details].[Unit_Name].[Unit_Name]  --.ALLMEMBERS
     } 
     DIMENSION PROPERTIES MEMBER_CAPTION, 
     MEMBER_UNIQUE_NAME ON ROWS 
     FROM ( SELECT ( { [Suggestions].[Sugg - Assigned to].&[UNIT] } ) ON COLUMNS 
     FROM ( SELECT ( STRTOSET('SG123', CONSTRAINED) ) ON COLUMNS 
     FROM ( SELECT ( { [Suggestions].[Sugg - Status].&[Pending Inputt] } ) ON COLUMNS 
     FROM [BOI_Tracker-Stats]))) 
     CELL PROPERTIES VALUE

I have the above MDX query that executing. I generated the query from the MDX query designer tool in SSMS and have only simple modifications by hand.

In the query, if i comment out the line [Parent_Details].[Unit_Name].[Unit_Name] --.ALLMEMBERS, i get the correct number of rows.

Main Question.

If i un-comment it so that i return the Unit_Name column, my rows are duplicated. The original 100 correct rows now because thousands of rows with duplicate values all over. Any body know what i should look out for that is causing this. Looks a wrong join is being applied.

Other things i would like to understand.

1. The query designer generated the query in the format

[Suggestions].[Parent_id].[Parent_id].ALLMEMBERS * . If i comment out .ALLMEMBERS * such that the query is just [Suggestions].[Parent_id].[Parent_id] with out .ALLMEMBERS * the results are the same. So what is the use of .ALLMEMBERS *

2. I also notice that the column i want to select is repeated twice like

Suggestions].[Parent_id].[Parent_id], why is this so?, why can't it just be generated as Suggestions].[Parent_id]

score 0 · Answer 1 · answered May 25 '18 at 08:23

If you select from different dimensions like that you basically multiply the results. If you think about it that's the correct behavior. In your case you have [Suggestions] and [Parent_Details] . These are different dimensions. In your query you want results having both so it does:

For each member of [Suggestion] get all members of [Parent Details] and add them to the result. So the result set becomes:

[Suggestion-1][Parent_Details-1][Measures...]
[Suggestion-1][Parent_Details-2][Measures...]
[Suggestion-2][Parent_Details-1][Measures...]
[Suggestion-2][Parent_Details-2][Measures...]
[Suggestion-3][Parent_Details-1][Measures...]
etc.

( having different levels from the [Suggestions] dimension doesn't multiply the measures )

This is a correct behavior when you think about it because if you add these two dimensions you probably want to know something like "What are the measures for that suggestion and for these parent details?" And that exact row will be correct in the result set. It all depends on what result do you want to get (What do you ask for).

The multiplication of the names depends on your cube design. First row is a level and second a member. If you create a hierarchy for example it will not look like that.

MDX query returning duplicate rows in SSAS tabular mode cube

1 Answers1