User Tools

Site Tools


Menu

Company Website

Video Tutorials

Documentation for Streamline 4.x

User Guide

1. General Information

2. Starting Up

3. Connecting data

4. Demand and Sales Forecasting

5. Inventory Planning

6. Reference

database-connection-data-types

3.4.1. Data types

Depending on what you are going to do – generate demand plans, perform revenue forecasting, or optimize your inventory, Streamline needs different data to be imported. In this article, we describe the data types required for each case.

Demand Planning

Sales history information is necessary to forecast demand. It is usually represented by a set of sales transactions in the database. Each transaction says what, when, and how much was sold.

The basic Transactional data includes data types shown in the table below.

Data name Description Datatype
Date The transaction date. Date or DateTime
Quantity sold The item amount that was sold in the transaction. Integer
Item code The item identifier, also known as an SKU. String

The data types described below are optional for demand planning, however, they allow you to forecast by location or channel, item category, and location category.

If you need to plan your demand by location (store, warehouse, or region) and by channel (or customer), the Transactional data should be extended with the data types represented in the table below.

Data name Description Datatype Is not given
Default Provided
Location A code of the location where the Item code is sold. It is used to forecast consumption of each Item code in each location. String Empty string NULL or empty string
Channel It represents a channel by which an item is sold. For example, e-commerce, direct sales, distributors, or a single customer.
The Is not given section in the tables means that gaps are allowed in the provided data. The Provided column shows the values that the corresponding SQL-query should return in order to Streamline recognize a gap in the data. The Default column shows the substitution (or the default value) that Streamline uses to fill in the gap.

An empty string for Location or Channel means that there is no location or channel set for this transaction.

To plan your sales by item category (such as a product group) and/or location category (for example, build a hierarchy of Region > City > Store), you should provide the data types shown in the table below.

Data name Description Datatype Is not given
Default Provided
Item category, Item sub-category Used to forecast by item categories. String Category with empty name NULL or empty string
Location category, Location sub-category Used to forecast by location categories.
Empty categories at the end of the hierarchy are perfectly possible: Category1 > Category2 > ⌀ > ⌀ means that the item will be put into the Category2. The symbol ⌀ means an empty category.
Empty categories anywhere else in the hierarchy are also allowed but discouraged. For example, Category1 > ⌀ > Category3. In this case Streamline creates a subcategory with an empty name.

You can import as many categories as you need.

In addition to the Item code and Location, you can import data types displayed in the table below.

Data name Description Datatype Is not given
Default Provided
Item description A description of the item. It is used to better understand what a particular item code means. String Empty string NULL or empty string
Location description A description of the location. It is used to better understand what a particular location code means.
Info field Any additional characteristic of the item (e.g., color, size, texture). You can import an unlimited number of such fields.
Transaction profit The profit obtaned from transaction. This data should extend the Transaction data. This allows Streamline to calculate total gross profit in each data aggregation period and also over the last twelve months. Float
Streamline automatically determines whether lost sales are caused by a stock-out or zero demand if you additionally import on hand history. This will dispose you of the difficulty to set 'Zeros are lost sales' option manually.

Revenue Forecasting

To forecast revenue, the Transactional data should be extended with one of the data types shown in the table below.

Data name Description Datatype
Sales price/unit The price of one unit of an item in the sale transaction. Float
Transaction revenue The amount of the sale transaction. Typically it equals to Sales price/unit multiplied by Qantity sold.

The currency that Sales price/unit or Transaction revenue is given in is taken as the project's base currency. Streamline does not indicate it by any means in its reports.

This information is also used to perform revenue-based ABC analysis and calculate selling price-dependent KPIs such as annual revenue, revenue next year, gross margin, and turn-earn index.

Inventory Planning

To generate purchase/replenishment plans, identify potential stockouts and overstocks, and create purchase recommendations, Streamline must be provided with three types of data:

To import item on-hand into Streamline, you should provide one of three variants of data shown in the table below.

