Skip to main content

Optional Field ETL

Optional Field ETL enhances how you analyze and model Sage 300 optional fields. It preserves each field's original data type as defined in Sage 300, enabling accurate, flexible, and efficient reporting and modeling.

Previously, all optional fields were treated as strings, which make analysis more complex and often required additional manual work—such as creating custom aggregations. With Optional Field ETL, correct data types are automatically applied, and all relevant fields are available for joins and modeling.

The process uses primary keys to determine join relationships with fact tables. If no primary key is available, it uses the clustered index.

important

If you are using DataSync, ensure that primary keys are properly defined. Otherwise, the application assigns them automatically. The ETL process uses your primary keys for joins and falls back to the clustered index if none are found.

Benefits of optional field ETL​

  • Analyzing optional or user-defined fields: Gain deeper insights from custom Sage 300 fields without manual type conversion.
  • Modeling fields using their native data types: Build data models that preserve the true data types of optional fields instead of treating them as strings.
  • Simplifying joins and aggregations: Reduce setup time by automatically identifying fields for joins and supporting consistent aggregations.

Set up Opitonal Field ETL​

Configure databases for synchronization​

  1. Go to the Data Models and Views tab and expand Optional Field ETL.
  2. Open Databases to Sync. Add all relevant databases and custom schemas from each environment you use.
    This ensures consistent field definitions across environments and prevents model mismatches.

Scan for optional fields​

  1. Under Optional Field ETL, right-click Optional Field Tables.
  2. Select Execute Info Pages.
  3. Run the Update Table List info page to scan tables ending in "-O" (e.g., ARCUSO from customer optional fields) and retrieve field definitions.
    • The CSOPTH table provides the data type information.
    • If multiple databases contain the same field, the version with the most recent audit timestamp is used.

Choose tables or view objects​

  1. Open the Table included in Refresh All worksheet.
  2. For each object, choose whether to generate a view or a table:
    • Use tables for faster performance (requires manual loading).
    • Use views for real-time access (may be slower).
  3. By default, all template tables are included. To include others, set Included in ETL for All Objects to 1 in the worksheet.

Run the ETL and update the data model​

  1. Return to the Optional Field Tables info pages.
  2. Run ETL for All Objects to generate your tables or views.
    • Tables are loaded incrementally for faster performance.
    • Generated object names follow this format: PVT_<TableName>.
    • Optional fields are added as new fields, with keys available for joins.
  3. To process a single table, run ETL for Single Table and select your table.
    If errors occur, enable Drop and Recreate and run it again.
  4. Manually add new optional fields to your data model. Optionally, add prompts or global parameters.

Add fields in the Data Model Designer​

  1. Go to the Data Models and Views tab and open your target folder.
  2. Right-click the data model and select Design Data Model.
  3. Follow steps to add fields.