You define data transformations so you can map external data to internal Planning and Consolidation data structures.
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:
|
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). |
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
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).
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:
|
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: |
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 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 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 This is used for loading master data as is (new in Planning and Consolidation 7.0). End of the note. Example
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:
Example 0SX_ACCS, US;0SX_CSLC, 1 is the result of the following selection statement: 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 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 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
|
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:
When this option is enabled, the following rules apply:
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
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
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
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
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
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:
|
*NEWCOL(A) |
A = dimension member |
This function creates a new field with the given value. Example
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
End of the example. |
*PAD(colname,length,direction,letter) |
|
This function is used for padding imported fields. 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:
|
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
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 The *STR() function must be used when evaluating numeric constants in an *IF statement. End of the note. 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:
Mapping Function 1:
then *str(XX) ; *str(YY)) Result Set 1:
End of the example. Data Set 2:
Mapping Function 2:
Result Set 2:
|
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
Items in brackets are optional.
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. |
To create a new transformation file, select
Enter data as required.To edit transformation files, select
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
. 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.
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.)
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.