ETL+ for Acumatica OData v3 and v4

Owned by Cortlandt _
Oct 06, 2023
3 min read

See also https://dataself.atlassian.net/wiki/spaces/DS/pages/1639940097

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.

Odata Related Pages https://dataself.atlassian.net/wiki/spaces/DS/pages/67502100, https://dataself.atlassian.net/wiki/spaces/DS/pages/1639940097

Click here for Acumatica DAC Schema Metadata.

Acumatica OData Features

  • 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.

  • 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

  • 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

  • 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.

  • Tables: When refreshing an Acumatica OData v4 source on ETL+ left panel, you’ll see the list tables available from the OData v4 metadata. Depending your Acumatica speed, it can take a minute or so to load the metadata. Open the following URL on a web browser to view the raw table list data processed by ETL+:

    • YourOdatav4URL/$metadata (ex.: www.myacu.com/odatav4/$metadata)

  • Table Columns:

    • When adding tables from the list of OData v4 tables from ETL+ left panel, ETL+ will create a SQL statement showing columns from the OData v4 metadata. See the prior item URL rule.

    • You can also use the following Statement syntax to pull all OData v4 columns:

      • SELECT * FROM TableName

    • When loading an OData v4 table, you can also extract several columns that are part of related tables. To figure out what columns are available, open on a web browser the following URL:

      • YourOdatav4URL/$TableName (ex.: www.myacu.com/odatav4/customer)

Acumatica DAC Schema

You can find the Acumatica DAC Schema Browser and metadata at: DAC Schema Browser

 

Related Pages

 

Keywords: Acumatica Cloud,