Walkthrough: Use the interactions batch pipeline to aggregate and store reporting data

Abstract

How to create and register an interaction aggregation processor.

This topic demonstrates how to create a batch interaction aggregation processor that groups total number of visits and total value of all visits against the user agent of the interaction.

Note

The processor logic and configuration differs slightly from a classic interaction aggregation processor. We recommend that you upgrade your classic processors to batch processors.

You will create:

  • A dimension table, which is a reference table that stores a list of unique user agents with IDs.

  • A fact table, which stores user agent IDs against total visit and value generated by that user agent.

  • Fact and dimension classes, which model the data in the fact and dimension table.

  • A batch interaction aggregation processor, which uses the fact and dimension classes to populate the tables in the reporting database.

The dimension table stores a list of user agent strings. Each user agent has a unique ID. The dimension table is referenced by the fact table, which stores the total number of visits and total value of all visits against a user agent ID.

Creating a dimension class

Every dimension has a key and a value class that is specific to that dimension - in this case, they are UserAgentNameKey (example key: -1743424285) and UserAgentNameValue (example value: Chrome).

  1. Create a dimension key class:

    namespace Documentation.Examples
    {
        using Sitecore;
        using Sitecore.Analytics.Aggregation.Data.Model;
        using Sitecore.Analytics.Core;
    
        public class UserAgentNameKey : DictionaryKey
        {
            private readonly Hash32 userAgentNameId;
    
            public Hash32 UserAgentNameId
            {
                get
                {
                    return this.userAgentNameId;
                }
            }
            public UserAgentNameKey(Hash32 userAgentNameId)
            {
                this.userAgentNameId = userAgentNameId;
            }
        }
    }
    
  2. Create a dimension value class:

    namespace Documentation.Examples
    {
        using Sitecore;
        using Sitecore.Analytics.Aggregation.Data.Model;
        using Sitecore.Analytics.Core;
    
        public class UserAgentNameValue : DictionaryValue
        {
            private readonly string userAgentName;
    
            [CanBeNull]
            public string UserAgentName
            {
                get
                {
                    return this.userAgentName;
                }
            }
    
            public UserAgentNameValue([CanBeNull] string userAgentName)
            {
                this.userAgentName = userAgentName;
            }
        }
    }
    
  3. Create a dimension class that uses the UserAgentNameKey and UserAgentNameValue classes:

    namespace Documentation.Examples
    {
        using Sitecore;
        using Sitecore.Analytics.Aggregation.Data.Model;
        using Sitecore.Analytics.Core;
    
        public class UserAgentName : Dimension<UserAgentNameKey, UserAgentNameValue>
        {
            public UserAgentName()
            {
            }
    
            public Hash32 Add([CanBeNull] string userAgentName)
            {
                string userAgentNameValue = (userAgentName ?? string.Empty);
                Hash32 result = Hash32.Compute(userAgentNameValue);
    
                UserAgentNameKey key = new UserAgentNameKey(result);
                UserAgentNameValue value = new UserAgentNameValue(userAgentNameValue);
    
                this.Add(key, value);
    
                return result;
            }
        }
    }
    

Creating a dimension table

Important

A dimension table should have the same name as the class. For example, the UserAgentName class should have a matching dbo.UserAgentName table with columns matching the class properties.

  • In the reporting database, create the following table:

    CREATE TABLE [dbo].[UserAgentName] (
            [UserAgentNameId] INT            NOT NULL,
            [UserAgentName]   NVARCHAR (1000) NOT NULL,
            CONSTRAINT [PK_UserAgentNames] PRIMARY KEY CLUSTERED ([UserAgentNameId] ASC)
    );
    
    GO
    

The dimension table stores a list of user agent IDs against the total number of visits and total value of visits generated by that user agent.

Creating a fact class

  1. Create a fact value class:

    namespace Documentation.Examples
    {
        using System;
        using Sitecore;
        using Sitecore.Analytics.Aggregation.Data.Model;
        using Sitecore.Analytics.Core;
        using Sitecore.Diagnostics;
    
        public class UserAgentValue : DictionaryValue
        {
            public long Visits { get; set; }
            public long Value { get; set; }
    
            //
            //   Reduces two <see cref="UserAgentValue"/> objects into one by
            //   adding corresponding members together.
            //
            [NotNull]
            internal static UserAgentValue Reduce([NotNull] UserAgentValue left, [NotNull] UserAgentValue right)
            {
                Debug.ArgumentNotNull(left, "left");
                Debug.ArgumentNotNull(right, "right");
                UserAgentValue result = new UserAgentValue();
                result.Visits = (left.Visits + right.Visits);
                result.Value = (left.Value + right.Value);
                return result;
            }
        }
    }
    

    Note

    The Reduce() method must be implemented so that two fact values can be added together.

  2. Create fact key class:

    namespace Documentation.Examples
    {
        using System;
        using Sitecore;
        using Sitecore.Analytics.Aggregation.Data.Model;
        using Sitecore.Analytics.Core;
        using Sitecore.Diagnostics;
    
        public class UserAgentFactKey : DictionaryKey
        {
            public DateTime Date { get; set; }
            public Hash32 Checksum { get; set; }
            public Hash32 UserAgentNameId { get; private set; }
    
            public UserAgentFactKey(Hash32 userAgentHash32)
            {
                UserAgentNameId = userAgentHash32;
            }
        }
    }
    
  3. Create a fact class that uses the UserAgentFactKey and UserAgentValue classes:

    namespace Documentation.Examples
    {
        using System;
        using Sitecore;
        using Sitecore.Analytics.Aggregation.Data.Model;
        using Sitecore.Analytics.Core;
        using Sitecore.Diagnostics;
    
        public class UserAgentFact : Fact<UserAgentFactKey, UserAgentValue>
        {
            public UserAgentFact() : base(UserAgentValue.Reduce)
            {
            }
        }
    }
    

