---
title: "Walkthrough: Using the interactions batch pipeline to aggregate and store reporting data"
slug: "walkthrough--using-the-interactions-batch-pipeline-to-aggregate-and-store-reporting-data"
abstract: "How to create and register an interaction aggregation processor."
---
# Walkthrough: Using the interactions batch pipeline to aggregate and store reporting data

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.

<details>
<summary><h2>Create dimension class and table</h2></summary>

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:

    ```csharp
    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:

    ```csharp
    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:

    ```csharp
    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:

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

    GO
    ```

</details>

<details>
<summary><h2>Create fact class and table</h2></summary>

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:

    ```csharp
    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:

    ```csharp
    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:

    ```csharp
    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:

    ```text
    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);
    ```

</details>

<details>
<summary><h2>Create a batch interaction aggregation processor class</h2></summary>

Create an class with the following method:

```csharp
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.

```csharp
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](https://doc.sitecore.com/xp/en/developers/105/sitecore-experience-platform/accessing-facets-and-services.html "Accessing facets and services") such as the xConnect Client API in the context of an interaction aggregation processor.

</details>

<details>
<summary><h2>Add processor to configuration</h2></summary>

Patch the processor into configuration as shown:

```xml
<?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>
```

</details>