I am using Extended Events to analyse dimension/measure usage in an SSAS cube. I have used xmla template to create the event (from Chris Webb blog). I then have parsed the data into a staging table where I have stripped out the required field from the event data XML field.
select TraceFileName
, TraceEvent
, e.EventDataXML.value('(/event/data[@name="TextData"]/value)[1]', 'varchar(max)') as TextData
into #List
from
(
select [file_name] as TraceFileName
, object_name as TraceEvent
, convert(xml, event_data) as EventDataXML
from sys.fn_xe_file_target_read_file('*path*', null, null, null)
) e;
I plan to then use CHARINDEX
to find measures/dimension calls within the field TextData
. However to do this I will need a list of all dimensions / measures in the SSAS cube. Is there a way to rip this out?