S&OP powered by HANA - Data Integration with Files

[Former Member]

In this blog I would like to share my experience of S&OP Data Integration. Instead of going complex integration options such as APO, CRM
any other external system, I have took  the simple looking safest option "Files". Help is also available for this... still always there is a new experience of our own :wink:

In  S&OP powered by HANA ( S&OP HANA) words used as a  "DATA IMPORT" to load a data files in S&OP HANA system.

Now lots of Questions in Mind...Where to start? Which Master data, Transaction data to be loaded? ...

At this point we will keep things simple and will not check how the data integration done with APO, ECC and All.

We will only concentrate on Target system i.e. S&OP HANA.

Decide first which master data we want to load in S&OP HANA. Do you have decided? yes, I have!!!

Before that can you check what are the master data available in S&OP HANA system? And How to check it.

See the tips of it as below to check the master data available in S&OP HANA System.

First Log on to New Web GUI of S&OP HANA system.( Hope you have link provided by SAP for Partners and you have valid user ID and related authorization to log on!!!!). After successfully log on... You can see below screen under configuration Tab for Master data .

Fig 1: S&OP HANA - Configuration - Master data

Check the Master Data Types in details:

Check the Master data Types as shown in Below screen.

Fig 2: S&OP HANA Configuration - How to Check Master Data Types Header details

Following Master Data are available in S&OP HANA

I have consolidated details of the master data types in below table, to understand it better.

Sr No	Master Data Type	Name of the Master Data	Description	Reference Master Data Types	Master Data Composed of
1	SM1BOM	Bill Of Materials	Bill Of Materials	Not Applicable	1)  Product (SM1PRODUCT) 2) Component (SM1COMPONENT)
2	SM1COMPONENT	Component	Component	SM1PRODUCT	Not Applicable
3	SM1CURRENCY	Currency	Currency	Not Applicable	Not Applicable
4	SM1CURRENCYTO	Currency To	Currency To	SM1CURRENCY	Not Applicable
5	SM1CUSTOMER	Customer	Customer	Not Applicable	Not Applicable
6	SM1CUSTOMERPRODUCT	Customer Product	Customer Product	Not Applicable	1) Customer (SM1CUSTOMER) 2) Product (SM1PRODUCT)
7	SM1EXCHANGERATE	Exchange Rates	Exchange Rates	Not Applicable	1) Currency (SM1CURRENCY) 2)  Currency To (SM1CURRENCYTO)
8	SM1LOCATION	Location	Location	Not Applicable	Not Applicable
9	SM1LOCATIONPRODUCT	Location Product	Location Product	Not Applicable	1) Location (SM1LOCATION) 2) Product (SM1PRODUCT)
10	SM1LOCATIONPRODUCTCURRENCY	Location Product Currency	Location Product Currency	Not Applicable	1) Product (SM1PRODUCT) 2) Currency (SM1CURRENCY) 3) Location (SM1LOCATON)
11	SM1PRODUCT	Product	Product	Not Applicable	Not Applicable
12	SM1PRODUCTCUSTOMERLOCATION	Product Customer Location	Product Customer Location	Not Applicable	Not Applicable
13	SM1RESOURCE	Resource	Resource	Not Applicable	Not Applicable
14	SM1RESOURCELOCATION	Resource Location	Resource Location	Not Applicable	1) Resource (SM1RESOURCE) 2) Location (SM1LOCATION)
15	SM1RESOURCELOCATIONCURRENCY	Resource Location Currency	Resource Location Currency	Not Applicable	1) Currency (SM1CURRENCY) 2) Location (SM1LOCATION) 3) Resource (SM1RESOURCE)
16	SM1RESOURCELOCATIONPRODUCT	Resource Location Product	Resource Location Product	Not Applicable	1) Product (SM1PRODUCT) 2) Location (SM1LOCATION) 3) Resource (SM1RESOURCE)

Based on business requirements you can choose the master data to load in the S&OP HANA System.

You can also create new master data types if required.

Mostly, experienced people who has worked on SAP products, first start with the comparison  what is available in ECC, APO, CRM, SRM how the data
to be integrated with S&OP HANA etc.

We will take a baby step to move on.

We will take 2 master data types  to load into the S&OP HANA System, one as “PRODUCT”  and second one “BoM” as an example.

1. PRODUCT.

Prerequisite:  To load a master data we have to create 2 files first,  one as a .CSV and one manifest and then to Zip it to load into the system.

These files has it’s own structure. Must have to follow it.

a) .CSV file

As we have to create the .CSV file for Master Data – Product, check all the attributes required for Product, which attributes are mandatory.  Mandatory attributes should be a part of .CSV files.

How to check the master data attributes?

Tips of it as below:

Log on to New WBE UI of S&OP HANA and see the master data types Product details as shown in below screen shot.

Fig 3: S&OP HANA Master Data Type - Product Details

Make the Excel sheet of all the Attributes and Highlight  the mandatory/Required attributes with Bold or any color and save it as a .CSV file. Which you can use as a Template for future.  e.g. of it is as below:

Fig 4: Consolidation of Master data Type product's Attributes

Copy data in .CSV file from Row 2 and save. 1 st line to be treated as remarks which we have written to understand the attributes.

For Product master data, SM1PRDBU (Product BU), SM1PRDFAMILY (Product Family), SM1PRDID (Product ID), SM1PRDSUBFAMILY (Product Sub-Family) are required attributes and It should be a part of the columns in the .CSV file.

SM1PRDID is a Key attributes.

While preparing the .CSV file use the Technical names of the attributes for a column heading   e.g. SM1PRDID,
SM1PRDBU etc. as your master data attributes are going to stored under these name in the system.

