Navigate Up
Sign In

SharePoint 2013 Analytics Feature - ITPro, DB Admin and PowerPivot Issues

Item is currently unrated. Press SHIFT+ENTER to rate this item.1 star selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+ESCAPE to leave rating submit mode.2 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.3 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.4 stars selected. Press SHIFT+ENTER to submit. Press TAB to increase rating. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.5 stars selected. Press SHIFT+ENTER to submit. Press SHIFT+TAB to decrease rating. Press SHIFT+ESCAPE to leave rating submit mode.

You may also be interested in: SharePoint Portals in minutes by SharePoint Implemented

 

Editor's note: Contributor Nicki Borell is a SharePoint Evangelist & Consultant for Experts Inside. Follow him @NickiBorell.

This post covers some facts and details about the new SharePoint 2013 Analytics feature. For common stuff about the 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 and Usage Analytics processing is based on several components. Continuous or incremental crawls must be scheduled and run. Also there are several timer jobs 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 of the new components:

2013-04-17-SharePoint2013Analytics-01.jpg

Search analytics

PowerShell

Some Facts:

  • There are some cmdlets for create / delete/ get / move etc. SharePoint Analytics infrastructure issues:
    • SPEnterpriseSearchAnalyticsProcessingComponent
    • SPEnterpriseSearchLinksDatabase
  • There are no direct PowerShell cmdlets to get information 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:

2013-04-17-SharePoint2013Analytics-02.jpg

There are two methods available. You can use:

  • GetSearchReport
  • GetRollupAnalyticsItemData

Find the complete post and information 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 would be established in SQL Server Databases. In an enterprise environment, a direct connect to SharePoint Databases isn´t used in general, but here are two point why this is interesting anyway:

  1. It’s interesting to see how the analytics database is used to plan your databases files, partitions etc.
  2. Only reading from the database using given functions and stored procedures can, of course, be an option.

Additionally, the 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 the log folder under: %InstallDrive%\Microsoft Office Servers\15.0\Data\Office Server\Analytics\EventStore

This table shows the result in my demo environment:

2013-04-17-SharePoint2013Analytics-03.jpg

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

2013-04-17-SharePoint2013Analytics-04.jpg

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 requested by “Administrator” for CA:

2013-04-17-SharePoint2013Analytics-05.jpg

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

Categories: SPF 2013; SQL; Search and Indexing; PowerShell

Comments

Mayank

Empty usage reports - Sharepoint 2013

Hi This is a great article
 
When I run Popularity trend for any site, the report is always empty. It seems that the analytics database is not storing usage information for some reason. Are you able to advice on this please?

Posted 16-May-2013 by Mayank
Chanel Galaxy S4 Case

Chanel Galaxy S4 Case

Incredible! This blog looks exactly like my old one! It's on a entirely different subject but it has pretty much the same page layout and design. Superb choice of colors!Chanel Galaxy S4 Case

Posted 25-Jul-2013 by Chanel Galaxy S4 Case
hars

Error when running the powershel script to get analytics info

You cannot call a method on a null-valued expression. $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) Give error here $result.GetHitCountForMonth($date)

Posted 25-Sep-2013 by hars
Jahanzaib Khan

usage report is still empty but number of queries report is not empty

hi i did everything i can from continues crawlers, sharepoint timer services to Requestusage view in db. one more thing receivers are enabled too. all the datacollection services are also running on sharepoint but unfortunately my usage report is empty but when i click on number of queries report it is fine. any help would be great appreciated thanks

Posted 26-Sep-2013 by Jahanzaib Khan
irfans@synergycom.in

number of visits for a month

Can you please tell how to get number of visits for a specfifc month

Posted 26-Sep-2013 by irfans@synergycom.in

Notify me of comments to this article

E-mail:
   

Add Comment

Title:

 
Comment:
Email:

   


Name:

 
Url: