Labels

Showing posts with label BODS Info. Show all posts
Showing posts with label BODS Info. Show all posts

Thursday, 18 February 2016

BODS Transformation Overview

BODS Transformation Overview/Bods List of Transformations



List of available transforms

  1. Data Integrator- Data_Transfer, Date_Generation, Effective_Date, Hierarchy_Flattening, History_Preserving, Key_Generation, Map_CDC_Operation, Pivot (Columns to Rows), Reverse Pivot (Rows to Columns), Table_Comparison, XML_Pipeline
  2. Data Quality- Associate, Country ID, Data Cleanse, DSF2 Walk Sequencer, Geocoder, Global Address Cleanse, Global Suggestion Lists, Match, USA Regulatory Address Cleanse, User-Defined
  3. Platform- Case, Map_Operation, Merge, Query, Row_Generation, SQL, Validation
  4. Text Data Processing- Entity_Extraction
Our approach is to get a detailed knowledge on all the above transforms starting with the mostly commonly used ones. So we will start with the Query transform.

QUERY Transform

Query transform is used to retrieve a data set based on the input schema that satisfies conditions that we specify. A query transform is similar to a SQL SELECT statement. The Query transform is used to perform the following operations: -
  • Maps column from input Schema to output Schema.
  • Perform transformations and functions on the source data.
  • Assign Primary Keys to output Schema columns.
  • Add New Output columns, Nested Schemas, and Function Calls to the output Schema.
  • Perform Data Nesting and Unnesting with Sub Schemas of the Output Schema. Also assign Make Current Schema.
  • Generate Distinct result set output for the input Schema.
  • Join data from Multiple Input Source Schemas. Equi Join as well as Outer Join is supported.
  • Filter input Source Data.
  • Performs Aggregation based on input column groups.
  • Generate sorted dataset based on source input column order.
  • Also we can generate DTD, XML Schema or File Format based on the Input or Output Schema.

CASE Transform

Case transform is used to divide or route the input data set into multiple output data sets based on the defined logical expression. It is used to implement IF-THEN-ELSE logic at dataflow level. This transform accepts only one source input. We can define multiple labels and their corresponding CASE expression. For input rows that do not satisfy any of the CASE conditions, we may select to output those records using theDEFAULT case. For that we need to select the check box Produce default output when all expressions are false.
Two other featured properties of this transform are Row can be TRUE for one case only and Preserve expression order. If we select the option Row can be TRUE for one case only, then a row is passed to the first case whose expression returns TRUE. Otherwise, the row is passed to all the cases whose expression returns TRUE. Preserve expression order option is available only when the Row can be TRUE for one case only option is checked. We can select this option if expression order is important to us because there is no way to guarantee which expression will evaluate to TRUE first.

MERGE Transform

Merge transform is used to combine multiple input dataset with the same schemas into a single output dataset of the same schema. It is equivalent to SQL UNION ALL statement. In order to eliminate duplicate records from output dataset basically to attain UNION operation, add a Query transform with DISTINCToption enabled after the Merge transform.

VALIDATION Transform

Validation transform is used to filter or replace the source dataset based on criteria or validation rules to produce desired output dataset. It enables to create validation rules on the input dataset, and generate the output based on whether they have passed or failed the validation condition. This transform is typically used for NULL ckecking for mandatory fields, Pattern matching, existence of value in reference table, validate datatype, etc.
The Validation transform can generate three output dataset Pass, Fail, and RuleViolation. The Pass Output schema is identical with the Input schema. The Fail Output schema has two more columns, DI_ERRORACTION and DI_ERRORCOLUMNS. The RuleViolation has three columns DI_ROWID, DI_RULENAME and DI_COLUMNNAME.

MAP_OPERATION Transform

Map_Operation transform allows conversions between data manipulation operations like INSERT, UPDATE, DELETE & REJECT. It enables to change the operation codes of input data sets to produce the desired output row type. There are 4 operation codes for any input row type – Normal, Update, Insert and Delete. In addition, the DISCARD option can be assigned to the output row type. Discarded rows are not passed through to the output of the transform.
If the output record is flagged NORMAL or INSERT, then it inserts a new record in the target table. If it is marked as UPDATE it basically overwrites an existing row in the target table. If the input record is flagged as Delete it does not load the records in the target table. But if the output row type is set to DELETE then it deletes the corresponding records present in target. If the row is marked as DISCARD then no records are passed to the output of the transform.

ROW_GENERATION Transform

Row_Generation transform produces a dataset with a single column. The column values start with the number that we specify in the Row number starts at option. The value then increments by one to specified number of rows as set in the Row count option. This transform does not allow any input data set.

SQL Transform

SQL transform is used to submit or perform standard SQL operations on database server. The SQL transform supports a single SELECT statement only. This transform does not allow any input data set. Use this transform when other built-transforms cannot perform the required SQL operation. Try to use this transform as your last option as it not optimised for performance and also reduces readability.
SAP BODS Platform Transform

KEY_GENERATION Transform

