4.4.4. Intermediate database

When possible, use a built-in connector in Streamline. We've connected a bunch of ERPs and inventory management systems already. But if there's no built-in connector created specifically for your ERP system, no worries, you can connect any database via the ODBC connection.

It can be an ERP database - most ERPs allow read‐only access to their databases, or it can be a separate database created specifically to store data needed by Streamline.

Connecting an ERP database in read-only mode is straightforward. Simply insert SQL queries into the Database connector tabs in Streamline and your data will be updated every time the user of Streamline clicks the Update Data button.

The only disadvantage of this approach is that you usually can't export ordering recommendations back to the same database. Users can only export recommended orders to a spreadsheet or text file.

Therefore it's sometimes difficult to decide whether you should simply insert SQL queries into Streamline and pull data directly from the ERP's database or, perhaps, create a new intermediate database instead. The intermediate database requires your IT team to arrange a 2-way data connection to the ERP using the tools and methods provided by the ERP vendor.

Use this check list to find out if an intermediate database is a must:

  1. It's important to have a seamless order upload using the Create button in Streamline.
  2. It's a retail company with millions of transactions. The data needs to be aggregated into daily buckets before any analysis.
  3. It's important to prevent any impact on the performance of the ERP database due to heavy queries from the planning/purchasing team.
  4. The ERP system has a tool that exports and imports data while direct access to its database is not allowed.
  5. There's a need to collect data from more than one ERP system or data source.

Creating an intermediate database

You can download SQL-scripts that create an intermediate database and find here an example of the gsl project file. This implementation example project includes scripts that work with the intermediate database.


Next: Inventory Management Systems

Download PDF