Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

MDX blows up SSAS. Need help

Go down

MDX blows up SSAS.  Need help Empty MDX blows up SSAS. Need help

Post  mpalko 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

Back to top Go down

Back to top


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