Process for using the Bulk Data Loader

  • Create a new BDL configuration. This will generate stage tables within the Optiva database.
  • Download schema. The schema creates excel files for all BDL supported symbols.
  • Access and Execution of the BDL should be limited to a small group of one or two users. BDL worksheets are provided to distribute to team members; however, the consolidation, upload through Script executions, should be a limited team.
  • This Excel file will have tabs for each supported detail code like header, acl, sets, etc. You can remove the tab from the excel file and add tabs into the excel file. If you want to create a single consolidated excel file for uploading data for multiple symbol, you can add new tabs. For example, if you want to upload item data with formulas, you can create tabs such as FSITEM and FSITEMACL.

  • Each Bulk Data Loader created in the UI will add a set of BDL Staging table with an _1+ for separating data loads into projects. The recommendation is to limit the number of projects.

    Deleting a BDL from the UI DROPS the BDL Staging tables - be sure your data has been loaded to the primary tables or is saved to worksheets before deletion.

  • Fill the data into these files and upload it onto stage.
  • You must use leading zeros for Codes and Versions. You must format the value as text with an apostrophe. For example: ‘00101 ‘001' for formula 00101\001.
  • Bulk Data Loader supports XLS or XLSX, CSV and ZIP file formats to upload the stage data.
  • The ZIP file must contain only XLS or XLSX and CSV file formats.
  • The Excel file tab(s) or csv file name being uploaded must match with the Optiva table name (like FSITEM, FSFORMULA, FSSAMPLE…).
  • You can remove the columns from excel file tab(s) or CSV file and pass empty values for “Allow null columns” of its respective stage table. See Null supported columns.
  • You cannot upload Excel or CSV files without data. The file must contain data in at least one tab for Excel. When stage table data has been overridden, only the uploaded file will have data in its respective tab in Excel.
  • After uploading a file, use the Validate button to validate symbols.
  • User can perform the validation process for multiple symbol(s). If validation status is “Error”, then there must be validation errors for uploaded data.
  • Click Refresh Validation Status on object list grid’s toolbar in Main tab to update the validation completion status for the selected object(s) in the grid.
  • If configured, notifications will be sent to Ming.le Inbox, Ming.le Tasks, and your email inbox upon Validation completion (failure or success).
    Note: 

    *Configuration for BDL notifications should already exist in the MT SAAS environment. The following should be in place:

    • IFS Role MingleIONEnabled

    • “From” email address configured in ION Desk > Configuration > ION Service > Properties > Activities Engine

  • All the validation error messages are displayed in the Validation Report tab. To view validation errors in this tab, refresh the stage configuration.
  • Users can modify and update the validation errors in Update BDL Data tab into stage table. Repeat this process until the validation status is Validated.

  • When Validation Errors exceed a "Specific Number" the application will generate an external notepad log and present it to you for analysis.

    Administrators can choose to Edit the errors in the application to update the staging tables or update their Worksheets and repeat the Upload and validation process steps. This can be repeated as many times as necessary.

  • After all validation errors are corrected, use Generate Scripts or Apply Script options to load this data into target Optiva tables.
  • An Optiva database reference document is available for BDL Administrators to provide definitions to most columns in the tabs. FSItem, FSFormula and FSFormulaIngred have unique functional columns to control Formulas, Constituents, Ingredients and Packaging BOMs along with ByProducts and Alternates.

Bulk Data Loader Symbol

Document Codes and Status capabilities are enabled for the Bulk Data Loader symbol.

By default two document codes, Notes and OPTIVA_STAGING_DOC document codes are added to the Text and Attachments types respectively.

BDL Doc Codes

Status codes In Progress and Completed are added.

BDL Status Codes

BDL tabs

The Security, Notes and Attachments tabs are available in the Bulk Data Loader form.

The Status tab is added to the header session. The Status can be In Progress and Completed. If the status is set to Completed and saved, all stage table will be deleted from the database. The Bulk Data Loader configuration will available for history. Once the status is Completed, users cannot use the Bulk Data Loader configuration for loading or validating staging data. BDL tabs