Custom Table To Store Sitecore Analytics data in Reporting Database Part 2

Hello everyone, this is continuation to previous blog, Part 1 of this series where we have seen, steps to create custom table in reporting database. Link for the same is here Part 1

This part, we will be concentrating more one the code logic required to update data in the custom table.

Let's start with the code part without any delay.

We will create 4 classes respectively:

• CampaignVisitKey.cs : Which will have the key on which the table entry will be done
• CampaignVisitValue.cs : The value for our columns which will be inserted
• CampaignVisit.cs : This class is inheriting from Fact table. It merges the current datatable of Campaign visits to existing table
• CampaignVisitProcessor.cs : This processor executes the actual logic of reading and assigning the values to our model.

Let's see the code for all the classes one by one

CampaignVisitKey.cs

       
using Sitecore.Analytics.Aggregation.Data.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ShrikantTest.Feature.Analytics
{
    public class CampaignVisitKey : DictionaryKey
    {
        public Guid ContactId { get; set; }
    }
}
	   
 

CampaignVisitValue.cs

       
using Sitecore.Analytics.Aggregation.Data.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ShrikantTest.Feature.Analytics
{
    public class CampaignVisitValue : DictionaryValue
    {
        public Guid CampaignId { get; set; }
        public DateTime Date { get; set; }
        internal static CampaignVisitValue Reduce(CampaignVisitValue left, CampaignVisitValue right)
        {
            return right;
        }
    }
}
	   
 

CampaignVisit.cs

       
using Sitecore.Analytics.Aggregation.Data.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace ShrikantTest.Feature.Analytics
{
    public class CampaignVisit : Fact<CampaignVisitKey, CampaignVisitValue>
    {
        public CampaignVisit() : base (CampaignVisitValue.Reduce)
        {
        }
        public override void Merge(Fact other)
        {
            base.Merge(other);
        }
        public override void Merge(ITable table)
        {
            base.Merge(table);
        }
        public override DataTable ToTable()
        {
            var table = base.ToTable();
            return table;
        }
    }
}
	   
 

CampaignVisitProcessor.cs

       
using Sitecore.Analytics.Aggregation.Pipeline;

namespace ShrikantTest.Feature.Analytics
{
    public class CampaignVisitProcessor : InteractionAggregationPipelineProcessor
    {
        protected override void OnProcess(InteractionAggregationPipelineArgs args)
        {
            var fact = args.Context.Results.GetFact<CampaignVisit>();
            if (args.Context != null && args.Context.Interaction != null && args.Context.Interaction.CampaignId != null && args.Context.Interaction.CampaignId.HasValue)
            {
                var key = new CampaignVisitKey
                {
                    ContactId = args.Context.Contact.Id.Value,
                };
                var value = new CampaignVisitValue
                {
                    CampaignId = args.Context.Interaction.CampaignId.Value,
                    Date = args.DateTimeStrategy.Translate(args.Context.Interaction.StartDateTime)
                };
                fact.Emit(key, value);
            }
        }
    }
}
	   
 

After adding and impelmenting all the stpes we need to patch this to our config. We will add all processors, custom table entries, store procedure entry in the web config.

Patch config Name: Feature.Analytics.Config

       
<?xml version="1.0" encoding="utf-8"?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <aggregation>
      <routines>
        <ExecRoutineStatementBuilder>
          <mappings hint="list:AddMapping">
			<SqlMappingEntity4 type="Sitecore.Analytics.Aggregation.SqlMappingEntity, Sitecore.Analytics.Sql">
              <Table>Fact_CampaignVisit</Table>
              <Routine>Add_CampaignVisit_Tvp</Routine>
              <TableType>Fact_CampaignVisit_Type</TableType>
            </SqlMappingEntity4> 
		  </mappings>
		</ExecRoutineStatementBuilder>
      </routines>
    </aggregation>	
   <group groupName="analytics.aggregation">
        <pipelines>
          <interactions>	
			<processor type="ShrikantTest.Feature.Analytics.CampaignVisitProcessor, ShrikantTest.Feature.Analytics" /> 
		  </interactions>
        </pipelines>
      </group
	  </pipelines>
  </sitecore>
</configuration>

	   
 

Now, you can verify that similar to default reporting database table, automatically this table will be filled when InteractionAggregationPipelineProcessor will run.

Thanks for reading. Happy learning