Key_Generation transform helps to generate artificial keys for new rows in a table. The transform looks up the maximum existing key value of the surrogate key column from the table; And uses it as the starting value to generate new keys for new rows in the input dataset. The transform expects a column with the same name as the Generated key column of the source table to be a part of the input schema.
The source table must be imported into the DS repository before defining the source table for this transform. The fully qualified Table name e.g. DATASTORE.OWNER.TABLE should be specified. Also we can set the Increment value i.e. the interval between the generated key values. By default it is 1. We can also use a variable placeholder for this option. We will be using this tranform frequently while populating surrogate key values of slowly changing dimension tables.

TABLE_COMPARISON Transform

Table_Comparison transform helps to compare two data sets and generates the difference between them as a resultant data set with rows flagged as INSERT, UPDATE, or DELETE. This transform can be used to ensure rows are not duplicated in a target table, or to compare the changed records of a data warehouse dimension table. It helps to detect and forward all changes or the latest ones that have occurred since the last time the comparison table was updated. We will be using this transform frequently while implementing slowing changing dimensions and while designing dataflows for recovery.
The source table must be already imported into the DS repository. The fully qualified Table name e.g.DATASTORE.OWNER.TABLE should be specified. Also set the input dataset columns that uniquely identify each row as Input primary key columns. These columns must be present in the comparison table with the same column names and datatypes. If the primary key value from the input data set does not match a value in the comparison table, DS generates an INSERT statement. Else it generates an UPDATE row with the values from the input dataset row after comparing all the columns in the input data set that are also present in the comparison table apart from the primary key columns. As per your requirement select only the required subset of non-key Compare columns which will give performance improvement.
If the Input primary key columns have duplicate keys, the transform arbitrarily chooses any of the rows to compare during dataflow processing i.e. order of the input rows are ignored. Selecting the Input contains duplicate keys check box provides a method of handling duplicate keys in the input data set.
If the comparison table contains rows with the same primary keys, the transform arbitrarily chooses any of the rows to compare. Specify the column of the comparison table with unique keys i.e. by design contains no duplicate keys as the Generated key column. A generated key column indicates which row of a set containing identical primary keys is to be used in the comparison. This provides a method of handling duplicate keys in the comparison table.
For an UPDATE, the output data set will contain the largest key value found for the given primary key. And for a DELETE, the output data set can include all duplicate key rows or just the row with the largest key value.
When we select the check box Detect deleted row(s) from comparison table the transform flags rows of the comparison table with the same key value as DELETE. When we select the options of the transforms - Generated key column, Detect deleted row(s) from comparison table and Row-by-row select or the Sorted input comparison method; Additional section appears to specify how to handle DELETE rows with duplicate keys. i.e. Detect all rows or Detect row with largest generated key value
Apart from all these properties there are three methods for accessing the comparison table namely Row-by-row select, Cached comparison table and Sorted input. Below is the brief on when to select which option.
  1. Row-by-row select option is best if the target table is large compared to the number of rows the transform will receive as input. In this case for every input row the transform fires a SQL to lookup the target table.
  2. Cached comparison table option is best when we are comparing the entire target table. DS uses pageable cache as the default. If the table fits in the available memory, we can change the Cache type property of the dataflow to In-Memory.
  3. Sorted input option is best when the input data is pre sorted based on the primary key columns. DS reads the comparison table in the order of the primary key columns using sequential read only once. NOTE: The order of the input data set must exactly match the order of all primary key columns in the Table_Comparison transform.
NOTE:
  • The transform only considers rows flagged as NORMAL as Input dataset.
  • Cautious when using real datatype columns in this transform as comparison results are unpredictable for this datatype.


BODS Designer

BODS Designer


Module Objectives
After completing this unit, you should be able to understand about:
–BODS tool Menu
–Tool Palates
–Different objects of BODS

Introduction
•The Data Integrator Designer provides a graphical user interface (GUI) development environment in which you defined at a application logic to extract, transform, and load data from data bases and applications into a data-ware house used for analytic and on-demand queries.
•You can also use the Designer to define logical paths for processing message-based queries and transactions from Web-based, front office, and back-office applications

Logging in to the Designer
•When we log in to the Data Integrator Designer, we are actually logging into the database we defined for the Data Integrator repository.
•Data Integrator repositories can reside on Oracle, Microsoft SQL Server, Informix, IBM DB2, Sybase ASE.



•We must configure a local repository to log into Data Integrator. Typically, we create a repository during installation. However, we can create a repository at any time using the Data Integrator Repository Manager.
•Each repository must be associated with at least one Job Server, which is the process that starts jobs.

•When running a job from a repository, we select one of the associated repositories.
•We can link any number of repositories to a single Job Server. The same Job Server can run jobs stored on multiple repositories.
•Occasionally, more than one person may attempt to log in to a single repository. If this happens, the Reset Users window appears, listing the users and the time they logged in to the repository

 Designer user interface
Data Integrator objects 

  • All ―entities we define, edit, or work with in Data Integrator Designer are called objects.
  • The local object library shows objects such as source and target metadata, system functions, projects, and jobs.
  • Data Integrator has two types of objects:
–Reusable objects
–Single-use objects
Reusable objects

  • After we define and save a reusable object, Data Integrator stores the definition in the local repository.
  • We can then re-use the definition as often as necessary by creating calls to the definition in the local object library.
  • The object library contains object definitions. When we drag and drop an object from the object library, we are really creating a new reference (or call) to the existing object definition.
  • Single-use objects
  • Some objects are defined only within the context of a single job or data flow, for example scripts and specific transform definitions.
Designer window
•The Data Integrator Designer user interface consists of a single application window and several embedded supporting windows.

Menu bar
•Menu bar contains a brief description of the Designer’s menus:
Project menu
Edit menu
View menu
Tools menu
Debug menu
Validation menu
Window menu
Help menu
Toolbar
•In addition to many of the standard Windows tools, Data Integrator provides application-specific tools, including:


Project area
The project area provides a hierarchical view of the objects used in each project. Tabs on the bottom of the project area support different tasks. Tabs include:




As we drill down into objects in the Designer workspace, the window highlights our location within the project hierarchy.

Tool palette
•The tool palette is a separate window that allows us to create new objects in the workspace. The icons are disabled when they are not allowed to be added to the diagram open in the workspace.
•The icons in the tool palette allows us to create new objects in the workspace. The icons are disabled when they are not allowed to be added to the diagram open in the workspace.
•When we create an object from the tool palette, we are creating a new definition of an object. If a new object is reusable, it will be automatically available in the object library after we create it.


Work space
•When we open or select a job or any flow within a job hierarchy, the workspace becomes ―active with our selection.
•The workspace provides a place to manipulate system objects and graphically assemble data movement processes.
•Workspace diagram is a visual representation of an entire data movement application or some part of a data movement application.
•We specify the flow of data through jobs and work flows by connecting objects in the workspace from left to right in the order we want the data to

Describing objects
we can use descriptions to add comments about objects. We can use annotations to explain a job, work flow, or data flow. We can view object descriptions and annotations in the workspace.



•When we drill into an object in the project area or work-space, a view of the object’s definition opens in the work space area. The view is marked by a tab at the bottom of the work space area.
•These views use system resources. If we have a large number of open views, we might notice a decline in performance.
•The local object library provides access to reusable objects. These objects include built-in system objects, such as transforms, and the objects we build and save, such as datastores, jobs, dataflows, and workflows.
•The local object library is a window in to our local Data Integrator repository and eliminates the need to access the repository directly.
•Updates to the repository occur through normal Data Integrator operation. Saving the objects we create, adds them to the repository. You can access saved objects through the local object library.

Local object library
Object Editors

•The editor displays the input and output schemes for the object and a panel below them lists options set for the object. If there are many options , they are grouped in tabs in the editor.
Object editors
In an editor, we can:
•Undo or redo previous actions performed in the window
•Find a string in the editor
•Drag-and-drop column names from the input schema into relevant option boxes
•Use colors to identify strings and comments in text boxes where we can edit expressions (keywords appear blue; strings are enclosed in quotes and appear pink; comments begin with a pound sign and appear green)
•We cannot add comments to a mapping clause in a Query transform.
•We can create reusable objects from the object library or by using the tool palette. After we create an object, we can work with the object, editing its definition and adding calls to other objects.
•We define an object using other objects. For example, if we click the name of a batch data flow, a new workspace opens for we to assemble sources, targets, and transforms that make up the actual flow.

Working with objects
•Objects dragged into the workspace must obey the hierarchy logic.
•For example,
–We can drag a data flow into a job, but we cannot drag a work-flow into a data-flow.
–We can change the name of an object from the workspace or the objectlibrary.
–We can also create a copy of an existing object. we cannot change the names of
built-in objects. Data Integrator makes a copy of the top-level object (but not of
objects that it calls) and gives it a new name, which we can edit.
–We can view (and, in some cases, change) an object’s properties through its property page.
–We can use descriptions to document objects. we can see descriptions on workspace diagrams. Therefore, descriptions area convenient way to add comments to workspace objects.
–A description is associated with a particular object. When we import or export that repository object, we also import or export its description.

Working with objects
•Annotations describe a flow, part of a flow, or a diagram in a workspace. An annotation is associated with the job, work flow, or data flow where it appears. When we import or export that job, work flow, or data flow, we import or export associated annotations.
•We can add, edit, and delete text directly on the annotation. In addition, we can resize and move the annotation by clicking and dragging. we can add any number of annotations to a diagram.
•We cannot hide an notations that we have added to the workspace. However, we can move them out of the way or delete them.
•―Saving an object in Data Integrator means storing the language that describes the object to the repository. We can save reusable objects. Single use objects are saved only as part of the definition of there usable object that calls them.
•We can choose to save changes to there usable object currently open in the workspace. When we save the object, the object properties, the definitions of any single use objects it calls, and any calls to other reusable objects are recorded in the repository. The content of the included reusable objects is not saved; only the call is saved.
•Data Integrator stores the description even if the object is not complete or contains an error (does not validate).
•Data Integrator also prompts us to save all objects that have changes when we execute a job and when we exit the Designer.