Tuesday, September 2, 2014

SSAS – Aggregation Design - Usage based optimization wizard - There are no queries in the log for selected measure group


Recently I got a scenario from my friend where she was trying to migrate the query log from production to development. While trying to create the aggregation design from usage based optimization wizard it was showing the message “There are no queries in the log for selected measure group”. She had already followed the below helpful link by updating the query log table with proper server path.


But still same message was getting displayed. We have followed the following steps to troubleshoot the issue.

1. Start the profiler pointing to the SQL Server instance in which the query log table is stored.
2. Choose the below events in the profiler
    • SQL:BatchStarting
    • SQL:BatchCompleted
    • SQL:StmtStarting
    • SQL:StmtCompleted

3. Start running the Usage based optimization wizard.
4. The profiler will show the queries issued against the database. In my environment it shows query as below



Select  Count( MSOLAP_ObjectPath ),  Count( Distinct MSOLAP_User ),  Count( Distinct Dataset ),  Avg(Duration ),  Min(StartTime ),  Max(StartTime )   From  [OlapQueryLog]  Where  ( [MSOLAP_Database] = N'AdventureWorksDW2012Multidimensional-EE' )    And  ( [MSOLAP_ObjectPath] = N'MyServer.AdventureWorksDW2012Multidimensional-EE.Adventure Works.Fact Internet Sales 1' )

With the above query we can clearly verify the update in the query log is fine or not. In our case the issue was with the update of the server name.One more point we noticed is the database name and solution name should be the same. The Object path was being sent with the solution name.

Hope this tip will be helpful while debugging this issue. 

1 comment:

  1. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Reverse Engineering Services in Delhi
    3D Laser Scanning Services

    ReplyDelete