Introduction
The requirement to upload a CSV file into a SAP BW system is something a lot of people have come a across every once and a while. There the well-known solution by Marc Bernard is provided. In SAP Note 2053696 it is now stated that this How-to Paper is obsolete and should not be used anymore. Therefore, the new Planning Function type File Upload from AO, 0RSPL_FILE_UPLOAD_AO was introduced.
The aim of this blog is to show how a file upload with this Planning Function could look like and how this Planning Function works together with Analysis for Office. As stated in SAP Note 2666458 this solution is available under SAP BW 7.5 SP12 and Analysis for Office 2.7. The scenario shown here will be implemented in a SAP BW/4HANA.
Scenario
In this scenario I will show that a user is able to upload data into an ADSO with the following information:
◈ 0CALDAY Calendar Day
◈ 0CUSTOMER Customer
◈ 0MATERIAL Material
◈ 0DOC_CURRCY Document Currency
◈ 0DEL_VAL Delivered Sales Order Value
The CSV file will have the following information:
◉ Calendar Day
◉ Material
◉ Delivered Sales Order Value
The other fields will be filled in the Filter to show the functionality of the Planning Function 0RSPL_FILE_UPLOAD in combination with a Planning Sequence. Below is an example how the CSV file will look like:
Objects
To implement this scenario, I will create the following objects in the system in this order:
1. ADSO
2. HCPR
3. Aggregation Layer
4. Filter
5. Query
6. Planning Function
7. Planning Sequence
1. ADSO (ZTEIF01)
The first step is to create an ADSO where I can plan the data on. Therefore, I create an ADSO with the following settings:
With the Data Mart DataStore Object setting all characteristics are a key and the ADSO will behave like an InfoCube. I also enable planning functionalities by ticking on Planning-Enabled.
In the ADSO I add the following InfoObjects:
◉ 0CALDAY
◉ 0CUSTOMER
◉ 0MATERIAL
◉ 0DOC_CURRCY
◉ 0DEL_VAL
To enable planning, it is also necessary to set the ADSO from Staging (Hint: In SAP BW 7.5 this is called Load-Mode) to Planning this is done by right clicking on the ADSO and selecting Manage DataStore Object.
Then the BW/4 cockpit will open in a designated browser and you can change the setting from Staging to Planning.
2. CompositeProvider (ZTEAF01)
In the next step I create a CompositeProvider on top of the ADSO. The Scenario tab will show the following setup.
No additional settings are made.
3. Aggregation Level (ZTEIF02)
As a next step I create an Aggregation Level. The previously build CompositeProvider serves as an underlying InfoProvider. I will add the following fields to the Aggregation Level:
◉ 0CUSTOMER
◉ 0MATERIAL
◉ 0DOC_CURRCY
◉ 0DEL_VAL
◉ 0CALDAY
◉ 0INFOPROV
4. Filter (ZTEIF02_PLN_FIL01)
As already mentioned, I want to fill the fields 0CUSTOMER and 0DOC_CURRCY via a filter. I will also add the field 0INFOPROVIDER which was added in the Aggregation Level in the filter. Therefore, I create a filter on the Aggregation level and the filter will look like this:
Hint: It is also possible to create this Filter in SAP GUI in the transaction RSPLAN.
Please be aware that setting 0CUSTOMER and 0DOC_CURRCY to a fixed value will restrict me to upload data only for a specific customer and only a specific currency. I assume that in the most cases this would not make sense. I just use this to show how this Planning function works in combination with a filter and Planning sequence.
5. Query (ZTEIF02_PLN_QRY02)
The next step is to create a query on top of the Aggregation Level. I set the General query setting to Start the Query in Input Mode.
In the next step I add the filter I just created.
Finally, I add the Key Figure and remaining objects to the columns and rows. It is important to set the Key Figure Planning setting to Input-Ready.
I can now already open the query in Analysis for Office to check if manual planning would work:
So far, I can only manually enter figures in the query and save them. In the next step I finally create the Planning Function.
6. Planning Function (ZTEIF02_F01_PF02)
Now I Create a Planning function with the Function Type File Upload from AO (0RSPL_FILE_UPLOAD_AO).
Hint: You can either create it in SAP HANA studio or in the SAP GUI in the transaction RSPLAN. For a better overview I will show it in the SAP GUI.
This Planning function gives me now the following Parameters:
1. Separator: Here I can select the separator which is used in my CSV file. In my case a semicolon.
2. Field Delimiter Character: I can set the Field-Delimiter Character which can/may be used in the CSV file
3. Date Format: Different Date formats are available for selecting the right Date Format. In my case I select the Date Format 1 – DD.MM.YYYY
4. Decimal Notation: I set the Decimal Notation to comma as I use commas for the key figures.
5. First Row with Data: Here I use row number 2 because the first row contains header information.
6. Overwrite Mode: Here are three different setting available. O (Overwrite all Records in Filter), U (Update Records), C (Collect Records). I use U because I just want to update the date. O overwrites the existing data and C adds the data up.
7. Check Duplicates: Here I do not select anything as I do not want to check for duplicates.
8. Map InfoObjects to File Columns: Here I select the fields which are provided in the CSV file and which order they will be provided. In my case it is (1) 0CALDAY, (2) 0MATERIAL and (3) 0DEL_VAL. When you click on the button Create mapping default all fields of the Aggregation Level will be added.
9. Filter Values from File: In this setting it is possible to filter and lock the values which are in the files. It is to make sure only the values from the files are overwritten. For example, if I add 0CUSTOMER to the filter and two users are uploading data (one for Customer A and one for Customer B) at the same time this is possible because they will then not lock each other. So they can only overwrite the data for their specific customer.
7. Planning Sequence (ZTEIF02_S01_PS01)
The last step is to create a Planning Sequence which includes our Aggregation Level, Filter and Planning Function. This function then looks like this:
Upload File
Now that I have created all the objects that I need to upload my CSV file, I can use Analysis for Office and to open the query. I also need to add my Planning Sequence which I have created. Then I will have the following elements in my workbook:
Now I can right click on the Planning Sequence and execute it. A File Browser will open and I can select the corresponding CSV file (I will upload the data which is shown in the Scenario Section):
After selecting the file, the data will be available in the query and you get a message that the Planning Sequence was executed successfully:
Finally, you need to save the data to your ADSO:
Hint: You can also execute the Planning Sequence and the Saving via VBA in a Macro and assign a Button to it.
Now the data from our flat file is also available in the ADSO:
No comments:
Post a Comment