Walkthrough: Using the interactions pipeline to aggregate and store reporting data
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.
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:
RequestResponsenamespace 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:
RequestResponsenamespace 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
andUserAgentNameValue
classes:RequestResponsenamespace 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
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:
RequestResponseCREATE 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:
RequestResponsenamespace 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; } } }
NoteThe
Reduce()
method must be implemented so that two fact values can be added together. -
Create fact key class:
RequestResponsenamespace 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
andUserAgentValue
classes:RequestResponsenamespace 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:
RequestResponseCREATE 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;
}
}
}
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>