Debugging SSAS stored procedures

SQL Server 2005 | SQL Server 2008

Stored procedures offer a really powerful extensibility for Microsoft OLAP. But the more complex the functions get, the more you need debugging capabilities. Of course you will want to put the business logic of your procedure into a separate class file that can be tested and debugged without any of the SQL Server components. But often enough, the problems happens just in the context of the OLAP engine. Usually you’re using objects within the Microsoft.AnalysisServices.AdomdServer scope like Set, Expression, Tuple etc. Code that relies on these objects can only be executed in the context of the OLAP engine. This makes testing a lot more complicated.

Below is the procedure for debugging stored procedures by attaching to the OLAP service. You might also want to read the corresponding technet article .

First you build your assembly as usual and register it in Analysis Services. I’m using SSMS for that. Be sure to check the option for „Include debug information“ (this includes the pdb file of the assembly as well as you can see by looking at the generated script) and to set permissions to ‚unrestricted‘ as shown in the screen shot below:

(click to enlarge)

Now you switch to Visual Studio and set the breakpoints in your code.

(click to enlarge)

Ok, now for the most important part, you choose Debug / Attach to process… in your Visual Studio environment.

In the following dialog make sure that you allow attaching to managed code (press the ‚Select…‘ button in case managed code is not in the list). You should also active the option to show processes in all sessions as shown in the screenshot above.

Now locate the entry for msmdsrv.exe in the list, select it and click ‚Attach‘.

(click to enlarge)

Visual Studios now enters debugging mode waiting for the breakpoint to be reached.

In order to make the code stop at your breakpoint you need to trigger some action on the server that causes the code with the breakpoint to execute. For example you could open a report in Report Manager or you could execute an MDX query in SSMS.

In our example we could execute this query that runs the stored procedure with our breakpoint: