Fortunately, Microsoft offers the whole starter kit for SQL Server Analysis Services in its SQL Server 2005/2008 Developer Edition. The Developer Edition also includes samples and tutorials to get you started building your own cubes and reports.
There are still some interesting tools to make your life a lot easier:
- BIDS Helper (Download at Codeplex)
Easily deploy MDX scripts without the need to process the cube (see below for another way to do this). This really saves time. BIDS helper can do a lot more things like design aggregations. It seamlessly integrates with Visual Studio and adds some useful context menu entries.
- MDX Script Performance Analyzer (Download at Codeplex)
The Performance Analyzer deconstructs your MDX cube script and activates each line separately while measuring the execution time for a given query. By doing this you can easily see the bottle necks of your MDX cube script.
Useful tip: If you need to deploy just the cube script and you don’t have BIDS helper installed, you can also deploy the cube as usual but then click ‘Cancel’ in the process dialog. It’s not necessary to process the cube if you’ve just changed the cube script. Since processing usually takes much longer than the deployment, this really saves time.
There are of course other useful tools in the SQL Server 2005/2008 package like the SQL Server Profiler that can also be used to profile MDX queries, cache and SSAS aggregations.