The reporting database reference

Version: 10.4

The reporting database contains aggregated data from the xDB collection database. This statistical data, generated by the aggregation pipeline and used by Sitecore reporting applications, is stored in fact and dimension tables organized in a star schema. In a star schema, the fact table is located at the center of the schema with the dimension tables surrounding it. This guide describes the default tables in the reporting database.

Note

There are several tables that aren't mentioned in this topic and are intended for system use only. Do not modify these.

For more information on the type of tables used in the reporting database see SQL Server table types used in the reporting database.

Fact tables

Fact table is a data warehousing term used to describe tables that contain measurements and metrics. In a fact table, you can use foreign keys to make joins with dimension tables.

The xDB reporting database includes the following fact tables:

Fact_AutomationStates

The xDB uses the Fact_AutomationStates table to enable engagement monitoring.

Key

Column

Description

PlanId

The unique engagement plan identifier.

StateId

The unique identifier of the engagement plan state.

Contacts

The number of contacts currently in this engagement plan state.

Fact_Conversions

The Fact_Conversions table contains the goals triggered by contacts. It provides report data for analyzing goal conversions and other related statistics.

Key

Column

Description

Date

The time period in which the interaction started.

TrafficType

The channel through which a contact receives content, for example, a website, email campaign, phone call, or other type of interaction.

ContactId

The unique identifier of the contact that triggered the goal.

CampaignId

The unique identifier of the campaign for the interaction.

GoalId

The unique identifier of the goal triggered.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

LanguageId

The unique identifier of the entry in the Languages dimension.

AccountId

The unique identifier of the entry in the Accounts dimension.

ItemId

The unique identifier of the item on which the goal was triggered.

GoalPoints

The engagement value points associated with the goal.

Visits

The total number of matching interactions.

Value

The aggregated engagement value accumulated by the matching interactions.

Count

The number of times the goal was triggered by the matching interactions.

Fact_Downloads

The Fact_Downloads table contains statistical data about downloaded assets. It enables reporting that lets you analyze download trends.

Key

Column

Description

Date

The time period in which the interaction started.

TrafficType

The channel through which a contact receives content, for example, a website, email campaign, phone call, or other type of interaction.

CampaignId

The unique identifier of the campaign triggered by the interaction.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

LanguageId

The unique identifier of the entry in the Languages dimension.

AccountId

The unique identifier of the entry in the Accounts dimension.

ItemId

The unique identifier of the item on which the goal was triggered.

AssetId

The unique identifier of the asset requested.

Visits

The total number of matching interactions.

Value

The total amount of engagement value accumulated by the matching interactions.

Count

The number of times that the asset was requested.

Fact_Failures

The Fact_Failures table contains statistical data about page events indicating errors. It enables reporting on the health of the website.

Key

Column

Description

VisitId

The unique interaction identifier.

AccountId

The unique identifier of the account belonging to the contact making the interaction.

Date

The time period in which the interaction started.

ContactId

The unique identifier of the contact initiating the interaction.

PageEventDefinitionId

The unique identifier of the page event definition.

KeywordsId

The unique identifier of the entry in the Keywords dimension.

ReferringSiteId

The unique identifier of the entry in the ReferringSites dimension.

ContactVisitIndex

The ordinal number of the current interaction among all interactions that contact has made.

VisitPageIndex

The ordinal number of the page visited in the current interaction.

FailureDetailsId

The unique identifier of the entry in the FailureDetails dimension.

Value

The total amount of engagement value accumulated by matching interactions.

Count

The total number of times the failure was encountered.

Fact_FollowHits

The Fact_FollowHits table contains statistical data about site searches.

Key

Column

Description

Date

The time period in which the interaction started.

ItemId

The unique identifier of the item clicked in the search results.

KeywordsId

The unique identifier of the entry in the Keywords dimension.

Visits

The total number of matching interactions.

Value

The total amount of engagement value accumulated by matching interactions.

Count

The total number of times the follow hit event was triggered.

Fact_MvTesting

The Fact_MvTesting table contains statistical data used internally by Sitecore to evaluate the efficiency of content variants used in A/B and multivariate testing.

Key

Column

Description

TestSetId

The unique identifier of the test set.

TestValues

The test combination used in the test.

Visits

The number of interactions that are associated with this test.

Value

The amount of engagement value accumulated by matching interactions.

Bounces

The number of sessions that bounced for the test. It indicates contacts that only visited this particular test page and then left the site.

TotalPageDuration

The total duration, in milliseconds, that contacts stayed on the test page.

TotalWebsiteDuration

The total duration, in milliseconds, that contacts remained on the site after viewing the test.

PageCount

The number of pages visited after the viewing the test.

Fact_MvTestingDetails

The Fact_MvTestingDetails table contains facts about the test performance and the groupings within each test. It is similar to the Fact_MvTesting table, but also includes Value as part of the key.

This table is used by the system to determine when enough data has been collected for a test to be statistically valid.

Key

Column

Description

TestSetId

The unique identifier of the test set.

TestValues

The test combination used in the test.

Value

The amount of engagement value accumulated during a test.

Visits

The number of interactions that accumulated engagement value during a test.

Fact_PageViews

The Fact_PageViews table contains statistical data about online or offline contact interactions.

Key

Column

Description

Date

The time period in which the interaction started.

ItemId

The unique identifier of the item viewed.

Views

The total number of similar page views.

Duration

The total duration, in seconds, of matching page views.

Visits

The total number of matching interactions.

Value

The amount of engagement value accumulated by matching interactions.

TestId

ID of the test viewed during the page view.

TestCombination

The combination of test values used during a test.

Fact_PageViewsByLanguage

The Fact_PageLanguageViews table contains details on the views recorded for items by language.

Key

Column

Description

Date

The time period in which the interaction started.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

ItemId

The unique identifier of the item that was viewed.

LanguageId

The unique identifier of the entry in the Languages dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

Views

The total number of times the page was viewed.

Visits

The number of interactions which included a view of the page.

Duration

The total amount of time contacts spent on the page.

Value

The amount of engagement value accumulated for interactions which included a view of this page.

Fact_Personalization

The Fact_Personalization table contains facts about personalization rules that have been used as part of a test.

Key

Column

Description

Date

The time period in which the interaction started.

RuleSetId

The unique identifier of the rule set used during the test.

RuleId

The unique identifier of the rule.

TestSetId

The unique identifier of the test set.

TestValues

The test combination used during a test.

IsDefault

Indicates if the rule used was a default rule.

ItemId

The unique identifier of the item that was viewed.

Visits

The number of unique interactions using the rule for this test combination.

Value

The amount of engagement value accumulated by the matching interactions.

Fact_SegmentMetrics

The Fact_SegmentMetrics table contains facts about a segment identified by the SegmentRecords table, and then grouped by contact transition type.

Key

Column

Description

SegmentRecordId

The unique identifier of the entry in the SegmentRecords dimension.

ContactTransitionType

Identifies the transition types measured in this row.

Visits

The number of interactions.

Value

The total amount of engagement value accumulated.

Bounces

The number of bounces.

Conversions

The total number of events in which a conversion took place.

TimeOnSite

The duration of the interaction in seconds.

Pageviews

The total number of page views.

Count

The total count of triggered events.

Converted

The total number of interactions in which at least one conversion took place.

Fact_SegmentMetricsReduced

The Fact_SegmentMetricsReduced table contains facts about a segment that has been identified by the SegmentRecordsReduced table, and then grouped by contact transition type. These facts are processed by the reduce agent.

Key

Column

Description

SegmentRecordId

The unique identifier of the entry in the SegmentRecords dimension.

ContactTransitionType

Identifies the transition types measured in this row.

Visits

The number of interactions.

Value

The total amount of engagement value accumulated.

Bounces

The number of bounces.

Conversions

The total number of events in which a conversion took place.

TimeOnSite

The duration of the interaction in seconds.

Pageviews

The total number of page views.

Count

The total count of triggered events.

Converted

The total number of interactions in which at least one conversion took place.

Fact_SiteSearches

The Fact_SiteSearches table contains statistical data about keywords used by contacts searching the site.

Key

Column

Description

Date

The time period in which the interaction started.

TrafficType

The channel through which a contact interacts with your organization, for example, a website, email campaign, phone call or other type of interaction.

CampaignId

The unique identifier of the campaign.

ItemId

The unique identifier of the item on which the search was initiated.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

LanguageId

The unique identifier of the entry in the Languages dimension.

AccountId

The unique identifier of the entry in the Accounts dimension.

KeywordsId

The unique identifier of the entry in the Keywords dimension.

Visits

The total number of matching interactions.

Value

The total amount of engagement value accumulated in the matching interactions.

Count

The number of times that the search was executed.

Fact_SlowPages

The Fact_SlowPages table contains information about pages that rendered slowly.

Key

Column

Description

Date

The time period in which the interaction started.

ItemId

The unique identifier of the item that was rendered.

Duration

The time, in milliseconds, that it took to render the item.

VisitId

The unique identifier of the interaction.

AccountId

The unique identifier of the entry in the Accounts dimension.

ContactId

The unique identifier of the contact.

ContactVisitIndex

The ordinal number of the current interaction among all interactions that were made by the contact.

Value

The amount of engagement value accumulated during the interaction.

Views

The number of times that the item was viewed.

Fact_TestConversions

The Fact_TestConversions table contains information about the goal conversions recorded as a result of a test.

Key

Column

Description

GoalId

The unique identifier of the goal that was converted.

TestSetId

The unique identifier of the test set.

TestValues

The test combination that the contact was exposed to.

Date

The time period in which the test started.

Visits

The number of unique interactions where the goal was converted.

Value

The amount of engagement value accumulated during the test.

Count

The total number of times that the goal was converted after the test began.

Fact_TestOutcomes

The Fact_TestOutcomes table contains information about the outcome of tests. It is used to rank and report on individual testers, for example, authors and optimization experts.

Key

Column

Description

TestSetId

The unique identifier of the test set.

TestOwner

The user name of the user who owns the test.

CompletionDate

The date and time that the test was completed.

TestScore

The test score that was awarded for the test.

Effect

The change in value over the original content.

Guess

The test owner's guess on the outcome of the test.

Fact_TestPageClicks

The Fact_TestPageClicks table contains facts about which pages were visited directly after the test.

Key

Column

Description

TestSetId

The unique identifier of the test set.

TestValues

The test combination used in the test.

ItemId

The ID of the next item navigated to directly after the test.

Views

The number of times that a contact navigated to the item.

Fact_TestStatistics

The Fact_TestStatistics table contains facts about the current statistical significance of the test. Unlike other fact tables, the data is not populated through aggregation but by the system.

Key

Column

Description

TestSetId

The unique identifier of the test set.

Power

The power of the test as calculated using Pearson’s Chi squared test.

P

The P value of the test as calculated using Pearson’s Chi squared test.

IsStatisticalRelevant

Indicates whether the test is statistically significant.

Fact_Traffic

The Fact_Traffic table contains statistical data about interactions. The Fact_Traffic table is optimized for inserts and date range queries.

Key

Column

Description

Date

The time period in which the interaction started.

Checksum

A value that is derived from the logical primary key for fast lookups.

TrafficType

The channel through which a contact came to a website.

CampaignId

The unique identifier of the entry in the Campaigns dimension.

ItemId

The unique identifier of the entry in the Items dimension.

KeywordsId

The unique identifier of the entry in the Keywords dimension.

ReferringSiteId

The unique identifier of the entry in the ReferringSites dimension.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

LanguageId

The unique identifier of the entry in the Languages dimension.

FirstVisit

Indicates whether the interaction is the first interaction for the contact.

Visits

The total number of matching interactions.

Value

The total amount of engagement value accumulated by the matching interactions.

Fact_ValueBySource

The Fact_ValueBySource table contains statistical data about the source channels that contacts use to interact with your organization or brand.

Key

Column

Description

Date

The time period in which the interaction started.

TrafficType

The channel through which a contact came to a website.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

LanguageId

The unique identifier of the entry in the Languages dimension.

FirstVisitValue

The total amount of engagement value accumulated during the first interaction of a contact.

Contacts

The number of unique contacts that accessed the content.

Visits

The total number of matching interactions.

Value

The total amount of engagement value accumulated during the matching interactions.

Fact_Visits

The Fact_Visits table contains statistical data about interactions.

Key

Column

Description

Date

The time period in which the interaction started.

ItemId

The unique identifier of the first item that a contact viewed during the interaction.

LanguageId

The unique identifier of the entry in the Languages dimension.

FirstVisit

Indicates whether this is the first interaction for the contact.

PagesCount

The number of items viewed during the interaction.

Fact_VisitsByBusinessContactLocation

The Fact_VisitsByBusinessContactLocation table contains statistical data about the geographical distribution of contacts who interact with your organization.

Key

Column

Description

AccountId

The unique identifier of the entry in the Accounts dimension.

BusinessUnitId

The unique identifier of the entry in the BusinessUnits dimension.

Date

The time period in which the interaction started.

TrafficType

The channel through which a visitor came to a website.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DeviceNameId

The unique identifier of the entry in the DeviceNames dimension.

ContactId

The unique identifier of the contact making the interaction.

LanguageId

The unique identifier of the entry in the Languages dimension.

Latitude

The latitude from where the interaction was initiated.

Longitude

The longitude from where the interaction was initiated.

Visits

The total number of matching interactions.

Value

The total amount engagement value points accumulated in the matching interactions.

Dimension Tables

Dimension tables are companion tables to fact tables, and are similar to Microsoft SQL Server lookup tables. They contain descriptive attributes or textual fields that help you understand and analyze the data in fact tables.

The xDB reporting database includes the following dimension tables:

Accounts

The Accounts table contains information about the organization based on their IP addresses.

Key

Column

Description

AccountId

The unique identifier of the organization.

BusinessName

The organization name that is returned by the IP Geolocation provider.

Country

The name of the country in which the organization is located.

Classification

The classification of the location.

IntegrationId

A unique identifier that connects this entry to an external data source.

IntegrationLabel

A label that provides additional information about the relationship between this entry and an entry in an external data source.

ExternalUser

Connects the account to a Sitecore user.

Assets

The Assets table contains the URLs of downloadable content.

Key

Column

Description

AssetId

A generated surrogate key that is used to reference entries in this table.

Url

The URL of the downloadable content.

BusinessUnits

The BusinessUnits table contains information about business units.

Key

Column

Description

BusinessUnitId

The unique identifier of the business unit.

AccountId

The unique identifier of the account connected to the business unit.

BusinessName

The name of the business unit.

Country

The ISO code of the country in which the business unit is located.

Region

The region within the country in which the business unit is located.

City

The city within the region in which the business unit is located.

DeviceNames

The DeviceNames table contains the names of devices used by the contact.

List of table columns with descriptions:

Key

Column

Description

DeviceNameId

A generated surrogate key used to reference entries in this table.

DeviceName

The name of the device.

DimensionKeys

The DimensionKeys table translates the raw representation of a key to a lightweight unique hash.

List of table columns with descriptions:

Key

Column

Description

DimensionKeyId

The hash of the DimensionKey.

DimensionKey

The unique string value identifying the variations of a dimension.

FailureDetails

The FailureDetails table contains details about failure events.

Key

Column

Description

FailureDetailsId

A generated surrogate key used to reference entries in this table.

Url

The URL that is used when the failure occurs.

ErrorText

The description of the error.

PreviousUrl

The URL that was visited prior to the failure occurring.

DataKey

The first 100 characters of the data associated with the event.

Data

The first 450 characters of the data associated with the event.

Items

The Items table contains the URLs of items.

Key

Column

Description

ItemId

The unique identifier of the item.

Url

The URL of the item.

Keywords

The Keywords table contains both the external and the local search terms used.

Key

Column

Description

KeywordsId

A generated surrogate key used to reference entries in this table.

Keywords

The search term used.

Languages

The Languages table contains the names of the languages used.

Key

Column

Description

LanguageId

A generated surrogate key used to reference entries in this table.

Name

The English name of the language.

SegmentRecords

The SegmentRecords table contains the measurements performed for a segment, date, site and dimension key. The dimension key contains the ID of the type of entity being measured by the segment, and is defined by the dimension or segment type.

Key

Column

Description

SegmentRecordId

A surrogate identifier derived from the SegmentId, Date, SiteNameId, and DimensionKeyId.

SegmentId

The unique identifier of the entry in the Segments dimensions.

Date

The date for the measurements of this record.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DimensionKeyId

The unique identifier of the entry in the DimensionsKeys dimension.

SegmentRecordsReduced

The SegmentRecordsReduced table contains the measurements performed for a segment, date, site, and dimension key processed by the reduce agent. The dimension key contains the ID of the type of entity being measured by the segment, and is defined by the dimension or segment type.

Key

Column

Description

SegmentRecordId

A surrogate identifier derived from the SegmentId, Date, SiteNameId and DimensionKeyId.

SegmentId

The unique identifier of the entry in the Segments dimensions.

Date

The date for the measurements of this record.

SiteNameId

The unique identifier of the entry in the SiteNames dimension.

DimensionKeyId

The unique identifier of the entry in the DimensionsKeys dimension.

Segments

The Segments table contains a local representation of segment items created in the Marketing Control Panel under Experience Analytics. The contents of the Segments table are used by xAnalytics when aggregating interactions.

Key

Column

Description

SegmentId

The unique identifier of the Sitecore item that contains the original representation of a segment.

DimensionId

The unique identifier of the Sitecore item that contains the original representation of the dimension that defines the type of this segment.

Filter

The serialized representation of the Sitecore item that this segment refers to as its filter.

DeployDate

The date after which this segment is expected to have processed all interactions using live aggregation.

Status

The status codes of segments during the rebuild process:

  • Not initialized = 0

  • Initializing = 1

  • Initialized = 2

  • Failed = 200

ReferringSites

The ReferringSites table contains the URLs of the websites that link to your site.

Key

Column

Description

ReferringSiteId

A generated surrogate key that is used to reference entries in this table.

ReferringSite

The name of the site that links to your website.

SiteNames

The SiteNames table contains the names of the websites defined in your setup.

Key

Column

Description

SiteNameId

A generated surrogate key used to reference entries in this table.

SiteName

The name of the logical website.

Testing_ClusterMembers

The Testing_ClusterMembers table maps between contacts and the clusters that the contacts belong to.

Key

Column

Description

ContactId

The unique identifier of the contact.

ClusterId

The unique identifier for the cluster the contact belongs to.

Date

The date and time when the record was saved.

TestId

The unique identifier of the test set.

Testing_Clusters

The Testing_Clusters table contains the clusters of contacts that have been identified for a test.

Key

Column

Description

ClusterId

The unique identifier for the cluster.

FeatureName

The name of the feature that is used for classification within the cluster.

Date

The date and time that the record was saved.

TestId

The unique identifier of the test set.

FeatureValue

The value of the feature used for classification within the cluster.

TreeDefinitions

The TreeDefinitions table stores a reduced set of properties from the Maps folder where the definition items for Path Analyzer, path maps are stored.

Key

Column

Description

DefinitionId

The GUID of the corresponding path map item ID from the Sitecore master database.

Data

Contains the serialized JSON of reduced set of properties of a given path map definition item, such as type, builder type, start date, end date, and rule based filter.

DeployDate

The deploy date of a given path map definition item.

Status

The status of the path map definition.

TaskId

The unique identifier of the task.

Trees

The Trees table contains the actual data for a particular path map in the form of a tree object serialized as a blob. Each record in this table corresponds to a particular tree definition by ID and corresponds to a date (StartDate and EndDate columns).

Key

Column

Description

DefinitionId

Foreign key reference to the TreeDefinitions.DefinitionId column in the TreeDefinitions table.

StartDate

The start of the date interval that the tree blob corresponds to.

For example: 2013-12-21

EndDate

The end of the date interval that the tree blob corresponds to.

For example: 2013-12-22

TreeBlob

The serialized blob containing the tree object providing the underlying data for a path map and for a given time scale.

Visits

The total count of visits that are stored in a tree object.

This helps to identify empty trees or trees without actual data. The Path Analyzer still creates these trees, but the Visits value will always be ‘0’.

Value

Total amount of engagement value for all nodes in a tree object.

Nodes

Total number of nodes in a tree object.

This helps to identify empty trees or trees without actual data. The Path Analyzer still creates these trees, but the number of nodes will always be ‘1’, with the root node being the only node in the tree.

Version

The auto-generated time stamp of the last update made to the tree. It is used to resolve concurrency issues between multiple processing instances that are trying to update the same tree record.

Trail Tables

Trail tables are used to ensure that no input data is processed more than once. Each time input data is processed, its unique identifier and a timestamp is recorded in the trail table.

The xDB reporting database includes the following trail tables: Trail_Interactions, Trail_PathAnalyzer.

Key

Column

Description

Id

The unique identifier of the data that has been processed. The name of the column varies among trail tables. In some cases, this may be a compound key.

Processed

The date and time that the data was processed.

Views

Views are SQL queries that enable you to create other queries more quickly and efficiently. For example, views enable you to reuse joins between tables.

The xDB reporting database includes the following views:

View

Description

CampaignOverview

Enables you to query the TrafficOverview table.

Conversions

Enables you to query the Fact_Conversions table.

Downloads

Enables you to query the Fact_Downloads table.

FollowHits

Enables you to query the Fact_FollowHits table.

NotFoundUrls

Enables you to query the Fact_Failures table and inner join with FailureDetails table.

ReportDataView

Enables you to query the Fact_SegmentMetrics table and inner join with SegmentRecords and DimensionKeys tables.

SiteSearches

Enables you to query the Fact_SiteSearches table

SlowPages

Enables you to query the Fact_SlowPages table.

TopLeads

Enables you to query the Fact_VisitsByBusinessContactLocation table.

Traffic

Enables you to query the Fact_Traffic table.

TrafficOverview

Provides backwards compatibility.

ValueBySource

Enables you to query the Fact_ValueBySource table.

Do you have some feedback for us?

If you have suggestions for improving this article,