odi

Migration from Informatica PowerCenter to ODI: accelerated with Sql2Odi

Janis Rumnieks

Apr 24, 2024 • 5 min read

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.

Nr	Statement Type	Statement Length, Chars	Nr of Joins	...of which Outer Joins	Nr of Set Operators (UNION, etc)	Nr of Analytic Functions (Ranking, Window)
1	SELECT statement	17373	29	29	0	0
2	WITH statement	16699	35	35	2	0
3	SELECT statement	16618	17	13	2	0
4	SELECT statement	15801	27	27	0	0
5	SELECT statement	13062	27	27	0	0
6	SELECT statement	12012	16	13	2	0
7	WITH statement	11122	9	8	2	0
8	WITH statement	11047	9	8	2	0
9	WITH statement	10307	9	8	2	0
10	WITH statement	10276	32	0	11	0
11	SELECT statement	9940	32	0	11	0
12	SELECT statement	9744	32	0	11	0
13	WITH statement	9579	8	6	2	0
14	WITH statement	9431	30	30	0	0
15	SELECT statement	9289	11	6	6	0
16	WITH statement	8942	19	18	0	0
17	WITH statement	8693	7	6	2	0
18	WITH statement	8360	16	15	0	0
19	SELECT statement	8303	21	21	0	0
20	SELECT statement	8166	6	0	0	0
21	WITH statement	7705	13	13	0	0
22	SELECT statement	7687	20	2	0	0
23	WITH statement	7497	13	13	0	0
24	WITH statement	7308	7	7	0	0
25	SELECT statement	6612	6	4	0	0
26	WITH statement	6556	14	13	0	1
27	WITH statement	6294	11	10	0	2
28	WITH statement	6165	29	11	3	1
29	SELECT statement	6061	16	0	5	0
30	SELECT statement	6042	8	2	0	0
31	WITH statement	5907	12	11	2	0
32	SELECT statement	5738	16	0	5	0
33	SELECT statement	5516	9	6	0	0
34	SELECT statement	5189	25	11	0	1
35	SELECT statement	5189	7	2	0	0
36	WITH statement	5069	19	5	0	0
37	SELECT statement	4872	27	3	0	0
38	SELECT statement	4648	9	9	0	0
39	SELECT statement	4505	1	1	0	0
40	SELECT statement	4219	4	4	0	0
41	SELECT statement	4207	4	2	0	0
42	SELECT statement	4202	2	0	0	0
43	WITH statement	4185	8	8	0	0
44	WITH statement	4166	8	8	0	0
45	WITH statement	4073	15	2	0	0
46	WITH statement	3927	7	5	0	0
47	WITH statement	3920	11	1	0	0
48	SELECT statement	3679	6	3	0	0
49	SELECT statement	3549	4	1	0	0
50	SELECT statement	3310	13	2	0	0
51	SELECT statement	3269	7	6	0	0
52	SELECT statement	3262	8	7	0	0
53	SELECT statement	3254	1	1	0	0
54	WITH statement	2956	4	4	0	0
55	SELECT statement	2852	12	0	0	0
56	SELECT statement	2671	12	0	0	0
57	WITH statement	2657	8	5	0	0
58	WITH statement	2615	2	0	0	0
59	SELECT statement	2482	4	3	0	0
60	SELECT statement	2274	3	3	0	0
61	SELECT statement	2241	2	2	0	0
62	WITH statement	2223	3	3	0	0
63	WITH statement	2192	3	3	0	0
64	SELECT statement	1719	1	0	1	0
65	SELECT statement	1685	3	0	0	0
66	SELECT statement	1626	1	0	0	0
67	SELECT statement	1600	3	2	0	0
68	SELECT statement	1569	2	2	0	0
69	SELECT statement	1516	3	2	0	0
70	SELECT statement	1444	3	2	0	0
71	SELECT statement	1304	1	1	0	0
72	WITH statement	1302	2	0	0	0
73	SELECT statement	1266	3	2	0	0
74	SELECT statement	1204	4	2	0	0
75	SELECT statement	1179	1	0	0	0
76	SELECT statement	1120	0	0	0	0
77	SELECT statement	1113	0	0	0	0
78	SELECT statement	994	3	0	0	0
79	SELECT statement	969	0	0	1	0
80	SELECT statement	958	2	2	0	0
81	SELECT statement	924	1	0	0	0
82	SELECT statement	920	0	0	1	0
83	SELECT statement	873	1	0	0	0
84	SELECT statement	790	0	0	0	0
85	SELECT statement	730	1	0	0	0
86	SELECT statement	729	2	0	0	0
87	SELECT statement	715	0	0	0	0
88	SELECT statement	470	0	0	0	0
89	SELECT statement	464	0	0	0	0
90	SELECT statement	461	0	0	0	0
91	SELECT statement	435	2	0	0	0
92	SELECT statement	432	1	1	0	0

Sign up for more like this.