AX2425

About imports

Use imports to import external data into Axiom Budget Planning and Performance Reporting tables for reports or planning models and calculations.

Import sources

You can import data into Axiom Budget Planning and Performance Reporting from the following sources:

  • An external database
  • A file (delimited or Excel)
  • Special designated sources, such as Ellucian

Import Wizard

Imports are defined in the Import Wizard dialog. Imports consist of the following:

  • Source tab: Specifies the source of the data to be imported.
  • Variables tab: Defines variables to use in the import to dynamically change certain import settings.
  • Mapping tab: Specifies the destination table for the import and maps the import data to columns in the destination table.
  • Transforms tab: Defines transformation statements to be performed on the import data before it is saved to the destination table. Transforms can use SQL or built-in Axiom Budget Planning and Performance Reporting functions.

The Import Wizard also contains the Execute tab, which can run the import in preview mode for testing or run the actual data import.

Import process

When data is queried from the source file or database, it is first placed in a temporary table known as the temp table. The import can perform actions on the temp table before the data is saved to the destination table in Axiom Budget Planning and Performance Reporting, such as mapping or data transformations. Use the reserved term {temptable} to refer to this temporary table in SQL statements.

When you run an import, the following processes occur:

  1. If any import variables are defined on the Variables tab, you are prompted to select values for these variables. The selected variable values are substituted for the variable names within the import settings.

    When running the import using a Scheduler job, you must specify values for the import variables within the Scheduler import task or use job variables that populate the import variable values when the job is run.

  2. The import creates the temp table by querying data from the source database or by gathering data from the specified file. The settings on the Mapping tab determine the structure of the temp table. Insert additional columns that are not in the source file or table into the temp table by adding them as work column mappings.

  3. Any transforms defined on the Transforms tab are processed in the order listed. Transforms can edit the temp table directly andreference information held in other tables in the Axiom Budget Planning and Performance Reporting database. Transforms can also set values for transform variables, which are used in subsequent transform steps and in certain import settings.
  4. The temp table data is validated and saved to the destination table based on the destination column settings on the Mapping tab. If a column in the temp table is not mapped, that data is not saved.

If the import utility is a multiple-file import, steps 2 through 4 are performed for each file to be imported. For more information, see Import data from one or more source files.

Import save behavior

The system performs save-to-database process for imports as follows:

  1. If the destination table has validated columns (columns with an assigned lookup column), the temp table data is validated against the lookup columns before saving. If a data row contains an invalid value, that data row is invalid and cannot be saved.

  2. By default, temp table data is aggregated before the save is performed and duplicate rows (rows with the same key column values) are treated as follows:

    • Columns holding numeric data are summed.

    • For all other column types, the duplicate rows must have the same values. For example, if a column contains strings or dates, the duplicate rows must have the same string or the same date.

If the optional setting Aggregate rows on final save is disabled, no aggregation is performed on the temp table data. In this case, any duplicate rows are invalid and cannot be saved.

NOTE: Aggregation only applies when importing data to a data table. If the destination table is a reference table, aggregation is not allowed. If duplicate keys are present in the import data, those rows are invalid and cannot be saved to the reference table.

  • Blanks are not allowed in key columns. If a data row in the temp table contains a blank key value, that data row is invalid and cannot be saved.

If any invalid rows are present in the import data, the import behaves as follows:

  • By default, if any invalid data rows are present, the import ends, and no data is saved to the destination table.
  • If the optional setting Ignore lookup and key errors is enabled, the save-to-database process ignores the invalid data rows and only valid data rows are saved.