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-importing-data

3.4.2. Importing data

Database connection uses SQL-queries to import your data into Streamline from a database. Each of them is specially designed to import a particular piece of data. In this article, we explain:

Importing queries

Database connection dialog has several tabs, such as Transactions, Item info, and so on (see figure below). Each tab is designed to import particular data by executing the query entered into the main field of the tab.

Strictly speaking, to start using Streamline, you can import your data (via deprecated fields) using the query for the Transactions tab only. However, we strongly recommend you to go with a typical workflow when at least four queries are used. These are:

  • Transactions query;
  • Item info query;
  • Orders-to-receive query; and
  • Orders-to-ship query.

The other tabs (queries) are optional and are used in special cases which we describe in the table below. This table matches SQL-queries with the data types which they should be used to import.

Query(tab) Demand Planning Inventory Planning
Transactions

Transactional data:

  • Date
  • Item code
  • Quantity sold
  • Location
  • Channel
  • Sales price/unit or Transaction revenue
  • Transaction profit

Transactional data:

  • On hand (remaining) or On hand change
Deprecated data: item description; item category; location category; qty to receive; delivery date; lead time; lead time variance; order cycle; order rounding; min lot; max lot; supplier code; supplier’s currency; shelf life, periods; item purchase price; inventory value/unit, item’s info field.
Item info
  • Item code
  • Location
  • Item categories
  • Location categories
  • Item description
  • Location description
  • Info field
  • Sales price/unit
  • Last on hand
  • Order cycle
  • Lead time
  • Lead time variance
  • DC name
  • Transfer region
  • Service level
  • # of safety stock periods
  • Min lot, Max lot, Rounding
  • Supplier min. qty/weight/volume/cost
  • Weight/unit, Volume/unit
  • Inventory value/unit
  • Item purchase price
  • Supplier code, Supplier's currency, Supplier's item code
  • Shelf life, periods, Shelf life, days
  • Display qty
Deprecated data: qty to receive, delivery date, qty to ship.
Orders to receive Orders-to-receive information
Orders to ship Orders-to-ship information
Bill of materials Bill of materials
Promotions Promotions information
Substitutions Substitutions information
To plan inventory in Streamline, you should provide data from both columns (Demand Planning and Inventory Planning) of the table above.

The table indicates deprecated data that should not be imported normally by those queries. It is strongly recommended to import those data using queries which are specially designed for them.

Last on hand can be imported using the Transactions query too. This can be done only in the case when you are providing On hand change data in the Transactional data. In this situation, the last on hand is imported via the On hand column. Despite this capability, we strongly recommend that you import last on hand using the Item info query.

If your Orders-to-receive information has only one transaction per item (or item-location), you can optionally use columns Qty to receive and Delivery date of the Transactions or Items info queries, to import it into Streamline. However, this situation is fairly specific and these columns are left for compatibility and are highly deprecated. Generally, we strongly encourage you to use the Orders-to-receive query to import your Orders-to-receive information.

Special cases

This section describes the capabilities of the connector to import data in very specific situations which happen rarely. We strongly recommend that you import the data according to the table above.

Item info query is absent

If the Item info query is absent, you can import all necessary information (see Deprecated data) using the Transactions query.

Transactions query includes the location information while the Item info query doesn’t

In this special case, you can import all the fields except for the columns that represent location state (Last on hand, To ship qty, Qty to receive, Delivery date) using the Item Info query. The location state information should be returned via other queries, for example: Last on hand and Qty to ship from the Transactions query and Qty to receive, Delivery date using the Orders-to-receive query.

In these special cases, Streamline does not know the stock-list. Consequently, if a project is updated using the Update data button, Streamline leaves the stock-list as it was before the update, no planning items are removed. Streamline removes planning items that did not appear in the updated data from the project in all the other data connections (and cases for the database connection) except for the Transactional spreadsheet connection.

Import options

The importer can automatically aggregate your timestamps in periods (days, weeks, months) using the Group by option. So if you want to see the forecasts, procurement plan, and other reports in months, group the data by months.

The Starting from option depends on the Group by parameter and is available only if you group the data by month or week. If you group timestamps by month, the Starting from parameter defines the ordinal number of the day in the month you want to start the import from. If the Group by option is switched to ‘week’, this allows starting the import from the specified day of a week.

Importing your data

This section describes how to import your data using the Database connection dialog. To import the data, follow these steps:

  1. Go to the menu File > New > Database connection.
  2. Establish the connection to your ODBC data source.
  3. Go to the specific query tab. For example, the Transactions tab.
  4. Enter your SQL-query in the main field of the tab.
  5. Click the Preview button, to execute the query.
  6. Set the meaning of the columns returned by the query.

