Units, Indexes, and Exchange Rates

This chapter describes units and related concepts, outlines the primary configuration options, and summarizes the available tools.

This chapter covers the following topics:

Introduction

At any time, a worksheet uses one unit of measure, which is used by most of the series in that worksheet. The user can switch to another unit of measure; any series that uses a unit of measure is correspondingly multiplied by the appropriate conversion factors. For example, a worksheet can express sales and forecast in units or in cases or dollar value.

Note: You do not need to use units in this way. You can instead hard code the unit into a series definition, so that it always expresses, for example, the buyback per case. Whatever your choice is, be sure to give useful names and hints to the series.

Similarly, at any time, a worksheet can use one index or exchange rate, which is used by any series that express financial quantities. The user can switch to a different index (such as CPI) or exchange rate, and the worksheet automatically multiplies those series by the index or exchange rate.

Unit Conversion Data

The imported data contains the item quantity per sales record, expressed as the number of units sold. Note that you can rename units.

The imported data also includes the unit price, which depends on the item, location, and date. You use the item price as a conversion unit, to represent monetary values.

Typically, you define additional units of measure, of two general kinds:

Size Units

When you define a size unit, you specify the following:

This means that the SKU 109784 has 0.01 cases per unit, or inversely, 100 units per case.

When you define the Case unit, you would specify the Cases column of t_ep_sku as the source of the conversion factor for this unit.

Monetary Unit

When you define a monetary unit, you specify the following:

How Units Are Used

The token #UNIT# represents the unit conversion factor. You can include this token within the server expression for a series, which should have the following general form:

quantity * #UNIT#

Within a worksheet, this token is automatically replaced by the conversion factor that corresponds to the unit that the worksheet is currently using. For example, if the Demand was 1200 units, and if the worksheet is using cases instead, then Demand is displayed as 12 cases.

To configure a series to use units, do either of the following:

Time Units

Any Demantra solution has a base time unit, such as weeks or months. Demantra provides some larger predefined time units, and you can add others. In general, there are two types of time units:

Note that by default, in any worksheet, the date and label for a given bucket is the first date in that bucket. Within a worksheet, another date format can be used.

Data-Dependent Time Units

The following example represents rows in the Inputs table. It shows a set of dates from a weekly system and shows how those dates are mapped into quarters and 4-4-5 periods. (A 4-4-5 time system creates quarters that consist of a four-week “month,” followed by another four-week “month,” and then followed by a five-week” month.” In practice, 4-4-5 calendars vary from company to company.) The second and third columns show the bucket numbers associated with each date, depending on the date system.

Date Bucket number when quarters are used Bucket number when 4-4-5 periods are used
1/3/05 100 122
1/10/05 100 122
1/17/05 100 122
1/24/05 100 122
1/31/05 100 123
2/7/05 100 123
2/14/05 100 123
2/21/05 100 123
2/28/05 100 124
3/7/05 100 124
3/14/05 100 124
3/21/05 100 124
3/28/05 100 124
4/7/05 101 125

The first thirteen dates belong to a single quarter, and the last date belongs to the following quarter. The first four dates belong to the first 4-4-5 “month” and so on.

Viewing Calendar Months in a Weekly System

Each Oracle Demantra implementation has a core time resolution definition. This definition drives the resolution of data kept in the system, which in turn drives the granularity of data available to users and internal processes. This data granularity serves as the smallest building block upon which all time management and display is done. Available data granularities are day, week, or calendar month. For more information, see Time Units.

If an organization wants to support both weeks and calendar months, then using the weekly time resolution is not suitable because weeks do not aggregate wholly into calendar months. To overcome this limitation, you may decide to upgrade the data model to support viewing weekly data aggregated by calendar month. Different worksheets display data in the selected aggregations, and allow for a variety of uses based on business roles. When you upgrade the data model to support weekly data by calendar month, the following changes occur:

Note: To view calendar months in a weekly system, both new and existing users should run the upgrade procedure.

Note: Upgrading the data model will increase data rows in the system by approximately 20%. If your business is not interested in viewing weekly data by calendar month, then you may continue using the current data model without experiencing any additional overhead associated with calendar month support.

Enabling Demantra to support viewing weekly data by calendar month is done using the UPGRADE_TO_CAL_MONTH procedure. For more information, see Enabling Calendar Month Support.

