Data Manager

 

Data Manager is a Business Planning and Consolidation (BPC) module that helps you move data in to BPC, as well as copy or move data within and across applications. In addition to allowing you to move and copy data, Data Manager supports mapping and complex transformations of data.

Integration

Data Manager is integrated with BPC for Excel. To start Data Manager, choose  BPC Desktop icon BPC for Excel Manage Data .

Features

These are the main features of Data Manager.

About Packages

Packages allow you to move data among your BPC databases and to effect business processes (such as legal consolidation and inter-company booking). By default, there are three types of packages that have been added to Data Manager:

Data Management Financial Processes System Administration

In addition to the above-named package types, BPC provides several example packages. These are not part of the default configuration, but you can add them to Data Manager using the Organize Packages feature. If a file has an associated transformation file, you can assign the file to the package as is, or modify it to suit your needs. See Data Transformations.

 Note

Data Manager packages are standard and shared among all applications within all application sets within a given BPC installation.

Organize Packages

Data Manager package organization involves adding, editing, moving, copying, and removing packages. You can start organizing your packages by selecting  Manage Data

Organize Package List from the action pane. See Adding or Modifying Packages, Moving or Copying Packages, and Removing a Package from the List.

Modify Packages

You might have to modify Data Manager packages to meet your specifications. The packages that come with BPC are designed to be dynamic so that you do not need to modify the packages in order for them to work with your applications and dimensions. But you might want to modify packages to behave differently or to perform additional functions.

The process chain allows you to set up pre-defined answers to package prompts so that you can automate running packages. See Adding or Modifying Packages.

Transform Data

Data Manager performs data transformations and mapping using transformation and conversion files. For example, when performing a data import, the source data may not be in the correct format. The correct dimension members are defined in the conversion file. When the Data Manager import package is run, it reads the transformation file (which has a pointer to a conversion file) and correctly maps the data. This is just one example of what you can do with data transformations. See Data Transformations.

Assign Packages to Teams

You can determine who runs packages in BPC by assigning packages to be available to specific BPC teams. You use the  eData Manage User Package Access function to do this.

Monitor Package Status

The Data Manager Status function lets you view the status of packages that are currently running and packages that have been completed. You use the  eData View Status.. function to do this.

Clear Saved Prompt Values

Data Manager saves values you type into various dialog boxes so that you do not have to repeat the information each time. You can clear these saved prompt values so that the system starts over with no values automatically filled in inside dialog boxes. To clear saved prompt values, you start BPC for Excel, and select   eData Clear Saved Prompt Values.

At the dialog box, select Yes to clear the saved prompt values, or No to leave the saved prompt values in place.

Run BPC Logic with Packages

You can set up BPC logic to work together with your Data Manager packages. Most packages automatically run default logic (default.lgf) for your application when importing

data. You can also include logic files in packages, or you can prompt users for logic files that they would like to use. See Adding or Modifying Packages.

Adding or Modifying Packages

Procedure

1. On the action pane, choose  Maintain data management Manage packages (organize list) .

2. Choose Add Package or select the package and choose Modify Package.3. Enter data as required. See Package Properties

To create a new group, enter the group's name in the Group field.

4. To add a new package, choose the browse icon. See Example Packages. You can upload package files to the MyFiles or Examples group. See **Data Transfer and Preview**.

o If you are adding a package, choose Add.o If you are editing a package, choose Save.

5. Select the package and choose Save Package List.

 Note

You must save the package before you can run it. Adding the package only adds it to the folder list. Saving it makes it available for use.

Package Properties

 The following table describes the fields you must complete when adding or editing packages. If any of these fields are incorrect or left blank, the package generates an error when the package is executed.

Field DescriptionPackageType FILE or Repository.File If the FILE package type is selected, a File text box is displayed so that

you can specify the file that contains the package. Alternatively, you can browse for files.

PackageName

The name of the package. If the file contains packages, you can view a list of available packages (when you select the file package type).

Group The folder group to which the package will be added. You can define a new group here.

Description A description of the package.Task Type You can control who has access to run the package. If you select Admin

Package, only users who are BPC Administrators will be able to run this package. The package can still be saved to a team that does not

comprise non-administrative users (such users will be able to see—but not run—the package). If you select User Package, users who have been assigned the Execute Data Management task security privilege will be able to execute the task.

Example Packages

 BPC also provide example packages. With the exception of the ImportUsingFTP package (see below for details), you can add the package and use it as is, or modify it as needed.

Features

The following tables describe the example packages that are included as default packages. These packages are available to all AppSets.

The following table describes the BPC sample packages that you can use as provided, or modify, as desired.

Package Name DescriptionAdminTask_MakeDim Creates dimension members and schedules the processing

of dimension members. For example, if a new member ID is added, this process updates the application data with the updated list.

See Scheduling Dimension Member Processing.AdminTask_Validate Validates the logic file.Clear_Journals Prompts you to select categories, time dimensions, and

currency to clear.ClearComments Clears comments from the comments table.Default_Formulas This package executes default formulas stored in your

default.xls file.Export_Journal Prompts you for the name of the export journal file.Full_Optimize Schedule a full optimization.

This package clears both real-time and short-term data storage and processes the dimensions. This option takes the system offline. It is best run during non–business hours. See Application Management.

ICBooking (Intercompany Balance Booking)

Intercompany balances are tracked in order to perform intercompany eliminations. This process chain prompts you for entities, categories, currencies, and time dimensions for the user, application set, application, selection, and processes intercompany booking according to the logic defined in icbooking.lgf.

ICData (Intercompany Reconciliation)

This process chain prompts you for entities, categories, currencies, and time dimensions and processes intercompany booking for the user, application set, application, and selection according to the logic defined in icdata.lgf.

IC_Elimination This process chain is used to perform inter-company eliminations. For more information on inter-company eliminations, see Inter-Company Booking Setup.

Import This process chain imports an ASCII file into the current application. You can specify the transformation file that defines conversion as well as data value signs.

ImportAndSendMail Import an ASCII file and send the result to specific user.Import_Descript This process chain imports dimension member description

records. Note that when these descriptions are entered in another language in the EVDESCRIPTION table (and the correct SAP NetWeaver language code is used) , the descriptions will appear will appear in the user's specified language. This setting can be modified in BPC for Web.

See Import Master Data Descriptions from Data File.Import_Using_FTP This process chain allows you to import an ASCII file to

download from an FTP site into the current application. You can modify the server name within the package. After you modify the server name, you can run this package at any time.

Legal_Consolidation This process chain prompts you for categories, currencies, and time dimensions, and calculates consolidation for the user, application set, application, and selection, according to the logic defined in consolidation.lgf.

Light_Optimize You can use this process chain to schedule a light optimization. A light optimization clears real-time data storage and moves it to short-term data storage. This option does not take the system offline, so you can schedule it to run during normal business activity. See Application Management.

Load_Infoprovider This process chain allows you to load data from one BI Cube (EDW or BPC) into a BPC Application. You can associate a tranformation file to do the mapping.

Opening_Balances This process chain prompts you for entities, categories, currencies, and time dimensions, and calculates opening balances for the user, application set, application, and selection, according to the logic defined in consolidation.lgf.

OwnershipCalc This process chain prompts you for categories, currencies, and time dimensions, and calculates ownership (including ownership percentage, accounting method, holding method, and parent information) for the user, application set,

application, and selection.Restore_Journals Restores Journals tables from an output file.RunCalcAccount This process chain prompts you for entities, categories,

currencies, and time dimensions, and runs calculate account for the user, application set, application, and selection, according to the logic defined in calcaccount.lgf.

Validate_Transform This process chain validates the transformation file.Validations This process chain prompts you for dimensions and

categories on which you can run validation logic (as defined in validation.lgf, by default).

Activities

To add sample packages to Data Manager, choose  eData Organize Package List Add Package icon. Complete the Add Package dialog box, click Add, then save it.

Use the Member Lookup

The Member Lookup provides a hierarchical view of the members in a dimension. You can choose one or more members, depending on the context. You can use the member selector anywhere you see a Choose Members button in the Data Manager interface, such as when you run a package. You can also use the Member Lookup when you select a dimension and click the Show Member List button on the Data Manager toolbar.

Data Transfer and Preview

Data Manager allows you to transfer data files to and from the server. You can also use the data preview feature to preview database, Excel, or text data files. This allows you to see the format of your data when deciding on how to set up a transformation. See Data Transfer and Preview.

Data Transformations

 

You define data transformations so that you can map external data to internal BPC data structures.

Features

Required Files

To perform data transformations, an administrator must set up at least two BPC for Excel workbook files:

Transformation file — Allows administrators to set up the rules for reading data from an external source and putting it in the proper form for your BPC database. Transformation files are Microsoft Excel files that contain one worksheet, named Instructions. The Instructions worksheet has three sections:

o *Optionso *Mappingo *Conversion

Conversion file — Allows administrators 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 Conversions.

After you set up these files, you run packages to use these files to define your data transformations. See Running and Scheduling Packages.

 Note

All dimensions of an application must be mapped in the transformation. So 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. Here are the available options and descriptions for each:

AMOUNTDECIMALPOINT=text character

Default: . (period)

Allows you to specify a non-period 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.

CONVERTAMOUNTWDIM=dimension name

Default: Account

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, seeData Conversions . If there is no formula in the Formula column of the Amount conversion sheet, this parameter has no effect at all. For example, the Formula column has the following formula: Value*1.10. All accounts will be increased by 10% during the conversion.

CREDITPOSITIVE= YES | NO

Default: YES

If No, all amounts referring to an ACCOUNT type (LEQ, INC) will have their signs reversed.

DELIMITER text_character| SPACE | TAB

Default: , (comma)

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

FORMAT = DELIMITED | FIXED | VARIANT

Required Parameter

The format of the data in the input or output file. You must enter a format type. Delimited means that there is a special character between each column of data, defined by the Delimiter option (below). Fixed means that the data is in a fixed field format.CategoryByTime is out of scope for BPC 7.0

Default: There is no default. This option must be defined.

 Note

CategoryByTime is out of scope for BPC 7.0

HEADER= YES | NO

Default: YES

If 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

Default: empty_string

If validating records, specifies the number of rejected records at which to stop processing. A value of -1 implies that 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.

ROUNDAMOUNT=integer

Required Parameter

Specifies the amount of decimal places to round values during the transformation.

When specifying integer, the integer can be zero or any positive integer.

Default: no rounding occurs

SKIP= integer

Default: 0 (zero)

Number of lines to skip at the top of a data file. If your data file has a header, set this value so that those lines are skipped during transformation.

SKIPIF= text_string | text_string2

Required parameter

Default: empty_string

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 will be skipped. Setting this option to an empty string means that no lines are skipped in the body of the data file. Header lines can still be skipped using the SKIP command, above.

Default: <empty_string>

VALIDATERECORDS= No | Yes

Default: No

If YES, validate the mapping and that members exist and that the record is proper for the BPC application before the import. In this case, for a member ID is not mapped, that specific record would be rejected during validation and ignored. If the import file has an extra field that is not mapped, all records will be rejected during validation.

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

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

The SELECTION option set the select rules when exacting transaction data from infoprovider.<Dimension1_techname> is the technique name of the dimension, <Dimension1_value> is the value of

Condition statement set to this dimension. For example 0SX_ACCS, US;0SX_CSLC, 1 represents the selection statement 0SX_ACCS=US and 0SX_CSLC= 1

 Note

This is used for loading transactional data as is (new in BPC 7.0).

Default: <empty>

FORMULA = LIB.LGF|LIB1.LGF

This option is only used for importing master data, it is illegal when running other packages.

The FORMULA option sets the K2 functions referenced by the master data member’s formula attribute

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 BPC 7.0).

 Example

DAVY_KP1_DEV7A2_Q_Mdxlib_1.LGF| DAVY_KP1_DEV7A2_Q_Mdxlib_2.LGF

Default: <empty>

CONVERT_INTERNAL = YES | NO

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

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

Default: NO

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

Mapping Section

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

 Note

By default, Data Manager associates fields to header names when you have a header row in your data file. This makes it easier to define mappings in this section of the transformation file. For example, if you have a header row and it defines a column named AccountVal, you can map the Account dimension to the Account field from the data file this way: Account = AccountVal

 Example

If you have a header row and it defines a column named "AccountVal," you can map the Account dimension to the Account field from the data file this way:

Account = AccountVal

*COL(A)

A = column index in the data file

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

Account =*COL(2)

*COL(A, B:C)

A = column index in the data file

B:C = start/end position within the column

Defines a dimension for a subset of a field in the data file.

 Example

Account=*COL(3,1:4)

*FCOL(A:B)

A:B = start/end position within the data row

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

 Example

Example: Account=*FCOL(4:7)

*MVAL(A:B)

A:B = start/end column of time period

