oracle business activity monitoring...preface x oracle bam enterprise link transforms reference...

Oracle® Business Activity Monitoring Enterprise Link Transforms Reference Guide

10g Release 3 (10.1.3.1.0)

October 2006

Oracle Business Activity Monitoring Enterprise Link Transform’s Reference Guide, 10g Release 3 (10.1.3.1.0)

Copyright © 2002, 2006 Oracle. All rights reserved.

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Oracle BAM Enterprise Link Transforms Reference Guide iii

ContentsPreface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix

Copyright Information from Group 1 Software . . . . . . . . . . . . . . . . . . xi

Chapter 1: Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

About Transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2

Overview of Transform Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Multipass and Single Pass Processing . . . . . . . . . . . . . . . . . . . . . . . 7Copied and Inplace Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8General Rules for Improved Performance . . . . . . . . . . . . . . . . . . . . 10Transforms and Unknown Schemas . . . . . . . . . . . . . . . . . . . . . . . . 11Summary of Transform Characteristics . . . . . . . . . . . . . . . . . . . . . . 12Other Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Chapter 2: Data Source Transforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Delimited Text File Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19How to Use the Delimited Text File Source Transform . . . . . . . . . . 20Settings in the Delimited Text File Source Dialog . . . . . . . . . . . . . . 22Tips for the Delimited Text File Source Transform . . . . . . . . . . . . . 23Delimited Text File Source Characteristics . . . . . . . . . . . . . . . . . . . 24

Flat File Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25Settings in the Flat File Source Dialog. . . . . . . . . . . . . . . . . . . . . . . 25Tips for the Flat File Source Transform . . . . . . . . . . . . . . . . . . . . . . 37Supported Input Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Supported Output Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Flat File Source Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

OLEDB Source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Settings in the OLEDB Source Dialog . . . . . . . . . . . . . . . . . . . . . . . 41Tips for the OLEDB Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42OLEDB Source Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Oracle BAM Enterprise Message Receiver . . . . . . . . . . . . . . . . . . . . . . 43Settings in the Oracle BAM Message Receiver Dialog . . . . . . . . . . 44Oracle BAM Message Receiver Characteristics . . . . . . . . . . . . . . . 44

Contents

iv Oracle BAM Enterprise Link Transforms Reference Guide

Oracle BAM Reader . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Settings in the Oracle BAM Reader Dialog . . . . . . . . . . . . . . . . . . . 45Oracle BAM Reader Characteristics . . . . . . . . . . . . . . . . . . . . . . . . 46

SQL Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Settings in the SQL Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Using the SQL Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49Tips for the SQL Query Transform. . . . . . . . . . . . . . . . . . . . . . . . . . 50SQL Query Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

VBScriptSource . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

XML Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53Settings in the XML Source Transform . . . . . . . . . . . . . . . . . . . . . . 55XML Source Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Chapter 3: Display Sink Transforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63

Grid. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Chapter 4: Non-Display Sink Transforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Oracle BAM Alert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66Settings in the Oracle BAM Alert Dialog . . . . . . . . . . . . . . . . . . . . . 67Oracle BAM Alert Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . 68Using the BAM Alert Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Oracle BAM Delete. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69Settings in the Oracle BAM Delete Transform . . . . . . . . . . . . . . . . . 69Oracle BAM Delete Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . 71

Oracle BAM Insert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Settings in the Oracle BAM Insert Dialog. . . . . . . . . . . . . . . . . . . . . 73Oracle BAM Insert Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . 73

Oracle BAM Message Sender . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Settings in the Oracle BAM Message Sender Dialog. . . . . . . . . . . . 75Oracle BAM Message Sender Characteristics. . . . . . . . . . . . . . . . . 75

Oracle BAM Message Tracker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76Settings in the Oracle BAM Message Tracker Dialog . . . . . . . . . . . 77Oracle BAM Message Tracker Characteristics . . . . . . . . . . . . . . . . 77

Oracle BAM Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78Settings in the Oracle BAM Update Dialog . . . . . . . . . . . . . . . . . . . 78Oracle BAM Update Characteristics . . . . . . . . . . . . . . . . . . . . . . . . 82

Terminal Sink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83

Contents

Oracle BAM Enterprise Link Transforms Reference Guide v

Chapter 5: Data Flow Control Transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . . 85

Comparison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Settings in the Comparison Dialog. . . . . . . . . . . . . . . . . . . . . . . . . . 87Tips for the Comparison Transform . . . . . . . . . . . . . . . . . . . . . . . . . 90Comparison Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90

Conditional Splitter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91Settings in the Conditional Splitter Dialog . . . . . . . . . . . . . . . . . . . . 92Tips for the Conditional Splitter Transform . . . . . . . . . . . . . . . . . . . 93Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93Conditional Splitter Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . 94

Join. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95Settings in the Join Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96Tips for the Join Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99Join Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

Round Robin Splitter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

Splitter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

SubPlan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102Settings in the SubPlan Iteration Dialog . . . . . . . . . . . . . . . . . . . . 104Tips for the SubPlan Transform . . . . . . . . . . . . . . . . . . . . . . . . . . . 106SubPlan Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

Union . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

Chapter 6: Data Manipulation Transforms. . . . . . . . . . . . . . . . . . . . . . . . . . . 109

Add Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111Settings in the Add Columns Dialog . . . . . . . . . . . . . . . . . . . . . . . 112Tip for the Add Columns Transform. . . . . . . . . . . . . . . . . . . . . . . . 113Add Columns Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

Column Select . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114Settings in the Column Select Dialog. . . . . . . . . . . . . . . . . . . . . . . 115Tips for the Column Select Transform . . . . . . . . . . . . . . . . . . . . . . 116Column Select Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . . 116

Concatenation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117Settings in the Concatenation Dialog. . . . . . . . . . . . . . . . . . . . . . . 118Concatenation Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

Disk Sort. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120Settings in the Disk Sort Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . 121Tips for the Disk Sort Transform . . . . . . . . . . . . . . . . . . . . . . . . . . 123

Contents

vi Oracle BAM Enterprise Link Transforms Reference Guide

Disk Sort Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

Expression Calculator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124Settings in the Expression Calculator Dialog . . . . . . . . . . . . . . . . . 125Creating an Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Tips for the Expression Calculator . . . . . . . . . . . . . . . . . . . . . . . . . 130Expression Calculator Characteristics . . . . . . . . . . . . . . . . . . . . . . 132

Filter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Settings in the Filter Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Tips for the Filter Transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134Filter Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134

Key Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135Settings in the Key Generation Dialog . . . . . . . . . . . . . . . . . . . . . . 135Tip for the Key Generation Transform . . . . . . . . . . . . . . . . . . . . . . 136Key Generation Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Key Lookup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137Settings in the Key Lookup Dialog . . . . . . . . . . . . . . . . . . . . . . . . . 138Tips for the Key Lookup Transform . . . . . . . . . . . . . . . . . . . . . . . . 139Key Lookup Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140

Memory Sort. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141Settings in the Memory Sort Dialog . . . . . . . . . . . . . . . . . . . . . . . . 142Tips for the Memory Sort Transform . . . . . . . . . . . . . . . . . . . . . . . 142Memory Sort Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

Message Digest . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144Key Construction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144Settings in the Message Digest Transform . . . . . . . . . . . . . . . . . . 145Using the Message Digest Transform . . . . . . . . . . . . . . . . . . . . . . 147

Oracle BAM Lookup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148Settings in the Oracle BAM Lookup Dialog . . . . . . . . . . . . . . . . . . 149Oracle BAM Lookup Characteristics . . . . . . . . . . . . . . . . . . . . . . . 150

Pivot . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151Settings in the Pivot Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151Tips for the Pivot Transform. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155Pivot Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

Rank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Settings in the Rank Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156Rank Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158

Record Number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159

Contents

Oracle BAM Enterprise Link Transforms Reference Guide vii

Settings in the Record Number Dialog. . . . . . . . . . . . . . . . . . . . . . 159Record Number Characteristics. . . . . . . . . . . . . . . . . . . . . . . . . . . 159

Rename Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160Settings in the Rename Columns Dialog . . . . . . . . . . . . . . . . . . . . 160Tips for the Rename Columns Transform . . . . . . . . . . . . . . . . . . . 161Rename Columns Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . 161

Search & Replace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162Settings in the Search & Replace Dialog . . . . . . . . . . . . . . . . . . . . 163Tips for the Search & Replace Transform . . . . . . . . . . . . . . . . . . . 163Search & Replace Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . 164

Substring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165Settings in the Substring Dialog. . . . . . . . . . . . . . . . . . . . . . . . . . . 166Tips for the Substring Transform . . . . . . . . . . . . . . . . . . . . . . . . . . 166Substring Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

Transpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167Settings in the Transpose Dialog . . . . . . . . . . . . . . . . . . . . . . . . . . 168Tips for the Transpose Transform . . . . . . . . . . . . . . . . . . . . . . . . . 169Transpose Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170

VBScriptCopy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171

VBScriptInplace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172

Appendix A: Expression Calculator Functions . . . . . . . . . . . . . . . . . . . . . . . . 173

Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174

String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

Mathematical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179

Logical Operators. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

Binary Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182

Miscellaneous Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183

Calculator Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184Relational and Equality Operators . . . . . . . . . . . . . . . . . . . . . . . . . 184Unary Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184

Appendix B: Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

Decimal Math Calculations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186Automatic vs. Manual Setting of Precision and Scale . . . . . . . . . . 187Treatment of Literals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189Switching Decimal Math On and Off . . . . . . . . . . . . . . . . . . . . . . . 189

Contents

viii Oracle BAM Enterprise Link Transforms Reference Guide

Expression Calculator Functions that Use Decimals . . . . . . . . . . . 189

Supported Binary Input Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

Oracle BAM Enterprise Link Transforms Reference Guide ix

Preface

In this section:

Who Should Read this Manual

Conventions

How to Proceed

Preface

x Oracle BAM Enterprise Link Transforms Reference Guide

Who Should Read this Manual

This manual is intended for data designers and application programmers who work in a Oracle BAM Enterprise Link environment. You are a power user of Oracle BAM Design Studio and want to build complex Plans with Transforms. You know your particular database environment and are familiar with concepts of database schema design, data movement and dimensional modeling.

If you are new to Oracle BAM Design Studio, you must read the Oracle BAM Enterprise Link Design Studio User’s Guide before reading this manual.

Conventions

The following conventions are used in this document:

Keys on the keyboard are referred to by name, such as Escape and Enter.

Commands you type or items you click with the mouse are printed in bold, such as clicking Update or typing yes.

Machine names or other variables that you must provide are referred to using <machine name>. Do not type the brackets (<>).

Paths, file names, and code appear in Courier font, such as C:\Oracle BAM Enterprise Link.

Preface

Oracle BAM Enterprise Link Transforms Reference Guide xi

How to Proceed

This manual is a reference guide for Transforms and provides descriptions of settings plus additional information needed during the application of Transforms. Transforms are grouped into chapters based on the types of Transforms. Use the following summary to determine which chapters you need to read:

Chapter 1, “Introduction” introduces Transforms and additional concepts for managing Transforms and data flows.

Chapter 2, “Data Source Transforms” describes the Transforms that select data from source databases.

Chapter 3, “Display Sink Transforms” describes the Transforms used to display data at the end of Plans.

Chapter 4, “Non-Display Sink Transforms” describes the Transforms that end Plans without a display of the results.

Chapter 5, “Data Flow Control Transforms” describes the Transforms that change the structure of data flows.

Chapter 6, “Data Manipulation Transforms” describes the Transforms that manipulate and process data within the data flow.

Appendix A, “Expression Calculator Functions” lists the functions you can use in the Expression Calculator.

Appendix B, “Data Types” discusses Decimal Math, and lists the supported binary input data types for the Flat File Source Transform.

Copyright Information from Group 1 Software

Information in this document is subject to change without notice. Companies, names and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Group 1 Software, Inc.

Copyright 1996-2004 Group 1 Software, Inc. All rights reserved.

Preface

xii Oracle BAM Enterprise Link Transforms Reference Guide

Trademark Notices:

Information Studio and Sagent are registered trademarks of Group 1 Software, Inc. The Sagent Logo, Sagent Design Studio, Flashcube, and StarMart are trademarks of Group 1 Software, Inc. All other trademarks are property of their respective companies.

Any provisions of the Sagent license related to ICU that differ from the IBM Public License 1.0 are offered by Sagent alone and not by any other party. The Source Code of the ICU program is available from Sagent upon written request. Further information regarding the ICU Program may be found at: http://oss.software.ibm.com/icu/.

You may make up to three copies of this publication, without the express written permission of Group 1 Software, for use at the authorized installation site. Reproduction and distribution outside of the licensed site is strictly prohibited. The contents of this Guide are subject to the confidentiality and non-disclosure provisions in your license agreement with Group 1 Software.

Group 1 products are warranted to perform as documented on hardware platforms running versions/releases of operating systems, subsystems, compilers, and facilities that are currently supported by their manufacturers. Please see your license agreement for additional details.

Oracle BAM Enterprise Link Transforms Reference Guide 1

Chapter 1

Introduction

In this chapter:

About Transforms

Overview of Transform Processing

About Transforms

2 Oracle BAM Enterprise Link Transforms Reference Guide

About Transforms

Transforms provide the data extraction, transformation, and load capabilities for Oracle BAM Enterprise Link. By selecting Transforms for particular situations, you can select data from different sources, perform calculations on data, make changes to the data schema, and load data to other target databases. You can analyze real-time and historical data and view data in charts or reports.

Transforms are the building blocks of a Plan. Each Transform performs specialized operations and functions as either a source, step or sink Transform. Source Transforms generate data from the data source, steps process input and output records, and sinks end the data flow by loading, saving or displaying results. You build Plans by adding Transforms to the data flow.

Plans operate on a pull model. Conceptually, Plans execute backwards from the sinks. Transforms do not process data until a downstream Transform requests the data. At runtime, sinks begin Plan execution by requesting data from the next upstream Transform. This request for data reaches a source Transform, which delivers data downstream for processing.

Produces OutputDelivers DataGenerates Data

Plan ExecutesRequests Data

Delivers Data

Requests DataRequests DataReceives Request

About Transforms

Chapter 1: Introduction 3

Transforms are grouped by type in the Tool Bin. The following sections provide quick reference tables listing each Transform and a description.

Data Source Transforms

Display Sink Transforms

Non-Display Sink Transforms

Data Flow Control Transforms

Data Manipulation Transforms

Note: Your installation might not include all of the Transforms in this guide. The Transforms in the Tool Bin vary depending on the Oracle BAM Enterprise Link products you licensed.

About Transforms



The following Transforms specify the source of the data:


The following Transforms display data and are also referred to as client sinks:

Non-Display Sink Transforms

The following Transforms specify a destination for data without a display:

Transform Name DescriptionDelimited Text File Source Imports delimited fields from a source file

Flat File Source Imports delimited or fixed length fields from a source file

OLEDB Source Connects to OLE DB provider as the data source

Oracle BAM Enterprise Message Receiver

Continuously processes the flow of real-time information from an enterprise message source

Oracle BAM Reader Reads data from a data object and sends the records to the next step

SQL Query Retrieves database records or records from other locations accessible through SQL

VBScriptSource Receives input data from an external application using VBScript

XML Source Imports XML files from a source file

Transform Name DescriptionGrid Displays results in column format

Transform Name Description

Oracle BAM Alert Launches specified alerts based on conditions of the Plan

Oracle BAM Delete Deletes records from a data object in the ADC

Oracle BAM Insert Inserts the records it receives into a data object in the ADC

About Transforms


Data Flow Control Transforms

The following Transforms define the structure of the data flow for the Plan:

Data Manipulation Transforms

The following Transforms process data:

Oracle BAM Message Sender

Inserts the records it receives into a data object in the ADC

Oracle BAM Message Tracker Tracks messages for global transactions

Oracle BAM Update Updates records in a data object in the ADC

Terminal Sink Ends a data flow with no display or further action


Comparison Compares two sets of input records and identifies matching records

Conditional Splitter Uses filter conditions to divide a data flow

Join Joins the columns from two data flows into one

Round Robin Splitter Distributes data among the specified number of outputs

Splitter Creates two identical copies of data flow output

SubPlan

Organize a Plan by simplifying its graphical representation and grouping steps in the Plan hierarchically. Also provides iterative processing of data flows

Union Merges two data flows into one

Transform Name DescriptionAdd Columns Adds one or more empty columns

Column Select Removes and reorders columns

Concatenation Combines column data and typed text

Disk Sort Sorts columns using disk files

Expression Calculator Uses expressions to add calculated columns


About Transforms


Filter Removes rows using filter conditions

Key Generation Generates unique numeric key values

Key Lookup Looks up a value in one table and inserts into another table

Memory Sort Sorts records in columns using memory

Message Digest Builds keys based on any type of input data

Oracle BAM Lookup Performs lookups on key fields from data in a data flow and data in a data object

Pivot Aggregates and arranges columns in crosstab format

Rank Outputs top or bottom ranked rows of a column

Record Number Adds a column with sequential numbers

Rename Columns Changes column names

Search & Replace Replaces a string or substring with another string

Substring Extracts a substring from a string value

Transpose Arranges columns in row format

VBScriptCopy Copies records so that record attributes and data can be changed using VBScript

VBScriptInplace Modifies records by changing data values only using VBScript





This section describes how Transforms process data and how this can affect Plan performance. Plan performance is how efficiently a specific Plan completes the requested processing of records.

The complexity of a Plan and the amount of data that must be processed by the available resources affects Plan performance. The combination and order of Transforms in a Plan also affect performance. By understanding the ways Transforms process data, you can make better decisions about how to design efficient Plans.

If a Transform can process one record without referring to other records, minimal resources are devoted to record storage. One record, or a block of records, is processed at a time and delivered to the next Transform. Some Transforms need all the records before they can continue processing. For example, a sorting Transform accumulates all the input records before it sorts and processes the records.

Multipass and Single Pass Processing

A Transform processes records in a single pass or in multiple passes of the same records.

Multipass Transforms complete more than one pass over data and sometimes reformat the records. Multipass Transforms buffer input records by maintaining them in memory or on disk as they read them. This requires more resources than single pass Transforms.

An example of a multipass Transform is the Percent of Total Transform. The Percent of Total Transform completes one pass of the data to compute a total and a second pass to compute the percentage of the total for each value.

Copied and Inplace Processing

A Transform either copies records in order to produce new output or processes the records inplace. Inplace processing changes the values of records but does not change the record size or add or delete fields.



Copied Transforms can reformat records for output. Records are stored and processed in blocks. Copied Transforms read one set of blocks as input and produce a second set of blocks as output. The blocks produced as output can have a different format from the input blocks. Fields can change size and values, records can change size, and fields can be added or deleted. Most Transforms that manipulate string values are Copied Transforms. Copied Transforms require more memory resources than inplace Transforms because of the need to copy input blocks to output blocks. Copied Transforms can be single pass or multipass Transforms.

Several Transforms perform inplace processing if you choose to produce output in an existing field and perform copied processing if you choose to place the output in a new field.

When you add a new field for the output in the Expression Calculator, it behaves as a single pass, copied Transform. The new fields you create in the Expression Calculator cause the records to be reformatted and a set of output blocks to be created at runtime. This is a single pass Transform because the Transform operates only once on each record and performs all calculations within the context of a single record.

Sorting

Some Transforms require sorted input. Sorting can be performed at different stages of a Plan. A sort can be performed in the SQL Query, as a disk sort, or internally as an in-memory sort.

The Plan stage where sorting occurs affects the amount of resources required. Sorting performed at the SQL Query stage requires fewer resources. Sorting performed internally requires all data to be copied into memory or on disk and therefore uses more resources.

Plans follow specific rules when determining where to perform sorting. A sort is automatically performed at the SQL Query stage unless one of the following conditions exist:

There is no SQL Query in the Plan.

The SQL Query is user-entered, or contains an ORDER BY clause created by the user.



The operations of intermediate Transforms, which are located between the SQL Query and the Transform requiring the sort, also affect where the sort is performed. A sort is not performed at the SQL Query stage if an intermediate Transform does one of the following:

alters any of the sort fields

requests sorted data

adds or deletes records

originates a sort field

contains multiple inputs or outputs

When any of these conditions exist, the sorting is performed either as a disk sort or internally. If the Plan has at least one non-display sink (a sink that has no display output), a disk sort is performed. The disk sort occurs automatically when the Plan runs and is not displayed in the data flow of the Plan.

If the only sinks in the Plan are display sinks, the sorting is performed internally by the Transform requiring the sorting. Sorting internally requires more memory than sorting using a disk sort or a SQL Query.

The following Transforms require sorting:

Join

Comparison

Rank

Pivot

Memory Sort

Disk Sort



General Rules for Improved Performance

To improve Plan performance, consider making some of the following changes to your Plans:

For data that is already sorted, use the Data is pre-sorted check box in the Join, Comparison, Rank, and Pivot Transforms. You can improve Plan performance by removing unnecessary sorting.

Add the Add Columns Transform near the beginning of the data flow to specify all new columns for the Plan in one step. This placement of the Transform uses fewer resources by enabling other Transforms to process data inplace. For example, the Expression Calculator can function as an inplace Transform because it can place the calculated output in the columns created by the Add Columns Transform.

Use single pass Transforms when possible. You can separate the operations of a multipass Transform into separate single pass Transforms. For example, if a Percent of Total step requires a lot of memory and slows down your Plan, run two separate Plans. The first Plan computes the total using the Pivot Transform in non-pivot mode. The second Plan computes the percent using the Expression Calculator Transform. You can achieve better performance using these two separate Plans instead of one using the Percent of Total Transform.

Another example is replacing a Key Lookup Transform with a Join Transform. If the lookup table in the Plan is large compared to the amount of input, or if the values you are looking up are not often repeated in the input, a Key Lookup is resource intensive and slows down the Plan. Perform a match/merge operation with a Join Transform rather than a random access retrieval with a Key Lookup Transform.



Create several expressions in one Expression Calculator instead of adding several Expression Calculator Transforms.

If a load Plan has performance issues at the loading step and the database server allows parallel loading, try using a Round Robin Splitter connected to several batch loaders loading to the same table. You can test your loading step by replacing the batch loader with a Terminal Sink to check if the execution time improves significantly.

Transforms and Unknown Schemas

The schema contains information for the data structure of the records. Each column has specific attributes such as size and data type. This information builds the data schema and must be kept intact when processing and loading data.

When a data flow includes a known schema, Transform dialogs can list the columns by the Part name in the Plan and provide user interface features for selecting and manipulating the column. Some Transforms display a different dialog when the schema is unknown. These dialogs require you to type the Part or column name to specify it in the Transform. The causes of unknown schemas are:

a data flow with unconnected Transforms

user-entered SQL

a Pivot Transform does not provide the output schema

You can add an Add Columns Transforms after the Pivot Transform or in other data flows to add the columns so that the downstream Transforms can recognize and display column names. A named-based matching is performed when the Plan is run.

Examples of Transforms that function differently with unknown schemas include:

Column Select

Disk Sort

Memory Sort



Rename Columns

Transpose

Summary of Transform Characteristics

For each Transform described in this guide, a reference table lists basic Transform characteristics. These characteristics describe the types of data processing that the Transform performs.

Refer to Table 1 for a definition of the characteristics. Understanding how Transforms process data can help you make decisions to improve Plan performance.

For the complete table of all Transforms and their characteristics, see Table 2.

Table 1: Definitions of Transform Characteristics

Transform Characteristic Description

Package typeTransforms are sold with the Oracle BAM Enterprise Link server. Additional Transforms are sold separately as the Power Products Transforms.

Alters schema Transforms can change the structure of the data schema passed through the data flow.

Multipass or single pass processing

Transforms can process records in a single pass or in multiple passes on the same records. Depending on the Plan, multipass Transforms can require additional resources that you might need to account for.

Copied or Inplace

Transforms can process records by changing the record inplace or by creating a new copy of the records to process. Some Transforms can function either way based on specified settings.

Sorted input Transforms can request that the input is sorted before it is processed.

Exposed propertiesTransforms have exposed properties that enable you to set values when the Plan is run, such as in Plan prompts and filters.



The following table lists the characteristics for all Transforms.

Table 2: Transforms and their characteristics

TransformPackage Type

Alters Schema

Multipass or Single Pass

Copied or Inplace

Sorted Input

Exposed Properties

Add Columns Basic Yes Single pass Copied No Some

Analytical Calculator Power Product Yes

Either one depending on function

Copied Yes None

Column Select Basic Yes Single pass Copied No Some

Comparison Basic Yes

Multipass, releases memory as processing

Inplace Yes Some

Concatenation Basic Yes Single pass Copied No Some

Conditional Splitter Basic Yes Single pass Copied No All

Disk Sort Basic No Multipass Copied Yes Some

Expression Calculator Basic Yes Single pass

Inplace if updating existing field, otherwise, Copied

No None

Filter Basic No Single pass Copied No All

Flat File Source Basic Yes Single pass Copied No Some

Grid Basic No N/A N/A Yes None

Join Basic No

Multipass, releases memory as processing

Copied Yes Some

Key Generation Basic Yes Single pass Copied No Some

Key Lookup Basic Yes Single pass

Inplace if updating existing field, otherwise, Copied

No Some

Memory Sort Basic No Multipass Copied Yes Some

Message Digest Basic No Single pass Copied No Some

OLEDB Source Basic No Single pass Copied No Some



Pivot Basic Yes Multipass Copied Yes Some

Rank Basic Yes Multipass Copied Yes All

Record Number Basic Yes Single pass Copied No All

Rename Columns Basic Yes Single pass Inplace No Some

Round Robin Splitter Basic Yes N/A N/A No N/A

Search & Replace Basic Yes Single pass Copied No All

Splitter Basic Yes N/A N/A No N/A

SQL Query Basic Yes Single pass Copied N/A All

SubPlan Basic N/A N/A N/A Varies N/A

Substring Basic Yes Single pass Copied No All

Terminal Sink Basic No Single pass N/A No N/A

Transpose Basic Yes Single pass Copied No All

Union Basic No N/A N/A No N/A

VBScriptCopy Basic Yes Single pass Copied No None

VBScriptInplace Basic No Single pass Inplace No None

VBScriptSource Basic Yes Single pass Copied No None

XML Source Basic Yes Single pass Copied N/A None

Table 2: Transforms and their characteristics (continued)

TransformPackage Type

Alters Schema

Multipass or Single Pass

Copied or Inplace

Sorted Input

Exposed Properties



Other Recommendations

This section includes general tips and additional information for working with Transforms and Oracle BAM Design Studio.

Saving Transforms for Reuse

You can save Transforms as custom Transforms in the Tool Bin. Custom Transforms save Transform settings so you avoid specifying the settings again. For example, you might save a SQL Query Transform as a custom Transform if you have edited SQL syntax. Other users with access to the Tool Bin can use the Transforms you save. Right-click the Transform step and select Create Transform to save a copy of your Transform and settings.

Note: All Transforms can be saved for reuse except the following:

Adding Comments to Your Plan

You can add comments to each step in your Plan to provide additional information about its purpose.

To view and edit comments:

1. Right-click on a step in the data flow and select About Step “<step name>”.

The About Step dialog displays.

2. Type a description of the step in the comments field.

3. Click OK to save the information.

Specifying File Paths in Transform Dialogs

In some Transform dialogs, you must specify paths to files so that they can be used for a Plan. If the Data Flow Service is running under the system account, which is also the default account, you must specify the path relative to the Data Flow Service machine. If the file is on a

Table 3: Transforms that cannot be saved for reuse

Grid Splitter

Union



separate machine, the file must be in a shared directory on a network machine. Mapped drives cannot be found. For example, a file path to a machine named Mars could be \\Mars\CDrive\Program Files\Oracle BAM Enterprise Link\Save.csv.

Standard Format and Transforms

Transforms store property values internally in the Standard Format with decimal points as decimal separators and commas as list separators and thousands separators.

When setting property values other than in the Transform dialogs, you must use the Standard Format.


Chapter 2


In this chapter:


Delimited Text File Source Imports delimited fields from a source file

Flat File Source Imports delimited or fixed length fields from a source file

OLEDB Source Connects to OLE DB provider as the data source


Continuously processes the flow of real-time information from an enterprise message source

Oracle BAM Reader Reads data from a data object and sends the records to the next step

SQL Query Retrieves database records or records from other locations accessible through SQL


VBScriptSource Receives input data from an external application using VBScript

XML Source Imports XML documents into Oracle BAM Enterprise Link


Delimited Text File Source

Chapter 2: Data Source Transforms 19


Use the Delimited Text File Source to extract delimited values from a text file. You select the path and filename and specify whether to read column names from the first row of the file. You can also use a file that contains sample data to test how field specifications are applied before running the Plan.

Advanced functions include scanning each column for the probable data type and length, or setting these attributes yourself. You can also specify the delimiter, quote and comment characters used in the text file.

Figure 1: Delimited Text File Source dialog



This section includes:

How to Use the Delimited Text File Source Transform

Settings in the Delimited Text File Source Dialog

Tips for the Delimited Text File Source Transform

Delimited Text File Source Characteristics

How to Use the Delimited Text File Source Transform

To set parameters for the Delimited Text File Source Transform:

1. Place a Delimited Text File Source Transform in a data flow and open its dialog.

2. Type the full path and file name of the text file in the File Name on Server field.

3. Select an input character set for the text file.

4. Modify the default special character settings, if necessary:

Select a delimiter character from the Separator Character dropdown list.

Select the character used for distinguishing strings from the Quote Character field.

Select the character used for distinguishing comment rows from the Comment Character dropdown list.

5. Click the Column Names in File check box if the first line of the text file contains column names.

6. Click the Extended Character Set check box if you are using special characters outside of alphanumeric characters.

7. If you want to autopopulate the Field List with column names or data types from the input file, click Column Names or Data Types, or both in the Auto-Populate Field List area.

8. At the time the Plan is run, you can override selections on this Transform with the column names and data type in the file by clicking the Populate at Runtime check box.



9. Click the Disable numeric format checking check box to indicate that data is already numerically formatted correctly.

10. If you want to specify a date format, click the Use Date Format check box and select a date format from the dropdown.

11. Click Add or double-click a Col No value to add an input field in the Field List.

The Field Specification dialog displays.

12. Type the name of the column to create in the target database in the Column Name field.

13. Select the correct output type for this field from the Output Data Type dropdown list.

14. Specify characteristics of the output column.

15. Click OK to save the field specification.

The Field List displays the new entry.

16. Repeat Steps 11 through 15 for each field in the input file.

17. (optional) If you want to view how the settings in this dialog affect data in the input file, create a sample input file on your client machine and specify it in the File Name field. This file should have the same characteristics as the input file on the Enterprise Link Server, but it only needs to contain a representative amount of data.

Any changes to field specifications are dynamically applied to the sample input file.

18. Click OK to close the dialog.



Settings in the Delimited Text File Source Dialog

To define your Delimited Text File Source use the following tables.

Table 4: Delimited Text File Source - File Attribute settings

Setting Description

File Name on Server Type the name of delimited text file. Specify this path as local to the Enterprise Link Server.

Character Set

Select the type of characters used from the dropdown. Select ANSI if the text file was created with a Windows text editor or character set, or select OEM to convert the character set to ANSI format.

Separator Character Select the type of separator character used from the dropdown.

Quote Character

Select the type of quote character used from the dropdown. The Transform removes the quote characters from the display of string values and uses any characters, including commas, between quote characters as part of the string value.

Comment CharacterSelect the type of comment character used from the dropdown. The Transform removes comment lines from the display of rows.

Column Names in File

Select this check box if file contains column names. Do not click this check box if the first line contains values other than column names. The Transform assigns column names. If there are no column names in the file, the Transform assigns names such as Column1 and Column2.

Extended Character Set Select this check box if you are using special characters outside of alphanumeric characters.

Table 5: Delimited Text File Source - Field List settings

Setting Description

Add Click to add a column and set specifications. The Field Specification dialog displays.

Remove Select an item from the column number list and then click this button. The column is removed.



Figure 2: Field Specification dialog

Tips for the Delimited Text File Source Transform

This Transform is recommended if you are selecting data from a clean delimited text file. If the selected data is complex, use the Flat File Source Transform because it provides more settings for managing data.

You can use the Delimited Text File Source to rename and remove columns which eliminates extra steps later in the Plan.

Table 6: Field Specification settings

Setting DescriptionColumn Name Type the column name.

Output Data Type Select the output data type.

Length Specify the length of the string output data.

Precision

Specify the number of digits in the decimal output, including the whole number and fractional parts. The more digits, the more precision. The Precision value must be less than or equal to 38.

ScaleSpecify the position of the decimal point in a fixed or floating point number. The Scale value must be less than or equal to the Precision.

Table 7: Delimited Text File Source - Sample Data Settings

Setting Description

File Name Select a file name and sample data displays in the lower half of the dialog.

Browse Use this to browse to find your file.

Sample Data Display Data is displayed in the output format.



You can manipulate the data type. For example, you might have the date as a string data type and choose to output it as a datetime type. You can have numbers that are input as characters and output as integers.

Start with the sample data section. Browse for your file and check column settings. If they are correct, copy and paste the file name from the sample data section to the top. Any changes you make to the column specifications are dynamically applied to the sample display.

In the Delimited Text File Source, empty string fields do not get the NULL field flag set.

Delimited Text File Source Characteristics

Load or Access server Both

Alters schema Yes

Multipass or Single pass Single pass

Copied or Inplace Copied

Sorted input No

Exposed properties Some

Flat File Source


Flat File Source

Use the Flat File Source to extract values from delimited, fixed length and binary flat files. File Transfer Protocol (FTP) streaming allows you to process files on remote systems without waiting for a file transfer to complete. The Flat File Source Transform handles multiple record types. You can add columns to store values that do not match expected data types without interrupting the data flow. The added columns allow error checking and correction.


Settings in the Flat File Source Dialog

Tips for the Flat File Source Transform

Supported Input Data Types

Supported Output Data Types

Flat File Source Characteristics

Settings in the Flat File Source Dialog

To define your Flat File Source Transform, open the dialog and set the following tabs:

File Select Tab

Attributes Tab

Processing Tab

Tags Tab

Columns Tab

Errors Tab

Flat File Source


Note: For most Plans that include Flat File Sources, you only need to specify settings on the File Select, Columns and Errors tabs.

File Select Tab

To define your source file, select the File Select tab and use Table 8 to guide your selections.

Figure 3: File Select tab dialog

Table 8: File Select tab settings

Setting Description

Source File Path or URL

Browse to the location of your source file on the Data Flow Service machine. If this file is not located on the Data Flow Service machine, specify a full path relative to this machine.You can use a URL if the source file is on a machine accessible through FTP or HTTP.

UserIf the source file is located on another machine or access is restricted, type a valid user name to access it.

Flat File Source


PasswordIf the source file is located on another machine or access is restricted, type a valid password to access it.

Sample File Path or URL

Browse to the location of your sample file on the Data Flow Service machine. If this file is not located on the Data Flow Service machine, specify a full path relative to this machine.You can use a URL if the source file is on a machine accessible through FTP or HTTP.

UserIf the sample file is located on another machine or access is restricted, type a valid user name to access it.

PasswordIf the sample file is located on another machine or access is restricted, type a valid password to access it.

View DataClick on this button to populate the sample data display. Any changes to field specifications are dynamically applied to the sample input file.

Validate Click this to validate file format settings.

Table 8: File Select tab settings (continued)

Setting Description

Flat File Source


Attributes Tab

To define what type of source file you are using, select the Attributes tab and use Table 9 to guide your selections.

Figure 4: Attributes tab dialog

Table 9: Attributes tab settings

Setting Description

Delimited Text Select this option if your source file is a delimited text file.

Fixed-Length Text Select this option if your source file is a fixed-length file.This is the default selection.

Binary Select this option if your source file is a binary file.

Record Size If you select binary as your record type, you must specify record size.

Character Set Select the character set used in your file from the dropdown.The default value is ANSI.

Field Delimiter Select the field delimiter from the dropdown if you are using a delimited file. The default value is the comma.

Flat File Source


Processing Tab

To specify how you want your source file read, select the Processing tab and use Table 10 to guide your selections. These settings are important for IBM DB2 users.

Figure 5: Processing tab dialog

Record Delimiter Select the record delimiter used in your file from the dropdown. The default value is <<CR and/or LF>>.

QuoteSelect the quote marker from the dropdown if you are using a delimited file. The default value is the double quote.

End of File Marker Select the end of file marker used in your file from the dropdown. The default value is <<CTRL+Z>>.

Comment

Select the comment marker used in your file from the dropdown. The default value is #. Values enclosed by the comment character are removed when the Plan is run.

Table 9: Attributes tab settings (continued)

Setting Description

Flat File Source


Table 10:Processing tab settings

Setting Description

First Line = Column Names Select this check box if you are using a delimited file and your first line contains column headings.

Disable numeric format checking

Check to indicate that data is already formatted correctly. Performance can improve by avoiding the redundant processing.

Mask Select or type the date format you want to use for the date conversions from the dropdown.

Disable date format checkingCheck to indicate that dates are already formatted correctly. Performance can improve by avoiding the additional processing.

Count of Leading Records to Skip

Use this box to specify the number of records at the beginning of the source file to skip. You can either type the number or use the small arrow buttons to set a number.By default, all records are processed.For example, if the Plan previously failed after processing 400 records, you could troubleshoot by specifying a value of 400 in this field to start the Plan running at the point where the error occurred.

Count of Records to Process

Use this field to specify the number of records to process. This allows you to process only a subset of source file records in the data flow. You can either type the number or use the small arrow buttons to set a number. The default of 0 means all records are processed.

Count of Leading Bytes to Skip

If you select binary as your record type, you can specify a different position as the starting position for the file.

Starting Percentage The starting percentage if processing a portion of the file.

Ending Percentage The ending percentage if processing a portion of the file.

Flat File Source


Tags Tab

To define the tags used in the source file, select the Tags tab and use Table 11 to guide your selections.

Note: A tag is a named label that identifies a particular record type. Input records must first be tagged in the source file to use tags in the data flow. For files with multiple record types, you must define a tag for each type of record.

Figure 6: Tags tab dialog

Flat File Source


Columns Tab

To define columns, select the Columns tab and use Table 12 to guide your selections.

