Self-service ETL Data Integration

Adeptia’s offers a unique “self-service ETL” capability to empower business users and data scientists to themselves create simple business data integration connections with speed and precision. Of course, developers can use it too to create rich data integration flows that include data validations, cleansing, routing, exception-handling and back-end connectivity. However, the need for users to rely on IT for executing integrations reduces to a minimum.

Adeptia’s comprehensive ETL solution provides a powerful business data transformation capability. This graphical, wizard-driven, easy-to-use software supports all any-to-any data conversion to streamline business data integration.

• Flat file to XML
• XML to CSV
• Any Database to XML
• Excel to Database
• XML to EDI
• HIPAA to XML
• HL7 to XML
• Date format, string, numerical conversions etc.
• And any other combination

Adeptia Connect is an enterprise-class data integration software that is centrally administered and managed to ensure smooth performance and uptime. Detailed logs are maintained and audit trails are available to ensure compliance with regulatory requirements like Sarbanes-Oxley. This business data integration solution is standards-based and it leverages Service-Oriented Architecture (SOA) and process-based approach to create flexible and loosely coupled data flows that suit any aggregation or data warehousing solutions. It is ideally suited for an Extract, Transform and Load (ETL) scenario.

Adeptia offers a powerful middleware application to document, automate and execute critical data processes especially for creating and populating data warehouses from various internal or external data sources. It includes a design studio and combines data transport with powerful metadata management. This allows data to be received from multiple sources in different formats and mapped to a common database schema. This enterprise-class data integration software allows companies to centrally manage their data schema and mapping rules in a central repository and directly archive those rules in PDF documents for easy sign-off.

Adeptia offers all the features and functionality that one would need in a complete process-centric, SOA-based application integration solution. This includes the following capabilities:

Adeptia Connect enables self-service data integration via its Data Interfaces feature that allows business users and data scientists to create simple app-to-app and app-to-database connections using pre-configured connectors. This is a configuration wizard that allows users to select the applications and databases they want to connect and then specify the parameters via a web form. This allows true self-service integration for business users for simpler connections, thus eliminating the need for waiting for IT.

Adeptia’s solution delivers comprehensive message transport capability facilitating assured, secure and timely delivery of business information to diverse set of applications, systems and databases. The data transport module facilitates end-to-end data delivery management through easy configuration and use of standards based data protocols. This allows synchronization, aggregation, distribution, replication and validation of data within organizations and across firewalls. Following capabilities are included:

• Data Protocols: File and LAN access, HTTP, HTTPS, FTP, SFTP, FTPS, SMTP, POP3, IMAP
• Databases: All relational databases are supported. SQL queries are auto-generated. Complex views, joins, stored procedures and database triggers.
• Web Services and APIs: Consume RESTful and SOAP web services and APIs. Publish data flows as web services.
• Message Queue: Connect to any message queue using the JMS protocol.
• Applications: Connectors available for dozens of apps including SAP, PeopleSoft, SharePoint, JD Edwards, Salesforce, NetSuite, Quickbooks etc.

Data translation and mapping is critical for automating data integration flows. Data is often received in different formats and needs to be converted into other formats for target applications or for users to review. Complex mapping rules need to be defined as these formats are converted. This usually requires tedious and extensive coding while the tools available to simplify this process are expensive and only offer limited capabilities. Adeptia provides functionality to do all this quite easily and without any need for coding. This data transformation capability is strength of Adeptia and differentiates it from other integration products in the market.

Schema is a structure of a file format and it specifies information on the different data fields and records that are in a message or data file. Designing a schema is the process of providing metadata information. Adeptia’s solution includes a Schema Designer and it is a wizard-driven, web-based module that allows business analysts and IT users to design and manage data formats. Adeptia Schema Designer allows developers to specify metadata for all commonly used file formats such as Flat Files, Fixed-length positional files, ANSI X12 EDI, EDIFACT, XML, Microsoft Excel, HIPAA HL7, ACORD AL3, SAP IDOC as well as relational databases like Oracle, SQL Suite, IBM DB2 and MySQL etc. Complex metadata information can be provided including data field element names, attributes, field types as well as hierarchical record structure. Schemas are stored in an online repository of services and they are designed once and can be reused again for other trading partners and flows.

