The reporting database reference
A description of the xDB reporting database schema.
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.
Note
There are several tables not mentioned in this topic that are intended for system use only and which should not be modified.
For more information on the type of tables used in the reporting database see SQL Server table types used in the reporting database.
The following sections describe the default tables in the reporting database:
Fact table is a data warehousing term used to describe tables that contain measurements and metrics. In a fact table foreign keys allow joins to be made 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 | |
|---|---|---|---|
|
| The unique engagement plan identifier. | |
|
| The unique identifier of the engagement plan state. | |
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The channel through which a contact receives content, for example, a website, email campaign, phone call or other type of interaction. | |
|
| The unique identifier of the contact that triggered the goal. | |
|
| The unique identifier of the campaign for the interaction. | |
|
| The unique identifier of the goal triggered. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the item on which the goal was triggered. | |
|
| The engagement value points associated with the goal. | |
| The total number of matching interactions. | ||
| The aggregated engagement value accumulated by the matching interactions. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The channel through which a contact receives content, for example, a website, email campaign, phone call or other type of interaction. | |
|
| The unique identifier of the campaign triggered by the interaction. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the item on which the goal was triggered. | |
|
| The unique identifier of the asset requested. | |
| The total number of matching interactions. | ||
| The total amount of engagement value accumulated by the matching interactions. | ||
| 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 | |
|---|---|---|---|
|
| The unique interaction identifier. | |
|
| The unique identifier of the account belonging to the contact making the interaction. | |
|
| The time period in which the interaction started. | |
|
| The unique identifier of the contact initiating the interaction. | |
|
| The unique identifier of the page event definition. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The ordinal number of the current interaction among all interactions that contact has made. | |
|
| The ordinal number of the page visited in the current interaction. | |
|
| The unique identifier of the entry in the | |
| The total amount of engagement value accumulated by matching interactions. | ||
| The total number of times the failure was encountered. |
Fact_FollowHits
The Fact_FollowHits table contains statistical data about site searches.
Key | Column | Description | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The unique identifier of the item clicked in the search results. | |
|
| The unique identifier of the entry in the | |
| The total number of matching interactions. | ||
| The total amount of engagement value accumulated by matching interactions. | ||
| 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 | |
|---|---|---|---|
|
| The unique identifier of the test set. | |
|
| The test combination used in the test. | |
| The number of interactions that are associated with this test. | ||
| The amount of engagement value accumulated by matching interactions. | ||
| The number of sessions that bounced for the test. It indicates contacts that only visited this particular test page and then left the site. | ||
| The total duration, in milliseconds, that contacts stayed on the test page. | ||
| The total duration, in milliseconds, that contacts remained on the site after viewing the test. | ||
| The number of pages visited after the viewing the test. | ||
| The number of unique contacts that viewed the tested content. |
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 | |
|---|---|---|---|
|
| The unique identifier of the test set. | |
|
| The test combination used in the test. | |
|
| The amount of engagement value accumulated during a test. | |
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The unique identifier of the item viewed. | |
|
| The unique identifier of the contact viewing the item. | |
| The total number of similar page views. | ||
| The total duration, in seconds, of matching page views. | ||
| The total number of matching interactions. | ||
| The amount of engagement value accumulated by matching interactions. | ||
| ID of the test viewed during the page view. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the item that was viewed. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
| The total number of times the page was viewed. | ||
| The number of interactions which included a view of the page. | ||
| The total amount of time contacts spent on the page. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The unique identifier of the rule set used during the test. | |
|
| The unique identifier of the rule. | |
|
| The unique identifier of the test set. | |
|
| The test combination used during a test. | |
|
| Indicates if the rule used was a default rule. | |
| The number of unique interactions using the rule for this test combination. | ||
| The amount of engagement value accumulated by the matching interactions. | ||
| The number of unique contacts who matched the 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 | |
|---|---|---|---|
|
| The unique identifier of the entry in the | |
|
| Identifies the transition types measured in this row. | |
| The number of interactions. | ||
| The total amount of engagement value accumulated. | ||
| The number of bounces. | ||
| The total number of events in which a conversion took place. | ||
| The duration of the interaction in seconds. | ||
| The total number of page views. | ||
| The total count of triggered events. |
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 | |
|---|---|---|---|
|
| The unique identifier of the entry in the | |
|
| Identifies the transition types measured in this row. | |
| The number of interactions. | ||
| The total amount of engagement value accumulated. | ||
| The number of bounces. | ||
| The total number of events in which a conversion took place. | ||
| The duration of the interaction in seconds. | ||
| The total number of page views. | ||
| The total count of triggered events. |
Fact_SiteSearches
The Fact_SiteSearches table contains statistical data about keywords used by contacts searching the site.
Key | Column | Description | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The channel through which a contact interacts with your organization, for example, a website, email campaign, phone call or other type of interaction. | |
|
| The unique identifier of the campaign. | |
|
| The unique identifier of the item on which the search was initiated. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
| The total number of matching interactions. | ||
| The total amount of engagement value accumulated in the matching interactions. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The unique identifier of the item that was rendered. | |
|
| The time, in milliseconds, that it took to render the item. | |
|
| The unique identifier of the interaction. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the contact. | |
|
| The ordinal number of the current interaction among all interactions that were made by the contact. | |
|
| The amount of engagement value accumulated during the interaction. | |
| 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 | |
|---|---|---|---|
|
| The unique identifier of the goal that was converted. | |
|
| The unique identifier of the test set. | |
|
| The test combination that the contact was exposed to. | |
| The number of unique interactions where the goal was converted. | ||
| The amount of engagement value accumulated during the test. | ||
| 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 | |
|---|---|---|---|
|
| The unique identifier of the test set. | |
| The user name of the user who owns the test. | ||
| The date and time that the test was completed. | ||
| The test score that was awarded for the test. | ||
| The change in value over the original content. | ||
| 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 | |
|---|---|---|---|
|
| The unique identifier of the test set. | |
|
| The test combination used in the test. | |
|
| The ID of the next item navigated to directly after the test. | |
| 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 | |
|---|---|---|---|
|
| The unique identifier of the test set. | |
| The power of the test as calculated using Pearson’s Chi squared test. | ||
| The P value of the test as calculated using Pearson’s Chi squared test. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
| A value that is derived from the logical primary key for fast lookups. | ||
|
| The channel through which a contact came to a website. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| Indicates whether the interaction is the first interaction for the contact. | |
| The total number of matching interactions. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The channel through which a contact came to a website. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
| The total amount of engagement value accumulated during the first interaction of a contact. | ||
| The number of unique contacts that accessed the content. | ||
| The total number of matching interactions. | ||
| 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 | |
|---|---|---|---|
|
| The time period in which the interaction started. | |
|
| The unique identifier of the first item that a contact viewed during the interaction. | |
|
| The unique identifier of the contact making the interaction. | |
|
| The unique identifier of the entry in the | |
|
| Indicates whether this is the first interaction for the contact. | |
| 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 | |
|---|---|---|---|
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The time period in which the interaction started. | |
|
| The channel through which a visitor came to a website. | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the entry in the | |
|
| The unique identifier of the contact making the interaction. | |
|
| The unique identifier of the entry in the | |
|
| The latitude from where the interaction was initiated. | |
|
| The longitude from where the interaction was initiated. | |
| The total number of matching interactions. | ||
| The total amount engagement value points accumulated in the matching interactions. |
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 | |
|---|---|---|---|
|
| The unique identifier of the organization. | |
| The organization name that is returned by the IP Geolocation provider. | ||
| The name of the country in which the organization is located. | ||
| The classification of the location. | ||
| A unique identifier that connects this entry to an external data source. | ||
| A label that provides additional information about the relationship between this entry and an entry in an external data source. | ||
| Connects the account to a Sitecore user. |
Assets
The Assets table contains the URLs of downloadable content.
Key | Column | Description | |
|---|---|---|---|
|
| A generated surrogate key that is used to reference entries in this table. | |
| The URL of the downloadable content. |
BusinessUnits
The BusinessUnits table contains information about business units.
Key | Column | Description | |
|---|---|---|---|
|
| The unique identifier of the business unit. | |
| The unique identifier of the account connected to the business unit. | ||
| The name of the business unit. | ||
| The ISO code of the country in which the business unit is located. | ||
| The region within the country in which the business unit is located. | ||
| The city within the region in which the business unit is located. |
CampaignActivityDefinitions
The CampaignActivityDefinitions table contains the classified campaign activity definitions matching those found in the Marketing Control Panel. This is populated by deploying campaign definitions in Sitecore and should not be modified directly using SQL.
Key | Column | Description | |
|---|---|---|---|
|
| The unique identifier of the campaign activity definition item. | |
|
| The campaign item version. | |
|
| The language that the campaign activity is deployed in. | |
| This indicates whether the campaign is active. | ||
| Campaign execution – this link is used to associate the campaign with an item for tracking or to associate it with an engagement plan. |
Campaigns
The Campaigns table contains information about deployed campaigns.
Key | Column | Description | |
|---|---|---|---|
|
| The unique identifier of the campaign. | |
| The name or title of the campaign. | ||
| The total cost of the campaign. | ||
| The cost of the campaign per click. | ||
| The cost of the campaign per day. | ||
| The base cost of the campaign. | ||
| The taxonomy classification of the campaign definition item. This column maps to the folder where you store your campaign. | ||
| The taxonomy classification of the campaign definition item. This column maps to the folder where you store your campaign. | ||
| The taxonomy classification of the campaign definition item. This column maps to the folder where the campaign is stored. | ||
| The taxonomy classification of the campaign definition item. This column maps to the folder where the campaign is stored. | ||
| The taxonomy classification of the campaign definition item. This column maps to the folder where the campaign is stored. | ||
| The taxonomy classification of the campaign definition item. This column maps to the folder where the campaign is stored. | ||
| A label that provides additional information about the relationship this entry has with an entry in an external data source. | ||
| A unique identifier that connects this entry to an external data source. | ||
| Indicates whether the campaign is active. | ||
| The start date for the campaign. | ||
| The end date for the campaign. | ||
| Custom data associated with the campaign. |
DeviceNames
The DeviceNames table contains the names of devices used by the contact.
List of table columns with descriptions:
Key | Column | Description | |
|---|---|---|---|
|
| A generated surrogate key used to reference entries in this table. | |
| 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 | |
|---|---|---|---|
|
| The hash of the | |
| The unique string value identifying the variations of a dimension. |
FailureDetails
The FailureDetails table contains details about failure events.
Key | Column | Description | |
|---|---|---|---|
|
| A generated surrogate key used to reference entries in this table. | |
| The URL that is used when the failure occurs. | ||
| The description of the error. | ||
| The URL that was visited prior to the failure occurring. | ||
| The first 100 characters of the data associated with the event. | ||
| The first 450 characters of the data associated with the event. |
Items
The Items table contains the URLs of items.
Key | Column | Description | |
|---|---|---|---|
|
| The unique identifier of the item. | |
| The URL of the item. |
Keywords
The Keywords table contains both the external and the local search terms used.
Key | Column | Description | |
|---|---|---|---|
|
| A generated surrogate key used to reference entries in this table. | |
| The search term used. |
Languages
The Languages table contains the names of the languages used.
Key | Column | Description | |
|---|---|---|---|
|
| A generated surrogate key used to reference entries in this table. | |
| The English name of the language. |
PageEventDefinitions
The PageEventDefinitions table contains the definitions of both the predefined and custom analytics page events.
Key | Column | Description | |
|---|---|---|---|
|
| The unique identifier of the page event definition. | |
| The name of the event. | ||
| The engagement value associated with the event. | ||
| Indicates whether the event is a system event. | ||
| Indicates whether the event provides feedback to content authors. | ||
| Indicates whether the event is a goal. | ||
| Indicates whether the event is an error. | ||
| The taxonomy classification of the page event definitions. This maps to the folder where your page events are stored. | ||
| The taxonomy classification of the page event definitions. This maps to the folder where your page events are stored. | ||
| The taxonomy classification of the page event definitions. This maps to the folder where your page events are stored. | ||
| The taxonomy classification of the page event definitions. This maps to the folder where your page events are stored. | ||
| The taxonomy classification of the page event definitions. This maps to the folder where your page events are stored. | ||
| The taxonomy classification of the page event definitions. This maps to the folder where your page events are stored. |
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 | |
|---|---|---|---|
|
| A surrogate identifier derived from the | |
| The unique identifier of the entry in the | ||
| The date for the measurements of this record. | ||
| The unique identifier of the entry in the | ||
| The unique identifier of the entry in the |
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 | |
|---|---|---|---|
|
| A surrogate identifier derived from the | |
| The unique identifier of the entry in the | ||
| The date for the measurements of this record. | ||
| The unique identifier of the entry in the | ||
| The unique identifier of the entry in the |
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 | |
|---|---|---|---|
|
| The unique identifier of the Sitecore item that contains the original representation of a segment. | |
| The unique identifier of the Sitecore item that contains the original representation of the dimension that defines the type of this segment. | ||
| The serialized representation of the Sitecore item that this segment refers to as its filter. | ||
| The date after which this segment is expected to have processed all interactions using live aggregation. | ||
| The status codes of segments during the rebuild process:
|
ReferringSites
The ReferringSites table contains the URLs of the websites that link to your site.
Key | Column | Description | |
|---|---|---|---|
|
| A generated surrogate key that is used to reference entries in this table. | |
| 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 | |
|---|---|---|---|
|
| A generated surrogate key used to reference entries in this table. | |
| 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 | |
|---|---|---|---|
|
| The unique identifier of the contact. | |
|
| The unique identifier for the cluster the contact belongs to. | |
| The date and time when the record was saved. | ||
| 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 | |
|---|---|---|---|
|
| The unique identifier for the cluster. | |
|
| The name of the feature that is used for classification within the cluster. | |
| The date and time that the record was saved. | ||
| The unique identifier of the test set. | ||
| 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 | |
|---|---|---|---|
|
| The GUID of the corresponding path map item ID from the Sitecore master database. | |
| Contains the serialized JSON of reduced set of properties of a given path map definition item, such as | ||
| The deploy date of a given path map definition item. | ||
| The status of the path map definition. | ||
| 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 | |
|---|---|---|---|
|
| Foreign key reference to the | |
|
| The start of the date interval that the tree blob corresponds to. For example: | |
|
| The end of the date interval that the tree blob corresponds to. For example: | |
| The serialized blob containing the tree object providing the underlying data for a path map and for a given time scale. | ||
| 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 | ||
| Total amount of engagement value for all nodes in a tree object. | ||
| 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 ‘ | ||
| 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. |
TrafficTypes
The TrafficTypes table contains the definitions of channels through which contacts discover content.
Key | Column | Description | |
|---|---|---|---|
|
| A predefined surrogate key that is used to reference entries in this table. | |
| The name of the channel. |
VisitorClassification
The VisitorClassification table contains a description of the visitor classification values.
Key | Column | Description | |
|---|---|---|---|
|
| A predefined surrogate key used to reference entries in this table. | |
| The name of the visitor classification. |
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:
Key | Column | Description | |
|---|---|---|---|
|
| 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. | |
| The date and time that the data was processed. |
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 |
|---|---|
| Enables you to query the |
| Enables you to query the |
| Enables you to query the |
| Enables you to query the |
| Enables you to query the |
| Enables you to query the |
| Enables you to query the |
| Provides backwards compatibility. |
| Enables you to query the |