Note: If a Plan ends in a Batch Loader and you are loading data by position, reorder the fields to match the target table. To reorder columns and rows, click and drag to the new position.

Table 11:Tags tab settings

Setting DescriptionFile Contains Multiple (Tagged) Record Type

Select this check box if your file contains multiple record types.

Input Type Select the input data type of the tag field in the source from the dropdown.

Start Specify the start position of the tag field in the source file.

Length Specify the length of the tag field in the source file.

Output Type Select a compatible output data type from the dropdown.

Output ColumnSpecify a name for the output column. The default value is Record Tag. Columns contain the tag values after the Plan is run.

Length If you select string as the output data type, you must specify the length of the output column.

Tag Name Type a label for the tag. This label is used for descriptive purposes only and can be any string.

Value Type the value of the tag from the source record. For example, a code of 01 for a customer name record.

Add Click this button to add another tag row.

Remove Click this button to remove a tag row.

Flat File Source


Figure 7: Columns tab dialog

Table 12:Columns tab settings

Setting Description

Detect Columns

Click this button to detect column names from the input file if this is a delimited file and the First Line=Column Names check box is selected in the Processing tab.

Detect Data TypesClick this button to detect data types from the input file if this is a delimited file and the First Line=Column Names check box is selected in the Processing tab.

Add Click this button to add another column row.

Remove Select a column and click this button to remove the column.

Column NameType the column label for the source field. If you choose to detect data columns, these names are automatically populated.

Input Type Select the data type of the input column.

Output Type Select the data type of the output column. Must be compatible with Input Type.

Flat File Source


Errors Tab

To specify how you want to process rejected and error records, select the Errors tab and use Table 13 to guide your selections.

Start Position Type the starting position of the field.

End Position Type the ending position of the field.

Output LengthType the number of bytes for the output column (for string fields) or total number of digits (for decimal fields).

Implied Input DecimalsType the number of decimal digits to the right of the decimal point in the input column (for decimal type columns).

Output Decimal DigitsType the number of decimal digits to the right of the decimal point for the output column (for decimal type columns).

Error Override Value

Type or select a replacement value if an invalid value is found and Use Override Values is checked on the Errors tab. You can also specify to keep the original value in the data flow. This is helpful for error processing.

Blanks as NullA flag for interpreting blank values as NULL values when the Plan is run. This is helpful for error processing.

Required Field

A flag for requiring this field to have a value in the data flow. If a field in this column has a NULL value, the record is treated as an error when the Plan is run.If this field is checked, null values and errors are captured.

Import

A flag for determining whether to process the column in the data flow. To improve performance select only necessary columns to be imported.

Tag in effect in Sample Display

For fixed length files and binary files, if multiple record types are defined on the Tags tab, you can select a tag from the dropdown in the Record Types Column. Records are separated by Tag with a separate column for each tag value.

Table 12:Columns tab settings (continued)

Setting Description

Flat File Source


Figure 8: Errors tab dialog

Table 13:Errors tab settings

Setting Description

Log File

Type the path and name of an error log file on the Data Flow Service machine. If this file is not located on the Data Flow Service machine, specify a full path relative to this machine.This file contains error messages and explanations generated when the Plan is run. If a file with this name already exists in the specified location, it is overwritten.

Reject File

Type the path and name of a file for storing rejected input records. If this file is not located on the Data Flow Service machine, specify a full path relative to this machine.This file contains records that were rejected because they contain errors. If a field contains only spaces or null values, it is set to NULL.

Flat File Source


Keep Error Record

Select this check box to include error records in the data flow.When this option is selected, records that contain an error are stored in the reject file and kept in the data flow. Override values are substituted for invalid field values. A number field that indicates the error count is added to the end of each record.

Error Count Column Name

Specify a column name for error counting. After the Plan is run, this column contains the total number of invalid fields in each record. The default name is ErrorCount. If the error count column field has no value, no error count column is created.

Keep Original Values

Select this check box to preserve field values in the data flow in their original input form.When this option is selected, the original value is placed in a separate column and kept in the data flow. The processed value is also in the data flow, doubling the number of output columns. This option is useful if another Transform in the Plan, such as Expression Calculator, uses advanced logic to handle errors. Original values are stored in the data flow as string fields and not converted to an output data type. Error values containing a hexadecimal 0 (zero) character are discarded from the data flow.

Original Value Column Prefix

If the Keep Original Values check box is selected, you can specify a prefix for naming the new column with the original input values.The default prefix is Orig_. For example, if an error is found in the Address column when the Plan is run, the address is placed in a new column with the name Orig_Address and the Address value is replaced by an override value.

Fail when Error Limit Exceeded

Select this check box to force the Plan to fail when the error limit set in the Max Errors Allowed field is reached.

Max Errors Allowed

Specify the maximum number of errors you want the Plan to allow before forcing it to fail.A message for each error is stored in a log file on the Data Flow Service machine. For example, to allow 5 errors before the Plan is stopped, specify a value of 6. The default value of 0 means the Plan runs regardless of errors.

Table 13:Errors tab settings (continued)

Setting Description

Flat File Source


Tips for the Flat File Source Transform

You must provide a user name that has read and write privileges for access to the location of the files.

Rearranging the Grid within the Columns tab of the Flat File Source Transform has adverse effects on the output and is not recommended.

The Flat File Source Transform does not support binary data of the Solaris float or Solaris double data types.

When using the Flat File Source Transform with a Fixed Length File, the output record that is added to the Reject File is not identical to the format of the record in the original input file.

If the source file includes dates, use a date mask for best performance.

In the Flat File Source, empty string fields get the NULL field flag set.

Using the Process by Percent of File feature allows the processing of large files in parallel by assigning two or more Flat File Source Transform to work on the same file. Use a Union Transform to merge the incoming data back into a single stream of data. To process the file in two equal parallel parts, add two Flat File Source Transforms to the data flow. In the first Transform, specify 0% to 50% as the start and end percentages. In the second, specify 50% and 100%. The first Transform ending value must overlap with the starting value of the next Transform to records being skipped.

Flat File Source


Supported Input Data Types

For input data, the following data types are supported:

For additional input data types for binary files see “Supported Binary Input Data Types” on page 192.

Supported Output Data Types

For output data, the following column types are supported:

Table 14:Supported data types for input data

Data Type DescriptionCharacter Variable-length sequence of characters.

Character-Trim Variable-length sequence of characters, which will have all trailing white space removed.

Zoned Decimal

Variable-length sequence of digit (0-9) characters. The high-order 4 bits of the final character is modified to encode the number’s sign, as follows. For EBCDIC files, hexadecimal C or F is used for positive values and hexadecimal D is used for negative values. For ASCII files, hexadecimal 3 or F are used for positive values and hexadecimal D is used for negative values.

Table 15:Supported column types for output data

Data Type Description LimitsDateTime Date/time values none

Decimal String of character digits with sign and decimal point

output length cannot be greater than 38 for decimal fieldsthe value for output decimal digits cannot be greater than the value for output length

Double Real number values, including decimals ~ 1.8 x 1023

Integer Integer values ~ +\- 2,000,000,000

Long Integer Integer values ~ +\- 2,000,000,000

Flat File Source


Flat File Source Characteristics

String Alphanumeric values none

Unsigned Integer Positive integer values less than ~ 4,000,000,000

Package type Basic

Alters schema Yes



Sorted input No


Table 15:Supported column types for output data

Data Type Description Limits

OLEDB Source


OLEDB Source

Use the OLEDB Source Transform to connect with an OLE DB provider. The OLEDB Source Transform uses an OLE DB provider as a data source in a data flow. The OLEDB Source Transform accesses sources that provide an OLE DB or an OLE DB for OLAP interface. This Transform supports both MDX and SQL commands for retrieving data from other sources. MDX is a multidimensional query syntax for OLE DB for OLAP and consists of macros defined on top of the SQL language for querying an OLAP data source. You can use several types of simple or complex providers with the OLEDB Source Transform.

Figure 9: OLEDB Source dialog

OLEDB Source



Settings in the OLEDB Source Dialog

Tips for the OLEDB Source

OLEDB Source Characteristics

Settings in the OLEDB Source Dialog

To define your OLEDB Source, open the OLEDB Source dialog and use the following table.

Table 16:OLEDB Source settings

Setting Description

Provider

Select a provider from the dropdown list. If the provider is only installed on the Oracle BAM Enterprise Link Server, type the name of the provider in the field.

Server Type the name of the server to access.

Database/Catalog Specify the name of the database to access.

User Type the user name.

Password Type the password.

Command Type the MDX or SQL command you want to execute.

OLEDB Source


Tips for the OLEDB Source

The OLE DB provider must be installed on both the client machine and the Oracle BAM Enterprise Link Server for the provider name to display in the dialog dropdown list. If the provider is only installed on the Oracle BAM Enterprise Link Server, you must type the name of the provider in the dialog. The server containing the metadata must be running in order to use it as the provider. Other examples of providers include SQLOLEDB for SQL Server and MSDASQL for ODBC connections.

Because the statement is treated like user-entered SQL, you do not see the column names in Transforms following the OLEDB Source Transform in a data flow. You can type column names in other Transforms for additional processing in the data flow.

