Optimal decision tree based multi-class support vector machine

In this paper, decision tree SVMs architecture is constructed to solve multi-class problems. To maintain high generalization ability, the optimal structure of decision tree is determined using statistical measures for obtaining class separability. The proposed optimal decision tree SVM (ODT-SVM) takes advantage of both the efficient computation of the decision tree architecture and the high classification accuracy of SVM. A robust non-parametric test is carried out for statistical comparison of proposed ODT-SVM with other classifiers over multiple data sets'. Performance is evaluated in terms of classification accuracy and computation time. The statistical analysis on UCI repository datasets indicate that ten cross validation accuracy of our proposed framework is" significantly better than widely used multi-class classifiers. Experimental results and statistical tests' have shown that the proposed ODT-SVM is significantly better in comparison to conventional OvO and OAA in terms of both training and testing time.

Povzetek: Metoda odlocitvenega drevesa s SVM dosega signifikantno boljse rezultate kot izvirni SVM.

Keywords: support vector machine, decision tree, class separability, information gain, Gini Index and Chi-squared, interclass scatter, intraclass scatter.

1 Introduction

Support Vector Machine (SVM) has been proved to be a successful learning machine in literature, especially for classification. SVM is based on statistical learning theory developed by Vapnik [6, 25]. Since it was originally designed for binary classification [3], it is not easy to extend binary SVM to multi-class problem. Constructing k-class SVMs (k > 2) is an on-going research issue [1, 4]. Two approaches are suggested in literature to solve multi-class SVM. One is considering all data in one optimization [7]. The other is decomposing multi-class into a series of binary SVMs, such as "One-Against-All" (OAA) [25] and "One-versus-One" (OvO) [16].
( | ) = () - ( | )

(4) where ( | ) is the information gain of the label for a given attribute E, ( )is the system's entropy and ( | ) is the system's relative entropy when the value of the attribute E is known.

The system's entropy indicates its degree of disorder and is given by the following formula

( )=- ()log( ( )) (5)

where ( ) is the probability of class C. The relative entropy is calculated as

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII]

Where ( ) is the probability of value j for attribute e, and C is the probability of C with a given

The optimal binary SVM model is selected on the basis of maximum value of that signifies more separability between patterns belonging to two different classes, for a given independent binary SVM containing [n.sub.i] elements of [C.sub.i] and [n.sub.j] elements of [C.sub.j] can be calculated as

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] (7)

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] (8)

(C) = -- and C = -- (9)

where , , , and denote number of true positive, false positive, true negative and false negative data points respectively.

The higher value of signifies less overlap or more distance between two different classes of data points. Hence, can be a natural measure to determine class separability of different classes of data points.

Similarly for every independent binary OAA SVM, assume there are two classes of dataset, C and C and training set D contains elements of C and elements of class C . for a given OAA SVM model i can be calculated as
= (19)

where and are given as

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] (20)

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] (21)

Using kernel trick [7], data points from and are implicitly mapped from [R.sup.d] to a high dimension feature space H. Let [empty set](x) : R [right arrow] H denote the mapping and , ={[empty set] ( ), [empty set] ) denote the kernel function, where is the set of kernel parameters and <.,.> is the inner product. K denotes the kernel matrix and { } , is defined as , . Let [K.sub.A, B] be kernel matrix computed with the data points from A and B which denote two subsets of training sample set D. Let [empty set] and [empty set] denotes the between class scatter matrix and within class scatter matrix in H, respectively and defined as follows

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] (22)

[MATHEMATICAL EXPRESSION NOT REPRODUCIBLE IN ASCII] (23)

Email task, Session and Workflow notification : Informatica

One of the advantages of using ETL Tools is that functionality such as monitoring, logging and notification are either built-in or very easy to incorporate into your ETL with minimal coding. This Post explains the Email task, which is part of Notification framework in Informatica. I have added some guidelines at the end on a few standard practices when using email tasks and the reasons behind them.
1. Workflow and session details.
2. Creating the Email Task (Re-usable)
3. Adding Email task to sessions
4. Adding Email Task at the Workflow Level
5. Emails in the Parameter file (Better maintenance, Good design).
6. Standard (Good) Practices
7. Common issues/Questions
1. Workflow and session details.
Here is the sample workflow that I am using. The workflow (wkf_Test) has 2 sessions.
s_m_T1 : Loads data from Source to Staging table (T1).
s_m_T2 : Loads data from Staging (T1) to Target (T2).
The actual mappings are almost irrevant for this example, but we need atleast two sessions to illustrate the different scenarios possible.

