Why Web Anlytical Reports "Custom Reports" always display empty reports?

As we discussed in previous blog, Web Analytics service as part of Microsoft® SharePoint® Server 2010, is set of features to help you collect, report, and analyze the usage and effectiveness of your SharePoint Server 2010 deployment. Web Analytics features include reporting, Web Analytics workflow, and Web Analytics Web Part.

As  SharePoint administrators, we could like to leverage the SharePoint 2010 Customized Web Analytic Reports to output weekly usage reports for management on the following metrics, total number of page views, average number of page views per day, total number of daily unique visitors and average number of unique visitors per day.  Some key benefits of the custom reports are the ability display visual charts and graphs in excel worksheets and the ability to aggregate report information into one consolidated report per web app and subsequently sent out to management.You could refer here for details on different reports.

One issue we found in our environment is Custom Web Analytic Reports are not showing data. One of the consultant is able to generate the same Custom Web Analytic Reports with correct in his environment. We have tried everything from permissions to excel services without any luck. He are the steps to reproduce this error in our environments.

1. Navigate to any site and select "Site Actions" -> "Site Setting" -> "Site Collection Web Analytics Reports" ->on the left navigation select "Top Browsers" ->"Analyze" tab. You will notice a valid report with data shown in the aspx page.

2. Navigate to the ribbon group "Analyze" and choose "Customize Report". If you do not have this button, please refer the blog to enable this report.


3. Name the report, "Harry.xlsx" note the document library and the report will be generated and displayed. You could navigate to the report under http://siteURL/AnalyticsReports/Forms/AllItems.aspx? and open in the browser. We found notice no data present in the report as shown in the below screen.

 

We are not able to find any errors on the server and here is the log trace for your reference. It seems like call excel service succeed.

User=NA\harryc    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

Site=/sites/Harry    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

WcfSendRequest: RemoteAddress: 'http://sbx01:32843/484f9d239d0046a2af1afd67257e47d4/ExcelService.asmx' Channel: 'Microsoft.Office.Excel.Server.CalculationServer.Proxy.IExcelServiceSoap' Action: 'http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/ExcelServiceSoap/GetRange' MessageId: 'urn:uuid:6d0562b4-f247-47c7-b98c-b221c40cccb0'    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
WcfReceiveRequest: LocalAddress: 'http://sbx01.na.qualcomm.com:32843/484f9d239d0046a2af1afd67257e47d4/ExcelService.asmx' Channel: 'System.ServiceModel.Channels.ServiceChannel' Action: 'http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/ExcelServiceSoap/GetRange' MessageId: 'urn:uuid:6d0562b4-f247-47c7-b98c-b221c40cccb0'    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

Entering monitored scope (ExecuteWcfServerOperation)    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

ECS RequestId=2012    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

User=0#.w|na\harryc    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

Session=1.V21.5If3FmRT7OZcBtXSYrDI590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.57a3d5b9-15eb-47cc-a8b4-aae9bb4a67941.N    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
Document=http://sbx01/sites/Harry/AnalyticsReports/Harry.xlsx    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
Result=Success    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=4.05470527678797    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
WcfReceiveRequest: LocalAddress: 'http://sbx01.na.qualcomm.com:32843/484f9d239d0046a2af1afd67257e47d4/ExcelService.asmx' Channel: 'System.ServiceModel.Channels.ServiceChannel' Action: 'http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/ExcelServiceSoap/GetRange' MessageId: 'urn:uuid:6d0562b4-f247-47c7-b98c-b221c40cccb0'    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
Entering monitored scope (ExecuteWcfServerOperation)    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
Leaving Monitored Scope (ExecuteWcfServerOperation). Execution Time=0.222095266297812    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60
 
Result=Success    beb00800-6d2c-4fad-9f53-fe4ae1f8fc60

If you have any solution on this, please share with us.

Today as May 26, we had a good discussion with Microsoft DSE support and this issue has been resolved now. The trick is Web Anlytical Reports "Custom Reports" is based on excel service and it requires C2WT service in order to pass through the authentication. Here is the way to result the issues.

Log in to central admin -> Central Adminstration -> System Settings -> Manage services on server -> start "Claims to Windows Token Service". See screen shot for your reference.

