Skip to Content

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 😉

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- Configuration  Master Data Type.png

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 Configuration - How to check the Master data Types.png

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 - Configuration check  master Data type attributes.png

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 types - Products attributes.png

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- master Data type - Procduct - template.png

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- Manifest file structure for master data type product.png

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 2 files.png

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 to use Zipped file.png

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- SnOP HANA Old WUI Data Integration.png

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

After selecting the file you will get below message:

Fig 10- Message received after Data Import file selection.png

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.png

Fig 11: Data Import reporting

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

Selecting 2nd option

Fig 12- Data Import reporting - Rejection of Records.png

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.png

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- Master Data Types Component - Header.png

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 the details of Component attributes in Excel File.png

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- Consolidation of the details of BoM.png

Fig 16: BoM’s  Attribute consolidated in Excel Sheet

And our template will looks like as below
Fig 17- Master Data Types BOM - template.png

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 BoM .CSV file.png

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 BoM.png

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 Excel Add-In output from Template Child-Parent relationship.png

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?

To report this post you need to login first.

24 Comments

You must be Logged on to comment or reply to a post.

  1. Raghav Jandhyala

    Excellent Blog! thanks for sharing.

    Regarding your question – How to restrict the data at Planning Area level? We have functionality called Visibility filters using which you can restrict the data access for particular users.

    Thanks,

    Raghav

    (0) 
    1. Anjali Butley Post author

      Hi Raghav,

      In the S&OP Excel Add-in connection  I have selected Planning Area SAPMODEL1 and my expectation is that the master data which I have uploaded for Planning area ZPR01 should not be visible for SAPMODEL1.

      Some how it is visible for Planning area SAPMODEL1 also.

      If I want to work/see Masterdata, Transaction data which I have loaded for ZPR01 and execution of S&OP Process I should connect Planning area ZPR01.

      Is my understanding not correct?

      Regards,

      Anjali

      (0) 
      1. Raghav Jandhyala

        Hi Anjali,

        Master data can be shared across multiple planning areas. It depends on how you configured. You can create separate master data type to be exclusively used for your planning area

        Thanks,

        Raghav

        (0) 
  2. Anjali Butley Post author

    Note: While loading the data follow the sequence

    1) Time Profiles

    2) Planning Object – Master Data

    3) Key Figure ( Transaction Data) ,

    Load only one Key figure at a time (SAP S&OP HANA 3.0). If you have 5 Key figures data to be loaded then Load it one by one.

    If it is not loaded in sequence, your data loading will fail.

    (0) 
    1. Anjali Butley Post author

      Above Blog is based on the experience of OnDemand SAP Sales and Operations Planning 2.0 Powered by SAP HANA.

      In new version of S&OP 3.0 SP1 Patch 1, You can download the template directly to use.

      Ref latest Help for the same to know more.

      (0) 
  3. AleK .

    Useful blog post. But is possible to have others types of integration (like CIF) or file-integration is only one?

    thank you

    (0) 
    1. Anjali Butley Post author

      Hello AleK

      There are 4 types of Data Integrations supporting ETL

      1) SAP HANA Cloud Integration (HCI), to integrate with SAP Applications like ECC, APO etc. THis is for On-demand

      2)  SAP Data Services/ETL tool conectes from Customers  On-Premise systems to S&OP On-Demand Solution

      3) SFTP and HTTPS protocols

      4) S&OP UI data Integration with Excel .csv file ( which I have explained here)

      Hope this clarifies your query.

      As mentioned by Vivek, CIF is for APO to ECC integration only.

      (0) 
      1. Jitendra Soni

        Hello Anjali,

        We are planning to automate file processing between S&OP and ECC.

        Could you please suggest the best way with and without ETL tools.

        I think SFTP and HTTPS protocols should work for us. Could you please help on this.

        (0) 
  4. Barbara Benedek

    Hi Anjali,

    I created a model and I am trying to do data integration (upload a .csv file) to a master data which has an attribute with parent property. I have difficulties to do this. I have to say I don’t really understand how this parent-child concept works in S&OP. Could you please recommend a document from that I can learn how it works? Or could you show an example?

    Thank you very much in advance,

    Barbara

    (0) 
    1. Anjali Butley Post author

      Hello Barbara,

      example

      Product A is a parent

      Product B and C are child

      In S&OP system first create A, B, C as a product. Once it is done, using BoM object of S&OP create a parent-child relationship

      e.g. SM1PRDID is A, SM1CMPID will be B and C.

      Which version of S&OP are you using?

      (0) 
      1. Barbara Benedek

        Hi Anjali,

        Thank you for your quick answer, and sorry for my late reaction. We are currently using IBP 4.0 version.


        I’ve also e-mailed with Daniel Kim, who sent me a document which describes how to create a parent-child relationship. So now most of my questions are answered. 🙂


        Thank you for your help,

        Barbara

        (0) 
        1. Aditya Gupta

          Hi Barbara,

          We are currently on S&OP 3.0.1.3 and wish to upgrade to IBP 4.0. Can you share your experience on upgrading from S&OP Version 3.0 to IBP 4.0.

          Where can I locate the installation files?

          (0) 
          1. Barbara Benedek

            Hello,

            IBP 4.0 works fine for me, I had no special experiences. 🙂 I’ve downloaded it from this folder:

            \\uspalg01.pal.sap.corp\UX_Public\SOP\LastGoodInstaller

            I’d recommend to uninstall the previous version first.

            (0) 
    1. Vivek Sinkar

      The dependent demand is simply calculated by exploding the BOM.

      The key figure Outbound Production Demand stores the component demand.

      The key figure Component Usage stores the components used in the production process.

      (0) 

Leave a Reply