Oracle Data Integrator Assessment Report

This topic contains information about the ODI assessment report. The assessment assesses workloads and produces in-depth insights that help to plan the migration. The input format for ODI assessment is XML file format.

In This Topic:

Highlights
Analysis
Lineage
Downloadable Reports

Highlights

The highlights section gives you a high-level overview of your assessment summary of the analytics performed on the selected workloads. It includes a graphical depiction of the complexity of packages, summary of the source input files, orchestrations, unpackaged files, and so on.

Summary

This section illustrates the input ODI scripts that are analyzed throughout the various components. Here, you can see the number of files, packages, unique entities, mappings, and more.

Files: Displays the number of source files.
Packages: Displays the number of packages existing in the source files.
Unique Entities: Displays the total number of unique entities (without duplicate entities) in the source files.
Mappings: Displays the total number of mappings. It describes the flow of data between the source and the target.
Scenario: These are codes generated to implement ODI objects such as mappings, packages, etc., in a production environment.
Procedures: Displays the total number of procedures. It is a set of sequential commands.
Variables: Displays the total number of variables.

Complexity

This section provides a summarized graphical representation of the ODI packages classification based on complexity. This information is required to make various decisions, including migration planning, and estimating budget.

Queries

It displays a synopsis of analyzed queries with information about unique text, unique pattern, as well as parsed and unparsed queries.

Unique (Text): Duplicate queries are eliminated, and solitary queries are identified.
Unique (Pattern): Identify the distinct query structures but the query structure’s values may change.
Parsed Queries: Displays the number of parsed queries.
UnParsed Queries: Displays the number of unparsed queries.

Statement Types

This section displays a summary of analyzed queries based on the query distribution sorted by the different query statement types in the input source file. Moreover, it displays a visual representation of the complexity breakdown across various statement types.

Query Complexity

This section provides a summarized graphical representation of queries’ complexity that helps in making different decisions, including budget estimation.

Complexity Type	Complexity Range	Description
Low	0-4	Handled directly by the tool, it requires no manual effort.
Medium	4-10	Most of the queries can be converted directly by the tool, and some require minimum manual effort.
High	10+	Complexity is high and requires more manual effort to handle.

Orchestration

This section provides a summary of load plans along with the step types.

Unpackaged Files

Here, you can see a summary of step types that are defined outside the package, for example, mappings, procedures, and so on.

Analysis

This topic provides a detailed examination of packages, entities, jobs, orchestration, and unpackaged files.

Source Analysis

This section provides a comprehensive report of the source files with information about packages, mappings, procedures, variables, and more.

Package Name: Displays the package name in the file.
File Name: Name of the file.
Mapping: Displays the number of mappings in each package. It describes the flow of data between the source and the target.
Procedure: Displays the number of procedures in each package. It is a set of sequential commands.
Scenario: These are codes generated to implement ODI objects such as mappings, packages, etc., in a production environment.
Variables: Displays the count of variables in each package.
Complexity: Displays the complexity of each package.

Entities

This section displays a detailed analysis of the entities. It includes information about the schemas, frequency of tables used, and the associated source files.

Table Name: Name of the table.
Schema Name: Name of the schema.
Frequency: Displays the frequency of table used.
File Name: Displays the associated source file of each table.

Jobs

This section displays detailed information about the job such as its type, package, and more.

Job Name: Name of the job.
Job Type: Displays the type of job such as Mapping, Scenario, Procedure, Variable, or Others.
- Mapping: Mapping describes the flow of data between source and target.
- Procedure: Set of sequential commands.
- Scenario: Scenarios are codes generated to implement ODI objects such as mappings, packages, etc., in a production environment.
- Variable: Object that holds only one value.
- Others: Jobs that don’t belong to Mapping, Procedure, Scenario, or Variable.
Package Name: Displays the associated package.
File Name: Displays the associated source file.

Orchestration

This section displays detailed information about the load plan such as its step type, status, and more.

Load Plan Name: Name of the load plan.
Step Type: Displays the step types such as Mapping, Package, Scenario, Procedure, Variable, or Others.
Active Flage: Indicates whether the step type is active or not.
File Name: Displays the associated source file.

Unpackaged Files

This section displays detailed information about the artifacts defined outside the package that are segregated into mappings and procedures.

Artifact Name: Name of the artifact.
Step Type: Displays step types such as Mapping or Package.
Step Name: Displays the step name.
File Name: Displays the associated source file.
Complexity: Displays the complexity of each step count.

Lineage

End-to-end data and process lineage identify the complete dependency structure through interactive and drill-down options to the last level.

Typically, even within one line of business, multiple data sources, entry points, ETL tools, and orchestration mechanisms exist. Decoding this complex data web and translating it into a simple visual flow can be extremely challenging during large-scale modernization programs. The visual lineage graph adds tremendous value and helps defining the roadmap to the modern data architecture. It deep dives into all the existing flows, like Autosys jobs, applications, ETL scripts, BTEQ/Shell (KSH) scripts, procedures, input and output tables, and provides integrated insights. These insights help data teams to make strategic decisions with greater accuracy and completeness. Enterprises can proactively leverage integrated analysis to mitigate the risks associated with migration and avoid business disruptions.

