There are many ways to optimize your analysis services cubes, but the real trouble is how do you know where you’re making progress and where you’re not? Well, how would you do this in your T-SQL environment ? You’d check out a profiler trace while you were running the queries and see what you could improve. We can do the same thing in Analysis Services. See Below for some instructions and insights:

Figure 1

Figure 1 Shows us connecting to an Analysis Services Instance in SQL Profiler

Figure 2

Figure 2 shows us configuring a standard trace file using the standard template (we will customize this of course, because no one uses the standard  )

Figure 3

Figure 3 shows us selecting only the most important events for basic troubleshooting. This is where Profiler proves it’s worth. The Query SubCube Verbose event and the get data from cache and aggregation events are showing you if the data is being retrieved from cache (i.e. your queries are using efficient scoping) or if they are being read directly from the partitions underneath. This could mean improper aggregations, etc..

Query SubCube Verbose is the full data about the query (similar to an XML Showplan in SQL Server, it is delivering the full query and the timings based on the profiler trace)

This trace is of me processing my cube, I’m looking for certain events that tend to run longer than others . Including building indexes, etc.. So far so good!