SSRS ReportServer Database Diagram

Ah!!! This is my first blog !!! Feels very good to get started !!!

In recent past it happened for me to work with SSRS reporting and i had to extensively work with the ReportServer database for getting the details about the reports. But when i started working i came to know there is no proper documentation for the ReportSever database. In the internet some information is available and it is scattered all over. So thought of creating a small documentation for the SSRS ReportServer database tables.

As a first step , i have created SSRS ReportServer database diagram for analyzing the relationship between the tables. You can download the PDF version of the file from the below link.

https://onedrive.live.com/redir?resid=EF9A407250207BDE!125&authkey=!AHefe5EwjU8GOGs&ithint=file%2cpdf

Thanks for reading my first blog. I will continue to write more in details about the ReportServer Database objects in my forthcoming blog "SSRS - ReportServer Database Tables Explored"

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.