Key Tables

This chapter provides reference information for some of the most important tables in Demantra, especially the data fields used by or written by the Analytical Engine. Unless otherwise noted, this information applies to all Demantra products.

This chapter covers the following topics:

Sales_Data

The following table lists the most important fields in the sales_data table. The Analytical Engine reads from and writes to some of these fields, which you use mainly to create series.

Field Name Use Field Purpose
item_id Read-only Unique identifier for the item. Together, item_id, location_id, and sales_date form the primary key for rows in the sales_data table.
location_id Read-only Unique identifier for the location.
sales_date Read-only Date for this record.
item_price Read-only (imported) Price for this item, at this location, on this date.
actual_quantity    
manual_fact    
manual_stat    
salesplus Read-only (imported) The demand used by the Analytical Engine.
orders    
FORE_0, FORE_1, FORE_2, ... Read-only The forecasts generated by the Analytical Engine. The Analytical Engine cycles through these columns. Each time, it writes the current forecast into one column (overwriting the oldest forecast). The Analytical Engine then adds a row to the forecast_history table that describes this forecast and that indicates which column it is stored in.
OBS_ERROR_STD User input Specifies how the Analytical Engine should consider this observation when fitting each engine model. Specify a positive number, to be used as a weight for this observation. Use 1 to treat this observation as a standard observation.
This field is ignored unless UseWeightedRegression is specified as yes (1).
UseWeightedRegression is an engine parameter; see “Engine Parameters” .
outlier Read-only Indicates whether the Analytical Engine has marked this row as an outlier.
regime_change Read-only Indicates whether the Analytical Engine has marked this combination as an regime change.
approve User input  
final approve User input  
batch Read-only  
PD1, PD2, PD3, PD4, PD5, PD6, PD7 No Available only in a daily system. Daily proportions for this combination, for different days of the week.
PW1, PW2, PW3, PW4, PW5, PW6 No Available only in a weekly or daily system. Weekly proportions for this combination, for different weeks of a month. When calculating these proportions, Demantra factors in the number that this week has.

Mdp_matrix

The following table lists the most important fields in the mdp_matrix table. You can use these fields to create series or levels that provide information about different combinations or that enable the user to manipulate different combinations.

Field Name Use Field Purpose
item_id Read-only Unique identifier for the item. Together, item_id and location_id form the primary key for rows in the mdp_matrix table.
location_id Read-only Unique identifier for the location.
aggri_98 User input Specifies whether to aggregate demand for this item-location combination, if this combination is young. See “prediction_status”.
aggri_99 User input Specifies whether to aggregate demand for this item-location combination, if this combination is dead. See “prediction_status”.
delta User input Used in the proport calculation as in the following example:
P1 = glob_prop * delta + (monthly demand) * (1 - delta)
delta_d User input Specifies the day-to-day smoothing of the daily proportions, which are calculated as in the following example:D1 = (actual average for day 1) *delta_d + (weekly proportion) * (1- delta_d) Here D1 is the proportion for the combination for the first day of the week.
delta_w User input Specifies the week-to-week smoothing of the weekly proportions, which are calculated as in the following example:PW1 = (actual average for week 1) * delta_w + (monthly proportion) * (1 - delta_w) Here PW1 is the proportion for the combination for the first week of the month.
do_aggri User input Specifies whether to perform aggregation on this item-location combination. Choose one of the following values:
0—Will Not Be Used in Aggregation
1—Will Use Aggregation
do_fore User input Specifies whether to perform forecasting on this item-location combination. Choose one of the following values:
0—Do Not Do Forecast.
1—Do Forecast (the default).
2—Do Zero Forecast. This combination is not used in aggregation.
dying_time Yes If no sales occurred during the length of time specified by dying_time, the combination will be marked as dead. If this field is null for a given combination, Demantra uses the dying_time parameter instead.
glob_prop Read-only Rolling average demand for this combination, averaged over the recent past, as specified by the length of time given by the hist_glob_prop setting.
hist_glob_prop User input Number of base time buckets worth of data to use to calculate the rolling average, glob_prop, for this combination. If this field is null for a given combination, Demantra uses the hist_glob_prop parameter instead.
is_fictive Read-only Indicates whether this combination is real or fictive. This field is set automatically by Demantra. It has one of the following values:
1 means that the combination was created through Member Management and no data has been loaded for it yet.
0 means that there are sales for this combination.
2 means that there are no sales for this combination.
3 means that an error occurred while loading this combination or while redefining this combination. (When Demantra loads a new combination or changes the definition of a combination, it temporarily sets is_fictive equal to 3. When Demantra finishes the action, it then resets is_fictive equal to 0 or 2.)
The engine does not consider the is_fictive setting.
missing_all_sources Read-only Used during chaining. Indicates whether the source data for this combination is complete. For each combination, this field has one of the following values:
Yes means that there is no data for this combination.
Partial means that there is data for this combination only for some of the dates.
No means that there is data for this combination for all dates.
missing_some_sources Read-only Used during chaining. For each combination, this field indicates one of the following:
Yes means that there is no data for one of the items in the combination.
Partial means that there is data for this combination only for some of the dates.
models Read-only Indicates the engine models that the Analytical Engine used when forecasting this combination, during the most recent engine run. Demantra uses a single letter to indicate each model:
A: ARLOGISTIC
B: BWINT
C: CMREGR
D: DMULT
E: ELOG
F: FCROST
G: LOGISTIC
H: HOLT
K: ICMREGR
J: IREGR
L: LOG
M: MRIDGE
N: NAIVE
R: REGR
T: NAIVE HOLT
V: ARIX
X: ARX
To specify multiple models, Demantra concatenates the letters together. For example, BDF means the BWINT, DMULT, and FCROST models.
For information on engine models, see "Theoretical Engine Models” .
new_member Read-only Specifies whether to run proport on this combination; used by the Run_full_matrix_proport parameter.
outlier Read-only Indicates whether the Analytical Engine has marked this combination as an outlier, for any time bucket.
P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12 Read-only Monthly proportions for this combination. Each proportion represents the level-adjusted sales for this combination, for each month of the year, as averaged over multiple years
PD1, PD2, PD3, PD4, PD5, PD6, PD7 No Available only in a daily system. Daily proportions for this combination, for different days of the week.
post_effect User input PE only. For each combination, specifies how the Analytical Engine should search for the effects of any given promotion, after the end of that promotion. Specify this as the number of base time buckets after the end of a promotion.
Null is treated as zero.
Searching for post-promotional effects can slow the engine down, so Oracle recommends doing this only for a few combinations. For those combinations, Oracle recommends specifying a value of 2–4, to avoid possible overlaps between different promotions.
pre_effect User input PE only. For each combination, specifies how the Analytical Engine should search for the effects of any given promotion, before the start of that promotion. Specify this as the number of base time buckets before the start of a promotion.
Null is treated as zero.
Searching for pre-promotional effects can slow the engine down, so Oracle recommends doing this only for a few combinations. For those combinations, Oracle recommends specifying a value of 2–4, to avoid possible overlaps between different promotions.
prediction_status Read-only Controls how the Analytical Engine uses this combination. Each combination has one of the following prediction status values:
96 (No Forecast) — This status means that the Analytical Engine will completely ignore this combination.
97 (Create Zero Forecast) — A user has set do_fore equal to 2 manually. This status means that the Analytical Engine will insert a zero forecast for this combination but otherwise ignore it.
98 (Young) — Sales for this combination are too new to be used for prediction.
99 (Dead) — Sales for this combination are not recent enough to be used for prediction.
1 (Live or Active)—Neither young nor dead.
The Analytical Engine ignores any young or dead combinations, except when it is necessary to aggregate. In case of aggregation, Demantra considers the do_aggri, aggri_98, or aggri_99 flag of the combination.
Demantra sets the prediction_status indicator as follows.
For fictive combinations (is_fictive = 1), Demantra automatically sets the prediction status to 98.
For real combinations (is_fictive equal to 0 or 2), Demantra uses the following rules:
If do_fore is 0, then prediction_status will be 99.
If do_fore is 1, then prediction_status is set as follows:
If the combination is dead because of the dying_time parameter, then prediction_status is set to 99.
If the combination is young because of the mature_age parameter, then prediction_status is set to 98.
Otherwise, the prediction_status is set to 1.
If do_fore is 2, then prediction_status will be 97.
dying_time and mature_age are engine parameters; see “Non-Engine Parameters”.
prop_changes Read-only Specifies whether to run proport on this combination; used by the Run_full_matrix_proport parameter.
PW1, PW2, PW3, PW4, PW5, PW6 No Available only in a weekly or daily system. Weekly proportions for this combination, for different weeks of a month. When calculating these proportions, Demantra factors in the number that this week has.
level_id Read-only The strategy of the forecast tree where the forecast for this combination was generated.
item_node Read-only Item member in that level.
loc_node Read-only Location member in that level.

Promotion_Data

PE only. The following table lists the most important fields in the promotion_data table. The Analytical Engine reads from and writes to some of these fields, which you use mainly to create series that show the forecast results.

Field Name Use Field Purpose
item_id Read-only Unique identifier for the item. Together, item_id, location_id, sales_date, and promotion_id form the primary key for rows in the promotion_data table.
location_id Read-only Unique identifier for the location.
sales_date Read-only Date for this record.
promotion_id Read-only Unique identifier for a promotion.
is_self Read-only Equals 1 if the lifts in this row (uplift, pre- and post-effect, and switching effects) are associated with the promotion and date of this row. See “Is_Self”.
fore_0_uplift Read-only Basic lift due to this promotion, during the dates of the promotion.
fore_0_sw_channel Read-only Effects of channel switching as described in “Switching Effects”.
fore_0_store Read-only Effects of store switching.
fore_0_product Read-only Effects of store switching.
fore_0_brand Read-only Effects of brand or category switching.
fore_0_pre_effect Read-only Pre-promotional effect due to this promotion.
fore_0_post_effect Read-only Post-promotional effect due to this promotion.
*norm* Read-only Normalized versions of the forecast data, if requested via the NormalizeResults parameter. When the Analytical Engine normalizes its results, it re-scales the historical engine results so that the observed baseline values are preserved.
NormalizeResults is an engine parameter; see “Engine Parameters”.

Is_Self

In any given row of the promotion_data, the Analytical Engine uses the is_self field to indicate whether the lifts in that row are associated with the promotion and date of that row. (Specifically, this refers to the uplift, pre and post-effect, and switching effects.) Consider the following example, with a combination that has a promotion (Promotion A) on it for some dates. For simplicity, this graph shows just the uplift on this combination due to the promotion. Each time point in this graph corresponds to a row in promotion_data. The notes at the bottom of the figure show the value of is_self for different times.

the picture is described in the document text

As you can see, during the dates of the promotion itself, is_self is 1. Outside those dates, is_self is 0 because these dates fall outside the promotion.

Now consider another combination and the same period of time. The sales for this other combination were lifted, even though the promotion was not applied to this combination. The following graph shows the uplift on this combination, due to Promotion A (which ran only on the other combination). Again, each time point corresponds to a row in promotion_data:

the picture is described in the document text

For all these rows in promotion_data, is_self is 0 because these lifts are due to a promotion that did not run on this combination.