User Tools

Site Tools


GMDH LLCのウエブサイトに戻る

ビデオ チュートリアル


1. 一般的な情報

2. スタートアップ

3. データ接続

4. 需要予測と販売予測

5. 在庫計画

6. 参照


GMDH Streamline: Database import and export

Data that can be imported

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. This section describes the necessary data 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 many were sold.

The Sales history data includes the following:

  • Date – the transaction date.
  • Quantity sold – the item amount that was sold in the transaction.
  • Item code – the item identifier, also known as an SKU.

If you need to forecast by location (such as a store, warehouse, or region) or channel, the Sale history should be extended with one of the following:

  • Location is used to forecast consumption of each Item code in each location.
  • Channel represents a channel by which an item is distributed or sold. For example, e-commerce, direct sales, distributors, or a single customer. This data should be used when you need to plan your demand by channel or customer.

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 following data:

  • Item category, Item sub-category, and so on.
  • Location category, Location sub-category, and so on.

You can import as many categories as you need.

In addition to the Item code, you can import:

  • Item description – an item description, used to better explain what a particular item code means.
  • Item’s info field – any additional characteristic of the item (e.g., color, size, texture). You can import an unlimited number of such fields.

Revenue Forecasting

To forecast revenue, the Sales history should be extended with one of the following:

  • Sales price/unit – the price of one unit of an item in the sale transaction.
  • Sales price/order – the price of the sale transaction.

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 Optimization

To balance your demand with supply, generate purchase plans, and identify potential stockouts and overstocks, Streamline needs the Sales history to be extended with one of the following:

  • On hand – the on-hand remaining after the transaction.
  • On hand change – how much the On hand has changed after the transaction. When this data is used, the sales orders query must return not only the sales transactions but all of the transactions impacting the On hand.

If On hand change is provided, then you should additionally provide the Last on hand – the item on hand at the time of import.

Some inventory management systems start from zero On hand and store On hand change but not the remainder. Obtaining the remainder from this information via SQL is extremely tricky. Streamline can build On hand history but you must provide every single transaction, and that can be slow. Of course, some transactions are not sales and you should set their Quantity sold to zero.

You should also provide:

  • Lead time – represents the interval of time between replenishment order placement and its receipt. It is defined in days.
  • Order cycle represents how often the item is ordered from your supplier. It can be given in sales history periods (weeks or month), days, or lead times. It must be an integer. For example, if inventory is replenished semi-monthly, then enter the number 2 and group your data by week.

If the Order cycle data and Lead time data have gaps or are completely missing, the default values (Order cycle = 1 month and the Lead time = 30 days) are loaded from the program settings.

To get accurate inventory replenishment plans along with the Lead time, Order cycle, and extended Sales history data, we strongly recommend you to provide Streamline with in-transition information (described below).

The accuracy of inventory reports can be improved, if you additionally provide the information on pending sales orders and the Lead time variance.

In transition information

This information indicates which items are being delivered now from your supplier. The following data should be provided:

  • Item code - the item identifier, also known as SKU.
  • Qty in transition – the quantity of the item in the In-transition transaction.
  • Delivery date – expected delivery date of the item from your supplier.

If locations are used, the Location should be provided as well.

If you do not know the delivery date, you can provide the PO sendout date – the date when the purchase order was placed. It is strongly recommended to provide Streamline with one of the Delivery date or PO sendout date. The former is preferable.

If neither the Delivery date or PO send out date is given, Streamline will determine that the order should be delivered at the beginning of the first forecasted period. Thus, if you use incomplete period forecasting, the Qty in transition is added to the Last on hand and this result is used to generate the forecasts and inventory plans for the current period.

Pending Sales Orders

To calculate future on-hand inventory more accurately, Streamline can account for information on pending sales orders. It describes which items are on open sales orders or are back-ordered and includes:

  • Item code - the item identifier, also known as SKU.
  • Qty to ship – the item amount that should be shipped to the customer.
  • Date - when the item should be shipped to a customer. In the case of backorders, this can be some promised date.

If locations are used, the Location from which the item is being consumed by the customer should be provided as well.

The Date is not obligatory but is highly recommended to provide. If it is not given, Streamline determines that sales order should be delivered to a customer as soon as required quantity will be in stock.

Accounting for Constraints in Optimization

If your business logic includes some constraints, for instance, minimal or maximal item lot, or order rounding, Streamline can take this information into account when building the replenishment plan. To do this, provide the following information:

  • 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.
  • Min lot and Max lot represent optional constraints on how few or how many of this item can be purchased from your supplier.
If Min lot is greater than Max lot, Streamline neglects Min lot and uses Max lot it in calculations.

Data for inventory KPIs

Streamline can calculate key inventory indicators such as expected stockout and overstock values, gross margin, and other metrics. To enable the calculations, you should provide one of the following:

  • Inventory value/unit – the balance value of one unit of the item in stock.
  • Item purchase price – the last purchase price of the item in the supplier's currency.

The Inventory value/unit is preferable. The Item purchase price is also used to calculate the value of the purchase orders' lines recommended by Streamline.

To calculate KPIs such as annual revenue, revenue next year, gross margin, and turn-earn index, the Sales history should be extended with either the Sales price/unit or Sales price/order.

Supplier information

Streamline allows you to import supplier information such as:

  • Supplier code  Ethe identifier of the supplier.
  • Supplier’s currency  Ethe currency in which the purchase orders are placed.

This data is only used for display purposes in the application. It is not involved in inventory optimization process.

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 following data:

  • Component code – the code of the item that is a component of the kitted item.
  • Qty in kit – the quantity of the component required for the kit.
  • Kitted item code – the code of the kitted item.

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 raw materials (at the lowest level of the process). Streamline supports an unlimited number of assembly levels. To get a materials requirements plan, you should provide Streamline with the following data:

  • Finished product code – the code of the finished product or the sub-assembly item code.
  • Component code – the code of the item that is a component of the finished product.
  • Component quantity – the quantity of the component required to build the finished product.

Planning Products with a Shelf Life

Streamline allows you to plan products having a limited shelf life. The only data you should provide is the item Shelf life. It should be given in the data aggregation periods (days, weeks or months). This data should be returned by the Item info query.

Data that can be exported

Exported data is customized per customer requirements. This may include any information that Streamline generates such as purchase orders, forecasts, or replenishment plans.

For example, to export the purchase orders to your database, the minimum information includes supplier code, item code, and quantity to order. Streamline calls stored procedures from your database and passes the minimin information into them. Stored procedures pull all necessary fields from the database and create purchase orders. Stored procedures are created in your database according to your system requirements. Alternatively, the purchase orders can be exported to an Excel file for manual submit to 3rd party online systems.

Download PDF

ja/db-exported-imported-data.txt · Last modified: 2019/03/22 16:46 (external edit)