19 Jan 2012

How to Query SharePoint Web Analytics Reports from SQL Server

This time, I want to show you on how you can query the SharePoint Web Analytics Reports from your SharePoint 2010. It seems that some people still confused because on my previous post, I didn’t give any examples or anything to proof this. I need to apologize, and in this post, I’ll write about how to Query it first, so you can get what I ‘m trying to explain.
I think image will tell you much than words…
That is the result of my query in SQL Server, I got the Top Pages on one of my SharePoint Web Application. So, to reach that result, below is the steps I took before.
Determine what type of Analytics Report I would need, is it Site Collection (SPSite) or Site (SPWeb)?
I grab my home-made application, GetAggregationId in Command Prompt, to get the Aggregation ID for my Site / Site Collection. This must be done in SharePoint machine box. Confused smile A little forgot right there, what is the parameters, but finally…
Yay… got it for SPWeb and SPSite level. Open-mouthed smile
Then I looked at the MS-MAVA Documentation, what query I could use and modify it based on my requirements. I want to get the Top Pages on my SharePoint site.
Open the SQL Server Management Studio, and point to Web Analytics Report Database. If you unsure where and what is the Web Analytics Report Database, you can read my previous post about Web Analytics.
After a serious modifications of the Top Pages query, now this is my final query.
Query for Web Analytics
  1. USE WebAnalytics_RPT_Service_DB
  2. exec sp_executesql
  3. N'SELECT TOP (@MaxRow) [PageId], [Frequency], [Percentage]
  4. FROM [dbo].[fn_WA_GetTopPages](@Start, @End, @AssetGuid, default)
  5. ORDER BY [Frequency] DESC',
  6. N'@Start int,@End int,@AssetGuid uniqueidentifier,@MaxRow int',
  7. @Start=20110101,@End=20120505,@AssetGuid='793474d4-6cc3-9228-6e94-6c761a0c5080',@MaxRow=10
You may want to notice, what is @Start, @End, @AssetGuid, and @MaxRow are. @AssetGuid is the Aggregation ID for your query. While @MaxRow specifies max rows returned for this query. @Start and @End is the start date and end date for the query in yyyyMMdd format. So, if you want to input start date for Jan 5th 2012, so the @Start must be 20120105. Notice that there’s no URL parameter in this query, all is set up by using Aggregation ID.
Congratulations… you’ve already done it! Hot smile

4 komentar:

  1. In addition to the SharePoint basic usage reports, many organizations wish to extend their holistic SharePoint marketing and analytics services via a compelling marketing offering built into the SharePoint environment that can help to improve enterprise websites, portals and third party applications. CardioLog - SharePoint Marketing Suite helps enterprises to engage their customers / users, achieve brand loyalty / portal ROI, and provides enhanced detailed analytics and usage reports. For further information, you may contact info@intlock.com.

  2. For you guys, who don't want to waste time and have some bucks in your wallet, you can follow another nice solution from Robert Briggs.

    Thanks Robert Briggs, for your comments ;)

  3. Hats of bro....
    gerat job..

  4. Microsoft.Office.Server.WebAnalytics.ProcessedDataRetriever.AggregationContext.GetContext can be used to get the AggregationId

    Microsoft.Office.Server.WebAnalytics.Reporting.AnalyticsReportFunction.GetWebAnalyticsReportData can be used from a web part.


[give me your ideas, hopes, comments and compliments below...]
[aaand...... +1 if you need to....]