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.

http://www.cognitiveinfo.com/2013/how-to-fix-usage-based-optimizer-connection-errors-in-ssas-2012/

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.