Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Did you try: select * into your_tracking_table from sys.dm_os_sys_info; your_tracking_table will be a table name of your choice, and replace the DMV with what you queried. –  StanleyJohns Sep 11 '13 at 3:10
    
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. –  Woodford C. Sep 11 '13 at 13:17
    
And to answer your question, I did attempt to do that. Thank you for your response. –  Woodford C. Sep 11 '13 at 13:33
add comment

1 Answer

up vote 1 down vote accepted

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:

  1. 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.)
  2. Create a job in SQL Agent that periodically queries the SSAS DMV over the linked server and dumps the results into a relational table.
share|improve this answer
    
That worked beautifully. The only change I needed to make was to the Product Name field, as I was developing using SQL Server 2012 (MSOLAP.5). Thanks very much. –  Woodford C. Sep 13 '13 at 12:48
    
@Woodford: You're welcome, I'm glad you got it working. –  Jon Seigel Sep 13 '13 at 12:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.