MDX blows up SSAS. Need help

View previous topic View next topic Go down

MDX blows up SSAS. Need help

Post  mpalko on Tue May 29, 2012 8:45 am

We have a Sales cube that we have been using at my company close to a year now. The other day I added a new attribute to the Sales Item dimension to the cube. No big deal. I have done this before. So my user modified her report using report builder 2.0 against the cube. After she did that, when she tried to execute/view the report SSAS blows up. The physical memory climbs all the way to 100% hanging the server. So after researching the problem, I noticed that it had to be related to the MDX query report builder creates. Here is the MDX from her report before the change, we'll call Query A (this works):

SELECT NON EMPTY {
[Measures].[Actual Labor Cost],
[Measures].[Actual Material Cost],
[Measures].[Invoice Extended Price],
[Measures].[Actual Overhead Cost],
[Measures].[Total Actual Invoice Cost],
[Measures].[Actual Sub Contracting Cost],
[Measures].[Actual Setup Cost] }
ON COLUMNS, NON EMPTY { (
[GL Post Date].[Calendar Year].[Calendar Year].ALLMEMBERS *
[GL Post Date].[Date].[Date].ALLMEMBERS *
[GL Post Date].[Month Name].[Month Name].ALLMEMBERS *
[Sales Item].[Business Line].[Business Line].ALLMEMBERS *
[Sales Item].[Product Line].[Product Line].ALLMEMBERS *
[Sales Item].[Product Class].[Product Class].ALLMEMBERS *
[Sales Item].[Group Code].[Group Code].ALLMEMBERS *
[Invoice Details].[SO Number].[SO Number].ALLMEMBERS *
[Customer].[Name].[Name].ALLMEMBERS *
[Sales Order Details].[Job Order Number].[Job Order Number].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [GL Post Date].[Calendar Year].&[2012] } )
ON COLUMNS FROM ( SELECT ( { [GL Post Date].[Month Name].&[5] } )
ON COLUMNS FROM ( SELECT ( { [Invoice Details].[M2M Company].&[US] } )
ON COLUMNS FROM ( SELECT ( { [Customer].[Intercompany].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Warranty].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Sample].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Freight].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[D And D].&[False] } )
ON COLUMNS FROM [Sales]))))))))
WHERE ( [Sales Item].[D And D].&[False],
[Sales Item].[Freight].&[False],
[Sales Item].[Sample].&[False],
[Sales Item].[Warranty].&[False],
[Customer].[Intercompany].&[False],
[Invoice Details].[M2M Company].&[US] )

Here is her query after her change that crashes SSAS, we'll call Query B:

SELECT NON EMPTY {
[Measures].[Actual Labor Cost],
[Measures].[Actual Material Cost],
[Measures].[Invoice Extended Price],
[Measures].[Actual Overhead Cost],
[Measures].[Total Actual Invoice Cost],
[Measures].[Actual Sub Contracting Cost],
[Measures].[Actual Setup Cost] }
ON COLUMNS, NON EMPTY { (
[GL Post Date].[Calendar Year].[Calendar Year].ALLMEMBERS *
[GL Post Date].[Date].[Date].ALLMEMBERS *
[GL Post Date].[Month Name].[Month Name].ALLMEMBERS *
[Sales Item].[Business Line].[Business Line].ALLMEMBERS *
[Sales Item].[Product Line].[Product Line].ALLMEMBERS *
[Sales Item].[Product Class].[Product Class].ALLMEMBERS *
[Sales Item].[Group Code].[Group Code].ALLMEMBERS *
[Invoice Details].[SO Number].[SO Number].ALLMEMBERS *
[Customer].[Name].[Name].ALLMEMBERS *
[Sales Order Details].[Job Order Number].[Job Order Number].ALLMEMBERS *
[Sales Item].[Group Code Description].[Group Code Description].ALLMEMBERS *
[Sales Item].[Product Group].[Product Group].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [GL Post Date].[Calendar Year].&[2012] } )
ON COLUMNS FROM ( SELECT ( { [GL Post Date].[Month Name].&[5] } )
ON COLUMNS FROM ( SELECT ( { [Invoice Details].[M2M Company].&[US] } )
ON COLUMNS FROM ( SELECT ( { [Customer].[Intercompany].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Warranty].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Sample].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Freight].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[D And D].&[False] } )
ON COLUMNS FROM [Sales]))))))))
WHERE ( [Sales Item].[D And D].&[False],
[Sales Item].[Freight].&[False],
[Sales Item].[Sample].&[False],
[Sales Item].[Warranty].&[False],
[Customer].[Intercompany].&[False],
[Invoice Details].[M2M Company].&[US] )


If I run Query A in SSMS, no problems. If I run Query B, it crashes. So I fooled around with it and realized something. The order on her ON ROWS clause didn't really match what she wanted. So we changed it around to this:

SELECT NON EMPTY {
[Measures].[Actual Labor Cost],
[Measures].[Actual Material Cost],
[Measures].[Invoice Extended Price],
[Measures].[Actual Overhead Cost],
[Measures].[Total Actual Invoice Cost],
[Measures].[Actual Sub Contracting Cost],
[Measures].[Actual Setup Cost] }
ON COLUMNS, NON EMPTY { (
[GL Post Date].[Calendar Year].[Calendar Year].ALLMEMBERS *
[GL Post Date].[Date].[Date].ALLMEMBERS *
[GL Post Date].[Month Name].[Month Name].ALLMEMBERS *
[Sales Item].[Business Line].[Business Line].ALLMEMBERS *
[Sales Item].[Product Line].[Product Line].ALLMEMBERS *
[Sales Item].[Product Group].[Product Group].ALLMEMBERS *
[Sales Item].[Product Class].[Product Class].ALLMEMBERS *
[Sales Item].[Group Code].[Group Code].ALLMEMBERS *
[Sales Item].[Group Code Description].[Group Code Description].ALLMEMBERS *
[Invoice Details].[SO Number].[SO Number].ALLMEMBERS *
[Customer].[Name].[Name].ALLMEMBERS *
[Sales Order Details].[Job Order Number].[Job Order Number].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [GL Post Date].[Calendar Year].&[2012] } )
ON COLUMNS FROM ( SELECT ( { [GL Post Date].[Month Name].&[5] } )
ON COLUMNS FROM ( SELECT ( { [Invoice Details].[M2M Company].&[US] } )
ON COLUMNS FROM ( SELECT ( { [Customer].[Intercompany].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Warranty].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Sample].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[Freight].&[False] } )
ON COLUMNS FROM ( SELECT ( { [Sales Item].[D And D].&[False] } )
ON COLUMNS FROM [Sales]))))))))
WHERE ( [Sales Item].[D And D].&[False],
[Sales Item].[Freight].&[False],
[Sales Item].[Sample].&[False],
[Sales Item].[Warranty].&[False],
[Customer].[Intercompany].&[False],
[Invoice Details].[M2M Company].&[US] )

Now by just moving Group Code Description and Product Group up to Sales Item dimension section, the query executes fine. Why is that? How does the order of the ON ROWS section make a difference to a point of crashing and not crashing SSAS? My apologizes, but knowledge on MDX is still a little green. Any help and explanation would be great.

Thanks, Mike

mpalko

Posts : 1
Join date : 2012-05-29

View user profile

Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum