Show TOC

Function documentationData Transformation Maintenance Locate this document in the navigation structure

 

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

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

The following details the transformation and conversion file rights that are granted:

  • With the Execute task of DM security, a user can read, write, and select the transformation and conversion file in Company.

  • With the GeneralAdmin task of DM security, a user can read and write the transformation and conversion file in Company and Team.

  • With the PrimaryAdmin task of DM security, a user can read and write the transformation and conversion file in Company.

  • With the TeamLeaderAdmin task of DM security, a user can read and write the transformation and conversion file in Team.

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. You can use this option in the following two ways:

  • AMOUNTDECIMALPOINT = Any single character

    For example, a period (.) or a comma (,). The decimal point is either a period or a comma and there is no thousands separator.

  • AMOUNTDECIMALPOINT = Any two characters.

    The first character functions as the decimal point and the second functions as the thousands separator. There should be no space, tab, or any other character between the two characters.

    The default setting is that the decimal point of the signed data is a period and there are no thousand separators. For example, 10000.12.

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 in *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 at all. For example, in a case where the Formula column has the following formula: <value>*1.10, all accounts are increased by 10% during the conversion.

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.0 and later versions.

End of the recommendation.

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 if the FORMAT option is set to DELIMITED. Use the keywords SPACE or TAB if the columns are delimited by spaces or tabs.

ENDROUTINE

<empty>

This option specifies the filter value to call an End routine used in a BAdI.

FORMAT = DELIMITED | FIXED

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. Delimited format includes multiperiod and variant. FIXED means that the data is in a fixed field format. For this setting, use the FCOL mapping option.

Note Note

CATEGORYBYTIME is out of scope for Planning and Consolidation 7.5

End of the note.

Set FORMAT = DELIMITED for Variant format, which is still supported.

Variant allows the user to define the name and position of the dimensions using multiple lines beginning with an asterisk (*).

*CATEGORY=ACTUAL

*TIME=2007.JAN

*ENTITY, ACCOUNT, AMOUNT

STORE1, EXTSALES, 1203

STORE2, EXTSALES, 1203

The variable format allows the user to define the name and position of the dimensions using multiple lines beginning with an asterisk (*). These lines (as many as needed) represent a header section.

Besides a simple, one line header, the data file can also contain a variant header. The header can have several lines, each line starting with an asterisk (*). The following are examples of variant headers:

The following example illustrates how you can use the variable format syntax, using a three line header section:

*CATEGORY, TIME

*ENTITY, ACCOUNT, PRODUCT,

*AMOUNT

ACTUAL, 1999.JAN, UK, Sales, Sedan, 1203

ACTUAL, 1999.JAN, UK, Sales, Coupe, 2345

While the above example is simple, some extra features of the variable format demonstrate its potential as a more useful tool.

Repetitive field values can be moved into the header section. For example, the above file could also be read in the following format, where the value of the category (ACTUAL) and time (1999.Jan) are set in the header section and removed from the value rows.

*<DIMENSION>=<VALUE> specify the value of the dimension. This has the same effect as the *NEWCOL command in the mapping section. The lines starting with ‘ is the comment line that is skipped during process.

*CATEGORY = ACTUAL

*TIME = 1999.JAN

*ENTITY, ACCOUNT, PRODUCT,

*AMOUNT

UK, Sales, Sedan, 1203

UK, Sales, Coupe, 2345

If one of the predefined values changes in the body of the file, a redefinition of the current member can be inserted anywhere in the file and as many times as desired, like in this example:

If the *<Dimension> =<value> header occurs in the middle of data file, all the records following this header are affected, but the records before the header are not.

*CATEGORY, TIME

*ENTITY = UK

*ACCOUNT, PRODUCT, AMOUNT

ACTUAL, 1999.JAN, Sales, Sedan, 1234

ACTUAL, 1999.JAN, Sales, Coupe, 2345

*ENTITY = FR

ACTUAL, 1999.JAN, Sales, Sedan, 986

ACTUAL, 1999.JAN, Sales, Coupe, 945

The results of Example 3 are:

ACTUAL, 1999.JAN, UK, Sales, Sedan, 1234

ACTUAL, 1999.JAN, UK, Sales, Coupe, 2345

ACTUAL, 1999.JAN, FR, Sales, Sedan, 986

