SharePoint 2013 Analytics Feature - IT-Pro, DB Admin & PowerPivot issues

This post covers some facts and details about the new SharePoint 2013 Analytics feature. For common stuff about that feature have a look at this TechNet article: LINK

In fact we have two different types of analytics in SharePoint 2013:

·         Search analytics analyzes content that is being crawled and added to the search index

·         Usage analytics analyzes user actions, or usage events, such as clicks or viewed items, on the SharePoint site

Search Analytics & Usage Analytics processing is based on several components. Continuous or incremental crawls must be scheduled and run. Also there are several timerjobs involved:

·         Analytics Timer Job for Search Service Application Search Service Application

·         Microsoft SharePoint Foundation Usage Data Import

·         Microsoft SharePoint Foundation Usage Data Processing

·         Usage Analytics Timer Job for Search Application Search Service Application

For more details about the jobs and job descriptions have a look here: LINK

This picture shows an overview for the new components:

Search analytics

PowerShell

Some Facts:

·         There are some cmdlets for create / delete/ get / move etc.  SharePoint Analytics infrastructure issues:

o   SPEnterpriseSearchAnalyticsProcessingComponent

o   SPEnterpriseSearchLinksDatabase

·         There are no direct PowerShell cmdlets to get informations from the SharePoint Analytics

·         We can use the .dot net SearchServiceApplication class, from Microsoft.Office.Server.Search.dll. in combination with PowerShell to work with SharePoint Analytics

Here is an example script from Radu Tut showing how we can connect via PowerShell to SharePoint Analytics:

$searchApp = Get-SPEnterpriseSearchServiceApplication

$site = Get-SPSite "{SiteUrl}"

$result = $searchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$site.ID,[System.Guid]::Empty)$result

#for a specific date or for a specific month

$date = Get-Date "2013-01-18"

$result.GetHitCountForDay($date)

$result.GetHitCountForMonth($date)

So for example using the first part of the script:

$searchApp = Get-SPEnterpriseSearchServiceApplication

$site = Get-SPSite "{SiteUrl}"

$result = $searchApp.GetRollupAnalyticsItemData(1,[System.Guid]::Empty,$site.ID,[System.Guid]::Empty)

$result

Gets the following result in my demo environment:

There are two methods available. You can use:

·         GetSearchReport

·         GetRollupAnalyticsItemData

Find complete post and info’s about using this in Radu Tuts blog here:  How to get Search Analytics Reports programmatically in SharePoint 2013

SQL Server DB´s

For me as an old SQL guy it was also interesting to see how this all will be established in SQL Server Databases. In an enterprise environment a direct connect to SharePoint Databases isn´t used in general, two point why this is interesting anyway:

·         It’s interesting to see how the analytics database is used to plan you databases files, partitions etc.

·         Only reading from the database using given functions and stored procedures can of cause be an option.

Additionally configuration overview for your environment can be found here: MSSSearchAnalysisEngineConfiguration in your Search_Service_Application_DB

The interesting table in Search_Service_Application_DB is [dbo].[SearchReportsData].

In this table the data for the Usage & Search reports is stored. The data from yesterday is imported once per day from log folder under:  %InstallDrive%\Microsoft Office Servers\15.0\Data\Office Server\Analytics\EventStore

 

The table shows the result in my demo environment:

Using PowerPivot for Excel we can connect to that table and analyze the data:

Usage analytics

The usage analytics data is stored in the logging database. This article describes how to access the data using SQL Views: LINK

Using these views together with PowerPivot we can create our own reports. Here is an example for RequestUsager showing all sites request by “Administrator” for CA:

Also there is an official way consuming Usage Data directly from the logfiles and use it to feed PowerPivot hosted in SharePoint itself: LINK

SharePoint 2013 empty Usage and Health data report

Why the “Usage and Health Data Report” can be empty in SharePoint 2013.

In SharePoint 2013 the feature WebAnalytics known from SharePoint 2010 is no longer part of the product. There is new feature called Analytics .

3 facts why “Usage and Health Data Report” can be empty

·       permission for the database Wss_logging as described in this article: http://social.msdn.microsoft.com/Forums/en-US/sharepointadmin/thread/681b6c53-3969-4984-8a78-4d6e6c55f29c

·       No Crawls: Actual data is processed during crawling. So if there is no crawler scheduled for content sources or there is a problem with the crawlers no actual data is processes.

·       Named Users: Only queries and interaction done by “normal” named users are captured. If you access SharePoint using an administrative account you queries etc. would not be showmen in the reports.