Time Aggregations when Viewing Weekly Data by Calendar Month

The following time aggregations are added when you upgrade the data model to support viewing monthly data in a weekly system:

Data is stored at an enhanced weekly aggregation, where weeks spanning multiple calendar months are split into periods. Worksheets with time aggregations of fiscal month, quarter, and year aggregate and display weekly data. Worksheets with the calendar month time aggregation will aggregate all weeks and periods belonging to each month. When data is split based on a proportional series, then data is allocated to each period within the week based on each period's proportions within the proportional series.

Note: Use caution when using the Lowest Period time aggregation for forecasting. Demantra's forecasting engine assumes that historical and future periods have an approximately equal length, and views all periods in the same manner.

To solve this limitation, the forecasting engine scales any period which is less than a whole week to match weeks. The scaling process modifies both historical demand and key causals for the period in order to approximate the period as a full week. When a forecast has been generated, the values for the Lowest Period time aggregation are scaled back to their original values. Scaling is controlled using the EngineScaleInput parameter. For more information, see System Parameters.

Parameters and Dates

Period-based parameters refer to the number of whole base time units. If you use a weekly time aggregation, then system parameters store values by weekly periods. If periods contain sub-weeks, then the analytical engine automatically converts system parameters to store these sub-week periods instead of weeks.

For example, a weekly system has a lead of 52 weeks. These 52 weeks contain 63 sub-week periods, since some weeks are split among different calendar months. When calculating the forecast, Demantra automatically converts the Lead parameter to 63. All conversions are written to the engine log, so that you may view the original and resulting values.

The following parameters are converted automatically by the engine to account for expanded forecasting periods:

Disaggregation to Dates

When an update occurs at a time aggregation higher than the base time aggregation, the update must then be allocated between different periods. Because some weeks are divided into unequal periods, each period uses a weighting to disaggregate the data between periods. For weekly systems, each period receives a weight based on the number of days in the period, resulting in values between one and seven. For daily systems, each period (day) receives a weighting of one. For monthly systems, each period (month) also receives a weight of one.

Allocation Series

The default method for allocating series between different time periods is determined by the number of days that fall in each period. This allocation method is set globally, but may be modified and contain local overrides.

Demantra uses the following series to control the allocation between weeks. These series are non-proportional and should only be edited when viewing information at the Lowest Period time aggregation:

Enabling Calendar Month Support

Use the UPGRADE_TO_CAL_MONTH procedure to update the Demantra data model to support viewing weekly data by calendar month. This procedure locates weeks that fall within two months, and splits them into two rows in the database. It also splits existing sales data, general level data, and time dimension data (INPUTS table). Each row represents the portion of the week that falls within each month. For example, a schema row beginning Monday, 29th 2010 will be split into two periods: November 29th (containing two days) and December 1st (containing five).

Before you Begin

  1. Backup your Demantra schema.

  2. Perform a manual review of the SPLIT_DATES_DATA_COLUMNS table and confirm whether or not you want to split each data column.

  3. Use the UPGRADE_TO_CAL_MONTH procedure to populate the table SPLIT_DATES_DATA_COLUMNS:

    exec upgrade_to_cal_month.scan_data_table_columns;

    Note: This procedure should be run every time that the model is changed.

  4. Once populated, the SPLIT_DATES_DATA_COLUMNS table contains numeric columns that directly relate to application series, plus any other numeric columns not identified as application columns. Whether or not a value gets split is determined by the value in the SPLIT_VALUE_Y_N column. Possible values are:

    • Y: (Yes) The upgrade procedure will split this value.

    • N: (No) The upgrade procedure will not split this value.

    • U: (Unknown) The upgrade procedure could not determine if the value should be split. Non-series numeric columns that have not been identified as application columns will have this value and are not split.

    Confirm the value for each table entry before proceeding to run the Upgrade procedure.

Setting the Queue Size

In order for the split process to work effectively the splitting is run in separate parallel jobs. The number of queues is controlled by the SpiltDataMaxQueues parameter. As an approximate guideline, the number of parallel queues should not exceed the number of CPUs in your Demantra deployment.

To set the queue size:

  1. Update the SplitDataMaxQueues parameter in the DB_PARAMS table. For more information, see System Parameters.

  2. Run the Cal_Month_Split_Set_Queues procedure to distribute the queue to the data tables:

    exec upgrade_to_cal_month.cal_month_split_set_queues;