You will see data generated for Web Anlytical Reports "Custom Reports" as below.

At this time, you should be able to enable the Web Anlytical Reports "Custom Reports" and display the data!

How to enable SharePoint 2010 Web Analytics "Custom Report"

After we evaluated and put the design together to enable SharePoint 2010 web analytics reports in November 2010 as described in previous blog, we have seen many power users try to use the reports. However, we have identified several issues that is preventing end users to get meaningful reports.

The first mystery to us is SharePoint 2010 provides a "Custom Report" users could schedule to generate the customized reports based on users criteria.  However some site collection reports such as "Top Pages" is missing "Custom Report" button on the ribbon. We have identified this is only happening on some sites for some environments not all sites. You could refer the two pictures with and without "Custom Report" button. listed in previous bog.

While debugging this inconsistent issue across different environments and different sites, we have finally identified the issue. Here is the summary of root cause, procedure to verify, and command to to fix it.

1. Root cause: The root cause is there is hidden site feature named WACustomReports needs to be enabled in order for users to see "Custom Report" button. You could check this from 14 hive directory
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES\WACustomReports.

<Feature  Id="AF6D9AEC-7C38-4dda-997F-CC1DDBB87C92"
          Title="$Resources:wacore,WACustomReportsFeatureTitle"
          Description="$Resources:wacore,WACustomReportsFeatureDesc"
          Version="14.0.0.0"
          Hidden="TRUE"
          Scope="Site"
          SolutionId="7ED6CD55-B479-4EB7-A529-E99A24C10BD3"
          ReceiverAssembly="Microsoft.Office.Server.WebAnalytics.UI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
          ReceiverClass="Microsoft.Office.Server.WebAnalytics.CustomReportsFeature"
          xmlns="http://schemas.microsoft.com/sharepoint/">
</Feature>

 2. Procedure to check. You could use the following powershell commend to verify whether you have this feature enabled on site collection.


Get-SPFeature -Site http://sitecollectionurl

If you have the feature enabled, you will see the following entry.
DisplayName                                               Id                                              Scope
-----------                                                   --                                               -----
WACustomReports                af6d9aec-7c38-4dda-997f-cc1ddbb87c92     Site


3. Commend to fix this if not already activated. Here is the commend to enable this feature so users could have the "Custom Report" button.


Enable-SPFEATURE WACustomReports –URL http://sitecollectionURL


Now you should have the "Custom Report" you could enjoy to use. We were working with Microsoft how we could enable this for all sites automatically. There is a thread on this already from MSDN.

I looked at issue again this week  April 26 2011, I realized that this feature is automatically enabled for sites created from OoB site definition. However, if you have your own site definition or template, you need to add this feature to the feature stapling to enable it after site created.

Why SharePoint 2010 Web Analytics "Top Browser" report does not record IE 8 browser?

After we Web Analytics reports to SharePoint 2010 sites, we have identified several issues. I have listed in SharePoint Web Analytics Service insight part II – Reports deep explore and issues. One of the issues is I’m not able to see IE 8 browser been reported in Web Analytics "Top Browser" report to the site I owned and I am use IE 8 all the time to access it. See the following screenshot for issue details. I have noticed IE 8 has been recorded on some other sites.

We have reported this issues to Microsoft and finally we identified this is a IE 8 issue NOT SharePoint issue. The issues is the IE 8 with “Display intranet sites in Compatibility View” setting does not send correct header information to SharePoint. It send itself as IE 7 and as a result, the Web Analytics "Top Browser" report will record as IE7.

Here is the way to fix it. From IE 8, go to Tools->Compatibility View Settings and  deselect “Display intranet sites in Compatibility View”. See the screen shot below.

 

 

This will allow IE 8 to send correct header to SharePoint and Web Analytics "Top Browser" report can record IE 8 access.

SharePoint Web Analytics Service insight part II – Reports deep explore and issues

After you setup the SharePoint 2010 Web Analytics as described in previous blog SharePoint 2010 Web Analytics Service insight part I - Planning and Configuration, you may enjoy reading all different reports now. Most of the reports are straight forward and users can understand without any instructions.  However, some of the reports are difficult to explain and some of them have some issues. It will worth while to drive deeper in some of those reports and share some of the issues identified.

1. Deep drive on some of the reports 

Among all Web Analytics reports, search reports are the most complicated reports to configure and explain. Let’s looked at the “Failed Queries” report as example in Search category as below. This report is missing leading since it does not display the query actually failed. Here is the way to read this report. You will noticed the “Percentage Abandoned” column. The most interesting value is less than 100% which means users did not click any links in the search result page NOT search failed.

The next "Best Bet" reports will display the search queries users conducted with the best bet URL users have setup. The two columns with "Best Bet Clickthrough" and "Percentage of Best Bet Clickthrough" are somehow difficult for me to explain. If you look at the report below, we are not sure why the number of Queries is 5 for the first entry, but the Best Bet Clickthrough is 11 that is much higher than the queries. The

Percentage of Best Bet Clickthrough is 57.89% that means some users do not click the Best Bet URLs after the search? If you know how to explain these reports, please let me know.

In order to get a"good" result on the Best Bet suggestions, you would need to setup the search keywords with suggestions as the screen shot below. Please refer the previous blog for reference how to setup. The search result will display the "Best Bet" as in the below screen shot if you have added this web part on the search result page.

Another report we are not sure how to explain is "Top Site Product Versions" report under Inventory. There is a column called "Site Product Version". In our case, we have 4 as the value as displayed in the below picture. We are not sure whether this is related to v4 master page on 2010. We are researching to figure at this time.

        2. Issues related to Web Analytics reports

The first mystery to us is some site collection reports such as "Top Pages" is missing "Custom Report" button on the ribbon. We have identified this is only happening on some sites not all sites. You could refer the two pictures with and without "Custom Report"button.

We have been working with Microsoft on this issue and have not identify the root cause or the solution. However, there is one symptom we identified that may related to this issue. We found the sites with "Custom Report"button have "Site Customized Reports" Document Library under View All Site Content page. Other sites without "Custom Report"button have not such Document Library on the sites. We just found the solution how to enable SharePoint 2010 Web Analytics "Custom Report".

 The second issue is IE 8 browser is not included in "Top Browser" report even IE 9 and all different Frirefox versions have been recorded. See the picture below. This seems like a IE 8 issue and you could find the reason why SharePoint 2010 Web Analytics "Top Browser" report does not record IE 8 browser.

The third issue is the duplicate entries for "Top Pages" report. The /Default.aspx is the same entry as whole URL like https://<servername>/sitename. However, both of them are listed in the report and we are not sure the reason. See the picture below for reference.

 
The forth issue is related to Best Bet Suggestion Report and Best Bet Action History Report. Best Bet Suggestion Report recommends URLs as most likely results for particular search queries based on analysis of usage patterns. The site administrators can accept or reject these suggestions. If they accept, the corresponding query-URL pair is added to the search keywords list. Best Bet Action History Report tracks the actions performed by the site administrator on the ‘Best Bet Suggestion’ Report. However, we are not be able to able to generate those reports and not be able to find the solution to generate these two reports. We have seen other people have similar issues and we are working with Microsoft to debug.

The fifth issue is related to capacity and performance of the Web Analytics services. If you recall the architecture of this service, the service depends on the log parser and report consolidator component. With increasing data, the report consolidator component may take longer than five hours to run that may impact the the performance. Here is the way you could enable the data trimming for this service.

• You can enable data trimming using the Set-WebAnalyticsServiceApplication cmdlet. When data trimming is enabled, the number of rows of data in the tables in the reporting database is trimmed to 20,000 rows per day per component (site, site collection, Web application, etc.). This decreases the time that the Reporting Component takes to run. 

• Verify that you meet the following minimum requirements: See Add-SPShellAdmin.

• On the Start menu, click All Programs.
• Click Microsoft SharePoint 2010 Products.
• Click SharePoint 2010 Management Shell.
• At the Windows PowerShell command prompt, type the following command: 

         Set-SPWebAnalyticsServiceApplication [-Identity <GUID>]-EnableDataTrimming

 Those are top issues we identified for Web Analytics services at this point. We are evaluating some other findings that could be shared in later time.

SharePoint 2010 Web Analytics Service insight part I - Planning and Configuration

