Sitecore Analytics reports - Is this only for Analytics database, can I use master database to generate reports?

Question

I was trying to create a report from master database in analytics reports. (Stimulsoft Report Designer) As it explains in the reports cookbook, I have created a "mrt file" (Report UI) and a report definition item in Engagement analytics. I have configured the datasource item as query item (/sitecore/system/Settings/Analytics/Reports SQL Queries/Visit Pages). It worked.

But then I tried with a query using the master database, in the SQL query item I specifically mentioned the database as 'testProjectMaster' to point to master database. It did not work!

Then I figured out that in "/sitecore/system/Settings/Analytics/Reports SQL Queries/Visit Pages" item and other query items, it does not specify the database, that means by default sitecore queries the analytics database.

Is this a limitaion in sitecore, cant we query the master databse for reports? Are there any good resources to follow on creating reports?

Answer 1

I suggest taking the SQL from the Visit Pages report and running it in SQL Server Management Studio. There, you will be able to quickly see what's preventing your query from running. If I had to venture a guess, I would suspect that your SQL user account does not have db_datareader access to the master database.

The default SQL queries provided by Sitecore assume that the DMS is configured as the default database in the connection string. This, however, does not prevent you from querying other databases or doing cross-database joins like so:

SELECT TOP 100 * FROM Pages
INNER JOIN Sitecore_Master.dbo.Items AS MasterItems ON Pages.ItemId = MasterItems.ID

A word of caution.. from my experience, this can really slow down your reports as it does not take advantage of indexing and creating indexed views doesn't work across multiple databases.