Slow start up time of Sitecore 9 in Azure

Not that long ago we were launching a new Sitecore 9 site in Azure and ran into a big issue with start up time. It was taking over 20 minutes for the application to start from an application pool recycle or a hard stop/start. After countless hours of debugging and running traces, we figured it out. So if you are running into a similar situation, maybe this post will help.

MVC 5

It turns out MVC 5 is mostly to blame for this slowness. There is a very good article written by David De Sloovere that you can read at http://blog.deltacode.be/2017/01/08/fix-slow-startup-of-asp-net-mvc-5-on-azure-app-services/. It covers all configurations and parameters that are needed to make your build pre-compile the views.

In case of Sitecore application, we ended up compiling all views including the Sitecore ones. We have several modules installed in our solution, so the views from those had to be pre-compiled as well. We used aspnet_compiler.exe tool to do that.

Technically, it is really good when you pre-compile the views even for catching the missing references or other problems in the Views before the code gets to target environment. However precompiling the views in local environment will take much longer that a developer would want to work efficiently.

Application Initialization

Another thing that was discovered that might not affact the start up time, but it does affect the moment when application is considered fully loaded. 

<system.webServer>
    <applicationInitialization doAppInitAfterRestart="true">         
        <add initializationPage="/sitecore/service/heartbeat.aspx" />
        <add initializationPage="/"  />  <!-- home page must load --> 
        <!-- put here all urls that must be loaded before the application is considered 

 to be fully loaded: landing pages, etc. -->
    </applicationInitialization>

This is more important for the Auto-scaling in Azure.

Sitecore PxM "Table of Contents" renderers

I just finished a PxM project where one of the requirements was to implement a "Table of Contents" based on the article heading tags. The content of the article comes from data sources of various renderings that assigned to an item presentation settings. The Sitecore data source structure that we had to work with have a separation between section titles and the body, so the task at hand was to parse HTML and find H2 and H3 tags that the "Table of Contents" can be built on.

Here is a screenshot of the Table of Contents in PDF:

To implement this functionality two renderers had to be implemented:

• Renderer for "Table of Contents" headings;
• A renderer that would inject the InserVar XML node into the heading ParagraphStyle nodes that would allow for InDesign to determine the page number where a particular heading is placed.

Table of Contents headings

The Table of Contents page displays the list of al H2 and H3 headings from the Article Content with the page number where those headings render in the content section. 

The PxM project structure for TOC looks like this:

The TOC page is of Flow type. It has a TextFrame that holds a Repeater that loops through all renderings in item Presentation settings that live in specified in "Placeholder" parameter placeholder and are based on the template name that is specified in "AllowedRenderingNames".

The "H Tags" renderer is responsible for parsing HTML from each "TextModule" rendering data source, extracting the value of the H2 or H3 tags and rendering a ParagraphStyle XML element with appropriate Style.

In Parameters section of this renderer item the following parameters are defined:

• HTags – the list of HTML tags to search for to produce the Table of Contents
• IndexName – the name of the index for the variable list that is used for the Table of Contents
• ReplaceVar – the substring that would be replaced with the index element order number.
• HParagraphStyles – the list of ParagraphStyles that should be used for the Table of Contents entries. This parameter goes together with the HTags one. In the example above the H2 tag value would render in PDF with “style 1 Level 1” ParagraphStyle, and H3 with “style 1 Level 2”.

The XML that this renderer produces look like this:

Dynamic Content Renderer with TOC

The Dynamic Content Renderer with TOC is a part of the following PxM project structure:

A similar Repeater creates a loop through the same renderings as the previous renderer. However, in this case, it is not extracting the value of the H tags, but injects an InserVar XML element with the index value.

XML output for this section looks like this:

Conclusion

In combination, these two renderers allow for the "Table of Contents" with page numbers to be rendered in PDF.

Source Code:

You can find the code for these renderers at:

https://github.com/jcore/PxM.TableOfContents 

Most popular articles with a section feature

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.

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 IEnumerable GetMostPopularItemsForSection(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.