How to automate flat file loads into SAP BW?

 

Flat file uploads are pain in the neck in most cases. However, in many cases we do not have an option except to go with flat file uploads because of non SAP source systems or in cases where business users has to consolidate source file from different systems. In any case automating these flat file loads will save much needed time and head ache for SAP BW support team and saves the delay in emailing the file to support team. This also helps with the security of sensitive data in flat files.

There are different options to automate flat file loads. You have to choose the best option for your environment. It depends on your system architecture, available interfaces, type of scheduling tool being used etc. For instance there are scheduling tools like (IBM Maestro/Tivoli) that has features (like being able to start the jobs based on presence of a file in the application server).  It is relatively easy and straight forward to automate if you are using such a tool.

There are three critical parts in automating flat file loads.

  1. Getting the flat file on to the app server (Flat file needs to be on the app server to be able to schedule the infopackage in the backgroud, you can schedule it in the back ground to load from  client workstation)
  2. Detecting the flat file automatically and starting the job/process chain to load the file.
  3. Actual loading of the file, checking for format errors, sending user notifications, post cleaning steps like archiving/moving files

 

Getting the flat file on to App Server:

 For this step can use a FTP client to upload the file from user’s computer to a designated directory/folder in the app server. This works in the cases where a single user/small user group is responsible for generating the flat files. One can give upload access to the user/user group to the folder where flat files reside before being loaded into BW. Users can upload the file using a ftp client like WSFTP pro.

The above approach works in case of single user/small user group. It won’t work in case of a large user group because of file overwrite and timestamp issues. The better approach for large user group is to use ftp scripts to consolidate and ftp the file to app server from windows shared folders.

This is how the second approach works:

  1. Users put their source files on to a common share windows NT drive (folder)
  2. A batch script consolidates (if there are more than one file) and ftps the file to app server
  3. This windows batch script can be schedule to run for every 30 mins or 1 hour depending on the requirement.
  4. The advantage of this approach is that no training of FTP client is needed for end users, the script takes care of multiple files issue and also it is scalable meaning can easily allow new users to upload the files all they need is access to the shared folder

 

Detecting the flat file and automatically and starting the job/process chain:

This is the critical part. Check your scheduling tool features if you are using one to see if it is possible to schedule jobs dependent on presence of a file in the app server. I know that IBM Maestro/Tivoli supports this. Other tools also might support. You need to resubmit the schedule at the end of the load to be able to load multiple files in a single day.

If you are using a scheduling tool that supports this all you need to do is schedule the jobs to be dependent of the file. You need to define the file name and directory path in when you define the jobs in the tool. The scheduling tool checks for the file at regular intervals ( 10 or 15 mins ) and starts the schedule when the file is present.

Don’t worry if you are not using a scheduling tool or your scheduling tool does not support this. There are other options. You can write an ABAP program to check if a file is present in the app server. This ABAP program can be used to trigger the process chain that loads the actual file.

This is how this works:

  1. ABAP program is scheduled to run every 30 mins or 1 hour depending on the requirement. This program triggers an event if the file is present.
  2. A process chain scheduled based on the event from above step. This process chain loads the file into BW.

 

Post load and cleanup activities:

      You need to do some housekeeping activities to avoid overlap of loads, keeping track of files and time stamps for troubleshooting if there are any data issues.

  1. First step is to rename the file at the beginning of the process chain/schedule. For instance if your file name is flatfile.csv , rename it to flatfile.cbs.load. By doing this you can avoid overlapping loads.

Let us assume your ABAP program runs every 30 mins, it detects the file and starts the load at 10 AM and also assume that the file is huge and it takes more than 30 mins to load. In this scenario the next run of the ABAP program at 10:30 AM detects the same file and it triggers the process chain before the fist file load is complete. Renaming the file avoids this issue.

  1. Check flat file for formatting issues: Top most problems with flat files are formatting issues. You can write an ABAP program to check for formatting issues before hand and notify the users and skip the data load.
  2. Archive the file to archive directory after the successful load and notify the users. You can use UNIX command if your scheduling tool supports or ABAP program or SM36 job for this.

Please drop a comment if you have any questions.


Comments (3)

Riccardo
Said this on 4-12-2010 At 09:18 pm

Hi,

   I have found intresting your article beacuse I'm handling an activity very similar to the one you are describing. For some reason that now I don't explain you (too long time), I have 89 loacations spread worldwide that submit on a daily basis the daily sales file. So everyday I should receive 89 files.

  The first two part you have described are clear and from this point of view the process is working fine, the big problem for me is that now the users would like to have a check of the data quality (there are some rules that can be applied to check the good quality of the files) and to be warn by email of the success or the failure of the loading (failure could be for poor quality of the data or format error).

Now you were suggesting to write an ABAP program for doing this, but when I could trigger a program like this, in the loading chain?  Do you think that XI could help somehow to have a solution easier to be applied?

thanks, Ric

IT_Guru
Said this on 4-13-2010 At 01:13 am

Wow! 89 files a day.  

Yes, you can do it in the process chain just before actual load step. You can reference this document which explains using an ABAP program to validate flat file for formatting errors. One advantage of doing this is this one file won't block other correct files from being available for reporting.

I am not familiar with XI so I can't comment on that. 

Paul_ABAP
Said this on 1-12-2012 At 03:00 am

I have a little different scenario.  First of all I am an ABAP developer and I have to write the code on the R/3  side and the BI side to kick of an event for processing the flat files placed on server.  I do not know a lot about BI and am not very good with my BI terminilogy. 

I have  no problem with placing the file on the server, I have no problem kicking off the event, the problem I'm having is in the process chain or info package(excuse my lack of knowledge for BI terms).  These info packages give you an exit or routine for determining the file name.  I am trying to find a way to get the directory and file name passed into this exit.  That way you don't have to worry about what file you are processing you just process the one the user sends in. 

Any ideas on how to do that?  I've tried several different things.  I've used FM   <!-- SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } -->RSPC_API_CHAIN_START I've used   <!-- SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; } .L0S52 { color: #0000FF; } .L0S70 { color: #808080; } -->CALL METHOD cl_batch_event=>raise but none of these allow for passing the directory and file name to the process chain.  I've also tried memory but because of how the event is triggered in batch (I believe) this does not work.  I tried a class definition as well where I load the attributes with the value.  I also tried loading value into memory and retrieving it when I get into the routine but that did not work either. 

It seems to me that if SAP allows you to process flat files and give you a routine for determining the flat file that they would include a way to pass the directory and file name in directly.  ANY IDEAS?!

 

Post a Comment
* Your Name:
* Your Email:
(not publicly displayed)
Reply Notification:
Approval Notification:
Website:
* Security Image:
Security Image Generate new
Copy the numbers and letters from the security image:
* Message:

Email to Friend

Fill in the form below to send this blog to a friend:

Email to Friend
* Your Name:
* Your Email:
* Friend's Name:
* Friend's Email:
* Security Image:
Security Image Generate new
Copy the numbers and letters from the security image
* Message: