Show TOC

Function documentationData Transformations Locate this document in the navigation structure

 

You define data transformations so that you can map external data to internal Planning and Consolidation data structures.

Example Example

When performing a data import, the source data may not be in the correct format. The dimension names in the source data may not match the dimension names in Planning and Consolidation. In this case, you map the external dimensions to the internal dimensions in the conversion file. The Data Manager import package reads the transformation file (which has a pointer to a conversion file) and correctly maps the data.

End of the example.

Prerequisites

To perform data transformations, at least two Interface for Excel workbook files must exist. The required files are described in the following table:

File

Description

Transformation file

This file allows you to set up the rules for reading data from an external source and put it in the proper form for your system database. Transformation files are Microsoft Excel files that contain one worksheet named Instructions. The Instructions worksheet has the following sections:

  • *Options

  • *Mapping

  • *Conversion

Conversion file

This file allows you to map member names from external to internal dimension structures. You can set up multiple sheets in a conversion file so that many transformations can access the same conversion workbook. You can have one conversion file per dimension. See Data Conversion Maintenance.

Features

After setting up a transformation file and a conversion file, you run packages using these files to define a data transformation. See Running a Package.

Note Note

All dimensions of an application must be mapped in the transformation. If the import file does not have a dimension (such as DATASRC), then you would use *NEWCOL (see below).

End of the note.

Options Section

The *Options section of the Transformation file contains definitions for various options that you can set for your transformation. The following table contains a list of available options and descriptions for each option:

Option

Default

Description

AMOUNTDECIMALPOINT = text character

. (period)

This option allows you to specify a nonperiod decimal point for countries that use a different character, such as a comma. The character specified in this option must differ from the character specified for the DELIMITER.

CONVERT_INTERNAL = YES | NO

NO

This option compares input member names with columns in the conversion file.

If NO, the input member names are compared with the external column in conversion file.

If YES, the input member names are compared with the internal column in conversion file.

Recommendation Recommendation

Do not change the default setting. This setting is here for the purposes of backward compatibility only. There are no reasons to change this in Planning and Consolidation 7.5 and later versions.

End of the recommendation.

CONVERTAMOUNTWDIM = dimension name

<Account>

This option specifies which dimension to look at for value calculations. You must specify a dimension conversion sheet using the Amount *Conversion option. For more information about conversion files, see Data Conversion Maintenance. If there is no formula in the Formula column of the Amount conversion sheet, this parameter has no effect. For example, if the Formula column has the following formula: <value>*1.10, all accounts are increased by 10% during the conversion.

CREDITNEGATIVE = YES | NO

YES

This option reverses the signs for a given account. If this parameter is set to NO, the option reverses the sign for all amounts referring to an ACCOUNT type (LEQ, INC).

DELIMITER = text character| SPACE | TAB

, (comma)

This option defines the single character that is the delimiter between columns of the output if the FORMAT option is set to DELIMITED, Use the keywords SPACE or TAB if the columns are delimited by spaces or tabs.

The delimiter character must be unique and must be the delimiter character used in the file. Otherwise, the Data Preview displays incorrectly.

EXPORTFORMAT = CATEGORYBYTIME | MULTIWITHHEADER | MULTIWITHNOHEADER

There is no default. This option must be defined.

This option defines processing associated with an exported file.

Using the CATEGORYBYTIME option generates an output file categorized by time period.

Using the MULTIWITHHEADER option generates an output file with header information.

Using the MULTIWITHNOHEADER option generates the same output file as MultiWithHeader without the header titles

FORMAT = DELIMITED | FIXED | CATEGORYBYTIME

There is no default. This is a required option.

This option defines the format of the data in the input or output file. DELIMITED means that there is a special character between each column of data, defined by the DELIMITER option (above).

FIXED means that the data is in a fixed field format. For this setting, use the FCOL mapping option.

CATEGORYBYTIME means that the data is listed by categories that represent time periods.

HEADER = YES | NO

YES

This option specifies if your data has a header row. If this parameter is set to YES, your input file contains one header row that defines the fields. If you do have a header row, you can refer to a field by name in the MAPPING section.

MAXREJECTCOUNT = empty string | -1 | positive number

empty string

This option specifies the number of rejected records at which to stop processing if you are validating records. A value of -1 implies that the Data Manager should keep processing, no matter how many rejected records exist. The default value is 500, which can be represented by an empty string.

NULLAMOUNTVALUE = text character

a space character

This value allows you to assign a valued to null records.

OUTPUTDELIMITER = text character| SPACE | TAB

, (comma)

This option defines the single character that is the delimiter between columns of the output if the FORMAT option is set to DELIMITED, Use the keywords SPACE or TAB if the columns are delimited by spaces or tabs.

