13 Replies Latest reply on Aug 21, 2013 2:44 PM by Joshua Milligan

    Adding Index() table calculation causes long query execution?

    Joshua Milligan

      This one's got me stumped:

       

      I'm connected to MS SSAS using Desktop and one of the views is a cross-tab that depending on various filters has about 20 or so rows of resulting data (one row per prospect).  Behind the scenes, the cube has about 12 million prospects. Performance is great until I try to sort prospect by the revenue measure at which point a query gets executed which never seems to finish.

       

      So, I thought that perhaps I'll just use a table calculation to do the sort instead.  After all, the view is created almost immediately with no sort, so a table calculation on 20 rows shouldn't add any perceptible overhead, right?  I create a ranking function using Index() and set the default calculation to be along prospect sorted by revenue field.  As soon as I add it to Rows, to the left of the prospect field, a dialog box comes up that says "Executing Query..." and sits there counting seconds and minutes until I finally cancel it.

       

      Now I know I should check the logs to see what MDX is getting executed, but I don't have access right now. (and no packaged workbook to post either -- and I've probably also come close to violating #12 of So Your Question Didn't Get Answered...)

       

      What I'm hoping is that someone has seen this before and can explain from a conceptual perspective why the simple addition of a table calculation to a view would cause a query to be run (and a long-running one at that).  I suspect that the "sort" of the table calculation must alter the underlying query, but I had thought the sorting would occur post-query.

       

      Regards,

      Joshua