Configure the data import definition to match items from the staging table to the data model. This ensures the data is consistent and properly transferred. This process involves specifying field mappings and setting clear data parameters before loading the data.

When the File Import is run from a Workflow task, the data from the file is first loaded into the Staging Table. There is no additional configuration needed to load a file to a Staging Table because both the file format and the staging table columns should match.

The second phase of the import is to move the data from the Staging Table to the Data Model. The Staging table and Data Model may not have the same format, so few configurations are required to handle a variety of scenarios. For example:

  • The file coming out of the source system may not include the time period. In this case, you need to define which time period it should be loaded to using the Data Import Definition.
  • The file coming out of the source system includes a key for the cost center instead of a name
  • The source system may give a file that uses different names for entities than the names for members used in the system. In this case, you need to set up Data Mappings.

The Data Import Definition specifies how each item in the Staging Table should be mapped to the Data Model.

Create a new data import definition

The Data Import Definition editor is used to configure:

  • How each item in the Staging table should be mapped to the Data Model.
  • Which data should be cleared in the Data Model before loading the data from the Staging Table to the Data Model?

To access the Data Import Definition editor:

  1. Navigate to Configure.
  2. Select Data Import Definitions.
  3. Select Add in the top-right to add a new definition.
  4. You are prompted to input:
ItemDescription
NameEnter a unique name for the Data Import.
Schema

Select a schema used for the Staging Table from the dropdown list:

  • acctrec
  • client
  • dbo
Staging TableSelect the Staging Table associated with the Data Import from the dropdown list.
Model NameSelect the Data Model associated with the Data Import from the dropdown list.
Assign RolesAdd, delete, and assign read or write roles to data imports. 
  1. Select Save to save the definition.

Assign roles

Data Import Logs enforce the role security set on each Data Import Definition. The system filters log lines based on the role assigned to the user. This means users only see the log lines they are allowed to see.

The system runs the security assigned per definition and per user. A user can read a definition's log lines only if they hold a role assigned to that definition with at least read or write access. When no role is assigned, the system leaves the logs visible to all users. 

Role assigned to definitionUser access level on that roleCan view the definition's log lines
YesReadYes
YesWrite (implies read)Yes
YesNone / Not assignedNo
No role assignedAnyYes (open to all users)

You need to specify Field Mappings and Clear Data Settings.

Specify field mappings in Data Import Definition

Use the Field Mappings editor to configure how the columns in the Staging Table map to the members and dimensions in the Data Model.

For example, the Staging Table can contain columns for Account, Entity, Date, Currency, and Amount. You can then supply the other intersections using the Field Mappings editor.

To access the Field Mappings editor:

  1. Right-click to select the definition in the Data Import Definition tab.
  2. Select Field Mappings in the context menu.
  3. The Field Mappings editor displays:
    • Target Type: Indicates whether it's a dimension or a measure field.
    • Target: Lists the dimensions and measures in the Data Model.
    • One row appears for each dimension in the associated Data Model. Additionally, one row for each measure. If your Data Model has 11 dimensions and one measure, the editor will have 12 rows.
  4. Select a Mapping Method for each target.

Filling in the remaining field mappings depends on the combination of the Target Type and the Mapping Method selected.

Mapping MethodDescription

Fixed

It applies to Dimensions and Measures.

You want to set the member that this measurement/measure should be set to, even if the column isn't in the import file. 

For example, there may be a Date column in the file, but you prefer to fix it to a specific member.

  • For dimensions to be set to Fixed, configure:
    • Value column
    • Security Profile column (optional)
  • For Measures to be set to Fixed, configure:
    • Value column

Match Key

It applies to Dimensions only.

Use the Key indicated in the import file for this dimension.

  • For dimensions to be set to Match Key, configure:
    • Source Field, which column in the Staging Table.
    • Security Profile column (optionally).

Match Name

It applies to Dimensions only.

The Match Name Method means that for this Dimension, use the Member Name indicated in the import file.

  • For Dimensions to be set to Match Name, configure:
    • Source Field, which column in the Staging Table.
    • Security Profile column (optionally).

Map

It applies to Dimensions only.

The Map Method means that you need to map the member name in the import file to a valid member name in the system. You can choose to set up one Map for members in all dimensions. 

Additionally, you can choose to set up one Map for each dimension that needs to be mapped.

  • For Dimensions to be set to Map, configure:
    • Source Field, which column in the Staging Table.
    • Member Map: Select a Member Mapping file to use for the mapping.
    • Security Profile column (optionally).

Measure

It applies to Measures only.

The Measure Method only applies when the Target Type is Measure. It loads the value or amount in the data import file to the measure.

  • For Measures to be set to Measure, configure:
    • Source Field, which column in the Staging Table.
    • Flip Credits: For numeric values, indicate whether the amounts for Credit accounts should be flipped.

Specify Clear Data settings in Data Import Definition

Specify what data to clear in the Data Model before loading data in the Clear Data Settings editor.

For example, if you wish to clear the entire fact table of all records, you would set the Option to Everything for all dimensions.

To access the Clear Data Settings editor:

  1. Right-click to select the definition in the Data Import Definition tab.
  2. Select Clear Data Settings in the context menu.
  3. The Clear Data Settings editor appears. Each row represents a dimension in the model.
  4. Select the Clear Data Option for each dimension.
    What will be cleared depends on what you select for each dimension in the data model.

The remaining Clear Data Settings depend on the Clear Data Option selected. 

Clear Data OptionDescription
Descendants Of

Clear all descendants of the member specified in the Value column.

  • You must specify a value.
Everything

Clears all members in the dimension. 

  • There's no need to specify anything for Value/From/To columns.
Fixed Range

Clears members in the range in the From and To columns and uses the hierarchy order.

For example:
If From=Toronto and To=Vancouver, then it'll clear any members in the hierarchy between Toronto and Vancouver (inclusive). 

  • You need to specify the From and To values.
Staging Range

This is the default setting. Clear members from the first member to the last member in the staging table (source file) based on the hierarchy order.

  • There's no need to specify anything for Value/From/To columns.
Staging Source Values

Clears members based on the members found in the staging table. 

For example, if the Staging Table (source file) contains London and Toronto entities, and the Clear Data Setting is set to Staging Source Values for the Entity dimension, rows with London and Toronto will be cleared from the data model before the data is imported.

  • There's no need to specify anything for Value/From/To columns.
Staging Unique Value

It validates that only one value should be in the staging table and will clear that member only. 

For example, if our Staging Table (source file) contains CAD and USD for currency, and the Staging Unique Value for the Currency dimension is set, the import errors out at runtime.

  • There's no need to specify anything for Value/From/To columns.

Note: The values in the Data Model that'll be cleared, are the intersection of all of the items specified in the Clear Data Settings. For example, if you have selected:

  • Accounts = Everything.
  • Time = Fixed (January and February).
  • And your Data Model has data for the time periods January through December.

Then only the data in January and February is cleared for all accounts.