The following is a simple MDX example:select{[Measures].[Unit Sales]} on columns, order(except([Promotion Media].[Media Type].members,{[Promotion Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows from Sales

OLEDB Source Characteristics

Package type Basic

Alters schema No



Sorted input No





Oracle BAM Transforms perform specific functions for connecting to enterprise message sources and loading data into the ADC. Plans containing an Oracle BAM Message Receiver Transform can be set to run continuously to process the flow of real-time information from an enterprise message source. Use Plan monitoring to monitor the status of these types of Plans.

Figure 10:Oracle BAM Enterprise Message Receiver dialog


Settings in the Oracle BAM Message Receiver Dialog

Oracle BAM Message Receiver Characteristics



Settings in the Oracle BAM Message Receiver Dialog

To define your Oracle BAM Message Receiver, open the Oracle BAM Message Receiver dialog and use the following table.

Oracle BAM Message Receiver Characteristics

Table 17:Oracle BAM Message Receiver settings

Setting DescriptionACD Server Name Type the ADC Server name. The default is Main.

Enterpise Message SourceSelect the enterprise message source name to use. Enterprise message sources are defined through Oracle BAM Architect.

Run ForeverClick the Run Forever checkbox to run this Plan continuously. To keep live data continuously loading into a data object, this option must be checked.

Enable Message LoggingClick the Enable Message Logging checkbox to turn on message logging for guaranteed message delivery to the ADC from message queues.

Data Object Type the data object name to use for message logging.

Package type Basic

Alters schema No



Sorted input No


Oracle BAM Reader


Oracle BAM Reader

The Oracle BAM Reader Transform is a source step that reads data from a data object and sends the records to the next step in the data flow.

Figure 11:Oracle BAM Reader dialog


Settings in the Oracle BAM Reader Dialog

Oracle BAM Reader Characteristics

Settings in the Oracle BAM Reader Dialog

To define your Oracle BAM Message Receiver, open the Oracle BAM Message Receiver dialog and use the following table.

Table 18:Oracle BAM Message Receiver settings

Setting DescriptionACD Server Name Type the ADC Server name. The default is Main.

Data Object Select the data object name to use.

Oracle BAM Reader


Oracle BAM Reader Characteristics

Package type Basic

Alters schema No



Sorted input No


SQL Query


SQL Query

Use the SQL Query to compose a SQL statement by dragging Parts into the Workspace or the Data Flow Editor. Use the SQL Editor dialog to type or edit a SQL SELECT statement directly.

A Plan can contain multiple data flows, each with a SQL Query source. When you drag Parts from the Parts Bin to the Workspace, a data flow consisting of a SQL Query and a Grid is automatically created.


Settings in the SQL Query

Using the SQL Editor

Tips for the SQL Query Transform

SQL Query Characteristics

Settings in the SQL Query

To define your SQL Query settings, use Table 19 to guide your selections.

SQL Query


Figure 12:SQL Query dialog

The SQL Viewer tab allows you to view the SQL statement. To make manual changes to the SQL you must use the SQL Editor.

Table 19:Parts tab settings

Setting Description

Parts in this query Lists the Parts you added to the Plan that are in the SQL statement.

Remove Select a Part and click this button to remove it from the SQL statement.

Suppress Duplicate Records

Select this check box to suppress duplicate records from showing up in the results. The results contain one observation of each unique value of all the Parts selected.

Trim Trailing Spaces Select this check box to remove trailing spaces from the result.

SQL Query


Using the SQL Editor

Use the SQL Editor to create or edit a SQL statement. Use the Query Editor to view the SQL statement. Type any SELECT statements in the SQL Editor dialog supported by the RDBMS server software you are using.

Note: You cannot combine edited SQL with a SQL statement that is created by dragging Parts into the Workspace. If you hand-edit your SQL statement, dragging Parts into the Workspace creates a new SQL Query.

To type or edit a SQL statement:

1. Right-click on the SQL Query and select SQL Editor.

The SQL Editor dialog displays.

Figure 13:SQL Editor dialog

2. Select a BaseView to query against from the BaseView dropdown list.

If you are editing a SQL Statement, the BaseView is selected already.

SQL Query


3. Type or edit the SQL text.

If you create a Plan by dragging items from the Parts Bin into the Workspace, the dialog shows the SQL statement generated by this Plan.

4. Click Format to structure the appearance of the SQL statement.

Each SQL keyword is placed on a single line in blue text, and the SQL text is formatted for readability.

5. Click OK to close the dialog and save your changes.

If you created the query by dragging items from the Parts Bin, any display of these columns is cleared from the Workspace.

Tips for the SQL Query Transform

If you are joining two SQL Queries, both must have the same Trim Trailing Spaces and Suppress Duplicate Records settings selected.

Each SQL Query targets a single BaseView. If you want to select data from two BaseViews, then add another SQL Query to the Plan.

When you add Parts to a Plan with more than one SQL Query, a dialog prompts you to select the SQL Query where the Part should be added. To distinguish SQL Query steps from each other, rename the step before you drag Parts into the Workspace.

Deleting a column from the Grid does not delete it from the SQL Query SELECT statement. To remove Parts from the SQL Query, select the Parts on the Parts tab and click Remove. This deletes the column from the SQL SELECT statement.

SQL Query


SQL Query Characteristics

Package type Basic

Alters schema Yes



Sorted input N/A

Exposed properties All

VBScriptSource


VBScriptSource

Use the VBScript Source to read input data from an external application. The source application must be OLE-compliant and installed on both the client computer and the system where the Data Flow Service is running.

The VBScriptSource Transform has no settings. The dialog includes a text field where you type VBScript and click the OK button to close the dialog and save your changes.

For more information, see the Oracle BAM Enterprise Link Design Studio User’s Guide or the Oracle BAM Enterprise Link online help.

You cannot create a column with the binary data type using VBScript Transforms.

Package type Basic

Alters schema Yes



Sorted input N/A

Exposed properties None

XML Source


XML Source

The XML Source Transform allows you to import XML documents into Oracle BAM Enterprise Link.


Settings in the XML Source Transform

XML Source Characteristics

Example of Using the XML Source Transform

In the code in Figure 14, there can be any number of Food elements per GroceryList, and any number of Items and Types per Food. A GroceryList also contains Drink elements, each of which contains Types and Items.

Figure 14:Example XML code<?xml version="1.0" encoding="UTF-8" ?><SampleDoc>

<GroceryList Store="A"><Food>

<Item>Pepper</Item><Item>Apple</Item><Type>Red</Type><Type>Green</Type><Type>Yellow</Type>

</Food><Food>

<Type>Mint Chip</Type><Item>Ice Cream</Item>

</Food><Drink>

<Item>Juice</Item><Type>Grape</Type>

</Drink></GroceryList>

</SampleDoc>

In the following scenarios we do not generate a column for the GroceryList or Food elements, because they do not contain any of their own data. We can also disregard Drinks. We do output both of Foods’ children – Item and Type.

XML Source


In Figure 15, both the Food and Item elements are flattened with a Maximum Occurrence of 2. Also note that the Type element has a Maximum Occurrence of 1.

Figure 15:Food and Item elements flattened (dialog and output)

If an element is not flattened, a new record is generated for each occurrence of that element within its parent element. Modifying the previous scenario so that no elements are flattened, but leaving the Maximum Occurrence settings unchanged, generates the output in Figure 16.

Figure 16:No elements flattened (dialog and output)

XML Source


If you edit the last scenario to allow unlimited occurrences of all elements, the output in Figure 17 is generated. Note that output records are generated for all possible combinations of each element.

Figure 17:Unlimited occurrences of all elements (dialog and output)

Settings in the XML Source Transform

The XML Source dialog has the following tabs:

Schema Definition

Document Source

XML Source


Schema Definition Tab

Figure 18:Schema Definition tab

The Schema Definition tab has the following sections:

XML Schema Information

Formatting Options

XML Schema Information Section

This section uses a tree containing the following icons to represent elements and their hierarchal relationship to one another:

Maximum Occurrence of 0 (unbounded occurrences)

Maximum Occurrence of 2 or more

Maximum Occurrence of 1

XML Source


Flattened Element (with a Maximum Occurrence of 2 or more)

Actually an XML attribute

If an element check box is selected, the element displays as one or more columns (depending upon the Flatten setting) in the output. If the element check box is not selected, the element is not output.

Note that an element’s visibility setting does not affect its children’s visibility, the way the document is parsed, or the number of rows generated. However, if all of an element’s children’s check boxes are unselected, that node’s branch is collapsed by default.

When an element’s check box is selected, the Type, Width, Date Format, Flatten, and Maximum Occurrence options are enabled.

Note: The exception to the above rule is that the Flatten and Maximum Occurrence options are never enabled for the root element, even if the check box is selected.

Use the following table to help you understand the options in this section.

Table 20:XML Schema Information settings

Setting DescriptionXML Schema Information

In this text box, the user browses for a XML document that is visible to the client machine.

Reload

The XML Schema Information file is read-only when it is explicitly referenced. If you want to read the file again, click Reload. If changes are made to the internal document tree, the file is not modified.If changes are made to the XML Schema information file, the internal document tree is not modified until Reload is clicked.

XML Source


Formatting Options Section


Table 21:Formatting Options settings

Setting Description

Type Used to set the output type of the element. This option is disabled for elements that are not selected for output.

Width Used to set the output width of the element. This option is disabled for elements that are not selected for output.

Date Format

Used to specify the input format of a Datetime element so that it can be converted to the internal Platform representation.This option is disabled for elements that are not selected for output, or have a type other than Datetime.

Flatten

When Flatten is selected, the currently selected element is pivoted Maximum Occurrence times. This means that for each occurrence of its parent element, the selected element is parsed (and displayed if selected) Maximum Occurrence times, along with all of its child elements. All additional occurrences of this element within its parent are ignored. The Flatten option is used to “flatten” the branch of an element that contains child elements. If such an element is flattened, instead of creating more records (rows), the Transform adds the information as extra columns in the record.This option is disabled for attributes, the root element, and all elements that are not visible and have no visible children (even if the check box is selected).The root element and attributes can not be flattened.This setting is disabled for elements that are ignored during parsing. Such elements are not visible, and have no visible children.

Scale Use to set the scale of the element. This option is enabled for decimal data types only.

Precision Use to set the precision of the element. This option is enabled for decimal data types only.

XML Source


Document Source Tab

Figure 19:Document Source tab

The Document Source tab has the following sections:

XML Document Source

Validation

Error Handling

XML Source


XML Document Source Section


Validation Section


Table 22:XML Document Source settings

Setting Description

XML Document Source Used to specify the URL (or path relative to the server) of the XML document to be parsed.

Use Definition Document

If selected, the XML Document Source is the same as the XML Schema Information file on the Schema Definition tab.

Table 23:Validation settings

Setting Description

ValidateStarts parser validation checking. This means that if an XML document being parsed references a DTD or XSD definition, the parsed data is validated against that definition.

XML Source


Error Handling Section


Note: Errors generated by documents that are not well-formed are considered fatal, and always cause plan execution to abort with an error message, regardless of whether Validate is selected. In the XML specification, a well-formed document complies with standard XML rules (for example, all tags have associated closing tags).

XML Source Characteristics

Table 24:Error Handling settings

Setting Description

Abort on Error If selected, the first validation error halts plan execution and displays an error dialog describing the error.

Generate Error Log If selected, all validation errors are logged to the file defined by Log File.

Log File

Specifies the location and name of a file to contain validation error message during parsing. The location of this file must be specified relative to the DFS machine. If the file already exists, it is destroyed and recreated (new data is NOT appended to the original file).

Package type Basic

Alters schema Yes



Sorted input N/A


XML Source



Chapter 3


In this chapter:


Grid Displays results in column format

Grid


Grid

The Grid Transform displays the result set in a columnar display that you can format. The Grid Transform is automatically added to the data flow when you create a Plan by dragging Parts into the Workspace.

A Plan can contain multiple data flows, each with its own Grid. A Plan with multiple Grids displays each Grid on a different presentation tab in the Workspace. A Plan can also have more than one Grid output on the same data flow.

You can format the appearance of the Grid text and background by using the options on the right-click menu for the Grid display. For more information, see the Oracle BAM Design Studio online help.

The Grid supports dates from January 1, 100 through December 31, 9999.

The Grid Transform displays the actual string result for all decimal fields whether or not you have enabled the Decimal Math Provider setting. The default Scale is 2. Results are rounded to the specified number of decimal places. If the column is formatted for fractions, the decimal result is converted to type double before calculating the fraction. Double and decimal fields have the same formatting options in the Grid Transform.

Package type Basic

Alters schema No

Multipass or Single pass N/A

Copied or Inplace N/A

Sorted input Yes



Chapter 4

Non-Display SinkTransforms

In this chapter:


Oracle BAM Alert Launches specified alerts based on conditions of the Plan

Oracle BAM Delete Deletes records from a data object in the ADC

Oracle BAM Insert nserts the records it receives into a data object in the ADC

Oracle BAM Message Sender Inserts the records it receives into a data object in the ADC

Oracle BAM Message Tracker Tracks messages for global transactions

Oracle BAM Update Updates records in a data object in the ADC

Terminal Sink Ends a data flow with no display or further action

Oracle BAM Alert


Oracle BAM Alert

The Oracle BAM Alert Transform launches specified alerts based on conditions of the Plan containing this Transform. You might want alerts to notify you when the Plan starts, when the Plan completes, when Plan errors occur, or when the Plan is stopped.

You can only launch alert rules owned by the user that the Data Flow Service is running as. End users who have access to Plans might not be able to launch their own alerts. You might need to reassign the alert rules to the Data Flow Service account.

You can connect the Alert Transform to a Plan at any location using a Splitter if the Plan requires another sink Transform. If you connect an Alert Transform inside a SubPlan, then the alerts will be launched each time the SubPlan ends instead of when the main level Plan ends.

When typing an alert name, you must use the syntax, DOMAIN\username.alertname. For example, COMPANY1\mjohnson.Alert45

Figure 20:Oracle BAM Alert dialog


Settings in the Oracle BAM Alert Dialog

Oracle BAM Alert Characteristics

Oracle BAM Alert

Chapter 4: Non-Display Sink Transforms 67


To define your Oracle BAM Alert use the following table to guide your selections.

Table 25:Oracle BAM Alert setting

Setting DescriptionName of rule to launch on Plan start

Type the name of a rule to launch when the Plan starts.

Name of rule to launch if Plan completes successfully

Type the name of a rule to launch if the Plan completes normally.

Name of rule to launch if Plan fails

Type the name of a rule to launch if the Plan fails. This rule is launched if the Plan ends because of an error instead of ending normally or being stopped manually.

Name of rule to launch if Plan is stopped

Type the name of a rule to launch if the Plan is stopped. This rule is launched if the Plan is manually stopped by using Oracle BAM Administrator to stop the Plan running.

Name of rule to launch for each row

Type the name of a rule to launch each time a record is sent to the Transform. For continuously running Plans where a large amount of data is added to the data flow, this field can potentially launch a large number of alerts. You might want to add a frequency constraint in the alert to limit how often this alert can be launched.Leave any of the fields blank for any conditions where you do not want a rule to be launched.

Oracle BAM Alert



Using the BAM Alert Transform

To use the BAM Alert Transform, add a splitter to the plan (anywhere in the plan) and connect the extra output flow from the splitter to the Oracle BAM Alert transform.

There are a couple of things you need to know about this transform:

1) rules/alerts must be owned or created by the account running the Oracle BAM Enterprise Link Data Flow Service.

2) use the syntax:

DOMAIN\username.RuleName

For example: If I created a rule named: Test-Launch Alert Transform

Then in one of the properties of the Oracle BAM Alert Transform will have an input of: ST-USERS\mlapid.Test-Launch Alert Transform.

Package type Basic

Alters schema No

Multipass or single pass Single pass


Sorted input No


Oracle BAM Delete


Oracle BAM Delete

The Oracle BAM Delete Transform deletes records from a data object in the ADC based on the records it receives from the data flow.




Settings in the Oracle BAM Delete Transform

To define your Oracle BAM Delete Transform, open the dialog and set the following tabs:

ADC Information Tab

Record Selection Tab

ADC Information Tab

To define your settings, select the ADC Information tab and use Table 26 to guide your selections.

Oracle BAM Delete


Figure 21:ADC Information tab dialog


To define your settings, select the Record Selection tab and use Table 27 to guide your selections.

Table 26:ADC Information settings

Setting Description

ADC Server Type the ADC Server machine name that contains the data object. The default name is Main.

Data Object name Select the name of the existing data object to use.

Include in Global Transaction

Click the Include in Global Transaction checkbox if you want to include this Transform in a Global Transaction. If you select this, type the name of the Global Transaction in the field.

Oracle BAM Delete


Figure 22:Record Selection tab dialog

Oracle BAM Delete Characteristics

Table 27:Record Selection setting

Setting Description

Col No.Click the checkbox to select the column. Click Check All to select all columns or Clear All to unselect all columns.

Condition For each column, select a value from the Condition dropdown list.

Apply Click Apply to apply the condition value.

Package type Basic

Alters schema No



Sorted input No


Oracle BAM Insert


Oracle BAM Insert

The Oracle BAM Insert Transform inserts the records it receives into a data object in the ADC.

Figure 23:Oracle BAM Insert dialog


Settings in the Oracle BAM Insert Dialog

Oracle BAM Insert Characteristics

Oracle BAM Insert


Settings in the Oracle BAM Insert Dialog

To define your Oracle BAM Insert use the following table to guide your selections.

Oracle BAM Insert Characteristics

Table 28:Oracle BAM Insert setting

Setting Description


Data Object name Select the name of the data object to use.

Data Object Options

Choose one of the following actions:Use Existing Data Object to insert into the specified data object.Create Data Object to create and insert into a new data object using the name specified. Data objects created using this option cannot be edited.Drop & Create Data Object to clear the contents of the specified data object and insert new data.

Include in Global TransactionClick the Include in Global Transaction checkbox if you want to include this Transform in a Global Transaction.

Global Transaction NameIf you select this, type the name of the Global Transaction in the field.

Package type Basic

Alters schema No



Sorted input No





The Oracle BAM Message Sender Transform inserts the records it receives into a data object in the ADC. The Message Sender Transform performs the reverse of the Message Receiver Transform. The Message Sender Transform packs XML into a message to send.

Figure 24:Oracle BAM Message Sender dialog


Settings in the Oracle BAM Message Sender Dialog

Oracle BAM Message Sender Characteristics



Settings in the Oracle BAM Message Sender Dialog

To define your Oracle BAM Message Sender use the following table to guide your selections.

Oracle BAM Message Sender Characteristics

Table 29:Oracle BAM Message Sender setting

Setting Description


Enterprise Message Source name

Select the name of the enterprise message source to use.

Package type Basic

Alters schema No



Sorted input No


Oracle BAM Message Tracker



The Oracle BAM Message Tracker Transforms tracks messages and can be included in global transaction groups.

Multiple Transforms in a Plan can be included in global transaction groups for tracking data processing. Messages are tracked for cases when the processing is interrupted.

Figure 25:Oracle BAM Message Tracker dialog


Settings in the Oracle BAM Message Tracker Dialog

Oracle BAM Message Tracker Characteristics



Settings in the Oracle BAM Message Tracker Dialog

To define your Oracle BAM Message Tracker use the following table to guide your selections.

Oracle BAM Message Tracker Characteristics

Table 30:Oracle BAM Alert setting

Setting Description




Package type Basic

Alters schema No



Sorted input No


Oracle BAM Update


Oracle BAM Update

The Oracle BAM Update Transform updates records in a data object in the ADC based on the records it receives from the data flow.


Settings in the Oracle BAM Update Dialog

Oracle BAM Update Characteristics

Settings in the Oracle BAM Update Dialog

To define your Oracle BAM Update Transform, open the dialog and set the following tabs:

ADC Information Tab


Update Specifications Tab

ADC Information Tab

To define your source file, select the ADC Information tab and use Table 31 to guide your selections.

Oracle BAM Update


Figure 26:ADC Information tab dialog


To define your settings, select the Record Selection tab and use Table 32 to guide your selections.

Table 31:ADC Information setting

Setting Description


Data Object name Select the name of the existing data object to use.



Oracle BAM Update


Figure 27:Record Selection tab dialog

To define your settings, select the Update Specifications tab and use Table 32 to guide your selections.

Update Specifications Tab

To define your source file, select the Update Specifications tab and use Table 33 to guide your selections.

Table 32:Record Selection setting

Setting Description

Col No.Click the checkbox to select the column. Click Check All to select all columns or Clear All to unselect all columns.

Condition For each column, select a value from the Condition dropdown list.

Apply Click Apply to apply the condition value.

Oracle BAM Update


Figure 28:Update Specifications tab dialog

To define your Oracle BAM Alert use the following tables to guide your selections.

Table 33:Update Specifications setting

Setting Description

Data Flow Columns Select a data flow column and click Add to update the column in the data object.

Data Object ColumnsSelect the data object column and choose either Add or Subtract from the dropdown list if you want to perform an operation other than Replace.

Insert record if not found Click the Insert record if not found checkbox to insert and update records.

Oracle BAM Update


Oracle BAM Update Characteristics

Package type Basic

Alters schema No



Sorted input No


Terminal Sink


Terminal Sink

Use the Terminal Sink to end a data flow without displaying results or taking further action. When you run a Plan that ends in a Terminal Sink, the results are not stored in cache, so a new query runs each time the Plan is updated.

The Terminal Sink has no dialog.

Package type Basic

Alters schema No



Sorted input No

Exposed properties N/A

Terminal Sink



Chapter 5

Data Flow ControlTransforms

In this chapter:


Comparison Compares two sets of input records and identifies matching records

Conditional Splitter Uses filter conditions to divide a data flow

Join Joins the columns from two data flows into one

Round Robin Splitter Distributes records among the outputs included in the splitter

Splitter Creates two identical copies of data flow output

SubPlan Organize a Plan by simplifying its graphical representation and grouping steps in the Plan hierarchically. Also provides iterative processing of data flows.

Union Merges two data flows into one

Comparison


Comparison

Use the Comparison Transform to separate the data flow from two input data sets into three data sets based on comparison criteria. The top output lists records in the first input that are not in the second. The middle output lists all records appearing in both inputs. The last output lists records in the second input that are not in the first.


Settings in the Comparison Dialog

Tips for the Comparison Transform

Comparison Characteristics

Comparison

Chapter 5: Data Flow Control Transforms 87

Settings in the Comparison Dialog

To define your Comparison Transform, open the dialog and set the following tabs:

General Tab

Advanced Tab

General Tab

To define your Comparison Transform, select the General tab and use Table 34 to guide your selections.

Figure 29: Comparison dialog

Table 34: Comparison dialog settings

Setting DescriptionInput 1 Select or type the name of a column for comparison.

Input 2 Select or type the name of a column for comparison.

Add Adds the values from Input 1 and Input 2 into the list of columns for comparison.

Remove Deletes selected comparisons from the list.

Remove All Deletes all comparisons from the list.

Comparison


Name Based Select this option to select columns with matching names for the comparison. If columns with matching names are found, they are mapped for comparison.

Positional Select this option to select columns for comparison by their position in the input lists.If the input for this step is a query that was edited in the SQL Editor dialog, no columns are displayed. In this case, mappings are determined when the Plan is run.

Match All Select this option to compare every column in both inputs. The number of columns per input must be the same. If Name Based is checked, column names in both first and second inputs must be identical, but can be in any order. Unless Match All is checked, Input 1 and Input 2 can have different numbers of columns. When the number of columns is different, only mapped columns are compared.

Default Select this option to compare records using the default method of handling case for this BaseView.

Sensitive Select this option to use case sensitivity when finding matched in the comparison.

Insensitive Select this option to find matches regardless of case.

Merge Keys Select this check box to merge key column values in the middle output. To use this option, the two sets of input columns must share a key column. The default setting is that key columns are not merged.

Table 34: Comparison dialog settings (continued)

Setting Description

Comparison


Advanced Tab

To set up advanced features on your Comparison Transform, select the Advanced tab and use Table 35 to guide your selections.

Figure 30: Advanced tab dialog

Table 35: Advanced tab settings

Setting DescriptionInput 1Data is pre-sorted

Select this check box if the data from Input 1 is already sorted.

Input 2Data is pre-sorted

Select this check box if the data from Input 2 is already sorted.

Ascending Indicates that the data is already sorted in ascending order.

Descending Indicates that the data is already sorted in descending order.

Comparison


Tips for the Comparison Transform

To specify multiple criteria for comparison, use additional Comparison Transforms and the Union Transform.

You can compare more than two columns.

When comparing data sets of different sizes, attach the larger data set to the top input of the Comparison Transform for better performance.

Use the Advanced tab to avoid performing unnecessary sorting on already sorted data. Removing unnecessary sorting can increase performance.

The Comparison Transform is limited to 16 columns unless you insert a Transform into the data flow that prevents sorting from being pushed back to the database.

Comparison Characteristics

Package type Basic

Alters schema Yes

Multipass or Single pass Multipass

Copied or Inplace Inplace

Sorted input Yes


Conditional Splitter



Use the Conditional Splitter to divide the output of the data flow into two sets using filter conditions. This Transform has one input and two outputs. The results that pass through the top output match the specified condition. The results that pass through the bottom output do not match the condition. To specify multiple conditions, use additional Conditional Splitter Transforms and the Union Transform or use a SubPlan Transform with iteration enabled instead of multiple Conditional Splitter Transforms.

Figure 31: Conditional Splitter dialog


Settings in the Conditional Splitter Dialog

Tips for the Conditional Splitter Transform

Conditional Splitter Characteristics



Settings in the Conditional Splitter Dialog

To define your conditional splitter use the following table.

Table 36: Conditional Splitter dialog settings

Setting DescriptionColumn Select or type the column to use for the condition.

If you type the column name it must match the name in the Parts Bin.

Operator Select a comparison operator from the Operator dropdown list.Values are:- is equal to- is greater than- is less than- is less than or equal to- is not equal to [and not null]- is not equal to- is null- is not null

Type Select the data type of the column you are using for the condition.Values are:- text- field- number

Value Select a value type from the Type dropdown list.For number, type the numeric value you are filtering in the Value field.For text, type the text you are filtering in the Value field. To broaden the filter criteria, use regular expressions in the text string. Regular expressions are similar to wildcards and are case-sensitive. For field, type or select the column name.

Regular Expressions Use to include one or more regular expressions in the Value field.If you click this check box, select either is equal to or is not equal to as a comparison operator.

Match Case Use to select strings where the case matches text you typed.



Tips for the Conditional Splitter Transform

In the Conditional Splitter dialog, the word “field” is synonymous to “column”.

Regular Expressions

Regular expressions are used in search or filter criteria and are case-sensitive and similar to wildcards. Some common regular expressions are:

Character Description Example

. A period returns any single character .uppy returns puppy or guppy

[ ] A set or range of characters inside brackets returns any one of the set or range of characters

[ab] returns either a or b[a-e] returns any character from a through e

[^] A caret within brackets returns any character except one of the set or range of characters following the caret

[^ab] returns any character except a or b[^a-e] returns any character except a through e

+ A plus following a character returns one or more occurrences of that character

[a-e]+ returns one or more occurrences of the characters from a through e

* An asterisk following a character returns zero or more occurrences of that character

[a-e]* returns zero or more occurrences of the characters from a through e

? A question mark following a character returns either zero or one occurrence of that character

a?b returns either b or ab

\ A backslash preceding any other special character returns a literal character

\+ returns +



Conditional Splitter Characteristics

Package type Basic

Alters schema Yes



Sorted input No


Join


Join

Use the Join Transform to produce a single result set by joining columns from two data flows. The joined columns form a new record structure. The Join Transform requires that the two data flows contain related key columns.


Settings in the Join Dialog

Tips for the Join Transform

Join Characteristics

Join


Settings in the Join Dialog

To define your Join Transform, open the dialog and set the following tabs:

General Tab

Advanced Tab

General Tab

To define your Join, select the General tab and use Table 37 to guide your selections.

Figure 32: General tab dialog

Join


Table 37: General tab settings

Setting DescriptionLeft Side Key (Top Input) Select or type an input column to use as part of the

join statement. Must be same data type as Right Side Key (Bottom Input).Left side key columns are from the top source.

Right Side Key (Bottom Input) Select or type an input column to use as part of the join statement. Must be same data type as Left Side Key (Top Input).Right side key columns are from the bottom source.

Add Use this button to add the columns selected in Left Side Key and Right Side Key to the list of join statements.You can include more than one pair of columns in the join. For example, if you want to constrain on values in more than one column, add another column pair to the join. These columns must also be of the same data type, but the type can be different than the first pair.

Remove Use this button to delete join statements.

Left Side Key Columns This dropdown lists the join statement columns from the top (left) data flow.

Right Side Key Columns This dropdown lists the join statement columns from the bottom (right) data flow.

Outer Join (Top or Left) Select to combine one or both of the result sets using outer joins.The left outer join includes rows from the top (left) result set that are not matched in the other result set. If you want records from the top (left) data flow to be included (even if no match is found in the bottom (right) data flow) click on the Outer join button directly under the left side of the join display box.

Join


Note: If you want records from both the top (left) and the bottom (right) data flows, then click both outer join buttons. Records from both data flows are included even if no matches are found.

Advanced Tab

To set up advanced features on your Join, select the Advanced tab and use Table 38 to guide your selections.

Figure 33: Advanced tab dialog

Outer Join (Bottom or Right) Select to combine one or both of the result sets using outer joins.The right outer join includes rows from the bottom (right) result set that are not matched in the other result set.If you want records from the bottom (right) data flow to be included (even if no match is found in the top (left) data flow) click on the outer join button directly under the right side of the join display box.

Merge Key Columns Use to merge the joined key columns into one column in the output. This produces a natural inner join. When Merge Key Columns is not selected, the output contains all of the key columns.Select the Merge Key Columns check box to output results with the join columns combined in one column.

Table 37: General tab settings (continued)

Setting Description

Join


Tips for the Join Transform


When joining data sets of different sizes, attach the larger data set to the top input of the Join Transform for better performance.

The default setting is an inner join, which returns only rows that have matching column values from both result sets.

If you sort your data with a Disk Sort or Memory Sort before joining you must sort in descending order if you want to select the Data is pre-sorted check box.

If the source Transforms for the Plan are the same type, specify a unique name for each source.

Join Characteristics

Table 38: Advanced tab settings

Setting DescriptionLeft Side Key (Top Input)Data is pre-sorted

Select this check box if the data from the top input is already sorted.

Right Side Key (Bottom Input)Data is pre-sorted

Select this check box if the data from the bottom input is already sorted.

Package type Basic

Alters schema No



Sorted input Yes


Round Robin Splitter


Round Robin Splitter

Use the Round Robin Splitter Transform to distribute records among the Transforms connected to the splitter. This divides up the data and the processing so that multiple sets can be processed at the same time. A Round Robin Splitter Transform has one input and multiple output connection points. New output connection points are automatically added to the Round Robin Splitter Transform as data flow connectors are added.

The Round Robin Splitter provides a way to distribute data in the data flow for processing more efficiently. An example for using the Round Robin Splitter is for loading a large data set to a batch loader. Instead of a single data flow to one batch loader, you can add a Round Robin Splitter with four outputs. For each output, attach an additional batch loader with the same settings specified in its dialog to load to the same target database. Because the Round Robin Splitter shares the data among the additional batch loaders so that each output receives 25% of the records, sets of data can be processed simultaneously and loaded faster.

The Round Robin Splitter Transform has no dialog.

Package type Basic

Alters schema Yes



Sorted input No


Splitter


Splitter

Use the Splitter Transform to create two identical copies of a result set in a data flow. A Splitter Transform has one input connection point and multiple output connection points. New output connection points are automatically added to the Splitter Transform as data flow connectors are added. The duplicate data set created by a Splitter Transform is a virtual data set only. It does not require additional physical resources.

The Splitter Transform has no dialog.

Package type Basic

Alters schema Yes



Sorted input No


SubPlan


SubPlan

Use the SubPlan Transform to create layered Plans in the Data Flow Editor. The SubPlan Transform reduces the number of steps displayed in the Data Flow Editor at one time by grouping steps together. As part of SubPlan processing, you can attach a data flow to the iteration connection point on the SubPlan. Use the iteration connection point to perform iterative processing on data. Many data extraction and transformation tasks that require several steps and multiple data paths can be accomplished in fewer steps using the SubPlan Transform and iterative processing.

Using the iteration connection point, you can define whether operations are performed on each record or by record groups. After the operations of a SubPlan are completed on the first record or record group, the steps contained in the SubPlan restart processing on the next record or record group.

You can add an unlimited number of SubPlans to a Plan. A SubPlan Transform has multiple input and output connection points. New connection points are added to the SubPlan as data flow connectors are added. Only one data flow can be connected to the SubPlan iteration connection point. An unlimited number of non-iterated data flows can be connected to a SubPlan.

When you add a new SubPlan, add data flow connector lines to the SubPlan and then open the SubPlan for the first time, the SubPlan Inputs and SubPlan Outputs symbols show:

An iteration output connection point corresponding to the iteration input of the SubPlan, if connected.

Corresponding input connection points for each SubPlan input data flow.

Corresponding output connection points for each SubPlan output data flow.

SubPlan


SubPlans and SubPlan connection points do not perform joining, merging or splitting operations. Each connection point inside of the SubPlan is a continuation of a data flow that is attached to it at the preceding level. Each connection point inside of the SubPlan must be connected to a data flow in order for the Plan to operate correctly. You can use Join, Union, and Splitter Transforms in the SubPlan in order to increase or decrease the number of data flows.


Settings in the SubPlan Iteration Dialog

Tips for the SubPlan Transform

SubPlan Characteristics

SubPlan


Settings in the SubPlan Iteration Dialog

To define SubPlan iteration, open the dialog and set the following tabs:

General Tab

Property Assignments Tab

General Tab

To define iteration type and source fields, select the General tab and use Table 39 to guide your selections.

Figure 34: General tab dialog

SubPlan


Property Assignments Tab

To define source field to property mappings, select the Property Assignments tab and use Table 40 to guide your selections.

Figure 35: Property Assignments tab dialog

Table 39: General tab settings

Setting DescriptionIteration Settings Select Iterate For Each Record to process each

record from the data sources.Select Sort Records and Iterate Each Time A Key Field Changes Value to sort the records by the key fields selected and then process records in groups each time a value changes.

Key Fields Shows all of the Parts in the data sources. If Iteration Settings is set to Sort Records and Iterate Each Time A Key Field Changes Value, select the Parts that you want to use for the key fields.

Ignore Case When Comparing Values For Iteration

Select when Iteration Settings is set to Sort Records and Iterate Each Time A Key Field Changes Value to ignore differences in case when evaluating key fields values to determine record groups.

Data is pre-sorted If the data contained in the dataflow is already sorted, for example by a preceding sort Transform, select Data is Pre-Sorted to suppress redundant sorting during iteration processing

SubPlan


Tips for the SubPlan Transform

To edit SubPlan iteration settings, you must have a data flow containing a data source Transform connected to the SubPlan iteration connection point.

Each SubPlan Outputs connection point that has a data flow connected to it inside of the SubPlan must have a corresponding data flow connected at the preceding level of the Plan.

Source fields can be assigned to zero or more properties. Destination properties can only be assigned to one field.

Table 40: Property Assignments tab settings

Setting DescriptionField to Property Mappings Use to create mapping between source fields and

custom Plan properties. Select a value in Source Field, select a value in Destination Property, and then click Add to create a mapping.

Source Field Lists Parts in the data sources of the data flow. If Iteration Settings is set to Iterate For Each Record, lists all of the Parts in the data sources. Otherwise, lists selected Parts in Key Fields.

Destination Property Lists custom Plan properties and exposed step properties defined in the Plan.

Add Click to create a mapping between the selected Source Field and the selected Destination Property.

Assignments LIsts source field and custom Plan property assignments for the SubPlan.

Remove Click to remove the assignment between the selected source field and custom Plan property mapping.

SubPlan


SubPlan Characteristics

Package type Basic

Alters schema N/A



Sorted input Variable1

1. If you choose to iterate by record group, the data set of the iterated data flow is sorted based on the previous step’s output. If you choose to iterate by record, no sorting is performed. Data sets of non-iterated data flows that pass through a SubPlan are not sorted as part of SubPlan processing.


Union


Union

Use the Union Transform to produce a single result set from the rows in multiple data flows, including duplicate rows. The Union Transform forms a composite of the row sets, placing one set on top of the other. The rows in each data flow must contain an equal number of columns, and each column must have identical attributes.

A Union Transform has multiple input connection points and one output connection point. New input connection points are automatically added to the Union Transform as data flow connectors are added.

The Union Transform has no dialog.

Package type Basic

Alters schema No



Sorted input No



Chapter 6

Data ManipulationTransforms

In this chapter:


Add Columns Adds one or more empty columns

Column Select Removes and reorders columns

Concatenation Combines column data and typed text

Disk Sort Sorts columns using disk files

Expression Calculator Uses expressions to add calculated columns

Filter Removes rows using filter conditions

Key Generation Generates unique numeric key values

Key Lookup Looks up a value in one table and inserts into another table

Memory Sort Sorts records in columns using memory


Message Digest Builds keys based on any type of input data

Oracle BAM Lookup Performs lookups on key fields from data in a data flow and data in a data object

Pivot Aggregates and arranges columns in crosstab format

Rank Outputs top or bottom ranked rows of a column

Record Number Adds a column with sequential numbers

Rename Columns Changes column names

Search & Replace Replaces a string or substring with another string

Substring Extracts a substring from a string value

Transpose Arranges columns in row format

VBScriptCopy Copies the input record using VBScript

VBScriptInplace Modifies each input record in place using VBScript


Add Columns

Chapter 6: Data Manipulation Transforms 111

Add Columns

Use the Add Columns Transform to add a new column to the data flow. You must specify the data type of the new columns and the columns can be empty or contain an initial value. The new column name displays automatically in the dropdown lists of the Transforms later in the data flow.

Figure 36:Add Columns dialog


Settings in the Add Columns Dialog

Tip for the Add Columns Transform

Add Columns Characteristics

Add Columns


Settings in the Add Columns Dialog

To define your Add Column, use the following table to guide your selections.

Table 41:Add Columns settings

Setting DescriptionName Type a name for the column you are adding.

Type

Select the data type of the new column. If you select string, type the length of the string in characters in the Size field. If you select decimal, specify the Scale and Precision.

Initial Value Specify the initial value for the new column.

Size Specify the length of the column.

Scale For decimals, specify the scale.

Precision For decimals, specify the precision.

Add Click this button to add the new column to the New Columns display.

Remove Click this button to remove the selected column. It is deleted from the New Columns display.

ModifyClick this button to apply changes made to the format of a column. The new column changes are in the New Columns display.

Pad with spaces Select this check box to pad data with spaces to the full length. This is for string data types only.

Create New Select this to have new column created.

Use existing Select this to overwrite an existing column with the new column.

Add Columns


Tip for the Add Columns Transform

Use the Add Columns Transform to add columns required in the results. This can increase Plan performance because other Transforms can produce results inplace instead of copied.

Add Columns Characteristics

Table 42:Supported column data types

Data Type Description LimitsString alphanumeric values none

Int integer values ± 64,000

Long integer values ±2,000,000,000

DateTime date/time values none

Double real number values, including decimals ±2,000,000,000

Unit positive integer values less than ~ 64,000

Decimal String of character digits with sign and decimal point. None

Package type Basic

Alters schema Yes



Sorted input No


Column Select


Column Select

Use the Column Select Transform to remove any unwanted input columns from the data flow. This Transform removes specified input columns in a data flow, and outputs the remaining columns. Removed columns are discarded from the data flow, improving processing performance for the remaining columns. A different Column Select dialog displays if the Plan includes user-entered SQL. No existing input columns are listed, and you must type the names of columns to select.

Figure 37:Column Select dialog


Settings in the Column Select Dialog

Tips for the Column Select Transform

Column Select Characteristics

Column Select


Settings in the Column Select Dialog

To define your Column Select, use the following table to guide your selections.

If the Plan includes user-entered SQL, a different dialog displays. In this case, use the following table to guide your selections.

Table 43:Generated SQL - Column Select settings

Setting Description

Column check boxes To exclude a column from the data flow, click the check box next to the column name to deselect it.

Include All Click Include All to check all the column check boxes.

Exclude All Click Exclude All to deselect all the column check boxes.

Order by Input

Reorder the columns to change their output order by clicking on a column and dragging it to a new position. If you have reordered the input columns and want to restore them to the import order, click Order By Input.

Include New Columns by Default

Click Include New Columns by Default to select Parts as they are added to the data flow. By unchecking this, new Parts added to the Plan will not be added to the data flow.

Table 44:User-entered SQL - Column Select settings

Setting Description

Enter Name of ColumnType the name of each column to select and click Add. If the input column is a Part, type the name as it appears in the Parts Bin.

Add Click Add to add the column to the data flow.

Include Only The Following Fields Click to include the fields in the list below.

Exclude The Following Fields Click to exclude the fields in the list below.

Columns Included/Excluded Lists the columns either included or excluded from the data flow.

Column Select


Tips for the Column Select Transform

Reorder columns in the Column Select dialog if additional processing in the data flow is performed by position rather than by column name. For example, Batch Loader Transforms can load columns by either name or position. Sink steps that display data, such as the Grid Transform display the original order.

If you delete the Grid Transform and add it to the data flow again, the columns display in the new order. You can use this technique to view the output to a Grid and verify the output order for a load.

If input columns are included in an Expression Calculator Transform before this Transform in the data flow, remove the columns from the data flow using the Expression Calculator instead of adding an unnecessary Column Select Transform.

Column Select Characteristics

Package type Basic

Alters schema Yes



Sorted input No


Concatenation


Concatenation

Use the Concatenation Transform to concatenate the values in multiple columns, or to concatenate a column value with typed text in a single column. You can place the result in a new column, or replace the values in an existing column. The result of a concatenation is a string value.

Figure 38:Concatenation dialog


Settings in the Concatenation Dialog

Concatenation Behavior with Null Values

Concatenation Characteristics

Concatenation


Settings in the Concatenation Dialog

To define your Concatenation, use the following table to guide your selections.

Concatenation Behavior with Null Values

The Concatenation Transform and the Expression Calculator concatenate string values with null values differently.

In the Concatenation Transform, concatenating a string value with a null value results in a string value.

In the Expression Calculator, concatenating a string value with a null value results in a null value.

You can change this behavior by adding a DWORD Registry setting named ConcatWithNulls in the following key:

Table 45:Concatenation dialog

Setting Description

Enter Field Name Double-click to select the column or columns that you want to combine or format.

Concatenated String

Type the Concatenated String field if you want to add characters to the string (for example, spaces, commas, or parentheses). The columns are underlined. Characters you type are not underlined.

Upper-caseClick the name in the Concatenated String field, and click Upper-case to specify the case of the column values.

Lower-caseClick the name in the Concatenated String field, and click Lower-case to specify the case of the column values.

Trim Right Trim Right to remove any extra spaces from the right concatenated string values.

Trim Left Trim Left to remove any extra spaces from the left concatenated string values.

New Field Click the New Field radio button to place results in a new column. Type a name for the new field.

Existing Field

Click Existing Field to replace the values in an existing column with the results from this Plan. If no existing input are listed, type the name of a string column as it appears in the Parts Bin.

Concatenation


My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Sagent\DataMart\TF

Set the value to one of the following:

Concatenation Characteristics

Value Behavior0 Default setting

1 In Expression Calculator, Concat(<NULL>, ABC) results in ABC

2 In Expression Calculator and in the Concatenation Transform, if one of the two fields is a <NULL>, the result is <NULL>

Package type Basic

Alters schema Yes



Sorted input No


Disk Sort


Disk Sort

Use the Disk Sort to sort large sets of input records before loading them into your target table.

During a disk sort, small sets of records are sorted using a small amount of memory and then written to temporary files on the Oracle BAM Enterprise Link Server. This process is repeated until all input records are sorted. The temporary files are then merged and the final result returned.

Note: This is a performance intensive multipass Transform. As an alternative, use filter controls to push sorting back to the SQL statement and improve performance.

Figure 39:Disk Sort dialog


Settings in the Disk Sort Dialog

Tips for the Disk Sort Transform

Disk Sort


Settings in the Disk Sort Dialog

To define your Disk Sort, open the Disk Sort dialog and use Table 46 to guide your selections.

Table 46:Disk Sort settings

Setting Description

Unsorted Columns

This field lists the unsorted column names in the data flow at this point in the Plan.Select a column name and click the right arrow button to move it to the Sorted Columns field.

Sorted Columns

This field lists the columns selected for sorting in the order of sorting priority and displays whether data is sorted in ascending or descending order. The default sorting order is ascending.To change the sorting order:Select a column name and click the up or down arrow button to have data sorted in ascending or descending order.To change the sorting priority order:Click and drag column name to the new position.

Case Sensitivity Default Select this option to have the RDBMS default case sensitivity used to sort your data.

Sensitive Select this option to have your data sorted using case sensitivity.

Insensitive Select this option to have your data sorted without using case sensitivity.

Null Precedence Default Select this option to have the RDBMS default null precedence used to sort your data.

SmallestSelect this option to have the smallest values placed first and null values placed last when the data is sorted.

GreatestSelect this option to have the greatest values placed first and null values placed last when the data is sorted.

Disk Sort


Note: If you write user-entered SQL, a different dialog displays. You must type the exact column names in the Enter Column To Sort field and click Add.

Max Blocks in Memory

This is the number of memory blocks on the Oracle BAM Enterprise Link Server to allocate for sorting records. The size of a block is determined by the AgentBlockSize setting in Oracle BAM Enterprise Link Admin. A larger number usually increases performance, but other processes on the server might be affected. The default value is 500. The minimum number of memory blocks required to sort records is 300. The maximum value is approximately 32767.

Max Threads

This is the number of threads on the Oracle BAM Enterprise Link Server to use for sorting records. Multiple threads can sort multiple sets of records simultaneously. The default value is 1 and the maximum value is 99.

Temp File Path

Define the path from the local machine to the temporary file you are using to sort the data.This works best if the file is on the machine that the Data Flow Service is running on.This is the location on the Oracle BAM Enterprise Link Server where sorted records are temporarily stored in files. Specify the path as relative to this machine. Files are created with the name you specify and a numeric suffix.For example, if you specify C:\Program Files\Oracle BAM Enterprise Link\sort and the sort operation requires 3 temporary files, files named sort1, sort2 and sort3 are created in this directory. If you do not specify a path, the directory specified by the TEMP environment variable on the Enterprise Link Server is used.

Table 46:Disk Sort settings (continued)

Setting Description

Disk Sort


Tips for the Disk Sort Transform

When setting Max Blocks in Memory, consider your Data Flow Service settings and other programs you are running. Make sure you set this number high enough to accommodate for the programs you are running.

When setting Max Threads, check the Data Flow Service to see how many threads are available. Use the largest number of threads to maximize performance.

Temporary file should be located on the same machine as the Data Flow Service to maximize performance.

Use the Data Flow Service counters with Windows NT Performance Monitor to determine how memory and thread values affect performance on the Oracle BAM Enterprise Link Server.

For more information on monitoring the Data Flow Service, see the Oracle BAM Enterprise Link Administrator’s Guide.

Disk Sort Characteristics

Package type Basic

Alters schema No



Sorted input Yes


Expression Calculator



Use the Expression Calculator to:

create expressions that manipulate data to get the exact data output you need

add columns, reorder columns, and select which columns to send to the output all in one step


Settings in the Expression Calculator Dialog

Creating an Expression

Tips for the Expression Calculator

Expression Calculator Characteristics



Settings in the Expression Calculator Dialog

To define your Expression Calculator, open the Expression Calculator dialog and use Table 47 to guide your selections.

Figure 40:Expression Calculator dialog



Table 47:Expression Calculator settings

Setting Description

Input Columns

This lists all the columns in the data flow at this point. Deselecting an input column check box removes the column from the data flow because it will not be included in the output. It can still be included in an expression.You can reorder the output columns by deselecting all check boxes and then re-selecting in the order you want the output.By default, all columns are selected.

Output Columns

This lists the columns that will be included in the data flow. The check boxes in the input columns list indicate whether or not the columns display in the output columns list.

Include New Columns by Default

This option allows you to add new columns to this Plan without changing the output.By default, this check box is selected.

Expressions

This lists all calculated column names, data types, lengths (if applicable), and expressions.For decimal fields, the Length column value is two more than the Precision value (one column for the sign plus one column for the decimal point). However, if Precision = Scale, the Length value will be three more than the Precision (the extra column holds a “0” to the left of the decimal point).

NewClick this button to create new calculated columns using the Expression Builder. You can create multiple calculated columns within the same Transform.

Edit

Select a calculated column from the expressions list and click this button to display the Expression Builder. Use the Expression Builder to edit expressions.You can also double-click on a column name to display the Expression Builder.

Delete Click this button to delete a selected expression.

Arrow Buttons

Use these buttons to move the expressions up and down. The order is very important. If an expression is referenced by another calculated column, it must be before (above) the calculated column referencing it.

Automatic Select this to have field length averaged from the results.



Note: If you write user-entered SQL, a different dialog displays. You must type the exact column names in the Enter Column To Sort field and click Add.

Fixed at

Select this to have fixed length results. You must set the length of the field.If the column has a string data type, by default the column is automatically sized to the largest resulting value. To set the column size to a fixed number of characters, click the column name in the Output Column list. Then click the Fixed at radio button and type a value. All characters beyond this length are truncated.

Replace Existing

Select this check box to replace an existing output column with results from a new calculated column. You must then select the column to replace from the dropdown.To place results in an existing column, select the Replace Existing check box. You must select the column name you want to replace from the dropdown.

Decimal Result

Specify the Precision and Scale values manually or allow the values to be calculated automatically.If you have selected a decimal field and the decimal math provider is enabled, this section is active. Select Automatic to let the computer determine Precision and Scale, or select Fixed at to enter your own values for Precision and Scale.See “Decimal Math Calculations” on page 186for more information about decimal fields and decimal field calculations.

Table 47:Expression Calculator settings (continued)

Setting Description



Creating an Expression

To create an expression, click New to display the Expression Builder dialog and use Table 48 to guide your selections.

Figure 41:Expression Builder dialog

Table 48:Expression Builder settings

Setting Description

Input FieldsThis lists all input fields. Double-click, type or drag items in the Input Fields list into the workspace to use in the expression.

Functions

This lists the functions you can apply to the expressions you build.Double-click, type or drag an item in the Functions list to add it to the expression. For detailed explanations of each function see Appendix A, “Expression Calculator Functions.”

Calculator

Pass/Fail iconData type field

Question

CaptionWorkspace

Mark button



Note: When you click OK to save the expression, the new column displays in the Output Column list with the default name of Calculatedn, where n is a positive integer. This column displays in the Grid or other sink in the Workspace.

Calculator

These functions can be used to create a calculated expression.Use the calculator keypad to add constants, separators and logical operators to the expression. Use the keyboard to add other syntax elements.Back deletes the preceding character. CE clears most recent changes since the expression was saved.C deletes all syntax for this expression.

Workspace This area displays the expression you are building.

Caption

When you highlight a function, the correct syntax displays in the caption. When you highlight an Input field, the data type for that field displays in the caption.

Pass/Fail icon

This checks for correct syntax. If your expression has correct syntax, the green check appears. If your expression has incorrect syntax, a red fail icon appears.When the expression syntax is correct, the automatic parsing feature displays a green check mark on the right side of the dialog.When the expression syntax is incorrect, the red fail icon displays. Click on the question mark button to display hints on how to fix the syntax. The question mark button is greyed out when syntax is correct.

Question Mark button If your expression is failing, click on this button to display tips on correcting the syntax.

Data type field This box displays the data type of the expression.

Table 48:Expression Builder settings (continued)

Setting Description



Tips for the Expression Calculator

Plan properties are listed as input columns in the Expression Calculator.

If you want to rename a field, use the Rename Columns Transform to save resources.

If you build large If-Than-Else statements, make sure that each section passes the automatic parsing and a green check mark displays before starting a new section.

If you need to include regular expressions, use the Search & Replace Transform.

For information on a concatenation Registry setting, see “Concatenation Behavior with Null Values” on page 118.

Make sure that all outpus are of the same datatype when you use if-then-else or switch statements.

Renaming the Expression

To rename the column:

1. Right-click on the default name and select Rename. The name becomes an editable field.

2. Type the new name and press Enter.

Editing an Expression

To edit an expression:

1. Double-click the name of the column in the Expression list, or select the name and click Edit.

Use the Expression Builder to edit the column.

2. When editing is complete, click OK.

To delete an expression:

Select the column name and click Delete or right-click on the column name and select Delete.



Changing the Order of the Expressions

Expressions are processed in the order they appear in the Expressions list. An expression can reference the results of other expressions. Because of this, arrange the order of expressions to produce the results including any dependencies on other data.

To change the order of the expressions:

1. Open the Expression Calculator dialog and select a column.

2. Click on the up and down arrow buttons to move the column up or down in the list.

3. Click OK to save the columns and close the dialog.

Using SwitchStatements

Enter Switch statements into the Expression Builder to replace nested If-Than-Else statements.

For example, the statement:

IF (column_name==”business”) THEN (“B”) ELSE (IF (column_name==”mod-cook”)THEN (“M”)ELSE (IF (column_name==”popular_comp”) THEN (“P”)ELSE (IF (column_name==”psychology”) THEN (“H”) ELSE (“NULL_STRING”))))

can be written as the following CASE statement:

SWITCH (column_name)Case (“business”): (“B”)Case (“mod-cook”): (“M”)Case (“popular_comp”): (“P”)Case (“psychology”): (“H”)Default (NULL_STRING)



Expression Calculator Syntax

These general rules apply to all expression syntax:

To designate a value as a string value or literal and prevent interpretation as an input field, enclose the string in single or double quotation marks.

Note: If you are using an expression string or a literal value that contains single quotes in the string, then use double quotes. For example, use “Southeast’s Regional RTMs” rather than ‘Southeast’s Regional RTMs’. Using single quotes in this case returns an error.

If the operator requires an operand, enclose the operand in parentheses. For expressions nested in other expressions, additional parentheses are required to show precedence, or the order in which expressions should be evaluated. The automatic parsing feature does not pass the expression until parentheses are correctly placed.

The type of an expression is determined by the kind of data it returns. If the expression returns an integer value, it is of type integer. If it returns a string value, the expression is of type string. The Expression Calculator Transform evaluates each expression’s output type and automatically assigns the correct data type.

Expression Calculator Characteristics

Package type Basic

Alters schema Yes


Copied or Inplace Both

Sorted input No


Filter


Filter

Use the Filter to place conditions, or constraints, on input columns passed to the next step in the data flow. This type of filtering is similar to adding a constraint to a SQL WHERE clause, but is independent of the relational database. If a Plan contains multiple Filter Transforms, all the specified conditions are applied.

To build SQL filters using the commands of your relational database, click the Show/Hide Filters button on the Filters toolbar to Add a Filter to a Plan.

Figure 42:Filter dialog


Settings in the Filter Dialog

Tips for the Filter Transform

Filter Characteristics

Settings in the Filter Dialog

To define your Filter, use the following table to guide your selections.

Table 49:Filter settings

Setting Description

Column Select a column to filter. All columns appear in this dropdown list, including aggregated columns.

Operator Select a comparison operator.

Type Select a comparison type. The types listed vary according to the data type of the column you choose.

Filter


Tips for the Filter Transform

The Filter Transform and other Transforms that perform comparisons do not support Oracle floating point (decimal) values.

For more information on regular expressions, see “Regular Expressions” on page 93.

Filter Characteristics

Value

Provide the comparison value, based on the type you selected.For a number type, type the numeric value you are filtering in the Value field.For a column type, select a column to compare from the Value dropdown list.For a text type, type the text you are filtering in the Value field. To broaden the filter criteria, use regular expressions in the text string. Regular expressions are similar to wildcards and are case-sensitive.

Regular Expressions

Click the Regular Expressions check box if you included one or more regular expressions in the value. If you click this check box, select either is equal to or is not equal to as a comparison operator.

Match Case Click the Match Case check box if you require the results to match the case of the input.

Package type Basic

Alters schema No



Sorted input No


Table 49:Filter settings (continued)

Setting Description

Key Generation


Key Generation

Use the Key Generation to generate a primary key for use in populating a dimension table.

Figure 43:Key Generation dialog


Settings in the Key Generation Dialog

Tip for the Key Generation Transform

Key Generation Characteristics

Settings in the Key Generation Dialog

To define your Key Generation, use the following table to guide your selections.

Table 50:Key Generation settings

Setting Description

Key Column

Type the name of the key column in the dimension table. This column must be of integer type. If this column does not exist in the dimension table, the column is generated.

Start Key Value At

If you are generating a new key, or overwriting existing key values, click the Start Key Value At radio button. To start generating the dimension key with a value other than zero or to append dimension records, type a starting value.

Key Generation


Tip for the Key Generation Transform

When you run a Plan that includes a Key Generation Transform, a new sequential integer value is generated for every input record in the data flow.

Key Generation Characteristics

Start Key Value From Next Available Value in

If you are appending records to a dimension table, start generating the dimension key with a value inherited from a key column. Click the Start Key Value From Next Available Value In radio button.

BaseView Select the BaseView that contains the table with the key column from the BaseView dropdown list.

Table Select the name of the table that contains the key column from the Table dropdown list.

Key Column

Select the name of the column to start the key value from the Key Column dropdown list.The Key Generation Transform finds the maximum integer value in this column. The new rows are appended to the dimension table using values sequential to this value.

Package type Basic

Alters schema Yes



Sorted input No


Table 50:Key Generation settings (continued)

Setting Description

Key Lookup


Key Lookup

The Key Lookup Transform uses the primary key values in target dimension tables to populate the foreign key columns. Because you populate dimension tables before fact tables, these primary key values already exist. Records to load into the fact table are still identified by old key values. Before records are loaded, old key values must be translated into corresponding new key values.

The Key Lookup Transform can also be used for:

validating data against a validation table

checking for a specific row in a slowly changing dimension table

completing a simple lookup such as finding a state name using a state code

Figure 44:Key Lookup dialog


Settings in the Key Lookup Dialog

Tips for the Key Lookup Transform

Key Lookup Characteristics

Key Lookup


Settings in the Key Lookup Dialog

To define your Key Lookup, use the following table to guide your selections.

Table 51:Key Lookup settings

Setting Description

BaseViewSelect the BaseView that contains the lookup table from the BaseView dropdown list. This is usually the target dimension table.

Table Select the name of the dimension table from the Table dropdown list.

Column

Select the name of the column that contains the value to look up from the Column dropdown list. In most population scenarios, this is the primary key column in the target dimension table.

New Field

Click New Field to place the resulting value in a new column in the fact table. Specify the column name. If New Field is selected and a column with this name exists, the existing field is used. If not, it is created.

Existing Field Click Existing Field to use an existing column. Specify the column name.

Default Error Value

Specify a default error value to insert a value into the fact table key field where a lookup error has occurred. Lookup errors occur when field values in the dimension table primary key column do not match the field values in the source table. This value flags the records that contain a lookup error. The default is a null value. You can specify any value.

Cache Size (records) Specify the cache size in number of records.

Cache Whole Lookup Table

Click Cache Whole Lookup Table to load the dimension table into memory on the Oracle BAM Enterprise Link Server when this step of the data flow is executed. This improves lookup performance if the records from key columns fit in memory.

Trim Trailing Spaces Click to remove spaces after the values.

Match Case Click the Match Case check box if you require the results to match the case of the input.

Input Table Column

Select the name of the natural key column in the source table from the Input Table Column dropdown list to use as input for the lookup operation. This is the old natural key in the source.

Key Lookup


Tips for the Key Lookup Transform

A key lookup operation compares the old key value of a source record to old key values now stored in the dimension table. When a match is found, the Key Lookup Transform determines the new key value for that record and inserts the new value into the fact table.

When you populate a fact table, a series of key lookups determines the values inserted in foreign key columns. Each input record is assigned a primary key value from each dimension table. This combination of key values uniquely identifies the record, and forms the concatenated key of the fact table. Key columns in the fact table are empty until the dimension table keys are looked up.

Target dimension tables provide the connection between old and new key values. When populating dimension tables, include the Part representing the old key in the Plan. A new key is generated, and the old key is stored for reference in the new table.

The Key Lookup Transform can be run in debug mode. In this mode, the Transform runs without accessing the database. The default value is returned for each row. This can help you debug Plans and find performance issues.

The values in the SQL and Properties tabs must be consistent. If you modify a value in the Properties tab after editing the lookup SQL, you are prompted to regenerate the SQL based on new values in the Properties tab.

Lookup Table Column

Select the name of the natural key column in the target dimension table from the Lookup Table Column dropdown list. This is the old key column stored for reference in the target dimension table.

Add Click Add to map the old key column in the source to old key column in the target.

Remove Click Remove to delete the mapping.

Ignore Null Keys Check to ignore records with null keys

Table 51:Key Lookup settings (continued)

Setting Description

Key Lookup


The Key Lookup Transform must return either 0 or 1 for each lookup. If more than one row is returned, an error occurs. In this case, eliminate the duplicate rows in the lookup table or modify the generated SQL so that only one row is returned. For example, include MAX in the statement.

With a dimension load Plan performing a Key Lookup for new records, SQL Query fails to trim trailing spaces. You can add an Expression Calculator to the data flow after the SQL Query and perform a Right Trim on the input field for the Key Lookup.

Key Lookup Characteristics

Package type Basic

Alters schema Yes


Copied or Inplace Both

Sorted input No


Memory Sort


Memory Sort

Use the Memory Sort to sort records in the data flow by the specified columns, in ascending or descending order.

Depending on which steps are between the SQL Query source and the Memory Sort step in the data flow, the Data Flow Service sorts the records either in memory or by adding a SQL ORDER BY clause to the SQL Query source. The Memory Sort Transform is optimized to select the best sorting method.

Figure 45:Memory Sort dialog


Settings in the Memory Sort Dialog

Tips for the Memory Sort Transform

Memory Sort Characteristics

Memory Sort


Settings in the Memory Sort Dialog

To define your Memory Sort, use the following table to guide your selections.

Tips for the Memory Sort Transform

The sequence of the columns in the Sorted Columns list determines the sequence that sorting is applied. Each column can have a different sort order. The first column in the Sorted Columns list is sorted first. Other columns are ordered relative to this column.

If the sort cannot be performed by the RDBMS, the CaseSensitiveSort setting for this BaseView in Oracle BAM Enterprise Link Admin determines whether case affects sort order. For more information, see the Oracle BAM Enterprise Link Administrator's Guide.

Table 52:Memory Sort settings

Setting Description

Unsorted ColumnsClick the name of a column you want to order your result set by. Either drag it to the Sorted Columns list or click the right arrow button.

Sorted ColumnsTo change the sort order of a column, click it and then click either the Ascending or Descending button. Ascending is the default value.

Case Sensitivity DefaultClick Default if you want the database server to sort records using its default method of handling case (recommended).

Sensitive or Insensitive

Click Sensitive or Insensitive only if the method of handling case is essential. If this value conflicts with RDBMS defaults, the sort is performed by the Data Flow Service.

Null Precedence DefaultClick Default if you want the RDBMS to sort records using its default method of handling nulls (recommended).

Smallest or Greatest

Click Smallest or Greatest only if the method of ordering null values is essential. If this value conflicts with RDBMS defaults, the sort is performed by the Data Flow Service.

Memory Sort


If the sort cannot be performed by the RDBMS, the NullsFirst setting for this BaseView in Oracle BAM Enterprise Link Admin determines where null values are placed in the sort order. For more information, see the Oracle BAM Enterprise Link Administrator's Guide.

Sometimes you receive a different Memory Sort result when you place a Splitter before the Memory Sort Transform than when you place it after the Transform. This is because the sort occurs internally in one case, and at the database in the other.

To use the Memory Sort Transform with edited SQL, you must type the name of the column to use as the primary sort column, and click Add. You must use the qualified name of the column. The column name displays in the Sorted Columns list.

Memory Sort Characteristics

Package type Basic

Alters schema No



Sorted input Yes


Message Digest


Message Digest

The Message Digest Transform builds keys based on any type of input data. A key is a single field value derived from the values of several different fields. The new key field is used as an index field. A key produced by the Message Digest Transform functions (in most cases) like a primary key. Once a key has been created, you can use it to sort records in a file or match records between one or more databases.

Record matching using Message Digest Transform-produced keys differs from traditional merge/purge record matching. The strength of traditional merge/purge matching is fuzzy matching. Fuzzy matching compares records by looking at many different fields, and makes a value judgement as to whether the records are the same. When matching records using Message Digest Transform-produced keys, only the key fields from each record are compared. Comparing keys only determines if the data is exactly the same in both records. The strength of record matching using Message Digest Transform-produced keys is the ability to determine if a record has changed by examining only one field, rather than all of the fields that comprise a record. Matching records by comparing one field is much faster than comparing multiple fields in each record.

You can also use the Message Digest Transform to encrypt raw data for use by another transform. For example, if you were comparing field data in another transform, but you did not want to send the raw data openly, you could make a key of the relevant fields and send the keys as input into the other transform. The input keys could then be compared with the resident keys of the other transform. If the input data were intercepted, the keys would not reveal the original data. The probability of reverse engineering an original record from the generated key is extremely low.

Key Construction

The Message Digest Transform uses public domain MD5 algorithms to produce a key from raw input data fields. These algorithms process the input data to such an extent that changing the input data slightly (even a small change in one field) can produce vastly different keys. If a file is sorted on this key field, similar but slightly different records will not be placed closely together.

Message Digest


Conversely, similar keys can be produced from very different initial data. Since the probability of generating the same key from different data is very low, the probability is high that if two keys match, they were produced from the same data.


Settings in the Message Digest Transform

Using the Message Digest Transform

Settings in the Message Digest Transform

The Message Digest Properties dialog has the sections:

Input Fields

Output Field

Figure 46:Message Digest Properties dialog, data source defined

Message Digest


Figure 47:Message Digest Properties dialog, data source not defined

To define your Message Digest Properties settings, use the following table to guide your selections.

Table 53:Message Digest Properties settings

Setting Description

Input Fields

This area is used to tell the Transform what fields to use when generating the key. If the input data source is defined, all of the fields from your input data source are displayed in this area. You can select one or more of these fields to build a key field.If a data source is not defined, the Message Digest Transform does not know the structure of the input data, including field names. In this case, the fields displayed here are those which you have previously selected for building the key.Selected fields are concatenated together and processed to produce an output key field. The output key field is always the same size (32 bytes), no matter how many input fields are selected. You may select any number of fields.It does not matter what data is contained in the fields; the Message Digest Transform will work with any data type.The output key depends upon the order in which the input fields are concatenated. The same field data will produce different keys if concatenated in a different order.

Output FieldThis field is created by the Message Digest Transform, and contains the output keys. You select the name of the field by typing the name into the text box.

Message Digest


Using the Message Digest Transform

Input Fields

If you have not defined a data source, you have the following options:

If you have defined a data source, you have the following options:

Output Field

Type a name in the Output Field text box to select the name of the output key field.

Table 54: Input field options if data source not defined

Option Action

Add Field Names

For each field you want to build the key with, enter the name of the field from the data source and click Add.Once you define a data source, the Message Digest Properties dialog will list all the fields in the input data source, but only the fields that you manually added will be selected for use in building the key.

Remove Field Names

To remove a field name from the list, select the field and click Remove.

Change Concatenation Order

The order in which the fields are listed determines the order of field concatenation. To change the concatenation order, select a field and press the up or down arrow button.

Table 55: Input field options for defined data source

Option ActionSelect Field Names

Select the fields to be used in building a key by clicking the box to the left of the field name. You may select one or multiple fields.

Change Concatenation Order

The order in which the fields are listed determines the order of field concatenation. To change the concatenation order, highlight a field by clicking on the field name and press the up or down arrow button.

Oracle BAM Lookup


Oracle BAM Lookup

Use the Oracle BAM Lookup Transform to perform lookups on key fields from data in a data flow and data in a data object. You can map an input data flow field to multiple output data object columns. You can select which columns to output and specify values for mapped data object columns when there is no match or when there are multiple matches.

The Oracle BAM Lookup Transform can be used for:

Validating data against a validation table.

Checking for a specific row in a slowly changing data object.

Completing a simple lookup such as finding a customer name using a customer code.

Figure 48:Oracle BAM Lookup dialog


Settings in the Oracle BAM Lookup Dialog

Oracle BAM Lookup Characteristics

Oracle BAM Lookup


Settings in the Oracle BAM Lookup Dialog

To define your Memory Sort, use the following table to guide your selections.


Setting Description

ADC Server Type the ADC Server machine name. The default name is Main.

Data Object Select the name of an existing data object to use for the lookup operation.

Cache Entire Lookup Table

Click the Cache Entire Lookup Table checkbox if you want to cache lookups instead of using source data each time. This produces better performance for slowly changing dimensions. If dimensions are constantly changing, keep caching disabled so that the new data is used each time.

Initial Cache Size

If caching is enabled, type a number of records for the initial cache size. For best performance, choose a prime number. To minimize collisions, the size should be about 20 percent larger than the largest data set.

Data Flow FieldSelect the name of the natural key column in the source from the Data Flow Field dropdown list to use as input for the lookup operation.

Data Object Column

Select the name of the natural key column in the ADC from the Data Object Column dropdown list. Click Add. The mapping of the data flow field in the source to the data object column in the ADC displays in the field below the Add button. Click Remove to delete any mapped keys that you do not want. To edit a mapped pair, remove the item and add it again.

Oracle BAM Lookup


Oracle BAM Lookup Characteristics

Output Data Object Columns

In the Output Data Object Columns section, you must add all of the columns that you want to output from the data object schema, not just the mapped key pairs. You can specify values for a mapped data object column when there is no match or when there are multiple matches. For other output columns, leave the value as <<null>>.Select a data object column from the Output Data Object Columns dropdown list. Select a value to insert if there is no match between the data flow field and data object column. The value can be null, -1, NA, or typed-in. The data type of the value must match the column data type.Select a value to insert if there is more than one match between the data flow field and data object column. The value can be null, -1, NA, or typed-in. The data type of the value must match the column data type. Click Add.

Remove

The data object column name and the specified values display in the field below the Add button. Click Remove to delete any mapped outputs that you do not want. To edit a value setting, remove the item and add it again

Package type Basic

Alters schema No



Sorted input Yes



Setting Description

Pivot


Pivot

Use the Pivot Transform to aggregate and transpose column values in the data flow. With this Transform, you can arrange input columns into a crosstab format that displays rows, columns and summarized values. You can also use fields as input and not display them.

In a population Plan, this Transform is useful for identifying unique values in a data set, aggregating the unique values, and placing the results in a column.


Settings in the Pivot Dialog

Tips for the Pivot Transform

Pivot Characteristics

Settings in the Pivot Dialog

To define your Pivot Transform, open the dialog and set the following tabs:

General Tab

Advanced Tab

Pivot


General Tab

To define your Pivot Transform, select the General tab and use Table 57 to guide your selections.

Figure 49:Pivot dialog

Table 57:Pivot settings

Setting DescriptionColumns Type a column name.

Add buttons Click the Add button to add the item to the list.

Group Column DataIf a Column list field is a datetime data type, you can click Group Column Date to select date groupings for your Grid columns.

Rows Type a row name.

Pivot


Values

You can add multiple input fields as values. You can also add the same input field as a value multiple times, so that each occurrence can perform a different operation.

Operations Choose an operation to summarize each value from the Operations field.

Decimal Sum

This section allows you to manually set Precision and Scale values for decimal fields, in order to increase the precision of the output decimal field. The Decimal Sum section is active when a sum operation is requested on a decimal field regardless of whether decimal math is enabled. For the dialog that appears when the schema is unknown, the Decimal Sum section is enabled for a sum operation on any field (because it is not known if the field is of type decimal). The information will be ignored at run time if the output field is not of type decimal.If you want to change the default values, enter different values for Precision and Scale.See “Decimal Math Calculations” on page 186for more information about decimal fields and decimal field calculations.

Table 57:Pivot settings (continued)

Setting Description

Pivot


Advanced Tab

To set up advanced features on your Pivot Transform, select the Advanced tab and use Table 58 to guide your selections.

Figure 50:Advanced tab dialog

Table 58:Advanced tab settings

Setting Description

Data is pre-sorted Select this check box if the input data is already sorted.

Pivot


Tips for the Pivot Transform

If a Column value is specified, no output schema displays. You can create an output schema after the Pivot Transform using the Add Columns Transform.

If no Column value is specified, an output schema displays.


Pivot Characteristics

Package type Basic

Alters schema Yes



Sorted input Yes


Rank


Rank

Use the Rank Transform to output the top or bottom ranked rows of a numeric column. You can rank a specified number of top or bottom column values, or a specified top or bottom percentage of all rows. For example, you can return the 10 highest values in a column, or the top 10 percent of all the rows in a column.


Settings in the Rank Dialog

Rank Characteristics

Settings in the Rank Dialog

To define your Rank Transform, open the dialog and set the following tabs:

General Tab

Advanced Tab

Rank


General Tab

To define your Rank Transform, select the General tab and use Table 59 to guide your selections.

Figure 51:Rank dialog

Table 59:Rank settings

Setting DescriptionRank Based on Column Select a column to rank from the dropdown list.

Items Type the number of ranked rows to output, or the percentage of the rows to output in the Items field.

Count Click Count to return the specified number of highest or lowest rows.

Percent Click Percent to return the specified percentage of the total number of rows.

Top Click Top to specify the high end of the set to output.

Bottom Click Bottom to specify the low end of the set to output.

Add Rank Number Column Click the Add Rank Number Column check box if you want to number the ranked results in a new column.

Column Name Type a name for the new column in the Column Name field. Your column name must not include spaces.

Rank


Advanced Tab

To set up advanced features on your Rank Transform, select the Advanced tab and use Table 60 to guide your selections.

Figure 52:Advanced tab dialog

Rank Characteristics

Table 60:Advanced tab settings

Setting Description

Data is pre-sorted Select this check box if the input data is already sorted.

Package type Basic

Alters schema Yes



Sorted input Yes


Record Number


Record Number

Use the Record Number to add a Plan column that displays a sequential number, beginning with 1. The Record Number Transform is useful when you want to display a count of rows in data.

Figure 53:Record Number dialog


Settings in the Record Number Dialog

Record Number Characteristics

Settings in the Record Number Dialog

To define your Record Number Transform, open the dialog and specify the following setting.

Record Number Characteristics

Table 61:Record Number setting

Setting DescriptionEnter Column Name Type a name for the new record number column.

Package type Basic

Alters schema Yes



Sorted input No


Rename Columns


Rename Columns

Use the Rename Columns to specify a new name for an input column in a data flow.

Figure 54:Rename Columns dialog


Settings in the Rename Columns Dialog

Tips for the Rename Columns Transform

Rename Columns Characteristics

Settings in the Rename Columns Dialog

To define your Rename Columns settings, use the following table to guide your selections.

Table 62:Rename Columns settings

Setting Description

New Name

Click the new column name in the list and type a new name in the field.If you are working with edited SQL, no field names are listed.

Current NameIf you are working with edited SQL, type the column name as it appears in the Parts Bin in the Current Name field.

Apply Click Apply to complete the mapping.

Rename Columns


Tips for the Rename Columns Transform

This Transform is useful when you want to use name-based mapping to load data into a database, and the source and target columns have different names. Add this Transform just before the sink Transform in the data flow to pass the new name to the sink. You might also use this Transform to rename a calculated field.

This Transform is also useful if you are using a single BaseView to load multiple target tables, and columns in the target tables have different names.

If the input for this step is a query that was edited in the SQL Editor dialog, no existing input columns are listed, and you must type the current and new column names. You must also type column names if a previous Transform does not correctly pass Part names to the Rename Columns Transforms, or if there are no Parts in the Workspace.

Rename Columns Characteristics

Add If you are working with edited SQL, click Add to complete the mapping.

Remove If you are working with edited SQL, click Remove to remove the mapping from the list.

Package type Basic

Alters schema Yes



Sorted input No


Table 62:Rename Columns settings (continued)

Setting Description

Search & Replace


Search & Replace

Use the Search & Replace to search a column for a specified string or substring and replaces all occurrences of that string with a different string in a new or existing column. You can use regular expressions to define your search string. You can also specify whether to match upper and lower case characters in the search string.

Figure 55:Search and Replace dialog


Settings in the Search & Replace Dialog

Tips for the Search & Replace Transform

Search & Replace Characteristics

Search & Replace


Settings in the Search & Replace Dialog

To define your Search & Replace settings, use the following table to guide your selections.

Tips for the Search & Replace Transform

Only string type columns display in the Originating Column dropdown list.

To broaden the search criteria, use regular expressions in the search string.

To perform more than one search and replace operation, use the Expression Calculator for better performance.

Table 63:Search & Replace settings

Setting Description

Originating Column

Select the input column to search from the Originating Column dropdown list. If the schema is unknown or you have edited SQL, type the name of the input column.

Find What Type a search string.

Replace with Type a replacement string.

Regular Expressions Click the Regular expressions check box to use regular expressions in the search string.

Match caseClick the Match case check box if you want the search to match upper and lower-case characters, as you typed them in the Find What field.

New FieldChoose New Field to place the replacement string in a new field. Type a name for the new column. Your column name must not include spaces.

Existing Field Choose Existing Field to place the replacement string in an existing column. Type the column name.

Search & Replace


Search & Replace Characteristics

Package type Basic

Alters schema Yes



Sorted input No


Substring


Substring

Use the Substring to extract a substring from a string value and place the result in a new or existing column. You define the substring by specifying a start position and a length.

Figure 56:Substring dialog


Settings in the Substring Dialog

Tips for the Substring Transform

Substring Characteristics

Substring


Settings in the Substring Dialog

To define your Substring settings, use the following table to guide your selections.

Tips for the Substring Transform

Only string type columns appear in the Originating Column dropdown list.

For example, if you are defining the substring 164 from the source string (164) 187-7451, then the starting position is 2, because 1 is the second character in the source string.

Substring Characteristics

Table 64:Substring settings

Setting Description

Originating Column Select the input column to search from the Originating Column dropdown list.

Starting Position Specify the numeric position of the character that begins the substring.

Substring Length Specify the length of the substring. Type 0 to select all the remaining characters in the string.

New FieldClick New Field to add a column to the output for the computed values. You can edit the default name for the new field.

Existing FieldClick Existing Field to place the output in an existing field. If you select an Existing Field that is shorter than the substring, the substring is truncated.

Package type Basic

Alters schema Yes



Sorted input No


Transpose


Transpose

Use the Transpose to transform column data into rows in the data flow. Transposing data is the opposite of pivoting data using the Pivot Transform, which transforms row data into columns.

Figure 57:Transpose dialog


Settings in the Transpose Dialog

Tips for the Transpose Transform

Transpose Characteristics

Transpose


Settings in the Transpose Dialog

To define your Transpose Transform, open the dialog and specify the following settings.

Table 65:Transpose settings

Setting Description

Available Columns

Click the name of a column to transpose and either drag it to the Transposed Columns list or click the right arrow button. To select more than one column, use the Shift and Control keys.The column name displays in the Transposed Columns list. If you transpose more than one column, the column types must be compatible.

Type Name of Columns to Transpose

If you have user-entered SQL, you must type the name of a column to transpose. If you transpose more than one column, the column types must be compatible.

Add If you have user-entered SQL, click Add to add the column to the Transposed Columns list.

Transposed Columns The column names that are transposed display in the Transposed Columns list.

For Column HeadersType a name for the column that will contain existing column headers. This new column is automatically added to the data flow. Its default name is Column1.

For Transposed Row Values

Type a name for the column that will contain transposed row values. This new column is automatically added to the data flow. Its default name is Column2.

Output tab

On the Output tab, click to deselect any columns you do not want passed to the next Transform in the data flow. By default, all input and generated columns are passed on to the next data flow step.

Transpose


Tips for the Transpose Transform

Transposing one or more columns adds two new columns to the data flow. The first column contains headers, or Part names, of all transposed columns. The second column contains the values from the transposed columns. For example, if you transpose Sales in Dollars, when the Plan is run the first new column contains the header, Sales in Dollars. The second column contains dollar values.

If you transpose more than one column, the column types must be compatible.

To perform more than one transpose operation in the same data flow, create a duplicate data set with a Splitter Transform. Transpose each set of data, then merge the data sets into one with a Union Transform.

If you need to perform more than one transpose operation in the same data flow, create a duplicate data set with a Splitter Transform. Transpose each set of data, then merge the data sets into one with a Union Transform.

Column Type Compatibility Table

Use the following table to match column types for the Transpose Transform.

Table 66:Column type compatibility matrix

int long string datetime double uint binary decimal

int True True False False True True False True

long True True False False True True False True

string False False True False False True True False

datetime False False False True False False False False

double True True False False True True False True

uint True True False False True True False True

binary False False True False False False True False

decimal True True False False True True False True

Transpose


Transpose Characteristics

Package type Basic

Alters schema Yes



Sorted input No


VBScriptCopy


VBScriptCopy

Use the VBScriptCopy Transform to:

copy the input record and manipulate its data and its column attributes.

to add or remove columns, or change any of the column attributes, such as size, type, and name.

The VBScriptCopy Transform has no settings. The dialog includes a text field where you type VBScript and click the OK button to close the dialog and save your changes.

For more information on the VBScript Transforms, see the Oracle BAM Enterprise Link Design Studio User’s Guide or the Oracle BAM Design Studio online help.


Package type Basic

Alters schema Yes



Sorted input No


VBScriptInplace


VBScriptInplace

Use this Transform to process each record in place, so only the data of the input record can be altered. You cannot add or remove columns, or change any of the column attributes. For example, a column in the input record with a 10-character string cannot be output with more than 10 characters, although it can be output with fewer.

The VBScriptInplace Transform has no settings. The dialog includes a text field where you type VBScript and click the OK button to close the dialog and save your changes.

For more information on the VBScript Transforms, see the Oracle BAM Enterprise Link Design Studio User’s Guide or the Oracle BAM Design Studio online help.


Package type Basic

Alters schema No



Sorted input No



Appendix A

Expression CalculatorFunctions

In this sppendix:

Date and Time Functions

String Functions

Mathematical Functions

Logical Operators

Binary Operators

Miscellaneous Operators

Calculator Operators




Use these functions to return part of a datetime value, calculate the interval between two values, or construct a new value.

Table 67:Date and time functions

Operator DescriptionData Type Restrictions

dtAddDays (date, n)adds n days to the date value. Takes days in month and leap years into account.

datetime only; n is an integer

dtAddMinutes (date, n)adds n minutes to the date value. Takes days in month and leap years into account.


dtAddMonths (date, n)adds n months to the date value. Takes days in month and leap years into account.


dtAddYears (date, n)adds n years to the date value. Takes leap years into account.


dtAge (date)returns the age, in years, between date and the current date

datetime only

dtCurrentDT () returns the current date and time takes no input

dtDateFromDouble (double)

constructs a datetime value from a double value. Does not take days in month or leap years into account.

double only

dtDateFromJulian (long)constructs a datetime value from a julian date value. The time value is 00:00:00.

long integer only

dtDateFromPStr (str, format)

constructs a datetime value from packed string str where format=1 if mmddyy or mmddyyyy2 if yyyymmdd

string only, where str is either 6 or 8 characters long


Appendix A: Expression Calculator Functions 175

dtDateToDouble (date)

constructs a double value from a datetime value, beginning at midnight, Dec. 30, 1899. The time value is expressed as a decimal portion of a day. Does not take days in month or leap years into account.

datetime only; date values from Jan. 1, 0100 to Dec. 31, 9999

dtDateToJulian (date)

constructs a julian date value from a datetime value. Julian dates begin at Jan. 1, 4713 B.C. The time data is lost.

datetime only

dtDateToString (dateval, format)

returns the passed-in date as a string value. The format of the returned string depends upon the format parameter.The format string can be any of the following:MM/DD/YYYYMM/DD/YYM/D/YYDD-MM-YYYYDD-MM-YYD-M-YYMMDDYYYYMMDDYYDDMMYYYYDDMMYYYYYYMMDDYYMMDD

dateval must be of type datetimeformat must be a string

dtDayOfWeek (date) returns the day of the week value. 1=Sunday. datetime only

dtDayofYear (date) returns the day of the year datetime only

dtDaysInMonth (month, year) returns the number of days in month

month and year are integers; if year is 2 digits, < 29 is assumed 20xx, >30 is assumed 19xx

dtDaysInYear (year)returns the number of days in year. Takes leap years into account.

if year is 2 digits, <29 is assumed 20xx, >30 is assumed 19xx

Table 67:Date and time functions (continued)




dtDiffDays (d1, d2)returns the difference between d1 and d2, in days as a double

datetime only

dtDiffHours (d1, d2)returns the difference between d1 and d2, in hours as a double

datetime only

dtDiffMinutes (d1, d2)returns the difference between two dates, in minutes as a double

datetime only

dtDiffMonthss (d1, d2)returns the difference between two dates, in months as a double

datetime only

dtDiffSeconds (d1, d2)returns the difference between two dates, in seconds as a double

datetime only

dtDiffYears (d1, d2)returns the difference between two dates, in years as a double

datetime only

dtGetDay (date) returns the day of the month value datetime only

dtGetHour (date) returns the hour value datetime only

dtGetMinute (date) returns the minute value datetime only

dtGetMonth (date) returns the month value datetime only

dtGetSecond (date) returns the second value datetime only

dtGetYear (date) returns the year value datetime only

dtIsLeapYear (year or date) returns 1 if the year is a leap year; otherwise returns 0.

date is of datetime type; if year is 2 digits, <29 is assumed 20xx, >30 is assumed 19xx

MakeDate (month, day, year)constructs a datetime value with date only. The time value is 00:00:00.

month, day and year are integers; if year is 2 digits, <29 is assumed 20xx, >30 is assumed 19xx





MakeDT (month, day, year, hours, minutes, seconds)

constructs a datetime value with time and date

month, day, year, hours, minutes and seconds are integers; if year is 2 digits, <29 is assumed 20xx, >30 is assumed 19xx.

MakeTime (hours, minutes, seconds)

constructs a datetime value with time only. The date value is Dec. 30, 1899.

hours, minutes and seconds are integers



String Functions


String Functions

The following functions manipulate string values. You can use these functions to change case, extract substrings, trim or pad string values, calculate length, and compare two string values.

Table 68:String functions

Operator DescriptionConcat(str1, str2) concatenates str1 and str2

IsDateTime(str) returns 1 if string is DateTime value, otherwise returns 0

IsDecimal(str) returns 1 if string is decimal value, otherwise returns 0

IsDouble(str) returns 1 if string is double value, otherwise returns 0

IsInt(str) returns 1 if string is integer value, otherwise returns 0

Left(str,n) extracts the n left most characters from str

Mid(str,n,m) extracts a substring from str starting at n and m characters in length

Right(str,n) extracts the n right most characters from str

ToUpper(str) converts str to uppercase

ToLower(str) converts str to lowercase

TrimLeft(str) trims white space from left side of str

TrimRight(str) trims white space from right side of str

PadLeft (str1, str2, n) pads str1 on the left with the sequence of characters in str2, n times

PadRight (str1, str2, n) pads str1 on the right with the sequence of characters in str2, n times

StrCmp(str1, str2)compares str1 to str2. If str1 precedes str2 in ASCII collation sequence, returns -1. If str2 precedes str1, returns 1. If neither takes precedence, returns 0.

StrLength (str) returns the length of str in characters

Substring (str1, str2) returns the position of str2 within str1




Mathematical functions perform a mathematical operation on a numeric value or values and return a numeric value as the result.

Table 69:Mathematical functions


Min(x,y) compares x and y and returns the smaller value no datetime or string

Max(x,y) compares x and y and returns the larger value no datetime or string

Range(x,y,z) Returns “1” if x is between y and z; otherwise “0” is returned

Accepts any numeric type for all 3 parameters

Round(x) returns x rounded to the nearest integer double or decimal only

RoundTo(x,y)

rounds the decimal to Scale digits after the decimal point. It takes parameters (decimal, scale).The value 0 as the Scale parameter causes rounding to the nearest integer value.

decimal only

Trunc(x) returns x truncated to the integer value portion double or decimal only

TruncateTo(x,y)

truncates the decimal to Scale digits after the decimal point. This function takes two parameters (decimal, scale). The value 0 as the Scale parameter truncates all digits to the right of the decimal point.

decimal only

Ceil(x)returns the smallest value greater than or equal to x. The data type returned is double.

double or decimal only

Floor(x)returns the largest value less than or equal to x. The data type returned is double.

double or decimal only

Abs(x) returns the absolute value of x long, double, decimal, or int only

Sqrt(x) returns the square root of x double or decimal only. x must be a positive value.

Mod(x,y) returns the remainder of x / y



Sin(x) returns the sine of x double only. x must be expressed in radians.

Cos(x) returns the cosine of x double only. x must be expressed in radians.

Tan(x) returns the tangent of x double only. x must be expressed in radians.

Asin(x) returns the arcsine of x double only. x must be expressed in radians.

Acos(x) returns the arccosine of x double only. x must be expressed in radians.

Atan(x) returns the arctangent of x double only. x must be expressed in radians.

Random(x) returns a random integer from 0 through x-1 double only

Table 69:Mathematical functions (continued)


Logical Operators


Logical Operators

Logical operators impose conditions on one or more values and return a single result based on the specified logic. The operands can be constants, custom properties, or expressions. These operators can be specified from the functions list, keypad, or keyboard.

Table 70:Logical operators

Operator Description

IF(x)THEN(y)ELSE(z) searches for condition x. If found, applies y; if not found, applies z.

SWITCH(x)CASE(y) : (z) DEFAULT(w) if x = y, execute z; if not, execute w.

(x)AND(y) searches for conditions x and y. If x and y are found and both non-zero, returns 1; otherwise, returns 0.

(x)OR(y) searches for conditions x and y. If either x or y is found and is non-zero, returns 1; otherwise, returns 0.

(x)XOR(y) searches for conditions x and y. If only one of x or y is found and non-zero, returns 1; otherwise, returns 0.

NOT(x) returns 1 if x is 0; otherwise, returns 1.

Binary Operators


Binary Operators

Binary operators require two operands, one on either side of the operator. The operands can be constants, custom properties, or expressions that, when evaluated, result in a numeric value. Specify the exponent or remainder operators by typing on the keyboard.

Table 71:Binary operators

Operator Description+ addition—adds two values

- subtraction—subtracts one value from another

* multiplication—multiplies two values

/ division—divides two values

x^n or x**n exponentiation—raises x to the nth power

n%x modulo—returns remainder of n divided by x




Casting operators convert a value from one data type to another. Restrictions on how the resulting value can be manipulated or expressed depend on attributes of the new data type.

Table 72:Miscellaneous operators

Operator DescriptionToDateTime(x) converts x to datetime type

ToDecimal(x,p,s) converts x to decimal type with Precision p and Scale s

ToDouble(x) converts x to double type

ToInt(x) converts x to integer type

ToLong(x) converts x to long type

ToString(x) converts x to string type

ToUint(x) converts x to unsigned integer type

NULL_INT returns a null integer value

NULL_UINT returns a null unsigned integer value

NULL_LONG returns a null long integer value

NULL_DOUBLE returns a null double value

NULL_STRING returns a null string value

NULL_DATETIME returns a null datetime value

NULL_DECIMAL returns a null decimal value




Relational and Equality Operators

Relational and equality operators allow logical comparisons of two values. These operators act on the values on either side of the expression. Except for the null value tests, which are in the Functions list, add these operators to an expression by typing on the keyboard.

Unary Operator

This unary operator changes the sign of the value in parentheses. Add this operator to an expression by typing on the keyboard.

Table 73:Relational and equality operators

Operator Description< tests for values less than

<= tests for values less than or equal to

> tests for values greater than

>= tests for values greater than or equal to

== tests for values equal to

!= or <> tests for values not equal to

IsNULL(x) tests for null values. If x is null returns 1, otherwise returns 0.

IsNotNULL (x) tests for non-null values. If x is not null returns 1, otherwise returns 0.

Table 74:Unary operator

Operator Description Data Type Restrictions-(x) returns the negative of x int, long, double, and uint only


Appendix B

Data Types

This appendix discusses Decimal Math, and lists the supported binary input data types for the Flat File Source Transform.

In this appendix:

Decimal Math Calculations

Supported Binary Input Data Types




For the sake of performance, decimal values are converted to type double when the values are used in calculations or comparisons. However, this process may result in a loss of precision. To ensure a specific accuracy of decimal results, you need to enable decimal math functionality. Transforms that do sorts or comparisons with decimal fields may be affected by enabling decimal math, because decimal values are no longer converted to type double.

A decimal data value is defined by two parameters: Precision and Scale. Precision is the number of significant digits, and Scale is the position of the decimal point. For example, the value 123.4567 has 7 significant digits, and so has a Precision of 7. There are four digits following the decimal point, so the value has a Scale of 4.

The following table lists the rules for decimal fields:

Table 75:Rules for decimal fields

The maximum Precision is 38.

The maximum Scale is 38.

Scale <= Precision. This rule is enforced.

For calculated decimal values:The default Precision is the maximum Precision of all the decimal fields used in the calculation.The default Scale is calculated by the functionMin[38 - Precision, Max[Scale of each decimal operand]]

Scale is ignored in all but the RoundTo, TruncateTo, and ToDecimal Expression Calculator Transform functions.

A result that has more digits than the Precision allows is rounded to fit the Precision.For example, in the computed value 123.456, if the Precision is 5, the result is rounded to 123.46.


Appendix B: Data Types 187

Automatic vs. Manual Setting of Precision and Scale

When defining a value to be of type decimal, you can let Oracle BAM Design Studio determine Precision and Scale, or you can set these values manually.

An Example of Setting Precision Manually

Manually setting Precision and Scale can be used, for example, to increase the Precision for a field when the result of the calculation does not fit into the default Precision.

For example, suppose a decimal field Dec1 has a Precision of 2, a Scale of 1, and a value of 1.2. You then define a calculated field:

Dec2 = Dec1 * 9

The new field Dec2 also has a Precision of 2 and a Scale of 1. But the value of Dec2 (10.8) can not be expressed in a decimal field with a Precision of 2 and Scale of 1. Therefore, you want to override the default settings for Precision and Scale, making the Precision at least 3. You could leave the Scale unchanged at 1.

If the length of the whole number portion of the computed result requires more digits than the Precision allows, a run-time error is generated.For example, if a result of 123.456 has a Precision of 2, no amount of rounding can give a meaningful result.

If there are more than Scale digits to the right of the decimal point in the computed result, and less than (Precision – Scale) digits to the left of the decimal point, then Oracle BAM Design Studio returns as many digits to the right of the decimal point as can be accommodated by the Precision.For example, imagine that a computed value of 123.456 has a Precision of 6 and Scale of 2. Since Precision – Scale = 4 and there are only 3 digits to the left of the decimal point, all three digits of accuracy are preserved to the right of the decimal point. If, however, the Precision of the result is 5 and the Scale is 2, the result is rounded to 123.46.

Table 75:Rules for decimal fields (continued)



Pitfalls of Manually Setting Precision and Scale

When calculating a decimal field, if you specify a result Precision that is different from the operand Precisions, the result precision is applied to the operands before the calculation is made. It is important to note that the original operand Precisions are NOT used in the calculation, with the calculated result Precision applied to the calculated field.

The following example illustrates this distinction.

Suppose you define a decimal field called Price and another decimal field called Cost, and each has a Precision of 10 and a Scale of 3. Each field is assigned the following values:

Price: = 123.40

Cost: = 123.35

Suppose that you use the Expression Calculator Transform to compute Profit:

Profit = Price – Cost

If you know that Profit is always going to be a fraction (always less than 1), and you then set the Profit Precision to 4 (because you know the answer will be approximately 0.05), then the calculated value of Profit will be incorrect.

This happens because the new Precision of 4 is applied to both Price and Cost, and the value of Profit is rounded off in order to fit the new Precision of 4:

Price = 123.4

Cost: = 123.4

Profit is calculated to be zero, which is incorrect.

However, if you instead let the value of Profit be determined automatically, the correct answer of 0.05 is calculated.

Note: This example applies only if the Precision of the result is set manually, with a value different from the decimal operands. If the Precision value is automatically determined, this example does not apply. Also note that the Scale setting does not matter for the purpose of this example.



Treatment of Literals

A literal numeric value in the Expression Calculator is treated as a double. For example, if you have a decimal field X in the expression:

X = 1.1

the system will cast the literal into type double (which could be a value such as 1.0999999999999). It seems clear that field X was assigned an undesirable value.

The proper way to ensure that a literal value is treated as a decimal type is to invoke the ToDecimal function as such:

X = ToDecimal(“1.1”, <precision>, <scale>)

Switching Decimal Math On and Off

The Windows Registry setting, UseDecimalMath, is a DWORD setting defined in the DC section. Set the value to 1 to perform all decimal type mathematical calculations with decimal accuracy. Set the value to 0 to manage decimals as doubles.

Note: By default, Decimal Math is turned off.

Additionally, there is a Use Decimal Math Provider check box on the Plan Properties dialog. This option enables and disables Decimal Math for the Plan in question.

Expression Calculator Functions that Use Decimals

The following table gives operational details about the Expression Calculator functions that use decimal fields.

Table 76:Expression Calculator functions that use decimal fields

Operator Description+-*/

If either operand is of type decimal, the result is of type decimal.

If-Then-ElseIf both the “then” and “else” phrases are of type decimal, the result is of type decimal. If only one phrase is of type decimal, an error results.



Switch-Case-Default

The “Switch-Case” conditional expressions must all be the same data type (and can be of type decimal). The “Case-Default” execution expressions must all be of the same data type, and determine the output of the function. For example, if the execution expressions are all of type decimal, the output will be of type decimal.The data type of all the conditional expressions must be the same, and the data type of all the execution expressions must be the same, but the data types of each section may be different.

MinMax

If every operand is of type decimal, the result is of type decimal. If all operands are not of the same type, an error results.

Range The Range function returns an integer (0 or 1). Each argument can be of any numeric data type.

UnaryAbsCeilFloorSqrt

Each function takes exactly one operand. If the operand is of type decimal, the result is of type decimal.

ToDecimal

Creates a decimal type out of another data type (other than datetime). This function also converts one decimal format to another decimal format with a different Precision and Scale.

AndOrXorNot==>>=<<=!=RoundTruncToDoubleToIntToLongToUIntToString

Each of these functions accepts decimal arguments.

Table 76:Expression Calculator functions that use decimal fields (continued)




RoundTo

This function takes two parameters (decimal, scale). It rounds the decimal to Scale digits after the decimal point.“0” is an acceptable value for the Scale parameter; it causes rounding to the nearest integer value.

TruncateTo

This function takes two parameters (decimal, scale). It truncates the decimal to Scale digits after the decimal point.“0” is an acceptable value for the Scale parameter; it truncates all digits to the right of the decimal point.

NULL_DECIMAL Returns a null decimal value.

Table 76:Expression Calculator functions that use decimal fields (continued)





The following data types are supported in the Flat File Source Transform.

Table 77:Data types supported by the Flat File Source Transform

Data Type Description

Packed Decimal

Variable-length sequence of binary-coded-decimal digits, packed two per byte with the more significant of each pair of digits in the high-order 4 bits of each byte. The low-order 4 bits of the final byte stores a code indicating the number’s sign as follows: hexadecimal C or F for positive, hexadecimal D for negative.

IBM 370 Small Integer Binary 2-byte number stored with the less-significant byte first. It has a range of -32,768 to +32,767.

IBM 370 IntegerBinary 4-byte number stored with the least-significant byte first. It has a range of -2,147,483,648 to +2,147,483,647.

IBM 370 Long Integer

Binary 8-byte number stored with the least-significant byte first. It has a range of-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

IBM 370 Small Unsigned Integer

Binary 2-byte number stored with the less-significant byte first. It has a range of 0 to +65,535.

IBM 370 Unsigned Integer Binary 4-byte number stored with the least-significant byte first. It has a range of 0 to _4,294,967,295.

IBM 370 Long Unsigned Integer

Binary 8-byte number stored with the least-significant byte first. It has a range of 0 to +18,446,744,073,609,551,615.

IBM 370 Float Binary 4-byte floating-point value in IBM 370 format.

IBM 370 Double Binary 8-byte floating-point value in IBM 370 format.

IBM 370 Extended Binary 16-byte floating-point value in IBM 370 format.

8086 Small Integer Binary 2-byte number stored with the more-significant byte first. It has a range of -32,768 to +32,767.

8086 IntegerBinary 4-byte number stored with the most significant byte first. It has a range of -2,147,483,648 to +2,147,483,647.

8086 Long IntegerBinary 8-byte number stored with the most significant byte first. It has a range of -2,147,483,648 to +2,147,483,647.



8086 Small Unsigned Integer Binary 2-byte number stored with the more significant byte first. It has a range of 0 to +65,535.

8086 Unsigned Integer Binary 4-byte number stored with the most significant byte first. It has a range of 0 to +4,294,967,295.

8086 Long Unsigned IntegerBinary 8-byte number stored with the most significant byte first. It has a range of 0 to +18,446,744,609,551,615.

8086 Float Binary 4-byte IEEE format floating point value.

8086 Double Binary 8-byte IEEE format floating point value.

Table 77:Data types supported by the Flat File Source Transform (continued)

Data Type Description


IndexAAdd Columns Transform 111adding

columns 111comments to Transforms 15record number columns 159

assignments 106

CCASE statements 131Column Select Transform 114columns

concatenating 117pivoting 151renaming 160transposing 167

comparing data 86Comparison Transform 86concatenating columns 117Concatenation Transform 117Conditional Splitter Transform 91connecting to OLE DB providers 40copied and inplace processing 7creating expressions 128

Ddata

comparing 86filtering 133

data flowsadding columns 111architecture 2joining 95removing columns 114splitting 101

unioning data 108Delimited Text File Source

Transform 19destination properties 106Disk Sort 120

settings 121tips and techniques 123using 121

display sinksGrid 64

Eediting expressions 130Expression Builder 128Expression Calculator 124

settings 125syntax 132tips and techniques 130using 125

expressionschanging the order of 131creating 128editing 130renaming 130

extracting substrings 165

Ffield-property mappings 106Filter Transform 133filtering data 133Flat File Source 25

attributes tab 28columns tab 32errors tab 34processing tab 29


settings 25supported input data types 38supported output data types 38tags tab 31tips and techniques 37using 25

Ggenerating keys 135Grids 64

Iignoring case 105improving performance 10inplace processing 7iteration 102Iteration Dialog settings 104

JJoin Transform

setting up 96tips and techniques 99using 96

joining two sets of columns 95

Kkey fields 105Key Generation Transform 135Key Lookup Transform 137keys

generating 135lookup 137

Llimitations

Grid 64lookup

keys 137

MMemory Sort Transform 141multipass processing 7

OOLEDB Source Transform 40Oracle BAM Alert Transform 66Oracle BAM Delete Transform 69Oracle BAM Insert Transform 72Oracle BAM Lookup Transform 148Oracle BAM Message Sender

Transform 74Oracle BAM Message Tracker

Transform 76Oracle BAM Update Transform 78

Pperformance

improving 10testing batch loader 11

Pivot Transform 151pivoting columns 151Plans

data flows 2execution model 2improving performance 10

RRank Transform 156ranking values 156Record Number Transform 159removing

columns 114Rename Columns Transform 160renaming

columns 160expressions 130

reusing Transforms 15Round Robin Splitter Transform 100


Ssaving

Transforms for reuse 15schemas

about 11unknown 11

Search & Replace Transform 162single pass processing 7sorting

different types of 8Disk Sort 120Memory Sort 141Transforms requiring 9using Join 89, 98, 154, 158

source fields 106source Transforms

Delimited Text File Source 19Flat File Source 25OLEDB Source 40SQL Query 47VBScriptSource 52

Splitter Transform 101splitting data flows

conditionally 91in SubPlans 103simple 101using comparisons 86using round robin 100

SQL Query 47edited 11, 49settings 47tips and techniques 50using 47

SubPlanscharacteristics 107described 102terminating 102tips on using 106

Substring Transform 165syntax, Expression Calculator 132

TTerminal Sink 83tips and techniques

Disk Sort 123Expression Calculator 130Flat File Source 37Join 99SQL Query 50

Transform characteristicscomplete table 13defined 12

Transformscopied and inplace 7definition 2multipass and single pass 7

Transpose Transform 167transposing columns 167

UUnion Transform 108unioning data 108unknown schemas 11user-entered SQL 11using

CASE statements 131comparison criteria 86Expression Calculator syntax

132

Vvalidating data 137VBScriptCopy 171VBScriptInplace 172VBScriptSource 52viewing results

Grid 64

oracle business activity monitoring...preface x oracle bam enterprise link transforms reference...

Documents