Exporting / Importing Planning Areas in S&OP using SQL
Sales & Operations planning solution is a flexible metadata driven solution, which can be configured in multiple ways depending on the customer’s business needs. At the heart of this application lies the unified planning model (aka planning area) which lets planners, executives run various scenarios to arrive at rightful decisions based on data. Technically, a Planning Area describes the structure of an S&OP plan, and is defined by a set of configuration elements that include Attributes, Dimensions, Time Profile, Planning Levels, Key Figures and Scenarios.
Sometimes, we see that there is a need to copy an existing model configuration across systems. For example, for presales activities, we might want to create a template model and then have multiple versions of these created for configuring different demos across systems. This allows changes to Key Figures or formulas to be made without any impact on the original Planning Area.
This article describes a way of doing this type of copy.
Note that this type of operation is not without risk. It requires the execution of SQL code in SAP HANA Studio or in SAP HANA XS IDE and should be performed by an advanced S&OP consultant. The systems should be backed up properly before proceeding. This article is given as an example, and you are using it at your own risk. SAP and myself shall not be held liable for any damage resulting from its use. This should not be used to IMPORT anything into a live production system without first getting approval from SAP S&OP Development
In this example, we have a Planning Area named SAP2 in server A. All the Master Data objects used in that Planning Area have the prefix S2. and it uses the Time Profile “1”
The goal is to have a copy of SAP2 named SAP2Z in Server B and that uses a copy of the Master Data objects with the prefix S2Z, and Time Profile “4”. Of course feel free to pick whatever names you want.
First, we open a web browser (preferably Google Chrome) and login to S&OP on Server A (source server) with an administrator account, such as SOPADMIN.
Then, we open a new tab in the same browser instance and copy and paste this URL:
<serverA:port> points to the S&OP server A
SAPMODEL1 and SM1 are the name and prefix of the source Planning Area
SAPMODEL2 and SM2 are the name and prefix of the new Planning Area to be created
X is the ID of the new Time Profile (Integer)
so in our case:
We connect to that URL and the browser displays some SQL code
This is the SQL code that we need to run in HANA Studio. It will construct an inactive version of the Planning Area in the target S&OP server (Server B).
We right-click on the code and select “View Page Source”. This displays the code in a much nicer format.
We copy and paste that code into a text editor such as Notepad++.
If there are more than 500 lines of SQL code, it is recommended to break it down in blocks of 400-500 lines.
We open SAP HANA Studio and connect it to the S&OP server B
(destination server), login as an administrator (SOPADMIN for example).
We open the SQL editor and paste and run each block of code in the proper order.
At one point we have a few errors.
It seems there’s a conflict in the table mentioned in the error messages, and that some values need to be unique.
Here are the lines of code that are causing the errors:
Here is the content of that table:
If we look at the values in that table, it appears that PLOBJTYPE and UPDATERULE have unique values.
Now let’s look at the code that caused the problem: We are inserting 3 more records. The new values for PLOBJTYPE are different from those already in the table, so no problem there. However the values for UPDATERULE are 2. 3 and 4, which are already in the table
We copy and paste those 3 lines of code into a new SQL editor window, and change the 3 UPDATERULE values to 11, 12 and 13 (based on the fact that the highest existing value was 10), and re-run that code.
We refresh the table.
Note: If this block of SQL code was not the last one, run any remaining code now
We now have a new Planning Area that is inactive
The planning area can now be activated in the following order:
- activate the time profile
- load time profile data
- activate master data
- activate compound master data
- activate planning area
- load master data
- load compound master data
- load key figure data