What hierarchies are actually being used in my cube?

Working out how to monitor Analysis Services usage is something I've spent a fair bit of time doing over the last few months; I'll get around to writing up my SQLBits presentation here soon, I promise, but in the meantime here's a quick blog entry on a related topic: how can you work out which dimension hierarchies your users are actually using in their queries? It's useful to know this for a number of reasons, for example if you're thinking of changing the AggregationUsage, AttributeHierarchyEnabled or AttributeHierarchyOptimizedState properties. Or if you're just plain curious.

The first step towards answering this question is to capture all the MDX queries that are being run, and you can do this by running a Profiler trace on the Query Begin event and saving it to a table in SQL Server in the way I described here. Once you've got the queries you then need to work out which queries mention which hierarchies and you can do this in SSIS using the Term Lookup transform in the data flow:

Unfortunately the Term Lookup transform is a bit picky as far as the input it can use. You can get a list of all the hierarchies in every dimension in every database in Analysis Services by running the following DMV query:
select * from $system.mdschema_hierarchies

To be able to use this in a Term Lookup I had to create a linked server in SQL Server pointing to Analysis Services, then building a view that queried the Analysis Services DMV using the OpenQuery function so:
select cast(hierarchy_unique_name as varchar(100)) as HIERARCHY_UNIQUENAME from openquery([AS], 'select * from $system.mdschema_hierarchies')

Having done this, and having run the SSIS package, you then get a table containing one row for each query/hierarchy combination:

Running a select distinct on this table of course gives you a list of all the hierarchies mentioned in your queries:
 

Using different DMVs would allow you to analyse different aspects of the queries - for example, you could work out which MDX functions were being used in the queries with the mdschema_functions DMV (similar to what Mosha was doing here, but maybe with a view to seeing whether your client tool was using 'bad' MDX functions).

Source Click Here.