SQL Parsing of Transformation Expressions in Import Bridges

Most Database, Data Integration (DI), and Business Intelligence (BI) import bridges depend on a common "SQL Transformation Expression Parsing" library to extract the data flow lineage out of the many transformation expressions used by these technologies. In fact, each tool and technology has its expression syntax. DI and BI tools often defined their transformation expression languages with a syntax close to programming languages like Visual Basic or Java. However, most DI and BI tools have support for SQL override, allowing data transformations to be defined as the full SQL executed as is by the original database. Note that this SQL parsing does not support the syntax of special data stores such as MOLAP Cubes often seen in BI tools such as Oracle Hyperion Essbase.

The section explains the general limitations in expression parsing, in particular with respect to the SQL syntax variations from the SQL 99 standard to the limitations in proprietary variations and database extensions for each popular database such as IBM DB2, Microsoft SQL Server, Oracle, Teradata, etc.

Furthermore, the support for SQL use cases from Data Definition Language (DDL) to Data Manipulation Language (DML) is increasingly limited by the complexity as the SQL features: