Data Transformations 
You define data transformations so that you can map external data to internal Planning and Consolidation data structures.
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.
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. |
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. |
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.
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: |
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
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.
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:
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 your system database.
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.
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.
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.
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.
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).
End of the example. |
*PAD |
Not applicable |
This function is used for padding imported fields.
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. |
|
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.
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
The *STR() function must be used when evaluating numeric constants in an *IF statement. End of the note.
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 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. |
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
Account = [COMPANY]AccountConv.xls!newaccount