locked
Cube usage past 30 days RRS feed

  • Question

  • Hi,

    We have an urgent need to find all users of our Cube for the past 30 days (user ID and all access dates & time). Our DBA is saying that without switching on Audit, he is unable to provide the output.

    Is there any other way we can obtain the required extract (using SQL Server 2014)?

    Thanks in advance.

    Wednesday, July 10, 2019 1:26 PM

Answers

  • No, SSAS don't store this informations, without existing auditing you can't get it.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 10, 2019 1:28 PM

All replies

  • No, SSAS don't store this informations, without existing auditing you can't get it.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 10, 2019 1:28 PM
  • As an alternative to custom auditing/trace (or even regularly capturing data from DMV: sessions/connections/commands etc.) or other data collectors you can enable QueryLog (similar to QueryStore in SQL Server) on your SSAS instance (same audit trace actually, it will affect all cubes/databases) with 100% sampling settings (performance impact expected) pointing it to a SQL table. Then just run query on a SQL table to extract information needed (users, objects, count, time, duration, etc.) as it has data similar to one you would see running profiler.


    Wednesday, July 10, 2019 1:58 PM
  • Also, please refer:

    Generating Usage Statistics from a SSAS Tabular Cube

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 11, 2019 3:15 AM
  • As an alternative to custom auditing/trace (or even regularly capturing data from DMV: sessions/connections/commands etc.) or other data collectors you can enable QueryLog (similar to QueryStore in SQL Server) on your SSAS instance (same audit trace actually, it will affect all cubes/databases) with 100% sampling settings (performance impact expected) pointing it to a SQL table. Then just run query on a SQL table to extract information needed (users, objects, count, time, duration, etc.) as it has data similar to one you would see running profiler.


    This is not correct, the QueryLog in SSAS only logs uncached SE query events. A single query can generate multiple SE cache requests or it can hit the cache and generate 0 log records. The QueryLog is only useful for running the Usage Based Optimization wizard, it is not really suitable for any other purpose.

    If you want to capture which users ran which queries at what particular time you need to use some sort of custom profiler trace solution. Either tracing out to .trc files and periodically loading those or using some custom service (like the old ASTrace example service) which writes trace events out to a SQL table.


    http://darren.gosbell.com - please mark correct answers

    Thursday, July 11, 2019 4:12 AM