The preview table at the bottom of the dialog has a special row with a drop-down list in each column for the returned data. The list displays the data types that Streamline understands and can import in the corresponding query tab. Thus, to import your data correctly, match the meaning of your data column to the most appropriate option in the drop-down list.

7. Repeat steps 3-6 for all your queries.
8. Set the import options and click OK.

In the next sections, we will describe the specifics of each query.

Transactions

This query is used to import the Transactional data. The list of the transactions must be in descending order.

If you are going to plan material requirements, Transactions query should also return transactions affecting items On hand due to assembly builds.

Item info

The purpose of this query is to import current on-hand for each planning item. It is also used to import additional information about planning items such as item categories, location categories, item info fields, constraints, inventory KPIs and others.

This query is used as a filter for imported planning items, i.e. only those planning items that are returned by this query will be imported, regardless of the fact that any other planning items may be returned via the other queries.
Records returned by this query must be unique by the planning item identifier. If locations are used, the identifier is represented by (Item code, Location) pair, otherwise, it is Item code.

Pay special attention to importing the following replenishment parameters:

  • Lead time
  • Order cycle
  • Min lot
  • Max lot
  • Rounding
  • Lead time variance

These characteristics depend on the echelon the planning item resides at. If a planning item is sourced from a DC, these parameters define the replenishment process from that DC. In this case:

  • Lead time – the average time interval to deliver an item from DC to location.
  • Lead time variance – the variance of the Lead time defined above.
  • Order cycle – how often you replenish from DC.
  • Min lot, Max lot, and Rounding – the constraints that are put on an item when you order it from DC.

If a planning item is sourced from a supplier:

  • Lead time – the average time interval to deliver an item by your supplier.
  • Lead time variance – the variance of the supplier Lead time.
  • Order cycle – how often you send purchase orders to the supplier.
  • Min lot, Max lot, and Rounding – the constraints that are put on an item when you order it from the supplier.

If the Supplier code is not given, Streamline attaches those items to an empty supplier code.

2019/09/05 08:15 · admin

Importing categories

As soon as the Item category is selected from the drop-down list for a specific column, the option changes to Item category 2 for subsequent columns, then to Item category 3 and so on. This sequence forms a hierarchy of categories in Streamline. In other words, the Item category 2 is a subcategory of Item category, the Item category 3 is a subcategory of Item category 2, and so on. This behavior applies to importing location categories too.

2017/11/10 14:18

Orders to Receive

This query is used to retrieve the information about items to be received.

Orders to Ship

This query is used to pull the data about items that are on open sales orders or backorders.

Substitutions

Streamline allows making substitutions when imports the data. That can be used when you need to disassemble kitted items and have the forecasts and procurement plans for the components only. To understand a kit’s content, Streamline implements substitution rules. The rule syntax looks like:

<Kitted item code><Qty in kit><Component code>.

Your SQL-query should return a set of such rules. As you can see, the columns should be ordered according to the rule syntax.

Bill of Materials

This query is used to import bill of materials data.

Promotions

This query is used to import information enabling Streamline to automatically adjust the forecasting models to the given promotional discounts. The figure below shows an example of the query and the result it returns from a database.

As you see from the figure, besides future promotions, you must provide the promotions' history for the corresponding products.

Updating data (importing only last changes)

At first, you import all the sales orders data. It’s not necessary to import all the data again in future imports. It’s reasonable to import only the last changes. To make Streamline do this automatically the :startdate parameter should be used in the Transactions query.

An example of the query is shown below:

SELECT [Posting DATE] AS [DATE],
       IIF (e.[Entry TYPE] = 1, -e.Quantity, 0) AS [Quantity],
       i.[No_] AS [Item code]
       FROM dbo.[CRONUS International Ltd_$Item Ledger Entry] AS e
       INNER JOIN dbo.[CRONUS International Ltd_$Item] AS i 
       ON i.[No_] = e.[Item No_]
       WHERE i.[No_] IN 
 (
 SELECT i.[No_]
 FROM dbo.[CRONUS International Ltd_$Item] AS i
 WHERE Blocked = 0
 ) AND [Posting DATE] >= ':startdate';

To import all the sales history using this query, you should specify the Import from date parameter to cover the history. Now, when you click the Update data button of the program toolbar, Streamline calculates the start date so that only the last changes of the sales history are imported. It substitutes the :startdate parameter of the query by the calculated date and executes the query.


Next: Exporting Data

Download PDF

database-connection-importing-data.txt · Last modified: 2019/09/05 08:16 by admin