English - SAP Help Portal
SAP Library
Data Export from SAP Integrated Business Planning 5.0
PUBLIC
Document Version: 2.0 – March 10, 2015
Data Export from SAP Integrated Business Planning 5.0
1
SAP Library
Copyright
© 2015 SAP SE or an SAP affiliate company. All rights reserved
SAP Library document classification: PUBLIC
No part of this publication may be reproduced or transmitted in any form or for any purpose
without the express permission of SAP SE or an SAP affiliate company
SAP and other SAP products and services mentioned herein as well as their respective logos are
trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and
other countries. All other product and service names mentioned are the trademarks of their
respective companies.
Please see http://www.sap.com/corporate-en/legal/copyright/index.epx#trademark for
additional trademark information and notices.
Data Export from SAP Integrated Business Planning 5.0
2
SAP Library
Icons in Body Text
Icon
Meaning
Caution
Example
Note
Recommendation
Syntax
Additional icons are used in SAP Library documentation to help you identify different types of
information at a glance. For more information, see Help on Help General Information
Classes and Information Classes for Business Information Warehouse on the first page of any
version of SAP Library.
Typographic Conventions
Type Style
Example text
Example text
EXAMPLE TEXT
Example text
Example text
<Example text>
EXAMPLE TEXT
Description
Words or characters quoted from the screen. These include field names, screen
titles, pushbuttons labels, menu names, menu paths, and menu options.
Cross-references to other documentation.
Emphasized words or phrases in body text, graphic titles, and table titles.
Technical names of system objects. These include report names, program
names, transaction codes, table names, and key concepts of a programming
language when they are surrounded by body text, for example, SELECT and
INCLUDE.
Output on the screen. This includes file and directory names and their paths,
messages, names of variables and parameters, source text, and names of
installation, upgrade and database tools.
Exact user entry. These are words or characters that you enter in the system
exactly as they appear in the documentation.
Variable user entry. Angle brackets indicate that you replace these words and
characters with appropriate entries to make entries in the system.
Keys on the keyboard, for example, F2 or ENTER.
Data Export from SAP Integrated Business Planning 5.0
3
SAP Library
Data Export from SAP Integrated Business Planning ............................................................... 5
1 Data Export Using the API .................................................................................................. 5
1.1 API Reference .............................................................................................................. 6
1.1.1 API Examples ........................................................................................................ 8
1.1.1.1 Using Time Period IDs Instead of Date and Time .......................................... 8
1.1.1.2 Specifying a Version for Obtaining Key Figure Data ...................................... 9
1.1.1.3 Specifying Output in CSV Format ................................................................... 9
2 Data Export Using HCI ...................................................................................................... 10
2.1 Configuring Data Export in HCI .................................................................................. 10
2.1.1 Export Data from IBP to a File on an On-Premise System ................................. 11
2.1.1.1 Procedure for Exporting Data to a File.......................................................... 13
2.1.2 Export Data from IBP and Interface Directly to an On-Premise System via a Web
Service .......................................................................................................................... 15
2.1.2.1 Procedure for Exporting Data via a Web Service ......................................... 16
Data Export from SAP Integrated Business Planning 5.0
4
SAP Library
Data Export from SAP Integrated Business Planning
Version
Data Export Guide (formerly called API Guide), 2.0 - March 10, 2015, Public
Overview
SAP Integrated Business Planning (IBP) increases the visibility of data to support decisions
around controlling costs, improving customer service, and much more. It works with data from
operational systems such as SAP Enterprise Resource Planning (SAP ERP) and SAP
Advanced Planning Optimization (SAP APO). IBP collects and then returns data to the
operational systems.
Data export from SAP Integrated Business Planning can be performed using the following
options:
API via a RESTful web service
Export of key figure data and master data using HCI
The target audience of this guide is IT specialists who do ETL (extract, transform, load) on
databases. This guide shows how to extract key figure data from an IBP system primarily to
transform and export the data to another target system.
1 Data Export Using the API
The API enables any ETL tool which can call a RESTful web service to export key figure data
from the application so that the data can be consumed by ETL tools (such as data services),
integration applications (such as SAP NetWeaver Process Integration) or directly from
operational systems.
The following figure illustrates how data export using the API works:
RESTful Web Service
getDataExport
Consumer
getDataExport result
IBP SAP HANA
Database
1. The consumer calls the API (getDataExport).
2. The RESTful web service queries the IBP HANA database
3. The RESTful web service returns the results to the consumer.
Data Export from SAP Integrated Business Planning 5.0
5
SAP Library
1.1 API Reference
Prerequisites
The getDataExport API uses a RESTful web service. Requests are formatted as JavaScript
Object Notation (JSON), and responses can be formatted as JSON or CSV. This section
explains the parameters for exporting key figure data using the API.
Prerequisites:
The source planning area in IBP from which data is extracted or exported must be
available.
To authenticate and invoke this RESTful service, you must have authorization to view
all planning data for the source planning area; that is, a visibility filter for the specific
planning area, with no filter rules defined, must be assigned to your user.
Basic authentication is required for the RESTful web service
Resource URI
POST <http or https>://<server URI>/sap/sop/sopfnd/services/analytics
For example: https://mydomain.com/sap/sop/services/analytics
>>>
MISSING TARGET TEXT FOR TEXT-ID: 'DITA_note' (language: en) <<<
There is only a POST because GET functionality is included in the POST request.
Data Export from SAP Integrated Business Planning 5.0
6
SAP Library
Sample Request Syntax
{
"ACTION":"getDataExport",
"plarea":"<planning_area_id>",
"select":"<attributes>,<key_figures>",
"filter":"IN(<time_attribute>,'<start_time>','<end_time>')"
"contenttype":<output_format>
}
Sample Request
In this example, a user obtains key figure data for Sales Forecast Quantity and Marketing
Forecast Quantity between July 1st, 2013 and September 30th, 2013. The user wants to
obtain the customer ID, product ID, period ID, sales forecast quantity, and marketing forecast
quantity for all entries in the x Phone product family in the SAP1 planning area.
{
"ACTION":"getDataExport",
"plarea":"SAP1",
"select":"SM1CUSTID,SM1PRDID,PERIODID0,SALESFORECASTQTY,MARKETINGFORE
CASTQTY",
"orderby":"SM1CUSTID,SM1PRDID,PERIODID0",
"filter":"IN(SM1PRDFAMILY,'x Phone')ANDBT(PERIODID0,'2013-07-01
00:00:00.0','2013-09-30 23:59:59.999')"
}
Parameters for getDataExport
Parameter
Description
action
This is always set to getDataExport. This parameter is required.
plarea
The Planning Area ID, for example, SAP1. This parameter is required.
select
You can put attributes here, such as customer name, product ID, and so on.
In this section, you must add key figures such as sales forecast quantity and
marketing forecast quantity, otherwise you will get an empty response.
filter
You can add filters.
Optionally, you can obtain key figure data from a non-default version. See
Specifying a Version for Obtaining Key Figure Data for an example. A time
attribute with start and end times must be specified in one of the following
ways:
contenttype
YYYY-MM-DD hh:mm:ss[.nnn], for example, 2013-07-01
00:00:00.0.
Use a time_profile_id, for example, 1234. See Specifying Output in
CSV Format for an example.
JSON is the default response format. You can specify output in CSV format by
adding the following code: "contenttype":"csv. See Specifying Output in
CSV Format for an example
Sample Response
This is a portion of the full response:
[{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1055","PERIOD_FROM":"2013-07-01
Data Export from SAP Integrated Business Planning 5.0
7
SAP Library
00:00:00.0000000","PERIOD_TO":"2013-07-31
23:59:59.9990000","SALESFORECASTQTY":"1234.136843","MARKETINGFORECAST
QTY":"1221"}
{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1056","PERIOD_FROM":"2013-08-01
00:00:00.0000000","PERIOD_TO":"2013-08-31
23:59:59.9990000","SALESFORECASTQTY":"1219.5","MARKETINGFORECASTQTY":
"1219.5"}
{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1057","PERIOD_FROM":"2013-09-01
00:00:00.0000000","PERIOD_TO":"2013-09-30
23:59:59.9990000","SALESFORECASTQTY":"1279.643158","MARKETINGFORECAST
QTY":"1279.63"}
{"SM1CUSTID":"102","SM1PRDID":"x102
Phone","PERIODID0":"1055","PERIOD_FROM":"2013-07-01
00:00:00.0000000","PERIOD_TO":"2013-07-31
23:59:59.9990000","SALESFORECASTQTY":"1234.136842","MARKETINGFORECAST
QTY":"1221"}]
An empty response looks like this:
[]
If the request contains a syntax error, the response contains an informative error message for
troubleshooting the error.
1.1.1 API Examples
The following examples show different ways of using the API.
Accepted content types: application/json, text/javascript, */*; q=0.01.
1.1.1.1 Using Time Period IDs Instead of Date and
Time
Overview
You can use time period IDs for filtering instead of datetime entries.
In the filter line, the datetime entries are replaced with period IDs (1057, 1058).
Sample Request
Notice that only the filter has changed.
{
"ACTION":"getDataExport",
"plarea":"SAP1",
"select":"SM1CUSTID,SM1PRDID,PERIODID0,SALESFORECASTQTY,MARKETINGFORE
CASTQTY",
"orderby":"SM1CUSTID,SM1PRDID,PERIODID0",
"filter":"IN(SM1PRDFAMILY,'x
Phone')ANDIN(PERIODID0,'1057','1058')"
}
Sample Response
This is a portion of the full response:
[{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1057","PERIOD_FROM":"2013-09-01
00:00:00.0000000","PERIOD_TO":"2013-09-30
Data Export from SAP Integrated Business Planning 5.0
8
SAP Library
23:59:59.9990000","SALESFORECASTQTY":"1279.643158","MARKETINGFORECAST
QTY":"1279.63"}
{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1058","PERIOD_FROM":"2013-10-01
00:00:00.0000000","PERIOD_TO":"2013-10-31
23:59:59.9990000","SALESFORECASTQTY":"1219.5","MARKETINGFORECASTQTY":
"1219.5"}
{"SM1CUSTID":"102","SM1PRDID":"x102
Phone","PERIODID0":"1057","PERIOD_FROM":"2013-09-01
00:00:00.0000000","PERIOD_TO":"2013-09-30
23:59:59.9990000","SALESFORECASTQTY":"1279.643158","MARKETINGFORECAST
QTY":"1279.63"}]
1.1.1.2 Specifying a Version for Obtaining Key Figure
Data
Overview
You can specify which version you want in the filter.
If a version is not specified, the default one will be chosen. If you want to specify a version,
add the following code to your filter:
ANDIN(<scenario_attribute>, '<scenario_id>')
In the following example, the DOWNSIDE version is specified:
ANDIN(SCNID,'DOWNSIDE')
Sample Request
The key figure has been changed to one that exists in this version (ACTUALSQTY):
{
"ACTION":"getDataExport",
"plarea":"SAP1",
"select":"SM1CUSTID,SM1PRDID,PERIODID0,ACTUALSQTY",
"orderby":"SM1CUSTID,SM1PRDID,PERIODID0",
"filter":"IN(SM1PRDFAMILY,'x Phone')ANDBT(PERIODID0,'2013-07-01
00:00:00.0','2013-09-30 23:59:59.999')ANDIN(SCNID,'DOWNSIDE')"
}
Sample Response
This is a portion of the full response:
[{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1055","PERIOD_FROM":"2013-07-01
00:00:00.0000000","PERIOD_TO":"2013-07-31
23:59:59.9990000","ACTUALSQTY":"0"}
{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1056","PERIOD_FROM":"2013-08-01
00:00:00.0000000","PERIOD_TO":"2013-08-31
23:59:59.9990000","ACTUALSQTY":"0"}
{"SM1CUSTID":"101","SM1PRDID":"x107
Phone","PERIODID0":"1057","PERIOD_FROM":"2013-09-01
00:00:00.0000000","PERIOD_TO":"2013-09-30
23:59:59.9990000","ACTUALSQTY":"0"}]
1.1.1.3 Specifying Output in CSV Format
Overview
You can obtain the response in CSV format instead of JSON. By default, the response will be
in JSON format. You can receive responses in CSV format by adding the following code:
"contenttype":"csv"
Data Export from SAP Integrated Business Planning 5.0
9
SAP Library
Sample Request
Notice the addition of content type at the end of the code:
{
"ACTION":"getDataExport",
"plarea":"SAP1",
"select":"SM1CUSTID,SM1PRDID,PERIODID0,SALESFORECASTQTY,MARKETINGFORE
CASTQTY",
"orderby":"SM1CUSTID,SM1PRDID,PERIODID0",
"filter":"IN(SM1PRDFAMILY,'x Phone')ANDBT(PERIODID0,'2013-07-01
00:00:00.0','2013-09-30 23:59:59.999')",
"contenttype":"csv"
}
Sample Response
This is a portion of the full response:
SM1CUSTID,SM1PRDID,PERIODID0,PERIOD_FROM,PERIOD_TO,SALESFORECASTQTY,M
ARKETINGFORECASTQTY
101,x107 Phone,1055,2013-07-01 00:00:00.0000000,2013-07-31
23:59:59.9990000,1234.136843,1221
101,x107 Phone,1056,2013-08-01 00:00:00.0000000,2013-08-31
23:59:59.9990000,1219.5,1219.5
101,x107 Phone,1057,2013-09-01 00:00:00.0000000,2013-09-30
23:59:59.9990000,1279.643158,1279.63
2 Data Export Using HCI
You can use SAP HANA Cloud Integration for data services (HCI) to export key figure data
and master data from an IBP application to an on-premise system.
For key figure data, you use a calculation scenario. A calculation scenario is a special
construct in the SAP HANA database that HCI can use to read data from an IBP application
and to write to a file, or to write the data directly to the target on-premise system using a web
service call. Calculation scenarios are generated in the planning area of the IBP application.
Prerequisites
The source planning area must be available (and, for key figure data export, must
contain a generated calculation scenario).
You must have authorization to view all planning data for the source planning area;
that is, a visibility filter for the specific planning area, with no filter rules defined, must
be assigned to your user.
Make sure your HCI environment is setup correctly, with a connection to IBP and an
on-premise agent. For more information, see the Help Center for SAP HANA Cloud
Integration. (When logged into the HCI web application, you can access the help
center using the Help link on the right of the page.)
A datastore must exist for each of the following:
o
The IBP application for the source
o
A file format group or SOAP web service for the target
2.1 Configuring Data Export in HCI
In HCI, you configure data export within a project. In a project, you create a task that specifies
the following:
Data Export from SAP Integrated Business Planning 5.0
10
SAP Library
Source datastore
This datastore is the IBP application, for example sales and operations, in which
there exists a calculation scenario or master data tables
Target datastore
This datastore is a file format group or a web service.
Data flow
The data flow defines how data is extracted from the source datastore, transformed,
and loaded to the target datastore. When creating the data flow, it is necessary to add
the calculation scenario or master data tables as the source datastore using the
Source Table object as the first object in the data flow. In the data flow, you specify
transforms. A transform is a step in a data flow that acts on a data set. A data flow
may contain one or more transforms. Within a transform, you can add filters to filter or
restrict data.
>>>
MISSING TARGET TEXT FOR TEXT-ID: 'DITA_note' (language: en) <<<
It is not necessary to use global variables, a preload script or a postload script.
For more information about how to set up projects, datastores, tasks, data flows and
transforms, see the Help Center for SAP HANA Cloud Integration.
2.1.1 Export Data from IBP to a File on an On-Premise
Data Export from SAP Integrated Business Planning 5.0
11
SAP Library
System
You export data to a file using the following sequence in HCI:
1. Create a target datastore of type File Format
A file format is a set of properties that describes the metadata structure of a flat data
file. File formats allow the software to access flat data files on an SAP Data Services
Agent host system, and read from or write to those files while the software executes a
task.
2. Create a source datastore of type SAP Business Suite Applications
o
For export of key figure data using a calculation scenario: Specify the IBP
application in which there exists a calculation scenario.
o
For export of master data: Specify the IBP application that has the IBP
master data types, for example, PRODUCT or CUSTOMER.
3. Create a task
You create a task within a project. In the task, you create a data flow. A data flow
defines how data is extracted from its source, transformed, and loaded to a target.
4. Define transforms
When exporting to a file format, you must define a Query transform and a Target
Query transform. You define the transforms within the data flow.
Data Export from SAP Integrated Business Planning 5.0
12
SAP Library
>>> MISSING TARGET TEXT FOR TEXT-ID: 'DITA_note' (language: en) <<<
It is necessary to define two transforms - simply mapping from a calculation scenario
or the master data to a target file does not work.
5. Run the task.
2.1.1.1 Procedure for Exporting Data to a File
To export data from IBP to a file in HCI, use the following steps:
Create a File Format
1. Choose DATASTORES and create a new datastore of type File Format Group. Or,
select an existing datastore of type File Format Group.
2. In the File Formats tab, choose Create File Format and then choose Create from
Scratch.
3. In the Create File Format dialog box, enter details for the file format and choose OK.
4. Select the file format and choose the Columns tab.
5. Specify details of the columns by entering the Name, Data Type and Length for each
column.
Data Export from SAP Integrated Business Planning 5.0
13
SAP Library
Create a Task
1. Choose PROJECTS.
2. Select an existing project, or create a new project first.
3. Choose Create Task and deselect Use Template to create the task from scratch.
4. Enter a name and description for the task, choose Next.
5. Select a source datastore (your IBP datastore) and choose Next.
6. Select a target datastore. Choose the file format group that contains the file format
you created above.
7. Choose Save and then Save and Define Data Flow.
8. Choose Add Target Object.
9. Select the file format that you created previously as the target file and choose Create
Data Flow.
10. Enter a name for the data flow, the name of the CSV file and choose Next.
Define Transforms
You must define two transforms for the dataflow.
>>>
MISSING TARGET TEXT FOR TEXT-ID: 'DITA_note' (language: en) <<<
Data Export from SAP Integrated Business Planning 5.0
14
SAP Library
Simply mapping from a calculation scenario or the master data to the target file (in a Target
Query transform) does not work. The task will fail with an error.
1. Add the source (calculation context/master data) by dragging and dropping it onto the
canvas.
2. Add a Query transform by dragging and dropping it onto the canvas and connect it to
the source object.
3. Double-click the Query transform and use drag and drop to add fields to the Output
list.
4. Optionally define a filter.
5. Close the Query.
6. On the canvas, connect the Query with the Target Query object.
7. Double-click the Target Query object and connect the fields to the Output fields which
represent your target file.
8. Close the Target Query.
9. Review the dataflow and choose Done.
Run the Task
1. In the Projects pane, select the task that you defined and choose Run Now.
2. In the Run Task Now dialog box, choose OK.
Once the task has run successfully, navigate to the location you designated for the target
datastore and drill down to the relevant file. You can find this location in the Root Directory
field of the configuration details of the File Format Group datastore.
2.1.2 Export Data from IBP and Interface Directly to an
On-Premise System via a Web Service
You export data from IBP and interface directly to an On-Premise system via a web service
using the following sequence in HCI. Note: In the following steps, SAP ECC is used as the
target system:
1. In the SAP ECC system:
o
Set up a web service-enabled BAPI in the source system
2. Create a source datastore of type SAP Business Suite Applications
For export of key figure data using a calculation scenario: Specify the IBP application
in which there exists a calculation scenario.
For export of master data: Specify the IBP application that has the IBP master data
types, for example, PRODUCT or CUSTOMER.
3. In HCI:
1. Create a target datastore of type Web Service
A web service datastore represents a connection from HCI to an external
web service-based data source. You need the WSDL (definition of the web
service) and a username and password to connect to the web service.
Data Export from SAP Integrated Business Planning 5.0
15
SAP Library
2. Create a task
You create a task within a project. In the task, you create a data flow. A data
flow defines how data is extracted from its source, transformed, and loaded
to a target.
3. Define transforms
When exporting to a web service, you must define a Query transform, an
XML Map transform and a Web Service transform.
4. Run the task.
2.1.2.1 Procedure for Exporting Data via a Web
Service
To export data from IBP and interface directly to an on-premise system via a web service, use
the following steps:
Configure the Web Service-Enabled BAPI
In the SAP ECC system, set up a web service-enabled BAPI in the SAP ECC system
using transaction WSADMIN. This generates the web service’s WSDL/URL. You
subsequently use this URL in the datastore definition in HCI.
Create a Target Datastore
1. Choose DATASTORES and then choose New Datastore.
2. In the New Datastore section, enter a name and in the Type field, select SOAP Web
Service.
3. In the Web Service Options section, enter the WSDL path - this is the URL generated
by the web service-enabled BAPI above.
4. In the Credentials section, enter the following:
o
User name
Enter the user name on the SAP system.
o
Password
Enter the password for the user name on the SAP system.
o
WSS Password
Enter the same password as in the Password field.
5. Choose Save.
6. In the Tables tab, choose Import Objects.
7. Select the checkbox next to the web service-enabled BAPI as defined in the WDSL
and choose Import.
>>> MISSING TARGET TEXT FOR TEXT-ID: 'DITA_note' (language: en) <<<
Data Export from SAP Integrated Business Planning 5.0
16
SAP Library
You need one datastore for each web service enabled BAPI that you use.
Create a Task
1. Choose PROJECTS.
2. Select and existing project, or create a new project first.
3. Choose Create Task and deselect Use Template to create the task from scratch.
4. Enter a name and description for the task, choose Next.
5. Select the source datastore (the calculation scenario or master data tables in the SAP
HANA database for the IBP application) and choose Next.
6. Select the target datastore (the web service, from the web service enabled BAPI, that
you created above).
7. Choose Save and then Save and Define Data Flow.
8. Choose Add Target Object.
9. Select the web service that you created previously as the target and choose Create
Data Flow.
10. Select the web service function as the target obejct, and choose OK.
11. Enter the name of the data flow, and optionally a description. The data flow is
displayed. The initial data flow consists of Target_Web_Service_Call connected to
outputLoader.
Define Transforms
1. Add a Source Table by dragging and dropping it onto the canvas and select the
calculation scenario or master data tables.
2. Add a Query transform by dragging and dropping it onto the canvas.
3. Add an XML Map transform by dragging and dropping it onto the canvas.
4. Connect the source, transforms and target as follows:
Source Table (calculation scenario/master data) -> Query (transform) -> XML Map
(XML transform) -> Target_Web_Service_Call (web service) -> outputLoader
5. In the Query transform, map columns as required from the input (calculation
scenario/master data) to the output. Optionally, enter required filters.
6. In the XML Map transform, choose Generate Schema and then choose Generate
schema from web service function to create a nested schema for the web service call.
Map columns from the input generated schema as required.
7. Generate an iteration rule. Select the name of the transform, choose the Iteration
Rule tab, and choose Propose rule.
Data Export from SAP Integrated Business Planning 5.0
17
SAP Library
>>> MISSING TARGET TEXT FOR TEXT-ID: 'DITA_note'
(language: en) <<<
Once the rule is proposed, HCI automatically uses it unless you edit it.
8. In the Target Web Services Call transform, edit the data flow:
1. Map only the input top level node to the output top level node.
2. In the Transform Details section, choose the Web Service Response tab,
ensure that the response file is created in a directory (specified in the
Response File Location field) that was specified during the agent
configuration. This makes it easy to find on the agent. The response file is an
XML file that captures the successful status for BAPI calls, or shows any
errors.
9. Choose Next and then Go To Review.
10. Review the dataflow and then run the task. Choose Done, and then Done.
Run the Task
1. In the Projects pane, select the task that you defined and choose Run Now.
2. In the Run Task Now dialog box, choose OK.
Data Export from SAP Integrated Business Planning 5.0
18
© Copyright 2024