Creating Pipe Delimited file using OTBI Report

Creating Pipe delimited file using OTBI Report

– By Amitesh Kumar

There are instances when we want to create a simple pipe delimited file for integration purpose and don’t want to use RTF template or write complex SQLs in BI Publisher to achieve the same. Oracle provides a quick way to use an analysis as a data source instead of SQL and the interactive viewer to create a pipe delimited file. Following are the steps:

1. Create an Analysis

Navigate to ‘Report & Analytics’ from the ‘Navigator’ screen and  Click on ‘New’ and select ‘Analysis’

Select ‘Person Real Time’ Subject Area (Complex OTBI reports can be created by joining subject areas)

Drag and Drop the fields needed and save

2.  Create a Data Model

Click on ‘New’ and select ‘Data Model’ from the drop down

Select ‘Oracle BI Analysis’ as a Data Source under ‘Data Sets’

Select the OTBI analysis created in the above step. Leave the ‘Time Out’ field as blank. The field is used in to limit the processing time for an analysis if the process is taking too long at runtime

Save the data model.

View the data and save the result as a sample data

Click on ‘Create Report’

3. Create a Report

Follow the train stop and drag and drop the field from the Data model to Create a report

Click on Property and scroll down to select Pipe as ‘CSV Delimiter’ and ‘True’ for ‘Remove Leading and Trailing White space’ field

Click on ‘view list’ and Deselect all the formats under ‘Output Formats’ column and select only ‘Data (CSV)’

Click on ‘Save’ icon and view report

As you can see the above report is in pipe delimited format and can be either exported or copied onto a notepad. The report can be scheduled just like any other BI Publisher report to place the file on an FTP server. As you will notice, the header for the file above shows generic values, the header name can be changed in ‘Data Model’ to reflect the correct column names.