SAP HANA Cloud Integration Document Version: Template Guide for SAP Sales and Operations Planning

(1)

SAP HANA Cloud Integration

Document Version: 1.0 - 2012-12-22

(2)

1 About the templates

SAP HANA Cloud Integration templates for SAP Sales and Operations Planning provide predefined content which serves as the starting point for populating your Sales and Operations Planning (S&OP) application.

The templates are designed to meet the specific requirements for S&OP data and reduce the time needed to get up and running with the application.

Tasks created from the templates contain the data flows necessary to do the following: ● Extract data from SAP Applications sources (ERP and SCM/APO).

● Transform it as required for Sales and Operations Planning.

● Load the data to predefined target tables for the default model (SAPMODEL1) in the staging area of the SAP HANA application cloud.

Task elements

Each task contains the elements described in the following table:

Element Description

Source datastore and tables Connects SAP HANA Cloud Integration and the SAP Applications

source.

Target datastore and tables Connects SAP HANA Cloud Integration and the S&OP application target.

Data flow Defines the movement and transformation of data from source to

target.

Global variables Parameterize the task.

Preload script Initializes global variables required for task run.

Postload script Records the timestamp of the most recent run.

Advantages to using templates

Because each S&OP implementation is unique, you will almost always need to customize the out-of-the-box tasks and data flows that you create from the S&OP templates. However, using the templates to create tasks and data flows as a starting point has the following advantages over creating tasks from scratch:

● Template tasks provide the framework and thus reduce the integration development time.

● Template tasks contain the global variables required to successfully load and process data into S&OP. ● Preload scripts are configured with default values for these global variables and provide a framework. This

eliminates the need to write preload scripts from scratch.

Template Guide for SAP Sales and Operations Planning

(4)

● Template data flows follow best practice design for optimal integration performance.

● Template data flows contain mappings from ERP and APO source systems which include joins and predefined filters.

● Template data flows contain predefined transformations of ERP and APO data.

(5)

2 Sales and Operations Planning

templates

2.1 Master data templates

Tasks created from master data templates load data to staging tables.

Tasks created from master data templates must be run prior to tasks created from key figure templates. Information about master data tasks is summarized in the table below.

Load order Task name SAP

Applications source

Source tables or

extractors S&OP target table

1 SOP_MD_LocationM aster ERP 0PLANT_ATTR T001W SOPMD_STAG_SM1LOCA TION 2 SOP_MD_ProductM aster ERP 0MATERIAL_ATTR MAKT SOPMD_STAG_SM1PROD UCT 3 SOP_MD_LocationPr od ERP MBEW MARA MARC SOPMD_STAG_SM1LOCA TIONPRODUCT Must be loaded prior to key figure tasks SOP_MD_Customer Master ERP 0CUSTOMER_ATTR KNVP KNVH SOPMD_STAG_SM1CUST OMER Must be loaded prior to key figure tasks

SOP_MD_Resource SCM /SAPAPO/RES_HEAD SOPMD_STAG_SM1RESO

URCE

2.2 Key figure templates

Tasks created from key figure templates load data to the SOPDD_STAGING_KFTAB_SM1BASESAPMODEL1 staging table.

(6)

Key figure tasks require that associated master data is already loaded, processed and currently available in the S&OP application. If master data is not loaded into S&OP before key figure data, the key figure data will be loaded but will not be processed properly by S&OP. Information about key figure tasks is summarized in the table below.

Task name SAP Application source

Source tables/ extractors/BAPI functions

S&OP key figure columns

SOP_KF_Actuals ERP 2LIS_12_VCITM_SOP

VBRK VBRP

SM1ACTUALSQTY SM1ACTUALSREV

SOP_KF_Inventory ERP MARC

MARA MARD

SM1INITIALINVENTORYQTY SM1INVENTORYTARGETQTY

SOP_KF_OpenOrders ERP 2LIS_11_VAHDR

2LIS_11_VAITM 2LIS_11_VASTI SM1OPENORDERSQTY SM1OPENORDERSREV SOP_KF_SalesForecastPric e

ERP 2LIS_13_VDITM SM1SALESFORECASTPRICE

SOP_KF_CapacityLimit SCM/APO /SAPAPO/RES_HEAD

9ACAPACITY

SM1CAPASUPPLY

SOP_KF_Consumption SCM/APO BAPI_LOCSRVAPS_GETLI

ST2

BAPI_PDSSRVAPS_GETLI ST

SM1CAPACONSUMPTION*

SOP_KF_DemandPlanning SCM/APO 9ABPC_EXTRACT SM1DEMANDPLANNINGQTY

SOP_KF_StatisticalForecast SCM/APO 9ABPC_EXTRACT SM1SALESFORECASTQTY

Note: *SOP_KF_Consumption is implemented to extract from a sequence of BAPI function calls via a custom ABAP program. You can download the necessary ABAP program from SAP Note 1772288. Related Links

SAP Note 1772288

(7)

3 Loading data to S&OP

You load data to S&OP by running a group of tasks in a specific order.

Use the templates to create customized tasks to meet the requirements of your S&OP planning area.

Defining the tasks

Define the necessary tasks as described in the following high-level steps:

1. Determine the table and column naming conventions used by your S&OP planning area. 2. Create tasks using the S&OP templates.

You can either create the tasks individually or create multiple tasks at once. If you do the latter, you must perform the Create Multiple Tasks action separately for SAP_APO and SAP_ECC.

3. Within each task, modify the data flows to specify the target tables and columns used in your planning area. Change the target table in a data flow by copying it to a new data flow.

4. For each task, review the default values of the global variables and make changes as needed. Global variables apply to all data flows within a task.

Note: Certain global variables are used by the S&OP application to process the data after it is loaded. If the global variables are not included in the task, then data will be loaded, but S&OP cannot process it as expected. The required global variables are: $G_PLAN_AREA, $G_SCENARIO,

$G_TIME_PSROFILE, and $G_COLUMN_LIST.

For details on the development workflow, see the Get Started tab of the SAP HANA Cloud Integration Help.

Task execution order

Load master data first and then load key figure data. This order is required to ensure proper processing of key figure data.

Run the tasks in the following order: 1. SOP_MD_LocationMaster 2. SOP_MD_ProductMaster 3. SOP_MD_LocationProd 4. SOP_MD_CustomerMaster 5. SOP_MD_Resource

6. All key figure tasks (run order does not matter)

(8)

Note: The order in which you run the SOP_MD_CustomerMaster and SOP_MD_Resource tasks does not matter, as long as they are run prior to the key figure tasks.

3.1 Change the target table in a data flow

Change the target table in a data flow by copying it to a new data flow. The table must already exist in the target datastore.

To change the target table:

1. From the Projects tab, select the task that contains the data flow you want to change and then click Edit. 2. From the Data Flows tab, select the data flow that loads to the target table you want to change and then click

Copy to New Target Table.

3. Enter a name for the new data flow.

4. Select a target table and click Copy Data Flow.

The new data flow contains the same source information and transformations as the original data flow, but loads to the new target. Edit the mappings and column names or make other changes to meet your needs.

3.2 Set global variables

Global variables are symbolic placeholders. When a task runs, these placeholders are populated with values. This allows users flexibility of run-time values used in extractions.

Note: Certain global variables are used by the S&OP application to process the data after it is loaded. If the global variables are not included in the task, then data will be loaded, but S&OP cannot process it as expected. The required global variables are: $G_PLAN_AREA, $G_SCENARIO, $G_TIME_PSROFILE, and $G_COLUMN_LIST.

Depending on your requirements and environment, allow the default values or set values in one of the following locations:

Option Description

Run Now dialog box Use for testing.

From the Projects tab, select a task. From the Actions menu, select Run Now. Execution Properties tab of

a task

Use for loading data.

From the Projects tab, select a task. From the Actions menu, select Edit. Click the

Execution Properties tab. Preload script Use for testing and loading data.

Update $G_COLUMN_LIST directly in the preload script.

(9)

Option Description

.

3.3 Preload scripts

Each of the Sales and Operations Planning (S&OP) tasks includes a preload initialization script.

If you do not specify values for global variables (symbolic placeholders), during task execution the preload script populates the global variables with default values.

Note: You must update the value of $G_COLUMN_LIST directly in the preload script when you customize your tasks. The value cannot be updated in the Global Variable dialog box.

3.4 Global Variables

3.4.1 Shared global variables

Shared global variables are included in all Sales and Operations Planning (S&OP) data integration tasks. Most tasks contain additional unique global variables.

Global variables contain default values which are used if the value has not been changed. As needed, change the value of each global variable per your requirements and environment. The default value and information about each of the shared global variables is shown in the following tables:

Global variable name

Data type Default value for master data task

Default value for key figure data task

Description

$G_LOAD_DATE datetime Sysdate() Sysdate() The load date of the

task.

$G_DEFAULT_TEXT varchar(1) ‘?’ ‘?’ Default text for

dimension default rows, foreign keys, and primary keys. Also used as a default for missing descriptions. $G_INITIAL_OR_DEL

TA

varchar(7) ‘INITIAL’ ‘INITIAL’ Identifies whether

the task is a first

(10)

Global variable

name Data type Default value for master data task Default value for key figure data task Description

(initial) load or a delta load.

$G_PLAN_AREA varchar(50) ‘’ ‘SAPMODEL1’ The Planning Area

value is defined in S&OP under

Configuration Model Planning Area Definition . The value must contain only uppercase alphanumeric characters and begin with a letter.

Spaces are not allowed.

$G_SCENARIO varchar(50) ‘’ ‘’ Indicates the base

scenario.

$G_TIME_PROFILE int -1 -1 The Time Profile ID

value is defined in S&OP under

Configuration Model Planning Area Definition .

$G_COLUMN_LIST varchar(5000) 'do not change' 'do not change' A list of all columns

loaded by the task. The format of the string must be: ‘TABLE.COLUMN1;T ABLE.COLUMN2‘

Note: This value must be set

(11)

Global variable

name Data type Default value for master data task Default value for key figure data task Description

directly in the preload script for each task. If you modify the task, ensure that this list includes all columns in the task.

3.4.2 Unique global variables

All Sales and Operations Planning (S&OP) data integration tasks contain shared global variables. Additionally, most tasks contain global variables which are unique to that task.

Global variables contain default values which are used if the value has not been changed. As needed, change the value of each global variable per your requirements and environment. The default value and information about each of the unique global variables is shown in the following tables.

Actuals (SOP_KF_Actuals)

Global variable name Data type Default value Description

$G_DELTA_DAYS int 730 Defines the number of

days of data to extract. Default for INITIAL load is 730 days (or 2 yrs). Default for DELTA load is 30 days. To change this value, update the preload script.

Capacity Limit (SOP_KF_CapacityLimit)

$G_SDATE datetime to_date(to_char( $G_LOA

D_DATE, 'yyyymm') || '01', 'yyyymmdd');

Set in the preload script as the first of the month in

(12)

which $G_LOAD_DATE occurs

$G_EDATE datetime to_date(to_char

add_months( $G_LOAD_D ATE, 18),'yyyymm') || '01','yyyymmdd');

Set in the preload script as the first of the month in which $G_LOAD_DATE +18 months occurs

Consumption (SOP_KF_Consumption)

Global variable Name Data type Default value Description

$G_LOGICAL_SYSTEM varchar(255) ‘APOCLNT800’ Logical System value

required to call BAPI functions

(BAPI_LOCSRVAPS_GETL IST2 and

BAPI_PDSSRVAPS_GETLI ST). Task will return no data if this value is not correct. Enter the correct logical system name for your SAP Applications system. For example, ‘SYSCLNT001’.

Customer Master (SOP_MD_CustomerMaster)

$G_PARVW varchar(255) 'AG' Use the value defined by

your Business Partner. Allows you to extract customer data for a single value of KNVP.PARVW. The default value used in the template is ‘AG’ for Sold-to party.

(13)

Inventory (SOP_KF_Inventory)

$G_MONTHS_FORECAST int 18 Number of months the

current inventory will be forecast. For example, 0 is the current month, 18 is 18 months in the future.

$G_MATERIAL_TYPE_1 varchar(4) ‘ROH' Extracts inventory data

from MARA.MTART for the specified material type.

$G_MATERIAL_TYPE _2 varchar(4) ‘HALB'

$G_MATERIAL_TYPE _3 varchar(4) ‘FERT'

$G_MATERIAL_TYPE _4 varchar(4) ‘HIBE'

$G_MATERIAL_TYPE _5 varchar(4) ‘FHMI'

Location Master (SOP_MD_LocationMaster)

$G_NODETYPE1 varchar(3) ‘DC’ Extracts location data for

the supply chain network from the specified nodes.

$G_NODETYPE2 varchar(3) ‘B’

$G_NODETYPE3 varchar(3) No default value

Location Product Master (SOP_MD_LocationProd)

$G_MATERIAL_TYPE _2 varchar(4) ‘HALB'

$G_MATERIAL_TYPE _3 varchar(4) ‘FERT'

$G_MATERIAL_TYPE _4 varchar(4) ‘HIBE'

(14)

$G_MATERIAL_TYPE _5 varchar(4) ‘FHMI'

Product Master (SOP_MD_ProductMaster)

$G_MATERIAL_TYPE_2 varchar(4) ‘HALB'

$G_MATERIAL_TYPE_3 varchar(4) ‘FERT'

$G_MATERIAL_TYPE_4 varchar(4) ‘HIBE'

$G_MATERIAL_TYPE_5 varchar(4) ‘FHMI'

Sales Forecast Price (SOP_KF_SalesForecastPrice)

$G_DAYS_FORECAST int 183 Number of days that the

initial forecast values are based on.

Default is 6 months (or 365/2).

$G_MONTHS_FORECAST int 18 Number of months

current sales price will be forecast. For example, 0 is the current month only, 18 is 18 months in the future.

(15)

www.sap.com/contactsap

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.

IBM, DB2, DB2 Universal Database, System ads, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/ 390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.

Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries. Oracle is a registered trademark of Oracle Corporation. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®_{, World Wide Web Consortium, Massachusetts} Institute of Technology.

Java is a registered trademark of Sun Microsystems, Inc. JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.

SAP HANA Cloud Integration Document Version: Template Guide for SAP Sales and Operations Planning