Skip navigation
Currently Being Moderated

7.2 Schema

VERSION 3  Click to view document history
Created on: Oct 12, 2011 11:51 AM by Zenoss API - Last Modified:  Oct 12, 2011 12:02 PM by Zenoss API

 2. Schema

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 dim_.

The Analytics reporting database schema follows popular conventions of data warehousing. Each fact table is related, through foreign keys, to multiple dimension tables.

 

Figure 7.1. Data Warehouse Schema Design

Data Warehouse Schema Design

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.

Comments (0)