SQL Server table types used in the reporting database


The different table types used in the reporting database.

The xDB reporting database uses several different types of Microsoft SQL Server tables.

Fact tables contain measurements and metrics used in Sitecore reporting applications. In a fact table, foreign keys allow you to make joins with dimension tables. Each aggregation has a corresponding fact table. Fact tables are not related to each other but can share dimension tables. Each fact table typically consists of a compound primary key and one or more value columns:

  • Primary key - Columns in the primary key describe the different aspects of the interactions that you want to analyze. The primary key can also include columns that make it easier to select individual rows or enable faster inserts.

  • Foreign key - Most columns in the primary key also have a foreign key relationship to a dimension table that provides more detail.

  • Value columns - Contain the collected facts or measurements you want to analyze.

  • Date column - This is the start time of the period in which the event was registered, depending on the date-time configured for each aggregation. For example, if you create an aggregation configured for hourly precision, the date column contains 2014-01-07 13:00:00. This indicates that the event took place between 13:00 and 14:00 on January 7, 2014.

Dimension tables are companion tables to fact tables, 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.

Unlike regular SQL Server tables, views are like virtual tables or stored queries that enable you to quickly create other types of queries. For example, you can use views to reuse joins between multiple tables to make them look like a single table. The data in a view is not stored in the database directly but is only accessible through a SELECT statement.

Trail tables 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. If an error occurs and the same input is processed a second time, the storage operation fails with a primary key violation in the trail table. The error is automatically handled by the aggregation processes. Trail tables can be helpful when troubleshooting.

Processing servers remove old entries from trail tables automatically. The names of trail tables are prefixed with the word Trail followed by an underscore character and each trail table has the same structure.