Use this command if your data file has multiple time period columns.

 Example

Consider a case in which you have the following columns:

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

Since columns 7 through 18 contain data based on time periods, you could use the following command: Time = *MVAL(7:18). 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 will be named P1, P2, P3,...,PN by default.

*NEWCOL(A)

A = dimension member

Creates a new field with the given value.

 Example

Account=*NEWCOL(Revenue)

*Str(string)

Adds a text string to the members of a column. 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

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

*PAD

Useds for padding imported fields.

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

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

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 Condition 2 can contain multiple items that are added together (using the plus (+) sign), as in this example:

 Example

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

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.

 Example

Given this data:

PRODUCT,ID,ENTITY

14,85,52

AB,CD,EF GH,IJ,KL

And this mapping formula:

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

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

The result is:

PRODUCT,ID,ENTITY

MHarn,85,52

AB,XX,EF

YY,IJ,KL

Given this data:

Entity, SEntity

U1000000, US01

Z2000A01, CA03

K3430000, JP04

And this mapping formula:

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

The result is:

Entity, SEntity

US01, US01

A01, CA03

ERR, JP04

Conversion Section

The *CONVERSION section of the transformation file defines which conversion sheet(s) to use with which dimensions, and has the following syntax:

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.

SheetNameThe name of the worksheet to use within the conversion file. If not specified, Data Manager assumes the sheet name is Conversion

Variable Description

[COMPANY]If [COMPANY] is defined then Data Manager tries to obtain the conversion file from the main company Data Manager folder. Otherwise 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 a 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 - BPC 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

Where 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

Sample Transformation File

 When you are copying or importing data between applications and need to map the dimensions which are different, you can create a transformation file to map the dimensions.

For example, the dimensions in the source application are: Category, Account, Entity, Time, Rptcurrency, Intco, andDatasrc and the dimensions in the target application are: Cat, Acc, Ent, Tim, Currency, Int, and Dat. You can create a transformation file like the following:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER =

SKIP = 0

SKIPF =

CREDITNEGATIVE=NO

CONVERTAMOUNTWDIM=

MAXREJECTCOUNT=

VALIDATERECORDS=YES

*MAPPING

cat=category

Acc=Account

Ent = Entity

Tim=time

Currency=Rptcurrency

int=intco

dat=Datasrc

*CONVERSION

Data Conversions

 

Conversion files define the mapping (by dimensions) from external member names to internal member names. They also provide the ability to perform arithmetic and rounding on data as it is converted. You create one conversion file per dimension in a transformation.

Each conversion file can contain one or more sheets for different types of data transformations. As a best practice, conversion files should be named the same as the dimension for which they are being used.

 Note

Data Manager supports VB script in conversion files. While VBA can be used successfully in some instances, we cannot guarantee it will work with BPC for Excel. To use Javascript, ensure that the following parameters are set:

CONVERT_INTERNALo If the transformation option Convert_internal = NO, the javascript can be

used in Internal column.o If transformation option Convert_internal = YES, the javascript can be used

in External column. Precede the Java Script code with string ‘js:’, which is mandatory. You can use Java Script code in IF statements, too. Examples:

o js: %external%.substring(0,2) Return a Long containing the number of characters in a string

o js: %external%.length Return the length of the string contained in external.

o js: %external%.replace("apples", "oranges") Return a string in which a specified substring has been replaced with

another substring a specified number of times. Java Script can be used in the FORMULA column

o Examples: js:Math.round(VALUE*Math.pow(10,2))/Math.pow(10,2);

Of the Source value is 200.356, the target value will be 200.36

Features

*skip

If you want to ignore certain external data, you can place the keyword *skip in the Internal column, as in the following example.

External InternalACCPAY *skip

Wildcards

You can use the asterisk (*) and question mark (?) wildcards in the External or Internal columns. An asterisk (*) stands for ANY character, while a question mark (?) stands for any SINGLE character. For example, if you want to reference all members, use the asterisk (*). This would be useful to apply a formula to all members. The following example would increase all of the members by ten percent:

External Internal Formula* * Value*1.10

Activities

To create a conversion file, select  eData New conversion file. Enter data as required.

To validate a conversion file, open the conversion file then select  eData Validate & Process Conversion File.

To copy a conversion file, select  Maintain Conversions Manage existing conversion definition . Select a file, then choose Open. Select Copy Conversion, select a conversion file, then choose Save.