Variant Data name Description Datatype Default
1 Last on hand The item on-hand at the time of import. Integer 0
2 On hand The on-hand remaining after the transaction.
3 On hand change,
Last on hand
On hand change is how much the item on-hand has changed after the transaction. In this case, the transactions query must return not only sales transactions but all of the transactions impacting the item on-hand. These could include inventory receipts, inventory transfers between locations, inventory adjustments, stocktakes, write-offs, customer/supplier returns, and others. 0

The second and third variants import on-hand history into Streamline that allows:

  • Calculating inventory turn-over.
  • Computing the number of days in the period you had run out of inventory (Stockout days).
  • Determining whether lost sales are caused by a stock-out or zero demand automatically. This disposes you of the difficulty to set 'Zeros are lost sales' option manually.

The On hand and On hand change data types should extend the Transactional data.

If you import on-hand providing the data from the 3rd case, you should set the Quantity sold to zero for the transactions that are not sales.

As we mentioned, you should also provide the data depicted in the table below.

Data name Description Given in Datatype Default
Lead time The average supplier lead time to deliver an item, which is actually the interval of time between purchase order placement and its receipt. Days Integer 30
Order cycle Represents how often the item is ordered from your supplier. Data aggregation periods (weeks or month), days, or the Lead times. 1 data aggregation period
To get accurate inventory replenishment plans along with the Lead time, Order cycle, and Transaction data, we strongly recommend that you provide Streamline with the Orders-to-receive information and Orders-to-ship information (described below).

The accuracy of inventory reports can be improved, if you additionally provide the Lead time variance (see table below).

Data name Description Given in Datatype
Lead time variance The variance of the Lead time. Lead time units Float

To optimize the inventory, Streamline uses a lot of inputs such as Last on hand, Lead time, Order cycle, and other. Safety stock can be also considered as one of the inputs because it is used to determine the optimal purchase/replenishment plan. There are several ways that Streamline uses to calculate safety stock. One of them is based on the number of safety stock periods. Streamline allows you to set up this parameter for each planning item individually. A description of this data type is given in the table below.

Data name Description Given in Datatype
# of periods for safety stock The number of future periods which demand is used as the safety stock. Data aggregation periods Float

Orders-to-receive information

This information indicates items that are being delivered currently:

  • from the supplier to your location or distribution center; these are all open lines in your purchase orders (POs);
  • from one of your locations to another one; these are all open lines in your transfer orders (TOs); the items which on-hand has already been changed in the source warehouse, however, has not been changed yet in the destination warehouse.

The data types, describing a line in these orders, are shown in the table below.

Data name Description Datatype
Item code The item identifier, also known as SKU. String
Qty to receive The quantity of an item in transition. Integer
Delivery date Expected delivery date of the item. Date or DateTime
Sendout date (Optional) The date when the purchase order was placed. It should be given if you do not know the Delivery date.
Location (Optional) The location the item is being delivered to. It should be given if locations are used. String
Lot cost (Optional) The cost of the purchase order line. Float
Order number (Optional). The system's PO/TO number. This information is used only for display purposes in the Planned orders preview dialog. String
The Sendout date should be given for open POs only, not TOs.
A purchase order Sendout date allows Streamline to calculate Next order by date for the first time as you import your data and forecast the project.

It is strongly recommended to provide Streamline with one of the Delivery date or Sendout date. The former is preferable.

If only Sendout date is given, Streamline calculates the Delivery date as a sum of the given Lead time period and the Sendout date.
If neither Delivery date or Sendout date is given, Streamline will treat that as if the order has been already received and adds the Qty to receive to the Last on hand when calculates a purchase/replenishment plan.
2019/04/03 14:36 · admin

Orders-to-ship Information

To calculate future on-hand inventory more accurately, Streamline can account for information on orders to be shipped to the customers. It describes the items that are on open sales orders or back-orders.

