Usually, when "Most popular" content feature needs to be implemented, developers choose to add a connection string to the reporting database to the CD instances and run a SQL statement that produces necessary output. However, it is not always possible to do that due to security reasons. We ran into a situation when calling Reporting database directly was not allowed, therefore we chose a different approach. Below you'll find an overview of the solution.
AnalyticsHelper:
TrackingService:
This code allows us to record the Page Event and LongId as its CustomValue where we store the IDs of all ancestors.
https://github.com/jcore/Sitecore.PopularItems/blob/master/ItemPath.sql
ItemPathProcessor is responsible for the processing of an interaction and storing the ItemPath data in the new SQL tables if there is an Item Path specified.
https://github.com/jcore/Sitecore.PopularItems/blob/master/ItemPathProcessor.cs
Below you can see the patch file that enables the ItemPathProcessor:
As you can see Dimension classes match the [ItemPath] SQL table columns and the Fact classes match the [Fact_PageItemPaths] one.
The first portion of the most popular feature is implemented - the data is collected.
Requirements:
- Display two of the most popular items that are based on a list of allowed templates
- The most popular items should live under the same section as the context item.
Gathering the information about a page view and the section
To collect the data about user visit and the section the item belongs to we implemented the following helper and placed it in DefaultLayout.cshtml that is being used by every single page item on the site.
@Html.Custom().PageViewWithSectionTracking(Sitecore.Context.Item.ID, Sitecore.Context.Item.TemplateID, SwitchingLinkManager.GetItemAncestors(Sitecore.Context.Item))
We have a custom LinkProvider implemented in the solution for items that live in buckets and are stored outside of the normal site tree structure. Therefore we have a helper method that retrieves all ancestors for an item and passes that to the helper method. The ancestors are not necessarily the ancestors of an item in the tree, but logical ancestors.
The PageViewWithSectionTracking method looks like this:
public static HtmlString PageViewWithSectionTracking(this CustomHelper helper, ID itemId, ID templateId, IEnumerable- ancestors) { return new HtmlString(AnalyticsHelper.TrackPageViewWithSectionPath(itemId, templateId, ancestors)); }
AnalyticsHelper:
public static class AnalyticsHelper
{
private static readonly ITrackerService TrackingService = (ITrackerService)ServiceLocator.ServiceProvider.GetService(typeof(ITrackerService));
public static string TrackPageViewWithSectionPath(ID itemId, ID templateId, IEnumerable- ancestors)
{
try
{
if (TrackingService != null)
{
var id = new ID(Constants.PageViewEventId);
TrackingService.TrackCurrentPageWithSectionEvent(id, itemId, templateId,ancestors);
}
}
catch (Exception ex)
{
Log.Error(ex.Message, ex, typeof(AnalyticsHelper));
}
return string.Empty;
}
}
TrackingService:
public virtual void TrackCurrentPageWithSectionEvent(ID pageEventId, ID itemId, ID templateId, IEnumerable- ancestors) { Assert.ArgumentNotNull(pageEventId, nameof(pageEventId)); if (!IsActive) { return; } var pageEventDefinition = Tracker.MarketingDefinitions.PageEvents[pageEventId]; Assert.IsNotNull(pageEventDefinition, $"Cannot find page event: {pageEventId}"); var pageData = new PageEventData(pageEventDefinition.Alias, pageEventDefinition.Id); var longId = ancestors != null ? string.Join("/", ancestors.Select(i=> i.ID)) : string.Empty; pageData.CustomValues.Add("LongId", longId); pageData.CustomValues.Add("TemplateId", templateId.ToGuid().ToString()); pageData.ItemId = itemId.ToGuid(); Tracker.Current.CurrentPage.Register(pageData); }
This code allows us to record the Page Event and LongId as its CustomValue where we store the IDs of all ancestors.
Processing collected data
Once the data is collected in the collection database, we need to make sure it is processed correctly and aggregated into custom tables that live in the Reporting database. For that, we need to implement the Dimension and Fact Key and Value classes, the processor to process the interactions and the following SQL tables:https://github.com/jcore/Sitecore.PopularItems/blob/master/ItemPath.sql
ItemPathProcessor is responsible for the processing of an interaction and storing the ItemPath data in the new SQL tables if there is an Item Path specified.
https://github.com/jcore/Sitecore.PopularItems/blob/master/ItemPathProcessor.cs
Below you can see the patch file that enables the ItemPathProcessor:
<configuration xmlns:env="http://www.sitecore.net/xmlconfig/env/" xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:role="http://www.sitecore.net/xmlconfig/role/" xmlns:search="http://www.sitecore.net/xmlconfig/search/" xmlns:set="http://www.sitecore.net/xmlconfig/set/">
<sitecore>
<pipelines>
<group groupname="analytics.aggregation" role:require="Standalone or Processing">
<pipelines>
<interactions>
<processor type="Analytics.InteractionProcessing.Processors.ItemPathProcessor, Analytics" />
</interactions>
</pipelines>
</group>
</pipelines>
</sitecore>
</configuration>
Dimension Key class
public class ItemPathDimensionKey : DictionaryKey
{
public Guid ItemId { get; }
public ItemPathDimensionKey(Guid itemId)
{
ItemId = itemId;
}
}
Dimension Value class
public class ItemPathDimensionValue : DictionaryValue
{
[CanBeNull]
public string ItemPath { get; }
[CanBeNull]
public string TemplateId { get; }
public ItemPathDimensionValue([CanBeNull] string itemPath, [CanBeNull] string templateId)
{
ItemPath = itemPath;
TemplateId = templateId;
}
}
Fact Key class
public class ItemPathFactKey : DictionaryKey
{
public DateTime Date { get; set; }
public Guid ItemId { get; }
public ItemPathFactKey(Guid itemId)
{
ItemId = itemId;
}
}
Fact Value class
public class ItemPathValue : DictionaryValue
{
public long Visits { get; set; }
public long Value { get; set; }
[NotNull]
internal static ItemPathValue Reduce([NotNull] ItemPathValue left, [NotNull] ItemPathValue right)
{
Debug.ArgumentNotNull(left, "left");
Debug.ArgumentNotNull(right, "right");
ItemPathValue result = new ItemPathValue
{
Visits = left.Visits + right.Visits,
Value = left.Value + right.Value
};
return result;
}
}
As you can see Dimension classes match the [ItemPath] SQL table columns and the Fact classes match the [Fact_PageItemPaths] one.
The first portion of the most popular feature is implemented - the data is collected.
Displaying the most popular items within a given section
If your security configuration permits to call the Reporting database directly you can do so. Make sure you put caching on every resultset you get to avoid putting a lot of traffic on the Reporting database. It is not designed to sustain large traffic.
To avoid connecting to the Reporting database directly we chose to implement a custom Solr index that would store the data from the reporting database. Sitecore CM would update the index on a schedule.
Here is the config patch file for the new index.
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:set="http://www.sitecore.net/xmlconfig/set/" xmlns:role="http://www.sitecore.net/xmlconfig/role/" xmlns:search="http://www.sitecore.net/xmlconfig/search/" xmlns:env="http://www.sitecore.net/xmlconfig/env/">
<sitecore>
<settings role:require="Standalone or Processing or ContentManagement or ContentDelivery">
<setting name="Reporting.IndexTimespanInMonths" set:value="1" />
<setting name="Reporting.TimespanInDays" set:value="30" />
<setting name="Reporting.IndexName" set:value="sitecore_reporting_index" />
</settings>
<contentSearch>
<indexConfigurations>
<indexUpdateStrategies>
<intervalAsyncReporting type="Analytics.Indexing.Strategies.IntervalAsynchronousStrategy, Analytics">
<param desc="database">core</param>
<param desc="interval">01:00:00</param>
<CheckForThreshold>false</CheckForThreshold>
</intervalAsyncReporting>
</indexUpdateStrategies>
<!-- If an index has no configuration specified, it will use the configuration below. The configuration is not merged if the index also has
configuration, it is either this configuration or the index configuration. -->
<popularItemsSolrIndexConfiguration type="Sitecore.ContentSearch.SolrProvider.SolrIndexConfiguration, Sitecore.ContentSearch.SolrProvider">
<!-- Should index Initialize() method be called as soon as the index is added or wait for an external trigger -->
<!-- For Solr Initialize() needs to be called after the IOC container has fired up -->
<initializeOnAdd>false</initializeOnAdd>
<!-- DEFAULT FIELD MAPPING
This field map allows you to take full control over how your data is stored in the index. This can affect the way data is queried, performance of searching and how data is retrieved and casted to a proper type in the API.
-->
<fieldMap type="Sitecore.ContentSearch.SolrProvider.SolrFieldMap, Sitecore.ContentSearch.SolrProvider">
<!-- This element must be first -->
<typeMatches hint="raw:AddTypeMatch">
<typeMatch typeName="guidCollection" type="System.Collections.Generic.List`1[System.Guid]" fieldNameFormat="{0}_sm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="textCollection" type="System.Collections.Generic.List`1[System.String]" fieldNameFormat="{0}_txm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="stringCollection" type="System.Collections.Generic.List`1[System.String]" fieldNameFormat="{0}_sm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="intCollection" type="System.Collections.Generic.List`1[System.Int32]" fieldNameFormat="{0}_im" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="guid" type="System.Guid" fieldNameFormat="{0}_s" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="id" type="Sitecore.Data.ID, Sitecore.Kernel" fieldNameFormat="{0}_s" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="shortid" type="Sitecore.Data.ShortID, Sitecore.Kernel" fieldNameFormat="{0}_s" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="string" type="System.String" fieldNameFormat="{0}_s" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="text" type="System.String" fieldNameFormat="{0}_t" cultureFormat="_{1}" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="int" type="System.Int32" fieldNameFormat="{0}_tl" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="bool" type="System.Boolean" fieldNameFormat="{0}_b" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="datetime" type="System.DateTime" fieldNameFormat="{0}_tdt" format="yyyy-MM-dd'T'HH:mm:ss.FFF'Z'" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="long" type="System.Int64" fieldNameFormat="{0}_tl" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="float" type="System.Single" fieldNameFormat="{0}_tf" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="double" type="System.Double" fieldNameFormat="{0}_td" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="stringArray" type="System.String[]" fieldNameFormat="{0}_sm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="intArray" type="System.Int32[]" fieldNameFormat="{0}_im" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="datetimeArray" type="System.DateTime[]" fieldNameFormat="{0}_dtm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="datetimeCollection" type="System.Collections.Generic.List`1[System.DateTime]" fieldNameFormat="{0}_dtm" multiValued="true" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
<typeMatch typeName="coordinate" type="Sitecore.ContentSearch.Data.Coordinate, Sitecore.ContentSearch.Data" fieldNameFormat="{0}_rpt" settingType="Sitecore.ContentSearch.SolrProvider.SolrSearchFieldConfiguration, Sitecore.ContentSearch.SolrProvider" />
</typeMatches>
<!-- This allows you to map a field name in Sitecore to the index and store it in the appropriate way -->
<!-- Add schema fields here to enable multi-language processing -->
<fieldNames hint="raw:AddFieldByFieldName">
</fieldNames>
<!-- FIELD TYPE MAPPING
This allows you to map a field type in Sitecore to a type in the index.
USAGE: When you add new field types to Sitecore, add the mappings here so they work through the Linq Layer
-->
<fieldTypes hint="raw:AddFieldByFieldTypeName">
</fieldTypes>
</fieldMap>
<documentOptions type="Sitecore.ContentSearch.SolrProvider.SolrDocumentBuilderOptions, Sitecore.ContentSearch.SolrProvider">
<!-- This flag will index all fields by default. This allows new fields in your templates to automatically be included into the index.
You have two choices :
1) Set this to true and place all the fields you would like to remove in the 'ExcludeField' list below.
2) Set to false and place all fields you would like to be indexed in the 'IncludeField' list below.
-->
<indexAllFields>true</indexAllFields>
<!-- GLOBALLY EXCLUDE TEMPLATES FROM BEING INDEXED
This setting allows you to exclude items that are based on specific templates from the index.
-->
<exclude hint="list:AddExcludedTemplate">
</exclude>
<!-- GLOBALLY INCLUDE TEMPLATES IN INDEX
This setting allows you to only include items that are based on specific templates in the index. You must specify all the
templates that you want to include, because template inheritance is not checked.
When you enable this setting, all the items that are based on other templates are excluded, regardless of whether the template
is specified in the ExcludeTemplate list or not.
-->
<!-- <include hint="list:AddIncludedTemplate">
<BucketFolderTemplateId>{ADB6CA4F-03EF-4F47-B9AC-9CE2BA53FF97}</BucketFolderTemplateId>
</include>-->
<!-- GLOBALLY INCLUDE FIELDS IN INDEX
This setting allows you to specify which fields to include in the index when the indexAllFields setting is set to false.
-->
<!--<include hint="list:AddIncludedField">
<fieldId>{8CDC337E-A112-42FB-BBB4-4143751E123F}</fieldId>
</include>-->
<!-- GLOBALLY EXCLUDE FIELDS FROM BEING INDEXED
This setting allows you to exclude fields from the index when the indexAllFields setting is set to true.
-->
<exclude hint="list:AddExcludedField">
</exclude>
<!-- REMOVE INBUILT SITECORE FIELDS
This allows you to store a field in different ways in the index. You may want to store a field as Analyzed and Not Analyze
-->
<fields hint="raw:AddExcludedSpecialField">
</fields>
<!-- COMPUTED INDEX FIELDS
This setting allows you to add fields to the index that contain values that are computed for the item that is being indexed.
You can specify the storageType and indextype for each computed index field in the <fieldMap><fieldNames> section.
-->
<fields hint="raw:AddComputedIndexField">
<field fieldName="id" returnType="id">Analytics.Indexing.Infrastructure.Fields.ItemId,Analytics</field>
<field fieldName="itemid" returnType="string">Analytics.Indexing.Infrastructure.Fields.ItemId,Analytics</field>
<field fieldName="itempath" returnType="stringCollection">Analytics.Indexing.Infrastructure.Fields.ItemPath,Analytics</field>
<field fieldName="visits" returnType="string">Analytics.Indexing.Infrastructure.Fields.Visits,Analytics</field>
<field fieldName="template" returnType="string">Analytics.Indexing.Infrastructure.Fields.Template,Analytics</field>
</fields>
</documentOptions>
<!-- MEDIA ITEM CONTENT EXTRACTOR FILE MAPPING
This map allows you to specify the extensions and mimetypes that we will pass through to the IFilters on your machine so they can be indexed.
We also allow you to include all files or exclude all files and leave it to the IFilters to control what is and is not indexed.
-->
<mediaIndexing hint="skip">
</mediaIndexing>
<!-- VIRTUAL FIELDS
Virtual fields can be used to translate a field query into a different query.
-->
<!--<virtualFields type="Sitecore.ContentSearch.VirtualFieldProcessorMap, Sitecore.ContentSearch">
<processors hint="raw:AddFromConfiguration">
<add fieldName="daterange" type="Sitecore.ContentSearch.VirtualFields.DateRangeFieldProcessor, Sitecore.ContentSearch" />
<add fieldName="_lastestversion" type="Sitecore.ContentSearch.VirtualFields.LatestVersionFieldProcessor, Sitecore.ContentSearch" />
<add fieldName="updateddaterange" type="Sitecore.ContentSearch.VirtualFields.UpdatedDateRangeFieldProcessor, Sitecore.ContentSearch" />
<add fieldName="_url" type="Sitecore.ContentSearch.VirtualFields.UniqueIdFieldProcessor, Sitecore.ContentSearch" />
<add fieldName="_fullpath" type="Sitecore.ContentSearch.SolrProvider.VirtualFields.FullPathFieldProcessor, Sitecore.ContentSearch.SolrProvider" />
<add fieldName="parsedcreatedby_s" type="Sitecore.ContentSearch.VirtualFields.CreatedByFieldProcessor, Sitecore.ContentSearch" />
</processors>
</virtualFields>-->
<!-- SITECORE FIELDTYPE MAP
This maps a field type by name to a Strongly Typed Implementation of the field type e.g. html maps to HTMLField
-->
<fieldReaders type="Sitecore.ContentSearch.FieldReaders.FieldReaderMap, Sitecore.ContentSearch">
<param desc="id">defaultFieldReaderMap</param>
<mapFieldByTypeName hint="raw:AddFieldReaderByFieldTypeName">
<fieldReader fieldTypeName="checkbox" fieldReaderType="Sitecore.ContentSearch.FieldReaders.CheckboxFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="date|datetime" fieldReaderType="Sitecore.ContentSearch.FieldReaders.DateFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="image" fieldReaderType="Sitecore.ContentSearch.FieldReaders.ImageFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="single-line text|multi-line text|text|memo" fieldReaderType="Sitecore.ContentSearch.FieldReaders.DefaultFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="integer" fieldReaderType="Sitecore.ContentSearch.FieldReaders.NumericFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="number" fieldReaderType="Sitecore.ContentSearch.FieldReaders.PrecisionNumericFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="html|rich text" fieldReaderType="Sitecore.ContentSearch.FieldReaders.RichTextFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="multilist with search|treelist with search" fieldReaderType="Sitecore.ContentSearch.FieldReaders.DelimitedListFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="checklist|multilist|treelist|treelistex|tree list" fieldReaderType="Sitecore.ContentSearch.FieldReaders.MultiListFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="icon|droplist|grouped droplist" fieldReaderType="Sitecore.ContentSearch.FieldReaders.DefaultFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="name lookup value list|name value list" fieldReaderType="Sitecore.ContentSearch.FieldReaders.NameValueListFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="droplink|droptree|grouped droplink|tree|reference" fieldReaderType="Sitecore.ContentSearch.FieldReaders.LookupFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="attachment|frame|rules|tracking|thumbnail" fieldReaderType="Sitecore.ContentSearch.FieldReaders.NullFieldReader, Sitecore.ContentSearch" />
<fieldReader fieldTypeName="file|security|server file|template field source|link" fieldReaderType="Sitecore.ContentSearch.FieldReaders.NullFieldReader, Sitecore.ContentSearch" />
</mapFieldByTypeName>
</fieldReaders>
<!-- INDEX FIELD STORAGE MAPPER
Maintains a collection of all the possible Convertors for the provider.
-->
<indexFieldStorageValueFormatter type="Sitecore.ContentSearch.SolrProvider.Converters.SolrIndexFieldStorageValueFormatter, Sitecore.ContentSearch.SolrProvider">
<converters hint="raw:AddConverter">
<converter handlesType="System.Guid" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldGuidValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.Data.ID, Sitecore.Kernel" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldIDValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.Data.ShortID, Sitecore.Kernel" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldShortIDValueConverter, Sitecore.ContentSearch" />
<converter handlesType="System.DateTime" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldUTCDateTimeValueConverter, Sitecore.ContentSearch" />
<converter handlesType="System.DateTimeOffset" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldDateTimeOffsetValueConverter, Sitecore.ContentSearch" />
<converter handlesType="System.TimeSpan" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldTimeSpanValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.ContentSearch.SitecoreItemId, Sitecore.ContentSearch" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldSitecoreItemIDValueConvertor, Sitecore.ContentSearch">
<param type="Sitecore.ContentSearch.Converters.IndexFieldIDValueConverter, Sitecore.ContentSearch" />
</converter>
<converter handlesType="Sitecore.ContentSearch.SitecoreItemUniqueId, Sitecore.ContentSearch" typeConverter="Sitecore.ContentSearch.SolrProvider.Converters.SolrIndexFieldSitecoreItemUniqueIDValueConverter, Sitecore.ContentSearch.SolrProvider">
<param type="Sitecore.ContentSearch.Converters.IndexFieldItemUriValueConverter, Sitecore.ContentSearch" />
</converter>
<converter handlesType="Sitecore.Data.ItemUri, Sitecore.Kernel" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldItemUriValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.Globalization.Language, Sitecore.Kernel" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldLanguageValueConverter, Sitecore.ContentSearch" />
<converter handlesType="System.Globalization.CultureInfo" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldCultureInfoValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.Data.Version, Sitecore.Kernel" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldVersionValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.Data.Database, Sitecore.Kernel" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldDatabaseValueConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.ContentSearch.IIndexableId, Sitecore.ContentSearch" typeConverter="Sitecore.ContentSearch.Converters.IndexableIdConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.ContentSearch.IIndexableUniqueId, Sitecore.ContentSearch" typeConverter="Sitecore.ContentSearch.Converters.IndexableUniqueIdConverter, Sitecore.ContentSearch" />
<converter handlesType="Sitecore.ContentSearch.Data.Coordinate, Sitecore.ContentSearch.Data" typeConverter="Sitecore.ContentSearch.Converters.IndexFieldCoordinateValueConverter, Sitecore.ContentSearch" />
</converters>
</indexFieldStorageValueFormatter>
<!-- INDEX PROPERTY TO DOCUMENT MAPPER
Maintains a collection of all the possible Convertors for the provider.
-->
<indexDocumentPropertyMapper type="Sitecore.ContentSearch.SolrProvider.Mapping.SolrDocumentPropertyMapper, Sitecore.ContentSearch.SolrProvider">
<!-- OBJECT FACTORY
Constructs search result objects based on the type that is passed in .GetQueryable<T>() and the rules defined in this section.
-->
<objectFactory type="Sitecore.ContentSearch.DefaultDocumentMapperObjectFactory, Sitecore.ContentSearch">
<!-- OBJECT FACTORY RULES
Examples:
<rules hint="list:AddRule">
Rule that applies to items with template "Sample Item":
<rule fieldName="template" comparison="Equal" value="{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}" valueType="System.Guid, mscorlib"
creationType="MySearchTypes.SampleItemResultItem, MySearchTypes"
baseType="MySearchTypes.IMySearchResultItem, MySearchTypes"
type="Sitecore.ContentSearch.DefaultDocumentMapperFactorySimpleRule, Sitecore.ContentSearch">
<param desc="fieldName">$(fieldName)</param>
<param desc="comparison">$(comparison)</param>
<param desc="value">$(value)</param>
<param desc="type">$(valueType)</param>
<param desc="creationType">$(creationType)</param>
<param desc="baseType">$(baseType)</param>
</rule>
Rule that applies to items with template "Sample Item" AND has the title "Sample Item":
<rule type="Sitecore.ContentSearch.DefaultDocumentMapperFactoryRule, Sitecore.ContentSearch"
creationType="MySearchTypes.SampleItemResultItem, MySearchTypes"
baseType="MySearchTypes.IMySearchResultItem, MySearchTypes">
<param desc="creationType">$(creationType)</param>
<param desc="baseType">$(baseType)</param>
<fieldComparisons hint="list:AddFieldComparison">
<fieldComparison fieldName="template" comparison="Equal" value="{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}" valueType="System.Guid, mscorlib" type="Sitecore.ContentSearch.DefaultDocumentMapperFactoryRuleFieldComparison, Sitecore.ContentSearch">
<param desc="fieldName">$(fieldName)</param>
<param desc="comparison">$(comparison)</param>
<param desc="value">$(value)</param>
<param desc="type">$(valueType)</param>
</fieldComparison>
<fieldComparison fieldName="title" comparison="Equal" value="Sample Item" valueType="System.String, mscorlib" type="Sitecore.ContentSearch.DefaultDocumentMapperFactoryRuleFieldComparison, Sitecore.ContentSearch">
<param desc="fieldName">$(fieldName)</param>
<param desc="comparison">$(comparison)</param>
<param desc="value">$(value)</param>
<param desc="type">$(valueType)</param>
</fieldComparison>
</fieldComparisons>
</rule>
</rules>
-->
</objectFactory>
</indexDocumentPropertyMapper>
<!-- DOCUMENT BUILDER
Allows you to override the document builder. The document builder class processes all the fields in the Sitecore items and prepares
the data for storage in the index.
You can override the document builder to modify how the data is prepared, and to apply any additional logic that you may require.
-->
<documentBuilderType>Sitecore.ContentSearch.SolrProvider.SolrDocumentBuilder, Sitecore.ContentSearch.SolrProvider</documentBuilderType>
<defaultSearchSecurityOption ref="contentSearch/indexConfigurations/defaultSearchSecurityOption" />
</popularItemsSolrIndexConfiguration>
<solrHttpWebRequestFactory type="HttpWebAdapters.HttpWebRequestFactory, SolrNet" />
</indexConfigurations>
<configuration type="Sitecore.ContentSearch.ContentSearchConfiguration, Sitecore.ContentSearch">
<indexes hint="list:AddIndex">
<index id="sitecore_reporting_index" type="Sitecore.ContentSearch.SolrProvider.SolrSearchIndex, Sitecore.ContentSearch.SolrProvider">
<param desc="name">$(id)</param>
<param desc="core">$(id)</param>
<param desc="propertyStore" ref="contentSearch/indexConfigurations/databasePropertyStore" param1="$(id)" />
<configuration ref="contentSearch/indexConfigurations/popularItemsSolrIndexConfiguration" />
<strategies hint="list:AddStrategy" role:require="Standalone or Processing or ContentManagement" >
<strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/intervalAsyncReporting" />
</strategies>
<strategies hint="list:AddStrategy" role:require="ContentDelivery" >
<strategy ref="contentSearch/indexConfigurations/indexUpdateStrategies/manual" />
</strategies>
<locations hint="list:AddCrawler">
<crawler type="Analytics.Indexing.Crawlers.PageViewsCrawler, Analytics">
</crawler>
</locations>
<enableItemLanguageFallback>false</enableItemLanguageFallback>
<enableFieldLanguageFallback>false</enableFieldLanguageFallback>
</index>
</indexes>
</configuration>
</contentSearch>
</sitecore>
</configuration>
Crawler
public class PageViewsCrawler : FlatDataCrawler<PageViewsWithPathIndexableEntity>
{
private readonly IPageViewsWithPathService _pageViewService;
public PageViewsCrawler()
{
_pageViewService = (IPageViewsWithPathService)ServiceLocator.ServiceProvider.GetService(typeof(IPageViewsWithPathService));
}
public PageViewsCrawler(IPageViewsWithPathService productService)
{
_pageViewService = productService;
}
protected override IEnumerable<PageViewsWithPathIndexableEntity> GetItemsToIndex()
{
var timeSpanInHours = Settings.GetIntSetting("Reporting.IndexTimespanInMonths", 1);
return _pageViewService.GetRecordsFromReportingDatabase(timeSpanInHours);
}
protected override PageViewsWithPathIndexableEntity GetIndexable(IIndexableUniqueId indexableUniqueId)
{
return _pageViewService.GetRecordFromReportingDatabase((Guid)indexableUniqueId.Value);
}
protected override PageViewsWithPathIndexableEntity GetIndexableAndCheckDeletes(IIndexableUniqueId indexableUniqueId)
{
return _pageViewService.GetRecordFromReportingDatabase((Guid)indexableUniqueId.Value);
}
protected override bool IndexUpdateNeedDelete(PageViewsWithPathIndexableEntity indexable)
{
return false;
}
protected override IEnumerable<IIndexableUniqueId> GetIndexablesToUpdateOnDelete(IIndexableUniqueId indexableUniqueId)
{
return new List<IIndexableUniqueId> { indexableUniqueId };
}
}
Entities
public class PageViewIndexableField : IIndexableDataField
{
public PageViewIndexableField(string name, object value)
{
Value = value;
Name = name;
Id = name;
TypeKey = FieldType.ToString().ToLowerInvariant();
}
public string Name { get; }
public string TypeKey { get; }
public object Value { get; }
public object Id { get; }
public Type FieldType => Value.GetType();
}
public class PageViewsWithPathIndexableEntity : IIndexable
{
private readonly Dictionary _fields;
public PageViewsWithPathIndexableEntity(ID itemId, string path, Dictionary fields, long visits)
{
AbsolutePath = path;
DataSource = "reporting_pageviewswithpath";
Culture = CultureInfo.CurrentCulture;
Fields = LoadFields(fields);
_fields = fields;
ItemId = itemId;
Visits = visits;
}
private IEnumerable LoadFields(Dictionary fields)
{
if (fields == null)
{
return null;
}
var result = new List();
foreach (var key in fields.Keys)
{
result.Add(new PageViewIndexableField(key, fields[key]));
}
return result;
}
public void LoadAllFields()
{
}
public IIndexableDataField GetFieldById(object fieldId)
{
if (Fields != null && Fields.Any(f => f.Id == fieldId))
{
return Fields.FirstOrDefault(f => f.Id == fieldId);
}
return null;
}
public IIndexableDataField GetFieldByName(string fieldName)
{
if (Fields != null && Fields.Any(f => f.Name == fieldName))
{
return Fields.FirstOrDefault(f => f.Name == fieldName);
}
return null;
}
public IIndexableId Id => new IndexableId(ItemId);
public IIndexableUniqueId UniqueId => new IndexableUniqueId(ItemId);
public string DataSource { get; }
public string AbsolutePath { get; }
public CultureInfo Culture { get; }
public IEnumerable Fields { get; }
public ID TemplateId
{
get
{
if (_fields != null && _fields.ContainsKey("templateId"))
{
return (ID)_fields["templateId"];
}
return ID.Null;
}
}
public ID ItemId { get; }
public long Visits { get; }
public DateTime Date { get; }
public IEnumerable ItemPath
{
get
{
return !string.IsNullOrWhiteSpace(AbsolutePath) ? AbsolutePath.Split('/').Select(i => new ID(i)) : null;
}
}
public ID StringToId()
{
return new ID(new Guid(MD5.Create().ComputeHash(Encoding.Default.GetBytes(string.Concat(ItemId, AbsolutePath, Visits, TemplateId)))));
}
}
Indexing Strategy
[DataContract]
public class IntervalAsynchronousStrategy : BaseAsynchronousStrategy
{
private AlarmClock alarmClock;
private TimeSpan updateInterval;
public IntervalAsynchronousStrategy(string database, string interval = null)
: base(database)
{
this.updateInterval = DateUtil.ParseTimeSpan(interval, Settings.Indexing.UpdateInterval, CultureInfo.CurrentCulture);
this.alarmClock = new AlarmClock(this.updateInterval);
}
public override void Initialize(ISearchIndex searchIndex)
{
var indexName = Settings.GetSetting("Reporting.IndexName", "sitecore_reporting_index");
searchIndex = ContentSearchManager.GetIndex(indexName);
this.Index = searchIndex;
base.Initialize(searchIndex);
this.alarmClock.Ring += (EventHandler)((sender, args) => this.Handle());
}
public override void Run()
{
if (IndexCustodian.IsIndexingPaused(this.Index))
CrawlingLog.Log.Warn(string.Format("[Index={0}] AHA IntervalAsynchronousUpdateStrategy triggered but muted. Indexing is paused.", (object)this.Index.Name), (Exception)null);
else if (IndexCustodian.IsRebuilding(this.Index))
CrawlingLog.Log.Warn(string.Format("[Index={0}] AHA IntervalAsynchronousUpdateStrategy triggered but muted. Index is being built at the moment.", (object)this.Index.Name), (Exception)null);
else
{
CrawlingLog.Log.Info(string.Format("[Index={0}] AHA IntervalAsynchronousUpdateStrategy triggered.", (object)this.Index.Name), (Exception)null);
long? updatedTimestamp = this.Index.Summary.LastUpdatedTimestamp;
this.Run(this.Index);
}
}
protected virtual void Run(ISearchIndex index)
{
CrawlingLog.Log.Debug(string.Format("[Index={0}] {1} executing.", (object)index.Name, (object)this.GetType().Name), (Exception)null);
long lastUpdatedStamp = index.Summary.LastUpdatedTimestamp ?? 0L;
if (this.RaiseRemoteEvents)
IndexCustodian.FullRebuild(index, true).Wait();
else
IndexCustodian.FullRebuildRemote(index, true).Wait();
}
}
Search Result Item
public class PageViewSearchResultItem : ISearchResult
{
private readonly Dictionary _fields = new Dictionary();
public string Name { get; set; }
public string Content { get; set; }
[IndexField("_datasource")]
public string Datasource { get; set; }
[IndexField("templateid_s")]
public virtual ID TemplateId { get; set; }
[IndexField("itempath_sm")]
public IEnumerable ItemPath { get; set; }
[IndexField("itemid_s")]
public ID ItemId { get; set; }
[IndexField("visits_tl")]
public long Visits { get; set; }
public virtual string this[string key]
{
get
{
if (key == null)
throw new ArgumentNullException(nameof(key));
return _fields[key.ToLowerInvariant()].ToString();
}
set
{
if (key == null)
throw new ArgumentNullException(nameof(key));
_fields[key.ToLowerInvariant()] = value;
}
}
public virtual object this[ObjectIndexerKey key]
{
get
{
if (key == null)
throw new ArgumentNullException(nameof(key));
return _fields[key.ToString().ToLowerInvariant()];
}
set
{
if (key == null)
throw new ArgumentNullException(nameof(key));
_fields[key.ToString().ToLowerInvariant()] = value;
}
}
}
Most Popular item Service
After all the data is collected and the index is built, we can start querying the index retrieving the values for most popular articles. Below is the code for the method that returns the IEnumerable of item IDs.
public IEnumerableGetMostPopularItemsForSection(IEnumerable templateIds, ID parentId, int count) { try { var cacheKey = $"{count}-{parentId}-{string.Join("-", templateIds)}"; var cachedItemIds = (IEnumerable )PageViewCache.CacheWrapper.Get(cacheKey); if (cachedItemIds == null) { var indexName = Settings.GetSetting("Reporting.IndexName", "sitecore_reporting_index"); using (var context = ContentSearchManager.GetIndex(indexName).CreateSearchContext()) { var results = context.GetQueryable ().Where(i => i.ItemPath.Contains(parentId) && templateIds.Contains(i.TemplateId) && i.Datasource == "reporting_pageviewswithpath").OrderByDescending(i=>i.Visits).Take(count).ToList(); if (results != null && results.Count > 0) { cachedItemIds = results.Select(i => i.ItemId); PageViewCache.CacheWrapper.Set(cacheKey, cachedItemIds); return cachedItemIds; } } } return cachedItemIds; } catch (Exception ex) { LogManager.Error(ex.Message, ex, this); } return null; }
Conclusion
This solution offers a food for thought when it comes to displaying content from the reporting database. We have solved the issue of connecting to Reporting database from CDs that very often live in completely different network group that CM. Also often querying Solr index is faster than querying SQL.