Creating a fact table

A fact table should have the same name as the class, prefixed with Fact_. For example, the UserAgentFact fact class would have a matching dbo.Fact_UserAgentFact table with columns matching the class properties.

  • In the reporting database, create the following table:

    CREATE TABLE [dbo].[Fact_UserAgentFact] (
            [Date]            SMALLDATETIME    NOT NULL,
            [Checksum]        INT              NOT NULL,
            [UserAgentNameId] INT              NOT NULL,
            [Visits]          BIGINT           NOT NULL,
            [Value]           BIGINT           NOT NULL,
            CONSTRAINT [FK_Fact_UserAgentFact_UserAgentName] FOREIGN KEY ([UserAgentNameId]) REFERENCES [dbo].[UserAgentName] ([UserAgentNameId]),
    );
    
    
    GO
    ALTER TABLE [dbo].[Fact_UserAgentFact] NOCHECK CONSTRAINT [FK_Fact_UserAgentFact_UserAgentName];
    
    
    GO
    CREATE NONCLUSTERED INDEX [IX_ByDateAndUserAgent]
            ON [dbo].[Fact_UserAgentFact]([Date] ASC, [UserAgentNameId] ASC)
            INCLUDE([Visits], [Value]);
    
    
    GO
    CREATE CLUSTERED INDEX [IX_ByDate]
            ON [dbo].[Fact_UserAgentFact]([Date] ASC, [Checksum] ASC);
    
    
    GO
    CREATE NONCLUSTERED INDEX [IX_Fact_UserAgentFact_UserAgent]
            ON [dbo].[Fact_UserAgentFact]([UserAgentNameId] ASC);
    

Create an class with the following method:

public void Process(InteractionBatchAggregationPipelineArgs args) { }

Note

Batch processors do not require a base class. Classic processors do require a base class.

The following example uses the fact and dimension classes created in previous steps. The processor will automatically populate the fact and dimension tables.

namespace Documentation.Examples
{
    using System;
    using Sitecore;
    using Sitecore.Analytics.Aggregation;
    using Sitecore.Analytics.Aggregation.Data.Model;
    using Sitecore.Analytics.Aggregation.Pipeline;
    using Sitecore.Analytics.Core;
    using Sitecore.Diagnostics;

    public class UserAgentProcessor
    {
        public void Process(InteractionBatchAggregationPipelineArgs args)
        {
            foreach (var context in args.Contexts)
            {
                var useragent = context.Interaction.UserAgent;

                if (string.IsNullOrEmpty(useragent))
                {
                    return;
                }

                //
                // Create the fact key
                //
                Hash32 userAgentId = UpdateUserAgentDimension(useragent, context.Results);
                UserAgentFactKey key = new UserAgentFactKey(userAgentId);
                key.Date = args.DateTimeStrategy.Translate(context.Interaction.StartDateTime);
                key.Checksum = Hash32.Compute(userAgentId);

                //
                // Create the fact value.
                //
                UserAgentValue value = new UserAgentValue();
                value.Visits = 1;
                value.Value = context.Interaction.EngagementValue;

                //
                // Emit fact.
                //
                UserAgentFact facts = context.Results.GetFact<UserAgentFact>();
                facts.Emit(key, value);
            }
        }
    }
}

Note

You can access facets and services such as the xConnect Client API in the context of an interaction aggregation processor.

Patch the processor into configuration as shown:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:role="http://www.sitecore.net/xmlconfig/role/">
<sitecore role:require="Standalone or Processing">
    <pipelines>
        <group groupName="analytics.aggregation">
            <pipelines>
                <interactionsBatch>
                    <processor type="Documentation.Examples.UserAgentProcessor, Documentation.Examples" />
                </interactionsBatch>
            </pipelines>
        </group>
    </pipelines>
</sitecore>
</configuration>