ACTUAL, 1999.JAN, FR, Sales, Coupe, 945

A set of members of one or more dimension can span across multiple columns (typically, 12 months of budget values). The following example of a header section accepts 12 value records:

*<Dimension> =<value1>,<value2> has the similar effect of an *MVAL command in the mapping section. This type of header occurs only in the beginning of the data file, not in the middle.

*CATEGORY=ACTUAL

*TIME=1999.JAN, 1999.FEB, 1999.MAR,1999.APR

*ENTITY, ACCOUNT, PRODUCT

*AMOUNT UK, SALES, SEDAN, 100, 200, 300, 400

The variable format allows you to specify which dimensions are in the page (CATEGORY=ACTUAL), which dimensions are in column (TIME=1999.Jan, 1999.Feb, and so on), and which dimensions are in row (ENTITY,ACCOUNT,PRODUCT,PERIODIC).

Irrespective of the format of the source file, the output file is always in the fixed format described for the input file. For example, the first line contains the delimited list of all dimension names with their corresponding positions for the data fields. All subsequent lines define one data cell per line.

The variable format file containing the 4 periods shown above generates the following output (if conversion of the members' labels is excluded):

CATEGORY, TIME, ENTITY, ACCOUNT, PRODUCT, AMOUNT

ACTUAL, 1999.JAN, UK, SALES, SEDAN, 100

ACTUAL, 1999.FEB, UK, SALES, SEDAN, 200

ACTUAL, 1999.MAR, UK, SALES, SEDAN, 300

ACTUAL, 1999.APR, UK, SALES, SEDAN, 400

FORMULA = LIB.LGF|LIB1.LGF

<empty>

This option sets the K2 functions referenced by the master data member’s formula attribute. This option is only used for importing master data. It is illegal when running other packages.

The libraries are syslib. The *syslib prefix is not needed here. Different libraries are separated by |.

Note Note

This is used for loading master data as is (new in Planning and Consolidation 7.0).

End of the note.

Example Example

DAVY_KP1_DEV7A2_Q_Mdxlib_1.LGF| DAVY_KP1_DEV7A2_Q_Mdxlib_2.LGF

End of the example.

HEADER= YES | NO

YES

This option specifies if your data has a header row. If this parameter is set to YES, then 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.

OUTPUTDELIMITER=

, (comma)

This is the same as DELIMITER, but defines the delimiter when using a transformation to export data. This option works only when exporting data.

OUTPUTFORMAT= NORMAL

Normal

It specifies the format of the data in the output file. Currently, only Normal format is supported.

OUTPUTHEADER=

<empty>

It allows you to use a custom header to export data. This option only works when exporting data. You can use {CRLF} to create more than one row for the header.

CATEGORY, TIME {CRLF} Entity, ACCOUTN, PRODUCT {CRLF} PERIODIC

The exported file is shown in below.

The above example should be placed all in the same one cell in the transformation file.

*CATEGORY, TIME

*ENTITY, ACCOUNT, PRODUCT

*PERIODIC

ACTUAL, 1999.JAN, UK, SALES, SEDAN, 1234

ACTUAL, 1999.JAN, UK, SALES, COUPE, 2345

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.

SELECTION = <Dimension1_techname>,<Dimension1_value>;<Dimension2_techname>,<Dimension2_value>

<empty>

This option sets the selection rules when you are extracting transaction data from an infoprovider. Use the following parameter definitions to set your selection:

  • <Dimension1_techname> is the technical name of the dimension. Navigation attributes are not supported.

  • <Dimension1_value> is the value of a condition statement set to this dimension. For one dimension, you can specify only one value.

Example Example

0SX_ACCS, US;0SX_CSLC, 1 is the result of the following selection statement: 0SX_ACCS=US and 0SX_CSLC= 1

End of the example.

This option is only used for importing transaction data from an infoprovider. It is illegal when running other packages.

By the default value, it is to load all data.

Note Note

This is used for loading transactional data as is.

End of the note.

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 contains <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.

SPECIFICMAPPING=YES/NO

No

It allows you to turn off the automatic dimension mapping feature. When set to YES, Data Manager does not automatically map dimension names. This option works both for importing and exporting and is especially useful for data export.

For example:

The columns in the database are CATEGORY, TIME, ENTITY, ACCOUNT, PRODUCT, SIGNEDDATA

  • For importing:

    The columns in source data are CATEGORY, TIME_NEW, ENTITY_NEW, ACCOUNT_NEW, PRODUCT, SIGNEDDATA

    • If SPECIFICMAPPING = YES

      This means, after transformation, all data should have the same columns that you define in the mapping section of the transformation file.

      You should write mapping rules in the mapping section such as:

      CATEGORY = CATEGORY

      TIME = TIME_NEW

      ENTITY = ENTITY_NEW

      ACCOUNT = ACCOUNT_NEW

      PRODUCT = PRODUCT

      SIGNEDDATA = SIGNEDDATA

      This means, even when the column names of source and target are the same, you have to write the mapping rules for all columns.

      If you write the following mapping rules:

      CATEGORY = CATEGORY

      TIME = TIME_NEW

      ENTITY = ENTITY_NEW

      ACCOUNT = ACCOUNT_NEW

      This means to import only these four specified columns from the source data file to the database.

      This case can be applied in the scenario that the source data file has more columns than the target database.

    • If SPECIFICMAPPING = NO (default value)

      This means, after transformation, all data should have the same columns with the target database.

      You should write mapping rules in mapping section like:

      TIME = TIME_NEW

      ENTITY = ENTITY_NEW

      ACCOUNT = ACCOUNT_NEW

      This means, when the column names of source and target are the same, you need to write only the mapping rules for all columns that have different names between source and target. The other columns of the database automatically map from the source columns with the same names. In this example, all six columns are imported into the database.

  • For exporting:

    The source columns in cube data are CATEGORY, TIME, ENTITY, ACCOUNT, PRODUCT, SIGNEDDATA.

    • If SPECIFICMAPPING = YES

      To export all columns of the source database, you should write mapping rules in the mapping section such as:

      CATEGORY = CATEGORY

      TIME_NEW = TIME

      ENTITY_NEW = ENTITY

      ACCOUNT_NEW = ACCOUNT

      PRODUCT = PRODUCT

      SIGNEDDATA = SIGNEDDATA

      This means, even when the column names of source and target are the same, you have to write the mapping rules for all columns. Then the exported data file has these six columns, three of them having the same names as the source database columns.

      If you only want to export several specific columns, write the mapping rules such as

      CATEGORY = CATEGORY

      TIME_NEW = TIME

      ENTITY_NEW = ENTITY

      The exported data file contain only these three columns; the other columns are not exported.

    • If SPECIFICMAPPING = NO (by default)

      To export all columns of the source database, you should write mapping rules in the mapping section such as:

      TIME = TIME_NEW

      ENTITY = ENTITY_NEW

      ACCOUNT = ACCOUNT_NEW

      This means that when the column names of the source and target are the same, you only need to write the mapping rules for the columns that have different names between the source and target. The other columns of the database are automatically mapped to the target columns with the same names. In this example, all six columns are exported from the database

STARTROUTINE

<empty>

This option specifies the filter value to call a Start routine used in a BAdI.

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 transformation.

  • 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 the application.

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 database.

    • 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 input the dimension members in the dim_mem with a *STR mapping command, or input any existing mapping command except *IF 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(colname,length,direction,letter)

  • colname – column name in the source header

  • length – the length of the output value

  • direction – the direction padding the letter to the input value; could be L (left) or R (right)

  • letter – the letter to be padded; by default, is a space

This function is used for padding imported fields.

Example Example

To prepend the letter A to the field time (such as the member ID) use Time = *PAD(time,5,L,A). If the time field contains 123 before the command, the resulting value is AA123. If you change the direction to R, the result is 123AA.

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. You cannot join *IF with other commands using a plus sign (+), such as *IF(..)+*COL(1).

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 or does not exist, then the Data Manager tries to obtain the conversion file from the Main company Data Manager folder. If [TEAM_NAME] is defined, the Data Manager looks to the corresponding team 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 (This conversion file should be in the company folder.)

Account = [COMPANY]AccountConv.xls!newaccount (This conversion file should be in the company folder.)

Account = [AdminTeam]AccountConv.xls!newaccount (This conversion file should be located in the corresponding folder of AdminTeam.)

More Information

Validating Transformation Files

Importing and Exporting Data Having Multiple Key Figures

BAdI for Start Routine and End Routine

Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, except if such damages were caused by SAP intentionally or by its gross negligence.