none
SSAS cube users - application tracking

    Question

  • Is there a way to track the SSAS cube usage from the application they are hitting. For example we want to know if the users are accessing the cube using excel pivot table or through some other application.

    Thursday, August 04, 2011 11:47 PM

Answers

  • Here's the approach I take. For Excel, I make sure all users are connecting via a .ODC file and I make sure the connection string in that file ends with ;Application Name=Excel PivotTable. Then I install ASTrace on the server to log Query Begin, Query End, and Errors to a SQL table. (Search for the tool. It's in the SSAS community samples project on Codeplex.) For Excel Services, so that I know the name of the xlsx file involved, I use SharePoint auditing. This data ends up in the AuditData table in the content database once turned on. For SSRS, I use the ExecutionLog table in the ReportServer database. Mash up that data and you have a pretty good idea of usage.
    http://artisconsulting.com/Blogs/GregGalloway
    Saturday, August 06, 2011 12:48 PM
    Moderator

All replies

  • You can get the Host Application name & Username from the below DMV. But this will give the currently connected user only.

    SELECT * FROM $system.DISCOVER_CONNECTIONS 
    

    Look for the columns CONNECTION_USER_NAME & CONNECTION_HOST_APPILCATION.

    Also you can run a profiler and monitor this.In the profiler look for NTUserName & ApplicationName columns.

    Hope this helps.

     

     

     

    • Proposed as answer by Thameem Friday, August 05, 2011 6:18 PM
    Friday, August 05, 2011 3:37 PM
  • But that just gives the currently connected user. However, i would like to track the history.

    Friday, August 05, 2011 8:00 PM
  • Here's the approach I take. For Excel, I make sure all users are connecting via a .ODC file and I make sure the connection string in that file ends with ;Application Name=Excel PivotTable. Then I install ASTrace on the server to log Query Begin, Query End, and Errors to a SQL table. (Search for the tool. It's in the SSAS community samples project on Codeplex.) For Excel Services, so that I know the name of the xlsx file involved, I use SharePoint auditing. This data ends up in the AuditData table in the content database once turned on. For SSRS, I use the ExecutionLog table in the ReportServer database. Mash up that data and you have a pretty good idea of usage.
    http://artisconsulting.com/Blogs/GregGalloway
    Saturday, August 06, 2011 12:48 PM
    Moderator