Fig 5: Sample template of Master Data Type - Product

I have saved my file with the name  ZPR01Product as a CSV file

This name we have to use in the manifest file.

b) Manifest file

Now we will see the details of the manifest file.

Below is the structure of the manifest file which we can use as a sample.

Fig 6: sample Manifest file for Master Data Type - Product

Next task is to zip the file and be ready to load the master data in S&OP HANA System.

Zipping the file is also a skill !!!

I was zipping the folder in which my files was stored and got the error while loading.... Big learning for me!!!

The correct way to zip the files are select both the files and send it to compressed (zipped folder) as shown below:

Fig 7: How to Zip the files to make ready for DATA IMPORT

And your folder is having now Zipped file which you have to select to load while doing "DATA IMPORT"

Fig: 8 ready use Zipped file for Data Import

Now you are ready to Load the Master Data in S&OP HANA.

Logon to Old WBE UI of S&OP HANA

Go to Data Integration -->  DATA IMPORT -->  Select the file to be loaded

Fig 9: S&OP HANA  Old Web UI - Data Integration

After selecting the file you will get below message:

Fig 10: Sample Message received after Data Import file upload

Wait for  few minutes ... Yes few minutes as it run as a batch job in the background to load to import the data and then see the reporting.

DATA INTEGRATION --> REPORTING

Fig 11: Data Import reporting

I am now more curious on the rejection of the Records than to see the full report.

Selecting 2^nd option

Fig 12: Data Import Reporting - Rejection of the records

Happy to see ' NO'  records as rejected one. ( Good Job Done till Now!!!). Well Done....

Master data product loaded successfully!!!

Download full Report and see the details of successfully loaded data

Fig 13: Data Import Reporting - Full Report

Now we will see our next Master Data Type - Bill of Materials (BoM) which is bit complex.

2. BOM – Bill of Materials

Bill of Materials master data loading is bit complex than the Product.

Why? Why it is bit complex?

Loading is not complex but the concept of Bill Of Materials and its related component has to be understand first.

See the details of the Master data Type Header details and check the attribute structure of it.

As mentioned in the beginning of this blog BOM has ‘ composed of’  Product and Component.

Sr No	Master Data type	Name of the Master Data Type	Description of the Master Data Type	Reference Master Data Type	Master Data Type 'Composed Of'
1	SM1BOM	Bill Of Materials	Bill Of Materials	Not Applicable	1) Product (SM1PRODUCT) 2) Component (SM1COMPONENT)

My learning points: Whenever any master data type has Reference master data Type and Composed of any Master data then it is a prerequisite to first load the Reference master data and  Composed of Master data types.

Sr. No	Master Data Type	Name of the Master Data Type	Description of the Master Data Type	Reference Master Data Type	Master Data Type 'Composed of '
1	SM1BOM	Bill Of Materials	Bill Of Materials	Not Applicable	1) Product (SM1PRODUCT) 2) Component (SM1COMPONENT)
2	SM1COMPONENT	Component	Component	SM1PRODUCT	Not Applicable

For BOM,  Product is one of the master data and it loaded successfully. Second part of the master data type of BOM is Component.

First we will see how Component is treated in S&OP HANA. And this master data type required to  load or not before BoM load.

Fig 14: Details of Master Data Type - Component

Component – SM1COMPONENT has Reference master data type. i.e. Component is to be loaded as a Master data type as Product.

Let us see the attributes of the Component.  Component’s attributes are shown below.

Fig 15: Consolidation of Component's Attributes done in Excel Sheet

If you try to load Component you will get the error with rejection as 'CSV file data structure violation'

• For file Transfer/scanned/f0442746-6c6b-48b5-a8f0-db43337d0e14/ZPR01Componenet.csv in batch ID f286c4a137e0499d8d53b27eaab1c4a8, check the structure of the .csv file.  Reason: Object SM1COMPONENT is unknown

My Learning point: Load Component as a product and Based on the internal technical structure system will map SM1PRDID to SM1CMPID.

For BOM both the Attributes SM1PRDID and SM1CMPID is mandatory.

After understanding this concept it is now simple to load the BOM!!!

Now we will see the BOM’s attributes to prepare the Template.

Fig 16: BoM's  Attribute consolidated in Excel Sheet

And our template will looks like as below

Fig 17: Sample Template of Master Data type - BoM as a Excel Sheet.

Now will see the details of the .CSV file which we want to load for BoM.

Fig 18: Sample template of ready to use .CSV file for Master data type - BoM

And manifest file is as below:

Fig 19: Sample Manifest file for Master Data Type - BoM

Zip these 2 files and prepare the .ZIP file to load a BoM - Data Import in S&OP HANA System using Web UI.

Follow the same steps to load the Data in S&OP HANA with Old Web UI.

Loaded BoM master data Successfully !!!

In Excel Add-In, Now you can see the BoM Child –Parent relationship using Template BoM Child-Parent as below.

Fig 20: Sample S&OP HANA Excel Add-IN - BoM Child parent relationship details with the help of SAP provided Template

Similar way you can load the transaction data e.g. Actual Qty

Summary of the learning points:

• Better first understand the Master data Type’s  attributes – Key, Attributes, Reference Master
  data type, Reference Attributes of S&OP HANA
• Structure of the Manifest file – Object Name, Time stamp format, planning area, Action
• Zip the files and not the folder
• Do the proper analysis of the error and understand it to take the corrective action
• Data load done for Planning Area but it is available for all where the Object is used.

Unanswered Query of mine: How to restrict the data at Planning Area level?