Walkthrough: Using the interactions pipeline to aggregate and store reporting data

Version:

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

Note

We recommend that you upgrade your classic interaction processors to batch interaction processors to improve performance.

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.
An interaction aggregation processor, which uses the fact and dimension classes to populate the tables in the reporting database.

Create dimension class and table

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).

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;
        }
    }
}

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;
        }
    }
}

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

Create fact class and table

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

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.

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;
        }
    }
}

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 interaction aggregation processor class

Create an class that inherits InteractionAggregationPipelineProcessor. 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 : InteractionAggregationPipelineProcessor
    {
        protected override void OnProcess(InteractionAggregationPipelineArgs args)
        {
            var useragent = args.Context.Interaction.UserAgent;

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

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

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

            //
            // Emit fact.
            //
            UserAgentFact facts = args.Context.Results.GetFact<UserAgentFact>();
            facts.Emit(key, value);
        }
        protected static Hash32 UpdateUserAgentDimension(string useragent, IInteractionAggregationResults results)
        {
            Assert.ArgumentNotNull(useragent, nameof(useragent));
            Assert.ArgumentNotNull(results, nameof(results));

            UserAgentName dimension = results.GetDimension<UserAgentName>();
            Hash32 result = dimension.Add(useragent);

            return result;
        }
    }
}

Note

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

Add processor to configuration

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>
        <interactions>
            <processor type="Documentation.Examples.UserAgentProcessor, Documentation.Examples" />
        </interactions>
        </pipelines>
    </group>
    </pipelines>
</sitecore>
</configuration>

If you have suggestions for improving this article, let us know!