Jump to: navigation, search

Aggregate Tables

Aggregate facts are aggregated representations of the core facts that were described in previous section. There are three main purposes for aggregated facts:

  • Simplified data queries
  • Increased query performance
  • Decreased database size (granular core facts do not need to be stored for an extended period of time)

Each aggregated fact in iWD Data Mart is an aggregation of two dimensions, one of which is always a time interval. iWD Data Mart directly aggregates facts for two time intervals: 15 minutes (intraday and historical) and day (historical).

In addition, those aggregation levels represented in the following figure are supported via database views.

Aggregation Levels

Each aggregated table or view in the Data Mart is postfixed with a time interval: _15MIN, _30MIN, _HOUR, _DAY, _WEEK, _MONTH, _QUARTER, or _YEAR.

Similar to the core facts, intraday aggregations are prefixed with I_ and historical aggregations are prefixed with H_. Blended aggregations are available only for 15-minute, 30-minute, and hourly time intervals, as shown in the following figure.

Blended Aggregation Levels

The following lists all of the possible aggregation tables and views per single aggregation subject area.

Aggregation Tables/Views per Subject Area
Name Aggregate Type Type
I_<subj_area>_15MIN Intraday 15-min aggregation Table
I_<subj_area>_30MIN Intraday 30-min aggregation View
I_<subj_area>_HOUR Intraday hourly aggregation View
H_<subj_area>_15MIN Historical 15-min aggregation Table
H_<subj_area>_DAY Historical daily aggregation Table
H_<subj_area>_WEEK Historical weekly aggregation View
H_<subj_area>_MONTH Historical monthly aggregation View
H_<subj_area>_QUARTER Historical quarterly aggregation View
H_<subj_area>_YEAR Historical yearly aggregation View
<subj_area>_15MIN Blended 15-min aggregation View
<subj_area>_30MIN Blended 30-min aggregation View
<subj_area>_HOUR Blended hour aggregation View

The iWD Data Mart provides aggregate tables and views for the following subject areas:

You must manually activate the plugins for all subject areas (except TASK_CLASSIF_FACT, which is delivered pre-activated) in order enable aggregation. Refer to Activating iWD Aggregate Plugins for more information.

So, for example, the complete set of database tables and views that are provided for the TASK_CAPT_FACT subject area are the following:

Tables
  • I_TASK_CAPT_FACT_15MIN
  • H_TASK_CAPT_FACT_15MIN
  • H_TASK_CAPT_FACT_DAY
Views
  • I_TASK_CAPT_FACT_30MIN
  • I_TASK_CAPT_FACT_HOUR
  • H_TASK_CAPT_FACT_WEEK
  • H_TASK_CAPT_FACT_MONTH
  • H_TASK_CAPT_FACT_QUARTER
  • H_TASK_CAPT_FACT_YEAR
  • TASK_CAPT_FACT_15MIN
  • TASK_CAPT_FACT_30MIN
  • TASK_CAPT_FACT_HOUR

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on December 19, 2017, at 02:26.