Sharepoint 2010 Web Analytics Troubleshooting (Reporting DB)

  • Article

We can divide the Troubleshooting of Web Analytics into 3 different multiple parts.

The data is moves from

  1.)

   .usage files are created on all the WFEs.

  2.)

    Data moves from .usage files to Staging Database and Logging Database with Import timer jobs.

  3.)  

    Data moved from Staging Database to Reporting Database by Report Consolidation components.

 

Below I have included some basic steps which can be tried to confirm if Data is getting moved from Staging DB to Reporting DB 

Troubleshooting Steps:

  • The Data which need to be written to the Reporting Database that is gathered in the fact tables in the Staging database.
  • Data is moved into the Reporting Database from staging database by the following Timer job "Microsoft SharePoint Foundation Usage Data Processing " which runs Daily once around 2.00 AM.

 

  • If you will check the ULS logs around 2.00 AM you can see the Web Analytics Service Component Controller, ReportConsolidatorComponent, WebAnalytics.DatabaseMaintenance and other jobs execute.
  • For fact table you can get in uls logs :-

------------------------------------------------------------------

10-09-2012 02:00:19.17 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2af Verbose Copying the fact data from the source database using the connection string Data Source=spsqlext10;Initial Catalog=WebAnalyticsServiceApplicationStagingDB

10-09-2012 02:00:19.17 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ak Verbose Copying site inventory table 
10-09-2012 02:00:19.17 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator g14w Medium The last inventory copy time is 10/08/2012 00:00:00. 
10-09-2012 02:00:19.18 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2al Verbose Finished copying site inventory table 
10-09-2012 02:00:19.18 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAQueryText 
10-09-2012 02:00:19.20 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAScope 
10-09-2012 02:00:19.22 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAUserTitle 
10-09-2012 02:00:19.24 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ag Verbose Copying the fact table dbo.WAUserDepartment 
10-09-2012 02:00:19.26 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator d0t3 Verbose Copying the fact table dbo.WAAssetMetadata 
10-09-2012 02:00:19.39 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ah Verbose Copying the fact table [dbo].[WAClickFact] 
10-09-2012 02:00:19.44 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ah Verbose Copying the fact table [dbo].[WAResultFact] 
10-09-2012 02:00:19.48 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ah Verbose Copying the fact table [dbo].[WAUserSearchFact] 
10-09-2012 02:00:19.50 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator dg6z Medium There is no new fact data to copy. 
10-09-2012 02:00:19.50 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2ai Verbose Finished copying the fact data 
10-09-2012 02:00:19.52 WebAnalyticsService.exe (0x1570) 0x1104 Web Analytics Services Data Consolidator c2bg Medium Finished copying fact data to the database with server name MOSS2010.phm.com and database name WebAnalyticsServiceApplication_ReportingDB

-------------------------------------------------------------------------------------------

  • You can verify when was the last time Data was copied from Staging database to Reporting Database from the following Table=>  Dbo.WASetting

 

  • You can also compare the data in both the database (staging and reporting) from the table  dbo.WAAssetMetadata.  This table is in both the databases and can be compared using simple queries..

Select * from dbo.WAAssetMetadata with (nolock)

 

  • If  you want to check the result for any specific site collection or after specific date to cross check if data is moved or not in the reporting database you can use similar queries…

           Select COUNT(*) from dbo.WAAssetMetadata WITH (NOLOCK)

           select COUNT(*) from dbo.WAAssetMetadata where AssetId LIKE 'https://sitecollection%'

           select COUNT(*) from dbo.WAAssetMetadata where AssetId LIKE 'https://sitecollection%' AND InsertDateTime > '2012-06-2202:00:59.330'

  •   For the above dates you should generally see the web analytics report in site collection web analytics reports.

-----

 

  • Also the Reporting database in addition to MDF and LDF files you can see NDF files for this database. Each week reporting Database creates these NDF files.
  • These are the table partitions and not the database and we should never delete manually these NDF files.
  • They are deleted by a web analytics process which is ReportConsolidate Component once it cross the retention period. This Process runs daily.

 

  • You an create some queries with the help of functions(Reporting Database-> Programmability->Functinos) like To check Number of unique visits and run in reporting database and get the results.

                         SELECT * FROM [dbo].[WATrafficAggregationByDate] WITH (NOLOCK)
                         WHERE [AggregationId] = @aggregationid AND [IncludeSubSites] = 1 AND [MetricType] = 2 AND [DateId] BETWEEN @Dateid AND @Dateid

-----------------------------------------------------------------------