ETL+ for Acumatica OData v3 and v4

Pointers, Tips, Advice and Technical details about using Acumatica OData v3 and OData v4.

See also ETL+ for Acumatica Cloud (OData), ETL+ Deployment for Acumatica Cloud (OData)

DataSelf has been working closely with Acumatica Inc to certificate and optimize the OData extraction to be efficient, fast and cause minimal performance impact in your Acumatica ERP/CRM.

Click here for Acumatica DAC Schema Metadata.

Acumatica OData Pointers, Tips and Advice

Miscellaneous knowledge, precautions, advisories, suggestions, insights, pointers, and recommendations for utilizing OData effectively.

• The first data load can take a while since OData renders data slowly.

• We recommend setting up delta refreshes (ETL+ Table Load Types) for tables with more than 10K rows. With delta refresh properly setup, most OData extractions should run very quickly.

• When loading tables, clicking Cancel stops the process at the end of the current OData page loading.

• The data load logging shows how many rows have been processed. At the end of the process, the log shows the load type: loaded, replaced, upserted, appended.

• Debug Log file includes OData URLs executed when Debug is set to Level 2.

• When running a Replace with Control or Upserted extraction from large datasets, ETL+ signs in to Acumatica, loads in sets of up to 100 extraction blocks into a temp table, loads the temp table to the target table, signs off, and then repeats this process for the next set of extraction blocks. This helps Acumatica rendering speed, and provides intermediary starting points in case of issues in the middle of an extraction process.

• When loading large tables, logs at each block of 100 pages.

• When setting up Upsert delta refresh on tables with primary keys with multiple columns, sort the PK columns on the ETL+ SQL Statement in a way to guarantee that the PK will sort rows in ascending order based on their creation order. To help with that, if available, we recommend adding a CreateDate column as the first column of your PK column set.

• Enabled SELECT * from OData data sources.

• Enabled Preview from OData pre-mapped tables.

• OData extraction ignores duplicated column names (avoiding OData → SQL extraction errors).

• For OData tables without built-in indexes, ETL+ will only extract data in blocks when indexes have been configured in ETL+. Without indexes, such extractions will be continuous (not in blocks).

• By default, character fields are loaded into varchar(max) in the data warehouse and are limited to the first 250 characters. Change the column format using Design as needed.

• When processing delta refreshes, the page size will automatically expand when the delta filter cannot get over the next page of data. The expanded page size only applies to the table being loaded.
  Message: The delta extraction filter requires an Extraction Pagination expansion from <current> to <new> rows.

  • See Extraction pages and Pause on the ETL+ Source Driver for OData

• Super admin: Creation of new OData entities sets the OData extraction blocks to 1000 records and 100ms pause (instead of 0 recs and 0ms pause which causes issues).

OData v3 Pointers, Tips and Advice

• OData v3 requires Generic Inquires (GIs) with Expose via OData box checked.

• Delta refreshes: Acumatica OData does NOT accept GI formula fields as filter or indexes for Replace and Upsert (ex.: a GI field with formula =[GLHistoryByPeriod.FinPeriodID]).

OData v4 Pointers, Tips and Advice

• OData v4 can extract data directly from DAC tables.

• Delta refreshes: Acumatica requires that filters for Replace and Upsert must not have NULLs and ETL+ extraction blocks must be bigger than the max number of records for each block of records with the same filter value.

• When working with Replace or Upsert and using date filters, ETL+ filtering adapts to the date format of the source Acumatica.

• If a table doesn’t have a native OData index, ETL+ errors out and logs “Extraction time out. Table without native OData index. Please create one via Replace or Upsert.” Set up Replace or Upsert to fix the issue.

• Odata Metadata, Opening Odata Server for information on tables and columns available for extract by ETL+ see Accessing Acumatica/OData in a Web Browser

Acumatica DAC Schema

You can find the Acumatica DAC Schema Browser and metadata at: https://help.acumatica.com/dacbrowser

Related Pages

• Choosing Acumatica OData Versions Overview of the differences between OData v3 and v4.

• ETL+ for Acumatica Cloud (OData) Installation and deployment

• Acumatica Technical Details

  • Acumatica Setups

    • Acumatica OData Extraction Issues / Trouble

  • Acumatica DAC Schema Metadata Browser

• OData URL from an Acumatica Instance

More Related OData Pages

• For the installation and deployment of ETL+ and Odata see ETL+ Deployment for Acumatica Cloud (OData) .

• For choosing between versions of OData see Choosing Acumatica OData Versions

• For solutions for problems with OData see Acumatica OData Extraction Issues / Trouble

• For technical information about Acumatica see Acumatica Technical Details

Keywords: Acumatica Cloud,