Page 1
Financial Information Management Online Help
■ SAP BusinessObjects Financial Information Management 10
2010-12-06
Page 2
Copyright
© 2010 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Business ByDesign, and other SAP products and services mentioned herein as well as their respective
logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business
Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web
Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well
as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the
United States and in other countries. Business Objects is an SAP company.All other product and
service names mentioned are the trademarks of their respective companies. Data contained in this
document serves informational purposes only. National product specifications may vary.These materials
are subject to change without notice. These materials are provided by SAP AG and its affiliated
companies ("SAP Group") for informational purposes only, without representation or warranty of any
kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The
only warranties for SAP Group products and services are those that are set forth in the express
warranty statements accompanying such products and services, if any. Nothing herein should be
construed as constituting an additional warranty.
2010-12-06
Page 3
Contents
About SAP BusinessObjects Financial Information Management..........................................7Chapter 1
1.1
1.1.1
1.1.2
3.1
3.1.1
3.1.2
3.1.3
3.1.4
3.1.5
4.1
4.1.1
What You Do in the Financial Information Management Application..........................................7
Prerequisites............................................................................................................................8
Workflow.................................................................................................................................8
User Roles.............................................................................................................................11Chapter 2
About Datastores..................................................................................................................13Chapter 3
Datastore Properties..............................................................................................................13
Creating a Financial Consolidation Datastore.........................................................................14
Creating an Intercompany Datastore......................................................................................15
Creating an SAP NetWeaver BW Datastore..........................................................................15
Creating an SAP ECC Datastore............................................................................................16
Creating a Database Datastore..............................................................................................17
About Jobs............................................................................................................................21Chapter 4
Creating Jobs: Overview........................................................................................................21
Creating a New Job: Initial Steps...........................................................................................22
About Source Data................................................................................................................25Chapter 5
5.1
5.2
5.3
5.4
5.5
6.1
6.2
6.3
SAP ECC as a Source of Data...............................................................................................25
SAP NetWeaver BW as a Source of Data..............................................................................25
The Financial Consolidation Application as a Source of Data..................................................26
A Database as a Source of Data............................................................................................26
A Flat File as a Source of Data...............................................................................................26
About Targets........................................................................................................................29Chapter 6
The Intercompany Application as a Target..............................................................................29
The Financial Consolidation Application as a Target................................................................29
SAP NetWeaver BW as a Target...........................................................................................29
2010-12-063
Page 4
Contents
About Mapping Tables..........................................................................................................31Chapter 7
7.1
7.1.1
7.1.2
7.1.3
7.1.4
7.2
7.3
8.1
8.2
8.3
10.1
10.2
10.3
10.4
10.5
10.6
10.6.1
Creating and Defining Mapping Tables ..................................................................................31
Patterns Used in Mapping Table Source Columns..................................................................32
Target Column Values............................................................................................................34
Amounts................................................................................................................................36
Changing Column Headers in Flat Files..................................................................................37
Populating Mapping Tables from Microsoft Excel...................................................................37
Mapping Tables and Drill to Source........................................................................................37
Example: Defining and Populating a Mapping Table.............................................................39Chapter 8
Defining a Mapping Table - Source Data................................................................................39
Linking Source to Target Definitions.......................................................................................40
Results...................................................................................................................................41
Example: Mapping Rule Behavior .........................................................................................43Chapter 9
After You Have Created a Job...............................................................................................47Chapter 10
Executing a Job......................................................................................................................47
Job Run History.....................................................................................................................49
Choosing to Launch a Job Via an External Scheduler.............................................................49
Saving a Job..........................................................................................................................50
Duplicating an Existing Job.....................................................................................................50
Deleting a Job........................................................................................................................51
Viewing Deleted Data............................................................................................................51
11.1
11.2
12.1
13.1
14.1
14.2
About the Drill to Origin Feature...........................................................................................53Chapter 11
Displaying the Origin of Data in the Financial Consolidation Application..................................53
Displaying the Origin of Data in the Intercompany Application................................................54
Drilling to SAP ECC Source Data.........................................................................................57Chapter 12
Example: Displaying Source Data in SAP ECC.......................................................................57
Sharing Transaction Tables Among Several Jobs.................................................................59Chapter 13
Renaming a Transaction Table................................................................................................59
Moving Financial Information Management Job Definitions.................................................61Chapter 14
Exporting a Financial Information Management Job................................................................61
Importing a Financial Information Management Job................................................................62
2010-12-064
Page 5
Contents
Use Cases.............................................................................................................................63Chapter 15
15.1
15.2
16.1
16.1.1
16.1.2
16.1.3
16.1.4
16.2
16.3
16.4
16.5
16.5.1
16.5.2
16.6
Index 77
Use Case 1............................................................................................................................63
Use Case 2............................................................................................................................64
Custom Jobs.........................................................................................................................67Chapter 16
About Data Services Job Customization................................................................................67
Dataflows...............................................................................................................................67
The Export Dataflow...............................................................................................................68
The Transform Dataflow.........................................................................................................68
The Load Dataflow.................................................................................................................69
Data Services Objects...........................................................................................................69
Global Variables.....................................................................................................................70
Repository Tables..................................................................................................................71
Customizing a Data Services Job...........................................................................................72
Publishing a Data Services Job to Web Services...................................................................72
Connecting to a Data Services Job........................................................................................73
Importing a Custom Job.........................................................................................................73
More Information...................................................................................................................75Appendix A
2010-12-065
Page 6
Contents
2010-12-066
Page 7
About SAP BusinessObjects Financial Information Management
About SAP BusinessObjects Financial Information
Management
SAP BusinessObjects Financial Information Management is a web-based solution that allows a business
user to do the following:
• Maintain mappings between source and target
• Launch and monitor loading processes
• Trace data to determine its origin
The financial information management solution is a tool that combines an easy-to-use interface with
the power of SAP BusinessObjects Data Services. It provides connectivity to both SAP and non-SAP
applications.
1.1 What You Do in the Financial Information Management Application
In the financial information management application you move data from a source to a target application.
Some examples include the following:
2010-12-067
Page 8
About SAP BusinessObjects Financial Information Management
To this Target ApplicationFrom this Source
Flat file
Database
SAP ECC
SAP NetWeaver BW
SAP BusinessObjects Financial
Consolidation
1.1.1 Prerequisites
• SAP BusinessObjects Financial Consolidation
• SAP BusinessObjects Intercompany
• SAP BusinessObjects Financial Consolidation
• SAP BusinessObjects Intercompany
• SAP BusinessObjects Financial Consolidation
• SAP BusinessObjects Intercompany
• SAP BusinessObjects Financial Consolidation
• SAP BusinessObjects Intercompany
• SAP NetWeaver BW
• SAP BusinessObjects Financial Consolidation
• SAP BusinessObjects Intercompany
For information on prerequisites for the financial information management client, refer to the
BusinessObjects Financial Information Management 10 Master Guide .
1.1.2 Workflow
The workflow in creating and executing a job is as follows:
• The administrator creates datastores to the source and target data.
• The business user does the following:
• Creates a new job.
• Defines and populates mapping tables.
• Executes the job.
Note:
The executor may also execute a job.
• The auditor views the execution logs.
SAP
2010-12-068
Page 9
About SAP BusinessObjects Financial Information Management
Note:
The auditor may also view deleted jobs and mapping table content.
Related Topics
• About Datastores
• About Jobs
• Creating Jobs: Overview
2010-12-069
Page 10
About SAP BusinessObjects Financial Information Management
2010-12-0610
Page 11
User Roles
User Roles
The user roles in the financial information management solution are the following:
• Administrator, who creates and maintains database and application datastores. Administrators have
access to all jobs defined in the platform.
The administrator may be, but is not required to be, the person who installs and manages the financial
information management system.
• Business user
• Auditor
• Executor
The principal user roles are summarized as follows:
Action
View list of jobs
View job definition
Create jobs
Export jobs
Delete jobs
View mapping tables
Modify mapping tables
Import jobs
Execute jobs
AuditorBusiness UserExecutorAdministrator
XXXX
XXXX
XX
XX
X (own jobs)X
XXXX
XX
XX
XXX
Schedule jobs
XXX
2010-12-0611
Page 12
User Roles
Action
View job results and audit trail
View extended data services
X (own jobs)X
AuditorBusiness UserExecutorAdministrator
XX (own jobs)XX
logs
Drill-to-origin
Create datastores to EPM
X
XXXX
applications
Delete datastores
View deleted jobs
X
X
Users and user groups are created in the CMS (Central Management Server) by the administrator who
is responsible for installing and configuring the financial information management solution.
Note:
Auditors and administrators see all jobs. Business users and executors see jobs according to permissions
granted to them at the job level. Job results, audit trails, and extended data service logs can be viewed
only by the business user who has executed the job.A business user can delete only the jobs of which
he is the owner.
2010-12-0612
Page 13
About Datastores
About Datastores
Datastores provide short-term storage of the information that is needed when loading data from source
to target applications. Before a business user can create or execute a job, an administrator must create
datastores. Datastores are stored in the financial information management repository.
You can create the following types of datastores:
• database
• financial consolidation
• intercompany
• SAP ECC
• SAP NetWeaver BW
The properties of datastores vary depending on the type of datastore you create. Datastores are required
for both source and target data.
If the source data is from a flat file, there is no need to create a datastore.
Note:
Only administrators can create datastores.
You create datastores on the "Home" page of the financial information management application.
Related Topics
• Creating a Database Datastore
• Creating a Financial Consolidation Datastore
• Creating an Intercompany Datastore
• Creating an SAP ECC Datastore
• Creating an SAP NetWeaver BW Datastore
3.1 Datastore Properties
The following table shows an overview of datastore properties with respect to the type of datastore
created:
2010-12-0613
Page 14
About Datastores
PropertiesType of Datastore
Financial Consolidation
Intercompany
NetWeaver BW
SQL Database
Oracle Database
• Name
• Web service URL
• Name
• Web service URL
• Name
• Application Server
• Routing string
• User name
• Password
• Name
• Type of database
• Server name
• Database name
• Login
• Password
• Name
• Type of database
• Net service name
• Login
• Password
SAP ECC
• Name
• Application server
• User
• Password
• Client number
• System number
• Data transfer method
• Other properties depending on the data transfer method
3.1.1 Creating a Financial Consolidation Datastore
You must be an administrator to create a datastore.
2010-12-0614
Page 15
About Datastores
To create a datastore:
1.
2.
3.
4.
5.
6.
7.
On the "Home" page, under "List of Datastores", click New.
Under "Datastore type" select "Financial Consolidation."
In the "Datastore name" box, type a unique name that you assign to the datastore.
Click Next.
In the "Web service URL box" box, type the location of the web service deployed in the financial in
formation management application ending with a slash, for example, http://ES-ANA-FIN32-
1:1080/FIM_REF_MAGISK_750_SQL2k5/.
Note:
The web service that you reference in creating the financial consolidation datastore must point to
the same central management server (CMS) as that on which the financial information management
application has been installed.
Test the connection.
Click Finish.
3.1.2 Creating an Intercompany Datastore
You must be an administrator to create a datastore.
To create a datastore:
1.
On the "Home" page, under "List of Datastores", click New.
2.
Under "Datastore type" select "Intercompany".
3.
In the "Datastore name" box, type a unique name that you assign to the datastore.
4.
Click Next.
5.
In the "Web Service URL" box, type the web service deployed in the intercompany application.
You must end the web service URL with a slash, for example: http://<hostname>:<port>/In
tercompany/WebServices/
6.
Test the connection.
7.
Click Finish.
3.1.3 Creating an SAP NetWeaver BW Datastore
You must be an administrator and be declared in the "Data Services Administrator Users" group in the
CMC to create a datastore.
To create a datastore proceed as follows:
1.
On the "Home" page, under "List of Datastores", click New.
2010-12-0615
Page 16
About Datastores
2.
Under "Datastore type" select "SAP NetWeaver BW".
3.
Click Next.
4.
In the "Application server" box, select the application server to which you want to connect. The list
of available application servers corresponds to the RFC connections that were created in the data
services application during configuration.
5.
In the "Routing string" box, enter a value as follows:
• the default value, /H/ if no SAP router is required
• /H/hostname/s/port if a simple router string is required.
The port is 3300 plus the SAP system number.
6.
Enter the User name and "Password" required to access the SAP NetWeaver BW system.
7.
Test the connection.
8.
Click Finish.
3.1.4 Creating an SAP ECC Datastore
You must have previously activated the SAP ERP datasources. Refer to the
Administrator's Guide
for detailed information.
SAP BusinessObjects
To create a datastore:
1.
On the "Home" page, under "List of Datastores", click New.
2.
Under "Datastore type" select "SAP ECC".
3.
Click Next.
4.
In the "Application server" box type the name of the application server to which you want to connect.
5.
Enter the following:
• User
• Password
• Client number
• System number
6.
Select the "Data transfer method" as follows:
2010-12-0616
Page 17
About Datastores
If the "Data transfer
method" is:
Enter the following information:
"Shared directory "
"FTP"
"Direct download"
"Custom transfer"
• the working directory on the SAP server, for example,
D:\DataServices
• the application path to the shared directory, for example,
\\bsw6529.wdf.sap.corp\DataServices
• the working directory on the SAP server, for example,
D:\DataServices
• the local directory
• FTP path relative to the working folder
• FTP host name
• FTP login
• FTP password
• the working directory on the SAP server, for example,
D:\DataServices
• the application path to the shared directory, for example,
\\bsw6529.wdf.sap.corp\DataServices
• the working directory on the SAP server, for example,
D:\DataServices
• the local directory
• the transfer program to be invoked during job execution
• additional arguments used by the transfer program such as security,
compression, or data services system variables. The arguments
are optional.
• the username and password
7.
If you want to enable drill to source, select this option, then enter the drill to source root URL, which
connects to the SAP web gui.
Note: the drill to source root URL must match the following syntax: http://<servername>:<port
number>/sap/bc/gui/sap/its/webgui?
8.
Click Test to test the datastore connection.
9.
Click Finish.
3.1.5 Creating a Database Datastore
You must be an administrator to create a datastore.
2010-12-0617
Page 18
About Datastores
To create a datastore:
1.
2.
3.
4.
5.
6.
On the "Home" page, under "List of Datastores", click New
Under "Datastore type" select "Database".
In the "Datastore name" box, type a unique name that you assign to the datastore
Click Next
In the "Type of Database" box, select the type from the list.
Do one of the following:
Enter the following information:If the database is:
SQL server
• Server name: the server hosting the MS SQL Server. The name can be
followed by listener port number separated by a colon for example, SQL
SRV:1433
• Database name
• Login
• Password
Oracle
• Net service name, which was created in the Oracle Net Configuration Assis-
tant.
• Login
• Password
7.
Test the connection.
8.
Click Finish.
3.1.5.1 Using the Financial Information Management Application with an Oracle
Database
To connect to an Oracle database with the financial information management application you need to
use Oracle aliases that have the same name as the Oracle SID instance addressed in the financial
information management/data services server TNSNAMES.ORA file.
Example:
# Generated by Oracle configuration tools.
APAC_SERVER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SRV-ORA10G)(PORT = 1521))
2010-12-0618
Page 19
About Datastores
)
(CONNECT_DATA =
(SERVICE_NAME = CART )
)
)
You also need to add the path to the location where the tnsnames.ora file is hosted.
You add this information on the Java page of the "Apache Tomcat Properties" dialog box under "Java
options".
Example:
-Dora cle.net.tns_admin=C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
2010-12-0619
Page 20
About Datastores
2010-12-0620
Page 21
About Jobs
About Jobs
A job is a series of steps that, when completed, allows you to load data from a source to a target.
A financial information management job is comprised of the following components:
• Properties: the general properties describing the job and the users
• Source Properties: that describe the source data
• Target Properties: that specify the application in which you want to load the transformed data
• Mapping Tables: tables that contain the individual transformations that need to take place
• Mapping Table History: a table that displays modifications that were made to the transformations
• Job Run History: a table that displays specific information on status of the job as follows:
• Job run ID
• Status
• Start time
• Duration
• User
• Imported lines
• Mapping errors
• Rejected lines
When you execute a financial information management job, you automatically create an underlying
data services job, which is unseen.
Note:
A data services job can be modified by a person who has access to the data services application and
who has the necessary advanced skill set required for customizing data services jobs.
Related Topics
• Customizing a Data Services Job
4.1 Creating Jobs: Overview
You create a job in a series of pages in the financial information management application. The steps
are as follows:
1.
Name the job and define the core properties.
2010-12-0621
Page 22
About Jobs
2.
Select the users.
3.
Specify the location of the source data and its properties.
4.
Specify the target, that is, the location to which you want to load the data, and its properties.
5.
Define and populate a mapping table with actual transformation values.
Each step is explained in greater detail in this document.
Related Topics
• Creating a New Job: Initial Steps
4.1.1 Creating a New Job: Initial Steps
To create a job, do the following from the "Home" page of the financial information management
application:
1.
Under "List of Jobs" click New.
The Create New Job dialog box opens. In this dialog box you define
• the properties of job
• a set of users who have rights to the job.
Certain job properties must be configured in order for the data services job to interact fully with the
financial information management application.
2.
Under "Properties" do the following:
• In the "Job Name" box type a name for your job. The name must be unique to your job. Once
you have named a job, you cannot re-name it.
Note:
The job name may contain only eight characters that are limited to:
• letters
• numbers
• underscores
• In the "Description" box, type a description to explain the purpose or behavior of the job.
• By default, the "Transaction Table" is given the name of your job preceded by "Transaction_ ".
If you want to use a different name for the transaction table, select Transaction Table and enter
a name for it in the box.
The transaction table contains the mappings to each source row along with the set of target
coordinates to which the row was mapped. The information contained in the transaction table is
displayed when the drill-to-origin function is used. The transaction table is created automatically
when you create a job.
By default, the "Validation Error Table" is given the name "Mapping_Errors_<your_job_name>".
The validation error table provides additional feedback for users when invalid data is caught by
the data services job at runtime.
2010-12-0622
Page 23
About Jobs
• If you want to connect with an existing data services job, select "Connect to an existing SAP
BusinessObjects Data Services Job". Connecting to an existing data services job entails
customizing the job.
3.
Click Next.
4.
UnderJob Users select the users to whom you want to allocate access rights to this job, and, using
the arrow, move the user names to the Job Users box.
5.
Click Next.
You proceed to the "Source" page.
The method for defining source and target properties varies depending on the type of datasource you
are using. Refer to the following related topics for detailed information:
Related Topics
• Customizing a Data Services Job
• Sharing Transaction Tables Among Several Jobs
• SAP NetWeaver BW as a Source of Data
• The Financial Consolidation Application as a Source of Data
• A Flat File as a Source of Data
• A Database as a Source of Data
• Creating and Defining Mapping Tables
2010-12-0623
Page 24
About Jobs
2010-12-0624
Page 25
About Source Data
About Source Data
Source data is the data that you want to transform and load into a target application. Source data
typically resides in source systems and general ledgers within an organization.
5.1 SAP ECC as a Source of Data
After you have completed the initial steps in creating a job, from the "Source" page, proceed as follows:
1.
On the "Source" page select the datastore, SAP ECC.
2.
Select the "SAP Datasource", which is the mechanism used to extract data from SAP ECC. Only
activated ECC datasources are displayed in the list.
Refer to the
information on activating SAP ECC datastores.
SAP BusinessObjects Financial Information Management Administrator's Guide
for
3.
Click Next.
4.
Click Next.
You proceed to the "Target" page.
5.2 SAP NetWeaver BW as a Source of Data
You can use the financial information management application to export data from SAP NetWeaver
BW to SAP BusinessObjects Financial Consolidation or SAP BusinessObjects Intercompany using the
SAP-certified Open Hub interface which lets you extract and distribute data from an SAP NetWeaver
BW system to a data mart or to another application.
Before you can use SAP NetWeaver BW with the financial information management application, your
administrator needs to create a connection to the SAP NetWeaver back-end. Detailed information on
creating the connection is available in the
Administrator's Guide
After you have completed the initial steps in creating a job proceed as follows:
1.
On the "Source" page select the datastore, SAP NetWeaver BW.
2.
Select an "Open Hub destination". The open hub interface lets you extract and distribute data from
an SAP NetWeaver BW system to a data mart or to another application.
.
SAP BusinessObjects Financial Information Management
2010-12-0625
Page 26
About Source Data
3.
Select the process chain that you want to use to trigger the Open Hub extraction.
4.
Click Next.
You proceed to the "Target" page.
5.3 The Financial Consolidation Application as a Source of Data
After you have completed the initial steps you select source and target data. In the "Source" page
proceed as follows:
1.
Select the financial consolidation datastore from which you want to take data.
2.
In the "Reporting ID" box, select the appropriate Reporting ID.
3.
In the "Consolidation" box, select the consolidation.
Both the reporting ID and the consolidation are made available via the financial consolidation web
service.
4.
Click Next.
You proceed to the "Target" page.
5.4 A Database as a Source of Data
After you have completed the initial steps in creating a job, from the "Source" page, proceed as follows:
1.
On the Source page, select datastore that corresponds to the source data.
2.
In the "Table" box, select the table that you want to use as the source of data.
3.
Click Next.
You proceed to the "Target" page.
5.5 A Flat File as a Source of Data
After you have completed the initial steps in creating a job, proceed as follows:
1.
On the "Source" page, select Flat File.
2.
Click Upload to browse to the file from which you want to load data.
Only .csv and .txt files are supported.
Note:
If a with the same name already exists, a message asking if you want to overwrite the file on the
server is displayed.
2010-12-0626
Page 27
About Source Data
3.
Click Preview if you want to preview the file. You can view the first lines of data.
The number of lines of data that you can preview is determined by your administrator.
4.
If the first line of data in your flat file contains column headers, select First line contains header.
If the first line does not contain a header, column numbers are designated by default as dimension
names.
Note:
Column headers cannot contain spaces. You need to insert an underscore, for example, Transac
tion Currency to Transaction_Currency.
5.
Select the column separator:
• Semicolon
• Comma
• Space
• Tabulation
Restriction:
Fixed-width flat files are not supported.
6.
Click Next.
You proceed to the "Target" page.
2010-12-0627
Page 28
About Source Data
2010-12-0628
Page 29
About Targets
About Targets
The target is the application to which you want to map data. You select the target application on the
"Targets" page.
6.1 The Intercompany Application as a Target
You have selected the source datastore and you are on the "Targets" page.
1.
In the "Datastore" box select the target datastore.
2.
In the "Type of Data Imported" box select the type of data you want to import. The data type can be
either:
• Balances
• Invoices
3.
Click Next.
You proceed to the "Mapping Tables" page where you define the mappings.
6.2 The Financial Consolidation Application as a Target
You have selected the source datastore and you are on the "Targets" page.
1.
In the "Datastore" box select the target datastore.
2.
Click Next.
You proceed to the "Mapping Tables" page where you define the mappings.
6.3 SAP NetWeaver BW as a Target
You have selected the source datastore and you are on the "Targets" page.
1.
In the "Datastore" box select the target datastore.
2010-12-0629
Page 30
About Targets
2.
3.
In the "3.X InfoSources / 7.X DataSources" box, select the infosource or datasource that corresponds
to your target.
Click Next.
You proceed to the "Mapping Tables" page where you define the mappings.
2010-12-0630
Page 31
About Mapping Tables
About Mapping Tables
A mapping table contains the rules to be applied to the source dimensions in order to produce the target
dimensions.
When you create a job, you must create at least one mapping table in which you specify the individual
transformations that need to occur. A mapping table usually consists of source and target dimensions.
When defining a mapping table, you can define up to five source columns and five target columns. At
mapping time, you can select from a list of values that are retrieved from the associated source column
table.
Each target column must be associated with a dimension in the target application, for example, Flow
or Account.
7.1 Creating and Defining Mapping Tables
On the "Mapping Tables "page you create and define the mapping tables that are used to transform
and load data. When you create a mapping table, first you select the source and target dimensions that
you want to map, and then you select the values for each.
To create a mapping table:
1.
On the right side of the page, in the "Dimension Selector", under "Source", select the source
dimensions.
The source dimensions appear in the table.
2.
Under "Target", select the target dimensions.
The target dimensions appear in the table.
3.
Click inside each source dimension cell and select the value that you want to use. You can also
browse to locate the value in the "Item Selector".
4.
Click inside each target dimension cell and select the value that that you want to use. You can also
browse to locate the value in the "Item Selector".
5.
Select the matching rule. When you run the job, the financial information management application
processes the rule by using either the highest priority matching rule or all matching rules.
The rules are as follows:
• Highest matching rule applies.
2010-12-0631
Page 32
About Mapping Tables
Only the first matching rule is applied according to its priority. By default, the priority is set to 10,
20, 30 and so on. You set the priority in the "Priority" column.
Note:
The "Priority" column, as well as all columns in the mapping table, can be displayed or hidden
by clicking the small black down arrow next to the "Source" label.
• All rules apply. Select this option when you want to:
• Include aggregate amounts
• Load more than one amount for a line
6.
Click Finish.
You return to the "Home "page. The job you created appears in the "List of Jobs".
Example:
You select the financial consolidation application as the target application.
From a source database you select the table column GL_ACCOUNT and the target dimensions AC and
FL.
Caution:
• You must ensure that all mandatory dimensions for the target application are correctly mapped,
otherwise the job fails.
• We recommend that you select the source dimension from the list boxes. If you manually modify
the selection, your job may no longer work. Manually modifying the source dimensions is advised
only when building complex jobs where the underlying data services job accesses a modified
source for which the user interface does not provide metadata browsing.
Related Topics
• Patterns Used in Mapping Table Source Columns
• Target Column Values
• Mandatory Dimensions
7.1.1 Patterns Used in Mapping Table Source Columns
2010-12-0632
Page 33
About Mapping Tables
ExampleMatchesPattern
.
pound sign
#
asterisk
*
dollar sign
$
[no1...no2]
any single characterperiod
any single alphabetic letterhash or
times
any alphabetic character zero
or more times
any number between no1 and
no2
6.
returns all character strings beginning with 6 and followed
by one single character
6#
returns all character strings beginning with 6 and followed
by a single letter of the alphabet
.*any character zero or more
6$ returns all character strings starting with 6 followed
by letters of the alphabet
If the pattern is empty, then it matches all the data.any string(blank)
{EMPTY}
{null}
{NULL}
plus sign
+
preceding character one or
more times
Predefined patterns that match empty dataempty strings{empty}
Predefined patterns that match NULL data
a+ returns the character strings made up of one or more
"a"s - ab+1 returns character strings starting with a ,
followed by b and finishing in 1
2010-12-0633
Page 34
About Mapping Tables
ExampleMatchesPattern
semi-colon
;
OR - enclose list in
{}
NOT<>
escape characterbackslash
\
7.1.2 Target Column Values
All types of expressions recognized by the data services application may be used to display target
column values. Some examples of these expressions are as follows:
{ABC+;XYZ*} - If the data matches either ABC+ or XYZ*
then the result is true.
<> pattern - The data is considered as matching if the
pattern does not match the data.
Used in a pattern before a non-alphabetic or non-numeric
character to indicate to the engine that the character
cannot be used to build the pattern Example: your source
data reads ‘# Trips’. You need to put a backslash in
front of the pound sign '\# Trips' to display ‘#
Trips’ under Target.
• Constants, inside single quotes, such as 'ABC'
• expression : everything is evaluated as an expression.
• Input parameters referred to as the source column name, for example, the mapping table has a
source column called GL_ACCOUNT. The string [GL_ACCOUNT] would refer to the current value of
this input column.
Within the expression operands are used and string functions or numeric functions nested inside string
functions. Some examples are as follows:
• Substring(input_string, start_pos, length)
• Ltrim_blanks(inputstring)
• Rtrim_blanks(inputstring)
• Ltrim(input_string, char_to_trim)
• Rtrim(inputstring, char_to_trim)
• Length(inputstring)
• Index(inputstring, start_pos, char_to_be_found)
• Lpad(inputstring, char_to_pad, length)
• Rpad(inputstring, char_to_pad, length)
2010-12-0634
Page 35
About Mapping Tables
Tip:
A full list of available data services functions can be found in the
SAP BusinessObjects Data Services
Reference Guide.
Note:
You cannot use target dimensions to calculate another target dimension in the financial information
management interface. This type of calculation could be performed by a data services expert by modifying
the underlying data services job created by the financial information application.
7.1.2.1 Dimension Codes Used in SAP BusinessObjects Financial Consolidation
For reference, below is a list of default financial consolidation dimension short codes
LdescSdescCode
CategoryCategoryCA
Data entry periodDat. ent. periodDP
Reporting unitRep. unitRU
CurrencyCurrencyCU
AccountAccountAC
FlowFlowFL
PeriodPeriodPE
Audit IDAudit IDAU
PartnerPartnerPA
ShareShareSH
ScopeScopeSC
VariantVariantVA
Journal entry numberNumberNU
Original reporting unitOrig.RUORU
Technical originTech. orig.TO
2010-12-0635
Page 36
About Mapping Tables
7.1.2.2 Mandatory Dimensions
You must ensure that all mandatory dimensions for the target application are correctly mapped, otherwise
the job fails.
Mandatory dimensions are as follows:
LdescSdescCode
Geographical originGeog.orig.GO
LedgerLedgerLE
Consolidation currencyCons.currncyCC
• In the financial consolidation application:
• Reporting Unit
• Data Entry Period
• Category
• In the intercompany application for:
Local Amount
7.1.3 Amounts
Some amounts need to be mapped with a negative sign.
InvoicesBalances
Transaction AmountTransaction Amount
In order to avoid any rounding performed automatically by the financial information management
application, the following syntax should be used:
to_decimal([Amount],'.','',10)*-1
2010-12-0636
Page 37
About Mapping Tables
The number 10 indicates the number of digits after the decimal. For more information, refer to the SAP
BusinessObjects Data Services documentation.
7.1.4 Changing Column Headers in Flat Files
Once you have defined a mapping table using a flat file, if you change a column name in the flat file,
the changes are not automatically saved. You must re-open the job and re-save the mapping table
definition in order to save the changes.
The procedure is as follows:
1.
Open the job
2.
Click "Source Properties" and select the file whose name you changed.
3.
Click "Mapping Table Definition" and select the new column name.
4.
Click Save
7.2 Populating Mapping Tables from Microsoft Excel
You can populate a mapping table by copying and pasting an individual cell, an entire column or row,
or a range of cells from a Microsoft Excel spreadsheet and paste it into the mapping table.
1.
In Microsoft Excel select the cell, column, or row and select Edit > Copy.
2.
In the mapping table, select the cell in which you want to start pasting data, right-click the cell, and
select Paste from the menu.
Remember:
Make sure that the source and target values from Microsoft Excel are correctly aligned in the financial
information management application.
7.3 Mapping Tables and Drill to Source
In order for the drill to source feature to function properly, you need to include in your mapping tables
some columns from the ECC datasource. The data in these columns is used to drill back to the origin
of the data in the SAP ECC.
These columns depend on the SAP ECC datasource used as you are redirected to a different SAP
ECC transaction based on the SAP ECC datasource you are use.
2010-12-0637
Page 38
About Mapping Tables
SAP ECC Datasource
ECC Transaction
Launched
KE5Z0EC_PCA_1
Required Datasource Columns
RBUKRS, RACCT, FISCPERFAGLB030FI_GL_10
RBUKRS, RACCT, FISCPERFAGLB030FI_GL_20
RBUKRS, BELNR, FISCPERFB030FI_AP_4
RBUKRS, BELNR, FISCPERFB030FI_AR_4
RVERS, KOKRS, RBUKRS, FISCPER, RPRCTR,
RFAREA, RACCT
2010-12-0638
Page 39
Example: Defining and Populating a Mapping Table
Example: Defining and Populating a Mapping Table
8.1 Defining a Mapping Table - Source Data
In this example data is taken from a flat file. The first line contains headers, and the columns are
separated by a semicolon. The source columns are:
• Company
• Account1
• Minority_Interests
• Debit
• Credit
Data in a Flat File
Company;Account1;Minority_Interests;Debit;Credit
SOC1;601100;PART1;10000;
SOC1;601200;PART2;15000;
SOC1;701100;PART1;;50000
SOC1;710000;;;20000
SOC1;411100;PART3;2000;
Below is the same data in table format:
CreditDebitMinority InterestsAccount1Company
10000PART1601100SOC1
15000PART2601200SOC1
50000PART1701100SOC1
20000710000SOC1
2000PART3411100SOC1
2010-12-0639
Page 40
Example: Defining and Populating a Mapping Table
8.2 Linking Source to Target Definitions
In a rule, the source dimension values are selected in order to generate the value of the target dimension,
and the option Highest Priority Matching Rule applies mapping rule is used for this example.
Table 8-3: Mapping Table1: Company/Reporting Unit
In this example, everything under Company is transferred to Reporting Unit.
Target dimensionSource dimension
"Reporting unit ""Company "
[Company]
Table 8-4: Mapping Table 2: Account1, Minority Interests/Account, Partner
For all accounts that begin with a 6 or a 7, the Account target dimension will be filled in with an R
followed by the account number. The Minority Interests column is reclassified: PART1 becomes P1,
PART2 becomes P2 and the "empty" cell remains so.
Target DimensionsTarget DimensionsSource DimensionSource Dimension
PartnerAccountMinority_InterestsAccount1
'P1''R'||[Account1]'PART1'[6..7]*
'P1''R'||[Account1]'PART2'[6..7]*
'R'||[Account1]{empty}[6..7]*
Table 8-5: Mapping Table 3: Account1, Debit, Credit/Amount
For expense accounts, the Amount target dimension will be filled in with the opposite of the Debit source
column. For client accounts, the Debit source dimension is transferred to the Amount target dimension
and remains unchanged. Similarly, for product accounts, the Credit source dimension is transferred to
the Amount target dimension and remains unchanged.
Target DimensionSource DimensionSource DimensionSource Dimension
AmountCreditDebitAccount1
[Debit]*-16*
[Debit]41*
[Credit]7*
2010-12-0640
Page 41
Example: Defining and Populating a Mapping Table
Explanation:[Debit]*-1 means that you need to multiply the debit amount by -1 to return a negative value
8.3 Results
The data produced as a result of the job is as follows:
AmountPartnerAccountReporting unit
-10000P1R601100SOC1
-15000P2R601200SOC1
50000P1R701100SOC1
20000R710000SOC1
The production report shows that the row corresponding to account 411100 has not been processed
since no rule was defined for it in Mapping Table 2: Account1, Minority Interests/Account, Partner.
2010-12-0641
Page 42
Example: Defining and Populating a Mapping Table
2010-12-0642
Page 43
Example: Mapping Rule Behavior
Example: Mapping Rule Behavior
A mapping table uses one or more rules to generate the value of the target dimension. The data in the
example is taken from a database table. The source columns are:
• Company code
• Posting period
• Fiscal year
• Currency
• Account number
• Trading partner
• Opening balance
• Total debit
• Total credit
• Ending balance
Your mapping rules for each mapping table are set to either Highest Priority Matching Rule applies
or All Matching Rules apply.
Note:
For option Highest Priority Matching Rule applies, when no priority column is added in the mapping
the first mapping has the priority.
The source sample data is:
Company
_Code
Posting
_Period
Fiscal
_year
Cur
rency
Account
_Number
Trading
_Partner
Opening
_Balance
Total
_Debit
Total
_credit
Ending
_Balance
520000052000001000144000EUR200845
4500004500001100144000EUR200845
4651.16004651.166145000EUR200845
Table 9-2: Mapping Table: Company Code/Reporting Unit
When the Highest Priority Matching Rule applies rule is used to match the source data in the Com
pany_Code column to the target RU (Reporting Unit) column, company code 5 is mapped to IEFO002T- and company code 6 is mapped to RTDF001-Y-.
2010-12-0643
Page 44
Example: Mapping Rule Behavior
RUCompany_Code
5
6
'IEFO002-T-'
'RTDF001-Y-'
Table 9-3: Mapping Table: Currency
The currency is left as is.
CUCurrency
*
[Currency]
Table 9-4: Mapping Table: Posting Period & Fiscal Year/Data Entry Period (DP) & Period (PE)
When the Highest Priority Matching Rule applies rule is used to match the source data in the
Posting_Period and Fiscal_Year columns to the target DP and PE columns, the posting period
1 and the fiscal year 2008 are mapped to 2008.01 in the target DP and PE columns.
DPFiscal_yearPosting_Period
PE
'2008.01''2008.01'20081
'2008.02''2008.02'20082
'2008.03''2008.03'20083
'2008.04''2008.04'20084
Table 9-5: Mapping Table: Account Number/Account (AC)
When the Highest Priority Matching Rule applies is used to match the source data in the Ac
count_Number column to the target AC column, account 144000 is mapped to A2460 in the target AC
column:
ACAccount_Number
144000
145000
'A2460'
'E1110'
2010-12-0644
Page 45
Example: Mapping Rule Behavior
Table 9-6: Mapping Table: Trading Partner/Partner (PA)
The All Matching rules apply rule is used to match the source data in the Trading_Partner column
to the target PA column. The last line in the rule (*) generates for each row an additional row that will
roll-up to calculate the aggregated value:
PATrading_Partner
1000
1100
6
'FRDR005-T'
'NLFO001-T'
'USDR001-T'
*
Table 9-7: Mapping Table: Opening Balance & Ending Balance/Flow (FL) & Amount
The All Matching Rules apply rule instructs the target file to take the opening and ending balances
for specific accounts and aggregate these balances per fiscal year:
Account
_Number
Opening _Balance
Ending _Balance
**144000
**144000
**145000
**145000
AMOUNTFL
[Opening_Balance]'F00'
[Ending_Balance]'F99'
[Opening_Balance]*-1'F00'
[Ending_Balance]*-1'F99'
The flow (FL) enables you to differentiate between amounts at opening and closing during the data
entry period.
Example results
The data produced as a result of the job is as follows:
AMOUNTFLPAACCUPEDPRU
IEFO002T-
IEFO002T-
IEFO002T-
EUR2008.042008.04
EUR2008.042008.04
EUR2008.042008.04
F00FRDR005-TA2460
F00NLFO001-TA2460
F00USDR001-TE1110
0
0
-4651,16
2010-12-0645
Page 46
Example: Mapping Rule Behavior
AMOUNTFLPAACCUPEDPRU
IEFO002T-
IEFO002T-
IEFO002T-
IEFO002T-
IEFO002T-
IEFO002T-
IEFO002T-
EUR2008.042008.04
EUR2008.042008.04
EUR2008.042008.04
EUR2008.042008.04
EUR2008.042008.04
EUR2008.042008.04
EUR2008.042008.04
F99FRDR005-TA2460
F99NLFO001-TA2460
F99USDR001-TE1110
F00A2460
F00E1110
F99A2460
F99E1110
520000
45000
-4651,16
0
-4651,16
565000
-4651,16
2010-12-0646
Page 47
After You Have Created a Job
After You Have Created a Job
Once you have finished creating a job, it appears in the List of Jobs. At this point, you have several
options as follows:
• Open the job to verify the properties
• Delete the job
• Run the job
• Export the job
• Import the job
You can also save the job to a scheduling file to be run by an external scheduler.
Related Topics
• Choosing to Launch a Job Via an External Scheduler
• Moving Financial Information Management Job Definitions
10.1 Executing a Job
When you have finished populating the mapping tables, you are ready to execute the financial information
management job.
1.
In the "List of Jobs", select the job you want to execute and click Run.
A dialog box bearing the name of the job opens. You select the dimensions to filter in this dialog
box.
2.
In the "Select dimensions to filter" box, select the dimension or dimensions you want to filter and
click Add. The dimensions appear in the "Dimension "column.
3.
For each dimension, under "Action" click Browse and select the members from the "Item Selector".
4.
Under "Target Application Options", select the parameters you want to use. The options are as
follows depending on the target application:
2010-12-0647
Page 48
After You Have Created a Job
OptionsTarget Application
SAP BusinessObjects Intercompany
SAP BusinessObjects Financial Consolidation
If you are importing balances:
• Reset Local Amount Journals
• Reset Transaction Amount Journals
• Delete Matching Period/Company Before Execution
If you are importing invoices:
• Overwrite existing
• Create/Update balances, with the additional options as follows:
• Reset Local Amount Journals
• Reset Transaction Amount Journals
• Delete matching Period/Company before execution
• No deletion before execution. Replace existing data.
the matched existing data is replaced by the imported data. Data
that does not correspond to the imported data in the same package remains unchanged.
• No deletion before execution. Aggregate.
new data loaded by the job is added to existing data, subject to
the same dimensionality
• Delete all before execution
all existing data in the financial consolidation application relating
to the Reporting ID is deleted before importing new data.
• Create package if it doesn't exist
• Run package rules
triggers the package's rules after import completion. Package
rules automatically create journal entries.
• Run controls
triggers the package's publishing process after import completion.
You certify that the content of the package is valid, and make it
available for the publication site.
• Publish package
triggers the package's publishing process after import completion.
You certify that the content of the package is valid, and make it
available for the publication site.
• Integrate
triggers the integration process of the package after import completion.
2010-12-0648
Page 49
After You Have Created a Job
5.
Click Execute.
The financial information management application executes the job and posts the status under the
"List of Jobs".
10.2 Job Run History
The "Job Run History" page provides important details on the execution of a job as follows:
• "Status":
• Succeeded, that is, the call to the web services was successful.
• Failed, the call to the web services failed.
• "Start Time"
• "Duration"
• "User": the name of the user
• "Imported Lines": the number of lines imported into the target application.
Note:
Only the number of imported lines for the last execution of the job is available.
• "Mapping Errors": the number of mapping errors
• "Job Run ID"
The lower part of the view displays the results of the web service call to the target application for
importing data:
• Import status, giving the:
• "Package ID number"
• "Status"
• "Error message", for example, "The dimension name VE does not exist in the data source."
• "Unmapped lines": the rows where no mapping rule could be applied
• "Rejected lines" with details on the cause and a description, for example, "The reporting ID xxx does
not exist".
• the "imported lines" with information on the dimensions
Note:
When you load data into the financial consolidation application, one line is returned per package.
• "Error Log": shows the data services error log, which is of interest to technical users.
• "Trace log": shows the data services trace log, which is of interest to technical users.
• "Monitor Log": shows the data services monitor log, which is of interest to technical users.
10.3 Choosing to Launch a Job Via an External Scheduler
2010-12-0649
Page 50
After You Have Created a Job
When you create a financial information management job, you can run the job immediately or you can
choose to launch the job via an external scheduling tool such as Windows task scheduler. You need
to generate a scheduling file that contains a command line to launch Internet Explorer on a given URL.
The scheduling file gives information on the selected job, the execution parameters, and the user
credentials.
1.
Under "List of Jobs", select the job, then click Run.
2.
Select the dimensions to filter and the runtime parameters.
3.
Click Save as scheduling file....
You are prompted to enter your login credentials.
4.
Enter your user name and password, then click OK.
A message is displayed informing you that the scheduling file for the job has been generated.
5.
Click Download, then browse to the location in which you want to save the generated script file.
6.
Click Save.
7.
Click Close.
When the job is launched, the result is sent to a log file.
Note:
The length of the generated URL may not be longer than 2000 characters. Setting a large number of
complex filters on a job may generate a URL longer than 2000 characters, in which case job execution
fails and a warning message is displayed.
10.4 Saving a Job
You can save changes that you make to a job.
1.
In the "List of Jobs", select the job and click Open.
2.
Make the required changes.
3.
Click Save.
A message informs you that the job has been saved. In the event of an error, an error message is
returned.
10.5 Duplicating an Existing Job
When you duplicate an existing job, the following information is included in the new job:
• the external job setting and data source job name
• the job description
• source and target properties
2010-12-0650
Page 51
After You Have Created a Job
• mapping table definitions
To duplicate an existing job:
1.
Under List of Jobs selct the job you want to duplicate, then click Open.
2.
Click Properties, then select users for the job.
3.
Click Save as....
A dialog box opens prompting your for a new name for the job.
4.
Type a new name for the job in the the dialog box.
Note:
The name of the job cannot contain spaces or non-alphabetic or non-numeric characters.
5.
Click OK.
A message reading "The job has been saved" appears in the upper-right part of your screen.
10.6 Deleting a Job
You delete jobs from the "Home "page.
1.
Under List of Jobs, select the job that you want to delete.
2.
Click Delete.
The job is deleted.
When a job is deleted only auditors have the rights to view deleted data. They can view the following:
• Source and target information for all jobs
• Data services run logs
• Execution status by package for each run instance
• Execution status of each job run instance
• Mapping table structure for all mapping tables
• Mapping table list for all jobs
• Mapping table content
10.6.1 Viewing Deleted Data
Only an auditor has the rights to view deleted, or outdated, data. When an auditor is logged on to the
application the deleted jobs appear greyed-out in the "List of Jobs". An icon in the form of a garbage
can appears next to the name of the job.
2010-12-0651
Page 52
After You Have Created a Job
To view deleted data proceed as follows:
1.
Under "List of Jobs" select the job for which you want to view the deleted data.
2.
Click Open.
The deleted job opens.
You can view the job properties.
2010-12-0652
Page 53
About the Drill to Origin Feature
About the Drill to Origin Feature
The drill to origin feature allows you to view the origin of data from the target application.
Before you can use the drill to origin feature it must have been enabled by your administrator in either
the financial consolidation or the intercompany application.
Refer to the
BusinessObjects Intercompany Installation Guide
SAP BusinessObjects Financial Information Management Installation Guide
for details on enabling the drill-to-origin feature.
11.1 Displaying the Origin of Data in the Financial Consolidation Application
In the financial consolidation application, open your report or schedule.
1.
Select the cell for which you want to view the origin of data, right-click, and select Drill-to-Origin
from the menu.
The "Drill-to-Origin" dialog box opens. Values are displayed for each of the finance dimensions.
2.
Verify that these values are correct, then click Continue.
The Drill-to-Origin page displays information from the transaction table, including:
• job_run_ID
• Row number, which corresponds to the row number in the transaction table
• Amount
• other target and source values
Example:
CURRENCY=EUR
or the
SAP
2010-12-0653
Page 54
About the Drill to Origin Feature
ROW NUMBERjob_run_ID
Note:
You can export the content of the table as a .csv, .xls or .xml file by clicking on the appropriate button
in the "Export Options "below the table. When you export to Excel the results of more that one financial
information job are displayed.
11.2 Displaying the Origin of Data in the Intercompany Application
P_AMOUNTPAINTERCOACCOUNTPERIODENTITY
15000.00R0010USA1R100I06-12ITA182628203207
5000.00R0120CAN1R100I06-12ITA183628203207
20000.00R100I06-12ITA184628203207
In the intercompany application open your report, then proceed as follows:
To view the origin of invoicesTo view the origin of balancesStep
Open the balance in question1.
Open the balance page of the invoice in
question.
2.
Go into the "Account Details". This re-
Click the "Invoices" column.
quires several clicks.
The application displays all the invoices.
Click on the currency code.3.
Click the origin of data icon for the particular
invoice.
The financial information management application opens to the Drill to origin page that displays data
from the transaction table including the following:
• Job name
• job_run_ID
• other target and source values
Example:
The origin of the data is displayed as follows:
2010-12-0654
Page 55
About the Drill to Origin Feature
The requested cell { COMPANY= R0100, PARTNER=R0020, CURRENCY=USD, CHILDACCOUNT=
A1130_F20, PERIOD=MARIAME } was traced to the following cells:
JOB_DESCRIPTION
ICB2JOB_NAME
-475JOB_RUN_ID
A1130_F20SC_ACCOUNT
R0100SC_COMPANY
USDSC_CURRENCY
1000SC_LOCALAMOUNT
R0020SC_PARTNER
MARIAMESC_PERIOD
1000SC_TRANSACTIONAMOUNT
A1130_F20CHILDACCOUNT
R0100COMPANY
USDCURRENCY
10000LOCALAMOUNT
2010-12-0655
Page 56
About the Drill to Origin Feature
Note:
You can export the content of the table as a .csv, .xls or .xml file by clicking on the appropriate button
in the "Export options "below the table. When you export to Excel the results of more that one financial
information job are displayed.
R0020PARTNER
MARIAMEPERIOD
10000TRANSACTIONAMOUNT
2010-12-0656
Page 57
Drilling to SAP ECC Source Data
Drilling to SAP ECC Source Data
Before you can use the drill-to-source capability of the financial information management application,
"Drill-to-source" must have been enabled when the SAP ECC datastore was created.
After you have loaded data from an SAP ECC to either the financial consolidation or the intercompany
application, you can drill from a value in the target application to the source data in the SAP ECC system.
You have displayed the origin of data from the target application and you have returned to the financial
information management application. A table displays the cells traced to the cell that you selected in
the target application.
1.
In the table locate the cell for which you want to view the source data and, in the corresponding row,
under "D2S" click View Source.
The first time you use the drill-to-source feature you are prompted to log on to the SAP ECC
application.
2.
Enter your login information, then click Connect.
You are connected to the SAP ERP"Data Entry View" that shows the data in the SAP ECC of origin.
12.1 Example: Displaying Source Data in SAP ECC
The following image shows the page in the financial information management application that displays
the origin of data that was loaded into SAP BusinessObjects Intercompany from an SAP ECC.
The following image shows the "Data Entry View" in SAP ECC.
2010-12-0657
Page 58
Drilling to SAP ECC Source Data
Note that the values in the financial information management table correspond to the information in the
"Data Entry View" as follows:
• " SC_BELNR" to the "Document Number"
• "SC_LIFNR" to the "Account"
• "LOCALAMOUNT" to "Amount"
The SAP ECC transaction varies according to the extractor, or datasource, used by the job, for example,
General Ledger, Accounts Receivable, Accounts Payable, Profit Center, and so on.
2010-12-0658
Page 59
Sharing Transaction Tables Among Several Jobs
Sharing Transaction Tables Among Several Jobs
Each financial information management job has its own transaction table that is used to display
drill-to-origin results. This table may be completely or partially cleaned-up at job runtime depending on
the target application loading options.
When you execute a job, the job name is added to the transaction table in order to differentiate the
drill-to-origin results when the transaction table is used by more than one job.
13.1 Renaming a Transaction Table
You may want to rename a transaction table, for example, when you are sharing a table among jobs
that import the same line of data.
To rename a transaction table, open the job and enter a new name in the "Transaction Table" box on
the "Properties" page.
2010-12-0659
Page 60
Sharing Transaction Tables Among Several Jobs
2010-12-0660
Page 61
Moving Financial Information Management Job Definitions
Moving Financial Information Management Job
Definitions
Once you have created a financial information management job you may want to move it to a different
environment, for example, from a development environment to a test environment, or from a test
environment to a production environment.
To move a job definition to a different environment you first export the job, which generates a flat file.
You then transfer the flat file to the target environment for import.
You move job definitions from the "List of Jobs" page
Related Topics
• Exporting a Financial Information Management Job
• Importing a Financial Information Management Job
14.1 Exporting a Financial Information Management Job
You export jobs from the Home page.
To export a job:
1.
Under "List of Jobs" select the job or jobs you want to export.
2.
Click Export.
The list of jobs you want to export appears in the "Export Jobs" dialog box.
3.
If you want to export the dependencies, select Export dependencies.
4.
Click Next.
5.
Click Save.
The job is exported in an .xml file. The file name is the job nickname with the .xml extension. You
cannot change the extension.
6.
Select the directory to which you want to save the file locally, then click Save.
7.
Click Close.
The following parameters are exported:
• Job properties
• Source properties
2010-12-0661
Page 62
Moving Financial Information Management Job Definitions
• Target properties
• Mapping table definition
• Mapping table content
• Source connection (if any)
• Target connection
The following parameters are not exported:
• Mapping table history
• Job run history
• Source file (if any)
• Last run parameters (filters and target application properties
• Underlying data services job
14.2 Importing a Financial Information Management Job
Before you attempt to import a job, note that flat files cannot be imported. If you are using a flat file as
a source of data, you must copy the flat file located in the upload file folder of the server from which
you are exporting the job to the upload file folder of the server to which you are importing the job. The
upload file folder is defined in the administration console.
To import a job:
1.
On the Home page under "List of Jobs" select the job you want to import, then click Import.
2.
In the "Import Objects" screen click Browse to locate the xml source file of the import.
3.
Click Next.
The Select Objects screen lists the objects that you can import. If the object already exists, this
information is displayed in the "Already exist" column. If the object already exists in the financial
information management environment and you select "Import", the existing object is overwritten by
the import.
4.
Click Next.
The financial information management job or jobs are imported and the underlying data services
jobs are regenerated. The results are displayed in the "Import Result" screen.
Note:
Press CTRL + Shift to select more than one job to export or import.
Note:
When you import datastore that has the same name as an existing datastore, regardless of the case,
the existing objects are updated. Therefore, we highly recommend that, when moving job definitions
from one environment to another, you use the same datastore name and that the connection names
be written in the same case. When working with Oracle databases, datastore names must be written
in upper case.
2010-12-0662
Page 63
Use Cases
Use Cases
15.1 Use Case 1
This use case transforms amounts of a debit account into negative amount while importing to a specific
account of the target system. Debit account starts with 1 and matches to account 10 in the target system
. Source file columns are Account, …., Amount. Target system dimensions are ACCOUNT, … AMOUNT
Rule 1a: If an account starts with 1 then the target account is 10.
Rule 1b: Default rule: Target account equals source account
Table 15-1: Mapping table for transformation of account
ACCOUNTAccount
[Account]*
'10'1*
Table 15-2: Mapping table for transformation of amount
Rule 2a: If an account starts with 1 then target amount is "source amount" * -1
Rule 2b: Default rule - Target amount equals source amount
AMOUNTAmountAccount
[Amount] * -1*1*
[Amount]**
2010-12-0663
Page 64
Use Cases
Table 15-3: Example
AMOUNTACCOUNTAmountAccount
15.2 Use Case 2
Import data detailed by partner and import also total (Aggregation of partners) into source system
The source file columns are Partner, Dim1….
The target system dimensions are PARTNER, DIM1….
Create 2 jobs: DETAIL to imported data detailed by partner and TOTAL to import total line
- TOTAL will not have a mapping for transformation of partner.
This allows lines to be aggregated ignoring partner dimension
1000123
20002
>> because of rules 1a
and 2a
>> because of rules 1b
and 2b
-100010
20002
Table 15-4: DETAIL will have a mapping for transformation of partner
PARTNERPartner
[Partner] Rule 1.*
Note:
.* prevents matching empty partners. These lines are imported by job TOTAL.
Table 15-5: Job DETAIL transforms data
10001Val1
>>
AMOUNTPARTNERDIM1AmountPartnerDim1
10001Val1
2010-12-0664
Page 65
Use Cases
AMOUNTPARTNERDIM1AmountPartnerDim1
20002Val1>>20001Val2
30001Val2>>30001Val2
>>4000Val3
Line is not processed
because of Rule 1
Table 15-6: Job TOTAL transforms data l
AMOUNTDIM1AmountPartnerDim1
3000Val1>>10001Val1
Partner is ignored. Lines
are aggregated
>>20002Val1
3000Val2>>30001Val2
4000Val3>>4000Val3
Table 15-7: Warning: if source mix total and detailed line , result can be inconsistent
AMOUNTDIM1AmountPartnerDim1
3000Val230001Val2
Val34000Val3
9000 - Unexpected amount
value
50001Val3
2010-12-0665
Page 66
Use Cases
2010-12-0666
Page 67
Custom Jobs
Custom Jobs
16.1 About Data Services Job Customization
The financial information management solution is a business user interface on top of the data services
application. The financial information management solution loads data into the EPM (Enterprise
Performance Management) applications. It generates data services jobs that business users launch
from the financial information management interface.
The data services jobs can be customized at the data services level to benefit from the features offered
by the data services solution, such as broad connectivity to information systems and scripting language.
Note:
Using this feature requires in-depth knowledge of SAP BusinessObjects Data Services, and therefore,
is limited to technical users.
Related Topics
• Customizing a Data Services Job
• Data Services Objects
• Global Variables
• Repository Tables
16.1.1 Dataflows
The generated data services jobs is composed of a sequence of dataflows as follows:
• An export dataflow, responsible for extracting the required data from the source of the financial
information management job, and applying the runtime filters
• A transform dataflow, responsible for applying the mapping rules, and identifying the mapping errors
• A load dataflow, responsible for loading the data to the target application of the financial information
management job, and identifying the rejected rows by the target application.
On the left of the SAP BusinessObjects Data Services Designer window the following components of
a data services project are displayed:
• Under "Project Area" the data services project generated by the financial information management
application, with the three dataflows
2010-12-0667
Page 68
Custom Jobs
Note:
The job that was created has the same name as the job name assigned in the financial information
management application.
• Under "Datastore", the various datastores including those created by the job
On the right side of the designer, a schematic of the dataflows is displayed.
For detailed information on dataflows, refer to the SAP BusinessObjects Data Services documentation.
16.1.2 The Export Dataflow
The export dataflow is dedicated to extracting data from a source.
The following image shows the export dataflow in the data services designer.
The database table is was selected in the financial information management application. The source
table in the schematic is a temporary table used to exchange data.
16.1.3 The Transform Dataflow
The transform dataflow takes the data and applies the mappings defined in the financial information
application.
The validation controls shown in the diagram make sure that every line in the table was translated using
at least one mapping rule. If this is not the case, the line has a null value and the data services application
pushes the row in to the mapping error table in the financial information management repository.
The data is passed to the transaction table, which is used in the drill-to-origin function. The transaction
table contains the data with its definition before and after mappings.
2010-12-0668
Page 69
Custom Jobs
16.1.4 The Load Dataflow
The load dataflow is dedicated to loading data into the target system. It take data from the transaction
table and prepares the data for the web services call. It then gathers the output from the web services
call and ascertains if there is a problem. Rejected rows are stored in tables in the financial information
management repository.
You can view the details generated by the load dataflow in the "Job Run Details" page of the financial
information management application.
16.2 Data Services Objects
When you create a job using the financial information management interface, data services jobs are
generated in the data services repository. In this table you need to replace jobName by the name of
the financial information management job that you created. The job name in financial information
management is limited to 8 characters.
Data Services Object NameDescription
PROJECT_%jobName%DS Project Name
%jobName%DS Job Name
DF_EXPORT_%jobName%Export DS DataFlow
DF_TRANSFORM_%jobName%Transform DS Data Flow
DF_LOAD_%jobName%Load DS DataFlow
Financial information management repository DB
DS DataStore Name
%jobName%_DS
2010-12-0669
Page 70
Custom Jobs
16.3 Global Variables
Data Services Object NameDescription
%jobName%_FILE_EXPORT_DSFile Export DS DataStore Name
%jobName%_TABLE_EXPORT_DSTable Export DS DataStore Name
%jobName%_FC_LOAD_DSFC Load Web Service DS DataStore Name
%jobName%_PCM_LOAD_WS_DSPCM Load Web Service DS DataStore Name
%jobName%_FC_LOAD_TABLE_DSPCM Repository DS DataStore Name
Creating a financial information management job also creates, as part of the job definition, a list of
global variables. These variables are set at runtime when the business user launches the job through
the financial information management interface.
Table 16-2: Global Variables
DS Global Variable NameDescription
Financial Information Management Job
Run ID
$JOB_RUN_ID
$SerializedSessionBOE SSO logon token
$UserBOE user name
$PasswordBOE user password
$Filter_%SourceDimensionName%Filters on Source Dimensions
$aggregateAmounts, etc ….Target Application Options
The following image shows the variables as they appear in the data services application interface. When
you execute a job in the financial information management application, you set runtime parameters.
Their equivalent appears in the list of global variables in the data services application.
A generated data services job also contains the list of available filters found in the financial information
management application.
The JOB_RUN_ID is an autogenerated number assigned to an instance of a job when it is run. Each
time a job is run, a new ID is assigned to the instance.
2010-12-0670
Page 71
Custom Jobs
16.4 Repository Tables
When you create a job using the financial information management interface, data services jobs are
generated in the data services repository. In this table you need to replace jobName by the name of
the financial information management job that you created. The job name in financial information
management is limited to 8 characters.
Financial Information Management Repository Table NameDescription
SOURCE_%jobName%Source Table Name
TRANSACTION_%jobName%Transaction Table Name
SC_%DimName%Source Column in Transaction Table
FIM_%jobName%_%MappingTableName%Mapping Tables
MAPPING_ERRORS_%jobName%Mapping Errors Table
2010-12-0671
Page 72
Custom Jobs
The source table is created in the financial information management repository to store the extracted
rows from the source. This table is not mandatory. The generated data services job can be customized
to eliminate this staging area.
The transaction table is created in the financial information management repository to store the output
of every successfully-applied mapping. The transaction table contains mappings from every source set
of data to every target set of data.
The transaction table is queried when you use the drill-to-origin feature from the target application to
display the origin of data.
Note:
The data services job is responsible for populating transaction tables. The financial information
management application never writes to these tables directly.
Generated data services jobs use the following other financial information management repository
tables:
Description
16.5 Customizing a Data Services Job
To customize a data services job, you must perform the following steps, which are explained in greater
detail:
1.
Create and execute a financial information management job.
The financial information management application generates a corresponding data services job.
2.
Via the data services management console, publish the generated data services job to the web
services.
3.
In the financial information management application, connect to the data services job.
4.
In the data services application, modify the data services job to suit your requirements.
5.
In the financial information management application, execute the job.
Financial Information Management Repository
Table Name
FIM_REJECTED_ROWRejected Rows Table Name
FIM_JOB_RUN_PACKAGE_STATUSJob Run Status
16.5.1 Publishing a Data Services Job to Web Services
2010-12-0672
Page 73
Custom Jobs
1.
From the Start menu clickSAP BusinessObjects XI 4.0 > BusinessObjects Data Services > Data
Services Management Console, then log in.
2.
Click "Administrator".
3.
On the left side of the page under "Administrator", click "Web Services".
4.
On the right side of the page, click the "Web Services Configuration" tab.
A list of published web service operations appears on the page.
5.
In the box, select Add Batch Job..., then click Apply.
6.
In the next page, select the job, then click Add.
Below the "Web Services Configuration" tab, a message appears indicating that the web service
provider for the batch job was successfully added.
You have successfully published the data services job to web services. You can close the window.
16.5.2 Connecting to a Data Services Job
When you connect to an existing data services job, you tell the financial information management
application that this is an external job. The application does not modify or erase the customized version.
1.
In the financial information management application, under "List of Jobs" select and open the job
that you want to modify.
2.
On the "Properties" page, select "Connect to an existing SAP BusinessObjects Data Services job".
3.
Select the job that you want to modify from the list, which displays all the jobs that were published
to web services.
By default, the names of the transaction table and the validation error table appear in the appropriate
boxes.
4.
Click Save.
Note:
Do not click Execute until after you have modified the job in the data services application.
16.6 Importing a Custom Job
When you export a job, the underlying data services job is not exported. If you have created a custom
data services job, you need to export the custom job and then import it as follows:
1.
In "DataServices Designer" log in to the source data services repository.
2.
Right click in the local object library >> Repository >> Export to file. Include the datastores used
the first time the job is moved from the source data services repository to the target data services
repository
Exporting to file generates an ATL file.
2010-12-0673
Page 74
Custom Jobs
3.
Through "DataServices Designer "log in to the target data services repository and import the ATL
file.
Note: If this is the first time the custom job is moved to the target environment, you need to modify
the datastore properties to point to the correct environment. This is particularly true for the datastore
pointing to the financial information management repository.
4.
In the "Data Services Management Console", verify sure that the web services URL is correct.
After you have exported the custom job, import the job as explained in Importing a Financial Information
Management Job
2010-12-0674
Page 75
More Information
More Information
LocationInformation Resource
SAP BusinessObjects product information
SAP Help Portal
SAP Service Marketplace
http://www.sap.com
Navigate to http://help.sap.com/businessobjects and on the "SAP BusinessObjects Overview" side panel click All Products.
You can access the most up-to-date documentation covering all SAP
BusinessObjects products and their deployment at the SAP Help Portal.
You can download PDF versions or installable HTML libraries.
Certain guides are stored on the SAP Service Marketplace and are not
available from the SAP Help Portal. These guides are listed on the Help
Portal accompanied by a link to the SAP Service Marketplace. Customers
with a maintenance agreement have an authorized user ID to access
this site. To obtain an ID, contact your customer support representative.
http://service.sap.com/bosap-support > Documentation
• Installation guides: https://service.sap.com/bosap-instguides
• Release notes: http://service.sap.com/releasenotes
The SAP Service Marketplace stores certain installation guides, upgrade
and migration guides, deployment guides, release notes and Supported
Platforms documents. Customers with a maintenance agreement have
an authorized user ID to access this site. Contact your customer support
representative to obtain an ID. If you are redirected to the SAP Service
Marketplace from the SAP Help Portal, use the menu in the navigation
pane on the left to locate the category containing the documentation you
want to access.
Docupedia
Developer resources
https://cw.sdn.sap.com/cw/community/docupedia
Docupedia provides additional documentation resources, a collaborative
authoring environment, and an interactive feedback channel.
https://boc.sdn.sap.com/
https://www.sdn.sap.com/irj/sdn/businessobjects-sdklibrary
2010-12-0675
Page 76
More Information
LocationInformation Resource
SAP BusinessObjects articles on
the SAP Community Network
Notes
Forums on the SAP Community
Network
Training
Online customer support
https://www.sdn.sap.com/irj/boc/businessobjects-articles
These articles were formerly known as technical papers.
https://service.sap.com/notes
These notes were formerly known as Knowledge Base articles.
https://www.sdn.sap.com/irj/scn/forums
http://www.sap.com/services/education
From traditional classroom learning to targeted e-learning seminars, we
can offer a training package to suit your learning needs and preferred
learning style.
http://service.sap.com/bosap-support
The SAP Support Portal contains information about Customer Support
programs and services. It also has links to a wide range of technical information and downloads. Customers with a maintenance agreement
have an authorized user ID to access this site. To obtain an ID, contact
your customer support representative.
Consulting
http://www.sap.com/services/bysubject/businessobjectsconsulting
Consultants can accompany you from the initial analysis stage to the
delivery of your deployment project. Expertise is available in topics such
as relational and multidimensional databases, connectivity, database
design tools, and customized embedding technology.
2010-12-0676
Page 77
Index
A
administrator 11
amounts 36
asterisk 32
B
backslash 32
business user 11
C
column headers 37
csv file 26
D
Data Services 67
data services, repository 71
data, deleted 51
data, origin 54
database, as a source 26
dataflows 67
datastore 13, 69
datastore properties 13
datastore, database 17
datastore, ECC 16
datastore, financial consolidation 14
dimension codes 35
dimensions 36
drill to origin, intercompany 54
drill to source 57
drill-to-origin 53
drill-to-source 57
E
ECC 25
escape character 32
Excel 37
executor 11
export dataflow 68
external scheduler 50
F
financial consolidation application, as
a target 29
flat file 26, 37, 39, 61
flat file, as a source 26
flat file, importing 62
H
history, job run 49
I
intercompany application 29
J
job execution 47
job run history 21, 49
jobs 21, 47
jobs, deleting 51
jobs, duplicating 50
jobs, exporting 61
jobs, importing 62
jobs, moving 61
jobs, saving 50
L
load dataflow 69
logs 49
M
mapping rules 43
mapping table 39
mapping table history 21
mapping tables 31, 37, 43
mapping tables, creating 31
mapping tables, ECC data 37
mapping tables, pasting data 37
N
NetWeaver 29
NetWeaver, as a target 29
O
objects 69
Open Hub 25
Oracle 17, 18, 62
oracle database 13
origin of data 53
outdated data 51
P
priority 31
R
rejected rows 69
repository 71
S
SAP ECC 13, 25
SAP NetWeaver BW 25
scheduling file 50
source columns 32
source data 25, 39, 57
SQL server 13
syntax 32
T
target columns 34
target definitions 40
transaction table 59
transaction table, renaming 59
txt file 26
V
validation error table 49
variables 70
W
web services 72
financial consolidation application, as
a source 26
2010-12-0677
Page 78
Index
2010-12-0678
Loading...