LeapLogic’s lineage helps to trace the complete data flow in steps. The visual representation helps to track how the data hopped and transformed along the way, with details of parameter changes, etc. It provides backward and forward lineage showing the hops and reference points of data lineage with a complete audit trail of the data points of interest. They also help temporarily ignore peripheral, unwanted data points like system tables, bridge tables, etc. and establish useful correlations between data points to uncover hidden patterns and results. Users can deep dive into certain flows leveraging next-gen features like contextual search, advanced filters according to workload type, an interactive visual interface, graph depth, and more.

Now, let’s see how you can efficiently manage lineage.

To view the required lineage:

Select either the Process or Data tab to enable process or data lineage respectively.
Enter the keywords in the Search Keywords field you want to search. Else, simply select the entities, procedures, packages, mappings, etc., from the Search dropdown.
To generate column-level lineage, turn on the Include Columns toggle (available only in Data Lineage).

Click the Search icon to generate the lineage.

Lineage helps you understand dependencies across processes, tables, and columns:

Process lineage illustrates the dependencies between two or more processes such as entities, procedures, packages, mappings, etc.
Data lineage depicts the table-level dependencies and column-level dependencies–input tables, output tables, and reference tables.
Column-Level lineage shows granular transformations and updates at the minutest level.

In addition, the filter search icon allows you to include or exclude particular nodes to obtain the required dependency structure. It helps you quickly narrow down complex dependency graphs to focus on the exact nodes, relationships, or data flows you want to analyze. Instead of navigating through an entire lineage network, you can apply filters based on object names, types, or relationship categories—making it easier to isolate specific tables, workflows, or transformation paths. This feature enhances traceability, reduces visual clutter, and enables you to precisely identify upstream or downstream dependencies that are most relevant to your analysis or migration scope.

You can also choose the direction of the lineage. By default, the Dependency Direction is Left to Right Hierarchy. This insight is useful to identify and analyze upstream data connections and relationships. You can also choose Right to Left Hierarchy – most useful for how data is getting consumed through the downstream route and which are the participating tables and columns, or Bidirectional dependency direction to visualize both kinds in a single view.

Moreover, you can also increase the Hierarchy Levels to nth level. This again allows great freedom to visualize the end-to-end hierarchy in one go.

Lineage facilitates you visualize how your selected nodes are connected and depend on each other. The nodes and their connecting edges (relationships) help you to understand the overall structure and dependencies.

Nodes	Edges
Tables	Call
File	Read
Procedure	Execute
View	Write
Bridge Table	OTHERS
Mapping	Contains
Package
Scenarios

Manage Lineage

This feature enables you to view and manage your lineage. You can add, modify, or delete nodes and their relationships to generate an accurate representation of the required dependency structure. There are two ways to update the lineage: either using Complete Lineage report or Lineage Template.

Using Complete Lineage report

Follow the below steps to modify the lineage:

Click the Manage Graph icon.

Click Download Complete Lineage to update, add, or delete the nodes and their relationships in the current lineage.

Once the complete lineage report is downloaded, you can make necessary updates such as updating, deleting or adding the nodes and its relationships.
After making the required changes, upload the updated lineage report in Upload to Modify Lineage.
Click Apply to incorporate the updates into the dependency structure.
Generate the required process or data lineage.

Using Lineage Template

Follow the below steps to add new nodes and their relationships to the current lineage report:

Click the Manage Graph icon.

Click Download Lineage Template.

Once the lineage template is downloaded, you can add new nodes and relationships in the template.
After making the required changes, upload the template in Upload to Modify Lineage.
Click Apply to incorporate the updates into the complete dependency structure.
Generate the required process or data lineage.

Important

To effectively manage lineage, you must adhere to the following rules:

Do not modify the column headers or their order in the Complete Lineage report or the Lineage template. The following are the column headers: source_name, source_type, target_name, target_type, relation_type, and database_id.
When deleting a row, retain the value in the database_id column and clear all the other column values.
When inserting a row, add all the column values except in the database_id column.
When updating a row, ensure that all the columns have relevant data.

You can also apply:

Feature	Icon	Use
Filter		Used to filter the lineage.
Reload Graph		Assists in reloading graphs.
Manage Graph		To view and manage lineage by adding, modifying, or deleting nodes and their relationships to accurately reflect dependency structures.
Save		Used to save the lineage.
Download		Used to download the file.
Expand		Used to enlarge the screen.

Downloadable Reports

Downloadable reports allow you to export detailed assessment reports of your source data which enables you to gain in-depth insights with ease. To access these assessment reports, click Reports.

Types of Reports

In the Reports section, you can see various types of reports such as Insights and Recommendations, Source Inventory Analysis, and Lineage Analysis reports. Each report type offers detailed information allowing you to explore your assessment results.

Insights and Recommendations

This report provides an in-depth insight into the source input files. It contains the final output including the details of queries, complexity, and so on.

Here, you can see the odi folder along with ODI Assessment.xlsx report.

ODI Assessment.xlsx: This report provides insights about the source inventory. It helps you plan the next frontier of a modern data platform methodically. It includes a report summary, package summary, load plan summary, and a lot more.

