* SAS ETL
Sunday, November 28, 2010
DB Software Laboratory ETL software
Oracle Warehouse Builder (OWB) is a tool that enables to design a custom Business Intelligence application. It provides dimensional ETL process design, extraction from heterogeneous source systems, and metadata reporting functions. Oracle Warehouse Builder allows creation of both dimensional and relational models, and also star schema data warehouse architectures.
Except of being an ETL (Extract, Transform, Load) tool, Oracle Warehouse Builder also enables users to design and build ETL processes, target data warehouses, intermediate data storages and users access layers. It allows metadata reading in a wizard-driven form from a data dictionary or Oracle Designer but also supports over 40 metadata files from other vendors.
In modeling of the extraction processes following source systems are supported: Oracle databases, flat files, SAP R/3,DB2, Sybase, Informix, SQL Server and others via Oracle Transparent Gateways, ODBC, Mainframe.
Oracle Warehouse Builder supports Oracle Database (releases 8i, 9i and newer) and flat files as target databases. Moreover, it allows integration with Oracle AS Discoverer, Oracle Workflow and Oracle Enterprise Manager.
ETL mappings and code design
Modeling of ETL processes takes place in a graphical environment. Oracle Warehouse Builder enables mapping of multiple sources into multiple targets as well as PL/SQL transformations of the data. It allows automated or custom mappings. It also allows operations on data and transforms them into PL/SQL code stored in a transformation library to be used later. More advanced operations are supported as well. A test run for selected data is also possible with error reporting. Additionally, Warehouse Builder provides data cleanse function upon loading including complex Name and Address solution and Match-Merge operator.
The dependencies between the mappings can be recorded graphically to an Oracle Workflow engine and process flow definitions can contain multiple activities including external processes.
Deployment
Deployment of the actual code is carried out by a Deployment Manager for all kinds of supported objects and the process includes validation of metadata. The specification to a file system created by a user can be saved and stored for further use.
Managing Metadata
Oracle Warehouse Builder provides a possibility to save an image of the design object at any stage. The snapshot is then stored for comparison and restoration. The metadata repository operates in various languages and allows adjustment of users privileges. Users can also extend their objects definitions to enter information not yet supported. Moreover, Oracle Warehouse Builder contains build-in reporting templates and allows updates in metadata.
Business Intelligence system Management
Oracle Warehouse Builder contains Oracle Enterprise Manager that allows scheduling of the mappings in an Oracle database environment. Additionally, the platform provides Runtime Audit Browser, which is a HTML-based tool detecting errors while loading data, and Changes Management adjusting the warehouse to changes introduced by a user in a logical model.
Oracle Warehouse Builder supports OMG CWM (Object Management Group, Common Warehouse Metamodel) metadata standard and allows to exchange metadata with other tools supporting this standard.
Except of being an ETL (Extract, Transform, Load) tool, Oracle Warehouse Builder also enables users to design and build ETL processes, target data warehouses, intermediate data storages and users access layers. It allows metadata reading in a wizard-driven form from a data dictionary or Oracle Designer but also supports over 40 metadata files from other vendors.
In modeling of the extraction processes following source systems are supported: Oracle databases, flat files, SAP R/3,DB2, Sybase, Informix, SQL Server and others via Oracle Transparent Gateways, ODBC, Mainframe.
Oracle Warehouse Builder supports Oracle Database (releases 8i, 9i and newer) and flat files as target databases. Moreover, it allows integration with Oracle AS Discoverer, Oracle Workflow and Oracle Enterprise Manager.
ETL mappings and code design
Modeling of ETL processes takes place in a graphical environment. Oracle Warehouse Builder enables mapping of multiple sources into multiple targets as well as PL/SQL transformations of the data. It allows automated or custom mappings. It also allows operations on data and transforms them into PL/SQL code stored in a transformation library to be used later. More advanced operations are supported as well. A test run for selected data is also possible with error reporting. Additionally, Warehouse Builder provides data cleanse function upon loading including complex Name and Address solution and Match-Merge operator.
The dependencies between the mappings can be recorded graphically to an Oracle Workflow engine and process flow definitions can contain multiple activities including external processes.
Deployment
Deployment of the actual code is carried out by a Deployment Manager for all kinds of supported objects and the process includes validation of metadata. The specification to a file system created by a user can be saved and stored for further use.
Managing Metadata
Oracle Warehouse Builder provides a possibility to save an image of the design object at any stage. The snapshot is then stored for comparison and restoration. The metadata repository operates in various languages and allows adjustment of users privileges. Users can also extend their objects definitions to enter information not yet supported. Moreover, Oracle Warehouse Builder contains build-in reporting templates and allows updates in metadata.
Business Intelligence system Management
Oracle Warehouse Builder contains Oracle Enterprise Manager that allows scheduling of the mappings in an Oracle database environment. Additionally, the platform provides Runtime Audit Browser, which is a HTML-based tool detecting errors while loading data, and Changes Management adjusting the warehouse to changes introduced by a user in a logical model.
Oracle Warehouse Builder supports OMG CWM (Object Management Group, Common Warehouse Metamodel) metadata standard and allows to exchange metadata with other tools supporting this standard.
Adeptia ETL Suite
AES platform components
Adeptia ETL consists of three distinct components. It has a web-based Design Studio that provides wizard-driven, graphical ability to document data rules as they relate to validations, mapping and edits. This tool includes a library of functions which can be pre-created and reused again and again.
Data Mapper has a preview capability to see actual source and target data, while the rules are being specified, if the source data file is available.
The second component is the Service Repository where all the rules and mapping objects are saved.
The third component is the Run-time Execution Engine where the mapping rules and data flow transactions are executed on incoming data files and messages.
Adeptia ETL Suite key components The diagram represents the key components of Adeptia ETL suite in a greater detail.
Key Features of Adeptia ETL Suite
* Data integration functionality which is perfectly suited for Extract, Transform, and Load (ETL) type of data warehousing solutions for business intelligence, reporting and dashboards
* Process flow based and service-oriented approach for data transformation and integration
* Easy to use, intuitive user-friendly interface to define data mapping and data transformation rules
* Graphical design environment and fully featured run-time engine in one package
* Object-based architecture that promotes reusability of components and functionality across disparate custom packaged and legacy applications
* Web-based interface that enhances productivity with minimal training
* Complete management, reporting and auditing capabilities to track and monitor transactions
What makes Adeptia ETL Suite unique?
* Process-centric Integration in single package
o Allows complete business process around the integration to be automated
o Supports Services Orchestration
o Allows Human Workflow to handle errors, exceptions and approvals
* Enables SOA
o Flows can be exposed as Services
o Includes a services repository
o Metadata Driven approach, Integration components are re-usable
* Quick implementations in days and weeks
o Allows quick projects so customers can see immediate value
o Ease of Use: Graphical, No coding approach ideal for business analysts
o Reduces reliance on expensive consultants
* Flexible pricing reduces customer risk
SOURCE:http://www.etltools.net/adeptia-etl-suite.html
Pervasive ETL Software
Pervasive ETL Software include data integration tools minimizng costs connected with system changing and making integration more efficient. They are designed for small and large companies. Easy-to-use graphical mapping interfaces increases work speed and allows developers to increase productivity.
Pervasive Data Integrator
Pervasive Data Integrator is enviroment which develops functionality for data aggreagating and data movement automating. It could load high performance data warehouse as well as smaller data parts. Unified toolset lets manage integration of many applications and operational data stores. Pervasive Integration Engine supports designing multi-step processes, that can be execute automaticaly. If any errors will apear during the process there are logging or rollback operations avalible. Data integrator supports "upsert" operation, so high-speed operations can be more efficient. Multithreading is also supported.
There is possibility to define rules for complex data aggregation, so multiple elements can be changed by one-pass writing. Pervasive Data Integrator support connectivity to all major databases, common CRM, ERP and COBOL sources.
Data Integrator's capabilities, like real-time continuous integration, native connectivity or legacy conversion, make creating integration solutions simpler.
Pervasive application integration develops functionality to integrate multiple applications (inside or outside of organisation). Enterprise Service Bus, Service Oriented Architecture and point-to-point architectures are supported. Environment can be f.e. used for point-to-point exchange between applications or building message queue for ESB.
Pervarsive solutions are transport independent and support "listening" for data arrival(f.e. from FTP folders). Application integration solution offers capabilities like real time processing, support for "multi mode" and "mass insert" options, increasing database connectivity, application messages validation, ability to keep data objects in buffered memory (for increasing processing speed).
Pervasive Business Integrator
Pervasive Business Integrator supports creating processes taking care of translating documents and moving them among the system. It includes some handy functionalities like advanced messages processing, connectivity to databases or xml format, component checking if there is some data arriving, support for systems of trading partners.
Pervasive Business Integrator lets users create and manage message processing and moving. It provides possibility of connection for major ERP systems (like SAP). Business Integrator uses standard data exchange formats (f.e. XML), supports Java framework and every JMS-compliant queue. Continuous, real time processing is supported. With Business Integrator there is possibility of making scalable integration infrastructure using integration model. This structure can be used in SOA or ESB projects. Messages moving between applications are validating. Pervasive Integration Manager owns management console which helps using this tool.
Data Profiler
Data Profiler is tool that monitore data and identify quality risks. It is available as Pervasive ETL module, but can be used as stand alone application as well. Data problems, when found too late, fe. during project testing, may cause huge problems. Becouse of continuous data quality testing, Data Profiler can prevent them. It improves quality and speed of software production by eliminating expensive and less efficient manual data validating processes. Data Profiler support multiple data platforms (more than 150 adapters), reduses costly and time-consuming refactoring by isolating unsafe data, eliminates intermediate data storage by data formats audit (Effective engine allows large transitional datasets audit), improve organizations internal control, and increase value of final products.
Pervasive Data Integrator
Pervasive Data Integrator is enviroment which develops functionality for data aggreagating and data movement automating. It could load high performance data warehouse as well as smaller data parts. Unified toolset lets manage integration of many applications and operational data stores. Pervasive Integration Engine supports designing multi-step processes, that can be execute automaticaly. If any errors will apear during the process there are logging or rollback operations avalible. Data integrator supports "upsert" operation, so high-speed operations can be more efficient. Multithreading is also supported.
There is possibility to define rules for complex data aggregation, so multiple elements can be changed by one-pass writing. Pervasive Data Integrator support connectivity to all major databases, common CRM, ERP and COBOL sources.
Data Integrator's capabilities, like real-time continuous integration, native connectivity or legacy conversion, make creating integration solutions simpler.
Pervasive application integration develops functionality to integrate multiple applications (inside or outside of organisation). Enterprise Service Bus, Service Oriented Architecture and point-to-point architectures are supported. Environment can be f.e. used for point-to-point exchange between applications or building message queue for ESB.
Pervarsive solutions are transport independent and support "listening" for data arrival(f.e. from FTP folders). Application integration solution offers capabilities like real time processing, support for "multi mode" and "mass insert" options, increasing database connectivity, application messages validation, ability to keep data objects in buffered memory (for increasing processing speed).
Pervasive Business Integrator
Pervasive Business Integrator supports creating processes taking care of translating documents and moving them among the system. It includes some handy functionalities like advanced messages processing, connectivity to databases or xml format, component checking if there is some data arriving, support for systems of trading partners.
Pervasive Business Integrator lets users create and manage message processing and moving. It provides possibility of connection for major ERP systems (like SAP). Business Integrator uses standard data exchange formats (f.e. XML), supports Java framework and every JMS-compliant queue. Continuous, real time processing is supported. With Business Integrator there is possibility of making scalable integration infrastructure using integration model. This structure can be used in SOA or ESB projects. Messages moving between applications are validating. Pervasive Integration Manager owns management console which helps using this tool.
Data Profiler
Data Profiler is tool that monitore data and identify quality risks. It is available as Pervasive ETL module, but can be used as stand alone application as well. Data problems, when found too late, fe. during project testing, may cause huge problems. Becouse of continuous data quality testing, Data Profiler can prevent them. It improves quality and speed of software production by eliminating expensive and less efficient manual data validating processes. Data Profiler support multiple data platforms (more than 150 adapters), reduses costly and time-consuming refactoring by isolating unsafe data, eliminates intermediate data storage by data formats audit (Effective engine allows large transitional datasets audit), improve organizations internal control, and increase value of final products.
SAS ETL
The ETL tools might be developed by different companies. One of them – widely known and popular – is SAS Enterprise which offers an integrated ETL platform.
SAS is over twenty-five-year company from the USA offering business intelligence software. SAS – one of the market leaders – combines data warehousing and intelligence applications for traditional business.
SAS’ platform is advertised as the one to accomplish current requirements of punctuality, cost-effectiveness, credibility and compatibility with different data storing systems. Moreover, the efficiency of SAS ETL is enough to support the whole enterprise processes, managing to extract data from other platforms and sources.
The SAS Enterprise ETL Server offers also an interactive ETL environment providing some facilities such as multithreaded and multiprocessing data extraction and the engines – able to work in the same time – that minimizes the time needed for succeeding the operations and data transfers. Also the calendar and job and resource dependency managing tools are included. What’s worth pointing, SAS ETL environment might be controlled with SAS ETL Studio (also included) that enables an access to the whole database from one point.
The SAS platform is also able to reduce duplicate or inaccurate data – that option permits to reduce overgrowing costs. It was projected to simplify the ETL processes – large pressure was put on the financial aspects. Also the efficiency got improved – all the algorithms, transformations and codes are registered (creating a full documentation) to make them easy revocable – it saves a lot of time. The drag-and-drop interface also simplifies the use, not demanding complicated knowledge from programming or SQL (Structured Query Language – database language for managing data).
SAS ETL is only one platform but the market offers much wider choice. Although all the companies, more or less, aspire to the common goal which is improving databases’ managing, platforms offered by them differs significantly. It’s impossible to tell unambiguously which one is the best – it all depends on the needs and requirements.
More information on SAS ETL and Data Integration: SAS tutorial
SAS is over twenty-five-year company from the USA offering business intelligence software. SAS – one of the market leaders – combines data warehousing and intelligence applications for traditional business.
SAS’ platform is advertised as the one to accomplish current requirements of punctuality, cost-effectiveness, credibility and compatibility with different data storing systems. Moreover, the efficiency of SAS ETL is enough to support the whole enterprise processes, managing to extract data from other platforms and sources.
The SAS Enterprise ETL Server offers also an interactive ETL environment providing some facilities such as multithreaded and multiprocessing data extraction and the engines – able to work in the same time – that minimizes the time needed for succeeding the operations and data transfers. Also the calendar and job and resource dependency managing tools are included. What’s worth pointing, SAS ETL environment might be controlled with SAS ETL Studio (also included) that enables an access to the whole database from one point.
The SAS platform is also able to reduce duplicate or inaccurate data – that option permits to reduce overgrowing costs. It was projected to simplify the ETL processes – large pressure was put on the financial aspects. Also the efficiency got improved – all the algorithms, transformations and codes are registered (creating a full documentation) to make them easy revocable – it saves a lot of time. The drag-and-drop interface also simplifies the use, not demanding complicated knowledge from programming or SQL (Structured Query Language – database language for managing data).
SAS ETL is only one platform but the market offers much wider choice. Although all the companies, more or less, aspire to the common goal which is improving databases’ managing, platforms offered by them differs significantly. It’s impossible to tell unambiguously which one is the best – it all depends on the needs and requirements.
More information on SAS ETL and Data Integration: SAS tutorial
Sybase ETL
Sybase ETL application has been designed for loading the data from the source systems, transferring them to data sets, and in result, loading those transformed data to target data warehouse or database. ETL solution is characterized by easiness of operating and a high level of scalability, which has been obtained because of the unique GRID architecture which the platform is based on.
The new generation of Sybase ETL application – Transform on Demand, provides the automation of data integration, by means of the unique method (Draw, Simulate, and Execute). This application doesn’t require any programming. Furthermore, it enables design, monitoring and planning the data processing, depending on the user’s needs. The complex simulation environment allows tracking down errors, which have occurred within data flow, and make the necessary transformations to prevent the damage of target data.
Sybase ETL benefits
* Minimizing the time, costs and human resources which are needed to operate application interfaces, data integration and extraction processes, transformation and data loading (ETL-extract, transformation, loading)
* Repeatability of data transformation projects (there is no need to create the new projects from the beginning)
* No professional training is needed, in scope of basic and not very complex data transformation processes; in case of advanced issues, only the basic training is required
* Easiness of updating, by means of the initialization of Warehouse Workbench application
* Lowering the costs, thanks to the automatic registration of the whole data transformation process
* Upgrading the quality of data by using the unique simulation environment before the right process is triggered
Sybase ETL most important features
* Using the unique method (Draw, Simulate, and Execute)
* Using of the modern technologies as: Java, SOAP I XML
* Great efficiency, due to Native Data access technology
* Possibility of making any types of transformations
* Operation of all commonly used types of data bases sources, target data bases, data sources WWW, XML language, homogenous files; compliance with ODBC
* Efficient simulation environment allows detecting the damaged data and errors, preventing from their permanent damage.
* Integrated control and align system of processes
* Automatic registration (on paper and XLM form)
* More than two hundred UTL/JavaScript commands, which have been created at an angle of data integration. Installation of application takes less than five minutes.
Uncertain future of Sybase ETL
According to a statement made in March 2009 by Sybase, the future of their ETL product is rather negative. It says that now Sybase ETL can be used only by Sybase IQ users who have license for that product. Because of that some users will be forced to obtain another ETL, or even more than one, for different targets than Sybase IQ instances. Although it is quite obvious that the Sybase ETL is exclusively used by Sybase users it will hinder their work and exporting the data to other parts of their information system will be impossible to carry through. It seams like every single target needs a different tool, which is similar to an old hand-coded system.
The new generation of Sybase ETL application – Transform on Demand, provides the automation of data integration, by means of the unique method (Draw, Simulate, and Execute). This application doesn’t require any programming. Furthermore, it enables design, monitoring and planning the data processing, depending on the user’s needs. The complex simulation environment allows tracking down errors, which have occurred within data flow, and make the necessary transformations to prevent the damage of target data.
Sybase ETL benefits
* Minimizing the time, costs and human resources which are needed to operate application interfaces, data integration and extraction processes, transformation and data loading (ETL-extract, transformation, loading)
* Repeatability of data transformation projects (there is no need to create the new projects from the beginning)
* No professional training is needed, in scope of basic and not very complex data transformation processes; in case of advanced issues, only the basic training is required
* Easiness of updating, by means of the initialization of Warehouse Workbench application
* Lowering the costs, thanks to the automatic registration of the whole data transformation process
* Upgrading the quality of data by using the unique simulation environment before the right process is triggered
Sybase ETL most important features
* Using the unique method (Draw, Simulate, and Execute)
* Using of the modern technologies as: Java, SOAP I XML
* Great efficiency, due to Native Data access technology
* Possibility of making any types of transformations
* Operation of all commonly used types of data bases sources, target data bases, data sources WWW, XML language, homogenous files; compliance with ODBC
* Efficient simulation environment allows detecting the damaged data and errors, preventing from their permanent damage.
* Integrated control and align system of processes
* Automatic registration (on paper and XLM form)
* More than two hundred UTL/JavaScript commands, which have been created at an angle of data integration. Installation of application takes less than five minutes.
Uncertain future of Sybase ETL
According to a statement made in March 2009 by Sybase, the future of their ETL product is rather negative. It says that now Sybase ETL can be used only by Sybase IQ users who have license for that product. Because of that some users will be forced to obtain another ETL, or even more than one, for different targets than Sybase IQ instances. Although it is quite obvious that the Sybase ETL is exclusively used by Sybase users it will hinder their work and exporting the data to other parts of their information system will be impossible to carry through. It seams like every single target needs a different tool, which is similar to an old hand-coded system.
Microsoft Integration Services
Microsoft SQL Server Integration Services (SSIS) is a part of the Microsoft's database product - Microsoft SQL Server. SQL Server Integration Services first appeared in this implementation of SQL in the 2005 and has been continued through to SQL Server 2008. The main purpose of adding this component to the product was to make data integration in the database easy, uncomplicated and fast.
The first question that should be asked is why do we need data integration in a SQL database. Data in regular databases is not very complicated, and accessing it is easy, because data accessing system is often a part of the database itself. The answer is that Microsoft SQL Server is a solution not only for transactional databases, but also for large data warehouses as a company's business intelligence solution. As we know, the data in a data warehouse has to be integrated in order to make it accessible data easy, and as fast as possible.
Like many others professional data warehousing solutions, Microsoft SQL Server has its own data integration tool – called SQL Server Integration Services.
Unique features and components of Microsoft SQL Server:
* Connection monitoring. The application can manage many connections to data sources, so that the data is stored properly.
* Tasks managing component, other part of the Integration Services, controls actions like copying and moving data, collecting the data from sources and others.
* Precedence Constraints which job is to control tasks, monitor their status, check if the tasks are finished and start new tasks.
* Event handlers are very important in some situations when some uexpected things happen to the data warehouse. This component allows the administrator to define what should be done in some abnormal situations. It's a great way to make our system dependable and safe.
Thanks to the components mentioned earlier, Integration Serveces provides many ways to manipulate data in the warehouse. There is a posibility to split the data into groups under some conditions, sort it, merge and many other operations making the Microsoft SQL Server very flexible solution for data warehousing.
There is also a possibility to create some scripts to manipulate with the data. Unfortunately, these scripts can only be created under the .NET enviroment using C# programming language (Windows environments only). There is no support for other programming languages, which is a big disadvantage, because administrator cannot decide how to program the server. From the other hand, there is a great number of online forums, tutorials and support materials for Microsoft programming products like C# or Visual C#.
In the SQL Server 2008 version, a development environment is available for creating data integration procedures. Thanks to the interface of this application, it is easy to use for inexperienced users, but as we all know, user friendly solutions are not always as effective as expert tools.
Integration Services is a great tool not only for companies keeping data warehouses, but also for administrators of small databases. List of its features is impressing, but there are some things that should be changed. Thanks to Integration Services Microsoft SQL Server is a very interesting solution for ETL Data Integration and data warehousing in business intelligence.
The first question that should be asked is why do we need data integration in a SQL database. Data in regular databases is not very complicated, and accessing it is easy, because data accessing system is often a part of the database itself. The answer is that Microsoft SQL Server is a solution not only for transactional databases, but also for large data warehouses as a company's business intelligence solution. As we know, the data in a data warehouse has to be integrated in order to make it accessible data easy, and as fast as possible.
Like many others professional data warehousing solutions, Microsoft SQL Server has its own data integration tool – called SQL Server Integration Services.
Unique features and components of Microsoft SQL Server:
* Connection monitoring. The application can manage many connections to data sources, so that the data is stored properly.
* Tasks managing component, other part of the Integration Services, controls actions like copying and moving data, collecting the data from sources and others.
* Precedence Constraints which job is to control tasks, monitor their status, check if the tasks are finished and start new tasks.
* Event handlers are very important in some situations when some uexpected things happen to the data warehouse. This component allows the administrator to define what should be done in some abnormal situations. It's a great way to make our system dependable and safe.
Thanks to the components mentioned earlier, Integration Serveces provides many ways to manipulate data in the warehouse. There is a posibility to split the data into groups under some conditions, sort it, merge and many other operations making the Microsoft SQL Server very flexible solution for data warehousing.
There is also a possibility to create some scripts to manipulate with the data. Unfortunately, these scripts can only be created under the .NET enviroment using C# programming language (Windows environments only). There is no support for other programming languages, which is a big disadvantage, because administrator cannot decide how to program the server. From the other hand, there is a great number of online forums, tutorials and support materials for Microsoft programming products like C# or Visual C#.
In the SQL Server 2008 version, a development environment is available for creating data integration procedures. Thanks to the interface of this application, it is easy to use for inexperienced users, but as we all know, user friendly solutions are not always as effective as expert tools.
Integration Services is a great tool not only for companies keeping data warehouses, but also for administrators of small databases. List of its features is impressing, but there are some things that should be changed. Thanks to Integration Services Microsoft SQL Server is a very interesting solution for ETL Data Integration and data warehousing in business intelligence.
Oracle Warehouse Builder
Oracle Warehouse Builder (OWB) is a tool that enables to design a custom Business Intelligence application. It provides dimensional ETL process design, extraction from heterogeneous source systems, and metadata reporting functions. Oracle Warehouse Builder allows creation of both dimensional and relational models, and also star schema data warehouse architectures.
Except of being an ETL (Extract, Transform, Load) tool, Oracle Warehouse Builder also enables users to design and build ETL processes, target data warehouses, intermediate data storages and users access layers. It allows metadata reading in a wizard-driven form from a data dictionary or Oracle Designer but also supports over 40 metadata files from other vendors.
In modeling of the extraction processes following source systems are supported: Oracle databases, flat files, SAP R/3,DB2, Sybase, Informix, SQL Server and others via Oracle Transparent Gateways, ODBC, Mainframe.
Oracle Warehouse Builder supports Oracle Database (releases 8i, 9i and newer) and flat files as target databases. Moreover, it allows integration with Oracle AS Discoverer, Oracle Workflow and Oracle Enterprise Manager.
ETL mappings and code design
Modeling of ETL processes takes place in a graphical environment. Oracle Warehouse Builder enables mapping of multiple sources into multiple targets as well as PL/SQL transformations of the data. It allows automated or custom mappings. It also allows operations on data and transforms them into PL/SQL code stored in a transformation library to be used later. More advanced operations are supported as well. A test run for selected data is also possible with error reporting. Additionally, Warehouse Builder provides data cleanse function upon loading including complex Name and Address solution and Match-Merge operator.
The dependencies between the mappings can be recorded graphically to an Oracle Workflow engine and process flow definitions can contain multiple activities including external processes.
Deployment
Deployment of the actual code is carried out by a Deployment Manager for all kinds of supported objects and the process includes validation of metadata. The specification to a file system created by a user can be saved and stored for further use.
Managing Metadata
Oracle Warehouse Builder provides a possibility to save an image of the design object at any stage. The snapshot is then stored for comparison and restoration. The metadata repository operates in various languages and allows adjustment of users privileges. Users can also extend their objects definitions to enter information not yet supported. Moreover, Oracle Warehouse Builder contains build-in reporting templates and allows updates in metadata.
Business Intelligence system Management
Oracle Warehouse Builder contains Oracle Enterprise Manager that allows scheduling of the mappings in an Oracle database environment. Additionally, the platform provides Runtime Audit Browser, which is a HTML-based tool detecting errors while loading data, and Changes Management adjusting the warehouse to changes introduced by a user in a logical model.
Oracle Warehouse Builder supports OMG CWM (Object Management Group, Common Warehouse Metamodel) metadata standard and allows to exchange metadata with other tools supporting this standard.
Except of being an ETL (Extract, Transform, Load) tool, Oracle Warehouse Builder also enables users to design and build ETL processes, target data warehouses, intermediate data storages and users access layers. It allows metadata reading in a wizard-driven form from a data dictionary or Oracle Designer but also supports over 40 metadata files from other vendors.
In modeling of the extraction processes following source systems are supported: Oracle databases, flat files, SAP R/3,DB2, Sybase, Informix, SQL Server and others via Oracle Transparent Gateways, ODBC, Mainframe.
Oracle Warehouse Builder supports Oracle Database (releases 8i, 9i and newer) and flat files as target databases. Moreover, it allows integration with Oracle AS Discoverer, Oracle Workflow and Oracle Enterprise Manager.
ETL mappings and code design
Modeling of ETL processes takes place in a graphical environment. Oracle Warehouse Builder enables mapping of multiple sources into multiple targets as well as PL/SQL transformations of the data. It allows automated or custom mappings. It also allows operations on data and transforms them into PL/SQL code stored in a transformation library to be used later. More advanced operations are supported as well. A test run for selected data is also possible with error reporting. Additionally, Warehouse Builder provides data cleanse function upon loading including complex Name and Address solution and Match-Merge operator.
The dependencies between the mappings can be recorded graphically to an Oracle Workflow engine and process flow definitions can contain multiple activities including external processes.
Deployment
Deployment of the actual code is carried out by a Deployment Manager for all kinds of supported objects and the process includes validation of metadata. The specification to a file system created by a user can be saved and stored for further use.
Managing Metadata
Oracle Warehouse Builder provides a possibility to save an image of the design object at any stage. The snapshot is then stored for comparison and restoration. The metadata repository operates in various languages and allows adjustment of users privileges. Users can also extend their objects definitions to enter information not yet supported. Moreover, Oracle Warehouse Builder contains build-in reporting templates and allows updates in metadata.
Business Intelligence system Management
Oracle Warehouse Builder contains Oracle Enterprise Manager that allows scheduling of the mappings in an Oracle database environment. Additionally, the platform provides Runtime Audit Browser, which is a HTML-based tool detecting errors while loading data, and Changes Management adjusting the warehouse to changes introduced by a user in a logical model.
Oracle Warehouse Builder supports OMG CWM (Object Management Group, Common Warehouse Metamodel) metadata standard and allows to exchange metadata with other tools supporting this standard.
ORACLE DATA INTEGRATOR
Oracle Data Integrator (ODI) is a software application based on ETL (extract-transform-load) structure used for data transformation and merging processes. In today's business the meaning of well organized data has increased dramatically and that is why having specialized software is so important.
Furthermore, these applications must be prepared to work in mixed systems and platforms environment and be sure that none of the quality will deteriorate. Oracle Data Integrator provides decalrative rules apart from implementation details and also its E-LT (extract-load, transform) structure eliminates need of having separate ETL server which results in optimized performance, efficiency and scalability.
ORACLE DATA INTEGRATOR architecutre
Main architecture component in Oracle Data Integrator is the repository that is accessed by client-server mode or thin client mode.
Repository is absolutely open, comprehensive, shared metadata database which is accessible for the applications responsible for transformation, project development, integration and information redistribution. Other architecture components are the Topology Manager, Designer, Security Manager, Operator, Agent, Scheduler and Metadata Navigator.
Graphical interface module that manages all the information about system's physical and logical structure is called the Topology Manager. Descriptions created with the Topology Manager of server data, machine and site will allow to run same interfaces on different systems.
A tool designed to develop transformations and to check data integrity is called Designer. Among many fuctions its most significant are visualization of data flow, development and maintenance data integration and transformation, reverse-engineering of applications or repository's data.
Oracle Data Integrator permissions and users management tool is simply called Security Manager. Operator is the module responsible for monitoring and production management. Agent executes application interfaces which Designer designed, next the Scheduler schedules them.
Metadata Navigator browses through contents of Oracle Data Integrator Repository in search of wanted content. It is a thin client-oriented for search and analysis but also to manage all production processes, it can also trace and cross-reference data lineage.
Apart from this standard modules comes a bunch of „out-of-the-box” modules which can be fully extensible, e.g. Log Miner, SQL Server Triggers, Oracle DBLink, Check MS Excel, Oracle Merge, Check Sybase – all as a part of Pluggable Knowledge Architecture are written for certain systems and environments and in fact are the very core of the Oracle Data Integrator, making the whole process accessible, modular and as simple as it can be.
In case where our target is an Oracle database, natural choice is to go with Oracle Warehouse Builder, but where target is not an Oracle based environment, like SQL Server or XML, Teradata we can use Oracle Data Integrator to support OWB. When the source is XML or Web Service, SOAP then ODI supplements as well. Clearly Oracle Data Integrator can be used in many different cases – e.g. when some dirty data has to be filtered out before loading, or data integration is very complicated or/and processed in real-time.
Oracle Data Integrator works as well in the staging and transforming area as the support for other Oracle software. It is „hot pluggable” which means connecting to any heterogeneous environment and still provide with number of required operations.
SOURCE:http://www.etltools.net/oracle-data-integrator.html
Furthermore, these applications must be prepared to work in mixed systems and platforms environment and be sure that none of the quality will deteriorate. Oracle Data Integrator provides decalrative rules apart from implementation details and also its E-LT (extract-load, transform) structure eliminates need of having separate ETL server which results in optimized performance, efficiency and scalability.
ORACLE DATA INTEGRATOR architecutre
Main architecture component in Oracle Data Integrator is the repository that is accessed by client-server mode or thin client mode.
Repository is absolutely open, comprehensive, shared metadata database which is accessible for the applications responsible for transformation, project development, integration and information redistribution. Other architecture components are the Topology Manager, Designer, Security Manager, Operator, Agent, Scheduler and Metadata Navigator.
Graphical interface module that manages all the information about system's physical and logical structure is called the Topology Manager. Descriptions created with the Topology Manager of server data, machine and site will allow to run same interfaces on different systems.
A tool designed to develop transformations and to check data integrity is called Designer. Among many fuctions its most significant are visualization of data flow, development and maintenance data integration and transformation, reverse-engineering of applications or repository's data.
Oracle Data Integrator permissions and users management tool is simply called Security Manager. Operator is the module responsible for monitoring and production management. Agent executes application interfaces which Designer designed, next the Scheduler schedules them.
Metadata Navigator browses through contents of Oracle Data Integrator Repository in search of wanted content. It is a thin client-oriented for search and analysis but also to manage all production processes, it can also trace and cross-reference data lineage.
Apart from this standard modules comes a bunch of „out-of-the-box” modules which can be fully extensible, e.g. Log Miner, SQL Server Triggers, Oracle DBLink, Check MS Excel, Oracle Merge, Check Sybase – all as a part of Pluggable Knowledge Architecture are written for certain systems and environments and in fact are the very core of the Oracle Data Integrator, making the whole process accessible, modular and as simple as it can be.
In case where our target is an Oracle database, natural choice is to go with Oracle Warehouse Builder, but where target is not an Oracle based environment, like SQL Server or XML, Teradata we can use Oracle Data Integrator to support OWB. When the source is XML or Web Service, SOAP then ODI supplements as well. Clearly Oracle Data Integrator can be used in many different cases – e.g. when some dirty data has to be filtered out before loading, or data integration is very complicated or/and processed in real-time.
Oracle Data Integrator works as well in the staging and transforming area as the support for other Oracle software. It is „hot pluggable” which means connecting to any heterogeneous environment and still provide with number of required operations.
SOURCE:http://www.etltools.net/oracle-data-integrator.html
BusinessObjects Data Integrator
The use of reliable and correct data is crucial for the business users and requires guarantee of data quality and both proper and constant controls and providing lineage of the data. Delivery of trusted information requires the process of data integration which enables data management to be smoothly performed thanks to e.g. finding sources of data, correcting errors, making changes. BusinessObjects Data Integrator (BODI) is a complex tool which provides a lot of options in data manipulation.
1. BusinessObjects Data integration process is divided into the following operations: Data unification
2. Data profiling
3. Data auditing
4. Data cleansing
Data unification
BusinessObjects Data Integration lets users operate and make changes in the data quickly and easily.
Thanks to the unique and helpful solutions it allows to integrate data from the source to the end-user reports and analysis and is crucial to establish the lineage of the data and changes to the data sources. The possibility of monitoring the whole process starting from the results to the data sources enables to find out directly which data origins from which source. It is useful not only to deliver trusted and reliable information but also to have an outlook on overall architecture.
BusinessObjects Data Integrator makes possible quick and trouble-free updating and creating the universes objects (using the BusinessObjects Data Integrator Designer tool). Those actions are facilitated thanks to specific information (column descriptions, data catalogue information) that is loaded into the universes and which help to build the universes easily. All the changes and the data lineage that are easy to be checked cause reduction of time consumption and labour-intensity of objects management, metadata sharing and updating. But it is not only useful for building or updating universes but the end user can see how their data was transformed.
Another Data Integrator tool provided by SAP BusinessObjects is Data Mart Accelerator for Crystal Reports. It allows to extract data from the Crystal Reports instance to aggregate-aware tables automatically. In addition, owing to creation of a time dimension it is possible to perform constantly trend-based analysis.
Data Profiling
BusinessObject Data Integrator has got a well developed instrument for operating in the design environment. The Designer program can be used for the majority of designing operations such as data mapping, transformations and ETL logics as well as for creating workflow and data flows. The developer who works with the Designer tool can create, preview and configure complex data profiles with minimum of efficiency and time. And what is unique, the developer can view the results before and after they are extracted or transformed without leaving the design environment.
Data validation
Data validation is one of the most essential elements of the trusted information delivery. To provide accurate information is to be sure that there is no erroneous or unreliable data. The BusinessObject Data Integration process helps to identify and then correct or reject incorrectness, NULL value data or out of range data. It is very useful when specific records or column needs to be validated by means of the changed rules.
Data Auditing
Process of data auditing consists in verifying data integrity, especially if the data is read, processed and loaded successfully. BusinessObject Data Integrator allows to build audit statistics in accordance with the specific categories (sum, average or row count) and then confront them with the established business rules.
Data cleansing
With Data Integrator it is possible to maintain data quality through data correcting, standardizing and consolidating. The process of cleansing data is particularly important because of common errors in consumers contact information. According to the data integration process it is possible to validate address information by parsing, matching and merging.
1. BusinessObjects Data integration process is divided into the following operations: Data unification
2. Data profiling
3. Data auditing
4. Data cleansing
Data unification
BusinessObjects Data Integration lets users operate and make changes in the data quickly and easily.
Thanks to the unique and helpful solutions it allows to integrate data from the source to the end-user reports and analysis and is crucial to establish the lineage of the data and changes to the data sources. The possibility of monitoring the whole process starting from the results to the data sources enables to find out directly which data origins from which source. It is useful not only to deliver trusted and reliable information but also to have an outlook on overall architecture.
BusinessObjects Data Integrator makes possible quick and trouble-free updating and creating the universes objects (using the BusinessObjects Data Integrator Designer tool). Those actions are facilitated thanks to specific information (column descriptions, data catalogue information) that is loaded into the universes and which help to build the universes easily. All the changes and the data lineage that are easy to be checked cause reduction of time consumption and labour-intensity of objects management, metadata sharing and updating. But it is not only useful for building or updating universes but the end user can see how their data was transformed.
Another Data Integrator tool provided by SAP BusinessObjects is Data Mart Accelerator for Crystal Reports. It allows to extract data from the Crystal Reports instance to aggregate-aware tables automatically. In addition, owing to creation of a time dimension it is possible to perform constantly trend-based analysis.
Data Profiling
BusinessObject Data Integrator has got a well developed instrument for operating in the design environment. The Designer program can be used for the majority of designing operations such as data mapping, transformations and ETL logics as well as for creating workflow and data flows. The developer who works with the Designer tool can create, preview and configure complex data profiles with minimum of efficiency and time. And what is unique, the developer can view the results before and after they are extracted or transformed without leaving the design environment.
Data validation
Data validation is one of the most essential elements of the trusted information delivery. To provide accurate information is to be sure that there is no erroneous or unreliable data. The BusinessObject Data Integration process helps to identify and then correct or reject incorrectness, NULL value data or out of range data. It is very useful when specific records or column needs to be validated by means of the changed rules.
Data Auditing
Process of data auditing consists in verifying data integrity, especially if the data is read, processed and loaded successfully. BusinessObject Data Integrator allows to build audit statistics in accordance with the specific categories (sum, average or row count) and then confront them with the established business rules.
Data cleansing
With Data Integrator it is possible to maintain data quality through data correcting, standardizing and consolidating. The process of cleansing data is particularly important because of common errors in consumers contact information. According to the data integration process it is possible to validate address information by parsing, matching and merging.
Subscribe to:
Posts (Atom)