Schema Parser allows data messages and files of different formats to be automatically processed based on the schema definitions. This is a run-time service that can be orchestrated by a user as a task in a process flow. It uses schema (metadata) rules as defined in the Schema Designer to identify the format and structure of a message file and so parse that file into records and data elements. This parsed data can then be sent to the mapping engine to be transformed into another format.

Adeptia’s Data Mapper module is unique. In an industry first, this was the first completely web-based data-mapping designer in the market. It is a graphical tool that enables visual and rapid specification of data translation and complex business rules in easy to understand English rather than code. This allows business analysts and IT developers to easily create and manage data maps. It auto-generates data transformation code in a fraction of time compared to custom manual coding, allowing mapping projects to be completed in hours rather than days and weeks. The Data Mapper eliminates the need for the intricate, time consuming and error-prone process of manually writing thousands of lines of data mapping code. Collaboration and mapping rules management is simplified by auto-generation of transformation rules in PDF format for easy documentation, business user sign-offs and archival.

Data Mapper simplifies management of mapping rules:

• Mapping rules are defined using a visual, drag-and-drop interface.
• They are specified in easy to understand English.
• Truly Any-to-Any data translation from wide variety of data formats.
• Documented in PDF directly from the code.
• Complex validation rules and mappings functions can be created once by savvy developers and then made available in a library of functions for analysts to reuse later.
• Visually designed mapping rules are directly converted into program executable code.
• Mappings are easily tested during the design phase by applying them on actual source data.
• Data maps are stored in an online services repository and they can be reused again and again for different trading partners and process flows.

Data Mapping rules are easy to specify in Adeptia. For any selected target data field, a text area is provided where a user can build complex transformation rules for that field. For ease of use, the Data Mapper consists of built-in libraries of Math, String, Aggregate, Conditional and Boolean functions for data manipulation. Database lookups and XML file lookups are supported. Text area is also provided for users to specify comments where they can note the versioning information, history and any other context for the mapping rules for that field. This simplifies edits and future manageability of the mapping rules.

Data Mapping Engine is the heart of the data transformation module of the Adeptia Connect. It takes the mapping rules as defined in the Data Mapper and applies those rules to the parsed data to create the transformed output. Adeptia has designed the Mapping Engine to be functionally powerful so it can apply complex rules to complex data structures.

Adeptia’s solution has a Process Designer that offers a graphical process design interface for creating multi-step data flows that represent how the incoming data files or streams should be processed and how the data should be routed. IT staff utilize this simple to use, drag and drop interface for quickly designing process and data flows and to implement business rules. Developers directly implement these processes for execution by creating and populating each service task in the process flow.

Process Engine is a powerful, full-featured orchestration engine for automated process execution of the data integration flows. Its support for decision nodes, sub processes, exception handling, forks and joins, loops and long running transactions allows execution of process flows of almost any complexity. It supports the concept of transactional execution using Java Transaction API (JTA). It directly executes the PF XML that is generated by the Process Designer. It also supports failure recovery as well as a job queue and clustering for reliability and high performance.

Adeptia supports processing of both transactional single-record messages as well as bulk data in the form of large multiple-record data files. The Schema Parser and Data Mapping engine have highly scalable performance so the Adeptia Connect can scale up to handling hundreds of megabytes and gigabytes of data files that may contain hundreds of thousands or millions of records. Adeptia Connect does this processing in an efficient manner allowing it to offer speed and throughput that is only matched by dedicated hardware appliance-type of solutions.

Real-time and batch triggers are available built into the solution. Adeptia provides an important data integration capability by including a highly capable job scheduler with the product. This allows data flows to be triggered based on a pre-defined schedule. It also allows for polling events to fire automatically to check for new files or new data and then trigger the data flow to process that new data. These batch and real-time triggers allow flexibility in determining how and when the implemented integration solutions will be executed.

Adeptia Connect allows deploying of data flows that include tasks where human decisions are needed. When an action is required business users are alerted via email and on the Task Manager. Users interact via a web-interface with the process flow to complete their tasks. These actions may require manual intervention to resolve exceptions, review of business data to make decisions, and download/upload of documents.