The data types, describing a line in these orders, are shown in the table below.

Data name Description Datatype
Item code The item identifier, also known as SKU. String
Qty to ship The item amount that should be shipped to the customer. Integer
Shipment date The date when the item should be shipped to a customer. In the case of backorders, this can be some promised date. Date or DateTime
Location (Optional). The location where the shipment has been done (or will be done) from. It should be given if you use locations. String
If the Shipment date is not given, Streamline treats that as if the sales order has been already sent out to the customer and deducts the Qty to ship from the On hand quantity when calculates a purchase/replenishment plan. If the On hand amount is not enough, Streamline will order the difference.
2019/06/14 10:55 · admin

Accounting for Constraints in Optimization

Streamline allows you to account for constraints when optimizes inventory. There are two types of constraints, constraints on the item quantity that is ordered and constraints on the purchase order as a whole. Data types for the constraints are given in the table below.

Data name Description Datatype
Display qty The minimum number of units a shelf to display. This parameter typically arises in the retail business. You can control how this amount is used when final safety stock is calculated. Integer
Constraints on item quantity ordered
Min lot and Max lot Represent optional constraints on how few or how many of this item can be purchased from your supplier. Integer
Order rounding Allows Streamline to take into account how many items are included in a carton, allowing the application to suggest exactly the right amount of the item to order.
Constraints on purchase order
Supplier’s min. qty The minimal quantity/weight/volume/cost of ordered items in total necessary in order to supplier accept the purchase order. Integer
Supplier’s min. weight/volume/cost Float
Weight/unit The weight of one unit of an item.
Volume/unit The volume of one unit of an item.
If Min lot is greater than Max lot, Streamline neglects Min lot and uses Max lot in the optimization.
Order constraints such as Supplier’s min. weight or Supplier’s min. volume should be given to Streamline along with the Weight/unit and Volume/unit relatively.
Constraints on a purchase order are incompatible with the product shelf life limitation. It means that Streamline's purchase order recommendations come from the given purchase order constraints, not the shelf life limitation if both are given.
2019/04/03 09:29 · admin

Data types for Inventory KPIs

Streamline can calculate key inventory indicators (KPI) such as expected stockout and overstock values, gross margin, and other metrics. To enable the calculations, you should provide one of the data types shown in the table below.

Data name Description Datatype
Inventory value/unit The balance value of one unit of the item in stock. It should be given in the base currency. It is preferable than the Item purchase price described below. Float
Item purchase price The last purchase price of the item in the supplier's currency.

The Item purchase price is also used to calculate the value of purchase orders' lines recommended by Streamline. Some of the KPIs (for example, the annual revenue, revenue next year, and other) require Sales price/unit or Transaction revenue to be additionally imported.

If supplier's currency is not given, Streamline implies that Item purchase price is given in the base currency.

Supplier's Information

Streamline allows you to import supplier information shown in the table below.

Data name Description Datatype
Supplier code The identifier of the supplier. String
Supplier’s currency The currency in which purchase orders are placed.
Supplier's item code Item code in supplier's stock-list that corresponds to your Item code. It allows Streamline to create purchase orders in supplier's item codes.
If Item value/unit is not given, Item purchase price and Supplier's currency are given, then gross-margin and turn-earn index will not be calculated.

Two-echelon Planning

Streamline allows performing two-echelon planning.

If you have only one distribution center (DC), Streamline does not require you to provide any additional data, and you can set up all DC's options in the DC settings. In this case, however, there is no ability to account for the case when a location is supplied by the supplier directly (skipping the DC).

If you have several DCs, you can import and set up them using the Database connection. In this case:

  • you should provide additional information (described below);
  • distribution center is always enebled and the Location control is disabled in the Settings;
  • a location can be supplied by the supplier directly skipping the DCs;
  • the options set in the DC settings are applied to all your DCs at once.