Web Analytics service as part of Microsoft® SharePoint® Server 2010, is set of features to help you collect, report, and analyze the usage and effectiveness of your SharePoint Server 2010 deployment. Web Analytics features include reporting, Web Analytics workflow, and Web Analytics Web Part.  There are three categories of the SharePoint Web Analytics reports: Traffic, Search, and Inventory. The reports are aggregated for various SharePoint entities like Site, Site Collection, and Web Application for each farm. For more information, see Reporting and usage analysis overview.

SharePoint 2010 Web Analytics Service Application is one of the services that consumes large server resource. This service is also one of the services offers many reports that some of them are difficult to explain.  After we configured and running this services four months in production it worth us to look at the reports generated to give us some idea how to utilize the service. We have also identified some issues for this service application that is the focus for this blog.

1.       SharePoint 2010 Web Analytics Service Application capacity plan and performance impact

If you looked at the Web Analytics service application architecture diagram, you will notice that it is using several services that is perform heavily duty file parsing processes. The architectural overview of the Web Analytics service in a SharePoint deployment is described in Figure1 from Microsoft.

Data is logged into .usage files on the front-end Web servers where it is processed into the staging database created in the previous steps through the Timer Job infrastructure.  The data in the staging database is retained for 30 days and transitioned into the reporting database for longer term retention as specified in the retention period when the service application was created.  The information is subsequently surfaced through a variety of Web Parts by the Web Analytics Web Service.

During planning phase, you would need to plan how much data you need to keep, the default is 25 month for the reporting DB. We changed to 13 month so users could view the whole year reports. Please refer Microsoft Web Analytics capacity planning.

 

2.       SharePoint 2010 Web Analytics Service Application setup procedure

Setup Web Analytics service is very simple. You could setup from central admin or through PowerShell scripts.

 

Here are steps from central admin.

• Open SharePoint 2010 Central Administration and select Manage service applications under Application Management
• Select New | Web Analytics Service Application on the ribbon user interface

•  On the Create Web Analytics Service Application dialog specify the name for the new Web Analytics Service Application
• Provide a name for the new Application Pool
• Provide the name of the default database server where the Web Analytics reporting and staging databases will be hosted and specify the desired retention period
• Click OK on the new Create Web Analytics Service Application dialog to provision the new service application
• On a single server deployment select System Settings from SharePoint 2010 Central Administration and then click Services on Server
• From the list of available services start the Web Analytics Data Processing Service and Web Analytics Web Service

You could create a SharePoint 2010 Web Analytics service application using PowerShell using the script provided by Microsoft TechNet. However, I would not recommend use PowerShell script.

You could refer to existing blog and make sure you plan the space of the staging and reporting space.

 

3.       How to setup Web Analytics features and different reports

 Web Analytics features include reporting, Web Analytics workflow, and Web Analytics Web Part. There are three categories of the SharePoint Web Analytics reports: Traffic, Search, and Inventory. The reports are aggregated for various SharePoint entities like Site, Site Collection, and Web Application for each farm. You could refer this blog for details.

The one set of the reports I would like to bring up to your attention is search reports. You would need to have permission to setup before you could get “good” result. For example Best Bets Suggestions allow search admins to determine what the most relevant search result is for a given keyword. You could setup it if you follow up the instructions. We are still having issues to get this web part working and will keep you posted the issues found.

Custom Web Analytics Reports are useful to get general understanding of what’s happening on your sites. To get started, click on Customize Report button under Analyze tab in Ribbon. 

Web Analytics Workflows is a powerful new feature set that enables you to get reports sent out either on a schedule or when specific conditions are met. To setup a WAW, go to Web Analytics report you’re interested in and click on Schedule Alerts or Reports on Analyze tab in Ribbon. 

Web Analytics Web Part targeted at Site Managers is an end-user facing Web Part that can be easily inserted into any page on your site. To use this Web Part, go into Edit mode of one of your Site Pages and click on any place you can add a Web Part. Then, from Insert tab on the Ribbon, click on Web Part. Finally, click on Content Rollup category and select Web Analytics Web Part.

Now, you should have all your Web Analytics features and reports ready for you to enjoy. In the next session, I will show you how to read different reports and some issues we encountered.