What is using all that memory on my Analysis server instance ?
With the release of SQL Server 2012 tabular models identifying what the memory use of objects on your server instance is has become more important then ever. Since everything is in memory, being able to tune the model to remove columns that take up loads of memory could be very valuable. Or being able to use your development database to extrapolate the memory usage that you will have on you product machine.
I have created a PowerPivot workbook that will allow you to investigate the memory usage on your server instance (this report could also be used on a MOLAP instance).
The report contains two worksheets. Worksheet 1 contains two dashboards, the first dashboard contains the top 10 tables on the server instance by memory usage.
The second worksheet allows a more detailed investigation on all the objects
on the server by using a pivottable. I have created a hierarchy of all the objects that allows to start at the top to bottom when you want to investigate the details.
of course since this is a pivottable there you can create your own insights if you want:
You can download the workbook and try for you self here BISMServerMemoryReport. Now also available is the Excel 2013 version here .
In order to get this working on your tabular or multidimensional server is change the connection:
After this, refresh the table and refresh the data in the workbooks from Excel.
The data itself is being retrieved into one table by a DMV function:
I hope you can use this workbook, let me know if you have any comments or feedback on the report. I am always looking for new insights and ways to improve the workbook.