Configuring source tables and setting incremental keys

To configure source tables and set incremental keys, you must have Infor ION Enterprise Connector installed and configured to enable data loads from a local database to Data Lake. The Enterprise Connector configuration includes setting up the enterprise location, database connection point, and Data Lake flow in ION.

See Infor ION Technology Connectors Administration and Infor ION documentation.

  1. Prepare fact data in the export table in your source database. For example, in the CA_FactCoffeeAdventure_export table from the example in the "Best practices to load data from the source system to Infor EPM OLAP" topic.
  2. In ION, select Connect > Connection Points and select the appropriate connection point.
  3. Click the Documents tab, then the plus icon, and then EDIT.
  4. On the AnySQL Modeler page, click VALIDATE MODEL and specify the query model name and the document name.
  5. Click the plus icon to expand a list of tables to configure.
  6. Drag the required table to the Drag an object to begin modeling panel and then, in the box with the table, click the column icon.
  7. Move all table columns to the Selected grid except for the InsertDate column.
  8. In the Selected grid, set these data types and properties for these columns:
    Column Data type Property
    ID Integer Identifier.

    Identifies a record. If a record is updated, only its latest version is displayed in Compass
    RecordID Integer Variation.

    Determines which version of a record with a certain identifier is the latest.
    ToDelete Integer Delete.

    Deletes a record. You must define a value that indicates that the record is deleted. For example, 1. By default, deleted records are not displayed in Compass.
  9. Click UPDATE.
  10. To set up the incremental key, click the Settings icon at the top right corner of the Drag an object to begin modeling panel.
  11. In the Settings window, click ADD INCREMENTAL KEY and specify this information:
    Object
    Select the table for which to set up the incremental key.
    Column
    Select the identity column for which to set up the incremental key. For example, RecordID. Then, this column contains unique numbers that are generated when new records are inserted into a table.
    This configuration ensures that only incremental records are loaded instead of all records.
  12. Click UPDATE.
  13. Click the Save icon.
  14. Click GENERATE METADATA.
  15. Click CLOSE.
  16. Configure the Data Lake flows for all relational tables with metadata (dimensions) and fact data that you have in your source database.