Migration from Informatica PowerCenter to ODI: accelerated with Sql2Odi

Streamlining ETL Migrations with Sql2Odi

I have previously blogged about Rittman Mead's Sql2Odi tool, which translates SQL statements (SELECT or WITH) into Oracle Data Integrator (ODI) Mappings. After years of use, we recently put Sql2Odi to the test during a large ETL migration project. Our goal was to convert Informatica PowerCenter Mappings, which often embed data join and transformation logic in SQL, to ODI Mappings.

The beauty of Sql2Odi lies in its ability to leverage existing SQL code, without having to re-implement all legacy ETL logic by hand. With minimal modifications, we fed query statements from PowerCenter into Sql2Odi and generated fully functional ODI Mappings.

Beyond Basic SQL: Handling Complex Queries

Sql2Odi goes beyond simple queries. It can handle even intricate SQL and generate fully working ODI Mappings:

  • Complex SQL Support: Process intricate logic embedded within your SQL statements, including joins, subqueries, unions, and analytic functions.
  • Target Table Definition: Specify the table where the data will be populated.
  • Column Mapping Flexibility: Define how source data maps to target columns using names or positions.
  • IKM, LKM, and CKM Configuration: Set up Integration, Lookup and Check Knowledge Modules (IKM, LKM, CKM) and configure their parameters.
  • Scenario Generation: Request the creation of a Scenario for the generated ODI Mapping.
  • ODI variables are supported inside strings.

Limitations

While Sql2Odi offers a lot of functionality, here are some limitations:

  • Nested Query Support: Nested SQL queries are not currently supported. (Subqueries are supported.)
  • No Query Lookup Handling: Lookups like WHERE COUNTRY IN (SELECT COUNTRY_NAME FROM ...) are not supported but usually can be re-written as joins.
  • Hierarchical/Pivot Query Limitations: Hierarchical and pivot queries are not supported. Some simpler hierarchical queries can be re-written as joins.
  • Oracle (+) Join Notation: The Oracle (+) join syntax cannot be directly translated. (The (+) notation can be converted to ANSI joins with the latest versions of Oracle SQL Developer. ANSI joins are supported by the Sql2Odi tool.)

Sql2Odi in Action: let the Numbers Speak

In my previous blog posts, I emphasized Sql2Odi's ability to handle complex SQL. Now, I have the numbers to prove it:

  • 92 SQL Statements Parsed: During the ETL migration project, a significant number of real-world SQL query statements were successfully processed, some of them very large and complex.
  • Nearly Half a Megabyte of SQL Converted: These 92 queries translated to a combined size of over 448,220 characters, demonstrating the tool's ability to handle substantial amounts of code.

Parsing Efficiency and Fine-Tuning Opportunities

The SQL2Odi tool follows a two-step process: SQL parsing and ODI content generation. Parsing the 92 statements is remarkably fast, taking less than 7 seconds for our tool. Generating ODI Mappings takes slightly longer, with each Mapping requiring between half a second and a few seconds. (Mapping validation and Scenario generation by the Sql2Odi tool costs extra execution time but also adds value.) While the generated Mappings are fully functional and work correctly, an ODI developer may find opportunities to streamline them for conciseness, especially the Mappings generated from WITH statements.

Proven Success in Large-Scale ETL Migration

This successful project demonstrates the power of Sql2Odi in streamlining ETL migrations and reducing development time. The tool's reliability has been validated in a large-scale project, showcasing its capability to handle even large and complex SQL queries effectively.

NrStatement TypeStatement Length, CharsNr of Joins...of which Outer JoinsNr of Set Operators (UNION, etc)Nr of Analytic Functions (Ranking, Window)
1SELECT statement17373292900
2WITH statement16699353520
3SELECT statement16618171320
4SELECT statement15801272700
5SELECT statement13062272700
6SELECT statement12012161320
7WITH statement111229820
8WITH statement110479820
9WITH statement103079820
10WITH statement10276320110
11SELECT statement9940320110
12SELECT statement9744320110
13WITH statement95798620
14WITH statement9431303000
15SELECT statement928911660
16WITH statement8942191800
17WITH statement86937620
18WITH statement8360161500
19SELECT statement8303212100
20SELECT statement81666000
21WITH statement7705131300
22SELECT statement768720200
23WITH statement7497131300
24WITH statement73087700
25SELECT statement66126400
26WITH statement6556141301
27WITH statement6294111002
28WITH statement6165291131
29SELECT statement606116050
30SELECT statement60428200
31WITH statement5907121120
32SELECT statement573816050
33SELECT statement55169600
34SELECT statement5189251101
35SELECT statement51897200
36WITH statement506919500
37SELECT statement487227300
38SELECT statement46489900
39SELECT statement45051100
40SELECT statement42194400
41SELECT statement42074200
42SELECT statement42022000
43WITH statement41858800
44WITH statement41668800
45WITH statement407315200
46WITH statement39277500
47WITH statement392011100
48SELECT statement36796300
49SELECT statement35494100
50SELECT statement331013200
51SELECT statement32697600
52SELECT statement32628700
53SELECT statement32541100
54WITH statement29564400
55SELECT statement285212000
56SELECT statement267112000
57WITH statement26578500
58WITH statement26152000
59SELECT statement24824300
60SELECT statement22743300
61SELECT statement22412200
62WITH statement22233300
63WITH statement21923300
64SELECT statement17191010
65SELECT statement16853000
66SELECT statement16261000
67SELECT statement16003200
68SELECT statement15692200
69SELECT statement15163200
70SELECT statement14443200
71SELECT statement13041100
72WITH statement13022000
73SELECT statement12663200
74SELECT statement12044200
75SELECT statement11791000
76SELECT statement11200000
77SELECT statement11130000
78SELECT statement9943000
79SELECT statement9690010
80SELECT statement9582200
81SELECT statement9241000
82SELECT statement9200010
83SELECT statement8731000
84SELECT statement7900000
85SELECT statement7301000
86SELECT statement7292000
87SELECT statement7150000
88SELECT statement4700000
89SELECT statement4640000
90SELECT statement4610000
91SELECT statement4352000
92SELECT statement4321100