 Import process (data)

The Import process for data copies data from an external source, such as a text file, an Excel file, an SQL Server database, or the staging database, to a model.

 Tip: In Process Manager, if you are creating a process group that includes more than one Import process, you may want to distinguish them by placing each in its own folder with a descriptive name; for example, Import dimension, Import data, and so on.

General tab

Description: Optional. Up to 250 characters.
Import Type: Select Import data. (The import dimension option is described here.)
Target: The connection and the cube to import the data to.

Source tab

File: To select a process file A data file used in the various import and export automated processes in Process Manager. A process file contains data or dimensions and is Excel (.xls or .xlsx) or text (.txt or .csv) format., click .

For an Excel workbook, in Options you can also specify the sheet to import from and whether to use the first row as headers.
Data connector: To select the data connector that contains the file that you want to import the data from, click .

Import Options tab

Handling Import Data
- Write option: Your can either have new data added to the existing data, or replace the data of members that are affected by the import.
- Skip validation: Turn on if you previously ran an import of similar capacity and received a memory-related error.

Time Format: Time across is the format used by most general ledger systems to store data. As the name implies, time periods run horizontally, as in a typical spreadsheet layout. This format requires only one field to describe a value for a time period; for example, to import the value for the month of January, you only need to import the field January.

Account	Organization	January	February	March
100201	1021	1012.30	223.03	330.00
100201	1022	750.43	203.82	198.03
100202	1024	1902.28	0.00	230.39

The Time down format uses two fields to describe a value for a time period—in the following example, Month and Value:

Account	Organization	Month	Value
100201	1021	Jan	1012.30
100201	1021	Feb	223.03
100201	1021	Mar	330.00
100201	1022	Jan	750.43
100201	1022	Feb	203.82
100201	1022	Mar	198.03
100202	1024	Jan	1902.28
100202	1024	Feb	0.00
100202	1024	Mar	230.39

Choose a Start and End date range for the data.

Debit/Credit: Many general ledger systems export data with debit and credit account balances that contain negative numbers. To ease the task of adjusting negative numbers, the debit/credit options allow you to reverse the signs in the natural debit or credit account so that all the values are loaded correctly, regardless of their true debit or credit value. For example, a revenue account (credit) is exported as a negative value. This needs to be reversed in FP&A in order to display positive values.
Rejected Records: Optionally, you can specify the number of rejected import records to save. (After running the process, you have the option of viewing the rejected records.)
Processing Options
- Incremental model update: (Disabled by default) Turn on to allow new data to be added directly (as if you are doing data entry). Bonus: you will not need to run a model refresh later. This option is useful if you are not making a large number of data changes, or if the fact table is large and is taking longer to refresh.

Target Mapping tab

The final step is to map or match the source data fields to the target fields, for the destination dimensions. You can do this manually or use the Auto Match  feature.

Target: Shows the properties of the dimension you selected in the General tab.
Source: Select a data import source for the target dimension.

To map the fields, click . (You will be able to edit/reject the results.)
Transformation: Optionally, you can add a mapping table; browse to select an existing one or click  to open the Mapping Table Editor to create one.

If for Source you chose Specific member, click  to open Member Selector and choose a member.

Save the process group.

 Note: To see the imported data, after running the Import process, you must update the cube.

 Rejected records

When data fails to import, you can see a list of the failed records and the problem with each, either by using the Rejected Records tool or the Task Log. Both give you the option to save the records to an Excel file.

You can see up to 1,000 unique rejected records.

View rejected records in Process Manager

In Process Manager, click  Rejected Records.
Select the target model and then the Import process.

View rejected records in the Task Log

In Process Manager, click  Task Log.
Select the process and click  Results.
In the Execution Results dialog, select the specific Import process.
Expand the Rejected Records drawer.