Data Integration Platform Accelerator - Talend ETL Tool

Business Challenge

Data integration and collaboration between enterprise applications is a key factor for any organization. The most important aspect is to ease the seamless integration of data and proper migration of different data sources and effective collaboration of multiple Enterprise Applications. The core problem is to address the integration needs and challenges of Enterprise Applications which are built on PostGres database.

The following are the key problems that need to be addressed:

1. Effective integration between multiple Enterprise Applications
2. Collaborative seamless data integration between various Enterprise Applications
3. Provide common source for various BI reports
4. Proper data migration of different kinds of data sources
5. Replication across multiple systems

Solution Approach

To address the problem the proposed solution approaches are:

1. Trigger Based Approach - Talend CDC
2. Using xmin – Postgres CDC
3. Trigger & xmin - Slony-I replication
4. Using WAL & Trigger - Streaming Replication : PostGres 9.0 onwards

1. Trigger Based Approach - Talend CDC:

The approach using Trigger base – Talend CDE:

• Creates change tables for each source table which needs to be watched
• Triggers store the primary key of the changed record
• Triggers store metadata of the transaction in the change tables
• PK (Primary key of the transaction)
• Change Type (Update / Insert / Delete)
• Changed By
• Changed Time
• Talend CDC component extracts the changes from the change tables
• Follows a publish / subscribe based model
• Provides a view to extract from the source table based on the PK
• Left-join with the source table
• Provides the latest updates
• Maintains its own control tables

2. Using xmin – PostGres CDE:

The approach using xmin – PostGres CDE:

• Maintains configuration on which tables need to be watched
• Extracts the xmin for the tables to watch and stores in control tables
• Creates a generic view to extract the changes that happened after the last extract based on xmin

Advantages:

• Non-Intrusive approach over the source schema

Disadvantages:

• Indexes are not available over xmin
• Wrap-around problem
• Cannot track deletes

Metrics:

• On a simulated environment time to extract on table with more than 500K rows on a 4GB RAM 2 Core VM takes around 4-5 seconds
• This would grow as the number of rows increases

3. Trigger & xmin – Slony I  Replication:

The approach using Trigger &xmin – Sloney I Replication:

• Master / Slave replication
• Based on triggers on Master or Origin node
• Publisher / Subscribe model
• Stores transactions in sl_log_1 / sl_log_2 tables
• Stores TableId
• Stores TransactionId
• Stores Audit info
• Creates Sync Events for subscribers
• Stores sync events in sl_event table
• Slony daemons on subscribers pull the events
• Get the transactionid for that event from the log tables
• Extract and load the data in the slave schema
• Have Talend CDC point to the slave and extract the changes based on the trigger based approach

Advantages:

• Although xmin is used internally to detect and  replicate the changes, the wraparound issues are handled by slony
• Replication Set allows to replicate only certain tables not the whole schema

Disadvantages:

• Pulling changes/modified data over wire may cause delay
• Changes to the source schema

Metrics:

• Accounting the existing stats by setting the SLON_DATA_FETECH_SIZE is between 100 to 400 the slony replication has good response times
• As the triggers would be on the slave it would be faster for talend CDC extraction as they are based on PK’s

4. Using WAL & Trigger - Streaming Replication : PostGres 9.0 onwards

Limitations with this approach:

• WAL-based replication requires that all databases use identical versions , running on identical architectures
• WAL-based replication duplicates the model
• Do not have the ability to have specific updates on Target Schema
• Synchronous Replication based on 2-Phase commits

Proposed Solution (Talend Tool)

Based on all the above approaches, Trigger based approach seems to be the best option with using Talend CDC. The main advantage with this approach is the efficient retrieval you receive from source based on indexed columns.

Overview
Talend is an Open Source Integration Software Company that provides open source middleware solutions that enable organizations to gain more value from their applications, systems and databases. Shattering the traditional proprietary model, Talend democratizes the integration market by providing enterprise-grade open source technologies that cover both the data integration and application integration needs of organizations of all sizes.

Talend's unified integration platform addresses projects such as data integration, ETL, data quality, master data management and application integration. With their proven performance, user friendly, extensibility and robustness, Talend's solutions are the most widely used and deployed integration solutions around the world. Talend is a tool for people who are already making a Java program and want to save an abundant amount of time with a tool that generates code for them. The simple DW Architecture is below.

Talend Features:

1. Collaborative Data Integration - Talend’s data integration products provide powerful andflexible integration, so that firms can stop worrying about how databases and applications are talking to each other. Thus providing them with the ability to maximize the value of using their data.
2. Transform and Integrate Data between Systems - Talend’s data integration products provide an extensible, highly-performant, open source set of tools to access, transform and integrate data from any business system in real time or batch to meet both operational and analytical data integration needs. With over 450 connectors, it has the ability to integrate almost any data source. The broad range of use cases addressed include: massive scale integration (big data/ NoSQL), ETL for business intelligence and data warehousing, data synchronization, data migration, data sharing, and data services.
3. A Comprehensive Solution - Talend provides a Business Modeler, a visual tool for designing business logic for an application; a Job Designer, a visual tool for functional diagramming, delineating data development and flow sequencing using components and connectors; and a Metadata Manager, for storing and managing all project metadata, including contextual data such as database connection details and file paths.
4. Broad Connectivity to All Systems - Talend connects natively to databases, packaged applications (ERP, CRM, etc.), SaaS and Cloud applications, mainframes, files, Web services, data warehouses, data marts, and OLAP applications. It offers built-in advanced components for ETL including string manipulators, Slowly Changing Dimensions, automatic lookup handling and bulk loading. Direct integration is provided with data quality, data matching, MDM and related functions. Talend connects to popular cloud apps including Salesforce.com and SugarCRM.
5. Teamwork and Collaboration - The shared repository consolidates all project information and enterprise metadata in a centralized repository shared by all stakeholders: business users, job developers, and IT operations staff. Developers can easily version jobs with the ability to roll-back to a prior version.
6. Advanced Management and Monitoring - Talend includes powerful testing, debugging, management and tuning features with real-time tracking of data execution statistics and an advanced trace mode. The product incorporates tools for managing the simplest jobs to the most complex ones, from single jobs to thousands of jobs. Processes can be deployed across enterprise and grid systems as data services using the export tool.
7. It uses a code-generating approach. Uses a GUI, but within Eclipse RC.
8. It generates java or Perl code which can run on any server.
9. It can schedule tasks (also with using schedulers’ like cron).
10. It has data quality features: from its own GUI, writing more customized SQL queries and Java.

Figure 2: Typical enterprise data integration model

Advantages of Using Talend Tool

• Talend is used in an application to retrieve and transform the data across multiple systems at enterprise level. 
• With Talend Centralize data integration, enrichment and distribution is made easy
• Capable of generating multiple BI Reports using unified transactional data model
• Ability to configure the Talend Jobs to pick data from a certain period of time.
• All such data flows are registered with an approval process for new ones, with special emphasis on extra-company flows
• Metadata-driven architecture to ensure that critical data elements and transformations are documented, controlled (e.g., sensitive data) and kept in sync with implementation
• Talend Slony replication is used to replicate data across multiple enterprise systems
• Talend CDC is used to create Triggers and views on the source database to pull the changed data and insert / update the Target Database.

Value Addition using Talend Open Source Tool

• Talend Open Source Tool enabled development for centralized location of data for various enterprise applications
• With Talend Open Source we could achieve easy transactional data transformations across systems
• Simplified tool to develop transactional data jobs
• Insulates clients from changes in transactional systems
• Reduces modelling and data quality effort downstream, e.g., at DW/BI for conforming dimensions, de-duping and resolving inconsistent reports
• Provides good turn around on data retrievals
• Provides source data for operational reporting
• Talend CDC enabled Rapid Application development 
• Slony enabled smooth replication of data

Enterprise Entitlement Engine and Framework

Overview
A key goal of business technology systems is to ensure that the right people have access to the right information at the right time. Entitlements Engine is a fine grained authorization engine that externalizes, unifies, and simplifies the management of complex entitlement policies—strengthening security and compliance, improving IT efficiency, and enhancing business agility. These authorizations may be used to protect the most fine grained business or IT concept. Many organizations look into this as high prioritized need and to be managed by Centralized Application/Tools for proper Authentication and Authorizations.

Essentially, systems accomplish these requirements by enforcing a set of policies that regulate the behavior of system components and resources to match the “access” profile of the user accessing the system. At the most abstracted level, the system forces a user to specify and verify who they are (authentication) and then limits resources that can be accessed or manipulated by the user (authorization). Policies and rules govern each of the two facets. These two components of access management impose different types of challenges and requirements.

Authentication – establishing and validating identity. In most cases user ids and passwords presented at login screens/forms suffice.
Authorization – what information is a user permitted to access and manipulate – can impose very complex requirements.

The Entitlement Engine will be a critical enterprise component that addresses the requirements for fine-grained, context-sensitive authorization requirements. Authorization needs are not hard coded into applications, but rather specified as “configuration” in a UI provided by the Entitlement Framework. It is intended to be “application context aware”, thus providing a means to express very fine-grained authorization requirements to the system. It integrates as a service layer with the application, providing loose-coupling. In addition, it can be integrated within an application’s presentation and validation framework to eliminate screen-at-a-time integration effort – making the execution seamless to developers.

