DWH projects are an endless loop of code writing, keeping quality standards, change, and code re-writing again. (CIO)

Use Cases & Pain Points Addressed

This tool solves the following pain points, or greatly reduces their impact:

DWH projects are lengthy and expensive. This tool reduces development time to a fraction of what it used to be, and thereby also reduces overall delivery time.

There is too much focus on development and too little on analysis, therefore there is often plenty of rework.

The ETL architecture is complex and sensitive to quality variations, adding risks to maintenance, operations, SLA's.

This tool helps in avoiding a degradation of standards throughout the solution life-cycle time, especially with high resource turn-over:
  • It is difficult/expensive to find competent resources in ODI, and to retain them.
  • Documentation gets outdated quickly… there is a need for auto-documentation as well as integration with modelling tools (e.g. Power Designer).
  • The tool also provides clarity about testing scope and guidelines, i.e. methodology alignment, e.g. what is the reference basis for test results?
  • Similarly, clear lines are drawn about input/output & responsibilities of the business analyst, tester, and developer.

The SQL code is the output of the Analysis effort, and input to starting the development work. A validated SQL statement is one that demonstrates that the mapping and transformation is in principle meeting the business requirement... pending its realization in an ETL tool and in-line with the ETL architecture guidelines!

It can be used in the following situations:
  • Development of an entire new DWH from scratch.
  • Development of new DWH modules and new requirements.
  • Architecture review of an existing DWH solution and streamlining on its standards.
  • Migration, e.g. from ODI 11g to ODI 12c.
  • Migration from another ETL tool to ODI 12c (provided the other tool can generate standard SQL from its own code).

Key Features & Differentiators

The following features and advantages should be noted:

Characteristics of the generated output:
  • The output of the ODI-generated code is expert-grade quality.
  • It guarantees constant standards, and compliance with ETL architecture guidelines.
  • Enablement of lineage is given automatically and guaranteed (for impact analysis).
  • Code generation is completed within seconds of processing the query, and can be tested right after.
  • Project/Solution-related documentation can be generated automatically (to Excel).
  • The generated output can also be uploaded to modelling tools (e.g. PowerDesigner), thereby also keeping mapping documentation updated on model-level (e.g. including comments derived from information such as which Knowledge Module is being used).

All forms of deployment are supported, incl. Cloud scenarios:
  • Both tool & ODI repository are on premise: the source or target databases can be either on premise or in Cloud: no problem.
  • Tool is on premise side but ODI repository is in the Cloud: as long as the tool can connect to ODI repository: no problem.
  • Both tool & ODI repository are deployed in the Cloud (dockerized): no problem.

The whole development is made possible without the need of using ODI Studio: it is sufficient to work through standard templates, which mostly reflects work done by Business Analysts.

As a whole, this product vastly accelerates the development effort (reducing needs for Technical Specialists/ETL Developers to a few resources, mostly to verify appropriate use of Excel templates, to review/optimize the SQL code before generation, as well as for Test support).

It reduces DWH project time by an approximate 50%(!), shifting core efforts and focus from development to analysis.

Example use case from a large corporation:
  • Completed DWH migration from Oracle 11g to 12c… 120 jobs in 2 days (Interconnect part of a DWH).
  • The code itself was generated in 30 minutes, but preparations steps (including adding column and connection to new KM took the remaining time.