Skip to Content

Dynamic Determination of File Name in OHDs/APDs

Applies to:

SAP BI 7.0. Will also work on SAP BI 3.5. For more information, visit the Business Intelligence homepage.

Summary

This article gives you a way of generating files with dynamic file names using Open Hub Destination (OHD)/ Analysis Process Designer (APD) in the application server.

Author: Prasath Vettrinathan

Company: Infosys Limited

Created on: 27 – Jun – 2012

Author Bio

Prasath Vettrinathan is an SAP BI/BW consultant currently working with Infosys Technologies. He has got rich experience in various areas of SAP BI and worked on various implementation/Support Projects

Table of contents

Introduction

Scenario

Steps to be followed

           Step 1: Create Logical File Path

           Step 2: Create File Exit Function module

           Step 3: Create Logical File Name

           Step 4: Creating OHD

Results

Introduction

Many a times, data from BW is to be sent to various legacy systems or for business users in the form of flat files. These flat files are generated as per the requirements and placed in the application server of BW in a specific directory with a specific naming convention. Here we deal with dynamic determination of the path of file or the name of the files using logical file name in OHDs/ APDs.

Scenario

When using OHDs or APDs there might be a requirement to place the generated file in a specific directory in the application server, also the naming convention of the file may contain dynamic parameter such as timestamp, counter etc., to distinguish the files that are generated by the same OHD or APD every day.  The timestamp used in the file name should be logical instead of placing the system date and time. 

For example, consider the flow

Flow_1.JPG
 

When the loads to the cube are completed, the delta/full data is to be put on the application server. OHD is used for generation of this file. The file generated by OHD will have the following naming convention. “ZXXXX_21062011163045.csv” where ZXXXX is the fixed component and 21062011163045 is the dynamic component, i.e., the time when the last load to the cube has happened.

Steps to be followed

1.      1. Create a logical file path, path where the file needs to be stored in the application server.

2.      2. Create file exit function module, which is used for filling the dynamic parameter in the file name.

3.      3. Create logical file name.

4.      4. Use the logical file name in the OHD, to generate files in the specified path in the application server with the specific naming

      convention.

Step 1: Create logical file path

The logical file path definition is used to associate a physical path in the application sever. Hence when a logical file name containing this logical file path is used, the file is stored in the actual physical path that is defined.

To define logical path name, Go to the FILE transaction. Select “Logical File Path Definition” and select “New Entries”.

Flow_2.JPG

Add an entry with the logical file path and a description

Flow_3.JPG

To assign the logical file path to physical path, select the new entry added and click on “Assignment of Physical Paths to Logical Path” and select “New Entries”

Flow_4.JPG

Enter the syntax group, which has to same as that of the OS of the application server. For ex., Windows NT, UNIX etc., also enter the physical path where the file has to be stored in Application server. The reserved name <FILENAME> is to be used in the physical path as a place holder for the file name. There are lists of other reserved words like <OPSYS>, <SYSID>, <DATE> which can be replaced with current value at runtime. Save the entry.

 

Flow_5.JPG

Now, If this Logical file path is used in the logical file name then the files that are generated would be placed in the physical path i.e. DIR_HOME

When generating the file with an OHD/APD without using a logical file name in the application server, by default the files are stored in DIR_HOME directory. Logical file name gives us flexibility to store the files in any directory we require. In this example, the files are saved in DIR_HOME directory itself.

Step2 : Create File Exit Function Module

The physical file name must have the timestamp which is the runtime parameters that needs to be filled during execution. For doing this, we will be using the reserved word <F=name> while defining the logical file name.  And a corresponding file name exit user defined function module should be created with the following naming FILENAME_EXIT_name. The return value of this function module will replace the reserved word in the file name.

Following is a list of reserved words and the corresponding function modules that are to be created.

<Z=name>   ->   Z_FILENAME_EXIT_name

<Y=name>   ->   Y_FILENAME_EXIT_name

The function module that is written must satisfy the following requirements:

·         An export parameter with the name “OUTPUT” must exist.  No structure may exist for these parameters.

·         Import parameters are only supported if they have default values.

Below is the code used for obtaining the timestamp.

Flow_6.JPG

Flow_7.JPG

Note:

The FM must be created before creating the logical file name, otherwise we will get an “error message”.

Step 3: Create Logical File Name

Click on “Logical File Name Definition” and select “New Entries”

Flow_8.JPG

Add an entry with the logical file name and a description. The Physical File must consist name of the file with the desired naming convention. Where ZCNTTS is the name used in the file name exit function module.

Flow_9.JPG

Save the entry.

Step 4: Creating OHD

OHDs are created with the required fields. Logical file name is used in the OHD that is created.

Give “Type of the file name” as “Logical file name” and “Appl Server File Name” as the logical file name that is created (Y_FILE_MULTIPLE) 

Flow_10.JPG

Results

On executing the OHD, the files are generated in the application server. The application server can be accessed using tcode AL11. Go to the directory DIR_HOME. The file is generated with the file name as ZOPT_20120611141959.7140000_G1.csv

Flow_11.JPG

Where the timestamp is the last load timestamp of the cube, which is present in the RSBKREQUEST table.

Flow_12.JPG

Note:

1.      1. The logical file name created can be used in APDs when the Data Target of an APD is “Files

  2. The reserved words <F=name>, <Z=name>, <Y=name> can also be used in the logical file path definition along with a   corresponding file exit function module. By doing so the path in which the file has to be stored can be determined dynamically.

                                  

10 Comments
You must be Logged on to comment or reply to a post.
  • Good effort on worthy topic !!!

    The document is nice, the complex scenario in simple explanation. For sure this will help us in realtime situation. Thanks for your effort and time.

    Keep posting your SAP thoughts.

  • Hi Prasath,

    Thanks for the very good document.

    Is it possible to have IMPORTING PARAMETERS for this function module so that we can dynamically pass the DTP name instead of hardcoding?

    Br,

    Harish

  • Hi Prasath,

    Its a good blog.

    Just wanted to know when the file is created for the first time and stored in application server why the name of the file starts with 0000000.csv although a timestamp is created in the RSBKREQUEST table. The next time file created always displays the correct timestamp but why not in first time creation. Or am I missing something? Please let me know.

    Thanks,

    Kumkum

  • Hi Prasath,

    I have used this dynamic file path selection, but after that the structure file is not getting generated along with the original file. Can I know why and how to get it generated?

    Thanks & regards,

    Mohammed Naveed.

  • Hi people,

    how is it possible to use in the filename the name of the DTP instead (or another parameter present in the DTP filter), allowing me to distinguish the generated file?

    I have 1 Open Hub, with 3 DTPs being executed with distinct parameters, and I really need to distinguish the files.

    This thread almost answers it, but I need to pass at least the DTP name to the FILENAME_EXIT_name function module.

    thank you 😉

    Rúben

  • Hi Prasanth,

    Thanks for the document. But i have a doubt, in the above mentioned scenarios i can see two document created, one with file name we gave and one with s_file name. I hope one is header and one is the normal content. But how is the header file getting generated, i meant where do we do the setting for the same?

    Regards,
    Vineet