Test Workflow (2 Sessions)

2. Creating the Email Task (Re-usable)
Why re-usable?. Becuase we’d be using the same email task for all the sessions in this workflow.
1. Go to Workflow Manager and connect to the repository and the folder in which your workflow is present.
2. Go to the Workflow Designer Tab.
3. Click on Workflow > edit (from the Menu ) and create a workflow variable as below (to hold the failure email address).

Failure Email workflow variable

4. Go to the “Task Developer” Tab and click create from the menu.
5. Select “Email Task”, enter “Email_Wkf_Test_Failure” for the name (since this email task is for different sessions in wkf_test).
Click “Create” and then “Done”. Save changes (Repository -> Save or the good old ctrl+S).
6. Double click on the Email Task and enter the following details in the properties tab.

Email User Name : $$FailureEmail   (Replace the pre-populated session variable $PMFailureUser, 
                                    since we be setting this for each workflow as needed).
Email subject   : Informatica workflow ** WKF_TEST **  failure notification.
Email text      : (see below. Note that the server varibles might be disabled, but will be available during run time).
Please see the attched log for Details. Contact ETL_RUN_AND_SUPPORT@XYZ.COM for further information.
 
%g
Folder : %n
Workflow : wkf_test
Session : %s

Create_Email_Task

3. Adding Email task to sessions
7. Go to the Workflow Tab and double click on session s_m_T1. You should see the “edit task” window.
8. Make sure you have “Fail parent if this task fails” in the general tab and the “stop on errors” is 1 on the config tab.
Go to “Components” tab.
9. For the on-failure email section, select “reusable” for type and click the LOV on Value.
10. Select the email task that we just created (Email_Wkf_Test_Failure), and click OK.

Adding Email Task to a session

4. Adding Email Task at the Workflow Level
Workflow-level failure/suspension email.
If you are already implementing the failure email for each session (and getting the session log for the failed session), then you should consider just suspending the workflow. If you don’t need session level details, using the workflow suspension email makes sense.
There are two settings you need to set for Failure notification emails at workflow level.
a) Suspend on error (Check)
b) Suspension email (Select the email task as before). Again, remember that if you have both session and workflow level emails, you’ll get two emails, if a session fails and causes the parent to fail.

Informatica workflow suspension email

Workflow Sucesss email
In some cases, you might have a requirement to add a success email once the entire workflow is complete.
This helps people know the workflow status for the day without having to access workflow monitor or asking run teams for the status each day. This is particularly helpful for business teams who are more concerned whether the process completed for the day.
1) Go to the workflow tab in workflow manager and click Task > Create > Email Task.
2) Enter the name of the email task and click OK.
3) In the general tab, select “Fail parent if this task fails”. In the properties tab, add the necessary details
Note that the variables are not available anymore, since they are only applicable at the session level.
4) Add the necessary Session.status=”succeedeed” for all the preceding tasks.
Here’s how your final workflow will look.

Informatica success emails

5. Emails in the Parameter file (Better maintenance, Good design).
We’ve created the workflow variable $$FailureEmail and used it in the email task. But how and when is the value assigned?
You can manage the failure emails by assigning the value in the parameter file.
Here is my parameter file for this example. You can seperate multiple emails using comma.

infa@ DEV /> cat wkf_test.param
[rchamarthi.WF:wkf_Test]
$$FailureEmail=rajesh@etl-developer.com
 
[rchamarthi.WF:wkf_Test.ST:s_m_T1]
$DBConnection_Target=RC_ORCL102
 
[rchamarthi.WF:wkf_Test.ST:s_m_T2]
$DBConnection_Target=RC_ORCL102

While it might look like a simpler approach initially, hard-coding emails IDs in the email task is a bad idea. Here’s why.
Like every other development cycle, Informatica ETLs go thorugh Dev, QA and Prod and the failure email for each of the environment will be different. When you promote components from Dev to QA and then to Prod, everything from Mapping to Session to Workflow should be identical in all environments. Anything that changes or might change should be handled using parameter files (similar to env files in Unix). This also works the other way around. When you copy a workflow from Production to Development and try to make changes, the failure emails will not go to business users or QA teams as the development parameter file only has the developer email Ids.
If you use parameter files, here is how it would be set up in different environments once.
After the initial set up, you’ll hardly change it in QA and Prod and migrations will never screw this up.

In development   : $$FailureEmail=developer1@xyz.com,developer2@xyz.com"
In QA / Testing  : $$FailureEmail=r=developer1@xyz.com,developer2@xyz.com,QA_TEAM@xyz.com
In Production    : $$FailureEmail=IT_OPERATIONS@xyz.com,ETL_RUN@xyz.com,BI_USERS@xyz.com

6. Standard (Good) Practices
These are some of the standard practices related to Email Tasks that I would recommend. The reasons have been explained above.
a) Reusable email task that is used by all sessions in the workflow.
b) Suspend on error set at the workflow level and failure email specified for each session.
c) Fail parent if this task fails (might not be applicable in 100% of the cases).
c) Workflow Success email (based on requirement).
d) Emails mentioned only in the parameter file. (No Hard-coding).
7. Common issues/Questions
Warning unused variable $$FailureEmail and/or No failure emails:
Make sure you use the double dollar sign, as all user-defined variables should. (unless you are just using the integration service variable $PMFailureEmailUser). Once that is done, the reason for the above warning and/or no failure email could be…
a) You forgot to declare the workflow variable as described in step 3 above or
b) the workflow parameter file is not being read correctly. (wrong path, no read permissions, invalid parameter file entry etc.)
Once you fix these two, you should be able to see the success and failure emails as expected.

Information Technology – A Definition:

We use the term information technology or IT to refer to an entire industry. In actuality, information technology is the use of computers and software to manage information. In some companies, this is referred to as Management Information Services (or MIS) or simply as Information Services (or IS). The information technology department of a large company would be responsible for storing information, protecting information, processing the information, transmitting the information as necessary, and later retrieving information as necessary.
History of Information Technology:

In relative terms, it wasn't long ago that the Information Technology department might have consisted of a single Computer Operator, who might be storing data on magnetic tape, and then putting it in a box down in the basement somewhere. The history of information technology is fascinating! Check out these history of information technology resources for information on everything from the history of IT to electronics inventions and even the top 10 IT bugs.
Modern Information Technology Departments:

In order to perform the complex functions required of information technology departments today, the modern Information Technology Department would use computers, servers, database management systems, and cryptography. The department would be made up of several System Administrators, Database Administrators and at least one Information Technology Manager. The group usually reports to the Chief Information Officer (CIO).

PowerCenter - Domain

The Power Center domain is the primary logical unit for management and administration within PowerCenter.
The service manager runs on a PowerCenter domain. The Service Manager supports :

• the domain
• and the application services.

PowerCenter has a service-oriented architecture that provides the ability to scale services and share resources across multiple machines.
PowerCenter provides the PowerCenter domain to support the administration of the PowerCenter services.

where:

• gateway host and gateway port are the basis for the administration console url

A domain can contain multiple repositories:

Informatica PowerCenter Repository tables

I am sure every PowerCenter developer either has an intention or necessity to know about the Informatica metadata tables and where information is stored etc. For the starters, all the objects that we create in Informatica PowerCenter - let them be sources, targets, mappings, workflows, sessions, expressions, be it anything related to PowerCenter, will get stored in a set of database tables (call them as metadata tables or OPB tables or repository tables).

* I want to know all the sessions in my folder that are calling some shell script/command in the Post-Session command task.
* I want to know how many mappings have transformations that contain "STOCK_CODE" defined as a port.
* I want to know all unused ports in my repository of 100 folders.

In repositories where you have many number of sessions or workflows or mappings, it gets difficult to achieve this with the help of Informatica PowerCenter client tools. After all, whole of this data is stored in some form in the metadata tables. So if you know the data model of these repository tables, you will be in a better position to answer these questions.

Before we proceed further, let me clearly urge for something very important. Data in the repository/metadata/OPB tables is very sensitive and that the modifications like insert or updates are to be made using the PowerCenter tools ONLY. DO NOT DIRECTLY USE UPDATE OR INSERT COMMANDS AGAINST THESE TABLES.

Please also note that there is no official documentation from Informatica Corporation on how these tables act. It is purely based on my assumption, research and experience that I am providing these details. I will not be responsible to any of the damages caused if you use any statement other than the SELECT, knowing the details from this blog article. This is my disclaimer. Let us move on to the contents now.

