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.
| 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 |
| 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 |
| 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.
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.
The following lists all of the possible aggregation tables and views per single aggregation 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:
- I_TASK_CAPT_FACT_15MIN
- H_TASK_CAPT_FACT_15MIN
- H_TASK_CAPT_FACT_DAY
- 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