How Demantra Calculates and Stores Data

As an implementor, you should understand how Demantra calculates and stores data, because this can affect how you set up your solution. This chapter provides an overview of most important details.

This chapter covers the following topics:

• How Data Is Stored
• How Data Is Calculated at the Lowest Level
• How Data Is Calculated at Higher Levels
• How Proportions Are Chosen
• How Proportions Are Used
• How Splitting Handles Null Values
• How Splitting Handles Zero Values
• When and How Data Is Saved to the Database

How Data Is Stored

In order to understand how Demantra works, you should be aware of two central facts about how Demantra stores data:

• Demantra stores data only at the lowest possible level.

• Demantra stores data only where the sales data is non-null.

These facts have wide-ranging implications. The following subsections expand these two facts, and the sections after that provide further details.

Data Is Stored at the Lowest Level

Demantra stores data only at the lowest possible item level, lowest possible location level, and base time unit. This has the following implications:

• When data is viewed or exported at a higher level, that data must be calculated.

• When data is edited or imported at any higher level, Demantra must calculate and then write the data for the appropriate lowest-level members.

• If the Analytical Engine generates a forecast at a higher level, Demantra must split that forecast and write it into the appropriate lowest-level members.

Data Is Stored Only Where the Sales Data Is Non-Null

Theoretically there might be a very large number of item-location combinations, but some items just might not be sold at some locations. It would be incorrect for Demantra to assume that all items could be sold at all locations during any time buckets. If Demantra assumed that, the result would be a very inaccurate forecast.

Instead, Demantra assumes (by default) that if there were no sales for a given combination during a specific time bucket, sales were not possible for that combination and time. Specifically, Demantra uses this assumption as follows:

• The Demantra database does not, by default, contain records that represent null sales. That is, for a given item-location combination, if no sales occurred during a given time bucket, Demantra does not contain a record for that combination and time bucket.

• If there is no sales record for a given combination and time bucket, Demantra ignores that combination and time bucket when performing various operations, such as forecasting.

In some cases, however, a null value for sales really does mean zero sales. Depending on the context, you can force Demantra to treat null values as zero values.

How Data Is Calculated at the Lowest Level

In general, the definition of a series also specifies how to calculate data at the lowest level, in the case when data changes at a higher level. Data can potentially change at a higher level either when it is imported at a higher level or when users edit a series while displaying data at a higher level.

Each series can be configured as proportional or non-proportional.

• If a series is proportional, the parent value is split among the child members according to the proportions of those child members.

• If a series is non-proportional, the value for each child member is set equal to value of parent.

Other series are neither proportional nor non-proportional. Data for these series should not be changed except at the lowest level. For details, see “Summary of Calculation Options”.

How Data Is Calculated at Higher Levels

The definition of a series specifies how to calculate data at any level. A series can have either or both of the following expressions:

• A server expression, which aggregates data from the lowest level in the database.

• A client expression, which calculates the series data, based on other series at the same level. If a series has a client expression, that series is automatically made non editable.

Server Expressions: Aggregation from Lower Levels

A server expression is an SQL expression that calculates the series data at any level by aggregating the associated lowest-level data. A very common server expression has the following form:

sum (table_name.update_column_name)

Here table_name.update_column_name is the table and column that stores data for this series.

If you view a combination at the lowest level, this expression reads the series value for one row in the given table. On the other hand, if you view a combination at a higher level, this expression sums the series values associated with all the rows that correspond to the child members.

• Parent: muffins

  • Expected: 200

  • Actual: 220

    Note: The values for Expected and Actual series are calculated by aggregating from child members

• Child: apple muffins

  • Expected: 100

  • Actual: 110

• Child: blueberry muffins

  • Expected: 100

  • Actual: 110

Similarly, if you view a combination at a larger time bucket, this expression sums the series values associated with all the rows that correspond to the smallest time buckets for the combination.

A server expression can also aggregate by averaging or taking the minimum or maximum value of the child members.

Client Expressions: Calculations within a Level

A series can also have a client expression, which calculates data in a different way. In contrast to server expressions, a client expression always refers to data at the same level. You use client expressions to calculate numbers that cannot be calculated by aggregation from lowest-level data. For example, consider the following client expression for a hypothetical series called ErrorSquared:

(Expected - Actual)*(Expected - Actual)/(Expected*Expected)

For a given combination at a given time bucket, this expression calculates the ErrorSquared series directly in terms of the Expected and Actual series for the same combination at the same time bucket. As you can see from the following example, it would not be possible to compute this series by aggregating lowest-level members:

• Parent: muffins

  • Expected: 200

  • Actual: 220

  • ErrorSquared: 0.0001

    Note: The value for the ErrorSquared series is calculated at this level, not aggregated from child members

• Child: apple muffins

  • Expected: 100

  • Actual: 110

  • ErrorSquared: 0.01

• Child: blueberry muffins

  • Expected: 100

  • Actual: 110

  • ErrorSquared: 0.01

In addition to formulas as these, you can use client expressions to perform the following kinds of computations, which are not possible with server expressions:

• Conditional expressions, including nested conditional expressions

• Expressions that refer to data at earlier or later time buckets

Using Both Server and Client Expressions

A series can have both a server expression and a client expression. The client expression always takes precedence. Therefore, if a series has both expressions, the client expression is usually of the following form:

If (condition, client-expression-value, series-name)

Here series-name is a reference to the same series to which this client expression applies. This reference directs Demantra to the server expression that this series uses. Depending on whether the condition is true for a given cell, this expression returns either the client expression value or server expression value.

How Proportions Are Chosen

Demantra provides three general ways to specify the relative proportions of different combinations:

• Actual proportions. This option splits higher-level data according to the proportions of the Demand series. This is an option when importing data.

• Proportions of a reference series. When you configure a series as proportional, you also specify a reference series (the Proportion Calculation series). You typically use one of the following series:

  • Demand (suitable for a historical series)

  • Final Plan (suitable for a forecast series)

  This option is available when you define a series and is used when data for that series is edited at an aggregated level.

• Matrix proportions, which are stored proportions that Demantra calculates and saves. (The mechanism that performs the calculation is called proport.) The calculation is based upon the demand, but also considers recent average demand, month-to-month variations, and so on. See Chapter 12, “Proport”. These proportions are available in multiple cases:

  • When importing data.

  • Automatically used when forecast must be created at higher level.

• When a user performs a chaining operation (data copy) at a higher level. A user performs chaining in order to use existing data for a given combination as a basis for a new combination.

How Proportions Are Used

The following figure shows an upper-level member, ABCD, and its four child members. It also shows a reference series (Sales), and it shows the value of that series for each child member, all within the same time bucket.

Now suppose that series S is a proportional series that uses Sales as its reference series, and suppose that the value of S is changed to 350 for the parent member. In this case, the series S is split across the child members as follows:

How Splitting Handles Null Values

Now consider a case where the reference series has a null value for one of the child member. The proport mechanism ignores that member, as follows:

How Splitting Handles Zero Values

Now let us consider two cases where child members have zero values. In the first case, the reference series is zero for one of the child members, but has non zero numbers for other child members. Any member with 0 sales receives 0% of the split, as follows:

Notice that member A that has a null value for the reference series; for this member, the value of series S is null, rather than 0.

In the second case, none of the child members has a non zero value. In a case like this, the parent value is split equally among all members that have zero values for the reference series.

As always, if a child member has null for the reference series, the proport mechanism ignores that member entirely.

When and How Data Is Saved to the Database

A series may or may not be stored in the database. If it is stored, its data is saved in the series update field. Because you can potentially have different values from splitting at different levels, it is important to understand when and how Demantra saves data to the update field for a series.

For a series that has an update field, Demantra saves data to that update field as follows:

• When you import data for that series, Demantra splits it down to the lowest level and saves it in the update field.

• When a user edits data for that series, Demantra splits it down to the lowest level and saves it in the update field. If the user is working with the desktop products (Demand Planner or Demand Replenisher), the MANUALS_INS procedure performs the splitting and saving. If the user is working with the Web products, no database procedure is needed.

  If this user changes causes a change to the value of another series in the worksheet, Demantra splits that other series value down to the lowest level and saves it in the update field for that other series.

  Demantra ignores any series whose values have not been changed.

If the series also has a client expression, Demantra also saves data to that update field as follows:

• When a user runs a worksheet that contains the series, Demantra evaluates the client expression for that series, computing the values at that level. Demantra then splits it down to the lowest level and saves it in the update field. If the user is working with the desktop products (Demand Planner or Demand Replenisher), the MANUALS_INS procedure performs the splitting and saving. If the user is working with the Web products, no database procedure is needed.

• When the Business Logic Engine evaluates a worksheet, Demantra evaluates the client expression for all series in that worksheet, computing the values at that level. Demantra then splits the values down to the lowest level and saves them in the update fields of the appropriate series. In this case, the MANUALS_POPULATE_INS procedure performs the splitting and saving.

Caution: Demantra does not automatically launch the database procedures. Instead, you must make sure to schedule the procedures to run periodically or otherwise automate running them. Use the Workflow Engine. See “Workflows”.