There around a couple of hundred OPB tables in 7.x version of PowerCenter, but in 8.x, this number crosses 400. In this regard, I am going to talk about few important tables in this articles. As such, this is not a small topic to cover in one article. I shall write few more to cover other important tables like OPB_TDS, OPB_SESSLOG etc.

We shall start with OPB_SUBJECT now.

OPB_SUBJECT - PowerCenter folders table

This table stores the name of each PowerCenter repository folder.

Usage: Join any of the repository tables that have SUBJECT_ID as column with that of SUBJ_ID in this table to know the folder name.

OPB_MAPPING - Mappings table

This table stores the name and ID of each mapping and its corresponding folder.

Usage: Join any of the repository tables that have MAPPING_ID as column with that of MAPPING_ID in this table to know the mapping name.

OPB_TASK - Tasks table like sessions, workflow etc

This table stores the name and ID of each task like session, workflow and its corresponding folder.

Usage: Join any of the repository tables that have TASK_ID as column with that of TASK_ID/SESSION_ID in this table to know the task name. Observe that the session and also workflow are stored as tasks in the repository. TASK_TYPE for session is 68 and that of the workflow is 71.

OPB_SESSION - Session & Mapping linkage table

This table stores the linkage between the session and the corresponding mapping. As informed in the earlier paragraph, you can use the SESSION_ID in this table to join with TASK_ID of OPB_TASK table.

OPB_TASK_ATTR - Task attributes tables

This is the table that stores the attribute values (like Session log name etc) for tasks.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this table means. You can know more about OPB_ATTR table in the next paragraphs.

OPB_WIDGET - Transformations table

This table stores the names and IDs of all the transformations with their folder details.

Usage: Use WIDGET_ID from this table to that of the WIDGET_ID of any of the tables to know the transformation name and the folder details. Use this table in conjunction with OPB_WIDGET_ATTR or OPB_WIDGET_EXPR to know more about each transformation etc.

OPB_WIDGET_FIELD - Transformation ports table

This table stores the names and IDs of all the transformation fields for each of the transformations.

Usage: Take the FIELD_ID from this table and match it against the FIELD_ID of any of the tables like OPB_WIDGET_DEP and you can get the corresponding information.

OPB_WIDGET_ATTR - Transformation properties table

This table stores all the properties details about each of the transformations.

Usage: Use the ATTR_ID of this table to that of the ATTR_ID of OPB_ATTR table to find what each attribute in this transformation means.

OPB_EXPRESSION - Expressions table

This table stores the details of the expressions used anywhere in PowerCenter.

Usage: Use this table in conjunction with OPB_WIDGET/OPB_WIDGET_INST and OPB_WIDGET_EXPR to get the expressions in the Expression transformation for a particular, mapping or a set.

OPB_ATTR - Attributes

This table has a list of attributes and their default values if any. You can get the ATTR_ID from this table and look it up against any of the tables where you can get the attribute value. You should also make a note of the ATTR_TYPE, OBJECT_TYPE_ID before you pick up the ATTR_ID. You can find the same ATTR_ID in the table, but with different ATTR_TYPE or OBJECT_TYPE_ID.

OPB_COMPONENT - Session Component

This table stores the component details like Post-Session-Success-Email, commands in Post-Session/pre-Session etc.

Usage: Match the TASK_ID with that of the SESSION_ID in OPB_SESSION table to get the SESSION_NAME and to get the shell command or batch command that is there for the session, join this table with OPB_TASK_VAL_LIST table on TASK_ID.

OPB_CFG_ATTR - Session Configuration Attributes

This table stores the attribute values for Session Object configuration like "Save Session log by", Session log path etc.

Access, Integrate, and Deliver Data Quickly and Cost-Effectively with Enterprise Data Integration

Informatica PowerCenter sets the standard for highly scalable, high-performance enterprise data integration software. Informatica PowerCenter empowers your IT organization to implement a single approach to accessing, transforming, and delivering data without having to resort to hand coding. The software scales to support large data volumes and meets enterprise demands for security and performance. Informatica PowerCenter serves as the foundation for all data integration projects and enterprise integration initiatives, including data governance, data migration, and enterprise data warehousing.

    Provide the right information, at the right time so that the business has the timely, relevant, and trustworthy data it needs, when it needs it, to make better and timelier business decisions
    Cost-effectively scale to meet increased data demand, save hardware costs, and reduce the costs and risks associated with data downtime
    Empower teams of developers, analysts, and administrators to work faster and better together, sharing and reusing work, to accelerate project delivery

Informatica ETL products

Informatica PowerCenter is an enterprise data integration platform working as a unit. With its high availability as well as being fully scalable and high-performing, PowerCenter provides the foundation for all major data integration projects and initiatives throughout the enterprise.

    These areas include:
    B2B exchange
    data governance
    data migration
    data warehousing
    data replication and synchronization
    Integration Competency Centers (ICC)
    Master Data Management (MDM)
    Service-oriented architectures (SOA) and more.

PowerCenter provides reliable solutions to the IT management, global IT teams, developers and business analysts as it delivers not only data that can be trusted and guarantees to meet analytical and operational requirements of the business, but also offers support to various data integration projects and collaboration between the business and IT across the globe.

Informatica PowerCenter enables access to almost any data sources from one platform. It is possible thanks to the technologies of Informatica PowerExchange and PowerCenter Options.

PowerCenter is able to deliver data on demand of the business offering the choice of data access of real-time, batch or change data capture (CDC).

Informatica PowerCenter is capable of managing the broadest range of data integration initiatives as a single platform. This ETL tool makes it possible to simplify the development of data warehouses and data marts.

Supported by PowerCenter Options, Informatica PowerCenter software meets enterprise expectations and requirements for security, scalability and collaboration through such capabilities as:

    dynamic partitioning
    high availability/seamless recovery
    metadata management
    data masking
    grid computing support and many more


In order to increase operational efficiency and to manage and execute various initiatives, the platform enhances successful collaboration between the business and IT.

Informatica ETL products

PowerCenter has an offer of a wide range of features designed for global IT teams and production administrators, as well as for individual developers and professionals:

    - Metadata Manager (consolidates metadata into a unified integration catalog)
    - development capabilities (team-based; accelerate development, simplify administration)
    - a set of visual tools and productivity tools (manages administration and collaboration between different specialists)
    - metadata-driven architecture (eliminates the recoding requirement).

    The Informatica ETL (Informatica PowerCenter) product comprises three major applications:
    Informatica PowerCenter Client Tools. These tools have been designed to enable a developer to:
    - report metadata
    - manage repository
    - monitor sessions' execution
    - define mapping and run-time properties (sessions)
    Informatica PowerCenter Repository - it is the centre of Informatica tools where all data (eg. related to mapping or sources/targets) is stored. Here all metadata for application is kept. All the client tools as well as Informatica Server use the Repository to obtain data. The Repository can be compared to a harddisk or memory in a PC - without it it is possible to process the data but there is virtually no data that can be processed.
    Informatica PowerCenter Server - server is the place where all the actions are executed. It physically connects to sources and targets to fetch the data, apply all transformations and load the data into target systems.

Sequence Generator

1. Sequence Generator
   

 

The SG transformation generates numeric values. We can use the SG to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.

 

NEXTVAL:

 

    We can use the NEXTVAL port to generate sequence numbers by connecting it to downstream transformation or target.

 

CURRVAL:

 

    CURRVAL is the NEXTVAL plus Increment By value. We typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation. When a row enters a transformation connected to the CURRVAL port, the IS passes the last created NEXTVAL value plus one.

Lookup Transformation

1. Lookup Transformation
   

 

We can use lookup transformation to lookup data in a flat file, relational table , view or synonym.

 

Tasks:

 

à
Get a related value: We can retrieve a value from lookup table based on value in the source.

 

à
Perform a calculation: We can retrieve a value from lookup table and use it in calculation.

 

à
Update slowly changing dimension table: Using lookup we can check whether rows exist in a target or not.

 

 

 

Connected lookup

 

    à Receives input values directly from the pipeline.

 

    à Use a dynamic or static cache.

 

    à Can return multiple columns from the same row or insert into dynamic cache.

 

    à If there is no match, the IS returns default value for all output ports, for dynamic cache IS inserts row into cache.

 

    à If there is a match, the IS returns a result from the lookup condition for all the lookup/output ports.

 

    à Pass multiple output values to another transformation.

 

    à Supports user-defined default values.

 

