We are have performance issues and need to go into a deeper diagnostic understanding of the queries generated, their formulation, where they are running and how to tune.
We are connecting to SSAS multdimensional via import mode.
In the query editor, multiple filters and transformations are being performed.
The report is taking over an hour.
how do we rule out that the queries issued are being selective and passing in values to filter data (I.e. Date > 2015 as opposed to pulling all data). I can see the m code generated but how do I assure my Ssas dab that the tool is not selecting the whole cube, and then subsequently dropping columns and filtering data. I expect that it will pass in filters as early as possible to reduce processing. The way the m code is displayed it suggests it starts big and then winnows down.
Where do we get a list of actual queries and do a performance review.
fiddler, sql profiler, etc.
any published best practices for performance reviews?
Look at the applied steps on the right hand side of he query editor. Right click on the first step and select Native Query. This is the query folded code being sent to the server. Work your way down the list, right clicking until this menu is greyed out. The first step where it is greyed out is the step that is causing query folding to stop. When query folding stops, power query takes over, and this is less efficient. The general rule is to use the menus (on manual code) for as long as possible to keep query folding going.
We're running Analysis Services multidimensional as the data source so unfortunately the 'view native query' menu isn't supported. We ran a Profiler trace at the server level and it captured what I was looking for and confirmed that it folded in multiple steps into 1 query.