Get Results from DMV Query into SQL Server Table today I began to troubleshoot an issue that I was asked to resolve, and for the first time I found myself querying in the SSAS database. I obtained a very useful set of results (who was connecting to what applications/cubes and for how long), but for the life of me I cannot figure out how to get the results from that query into a database table so that I can track the data. Any help would be much appreciated, and thank you in advance for any assistance you are able to provide. 

Here is the query I am running: 

Select connection_id , connection_user_name ,  
connection_impersonated_user_name , connection_host_name ,   
connection_host_application , connection_start_time , 
connection_elapsed_time_ms , connection_last_command_start_time , 
connection_last_command_end_time , connection_last_command_elapsed_time_ms , 
connection_idle_time_ms , connection_bytes_sent , connection_bytes_received , 
connection_data_bytes_received FROM $system.discover_connections 


This returns all of the information I am currently interested in seeing.
In SSAS, there's no such thing as a table; it's all multi-dimensional stuff. What you need to do is get the results into a table in a database engine instance.

Here's one way to do it:


Create a linked server to SSAS on your management SQL Server database engine instance. (Note: you'll need to install the SSAS drivers if the two instances aren't on the same box.)
Create a job in SQL Agent that periodically queries the SSAS DMV over the linked server and dumps the results into a relational table.