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

Hello everyone, Hope everyone is doing great. This blog is one more addition to my existing blogs list. You can anytime refer my Blog Site for Sitecore related blogs. Hope this blog site will help you in learning something new.

Now let's start with todays topic for discussion. In this blog we are going to see how we can create custom table in Sitecore reporting database and how we can write custom aggregation processor to pass all the user define analytics data to our custom tabel similar to how sitecore internally processes the same. This blog will be divided into two parts:

• We will see how we can create custom table in reporting database along with Store procedure, Table aliases required for the same
• We will see the custom aggregation code required for the same

We will try to understand all this things using a Use Case.

Use Case: We need to create a cutom table in reporting db to store all the campaign visited by the Contact along with the dates of visits.

Step 1: We will create a table in SQL reporting Database

       
/****** Object:  Table [dbo].[Fact_CampaignVisit]    Script Date: 11/21/2022 1:42:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Fact_CampaignVisit](
	[VisitDate] [smalldatetime] NOT NULL,
	[ContactId] [uniqueidentifier] NOT NULL,
	[CampaignId] [uniqueidentifier] NOT NULL,
	
 CONSTRAINT [PK_CampaignVisit] PRIMARY KEY CLUSTERED 
(
	[VisitDate] ASC,
	[ContactId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Fact_CampaignVisit]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_CampaignVisit_Contacts] FOREIGN KEY([ContactId])
REFERENCES [dbo].[Contacts] ([ContactId])
GO

ALTER TABLE [dbo].[Fact_CampaignVisit] NOCHECK CONSTRAINT [FK_Fact_CampaignVisit_Contacts]
GO
	   
 

This is straight forward script where we are creating a table Fact_CampaignVisit.We are creating a composite primary key using Contact ID and Date column.

Step 2: Create Alias type table of Fact_CampaignVisit.

This will hold all the type of data for the columns which the Fact_CampaignVisit expects.

       
CREATE TYPE Fact_CampaignVisit_Type AS TABLE
(
	[VisitDate] [smalldatetime] NOT NULL,
	[ContactId] [uniqueidentifier] NOT NULL,
	[CampaignId] [uniqueidentifier] NOT NULL
)
	   
 

Step 3: Store Procedure for inserting the data

       
/****** Object:  StoredProcedure [dbo].[Add_CampaignVisit_Tvp]    Script Date: 11/21/2022 1:51:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Add_CampaignVisit_Tvp]
  @table [Fact_CampaignVisit_Type] READONLY
AS
BEGIN

  SET NOCOUNT ON;

  BEGIN TRY

    MERGE
      [Fact_CampaignVisit] WITH (HOLDLOCK) AS [target]
    USING
      @table AS [source] ON
      ( 
        ([target].[ContactId] = [source].[ContactId]) 
      )

    WHEN MATCHED THEN
      UPDATE
        SET
          [target].[CampaignId] = ([source].[CampaignId])

    WHEN NOT MATCHED THEN
      INSERT
      (
        [VisitDate],
        [ContactId],
        [CampaignId]
	  )
      VALUES
      (
        [source].[VisitDate],
        [source].[ContactId],
        [source].[CampaignId]
	  );
  
  END TRY
  BEGIN CATCH

    DECLARE @error_number INTEGER = ERROR_NUMBER();
    DECLARE @error_severity INTEGER = ERROR_SEVERITY();
    DECLARE @error_state INTEGER = ERROR_STATE();
    DECLARE @error_message NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @error_procedure SYSNAME = ERROR_PROCEDURE();
    DECLARE @error_line INTEGER = ERROR_LINE();

    RAISERROR( N'T-SQL ERROR %d, SEVERITY %d, STATE %d, PROCEDURE %s, LINE %d, MESSAGE: %s', @error_severity, 1, @error_number, @error_severity, @error_state, @error_procedure, @error_line, @error_message ) WITH NOWAIT;
  
  END CATCH;

END;
GO
	   
 

Next part of this blog where we will see the backend code logic required for the same. Part 2 link is available Here

Thanks for reading. Happy learning.