Extract, Transform, and Load (ETL) is the process by which large amounts of data is extracted from disparate data sources and consolidated into a single database. In the extraction phase, data is parsed and evaluated for suitability. During transformation, data is manipulated to achieve the format required for storage. Some common transformations include the elimination of unnecessary columns, calculation of computed values, and translation of values such as dates into a common format so that the data can be consolidated. The data is then loaded into the database at a frequency and scope consistent with the organization's needs.
SQL Anywhere offers several features in support of ETL. For example:
OPENSTRING operation Use the OPENSTRING operation in the FROM clause to transform and load data from client- and server-side data sources. See FROM clause.
openxml system procedure Use the openxml system procedure to extract data from XML documents. See openxml system procedure.
MERGE statement Use the MERGE statement to merge data from different source objects. See MERGE statement.
Proxy tables Use proxy tables to access objects such as tables, views, and materialized views in a remote database. See Working with proxy tables.
System procedure calls in the FROM clause You can use various system procedures in the FROM clause of a query to extract and transform data for loading. For a list of system procedures offered in SQL Anywhere, see System procedures.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|