Search

Configuring Manual Data Imports

Configuring a Data Import Definition

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 some configuration will be needed 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, we 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 provide a file that uses different entity names than the member names used in Fluence - in this case we 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.

Creating 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 to clear 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 the Configure
  2. Select the Data Import Definitions
  3. Select the Add button in the top right to add a new Data Import Definition.
  4. You are prompted to input the following:

Item

Description

Name

Enter a unique name for the Data Import

Schema

Select a schema used for the staging table from the drop list:

●        acctrec

●        client

●        dbo

Staging Table

Select the staging table associated with the Data Import from the drop list.

Model Name

Select the Data Model associated with the Data Import from the drop list.

 

  1. Click Save.
  2. The Data Import Definition is saved.
  3. You will now need to specify the Field Mappings and Clear Data Settings.

Specifying Field Mappings in the Data Import Definition

The Field Mappings editor is used to configure how the columns in the staging table map to the members and dimensions in the Data Model. For example, the staging table could contain columns for Account, Entity, Date, Currency and Amount. The other intersections could then be supplied using the Field Mappings editor.

To access the Field Mappings editor:

  1. Within the Data Import Definitions tab, right-click to select the definition.
  2. A context menu will appear. Select Field Mappings.
  3. The Field Mappings editor appears.
  • The Target Type column Indicates whether this is a Dimension or measure field.
  • The Target column lists the dimensions and measures in the Data Model.
  • There is one row for each dimension in the associated Data Model plus one row for each measure. Therefore if your data Model has 11 dimensions plus one measure, there will be 12 rows in the Field Mappings editor.

image6.png

  1. Select a Mapping Method for each Target.
    How you fill out the remainder of the Field Mappings depends on a combination of the Target Type and the Mapping Method selected. Use the following table as a guide:

Mapping Method

Description

Fixed

(applies to Dimensions and Measures)

The Fixed Method means that you want to set the member that this dimension/measure should be set to regardless if the column exists in the import file or not. For example, there may be a Date column in the file, but you prefer to Fix it to a specific member.

 

●        For Dimensions set to Fixed, you will need to set:

    • Value column
    • Security Profile column (optional)

●        For Measures set to Fixed, you will need to set:

    • Value column

Match Key

(applies to Dimensions only)

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

●        For Dimensions set to Match Key, you will need to set:

    • Source Field (which column in the staging table)
    • Security Profile column (optionally)

Match Name

(applies to Dimensions only)

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

●        For Dimensions set to Match Name, you will need to set:

    • Source Field (which column in the staging table)
    • Security Profile column (optionally)

Map

(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 Fluence. You can choose to define one Map for members in all dimensions or you can choose to define one Map per dimension that needs to be mapped.

●        For Dimensions set to Map, you will need to set:

    • 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

(applies to Measures only)

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

●        For Measures set to Measure, you will need to set:

    • Source Field (which column in the staging table)
    • Flip Credits - for numeric values, you will need to indicate whether the amounts for Credit accounts should be flipped.

 

Specifying Clear Data Settings in the Data Import Definition

The Clear Data Settings editor is used to specify what data should be cleared in the Data Model before the data is loaded. 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. Within the Data Import Definitions tab, right-click to select the definition.
  2. A context menu will appear. Select Clear Data Settings.
  3. The Clear Data Settings editor appears. There is one row for each dimension in the model.
  4. For each dimension, select the Clear Data Option. Exactly what will be cleared will depend on what is selected for each of the dimensions in the Data Model. How you fill out the remainder of the Clear Data Settings depends on the Clear Data Option selected. Use the following table as a guide:

Clear Data Option

Description

Descendants Of

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

 

●        In this case you must specify a Value

Everything

Clears all members in the dimension.

 

●        In this case, you do not need to specify anything for Value/From/To columns

Fixed Range

Clears members in the range in the From and To columns - uses hierarchy order.
For example:
If From=Toronto and To=Vancouver, then it will clear any members in the hierarchy between Toronto and Vancouver (inclusive)

 

●        In this case, 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.

 

●        In this case, you do not 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.

  • In this case, you do not need to specify anything for Value/From/To columns

Staging Unique Value

This setting does a validation as well. 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 we set Staging Unique Value for the Currency dimension, the import will error out at runtime.

 

●        In this case, you do not need to specify anything for Value/From/To columns

 

Note:

The values in the Data Model that will be cleared, will be the intersection of all of the items specified in the Clear Data Settings. For example,

If you have selected:

  • Accounts = Everything
  • Time = Fixed (Jan..Feb)
  • And your Data Model has data in the time periods Jan through Dec

Then only the data in Jan and Feb will be cleared for all Accounts.