none
MDX Is Making Me Get Gray Hairs RRS feed

  • Question

  • I consider myself to be extremely proficient in SQL and thought it wouldn't be too difficult to pick up MDX...apparently I was wrong :(

    Below is a query that I've been working on, with lots of help fro this community :).  

    With	member measures.zero as filter([Student].[Person ID],[Measures].[At Standard] = 0).count
    		member measures.one as ([Student].[Person ID], [Measures].[At Standard])
    Select 
    	Non Empty measures.zero on columns,
    	Non Empty [Teacher].[Teacher Name].children on rows
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Proficiency Assessment Standards].[Diagnostic].&[False],
    		 [Location].[Location].&[Doss High]
    		)
    		
    

    If I use the calculated member: measures.one in the columns axis, it returns the 4 teachers that teach Algebra I at this school, with a count of the number of students at Standard.

    If I use the calculated member: measures.zero in the columns axis, it returns a list of every single teacher in the district, with a count of 1 for each teacher.

    What I'm wanting is the four teachers from the "measures.one" member, and a count of the number of students not at standard, which is what I thought the filter function in the "measures.zero" member would give me.

    Trying to think about this logically...I'm guessing the filter function is producing a numeric value as the output, so the association with the record fact table is getting lost?  If that's the case, what can I do about it?

    Also, I should mention my "end goal" here is to create Calculated Members that I can insert into my cube (created using Visual Studio/Sql Server 2008).

    Thursday, December 29, 2011 3:46 PM

Answers

  • Well I managed to play around until I found the answer -- yay me!  I swear I had already tried this, but I guess I didn't have it perfect.  My final solution looks like this:

     

    With 
    	member [0/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 0).count
    	member [1/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 1).count
    	member [2/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 2).count
    	member [3/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 3).count
    Select 
    	Non Empty 
    	{[0/3], [1/3], [2/3], [3/3]}
    		on columns,
    	Non Empty [Teacher].[Teacher Name].children on rows
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Proficiency Assessment Standards].[Diagnostic].&[False],
    		 [Location].[Location].&[Doss High]
    		)
    
    

     


    This is giving me the number of students "at standard" for three total standards.  There is one weird thing happening with the "zero" count, but I think I'm going to open up a new thread about it.  (Basically, it seems to be counting all fields from the database where "At Standard" is 0, and not limiting/filtering it to any of the criteria in the query -- my "zero" count should be 5 and it's coming back as 71,159 ??)

    My final result set looks like:

    ______________0/3___1/3___2/3___3/3
    Ford, Paul__71159___7____9_____2



    • Edited by puffster Tuesday, January 3, 2012 6:42 PM
    • Marked as answer by puffster Tuesday, January 3, 2012 6:43 PM
    Tuesday, January 3, 2012 6:41 PM

All replies

  • I continue to play with this an am getting closer, currently my query looks like this:

    With set zero as ([Student].[Student Name].children, [Measures].[At Standard])
    Select 
    	Non Empty 
    	{
    		filter(zero, [Measures].[At Standard] = 1) 
    	}	on columns,
    	Non Empty [Teacher].[Teacher Name].&[Ford, Paul] on rows
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Proficiency Assessment Standards].[Diagnostic].&[False],
    		 [Location].[Location].&[Doss High]
    		)
    
    


    This gives me a result set that looks like:

    Student 1 Student 2 Student 3
    AtStandard AtStandard AtStandard 
    Ford, Paul  1 1 1

    It's basically listing out each student in their own column....what I'm wanting is a single column that gives the count of the number of students that meet that criteria.  I've tried adding a Count(function) in the "on columns" section, but then I get fussed at because it's expecting a tuple set expression.

    So what I'm shooting for is a result set that would look like this:

    AtStandard
    Ford, Paul  3

    Tuesday, January 3, 2012 5:03 PM
  • Well I managed to play around until I found the answer -- yay me!  I swear I had already tried this, but I guess I didn't have it perfect.  My final solution looks like this:

     

    With 
    	member [0/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 0).count
    	member [1/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 1).count
    	member [2/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 2).count
    	member [3/3] as filter([Student].[Student Name].children, [Measures].[At Standard] = 3).count
    Select 
    	Non Empty 
    	{[0/3], [1/3], [2/3], [3/3]}
    		on columns,
    	Non Empty [Teacher].[Teacher Name].children on rows
    From	[Proficiency Standards Assessment - All]
    Where	(
    		 [Proficiency Assessment Standards].[Standard Area].&[Algebra I],
    		 [Proficiency Assessment Standards].[Diagnostic].&[False],
    		 [Location].[Location].&[Doss High]
    		)
    
    

     


    This is giving me the number of students "at standard" for three total standards.  There is one weird thing happening with the "zero" count, but I think I'm going to open up a new thread about it.  (Basically, it seems to be counting all fields from the database where "At Standard" is 0, and not limiting/filtering it to any of the criteria in the query -- my "zero" count should be 5 and it's coming back as 71,159 ??)

    My final result set looks like:

    ______________0/3___1/3___2/3___3/3
    Ford, Paul__71159___7____9_____2



    • Edited by puffster Tuesday, January 3, 2012 6:42 PM
    • Marked as answer by puffster Tuesday, January 3, 2012 6:43 PM
    Tuesday, January 3, 2012 6:41 PM