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)

From release 9.0.014

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 and day (historical).

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

Aggregation Levels
Aggregation Tables/Views per Subject Area
Name Aggregate Type Type
H_<subj_area>_15MIN 15-min aggregation Table
H_<subj_area>_DAY Historical daily aggregation Table
I_<subj_area>_15MIN Intraday 15-min aggregation view View
Blended views for reports for different levels of aggregation
Name Aggregate Type Type
IWD_AGG_<subj_area>_15MIN 15-min aggregation View
IWD_AGG_<subj_area>_30MIN 30-min aggregation View
IWD_AGG_<subj_area>_HOUR Hour aggregation View
IWD_AGG_<subj_area>_DAY 15-min aggregation View
IWD_AGG_<subj_area>_WEEK 30-min aggregation View
IWD_AGG_<subj_area>_QRTR Hour aggregation View
IWD_AGG_<subj_area>_YEAR Hour aggregation View
Deprecated views
Name Aggregate Type Type
I_<subj_area>_30MIN Intraday 30-min aggregation View
I_<subj_area>_HOUR Intraday hourly aggregation View
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

Up to release 9.0013

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
This page was last edited on September 30, 2020, at 14:16.
blog comments powered by Disqus