SharePoint 2010 Web Analytics under the covers

I encountered an issue with Web Analytics not processing usage that was evident through the lack of hits in the usage reports in central administration and site collections. Fortunately I got the problem fixed which I’ll share with you in a moment as in the process I did some digging into the how the Web Analytics process works and the reporting databases that may be of interest.

I started with the high level view provided in Microsoft Enterprise Content Management (ECM) Team Blog http://blogs.msdn.com/b/ecm/archive/2010/03/21/introducing-web-analytics-in-sharepoint-2010.aspx.

I’ve interpreted the process visually for how web analytics get collected look something like this:

These are the steps that occur:

• User request a page and action gets picked up by the Web Analytics service that runs on each SharePoint server in your farm (this may be server side or JS call but have investigated).
• The Web Analytics service logs this in the “.usage” files in the location specified in central administration.
• A Timer job called “Microsoft SharePoint Foundation Usage Data Import” that by default runs every 30 minutes that imports the logs into the staging database.
• Each night the “Microsoft SharePoint Foundation Usage Data Processing” Timer job runs and transforms the data into the reporting database; from my investigation, this populated the “WAClickFact” (Fact) table.
  • This timer job also runs a number of stored procedure to aggregate the data into other tables (WAClickAggregationByDate , WATrafficAggregationByDate, WASearchTrafficAggregationByDate, etc) that are displayed in the reports. Note: Running this manually does not seem to execute the latter part of this process.
• The last run time of the import from staging and the aggregation is logged in the Settings table in the Reporting database

In my case, the data for hits was being populated into the fact table in the reporting database but the aggregation tables were missing data. The problem was that database files were missing which is when I discovered that part of the Timer Job processing task creates new files for the aggregation tables every 4 or 5 days (this may be based on size) and is done so to improve performance by partioning the table. The reason for the missing files. Not enough disk space was available and as the routine never attempts to create the files again it fails until you manually create the file.

Microsoft assisted in locating the missing file which was logged in the Diagnostic logs when we set Web Analytics logging to Verbose. We could then create the files manually using the script below and leave the overnight jobs to run. Thankfully this processed all the missing aggregations and we lost no data so much thanks to Microsoft’s support team.

Use this SQL statement to find any groups and check for ones with zero files.

SELECT f.*,g.groupname, g.status AS FGStatus FROM sysfiles f LEFT OUTER JOIN sysfilegroups g ON f.groupid= g.groupid

Use the following SQL to get the file location and create a new file.

use DBName

go

DECLARE @DBFilePath NVARCHAR(2000)

SELECT @DBFilePath=LEFT(filename,LEN(filename)-CHARINDEX(N’\’, REVERSE(filename))+1) FROM sysfiles WHERE RIGHT(filename,3)=’mdf’

IF NOT EXISTS (SELECT 1 FROM sysfiles f INNER JOIN sysfilegroups g ON f.groupid= g.groupid WHERE g.groupname=’SharePoint_WebAnalytics_ReportingAggregation20101128′)

EXEC(‘ALTER DATABASE showFGDB ADD FILE (

NAME= ”SharePoint_WebAnalytics_ReportingAggregation20101128”,

FILENAME = ”’+@DBFilePath+’SharePoint_WebAnalytics_ReportingAggregation20101128.ndf”)

TO FILEGROUP SharePoint_WebAnalytics_ReportingAggregation20101128’)

By Alan Marshall

Twitter: @pomealan

Principal Architect

Gen-i NZ