1. Administering Data Management for Oracle Enterprise Performance Management Cloud
  2. Integration Tasks
  3. Working with Import Formats
  4. All Data Types Data Loads
  5. Setting the Import Format for Multi-Column Data Types

Setting the Import Format for Multi-Column Data Types

You can load data from multiple columns using a combination of column expressions and driver expressions as shown below:

  • A Column expression is used to specify the columns to import. You can import a contiguous set of columns or a non-contiguous set of columns.

    You specify contiguous columns by using starting and ending columns. For example, 5,10 indicate columns 5 through 10.

    You specify non-contiguous columns by using column1 | column2 | column3. For example, 5|7|10 indicates import columns 5, 7 and 10.

  • A driver expression can be used to specify the dimension and target value for each column. The driver dimension is the member into which data is loaded. You can have one driver dimension per load, but multiple members can be defined for the driver dimension. The driver dimension is only applicable to the multi-column type.

    The member assignment in the data rule is similar to the multi-period assignment. You can assign the driver member in the header record, import format (member expression), or data rule. If you don't specify a header record or member expression in the import format, you can select the member(s) in the data load rule.

    For more information, see Adding an Import Expression for a Data Driver and Assigning Driver Dimension Members.

  • For the multi-column type, you can use a header, multi-row header, or no header specified in the import format. These are the different formats:

    Note:

    In the import format you must have a column definition for the driver dimension defined in the data field. If your driver is "Account," then your import format must include a source column and field or start and end period for the account dimension. This must be a valid field in the file, or a valid start and end position in the file. This is not referenced by the process, but it must be valid for the process to execute.

    • For a file with a header record, use the format Driver=<Dimension Name>; Header=<Row Number>; Column=<Column Numbers>.

      For example, when the import format definition Driver=Account;HeaderRow=1;Column=2,4 is applied to the following sample data file: 

      Entity,ACCT1,ACCT2,ACCT3
Entity01,100,200,300

      This tells the system that row 1 is the header, and data starts in row 2. In column 2, the entity is the first value, and then the next three columns are the values for ACCT1, ACCT2 and ACCT3.

    • For a file with multiple row headers (driver members don’t line up with the data column), you can use a modified header expression. For example, when you export data from Essbase as in the following data file, the data column header is a new row and does not line up data. 

      "Period","Consolidation","Data Source","Currency","Intercompany","Entity","Movement","Multi-GAAP","Product","Scenario","Years","View","Account"
"FCCS_Sales","FCCS_Cost of Sales"
"Jan","FCCS_Entity Input","FCCS_Data Input","Entity Currency","FCCS_No Intercompany","01","FCCS_No Movement","FCCS_Local GAAP","P_110","Actual","FY15","FCCS_Periodic",3108763.22,2405325.62
"Jan","FCCS_Entity Input","FCCS_Data Input","Parent Currency","FCCS_No Intercompany","01","FCCS_No Movement","FCCS_Local GAAP","P_110","Actual","FY15","FCCS_Periodic",3108763.22,2405325.62

      With a multi row header, you identify header row that contains the driver information to the system. When the header row is specified as Header=2,1, this means that the header starts at row 2, and the driver members start at column 1.

      In another example, say your second header is A,B,C,D and columns are 10 to 13 for these values. If you set column expression to 10|12,13, then the B member and its values (at column 11) are skipped.

    • To load multiple columns without a header record in the data file, use the import format definition Driver = <Dimension Name>; Member = <List of Members>; Column=<Column Numbers>. Use this method when to skip a source column in the source record.

      For example, when the import format definition Driver=Account;member=ACCT1, ACCT2, ACCT3;Column=2,4; is applied to the following data file: 

      Entity01,100,200,300

      you tell the system to include entity as the first value, and then for the next three columns to use driver dimension members values from ACCOUNT; ACCT1, ACCT2 and ACCT3.

  • For data source application types, you assign the driver dimension, but the system assigns row 1 as the header. You can load multiple columns by selecting the columns from the Add Import Format Mapping Expression screen.