Adeptia Connect provides easy manageability through single web-based console. Adeptia Connect dashboard makes it easy to monitor and track how the data flows are executing. Adeptia Connect saves all logs and process data in a database for archival. Pre-built reports are included that allow analysis of run-time process metrics to review performance of processes and identify bottlenecks.

Adeptia Connect with its application integration capabilities enables IT departments to be more productive and to be more responsive to the needs of the business. Some of the main benefits of the Adeptia solution are:

• Make business users more productive by allowing self-service integration
• Make IT staff more productive by having them focus on high value tasks
• Make business more agile and IT more responsive
• Create reusable, easy to modify ETL processes to attain higher data quality.
• Rapidly generate data warehouses, data marts and data streams. Easily add and manage data sources.
• Simpler, better ETL through one single platform.
• Auto document and formalize data flows and mapping rules
• Enforce data validation rules by specifying them in processes
• Ensure data flows are constant and repetitive, not ad-hoc
• Trigger data flows based on real-time or scheduler events
• Specify error-handling rules and manage exceptions
• Leverage technology investments by extending functionality of existing systems
• Enhance customer and partner relationships by making it easier to do business with your organization
• Single installer and package — No need to maintain multiple components including hardware, web servers, databases, integration brokers, etc.

Adeptia Connect is flexible data integration software that is an ideal platform to create many types of data connectivity solutions and address different types of data integration needs. Some of the common uses of the product as ETL software are listed below.

Synchronize data between your enterprise applications

• Use polling triggers or real-time events to synchronize data between different systems
• Ensure data consistency and accuracy across different applications
• Eliminate data silos where business data is trapped
• Improve data access to help business users get right data at the right time

Data Profiling

Assess the quality of your data before commencing data migrations.

Intelligent Data Cleansing

• Duplicate Removal (de-duplication): Combine data from various different sources and remove duplicate values
• Fuzzy Lookup: Configure sophisticated rules to highlight errors and fix them

Data Enrichment

While processing data, perform lookups with internal applications, databases and third-party providers to enrich your data. Such as verifying customer ids, zip codes etc.

Extract, Transform and Load (ETL) for Business Intelligence (BI)

Data analysis for business intelligence requires information to be aggregated from multiple sources into a data warehouse. Adeptia is used by many customers in an ETL scenario to collect data in different formats, using different protocols and then normalize the data into a target database for later analytics.

Data Conversion

Adeptia’s solution is ideal for converting XML, Text or Excel files to a database. This solution is also perfectly suited for database-to-database conversions.

Data Migration

Starting with data integration as the foundation, Adeptia enables companies to access, cleanse and migrate data from legacy systems to their SaaS applications, internal systems or databases in real-time.

• Data migration from legacy to new applications
• Data extraction from ERP/CRM to reporting and BI systems

Your data integration architecture should be able to support both simple (ETL) and complex (ESB) integration requirements. Features such as connecting to disparate applications, handling structured or un-structured data, implementing a service bus to route data from one system to another, populating datamarts and synchronizing data with cloud applications are some of the important parts of a data integration strategy. Therefore the framework needed to address these requirements should include features that can be used to implement these or any additional integration scenarios.

Adeptia Connect is the most versatile and comprehensive integration software platform on the market. By using Adeptia’s solution, you will be able to reduce customer onboarding time and exception handling costs, be easier to do business with, hit revenue targets faster, and make IT more productive. Different tiers of Adeptia’s solution allow you to choose what you need for your organization. One platform for all integration scenarios (B2B integration, Application/ESB integration, Data/ETL integration, Cloud integration and Business Process Management). Adeptia Connect contains user interfaces for both business users and developers in the same product. State-of-the-art technology that utilizes a completely web-based user interface with a central object repository. We’ve merged integration with Business Process Management, allowing integration-centric business processes to be fully automated.

Adeptia Connect doesn’t just support system-to-system data flows. Adeptia Connect has the unique capability to support human-to-system flows. State-of-the-art, meta-driven approach to creating and deploying process flows provides all the benefits of SOA-based approaches (abstraction, inheritance, and more). Adeptia’s Data Mapper is a graphical tool that makes it easy to configure data transformation, mapping, validation, and cleansing rules to process information. Adeptia’s Process Designer is a graphical process design and modeling interface for creating process flows with an easy-to-use, drag-and-drop interface.

Want to learn more about Adeptia’s ETL architecture?