At the foundation of the Analytics reporting database are fact tables and dimension tables.
Fact tables contain metrics that change frequently. Each row of a fact table contains a time stamp and a value for the metric it represents. In the reporting database, fact table names begin with
fct_, followed by the Resource Manager alias name converted to lower case letters.
Most fact tables are loaded with metrics extracted from RRD files on the Resource Manager collector. (An exception is the
fct_event table, which is loaded with data extracted from the Resource Manager events database.)
Dimension tables contain persistent or slowly changing entities, and offer a way to group and filter facts. In the Analytics reporting database, dimension tables contain data extracted from the Resource Manager object database. Dimension table names are prefixed with
The Analytics reporting database schema follows popular conventions of data warehousing. Each fact table is related, through foreign keys, to multiple dimension tables.
The schema allows the domain or report author to easily formulate a SQL query by first choosing a fact, and then choosing one or more dimensions by which to group or filter the results.
The fact tables in the reporting database contain all of the values extracted from RRD files. Typically, these values are extracted at a resolution of one value every minute or every five minutes. This causes two problems. First, the fact tables contain many rows, which can slow down queries. Second, joining two fact tables (for example, the tables for input and output octets) is difficult because the timestamps are not guaranteed to match.
To solve these problems, the reporting schema includes hourly aggregate tables for each performance fact table. You should use aggregate tables if the report needs one-hour resolution or less (for example, for daily reports).
Aggregate table names are prepended with
agg_. Aggregate tables follow a similar schema as fact tables, and for the most part can be swapped in and out of the same query with the corresponding fact table.