Un-connected lookup

 

    à Receives input value from the result of :LKP expression in another transformation.

 

    à Use a static cache.

 

    à Returns one column from each row.

 

    à If there is no match, the IS returns NULL.

 

    à If there is match, the IS returns the result of lookup condition.

 

    à Pass one output value to another transformation.

 

    à Does not support user-defined default values.

 

We can perform the following tasks with un-connected lookup:

 

    à Test the result of a lookup in an expression.

 

    à Filter rows based on the lookup results.

 

    à Mark rows for update based on the result of a lookup and update SCD's.

 

    à Call the same lookup multiple times in a mapping.

 

 

 

 

 

Cached lookup:

 

    When we enable lookup caching, the IS queries the lookup source once, caches the values , and lookup values in the cache during the session. Caching lookup values can improve session performance.

 

 

Un-cached lookup:

 

    When we disable caching, each time a row passes into the transformation , the IS issues a select statement to the lookup source for lookup values.

 

 

Lookup policy on multiple match:

 

    Determines which rows the lookup transformation returns when it finds multiple rows that match the lookup condition. We can select the first row or last row returned from the cache or lookup source, or report an error. Or, we can allow the lookup to return any matching value the transformation returns the first value that matches the lookup condition.

 

If we do not enable the output old value on update option, the lookup policy on multiple match option is set to Report an error for dynamic lookups.

 

 

Lookup query:

 

    The IS queries the lookup based on the ports and properties we configure in the lookup transformation. The IS runs a default SQL statement when the first row enters the lookup transformation.

 

Default lookup query contains:

 

à
SELECT statement includes all the lookup ports in the mapping. Do not add or delete any ports columns from the default SELECT statement.

 

à ORDER BY clause orders the columns in the same order they appear in the lookup transformation. We cannot view this when we generate the default SQL using the lookup SQL override.

 

Overriding the LOOKUP Query:

 

    We can override the lookup query for a relational lookup.

 

à Override the ORDER BY clause: Create order by clause with fewer columns to increase performance. When we override the ORDER BY clause we must suppress the generated ORDER BY clause with a comment notation (--).

 

à If the table name or column name in the query contains any reserved words we must enclose them in the quotes.

 

à Add a WHERE clause: Use a lookup SQL override to add a WHERE clause to the default SQL statement. We can use the WHERE clause to reduce the number of rows included in the cache.

 

à Use a lookup SQL override to query the lookup data from multiple tables.

 

 

Notes:

 

è Lookup table can be a single table or we can join multiple tables in the same database using a lookup SQL override.

 

è The Designer designates each column in the lookup source as a lookup(L) and output(O) port.

 

è If we delete ports from flat file lookup, the session fails.

 

è We can delete ports from a relational lookup if the mapping does not use the lookup port. This reduces the amount of memory the IS needs to run the session.

 

è The IS always caches flat files and pipeline lookups.

 

è If we use pushdown optimization, we cannot override the ORDER BY clause or suppress the order by clause with the comment notation.

 

è The IS matches null values for lookup transformation. For example, if an input lookup condition column is NULL, the IS evaluates the NULL equal to NULL in the lookup.

 

è If we configure flat file lookup for sorted input, the IS fails the session if the condition columns are not grouped. If the condition columns are grouped, but not sorted, the IS processes the lookup as if we did not configure sorted input.

 

è Lookup condition contains the following operators: =,>,<,>=,<=,!=

 

è We can use a dynamic cache for relational or flat file lookups.

 

è The IS builds caches for un-connected lookup sequentially regardless of how we configure cache building.

 

 

 

 

 

 

 

 

 

Lookup caches:

 

    The IS builds a cache in memory when it processes the first row of data in a cached lookup transformation. The IS stores condition values in the index cache and output values in the data cache. The IS queries the cache for each row that enters the transformation.

 

Building caches:

 

    We can configure the session to build caches sequentially or concurrently. When we build sequential caches, the IS creates cache as the source rows enter the lookup.

When we configure the session to build concurrent caches, the IS does not wait for the first row to enter the lookup before it creates cache. Instead, it builds multiple caches concurrently.

 

Persistent cache:

 

    We can save the lookup cache files and reuse them the next time the IS processes a lookup. If the lookup table does not change between sessions we can the lookup to use persistent cache. The first time the IS runs a session using a persistent lookup cache, it saves the cache files to disk instead of deleting them. Then next time we run the session it builds the cache memory from cache files. If the lookup table changes occasionally, we can override the lookup property to Recache the lookup from the database.

 

 

