AX1190

Setting up file processing: Saving data in batches

Using file processing, you can save data to the database from an Axiom file. The processing type Save Data in Batches is intended for specialized use cases where multipass processing is desired but many records can be processed per pass.

The Save Data in Batches processing type works as follows:

  • You create an Axiom query to define the list of records to be processed. This query is then specified as the source query for the file processing action. The query should be set up as normal within the sheet, where it will serve as the main driver for processing records.

  • When file processing is initiated, Axiom runs the designated Axiom query in memory first to obtain the full list of records. The number of passes for the process is determined by dividing the total number of records by the specified batch size. For example, if the Axiom query returns 7,000,000 records and the batch size is 7,000, then there will be 1000 passes (each pass processing 7,000 records).

  • Multipass processing then begins. For each pass of the process, Axiom refreshes the file but only returns a subset of records (a "batch") to the Axiom query in the sheet. This process continues, iterating over multiple passes, until all of the records that were retrieved in memory have been processed.

  • Data is saved to the database according to the save options specified in the file processing setup. You can save data after each pass, or save data once at the end of the process, or save data to an output sheet (for testing).

  • Although the process is iterative and therefore considered "multipass processing," none of the normal multipass settings apply and will be ignored if set. No dimension filter is applied to each pass as when using normal multipass processing, because each pass encompasses multiple records.

NOTE: When using Save Data in Batches, the only valid use of the GetCurrentValue function is the PassNumber option to return the number of the current pass. All other options are inapplicable in this context. For example, GetCurrentValue() is inapplicable because there is no single current value to return; instead multiple values (records) are being processed in each pass.

To set up file processing to save data in batches:

  1. Open the file where you want to set up file processing, and enable it as follows: On the Axiom tab, in the File Output group, click File Processing > Add File Processing control sheet to active workbook.

    NOTE: In systems with installed products, this feature may be located on the Main tab (either directly on the tab, or on the Publish menu).

    The File Processing pane opens, and a sheet named Control_FileProcessing is added to the file.

  2. In the File Processing pane, for Processing Type, select Save Data in Batches.

  3. From the Save Data Mode list, specify how data should be saved:

    • Save After Each Pass: A save-to-database occurs after each pass.

    • Save Once at the End: The data from each pass is saved in memory until all passes are complete, and then the save-to-database occurs.

      You should save at the end if the process could result in multiple rows of data with the same key codes, so that the rows are summed before saving to the database, rather than having the data from one pass overwrite the other.

    • Save to Output Sheet: The data from each pass is collected and saved to an "output sheet" within the file being processed. No data is saved to the database. The output sheet is named SaveData_SheetName, where sheetname is the name of the sheet set up to save to the database.

      This option is primarily intended for testing the file setup, so that you can review the data that would be saved without actually saving it.

  4. For Source Worksheet, specify the worksheet that contains the Axiom query to use for the process.

  5. For Source Axiom Query ID, specify the Axiom query to use for the process.

    TIP: You can use the Preview Axiom Query Data button to see the number of records that will be processed and to preview the data to be returned by the query.

  6. Optional. For Batch Size, specify the number of records to include in each pass. By default this is set to 7,000.

    In most cases, you can leave the default batch size. However, you might consider making the batch size smaller if your in-sheet calc method uses many rows.

    For example, if your calc method is 1 row and the batch size is 7,000, that means the Axiom query will return and process 7,000 rows of data per pass. However, if the calc method is 20 rows and the batch size is 7,000, that means the Axiom query will return and process 140,000 rows of data per pass. In the latter case, the processing performance may be improved if you lower the batch size to lower the overall rows of data to be processed per pass.

  7. When you are finished configuring the file processing settings, click File Options > Save to save the settings in the file.

Users can now process the file. Although the traditional multipass settings do not apply, the file must be processed using Process File Multipass in order to enable the iterative pass behavior. This can also be initiated using the Process Save Data in Batches action in the File Processing task pane.