Dynamic Determination of File Name in OHDs/APDs

[Former Member]

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

 

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”.

Add an entry with the logical file path and a description

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”

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.

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.

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”

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.

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) 

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

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

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.