Introduction
Sometimes we are not granted the joy of working with SAP source systems and their built in extractors. When faced with non-SAP sources, we often fall back on flat files to upload data into our SAP Business Warehouse system. In many cases loading flat files into SAP Business Warehouse can be a serious hassle. Just think of all the things that can go wrong when flat files that you need to upload arrive into your already stuffed mailbox on a daily basis.
- You might not be so eager to load these files during the night, so there might be a significant delay before the data becomes available in the reports for the end users.
- The files might just be too big to be sent by email.
- From a security point of view, it is not wise that potentially sensitive data finds its way into your inbox as an attachment.
- …
- And finally, it is just no fun to have to repeat this manual intervention every day.
In this article, we will explore how the process of loading flat files into SAP BW can be fully automated by using an FTP server on which the files are being stored. The reason for an FTP server, rather than granting the right to put files on the SAP BW server, is that we can keep control of what files end up on the SAP BW machine and we minimize the risk of running out of disk space or security risks.
We will explain how SAP BW can access the FTP server and copy the files unto its own application server. The ABAP code needed to do this will be discussed and we will also show how a flat file on the SAP BW application server can be loaded into the PSA (Persistent Staging Area).
Flat files on an FTP server
The File Transfer Protocol basically facilitates the downloading and uploading of files on the internet. FTP is fairly easy to use. Most FTP client software allows easy transfer of files via drag and drop or you can even use your internet explorer or windows explorer to access the files. Some of the benefits of using FTP are:
- Transferring large files can be done more efficiently.
- Automatic resumption of file downloads or uploads without a separate download manager.
- User authentication and secure transfers with encryption protocols.
To access an FTP server via your Internet Browser you will need the host name of the server. Its address will be similar to a website address, except instead of "http://”, it will begin with "ftp://”.
Image 1: FTP access via Internet Explorer
Alternatively, you can enter the same address in your Windows Explorer and use the Windows functionalities to drag and drop, organize, open, rename… the files.
Image 2: FTP access via Windows Explorer
Once you have agreed with the person in charge of generating the flat files that an FTP server will be used to transfer them, you should carefully consider a name for these files. If the old files may be overwritten every time a new file is uploaded you can agree to use the exact same name for each new file. If it is required to keep some kind of history of all the files being transferred, it is advisable to include the date on which the file was posted into the name. Whatever naming strategy you agree on, make sure that you will be able to generate that exact same name in the ABAP program used to pick up the file from the FTP server and copy it onto the SAP BW application server.
In this article we will discuss ABAP code that takes into account the date at which the flat file was generated. The naming convention used for the flat file is: flatfilenameYYYYMMDD.CSV. In our example we will be loading files the day after they were generated and posted on the FTP server.
Image 3: Our naming convention includes the date the file was generated
Accessing an FTP server from within SAP BW
Fortunately, SAP has foreseen a standard ABAP program to be able to communicate with an FTP server. This program is called RSFTP002 and can be executed using transaction code SE38.
Image 4: Program RSFTP002
In the input screen you can enter the following variables:
- Username and password (if needed)
- The hostname or IP-address
- Up to 3 FTP commands
- The RFC Destination which by default is set to SAPFTP
- And a compression indicator which by default is set to ‘N’ so files are being transferred in an uncompressed state.
As mentioned above, the default setting for the RFC destination is SAPFTP. This destination is used to send a file residing locally on the user’s client directly to a remote server without technically pulling the file through the SAP application server. However, when you want to transfer files to an SAP application server from a remote system, you need to use the SAPFTPA destination.
In ‘Appendix A’ we give the ABAP code of a program that is capable of connecting to an FTP server and transferring a file from that FTP server to the SAP BW application server. The program also includes some code to inform the user when it could not find the file it was looking for on the FTP server. This error message is useful when the program is included in a process chain. Furthermore, since we do not want our SAP BW application server to become cluttered with a myriad of flat files, a simple clean up service is foreseen. A more detailed explanation will be given in ‘Appendix A’.
Where are my flat files located within BW after the transfer?
Once you have transferred the flat files from the FTP server onto the BW application server, you can find them in the SAP home directory (DIR_HOME). This directory can be accessed using transaction code AL11.
Image 5: SAP directories using AL11
Here you have a clear overview of the files that have been copied from the FTP server. You can also find useful information like the time and date the flat file was put onto the BW application server as well as the size of the files. You can even have a look at the actual data in the file by double clicking it.
Image 6: Examples of files transferred to the home directory
Loading flat files from the BW application server into the PSA
Now that your flat files are available on the BW application server, you would like to load them into the Persisting Staging Area (PSA) of BW. Once the data is located in the PSA you can load it further on into DSO’s, InfoCubes, etc.
First you have to define a DataSource suitable for loading the type of data in the flat file. Make sure you create this DataSource in the Source System that was created for flat file loading. Then make the correct selections in the fields below:
- Adapter = Load Text-Type File from Application Server
- Data Format = Separated with Separator (for Example, CSV)
You should also carefully define the properties of each individual field. This can be done in the ‘Fields’ tab. Finally, you should test your DataSource by using the ‘Read Preview Data’ button which can be found in the ‘Preview’ tab.
Once the DataSource has been created you can move on to the next step: creating an InfoPackage for carrying out the actual data load from the flat file into the PSA. As you could have guessed, the settings with respect to the ‘Adapter’ and ‘Data Format’ fields required for the DataSource are also required here. Furthermore, it is also important to set the ‘Character Set Settings’ to ‘Direct Entry’ and to choose ‘1100’ as the ‘Character Set’. ‘1100’ is the code page used by the default installation.
Image 8: InfoPackage for flat file loading from application server
Note that in the InfoPackage in ‘Image 8’, the path towards the flat file to be loaded is determined in a routine. This is done because the flat file name includes a date which changes every day. Assume that every night, a flat file generated the day before is loaded. The code for the path will look like:
data previous_day type sy-datum.
previous_day = sy-datum - 1.
concatenate '/usr/sap/BWP/DVEBMGS53/work/flatfilename' previous_day'.csv' into p_filename.
p_subrc = 0. |
The path for the home directory in which the flat file is located can be found using transaction code AL11. (See ‘Image 5’.)
Bringing the pieces together in a process chain
Obviously, you would like to limit the manual actions required for loading the flat files into SAP BW to an absolute minimum. For that purpose, you can integrate the program presented in ‘Appendix A’ and the InfoPackage for flat file loading described in the previous section into a process chain that also contains all the steps necessary for loading the flat file data from the PSA into DataStore Objets (DSO), InfoCubes, InfoObjetcs, etc.
Image 9: Process chain including program for retrieving flat file from FTP
As shown in ‘Image 9’, the second step of the process chain includes the ABAP program for retrieving the flat file from the FTP server and copying it to SAP BW whereas step 2 represents the InfoPackage to actually load the flat file data from the SAP BW application server into the PSA. The ensuing steps can be standard processes to load data into a DSO, activating it, loading it into an InfoCube and so on.
The Image below shows how the execution of an ABAP program can be included as a step in a process chain. Using transaction code RSPC, you can include such a step by selecting the ‘ABAP program’ process type under the ‘General Services’ folder. All you need to do then is give the variant a proper name and select the program of ‘Appendix A’ as the program to call. In ideal circumstances, this process chain will be scheduled to run during the night, so that by morning all flat file data is available in the InfoCubes and ready to be used in reports.
Image 10: Including an ABAP program in a process chain
When a certain step of a process chain fails, SAP usually gives you an error message describing what went wrong. Obviously, if you want your custom ABAP program to generate meaningful error messages, you will need to write some extra code yourself. In our case, a new flat file was supposed to be posted on the FTP server on a daily basis so that every night we could load the flat file generated the day before. However, we found that the most common issue was the absence of the new flat file. Therefore, we included code to generate an appropriate error message whenever the daily process chain fails due to the absence of the new flat file.
Image 11: Error message when no new flat file is found
It is possible to provide more detailed error messages, but since this is outside the scope of this paper, we will not elaborate on this topic. The ABAP code in ‘Appendix A’ shows how the error message in ‘Image 11’ can be generated.
Conclusion
In this paper we have presented all the building blocks needed to automatically copy flat files from an FTP server onto the SAP BW application server and then load the data into the PSA from which it can be loaded further into DSO’s and InfoCubes. When these building blocks are integrated in a process chain, the data can be safely loaded during the night. You do not need to lose sleep or worry about big flat files overflowing your inbox with sensitive data and your report users will be happy to have their data when they arrive in the morning.
APPENDIX A
We have mentioned a custom ABAP program for transferring flat files from an FTP server onto the SAP BW application server. This program also generates an error message in case no new flat file was made available on the FTP server. Finally, some cleaning up is done to prevent the FTP directory as well as the SAP home directory (DIR_HOME) from looking like a garbage bin.
In the code below, all actual file names, directory names, ftp addresses, etc. have been replaced by an italicized more generic description.
In part 1 of the code a connection is made with the FTP server. The correct directory is selected and a ‘get’ command is executed to fetch the flat file from the server. The name of the flat file has been constructed using the system date. Make sure that whoever is responsible for the FTP server gives you the host address, a username for BW to access the server as well as a password.
Part 2 provides some code to generate a meaningful error message in case something goes wrong. Since this is not the main focus of this paper, we will not go into detail. Obviously you can use this code as an inspiration for generating your own error messages.
Part 3 makes sure that the flat file that was loaded during the previous run of our daily process chain is removed from the FTP directory. If you want to keep a full history of your data on the FTP server, this clean up may not be desired. However, if no historic files are to be kept on the FTP server, you might as well delete them for the sake of recovering disk space.
Something similar can be done for the flat files that have been copied to the SAP BW application server and are now stored in the SAP home directory (DIR_HOME). In our case, we want to keep every flat file that was generated on the first day of the month. All the other files may be deleted, which helps keeping the home directory clean.
report zftp_get_flat_file_data.
******************* ***** part 1 ***** *******************
******************* ***** part 2 ***** *******************
******************* ***** part 3 ***** *******************
******************* ***** part 4 ***** *******************
|
