Walkthrough: Reporting on a new dimension

Abstract

Describes how you create and use a new dimension. This is sometimes also called a flexible dimension.

Sitecore has a number of dimensions that it reports on. If you want to report on a dimension that Sitecore does not report on, you can create your own dimension and collect metrics for it.

This walkthrough describes how to:

  • Create classes for dimensions and metrics - you create classes to represent the dimension and do the metrics calculations.

  • Update the reporting database - you update the reporting database to store your dimension data there.

  • Create items for dimension and metrics - you create Sitecore items that represent the dimension and its metrics.

  • Configure the new dimension - you configure the dimension to make Sitecore use it.

When you have created and configured the new dimension, you can add charts that display the data.

Create classes for dimensions and metrics

The following procedures show how you create the classes where you implement your new dimensions and calculate metrics.

Add references

Add references to these libraries in your Visual Studio solution:

Sitecore.Analytics.Aggregation
Sitecore.ExperienceAnalytics.Aggregation
Sitecore.XConnect
Sitecore.Kernel

Define a model

To create a model for the dimension and the metrics:

  1. Create a class that extends the DictionaryValue class and the IMergeableMetric<TableName> interface. The naming convention for these classes is <YourDimension>Metrics, for example PostCodeMetrics, OutcomeMetrics, and so on.

  2. Define properties for your metrics, for example Visits, MonetaryValue, and so on.

  3. Implement the MergeWith method. In this method you specify how data that is calculated for a new interaction is merged into the already existing calculated data.

The following example shows the implementation of a dimension called Outcome:

class OutcomeMetrics : DictionaryValue, IMergeableMetric<OutcomeMetrics>
{
  // Metric properties
  public int Visits { get; set; }
  public decimal MonetaryValue { get; set; }
  public int OutcomeOccurrences { get; set; }
  
  public T MergeWith<T>(T other) where T : PostCodeMetrics, new()
        {
            if (other == null)
            {
                throw new ArgumentNullException(nameof(other));
            }

            return new T
            {
                Visits = Visits + other.Visits,
                MonetaryValue = MonetaryValue + other.MonetaryValue,
                OutcomeOccurrences = OutcomeOccurrences + other.OutcomeOccurrences
            };
        }
}

Note

You use the names of the class and the metrics properties in a later task where you define the database schema.

Create a group resolver

You must define a strategy for resolving the keys used to group the dimensions, for example URL, Goal, or Outcome. You do this by implementing a group resolver.

To create a group resolver:

  1. Create a new class that extends the IGroupResolver<T> interface, where T is the type object you want to use as a key when you group records for your dimension.

  2. Implement the MeasureGroupOccurrences method.

In the following example you measure metrics for Outcomes and, therefore, you use the Outcome type to resolve groups:

class OutcomeResolver : IGroupResolver<Outcome>
{
   public IEnumerable<VisitGroupMeasurement<Outcome>> MeasureGroupOccurrences(
            IInteractionAggregationContext context)
   {
      if (context == null)
      {
         throw new ArgumentNullException(nameof(context));
      }
      var outcomeEvents = context.Interaction.Events.OfType<Outcome>();

      return outcomeEvents.GroupBy(outcome => outcome.DefinitionId)
             .Select(
                outcomeOccurrences =>
                  new VisitGroupMeasurement<Outcome>(
                     new  VisitGroup(outcomeOccurrences.Key), outcomeOccurrences));
      }
}

Create a fact calculator

You must create a fact calculator. This is the class that calculates the values of individual metrics defined in your model for this dimension, for example number of page views, bounce rate, and so on.

To create a fact calculator:

  1. Create a class the extends the IFactCalculator<out TValue, TGroup> interface, where TValue is the type of the first class you created and TGroup is the group type in the IGroupResolver class you created.

  2. Implement the CalculateFactsForGroup method. This method calculates the individual metrics for the specific groups.

The following example measures the total monetary value and the number of times the outcome happened:

class OutcomeFactCalculator : IFactCalculator<OutcomeMetrics, Outcome>
{
  public OutcomeMetrics CalculateFactsForGroup(
       VisitGroupMeasurement<Outcome> groupMeasurement,
       IInteractionAggregationContext context)
  {
      return new OutcomeMetrics
      {
         MonetaryValue = groupMeasurement.Occurrences.Sum(
outcome => outcome.MonetaryValue),
         OutcomeOccurrences = groupMeasurement.Occurrences.Count(),
         Visits = 1,
       };
   }
}

Create a parent key resolver

This step is optional. If you have a series of dimensions that are hierarchical in nature, for example outcome groups that contain many outcomes, you can implement a parent key resolver to represent the relationship in the hierarchy.

To create a parent key resolver:

  • Create a class that extends either the IVisitGroupKeyResolver<GUID> or the IVisitGroupKeyResolver<String> interface.

For example, you want to be able to drill down from an outcome group to an individual outcome. To do this, you must resolve an outcome group and its associated group keys by implementing the GetKeys method. This method returns the GUID of the outcome group parent item:

class OutcomeGroupKeyResolver : IVisitGroupKeyResolver<Guid>
{
   private readonly IDefinitionManager<IOutcomeDefinition> _outcomeDefinitionManager;

   public OutcomeGroupKeyResolver(
IDefinitionManager<IOutcomeDefinition> outcomeDefinitionManager)
   {
     _outcomeDefinitionManager = outcomeDefinitionManager;
   }

   public IEnumerable<Guid> GetKeys(IVisitGroup visitGroup)
   {
     var outcomeId = Guid.Parse(visitGroup.Id);
     var def = _outcomeDefinitionManager.Get(outcomeId, CultureInfo.InvariantCulture);
     yield return def.OutcomeGroupUri.TaxonId;
   }
}

Create an interaction filter

This step is optional. You can implement interaction filters for your dimension. You use an interaction filter to ignore data related to an interaction, based on events, goals, campaigns, channels, contacts or downloads. For example, if you do not want a certain page on your website to appear as part of your reports, you create a filter that specifies that the page should be ignored.

To create an interaction filter:

  • Create a class that extends the IInteractionFilter interface and implements the KeepInteraction method.  

In the following example you restrict processing so that only data for interactions that have events of a specific type based on their definition GUID are calculated:

class EventFilter : IInteractionFilter
{
  public bool KeepInteraction(Interaction interaction)
  {
     if (interaction.Events.Any(data => ReservedIds.Contains(data.DefinitionId)))
     {
         return true;
     }

     return false;
  }


   internal static readonly Guid[] ReservedIds = 
{ 
// list of guids
};
}

Update the reporting database

You must update the reporting database to store data for the new dimension. You create a table to store results, you create a type to represent the data, and you create a stored procedure to manipulate the data stored in the table.

Create a table

Create a table in SQL with a name following this convention: Fact_YourDimensionNameMetrics where YourDimensionName is the dimension name you used when you created your class.

When you create the table several columns are mandatory and required by Sitecore:

  • SegmentRecordId

  • SegmentId

  • Date

  • SiteNameId

  • DimensionKeyId

You must create a column to represent each metric property defined on your model class and the names must match. This is an example of a script to do this:

CREATE TABLE [dbo].[Fact_OutcomeMetrics](
     -- Mandatory Columns
			[SegmentRecordId] [bigint] NOT NULL,
     [SegmentId] [uniqueidentifier] NOT NULL,
     [Date] [smalldatetime] NOT NULL,
     [SiteNameId] [int] NOT NULL,
     [DimensionKeyId] [bigint] NOT NULL,
			-- Dimension specific metric columns
     [Visits] [int] NOT NULL,
     [MonetaryValue] [money] NOT NULL,
     [OutcomeOccurrences] [int] NOT NULL,
CONSTRAINT [PK_Fact_OutcomeMetrics_1] PRIMARY KEY CLUSTERED 
     (
     [SegmentRecordId] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     ) ON [PRIMARY]

Create a type and a stored procedure

You use a type and a stored procedure to manipulate the data in your table. The type must match the structure of the table exactly. This is an example:

CREATE TYPE [dbo].[OutcomeMetrics_Type] AS TABLE(
     -- Mandatory Columns
     [SegmentRecordId] [bigint] NOT NULL,
     [SegmentId] [uniqueidentifier] NOT NULL,
     [Date] [smalldatetime] NOT NULL,
     [SiteNameId] [int] NOT NULL,
     [DimensionKeyId] [bigint] NOT NULL,

			-- Dimension specific metric columns
     [Visits] [int] NOT NULL,
     [MonetaryValue] [money] NOT NULL,
     [OutcomeOccurrences] [int] NOT NULL,
     PRIMARY KEY CLUSTERED 
     (
     [SegmentRecordId] ASC
     )WITH (IGNORE_DUP_KEY = OFF)
     )

The stored procedure must create a record in the table if this does not exist, or add the incoming values to an existing record, and capture any errors. This is an example:

CREATE PROCEDURE [dbo].[Add_OutcomeMetrics_Tvp]
       @table [dbo].[OutcomeMetrics_Type] READONLY
     AS
     BEGIN
       SET NOCOUNT ON;

       BEGIN TRY

         MERGE [Fact_OutcomeMetrics] AS Target USING @table AS Source
         ON 
           Target.[SegmentRecordId] = Source.[SegmentRecordId]
         WHEN MATCHED THEN
           UPDATE SET 
             Target.[Visits] = (Target.[Visits] + Source.[Visits]),
             Target.[Value] = (Target.[Value] + Source.[Value]),
             Target.[MonetaryValue] = (Target.[MonetaryValue] + Source.[MonetaryValue]),
             Target.[OutcomeOccurrences] = (Target.[OutcomeOccurrences] + Source.[OutcomeOccurrences]),
         WHEN NOT MATCHED THEN
           INSERT (
     [SegmentRecordId],
     [SegmentId],
     [Date],
     [SiteNameId],
     [DimensionKeyId],
     [Visits],
     [Value],
     [Bounces],
     [Conversions],
     [TimeOnSite],
     [Pageviews],
     [MonetaryValue],
     [OutcomeOccurrences],
     [Count]
           )
           VALUES (
     Source.[SegmentRecordId],
     Source.[SegmentId],
     Source.[Date],
     Source.[SiteNameId],
     Source.[DimensionKeyId],
     Source.[Visits],
     Source.[Value],
     Source.[Bounces],
     Source.[Conversions],
     Source.[TimeOnSite],
     Source.[Pageviews],
     Source.[MonetaryValue],
     Source.[OutcomeOccurrences],
     Source.[Count]
     );

       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

Create items for dimensions and metrics

The next steps are to create Sitecore definition items that represent your dimension and metrics. You use these items when you create new reports.

Create the metrics items

To create a metrics item:

  1. In the Content Editor, navigate to the FlexibleMetrics folder in the Core database (sitecore/Client/Applications/ExperienceAnalytics/Common/System/ChartFields/FlexibleMetrics).

  2. For each metric associated with your new dimension, in the Home tab, in the Insert group, select, insert, and name a MetricChartField. Note that a field can already exist because metrics can be shared across multiple dimensions.

  3. Select the new item and specify values in these fields:

    • In the Appearance section, in the HeaderText field, enter a descriptive name for the new metric.

    • In the DataBinding section, in the DataField field enter the name (in camelCase) of the corresponding column in the table you have created.

  4. Save.

Create and configure the dimension item

To create and configure the dimension item:

  1. In the Content Editor, navigate to the Dimensions folder in the Marketing Control Panel in the master database (Marketing Control Panel/Experience Analytics/Dimensions) and select the subfolder to store your flexible dimension item in. You can choose Pages, Visits, or create your own subfolder.

  2. In the Home tab, in the Insert group, select, insert, and name a FlexibleDimension item.

  3. Select the new item and specify values in these fields:

    • In the Data section, in the Metric Type field, enter the name of the class you created (for example OutcomeMetrics).

    • In the Name field, enter a descriptive name.

    • In the Metrics field, select all metrics you want to add to the dimension.

    • Save.

Create a segment item

Experience Analytics uses segments to filter data into smaller sets when aggregating. You must specify at least one segment before any data is aggregated. You normally specify an all interactions/visits segment for this purpose. Create a custom report filter and segment has more information.

To create a segment:

  1. In the Content Editor, navigate to /sitecore/System/Marketing Control Panel/Experience Analytics/Dimensions, and select the dimension you created.

  2. In the Home tab, in the Insert group, select, insert, and name a Segment item.

  3. In the Review tab, in the Workflow group, click Deploy. By default, this starts data collection for the segment 30 minutes after deployment.

Note

Do not change a segment after you have deployed it. If you do, it can cause data inconsistencies.

Configure the new dimension

You must specify the new dimension in the Sitecore configuration to invoke it during aggregation.

To configure the new dimension:

  1. Create a patch file similar to this:

    <configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
        <sitecore>
            <experienceAnalytics>
                <aggregation>
                    <flexibleDimensions>
                        <!--Add references to your new dimension here -->
    <dimension id="Guid of your new dimension item" type="Sitecore.ExperienceAnalytics.Aggregation.FlexibleMetrics.Framework.FlexibleDimension`2[[Metrics Class fully qualified name, metrics class assembly name], [Fully qualified name of Group, Assembly name of Group]], Sitecore.ExperienceAnalytics.Aggregation">
    
      <param type="GroupResolver fully qualified name, GroupResolver assembly name" />
    
      <param type="FactCalculator fully qualified name, FactCalculator assembly name"/>
    </dimension>
                    </flexibleDimensions>
                </aggregation>
            </experienceAnalytics>
        </sitecore>
    </configuration>
    
  2. Optionally, specify parent key resolvers and filters by adding additional param elements to your dimension:

    <dimension …>
     …
    <param type="Sitecore.ExperienceAnalytics.Aggregation.FlexibleMetrics.Framework.KeyComposition.HierarchicalKeyComposer`1[[System.Guid]], Sitecore.ExperienceAnalytics.Aggregation">
      <ParentVisitGroupKeyResolvers hint="list">
        <ParentVisitGroupKeyResolver type="ParentKeyResolver fully qualified name, assembly name" /> 
      </ParentVisitGroupKeyResolvers>
    </param>
    
    <InteractionFilters hint ="list">
      <InteractionFilter type="Filter fully qualified name, assembly name" />
    </InteractionFilters>
    </dimension>