Note: You do not need to rerun the full data column scan after setting the queue size.

Running the Split Data Procedure

Use the UPGRADE_TO_CAL_MONTH procedure to start the split process:

exec upgrade_to_cal_month.main(start_date,end_date);

Where start_date is the first date in the Inputs table that the procedure will split, and end_date is the last date in the Inputs table that the procedure will split. For example, the following command splits all data between January 1, 2010 and January 1, 2011:

exec upgrade_to_cal_month.main('01-JAN-2010','01-JAN-2011');

Specifying "null" for the start_date tells Demantra to split all dates from the start of the database. Similarly, specifying "null" for the end_date tells Demantra to split all dates to the end of the database. For example, the following command splits the entire Inputs table:

exec upgrade_to_cal_month.main(null,null);

Note: The split dates process may be run multiple times for the same date ranges. can be repeated again for the same time ranges. Only unsplit data and dates are processed.

Reviewing Split Procedure Results

Use the following Demantra tables to verify the outcome of the split process:

The possible status values in these tables are:

Status Code Explanation
1 Complete
2 Ready
3 Running
4 Failed
5 Timeout
6 Killed
7 Finalized

Setting and Modifying the Base Time Unit

The base time unit is used by the Data Model to aggregate the source data to the specified time bucket size. Allowed settings of the base time unit (time bucket size) are:

the picture is described in the document text

Impacts of Changing the Base Time Unit

If the time bucket is re-configured, the time aggregation set for all worksheets is modified to match the new time aggregation. A review of all worksheets is strongly recommended. See Worksheets.

After making changes, the Data Model should be upgraded, not Rebuilt, with the Run Time Bucket option checked. See Building the Data Model and Manipulating Existing Data Models.

The erased member and fact data in Demantra must then be downloaded again. See Loading the Data into the Data Model.

Integration profiles are required to be redefined by the user, if the unit of time specified therein becomes invalid. For details, see Loading Series and Promotions.

The following time aggregations are only available if the base time unit is set to weeks:

Pre-configured calendars support dates from 1995 to 2030. For more information, see Viewing Weekly Data by Calendar Month.

Common changes to the Base Time Unit

The Business Modeler allows the Demantra System Administrator to change the base time unit at any time after initial installation. Common changes include:

To set or change the Base Time Unit

Prerequisite

Install the Business Modeler.

  1. Navigate to the data model.

    Business Modeler > Data Model > Open Data Model

    The Select Time Bucket window appears.

  2. Select the Base Time Unit from the time bucket list of values. The default value is: week

  3. If the Time Bucket field is set to week, then choose the day that represents the starting day of the week from the First Day Of Week list of values. The default value is Monday. User may change the default but must then rebuild the model.

  4. If the Time Bucket field is set to week, then select the Aggregation Method from the list of values to determine whether events that occur mid week are aggregated to the start date or the end date of the weekly time bucket.

  5. Save your work.

  6. Click OK.

Unit-Level Association

In Demantra, you associate each unit with the levels where it makes sense to use that unit. For example, a relatively small unit might make sense only at lower levels.

Demantra uses this association within worksheets. If a worksheet contains three levels, for example, then only the units associated with those levels can be used in that worksheet.

Indexes and Exchange Rates

Monetary units of measure can use financial indexes and exchange rates. This means that when users display data in a worksheet, they can apply any of those associated indexes or exchange rates.

Each index and exchange rate is stored in a different table, except for the placeholder index (constant, equals one for all dates).

The placeholder index is used to switch a worksheet back to the same monetary units that are used in the imported data. By default this is called dollar $, because monetary values are usually imported in dollars.

Configuration Notes

This section contains configuration notes related to units, indexes, and exchange rates.

Dependencies

Before you can configure units, you will need to load some sample data for items, including unit conversion data.

If a unit requires an index or exchange rate, you must configure that index or exchange rate first.

Tools

Demantra provides the following tools for creating and configuring units:

Tool Purpose/Notes
Data Model Wizard* Can define units, although this wizard provides only a subset of the options.
Configure > Display Units option* Defines units.
Data Model > Global Factors option* Allows you to add columns and values to the Inputs table.
Configure > Configure Units for Levels option* Allows you to associate units with levels.
*These options are in the Business Modeler.