Using proper ETL / ELT tools is the basis of success when implementing a data warehouse. The aim is for the customer to receive data for reporting purposes on time and in good quality. It is not enough to simply record data; it should be continuously monitored to avoid errors over time due to delays or incorrect data processing.
There are, essentially, two approaches to implementing a data pump:
ETL (Extract, Transform and Load) - transfers data so that the data are first extracted from the database source, then transformed (in the intermediary layer stage) into the target structure and subsequently transferred to the target database. This results in more accurate targeting of the desired data to create a business advantage.
ELT (Extract, Load and Transform) - a data transfer where the data extracted from the source database and „all“ is made available to the target database. The selected data is then transformed to the required outputs. This approach is more flexible in terms of data availability, but more demanding on the amount of data.
Compared to the classic ETL (Extract, Transformation, Load) architecture, ELT (Extract, Load, Transformation) eliminates the need of a specialized transformation intermediate layer in the form of specialized ETL tools and related specialized infrastructures.
Transformations are already carried out in the source or, more often, target systems, which leads to significantly higher usage of the acquired infrastructure. ELT architecture is much faster and more efficient than the ETL solution, due to the limitations of the data transfer overhead and optimized transformation technologies and algorithms. ELT solutions are not suitable for technological environments which are highly heterogeneous, however, we can solve this too. The original approach to ELT architecture meant long and inefficient manual code writing and while specialized ELT tools exist, they carry many restrictions and come at a high price. To overcome such disadvantages, Adastra has developed its own metadata-driven ELT generators to generate standardized and highly performing ELT. Our ELT generators have been created primarily for the Oracle Database platform, however their universal architecture enables easy migration onto any target platform.
In our opinion the main advantages of our ELT generators are:
The Informatica PowerCenter is a longstanding, proven ETL/ELT tool that can be used for ETL/ELT tasks as well as for data migration tasks, one-offs or repeated data cleaning and data profiling. It can process data from numerous databases, files, applications, messaging middleware layers and others.
Data can be structured or non-structured. You can transfer the performance of transformations from an ETL tool onto a source or target database server as you see fit to speed up the entire process allowing you to use the database server performance and take some of the load off the ETL server. Of course, the ETL tool automatically creates metadata for all the activities in it. Metadata are accessible in relational form via the so-called MX Views, or, can be further processed in other reporting tools or via the specialized Informatica Metadata Manager application.
The Informatica PowerCenter is a comprehensive tool for developing transformations, managing entire workflows and monitoring operations.
We have extensive experience with Informatica Powercenter technology to help you with your projects.
The Oracle Data Integrator is an integration platform for any data integration (migrations, replications, data pumps for DWH, synchronizations in MDM and data and transformation services within SOA) in a heterogeneous environment. It features:
Heterogeneous E-LT Architecture
From the heterogeneity point of view, connectors for various technologies and applications are supplied with ODI. The entire system is open: you can modify the connectors and add or define your own.
The orchestration itself is carried out by an ODI Agent that can be configured for ‚load balancing‘ (multiple concurrent Agents) and can run on any JAVA supporting platform.
ODI lets you solve any type of data integration – from standard bulk transfers (Data-Oriented Integration) via real time transmission (Event-Oriented Integration) up to transfers using services (Service-Oriented Integration)
There is no need for a developer to be a „guru“ of all technologies and integration areas – ODI will help them thanks to declarative design and the "Knowledge” modules.
Currently, there are „best practice“ processes for almost everything. This applies for data integration and available technologies. ODI includes more than 150 such ready-made „best practices“ procedures/templates (the so-called Knowledge Modules) for various areas of data integration and assorted technologies.
All Knowledge Modules included are open, you can study them, modify them or create your own.
As the name suggests, MS Integration Services solve data integration between individual systems. Thus, the source, as well as the destination of the data stream, can be relational and non-relational databases, text files, web services etc. If the need arises to integrate into an outside data source, you may write your own connector in the MS.NET Framework and run MS Integration Services on it.
Data stream integration is designed in a fully graphical environment which includes a debugger that displays the flow of data. There are desktop specialists available to assist your team or you can rely on Adastra's support for the graphical environment.
Here is a successful design-setting and testing where the data transfer process is implemented on an MS SQL server.
The Microsoft Power Query is an extra Add-on for MS Excel (for the 2010 version onwards) which is part of the BI self-service for Excel. It is an intuitive tool for editing, combining and cleaning data from various sources (relational, structured data, partially structured data, non-structured data). You can download the data from various sources, and, unlike standard databases, it offers the option to import from files of various formats, as well as from the web, Sharepoint, Odata, the Azure cloud, Hadoop (HDFS, Hive), Active Directory, Exchange and Facebook.
Intuitive mouse control, with an immediate preview of the resulting data and an editing overview of individual steps, is a quick and effective tool suitable for less advanced users. Advanced users can work with an extended option to write individual steps directly or through commands, or, edit the existing steps. Individual sequences (queries) from individual sources can then be combined together. The resulting import is performed either in an Excel spreadsheet or PowerPivot.
DS represents one of the few „all-in-one“ solutions for data integration, data quality, text data processing, data profiling and metadata administration. Open DS architecture is an „information highway“ that enables work to proceed more efficiently with data from both within and outside of SAP system environments.
With DS you can:
Main parts and their properties:
Why choose SAP DS?
Javlin CloverETL is a complex ETL tool that is being developed in the Czech Republic. It is based on Java and integrates data from heterogeneous data sources quickly and easily and for a very interesting price.
The application is divided into the Server and the Designer (GUI), with the GUI also including an ETL engine, so it can be used completely independently without a server. The tool can be used for implementing data warehouses, web service creation, data migration, data quality as well as a tool for implementing a complex transformation/business logic, which can be used, for instance, by the Enterprise Service Bus in real time. Support for Hadoop technologies is built in.
CloverETL offers you:
The tool exists in a community version, desktop version and several server versions, where the highest version supports implementation on application clusters. It can also be acquired for a limited time period. We believe that Javlin CloverETL can compete well enough with the tools that are considered as the top-of-the-art in the field of data integration. We would be happy to help you if you decide to consider this option.