The delimiter character must be unique and must be the delimiter character used in the file. Otherwise, the Data Preview displays incorrectly.

OUTPUTHEADER = text string

empty string

This option allows you to create a custom header to export data. It is ignored when the transformation file is used to import data.

You can use {CRLF} to create more than one row for the header. For example: MyCompany Data{CRLF}Category,Time,Account, Entity,Datasrc,Intco,RPTCurrency,Amount.

Note Note

The complete OUTPUTHEADER text string should be in the same cell in the transformation file.

End of the note.

ROUNDAMOUNT = integer

There is no default. You must define this option.

This option specifies the amount of decimal places to which values are to be rounded during the transformation. By default, there is no rounding during data transformation. If you use this option, you must enter a parameter. The integer can be zero or any positive integer.

SKIP = integer

0 (zero)

This option specifies the number of lines to skip at the top of a data file.

Example Example

Use this option to skip header lines during transformation.

End of the example.

SKIPIF = text string | text string2

empty string

This option causes the transformation to skip a line in the data file if it begins with the specified strings. The strings are separated by |, in the above case. If a record begins with<text string> or <text string2>, the record is skipped. To include all lines in the transformation, set this option to an empty string. If you use this option, you must enter a parameter.

You can skip null database records by setting SKIPIF = NULL.

VALIDATERECORDS = No | Yes

No

This option specifies whether or not to validate records. If this parameter is set to No, the validation does not occur. If this parameter is set to Yes, check the following items before the import:

  • Validate the mapping.

  • Ensure that the members exist.

  • Verify that the record is proper for the Planning and Consolidation application.

When this option is enabled, the following rules apply:

  • If a member ID is not mapped, the record associated with the ID is rejected during validation and ignored.

  • If the import file has an extra field that is not mapped, all records are rejected during validation.

VALIDATERECORDS is only relevant for importing files. If you are using Planning and Consolidation for the Microsoft platform, ensure that VALIDATERECORDS is set to No when exporting files.

Mapping Section

The *Mapping section of the transformation file defines how data is mapped to your system database.

Example Example

For a header row and that defines a column named AccountVal, you can map the Account dimension to the Account field from the data file this way:

Account = AccountVal

End of the example.

The following table details the available mapping functions:

Mapping Function

Parameters

Description

*COL(A)

A = column index in the data file

This function defines a dimension for a field in the data file when the data file does not have a header row. See the note below about automatic field names when you do have a header row.

Example Example

Account =*COL(2)

End of the example.

*COL(A, B:C)

A = column index in the data file

B:C = the start and end positions within the column

This function defines a dimension for a subset of a field in the data file.

Example Example

Account=*COL(3,1:4)

End of the example.

*FCOL(A:B)

A:B = the start and end positions within the data row

This option allows you to define the start and end character columns for each field when you are using Fixed format data files, In the example below, the columns 4 through 7 represent the Account dimension.

Example Example

Account=*FCOL(4:7)

End of the example.

*MVAL(A:B)

or

Dimension =*MVAL(keyfiguretype1|dim_mem1||keyfiguretype2|dim_mem2||keyfiguretype3|dim_mem3)

A:B = the start and end time period columns

This function supports the following types of usage:

  • Allows you to use multiple time period columns

    Example Example

    If you have a number of consecutive columns that are based on time periods, you can use this function to include all of them. In the following set of columns, you can use the following command:

    Time = *MVAL(7:18)

    Account,Category,DATASRC,entity,IntCo,RptCURRENCY,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

    Using this command requires that you use a time.xls conversion sheet. To see the sample time.xls conversion file, open the existing conversion definition window. If your data does not have a header row, the time periods are named P1, P2, P3,...,PN by default.

    End of the example.
  • Allows you to convert multiple key figures to one key figure according to the following guidelines:

    • The dimension must be an existing column in the cube.

    • The dimension members in dim_mem must be the noncalculated members of the dimension.

    • The keyfiguretype must be the field name of the source data.

    • You can directly input the dimension members in the dim_mem, or input any existing mapping command in the dim_mem, such as *newcol, *col or *newcol(cash)+*col(1).

      Example Example

      Source data:

      Account, Entity, Data source, LC, GC, TC.

      Cash, SalesItaly, Input, key1, key2, key3

      Mapping command:

      Account=*MKEY(LC|*NEWCOL(Cash_lc)||GC|*COL(1)+*STR(gc)||TC|*NEWCOL(Cash_tc))

      Target data:

      Account, Entity, Data source, keyfigure

      Cash_lc, SalesItaly, Input, key1

      Cash_gc, SalesItaly, Input, key2

      Cash_tc, SalesItaly, Input, key3

      End of the example.

      Example Example

      Source data:

      Account, Entity, Data source, LC, GC, TC.

      Cash, SalesItaly, Input, key1, key2, key3

      Mapping command:

      Keyfiguretype=*MKEY(LC|*NEWCOL(LC)||GC|*STR(GC)||TC|*NEWCOL(TC))

      Target data:

      Account, Entity, Data source, Keyfiguretype, keyfigure

      Cash, SalesItaly, Input, LC, key1

      Cash, SalesItaly, Input, GC, key2

      Cash, SalesItaly, Input, TC, key3

      End of the example.

