Import Master Data from a File

You can define a package to upload master data from a flat file using the process chain /CPMB/IMPORT_MASTER.

The data can be imported from a file stored on the Application server and FTP source by entering the fully qualified path and file name (see INFILE Prompt() Command).

Activities

Create a Data Manager package by using the corresponding process chains (see Package Usage).
Run the Data Manager package as follows:
1. Log on to the Planning and Consolidation Office module.
2. On the action pane, choose Manage data Run a package .
3. Choose the package created in step 1 and choose Run.
4. Choose the import file.
5. Choose a transformation file.
6. Choose the name of the destination dimension.
7. Choose the Write mode.
  
  Note
  Master data is written into the Planning and Consolidation dimension by update mode. This option indicates how to write the hierarchy into the dimension since you can maintain the hierarchy or not maintain the hierarchy in the source flat file. If you do maintain the hierarchy column in the source flat file, then the result is the same regardless of the option you choose. The target hierarchy is updated. If you do not maintain the hierarchy column in the source flat file and you choose Overwrite, the original hierarchy is deleted, and then the new hierarchy is written. For Update, the original hierarchy is kept, and then the new hierarchy is used to update the original one. See Write Mode Example.
  
  End of the note.
8. Choose one of the following options:
  - Run now
  - Schedule
  - Run now & schedule
9. Choose Finish.

You can change the default package instructions (see Modifying the Dynamic Script of a Package).

Dynamic script task parameters

Task	Parameter	Default Value	Description
/CPMB/MASTER_CONVERT	DIMNAME		Dimension name
/CPMB/MASTER_CONVERT	FILE		File name
/CPMB/MASTER_CONVERT	SPACKAGE_SIZE	0	Package size
/CPMB/MASTER_CONVERT	TRANSFORMATIONFILEPATH		Transformation file
/CPMB/MASTER_LOAD	DIMNAME		Dimension name
/CPMB/MASTER_LOAD	FORMULA_FILE_NB		INTERNAL
/CPMB/MASTER_LOAD	INPUTNO		INTERNAL
/CPMB/MASTER_LOAD	WRITEMODE	1	Write mode: 1 = Overwrite hierarchy 0 = Update hierarchy

Example Example

Original Master Data that existed in the target dimension:

ID, EVDESCRIPTION, PARENTH1, ACCTYPE, SCALING, FORMAT, RATETYPE, FORMULA, IS_INPUT, STORED_CALC

CE0004000, Personnel Costs, , EXP, Y, , AVG, CE0004000, ,

CE0004010, Personnel Expense, CE0004000, EXP, Y, , AVG, CE0004010, Y,

CE0004020, Wages and Salaries, CE0004000, EXP, Y, , AVG, CE0004020, Y,

CE0004030, Social Contributions, CE0004000, EXP, Y, , AVG, CE0004030, Y,

Example 1:

Source master data to be imported:

ID, EVDESCRIPTION, PARENTH1, ACCTYPE, SCALING, FORMAT, RATETYPE, FORMULA, IS_INPUT, STORED_CALC

CE0004000, Advertising Costs, , EXP, Y, , AVG, CE0004000, ,

CE0004010, Operating Cost, , EXP, Y, , AVG, CE0004010, ,

CE0004020, Operating Supplies, CE0004010, EXP, Y, , AVG, CE0004020, Y,

CE0004030, Maintenance Supplies, CE0004010, EXP, Y, , AVG, CE0004030, Y,

Since hierarchy information (PARENTH1 column) exists in the new master data, the final master data is the same in overwrite mode and update mode.

Final master data in the target dimension after importing:

ID, EVDESCRIPTION, PARENTH1, ACCTYPE, SCALING, FORMAT, RATETYPE, FORMULA, IS_INPUT, STORED_CALC

CE0004000, Advertising Costs, , EXP, Y, , AVG, CE0004000, ,

CE0004010, Operating Cost, , EXP, Y, , AVG, CE0004010, ,

CE0004020, Operating Supplies, CE0004010, EXP, Y, , AVG, CE0004020, Y,

CE0004030, Maintenance Supplies, CE0004010, EXP, Y, , AVG, CE0004030, Y,

Example 2:

Source master data to be imported:

ID, EVDESCRIPTION, ACCTYPE, SCALING, FORMAT, RATETYPE, FORMULA, IS_INPUT, STORED_CALC

CE0004000, Advertising Costs, EXP, Y, , AVG, CE0004000, ,

CE0004010, Operating Cost, EXP, Y, , AVG, CE0004010, ,

CE0004020, Operating Supplies, EXP, Y, , AVG, CE0004020, Y,

CE0004030, Maintenance Supplies, EXP, Y, , AVG, CE0004030, Y,

Since hierarchy information (PARENTH1 column) does not exist in the new master data, the final master data is different for overwrite mode and update mode. The original hierarchy is deleted for overwrite mode, but kept for update mode.

Final master data in the target dimension after importing:

Overwrite mode:

ID, EVDESCRIPTION, PARENTH1, ACCTYPE, SCALING, FORMAT, RATETYPE, FORMULA, IS_INPUT, STORED_CALC

CE0004000, Advertising Costs, , EXP, Y, , AVG, CE0004000, ,

CE0004010, Operating Cost, , EXP, Y, , AVG, CE0004010, ,

CE0004020, Operating Supplies, , EXP, Y, , AVG, CE0004020, Y,

CE0004030, Maintenance Supplies, , EXP, Y, , AVG, CE0004030, Y,

Update mode:

ID, EVDESCRIPTION, PARENTH1, ACCTYPE, SCALING, FORMAT, RATETYPE, FORMULA, IS_INPUT, STORED_CALC

CE0004000, Advertising Costs, , EXP, Y, , AVG, CE0004000, ,

CE0004010, Operating Cost, CE0004000, EXP, Y, , AVG, CE0004010, ,

CE0004020, Operating Supplies, CE0004000, EXP, Y, , AVG, CE0004020, Y,

CE0004030, Maintenance Supplies, CE0004000, EXP, Y, , AVG, CE0004030, Y,

End of the example.