Create a standalone report from the SharePoint 2010 Web Analytics Service App

I did it – albeit with a lot of help from a variety of folks. I successfully created an SSRS report that queries the SharePoint Web Analytics database. If you don’t have SSRS, then there’s always PowerPivot. The queries will work with both. Here’s how I did it.

First, you’re going to need the Site Aggregation ID. Why Microsoft uses this is beyond me. I ran across a blog post where the writer wrote a console app to extract this (blog post HERE). Tried the app with no luck. I then posted THIS on TechNet. Thanks to Guru Karnik for getting me on the right path.

The only catch is Guru’s query uses CURSORs. According to Tim Laqua this is very bad, and I’m inclined to listen to him (you’ll notice our blogs look eerily similar – yes I used the same layout). So what to do? Well we first need the Aggregation ID. I grabbed this portion of Guru’s query and fired away:

SELECT DISTINCT DimensionName as SiteCollectionID,AggregationId,SM.[Path]

FROM WASiteInventorySnapshot WASIS WITH (NOLOCK)

INNER JOIN [SPConfigDB].[dbo].[SiteMap] SM

ON WASIS.DimensionName = SM.Id

WHERE WASIS.DimensionType=0

Order by Path

Take a look at your results and you now have the Site Collection GUID, the AggregationID and the URL path. Filter on the site collection you’re after and grab the Aggregation ID. If you’re not sure which site collection you want to grab (as was the case for me), you can grab the site collection id and use PowerShell to show you the way like this:

OK, AggregationID in hand, we place it into the following query:

USE [WebAnalytics_ReportingDB]

   --Declare everything

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Date

DECLARE @EndDate Date

DECLARE @DateDiff Int

  --Set Variables; @datediff is the amount of days you're after

SET @DateDiff = 30

SET @StartDate = DATEADD(d,-1*@DateDiff,GETDATE())

SET @EndDate = GETDATE()

SET @AggregationID = '[paste aggregationid here]'

SET @SiteID = '[paste siteid here]'

SET @SitePath = '[paste URL here]'

  --Make Magic

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM [WebAnalytics_ReportingDB].[dbo].[fn_WA_GetSummary] (Cast(CONVERT(varchar(8),@EndDate,112)as int),Cast(CONVERT(varchar(8),@StartDate,112)as int),@DateDiff,@AggregationId,1)

You’ll notice I took Guru’s query and further tweaked it for my use. Couple of notes here:

• The GetSummary function the query is calling is a little jacked. You need to set a Start Date, End Date, and a Date Difference (i.e. the difference between the 2 dates). Why? Because that’s the variables the function wants. Like I said…jacked. Get’s worse below.
• For me, I wanted the SSRS report to always display the last 30-days starting with Today’s date. Later I can always go back and add some sort of parameter/expression magic to make the dates customizable by the user, but for now this works.
• Notice in the Set Variables section of the query that I’ve included the SiteID and SitePath. I did this for posterity only. It has no other bearing on the query other than making it available for me when I create the report.
• I first declare the date fields as Date so that I can do some magic with them in terms of calculating the dates programmatically, but later on I have to convert them to Int so the function will work properly. Very jacked, but hey, it works.

But what about Top users? Got that too:

USE [WebAnalytics_ReportingDB]

   --Declare everything

DECLARE @SiteId UniqueIdentifier

DECLARE @AggregationId UniqueIdentifier

DECLARE @SitePath NVarchar(255)

DECLARE @StartDate Date

DECLARE @EndDate Date

DECLARE @DateDiff Int

  --Set Variables; @datediff is the amount of days you're after

SET @DateDiff = -30

SET @StartDate = DATEADD(d,@DateDiff,GETDATE())

SET @EndDate = GETDATE()

SET @AggregationID = '[paste aggregationid here]'

SET @SiteID = '[paste siteid here]'

SET @SitePath = '[paste URL here]'

  --Make Magic

SELECT @SiteId AS 'SiteId',@AggregationId AS 'AggregationId',@SitePath AS 'SitePath',@StartDate 'StartDate',@EndDate 'EndDate',

* FROM [WebAnalytics_ReportingDB].[dbo].[fn_WA_GetTopVisitors] (Cast(CONVERT(varchar(8),@StartDate,112)as int),Cast(CONVERT(varchar(8),@EndDate,112)as int),@AggregationId,1)

Similar to the query above, only this time I call a different function: GetTopVisitors. Again, had to do some magic with the Start and End Dates to calculate programmatically.

This is only the beginning. Lot’s of possibilities here. The downfall to this approach is that it’s site specific. There is a function that will show top pages, but you can’t get down to the specific web (i.e. sub-site).

Next iteration will allow users to input custom dates and we’ll get creative with the graphs and charts in SSRS (think: dancing kittens and rainbows style). Thanks to everyone that gave me a hand in this.