*NEWCOL(A)

A = dimension member

This function creates a new field with the given value.

Example Example

Account=*NEWCOL(Revenue)

End of the example.

*Str(string)

Not applicable

This function adds a text string to the members of a column. This function is useful if you need to map data file fields to fields in your database that have the same names, except that the field names contain extra characters (either before or after the name).

Example Example

Entity = *Str(NE) + *COL(1)

End of the example.

*PAD

Not applicable

This function is used for padding imported fields.

Example Example

To prepend the letter A to a field (such as the member ID) use *PAD(A). If the field contains 123 before the command, the resulting value is A123.

End of the example.

*If (Condition1 then Action1;Condition2 then Action2;Default Action)

Planning and Consolidation takes the following actions, depending on the results of the condition evaluations:

  • Condition1 - If this evaluates to True, map using Action1

  • Condition2 - If Condition1 is "False" and Condition2 is "True," map using Action2

  • Default Action - If both Condition1 and Condition2 are "False", map using this Default Action

  • Condition1 and Condition2 can contain multiple items that are added together (using the plus + sign).

This function allows you to test a series of conditions, and to apply a corresponding action if the condition is true.

Example Example

Product=*if (Product+ID+Entity = *str(148552) then *str(MHarn);ID(1:1) = *str(C) then *str(XX) ; *str(YY))

End of the example.

Explanation: This means that if the sum of the product, ID, and entity value are equal to 148552 then change product value to MHarn or

Else IF first value of ID field is equal to C then Change Product value to XX

Else change product value to YY

Note Note

The *STR() function must be used when evaluating numeric constants in an *IF statement.

End of the note.

Example Example

Examine the following series of data sets, mapping function examples, and result sets to gain an understanding of how the *IF function can be used in various scenarios:

Data Set 1:

PRODUCT,ID,ENTITY

14,85,52

AB,CD,EF GH,IJ,KL

Mapping Function 1:

Product=*IF (product+ID+Entity=*Str(148552) then *str(MHarn);ID(1:1) = *str(C)

then *str(XX) ; *str(YY))

Result Set 1:

PRODUCT,ID,ENTITY

MHarn,85,52

AB,XX,EF

YY,IJ,KL

End of the example.

Data Set 2:

Entity, SEntity

U1000000, US01

Z2000A01, CA03

K3430000, JP04

Mapping Function 2:

Entity=*IF(*col(1,1:1)=U then SEntity;*col(1,1:1)=Z then *col(1,3:6); *STR(ERR))

Result Set 2:

Entity, SEntity

US01, US01

A01, CA03

ERR, JP04

Conversion Section

The *CONVERSION section of the transformation file defines which conversion sheet to use with which dimensions. Use the following syntax to associate a dimension with a conversion sheet:

Dimension Name = [COMPANY]WorkbookName[!SheetName]

Note Note

Items in brackets are optional.

End of the note.

The following table describes the variables.

Variable

Description

DimensionName

Either the dimension name to correlate with the conversion file or the keyword Amount. Use the Amount option to specify the conversion file to be used if you specify the ConverAmountWDim option in the *Options section. This allows you to use a formula to scale the input or output values.

Workbookname

The name of the conversion file

SheetName

This is the name of the worksheet to use within the conversion file. If a name is not specified, the system assumes the sheet name is Conversion.

[COMPANY]

If [COMPANY] is defined then the Data Manager tries to obtain the conversion file from the main company Data Manager folder. Otherwise the Data Manager looks in the appropriate Site folder.

Activities

  • To create a new transformation file, select   eData   New Transformation File.   Enter data as required.

  • To edit transformation files, select   eData   Manage Transformation Files,   then browse to the desired file. Enter data as required.

  • To copy a transformation file, open the file you want to copy by selecting Manage Data from the Getting Started - Interface for Excel action pane. Select   Maintain transformations   Manage existing transformation.  Open the desired file and select Copy Transformation File.

  • To copy dimension files between applications, you create a transformation file to map the dimensions. See Sample Transformation File.

Example

In this example, AccountConv.xls is the name of the Account dimension conversion file.

Account = AccountConv.xls

Amount = AccountConv.xls (Use the Amount keyword with the ConvertAmountWDim transformation option.)

Account = AccountConv.xls!newaccount

Account = [COMPANY]AccountConv.xls!newaccount