Recache from source:    

 

    If the persistent cache is not synchronized with the lookup table, we can configure the lookup transformation to rebuild the lookup cache.

We can instruct the IS to rebuild the cache if we think that the lookup source changed since the last time the IS built the persistent cache.

 

 

Static cache:

 

    The IS does not update the cache while it processes the lookup transformation.

 

Dynamic cache:

 

    The IS dynamically inserts or updates data in the lookup cache and passes data to the target.

 

 

 

 

 

 

 

 

 

Properties:

 

NewLookupRow:

 

    The designer adds this port to a lookup configured to use a dynamic cache. Indicates with a numeric value whether the IS inserts or updates the row in the cache, or makes no changes to the cache.

 

0 – IS does not update or insert the row in the cache.

 

1 – IS inserts the row into the cache.

 

2 – IS updates the row in the cache.

 

Associated port:

 

    The IS uses the data in the associated port to insert or update rows in the cache. If we associate a sequence ID, the IS generates a primary key for inserted rows in the lookup cache.

 

Ignore Null inputs for updates:

 

    We can enable this property when we don't want the IS to update the column in the cache when the data in this column contains a null value.

 

Ignore in Comparison:

 

    The IS compares the values in all lookup ports with the values in their associated input ports by default. We can use this property when we want the IS to ignore the port when it compares values before updating a row.

 

    When we add a WHERE clause in a lookup SQL override, the IS uses the WHERE clause to build the cache from the database and to perform a lookup on the database table for an un-cached lookup. It does not use the WHERE clause to insert rows into a dynamic cache when it runs a session.

 

 

Shared cache:

 

    We can share the cache between multiple transformations. We can share the unnamed cache between transformations in the same mapping. We can share the named cache between transformations in same or different mappings.

Router Transformation

1. Router Transformation
   

 

Router transformation tests same input data based on multiple conditions and gives the option to route rows of data that do not meet any of the conditions to a default output group.

Working with Groups

 

Router has the following types of groups.

 

• Input Group : The designer copies property information from the input ports of the input group to create a set of output ports for each output group.
  

 

• 
  Output Group : There are two types of output groups.
  

    è User-defined Groups

    è Default Group

 

We cannot delete or modify output ports or properties.

 

User defined Group

 

The Designer creates the default group after we create one new user-defined group. The designer does not allow to edit or delete the default group. This group does not have a group filter condition associated with it. If all the conditions evaluate to FALSE, the IS passes the row to the default group.

 

• If you want to drop all rows in the default group, do not connect it to transformation or target.
  
• The Designer deletes the default group when we delete the last user-defined group from the list.
  

 

• We can enter default values for input ports in Router to replace NULL input values.
  
• If a row meets more than one group filter condition, the IS passes this row multiple times.
  

 

• We can connect one group to one transformation or one target.
  
• We can connect one output port in a group to multiple transformations or targets.
  
• We can connect multiple output ports in one group to multiple transformations or targets.
  
• We cannot connect more than one group to one transformation or target.
  

1. Router Transformation
   

 

Router transformation tests same input data based on multiple conditions and gives the option to route rows of data that do not meet any of the conditions to a default output group.

Working with Groups

 

Router has the following types of groups.

 

• Input Group : The designer copies property information from the input ports of the input group to create a set of output ports for each output group.
  

 

• 
  Output Group : There are two types of output groups.
  

    è User-defined Groups

    è Default Group

 

We cannot delete or modify output ports or properties.

 

User defined Group

 

The Designer creates the default group after we create one new user-defined group. The designer does not allow to edit or delete the default group. This group does not have a group filter condition associated with it. If all the conditions evaluate to FALSE, the IS passes the row to the default group.

 

• If you want to drop all rows in the default group, do not connect it to transformation or target.
  
• The Designer deletes the default group when we delete the last user-defined group from the list.
  

 

• We can enter default values for input ports in Router to replace NULL input values.
  
• If a row meets more than one group filter condition, the IS passes this row multiple times.
  

 

• We can connect one group to one transformation or one target.
  
• We can connect one output port in a group to multiple transformations or targets.
  
• We can connect multiple output ports in one group to multiple transformations or targets.
  
• We cannot connect more than one group to one transformation or target.