How-to Load a File into BW-Integrated Planning (Version 3)

[former_member93896]

Hello Friends of Integrated Planning,

thank you very much for all the feedback I received on the File Upload/Download how-to over the past years. I have great news: Basically every development request has been implemented! Yes, this means that there is a big load of new features available with version 3. Upload and download of CSV files and a new user interface that allows the preview of the file and plan data before you save it are just two of the highlights. The new version is also compatible with SAP BW 7.4.

Prerequisites

Minimum release to use the new version is SAP BW 7.30.

Download

You can download the complete how-to guide as well as the solution from SAP Note 2053696.

Enhancements

The following list shows the changes and enhancements in version 3 compared to the previously published version 2.4:

• v3.0
  
  
  
  • Enabled conversion exit in variable screen
  
  
  • Removed context info from message output (can be enabled again with show_messages parameter)
  
  
  • Added search help for all selection fields including special characteristics like fiscal period
  
  
  • Added check that 0INFOPROV must be filled for uploads on MultiProviders
  
  
  • Support for CSV format for upload and download (new parameters for data separator and escape character)
  
  
  • Improved auto-detection of file format
  
  
  • Added info messages to display version and detected file format
  
  
  • New parameter for checking for duplicate records
  
  
  • New parameter to define display of +/- sign for download
  
  
  • New and improved alternative user interface
  
  
  • Function to generate the required master data for ZIP_* InfoObjects
  
  
  • Added BADI for performing custom transformations during upload and download
  
  
  • Integrated File Upload/Download with Report-Report-Interface
  
  
  • New parameter setting for download to select field description instead of technical name in header line
  
  
  • Automatic recognition of UTF byte-order-mark during upload
  
  
  • Added ready-for-input variables for all parameters
  
  
  
  


• v3.1
  
  
  
  • Added support for XLS format for upload with SAPGUI- Added load from application server which enables upload from Analysis Office
  
  
  
  


• v3.2
  
  
  
  • Minimum release increased to SAP BW 7.30- File Upload is now supported for SAP BW 7.40
  
  
  • Updated screen shots to show GUI-based planning modeler and web-based file upload/download application
  
  
  
  


• v3.3
  
  
  
  • Added enhancement to support F4-BADI- Added option to sort variables on selection screen
  
  
  • New option to display instructions and support information to end-users
  
  
  • v3.4
  
  
  • Added support for formula variables- New option to set maximum number of visible messages in log
  
  
  • Added support for exit variables which are ready for input
  
  
  
  


• v3.5
  
  
  
  • Download of key figures other than "Amount" or "Quantity" will take "Decimal" setting in to consideration when using "Convert Fields" option
  
  
  
  


• v3.6
  
  
  
  • Improved error message in case of character format/encoding issues- Added detection of incorrect field separator (semicolon instead of comma and vice versa)
  
  
  • New Standard File Upload functionality
  
  
  • Support for Advanced DataStore Objects for SAP BW powered by SAP HANA
  
  
  
  


• v3.7
  
  
  
  • Added link to documentation of BW workspace solution as standard alternative to this how-to solution- Included chapter in documentation about required authorizations
  
  
  
  


• v4.0
  
  
  
  • Support for upload and downloading comments (characteristics as key figures)- Improved error messages when uploading files in CSV and TXT format
  
  
  
  


• v4.1
  
  
  
  • Added option to sort columns of data preview (file preview is always sorted the same as the file format)- Added optional URL parameters for defining maximum number of visible rows for file and data previews
  
  
  
  


• v4.2
  
  
  
  • Added optional URL parameter for displaying "logoff" button and redirecting to a target URL after logoff
  
  
  
  

Preview

Here are a few screen shots of the version 3 user interface (Note: The old UI is still available in the version 3 transport). For more details, please refer to the how-to guide (see "Download" section above).
File Upload Selection Screen:



File Upload Preview Screen:



File Download Selection Screen:



File Download Preview Screen:

Your Feedback

