Importing

ERP systems contain most of the data needed to run PlanetTogether including:


  • Plants
  • Departments
  • Resources
  • Resource Capabilities (sometimes called Operation Types)
  • Jobs (including Manufacturing Orders, Operations, and Material requirements)

Some of this data is fairly static, such as plants and departments, while jobs tend to be added, closed, and changed at least daily. All of the above data can be entered manually into PlanetTogether but it is generally preferred to import as much as possible so that most data maintenance is in one place. For some data elements, especially resources, the definition in PlanetTogether contains fields that do not have an ERP equivalent. In these cases, the values can be left at their default values, calculated in the interface, or manually entered in PlanetTogether after the object is created via the interface (or manually). If a value is omitted in the interface, then manual changes to object fields will be preserved even after updating the other fields via the interface.

Note: Some data is entered directly in PlanetTogether and cannot be sent via the interface.  This data, however, is very easy to enter.  It includes:

  • Users, and
  • Dispatchers (used in optimization)

    Import Options

    There are three options for importing data into PlanetTogether:

    1. Use the APS Interface Service
    2. Build a custom interface using the APS API (application program interface)
    3. Using the Scheduling Agent's Custom Message Processor to import from text files.  (This is usually used for importing from bar code type transaction systems.)

    The APS Interface is usually easier and faster to set up since it requires no programming. It is a batch-style, "pull" interface that is initiated from within PlanetTogether. If a real-time, transactional interface is desired or if data volumes are especially high and your database's ODBC/OLEDB/SQL interface is slow or unavailable then the APS API would be a better choice.

    If you decide to build a custom interface using either the APS Interface or the APS API, the first thing to do is make an integration plan.

    Connecting to a Database

    PlanetTogether can connect to a variety of different data sources, including SQL, Oracle, OLEDB, and ODBC.

    SQL: In the instance manager, you can enter the name of the SQL Server and the Database from which you want to draw information, and the connection string will be formatted correctly underneath.

    ODBC: Leave the 'Server Name' and 'Database' Names blank and select ODBC on the left-hand side. The connection string will need to be entered manually.

    Oracle: PlanetTogether uses the .NET Framework Data Provider for Oracle as the provider. The connection string, like ODBC, needs to be set manually. For information regarding the string format, examples can be found here. This is a link to more information on the Oracle Connection class that PlanetTogether uses to import from Oracle.

    Note: There are certain keywords that are reserved by Access, ODBC, and SQL Server. When modifying database fields and columns, these keywords should be avoided to prevent syntax errors.

    Configuring Database Connection

    The APS.net Interface Service exchanges data with external databases using Connections. There is usually one Connection for each database. In most cases, all of the data you need will be available in a single ERP database. Each Connection has the following properties:

    • Connection Name: This is a text name for identifying the Connection. Each Connection name must be unique but can be virtually anything that you would like to call it. (Some characters are not allowed in the name and will be replaced with underscores automatically when you exit the field.)
    • Connection Type: The Connection Type is an important setting. It must be set to the correct value for the Interface Service to be able to connect to your database.
      • ODBC: This stands for Open Database Connectivity. Many databases provide ODBC drivers to support interfacing. If you will be using ODBC then an ODBC System Data Source must be set up on the computer that the Interface Service is running on. The Connection will not work unless the Data Source is working. When you select the ODBC option, a list of System Data Sources will be displayed. This list is from the PC running the Interface Service, which may not be the same PC on which you are running the Interface Wizard.
      • OLE DB: A connection to any data store accessible via OLE DB. Depending on the functionality supported by the native OLE DB provider, some methods, or properties of an OLE DB Connection object may not be available. The .NET OLE DB Connection String is designed to match the standard OLE DB connection string format as closely as possible with the following exceptions. The "Provider = value " clause is required. However, you cannot use "Provider = MSDASQL" because the .NET Framework Data Provider for OLE DB does not support the OLE DB Provider for ODBC (MSDASQL). To access ODBC data sources, use an ODBC Connection Type instead. You can use an OLED DB Connection to connect to a variety of data sources, including a Microsoft Data Link (UDL).
      • SQL: A connection to a SQL Server or later. It is optimized for use with SQL Server by-passing the OLE DB layer.
      • Oracle: A connection to an Oracle database.
    • The database stores time spans in: This setting determines the conversion to be used when creating TimeSpans in APS.net from numeric values gotten from the external database. Since some Connection Types do not support the TimeSpan type, it is necessary to import TimeSpans as numeric values and then convert them to TimeSpans later. This field is very important because if this value is set incorrectly then all TimeSpans will be incorrect.
    • Connection String: This is used by the Interface Service to establish a connection to a data source. If it is invalid then the Connection will fail. The value required depends upon the Connection Type and the data source you are connecting to.

    These are some examples of ODBC Connection Strings:

    • Driver={SQL Server};Server=MyServer; Trusted_Connection=yes; Database=Northwind;
    • Driver={Microsoft ODBC for Oracle}; Server=ORACLE8i7; Persist Security Info=False; Trusted_Connection=yes
    • DSN=dsnname
    • Driver={Microsoft Access Driver (*.mdb)};DBQ=c:binnwind.mdb
    • Driver={Microsoft Excel Driver (*.xls)};DBQ=c:binbook1.xls
    • Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:bin

    These are some examples of OLE DB Connection Strings:

    • Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NWIND_RW.MDB
    • Provider=MSDAORA; Data Source=ORACLE8i7;Persist Security Info=False;Integrated Security=yes
    • Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:binLocalAccess40.mdb
    • Provider=SQLOLEDB;Data Source=MySQLServer;Integrated Security=SSPI

    The SQL Connection String is similar to an OLE DB connection string but is not identical. Unlike OLE DB, the Connection String that is returned is the same as the user-set ConnectionString, minus security information if the Persist Security Info value is set to false (default). The .NET Framework Data Provider for SQL Server does not persist or return the password in a connection string unless you set Persist Security Info to true. This is a typical SQL Connection String:

    • Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer

    This is a typical Oracle Connection String:

    • Data Source=Oracle8i;Integrated Security=yes