This report contains the following information:

Report Summary: Provides information about all the generated artifacts.
Volumetric Info: Lists an aggregated inventory of every source file. For instance, it provides information about the total number of files, packages, mappings, and so on.
Package Details: Provides comprehensive information about packages including information about scenarios, scenario steps, component types, step types, and more.
Procedure Details: Provides information about scenarios with step type as procedure that are defined outside the packages.
Mapping Details: Provides information about scenarios with step type as mapping that are defined outside the packages.
Variable Details: Provides information about scenarios with step type as variable that are defined outside the packages.
Scenario Details: Provides information about scenarios with step type as scenario that are defined outside the packages.
Other Details: Provides information about scenarios that are defined outside the packages with step types other than procedure, mapping, variable, and scenario.
Mapping Summary: Lists all the mappings along with information about the number of join components, expression components, complexity, and so on.
Package Summary: Provides information about the packages in the file. It includes statistical information about the components, mappings, procedures, and more along with the complexity of each package.
Entities: Provides information about the tables along with information about the associated logical schema, table aliases, and more.
Load Plan Details: Provides a summary of load plan components including information about the scenarios, used, and impacted tables, and so on.

Additional ODI Assessments Reports

To access additional assessment report, open the ODI folder.

odi_scenario_highlights.csv: This report provides information about the source files including total number of mappings, scenarios, variables, and more.

odi_sql_highlights.csv: This report provides information about the number of queries segregated by statement types such as delete, merge, insert, and more.

Browse through the output folder to access the queries_detail.csv report.

queries_detail.csv: This report provides information about queries including the used and impacted tables, analyzed status, complexity, and more. If the analyzed status is TRUE, it indicates that the query is analyzed successfully. Conversely, a FAIL status indicates that the query is not analyzed.

Source Inventory Analysis

It is an intermediate report which helps to debug failures or calculate the final report. It includes all the generated CSV reports.

keywordSearch.csv: Provides detailed insights into keyword occurrences— specifically packages—identified across all uploaded source files. It captures information such as the search type, the file where the keyword was found, the line number of occurrence, and the actual line content where the keyword appears.
The system dynamically extracts a specific list of keywords (Packages) from the package_summary.csv assessment output reports. These extracted keywords are then used to perform a case-insensitive search across all uploaded artifacts to help you to understand where specific packages are referenced.

Lineage_Raw.xlsx: This report provides complete dependency details for all nodes. It provides an end-to-end data and process lineage that helps to identify the complete dependency structure and the data flow.

This report contains the following information:

Volumetric Info: Provides volumetric information about the artifact types such as tables, procedures, mappings, variables, packages, and more.
Nodes: Lists all the source and target nodes along with their type. Each node represents a data object in the lineage—such as tables, packages, mappings, etc.—making it easier to trace how data is consumed, transformed, and processed across the data flow.
Dependency (Process): Provides information about the process lineage. It offers detailed visibility into interdependencies between processes—such as mappings, tables, etc.,—helping you understand how they are connected within the data flow.
Dependency (Data): Provides information about the data lineage. It captures detailed table-level—including input tables, output tables, and reference tables—offering end-to-end visibility into how data flows and transforms across the data flow.
Dependency (Data Model): Provides dependency details about the data models. It highlights the end-to-end relationships and dependencies between model elements, helping you understand structure and trace linkages.

To access various outputs such as Entities.csv, Load Plan Details.csv, package summary.csv, and more, navigate through the etl > odi > output folders.

All_Procedures_Details.csv: This report provides details about procedures, including their type, associated procedure steps, procedure objects, and more.

Connection Details.csv: This report contains detailed information about connections, such as their type, Java driver, username, and more.

assessment_unparsed_files.csv: This report provides information about all the unparsed files along with the reason for parsing failure.

Entities.csv: This report provides information about entities along with associated logical schema, table aliases, and more.

Load Plan Details.csv: This report provides comprehensive information about load plans including scenarios, scenario steps, execution orders, used and unused tables, and more.

package summary.csv: This report provides comprehensive information about packages along with information about components, mappings, procedures, complexity, and more.

Lineage Analysis

This section provides lineage-related reports, including entity_link.csv, entity_report.csv, entity_summary.csv, link.csv, script_report.csv reports.

entity_links.csv: This report provides information about how views are connected to entities or tables and how these links extend across multiple levels. Level 1 shows the immediate table to which a view is linked. If that table is further connected to another entity, the next connection appears in Level 2, and so on.

entity_report.csv: This report provides detailed lineage information for each entity within the uploaded source files. It provides a comprehensive list of all entities along with their respective types, identifies the processes or scripts that read from or write to each entity, and includes other dependency details.

entity_summary.csv: This report provides information about each entity along with the type of relation in which it appears—such as procedures, variables, packages, mappings, etc.,—and the operation performed in that context, including Read, Write, or ReadWrite.

link.csv: This report provides information about entities linked to each view.

script_report.csv: This report provides detailed lineage information for each script. It lists all scripts along with their types, reads data from or writes data to a process or script, and includes other dependency details.