As always, I appreciate your feedback. It's as simple as adding a comment to this blog.
Enjoy the new File Upload and Download for BW-Integrated Planning!

Best,
Marc Bernard
@marcfbe

[former_member93896]

The file upload will have C_TH_DATA and at least one copy (sometimes several copies) in memory. Several million records probably still lead to memory issues. You will need some trial and error to find the maximum for your system (also with other concurrent users).

But please, no complains about performance. If you want a fast load (that also requires less memory), use ETL.

Best,
Marc
SAP HANA Competency Center

 

[SharadLTI]

Thanks Marc .. 🙂

[former_member298480]

Thanks Marc for your quick and clear response !

I am not sure to get you 100% still... :

• indeed ZRSPLF_F4_ENHANCEMENT needed to be adjusted, i did it.


• indeed when putting a break point this is triggering RSD_CHA_HELP_VALUES_EXIT.

>> However in debug this is still not going into my implementation of enhancement spot RSR_VARIABLE_F4_RESTRICT where my custom code to restrict the list is actually defined in an implementation class calling method IF_RSR_VARIABLE_F4_RESTRICT~GET_RESTRICTION_FLAT.

 

Am i missing something obvious in here ?

Thanks again.

Best regards,

Vincent

[former_member93896]

Check that the enhancement spot implementation has the correct filter i.e. proper name of the InfoObject that is used behind the variable.

Best,
Marc
SAP HANA Competency Center

 

[former_member298480]

Hi Marc,

Thanks, actually i am concerned because yes the enhancement implementation is properly configured, and actually this works smoothly when i try to execute the planning sequence in RSPLAN. But when opening the WebDynpro then there is no restriction anymore and if i put an external break-point inside the enhancement spot it does not go inside.

Do you think we can open OSS message to get support on this ? (except if you have other ideas to suggest).

 

Thanks again !

Regards,


Vincent 

[former_member93896]

I tested it again and the F4-BAdI is certainly called by the WebDynpro as well. Please create an incident with open GUI and HTTP connection (have it forwarded to me).

Best,
Marc

 

[former_member298480]

Hi ! Thanks a lot.

I have created ticket, it should be forwarded to you:

357702 / 2018 F4 value help BADI not triggered in Webdynpro Upload File

Meanwhile i have asked for system opening. It may take a few days, there is no emergency on this topic for the time being.

 

Best,

Vincent

[riseyb]

 

Hello,

is it possible to integrate a button for a logout?

Only closing the web browser is in some cases not enough to cancel a lock.

Thank You and best regards

Ricarda Seyb

[former_member93896]

Hello Ricarda,

interesting request. No one ever asked for it... but it was easy to implemented. Please send me an email, so I can send you a version for testing.

Best,
Marc
SAP HANA Competency Center

[wounky]

Hi Marc,

 

could you please tell how what is the expected behavior for the field conversion marked with 'X' for the field value that has already been provided the user in the system format?

e.g. user inputs YYYYMMDD and not DD.MM.YYYY for 0calday

Asking this as it currently generates an error without description - "File Upload: Table conversion error.".

 

If it is how it is supposed to work, would you be so kind and propose some workaround so both formats are supported?

 

"Field conversion:
If the "Convert Fields" setting is turned on, field values are interpreted
according to user settings (see below)."

 

Would very much appreciate your reply.

 

Thanks in advance &

Kind regards,
Sebastian

[former_member93896]

Hi Sebastian,

the file upload is using the same logic as the standard GUI_UPLOAD to parse a line of the file and separate it into fields. With field conversion turned on, this logic will validate data types, which includes the user's date format settings. So the user must supply dates in the format they see everywhere else in SAP. If field conversion is off, there's no such check.

Since this is not coding of the file upload, I won't be able to change or adjust it. The only way around it, is to upload in XML (which is unlikely to be accepted).

The remaining alternative is to keep field conversion off meaning all values must be in internal format. However, what you can do is program your own conversion logic in the provided BADI and method transform file from user entries to internal values. You will have to do it for all fields and obviously cover the cases that any user might come up with (or you define). A bit of programming but doable.

Best,
Marc
SAP HANA Competency Center

 

 

[wounky]

Thank you!

Appreciate your prompt reply as UAT's are live.

 

Have a good weekend!

 

Best,

Sebastian

[Former Member]

Hi

what is the character encoding that works?

In the web options, I saw that my xls is set on "western european windows". I changed to unicode, utf8, us-ascii, no luck.

Regards

Yann

 

[Former Member]

Found it: there was a "scandinavian" character in my data set. ø is not accepted in the upload... Changed it to o and it worked.

[former_member528767]

Hi Marc,

 

could you clarify the situation about "New Standard File Upload functionality" (I mean planning function type 0RSPL_FILE_UPLOAD_AO): is it possible to upload an .xls (not .csv) file using this standard function? Or the only way to achieve this is to use your Z-development?

We are on BWonHANA 7.50 SP12, AO2.7.

 

Thanks,

Alex

 

[former_member93896]

Hello Alex,

the standard solution is documented here:

https://help.sap.com/viewer/69ec98e0c0e54128a61c32f3255fa438/11.0.5/en-US/142ff737ee8c46a4811192fc22...

It does not support XLS, just CSV format.

Best,
Marc
SAP HANA Competency Center

[former_member320301]

 

Hi Marc,

It's a great tool for users who require mass download and upload data.

I currently faces two problems:

1. when downloading data with DATE type infoobject and with field conversion on, the field content is truncated to only 8 characters irrespective of my user profile. e.g. 20180725 would be downloaded as '25.07.20'  when my profile has date format 'DD.MM.YYYY'


2. The second issue is I can download the file through RSPLAN, but when I try to download it through webdynpro, I can the following error message as soon as I click Download button.
   

   500 SAP Internal Server Error

   
   ERROR: The ASSERT condition was violated. (termination: RABAX_STATE)

do you know what could be the problem? How do I troubleshoot this?

 

Regards,

Aaron

[former_member93896]

Hi Aaron,

I tested it and the download of date works correctly and as designed. I tried all formats and it looks to me like you picked "Text (fixed)" format (plus "Convert field = X). This will lead to the behaviour you see since as you know, the date field has only 8 characters. If you want to see the complete date, select "Text (Tab-delimited)" (or CSV). The fixed format makes sense only if you leave convert fields off.

I was unable to reproduce the second issue. Please check that you have the latest version 4.2 of the solution installed. If you can't figure it out, send me the complete dump from transaction ST22.

Best,
Marc
SAP HANA Competence Center

[former_member320301]

 

Hi Marc,

according to note, v4.2 only works for BW7.5 and BPC 10.1. Our system is still on BW7.4 and BPC 10.0. We have installed v4.0.

For the first issue, I have fixed it manually for now by changing the _CREATE_DATA_STRUCTURE method line 17. When infoobject is of type D, instead of assign it D(8) type to the field in the structure, I have assigned it C(10) so that it will capture the full string of converted date '25.07.2018'. This has solved the issue for now.

For the second issue, I'm still trying to troubleshoot where the problem is. It seems to throw an error even before it hits the planning function program.

 

Regards,

Aaron



 

Regards,

Aaron

[former_member93896]

That's a misunderstanding. Any v3 or v4 works starting from SAP NetWeaver 7.3. If you have SAP NetWeaver 7.4 or higher, then you need any v4 version... Of course, the latest is always the best. So v4.2 should work for your system.

Best,
Marc
SAP HANA Competence Center

 

 

[former_member320301]

Hi Marc,

ok. I will deploy it and try again. Just one suggestion, can you change your document to clarify this misunderstanding?

 

Regards,

Aaron

[former_member93896]

Done. The 4.2 document contains a compatibility matrix.

Best,
Marc
SAP HANA Competence Center

[former_member320301]

 

Thanks

[frank_klingl]

Hi Alexey,

with AO 2.7 those planning function are now working.

See

Regards

Frank

[former_member320301]

Hi Marc,

 

Does this tool allow us to download/upload the text instead of key of field? It is not very user-friendly for business users as most of the time it is hard to interpret what key represents.

 

Regards,

Aaron

[former_member93896]

Hi Aaron,

that's a good point. You can enhance the solution to include the texts: In the download file format, add some fields for the texts (like 0TXTLG). Then use the TRANSFER_FILE enhancement spot to fill the text fields (chapter 5.7 in the guide).

Best,
Marc
SAP HANA Competence Center

[former_member320301]

Hi Marc,

Thank you for you suggestions. I'm just back from holiday and testing this. For this to work, I need to define both key and text of the field as output fields in the 'File format' of the planning function. What if I only want to output text field in the file? Is it achievable using BADI as well?

Similarly, I only want to have text as a input in the upload file.

 

Thanks & Regards,

Aaron

[former_member600684]

Hi,

I have issue with FF upload using hierarchy-variables. Confguration i use is AFO 2.6 sp01 x86 32 bit with workbook/excel 32bit.  Webdynpro exits with 500 server error. But ST22 mention ASSERTION FAILED. When debugging webdynpro ZRSPLF_FILE_UPLOAD it exits at   component Views, method  ONACTIONON_UPLOAD.

READ TABLE lt_fields INTO ls_fields WITH TABLE KEY m_id = l_id.
ASSERT sy–subrc = 0.

But it starts in method WDDOMODIFYVIEW  where variables are handled.

When i use hierarchy node in planningfilter, it works but not desired as user needs auth on all  nodes. If we use hier variable then it stops.  In debugging we see that variable is a type 2 variable and that values are not saved by the program. As we also use in same test type 1 variable we see it comes through.   Is there a version where type 2 variables work properly?

Thanks

[former_member93896]

Hi Nico,

There is no support for hierarchy node input variables (see the HTG, 3. Prerequisites, Limitations).

Sorry, I don't have any solution.

You might be able to use an exit variable (select-options) and derive the single values from the hierarchy node (which you somehow have to pass to the ABAP backend first).

Best,
Marc
SAP HANA Competence Center

 

[ramona_cotiuga]

Hello, any update on this topic. I get the same issue:)

Thank you!

[AlexandraV]

Hi, Marc!

When we're trying to upload and save data to a direct update DSO short dump occurs.



Category ABAP Programming Error
Runtime Errors SAPSQL_PARSE_ERROR
Except. CX_SY_DYNAMIC_OSQL_SEMANTICS
ABAP Program SAPLRRSI
Application Component BW-BEX-OT



 



In the error there is a reference to a characteristic which we're using as a key figure -"/BIC/ZBP_DESC (comments a stored there).

 

We're on BW 7.4 SP16. The planning model is very simple: Write Optimized DSO -> Aggregation Level. Error occur  when we press on the save button.

 

Thanks for any further assistance.

 

Regards,

Alexandra

[former_member93896]

Hi Alexandra,

planning is only supported for InfoCubes and Direct Update DSO. If you are using a Direct Update DSO, please create an incident.

Best,
Marc
SAP HANA Competence Center

[AlexandraV]

Hi Marc,

Just an update.

Problem was caused by derivation that we're using to record changes in planning data (logging - user, time, document id).

We turn of this functionality and switched to BADI RSPLS_LOGGING_ON_SAVE.

It helped.

 

Regards,

Alexandra

[caotuan_chau]

Hi Marc,

I am using Version 4.0 . Is there a possibility to hide the warning messages with the parameter Show_messages?

Regards,

Tuan

[former_member93896]

Hi Tuan,

yes, that should work but you might miss some messages that are important for the user. Try show_messages=EXA.

Best,
Marc
SAP HANA Competence Center

[caotuan_chau]

Hi Marc,

I tried out your suggestion, but still the warnings are displayed. Do you have another idea?





