2

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.

2
  • 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
  • And to answer your question, I did attempt to do that. Thank you for your response. – Woodford C. Sep 11 '13 at 13:33
2

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.
1
  • 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
0

If you track connections, sessions and other objects through DMVS consider that the data like the sum bytes of sessions or connection will be not valid.

For example with a periodic SQL Job with an interval of 60secs, if a connection is made and this connection made some sessions that retrieved data and the session and connection expired or closed inside the window time of 60secs, the Job may not capture the data and then your results may be incorrect.

DMVS is data that is stored and is remained in RAM of the running server until is restarted or closed. Also DMVS must considered as snapshot of the system and not historical log data.

The resources consumption and the data transfer will be bigger than the displayed results from the tables that you captured with DMVS.

3
  • It may not have been obvious from the question since the actual query was in the comments (I have added it in the question now) but the OP is querying SSAS dmv's – Tom V Nov 30 '16 at 10:35
  • Yes, but also i am trying to say that you are able to view now the connections and sessions running on the server executing this mdx but you can not be sure for the total of users connected within a day while capturing with interval. – Stavros Koureas Nov 30 '16 at 10:42
  • OK, I was just pointing out in case you missed it – Tom V Nov 30 '16 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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