Concepts
Authentication: Before a user or another system can access any resource managed by the system, the requesting entity must establish and “authenticate” their identity.  At a high (simple) level, this process is implemented using one of several authenticating forms (certificates, logins, biometrics, etc.), depending on the context of the request and the requirements of the established policy. Most commonly, a user provides credentials (user-id, password) at “login”. If the provided credentials meet the security requirements, system can proceed with the identity of the validated user. The authentication process is supported by a multitude of rules and policies (e.g., password rules, expiration policies, failed attempts, etc.) that guard against users (and other systems) trying to gain unauthorized access.

In Current Entitlement Engine Authentication is not addressed as this is maintained by other Third party tool.

Authorization:  In an organization or business unit there are people in different roles who are required to perform specific tasks – but not authorized to perform other tasks. Enterprise applications and resources facilitate these individuals in performing their job functions efficiently and effectively. As these applications and resources will be accessed by people with different levels of authorization, applications require the capability to provide the necessary restrictions based on the role the user has.

Role Based Access Control (RBAC):   
The first part of the authorization approach relates to restricting system access to authorized users based on the role they have. Thus, authorization is expressed as permission sets based on roles.
RBAC is considered “Coarse-grained” authorization and is used to define broader-level functionality (features or resources) a role can access. Users are assigned one or more roles. When that user logs in to a particular application, the application can determine what resources (menu items, screens, etc) that user can access, based exclusively on their role.

Limitations:
At this level, we cannot define the “context” in which the feature is being accessed and, therefore, not specify a specific permission set for a “context”. For example, the authorization policy – that a Credit Reviewer cannot approve a Commitment – can be expressed and executed just based on a user’s role.
We also do not have a way of creating a profile based on the user skill set and assign permissions that are based on attributes other than role.

Attribute Based Access Control:  
The second part of the authorization approach augments RBAC capabilities to allow policies based on attributes of a user (e.g., skill, age, etc) and/or the environment (e.g., time, network, etc). While this notion can be extended to additional attributes (like application or business object), there is no structured (or simple) mechanism in off-the-shelf products to facilitate accessing these attributes or to define policies applicable to these attributes. Further, these tools do not support out-of-the-box facilities to help integrate with applications to execute policies in a runtime environment. Managing fine-grained control in a flexible manner while lowering the cost of delivery and maintenance requires the framework to be “context aware”. It must support exposing application and object attributes (e.g., screens, forms, button in an application or loan amount, LTV, etc.) at definition time so that policies for these (and other) attributes can be defined for profiles and roles. In addition, the framework needs to support convenient integration with the application framework.  Moreover, the architecture of the framework must allow efficient execution of privileges at runtime so that the system will scale with high volumes.

3. Objectives
The objectives of the proposed Entitlement Engine are:

1. Fine grained authorization which gives the flexibility in defining the permissions based on a context for a specific role or profile.
2. Policies can be defined with relevance to context. 
3. To provide centralized way of defining and evaluating policies based on the application, object, roles, profiles and resources. Changes are achieved through “configuration”.
4. To integrate with the application framework and perform efficiently, since policies are defined as values, ranges and lists for attributes and not in a computer language.

4. Solution Approach
The approach proposed here it to develop and deploy an Enterprise Entitlement Engine and Framework that specifically meets the sophisticated needs of the organization. Below is the level of access or security control that Entitlement Engine is proposed to have.

Traditional IAM tools like (Oracle Enterprise Manager, IBM TIM and TAM, WSO2 Identity server) are good in providing a centralized way of access management at the enterprise resource level (show a form or not show a form) but not for managing “sub-resource” level details and privileges. They lack a centralized way of access management at the Object, Record and Field level, particularly in the context of an application that manipulates these objects. 

The proposed Entitlement Engine provides some critical features to meet the necessary requirements.

1. A business user interface to define profiles and associate access to the lowest level (field or data in a field) per application. 
2. Entitlement engine provides the permissions for a specific role or profile based on the object hierarchy for the application once the user logs in. 
3. As the permissions need to be evaluated for all the users who login the engine would be performance sensitive and would need to bring all the permissions and access on the whole object hierarchy.  As we are not going to evaluate the permissions on each and every field, the approach is to cache the context specific retrieved entitlements so that there would be zero latency in the requests made by the application.
4. The Entitlement Engine also provides a way for certain role to delegate some of his permissions to the roles under him. This gives the flexibility for the manager to maintain business continuity even in his absence. All the activities done would be audited and tracked for future reference.
5. Entitlement engine provides services, which can be accessed by applications, which are built in any language and get the permissions. The engine provides RESTful web-services which could be integrated for better performance.
6. Entitlement engine provides various reports on the profiles created, usage of applications, delegations made by profiles, changes in object permissions etc. This helps the management as well as the compliance team to make sure the access to the applications and features within the applications is based on the standards defined.
7. Entitlement engine is built upon a framework which could be extended as needed as well can integrate with any existing systems as needed. 
8. Entitlement engine has inbuilt caching mechanism’s implemented which bring down the response times for requests from applications. The architecture is built-in to consider high availability and fail-over by proper load balancing at various layers.

5. Entitlement Engine Features

Entitlements System has the following features:

System Management

In our entitlements system we treat every enterprise resource as a system. We can define different kinds of systems like Web Application, Web Service, Database, FTP server, any network device etc., by using our pre-configured metadata about different system types.

Below are the high level features 

• Defining  System
• Associating attributes defined in the System type to System
• Associating Object Hierarchy to a system
• Defining  allowed access types to the objects in the object hierarchy

Object Hierarchy Management

This feature allows defining and associating an object hierarchy to a system. Every system has a specific object hierarchy. We have pre-configured object types which can be associated to these objects or we can extend the metadata as needed. At the object level we can define what different kind of access can be allowed on this object.

Configuration Management

Every feature (System, Profile, Access and Object) in the entitlement system is based on a type. The configuration management helps in defining the metadata for the features provided by the entitlement system. 

Below are some of the configurations

• System Type
• Object Type
• Access Type
• Identity Provider

Profile Management

Profile defines what level of access a user has with a particular system. Profiles are created for specific systems and then associated with user(s), role.

High level features 

• Profile Creation - A particular profile can be created for the System Object hierarchy. 
• Profile  Delegation - This feature allows a manager to delegate some or all of his objects from a profile to a sub-ordinate. The manager can also specify the duration and can change the level of access for that delegated profile.
• Profile Configuration - Profile can be configured against associated system object hierarchy. Extended data constraints can be configured for objects in object hierarchy.

Integration with Third -Party  Identity Providers

The entitlements system is powered to integrate with any third party Identity provider. The system has also the capability to define identities internally or map/sync the identities from different third party vendors. The internal structure mapped based on Users,

•  Users - We have regular sync job which update the users in our entitlements system with the third party provider.

User Profile Management

Entitlement Engine facilitates mapping profiles to users.

Entitlement  Services

These are the different services provider by our entitlements engine to the enterprise applications (for getting the entitlements for the logged in user). We have pre-defined services which can be accessed by REST Protocol.

Audit Control

Every action done on the entitlements system is tracked. We have a view and edit history on every feature (System, Profile, Object Hierarchy etc.) of the system. 

Reports

There are several reports provided by our entitlement system which gives a high level as well as detailed information to the business users.

Below are some of the canned reports

• Users based on selected  profiles
• Delegated profiles
• Profiles based on system
• Profiles mapped to users, organization units and cost center
• System level profile usages by user

Back-End jobs

There are back-end scheduled job to sync-up the identity info from the configured identity provider.

Note: The below are handled by a third party tools.

1. Identity Management: The third party tool will be taking care creation, updating and managing the User identities.
2. Authentication: The third party tool will be taking care of the authenticating the user based on the identity provider and provide a federated access or SSO implementation for the enterprise applications.

6. Technologies 

Operating System	Windows XP/Windows 7(Development), RedHat Enterprise Linux 5(Production)
IDE/Language	Eclipse / Java
Secondary Cache/Persistence Frameworks	Memcache
Frameworks	PrimeFaces 3.5, Spring 3.0
UI/Ajax Frameworks/HTML/CSS	JQuery
Database	PostgreSQL
Browser Compatibility Test	IE 6.0+, FireFox, Chrome, Safari
Web Server/Application Server	JBoss 7.1.1

7. Advantages on Building on Open Standards

Our solution is built on open standards so that it’s easy to integrate with any IAM tool and heterogeneous applications based on different environments. Below are some of the details.

1. We have built-in adapters which could be used by the enterprise applications which do understand the authentication tokens which are SAML based.
2. We have built-in connectors which help the applications to communicate over SOAP, REST or Thrift. This gives the applications the flexibility to choose the protocol they need.
3. We have built in service providers for giving out the defined application level permissions in the form of XML, JSON or Compact mode.
4. We have built-in adapters when configured can talk to any XACML based PDP. This adapter does understand the XACML response and can provide the response back to the application based on their needs (SOAP/JSON/Compact etc.).
5. Our solution is built on open source frameworks (Spring, Primefaces) and deployed over JBOSS as the application server. 

To learn more about Prolifics, visit www.prolifics.com.