Regards,

Tuan

[former_member93896]

Hi Tuan,

messages related to parameters of the planning function could not be hidden. I enhanced the solution so it should now be possible. Please download the v4.2 zip again from SAP Note 2053696 and install transport K900495.CBW

Best,
Marc
SAP HANA Competence Center

[FelixS2]

Hi Marc,

thank you for updating the tool so steadily! We also want to use the upload with "column = time period" and therefore to suppress the warnings is useful. Even though we have to keep in mind that locks are displayed as warnings will not be shown when surpressed.

 

Regards

Felix

[former_member93896]

Hi Felix,

Always a pleasure to make it more useful. Changing a few lines of code here or there is no problem.

The lock messages are coming from the IP framework and not the planning function itself. Therefore, the lock warning is still visible even if you use show_messages=EXA.

Best,
Marc
SAP HANA Competence Center

[former_member93896]

see below

[caotuan_chau]

Hi Marc,

thank you for the solution. I will update the Version and test it again.

Regards,

Tuan

[former_member346188]

Hello Marc,

 

Could you please tell if it is possible to round floating point key figure upload with nine decimal places (max) to the way BEx does it?

Even though the same Key Figure is used for the flat file upload, the values uploaded via the flat file differ from the ones in the file and in the Input-Ready Query.

Please find a sample below.

 

Scenario:

User loads via the flat file: 0,00089465

Stored in the DB is:  8,9464999999999998E-04 (0,000894649999999999)

Displayed in the query is: 0,00089465

On the input-ready query zero post attempt, posted to the database is: -8,9465000000000009E-04 (-0,00089465)

 

Result: Values do not match and do not sum up.

 

Thanks in advance &

Kind regards,

Sebastian

[former_member93896]

Hi Sebastian,

floats can be tricky. Unfortunately, we can't guarantee precise numbers which is due to the nature of how floats are stored (i.e converted to binaries). And this becomes more difficult in cases you have to add several floats together as is often the case in planning.

https://launchpad.support.sap.com/#/notes/2529372

Best,
Marc
SAP HANA Competence Center

[former_member346188]

Hi Marc,

 

got it, thank you.

Seems like 'inevitable' change 😉

 

Best,

Sebastian

[maria_ruseva1]

Dear Marc and SAP experts,

is it possible to set the decimal separator to be always dot in the upload function for txt files?

thanks and regards,

Maria

[RafkeMagic]

in the new standard planning function (0RSPL_FILE_UPLOAD_AO) that is possible

[SharadLTI]

Hello Bernard,

Does the File upload for BW IP support time dependent master data upload ?

Eg . if a particular Profit Center is invalid on the day of upload ,will it throw error  or will it pass as it is  a valid master data ( is there any additional settings involved).

Thanks ,

Sharad

[former_member637992]

Dear Marc,

Any plans to support Hierarchy node variables in Upload flat file . Or still to be planned for an enhancement.

[guidozani]

Hi Marc,

I'm trying to Upload a ".txt" positional file. Everything works fine when i set "File Format" parameter with a list of InfoObjects followed by KF but as soon as i set "File Name" with a mix of InfoObjects and KF (which is my target) i start getting some errors:

-"File Upload: Field conversion error. Line: 1".

-"File Upload: Check if the correct decimal notation was used".

NOTE:

1) KFs are set with a custom domain which fix the N.Character according to the N. of dedicated digits in the file.

2) If i switch the order (IO before KF) both in the "File Format" and txt everything works fine.

I know there's a limitation with the DOWNLOAD Planning Function ("The file format must contain all characteristics first, followed by all key figures. If characteristics and key figures occur in a mixed order, the file download will NOT aggregate the data properly!"), is that true even with the UPLOAD?

Thanks Again for your valuable blog.

Regards,

Guido

[pvarb]

Hi Marc,

Can you please share where the current solution can be downloaded?

The SAP Note mentioned above does not work.

Thanks,

Patrick