You can import any number of DCs and set up which locations are supplied by a particular DC on an item basis. It means you should set up a triple (Location, DC name, Item code). For example, the triple (West, DC west, Dark chocolate) means that DC west supplies West location with Dark chocolate.

There are two limitations on the DC-location relation:

  • Two (or more) DCs can’s supply the same item to the same location.
  • A DC can’t supply another DC, that is, only DC-to-location relations are allowed.

To set up the relations, the DC name data type should be returned with the Item info query. It indicates the name of the DC in the triple.

Now, we describe the data types that the Item info query should return in order to Streamline set the relations properly.

As we explained previously, to set up a triple, the Item info query should return the following data columns: Location, DC name, and Item code. The table below shows an example of records that should be returned by the query in order to set up a DC-location-item relation.

Location DC name Item code
A DC1 Item1
DC1 NULL Item1

As you see, we need two records to be returned for each DC-location-item relation. The first one links location A to DC1, meaning that Item1 will be supplied by DC1 to location A. The second one declares DC1 as a location that stores Item1.

To set up a situation when an item is supplied to a location by the supplier directly (no DC involved), the query should return the record shown in the table below. The table shows example data.

Location DC name Item code
B NULL Item1

Let's consider an example shown in the figure below.

In this case, the query should return the data shown in the table below.

Location DC name Item code
A NULL Item1
B DC1 Item2
C DC1 Item3
C DC2 Item4
DC1 NULL Item2
DC1 NULL Item3
DC2 NULL Item4

Material Requirements Planning

Bill of materials information describes the components of finished products. Components can be considered as sub-assemblies (at the intermediate levels of the production process) or as raw materials (at the lowest level of the process). You can import an unlimited number of assembly levels.

Streamline also supports material requirements planning for batches. I.e. when a BOM describes ingredients that are used to produce a number of finished products.

To get a materials requirements plan, you should provide Streamline with the data types shown in the table below.

Data name Description Datatype
Finished good's code The code of a finished product or a sub-assemblied item. String
Material's code The material's or component's code.
Material qty/batch The quantity of material or component that is required to produce a batch of the finished good. Integer
Finished batch size (Optional) The size of a finished good batch, i.e. the number of finished goods to be produced. If it is not given, the batch size is equal to 1 by default.

Planning Products with Shelf Life

Streamline allows you to plan products having a limited shelf life. Product shelf life can be given in two units of measure (see table below).

Data name Description Given in Datatype
Shelf life, periods It is the desired time you want the product to be sold for. Data aggregation periods Float
Shelf life, days Days

The Shelf life parameter is used as a constraint in the inventory optimization. It is the maximal limit on the current order quantity derived from the given shelf-life period and generated demand forecasts.

Accounting for products promotions

Streamline allows you to account for given promotions for a product automatically when it generates the forecasting model. To do this, you should provide the data types shown in the table below.

Data name Description Datatype
Item code The item identifier, also known as SKU. String
Location (Optional.) The location where your promotion is carried out.
Channel (Optional.) The channel by which the promoted item is distributed or sold.
Start date The date the promotion starts. Date or DateTime
End date The date the promotion ends.
Discount The promotion discount for the item. It should be given as a fractional number. For example, if a discount is 30%, you should provide 0.3. Float

In order to Streamline be able to account for the future promotional discounts for a product, you must also provide the history of the past promo discounts for this product in the same format as for the future discounts (see an example).

If you need to account for an item promotion that is carried out for all your locations at once, provide an empty string for the Location column.

Disassembled Kit Planning

If you have kitted items in your inventory but want to forecast and plan only by their components, Streamline can automatically disassemble them and take into account this information. In this case, you should provide the data types indicated in the table below.

Data name Description Datatype
Component code The code of the item that is a component of the kitted item. String
Qty in kit The quantity of the component required for the kit. Integer
Kitted item code The code of the kitted item. String

Next: Importing Data

Download PDF

database-connection-data-types.txt · Last modified: 2019/07/11 09:49 by admin