Michal Adamczyk
Oracle E-Business Suite (EBS) database is known for its large size and complexity due to the comprehensive nature of the integrated business applications it supports. Dataedo helps make sense of the complex Oracle E-Business Suite (EBS) database by providing documentation and insights into its structure and relationships.
Catalog and Documentation
Dataedo leverages the Oracle internal data dictionary to import metadata, enabling users to efficiently capture and utilize essential information about the Oracle E-Business Suite (EBS) database.
Users can select an EBS application (for example Human Resources) that interests them and import parts of the EBS database that are used by it. Dataedo imports tables and views with their columns which can be further used to create ER diagrams based on imported table relationships (foreign keys) that help to explore the EBS database structure. Apart from that, SQL functions, procedures and packages with scripts that define them are imported. Furthermore, the EBS connector supports importing sequences.
Users will be also among others able to:
- run Data Classification on the EBS database in the repository to search for columns containing potentially sensitive data.
- change all descriptions in Dataedo Desktop and Dataedo Portal
- link a Business Glossary term to any documented EBS object
- build Lookups for columns and views and feed them with distinct values from a column
- run Data Profiling for a table or view and then save selected data in the repository.
Required permissions
To import all mentioned metadata user must have access to one of the system database schemas or schema named APPS. This schema has access to the complete Oracle E-Business Suite data model and stores all product code objects (for example views). For further information about its role visit: Oracle EBS Database Features. To learn more about EBS database schemas visit: Database Schemas Found in Oracle E-Business Suite.
Connecting to Oracle EBS
To connect to Oracle Instance wherein the EBS database is located you need to first click the Add button on the left upper corner and choose New connection.
From the sources choose Oracle EBS.
Now data necessary to establish a connection between Dataedo and the Oracle EBS database that resides on the Oracle instance must be provided. Two connection types are available: Direct(visible on screen below) or Oracle wallet. When connecting to the database directly specified must be:
- Host - hostname or address where a database is on, for example, ebsserver, ebsserver.ourdomain.com or 192.168.0.1.
- Instance identifier (Service name or SID) - Oracle instance Service name
- Port - Oracle instance port
In the case of Oracle wallet connection type user needs to specify the:
- Connection file - location of the wallet file
- Service - service name
Independently of choosing the connection type specified must be:
- User - name of the user (see Required access)
- Password - user password
- Application - Prefix or prefixes of applications divided by a comma whose metadata are going to be imported (click the three-dot icon to see a list of available apps)
- Timeout (s) - number of seconds after which the connection attempt will be interrupted
If dependencies between objects are to be imported, Import dependencies checkbox must be checked.
After clicking Connect Dataedo will start retrieving database objects and when it finishes it will display a window that allows to choose objects to import. Checking the Advanced filters checkbox enables to define more complex filters.
Confirm which objects are to be imported by clicking Next. You will see a window that allows changing the default name of the documentation under which it will be visible in the Dataedo repository. This name can be changed later.
Click Import to start the import process. Wait until the import process is completed.
Close the import window using Finish button. Your EBS database structure has been imported to new documentation in the repository.
Connector specification
Imported metadata
| Imported | Editable | |
|---|---|---|
| Tables | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Data types | ✅ | |
| Nullability | ✅ | |
| Column comments | ✅ | ✅ |
| Table comments | ✅ | ✅ |
| Foreign keys | ✅ | ✅ |
| Primary keys | ✅ | ✅ |
| Unique keys | ✅ | ✅ |
| Triggers | ✅ | |
| Views, Materialized Views | ✅ | ✅ |
| Script | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Data types | ✅ | |
| Column comments | ✅ | ✅ |
| View comments | ✅ | ✅ |
| Functions, Procedures, Packages | ✅ | ✅ |
| Script | ✅ | ✅ |
| Parameters | ✅ | |
| Script | ✅ | ✅ |
| Input arguments | ✅ | |
| Output results | ✅ | |
| Dependencies | ✅ | ✅ |
Supported features
| Feature | Supported |
|---|---|
| Data profiling | ✅ |
| Data classification | ✅ |
| Data lineage | ✅ |
| Reference data (import lookups) | ✅ |
| Importing from DDL | |
| Generating DDL | ✅ |
| FK relationship tester | ✅ |
Data profiling
| Profile | Support |
|---|---|
| Table row count | ✅ |
| Table sample data | ✅ |
| Column distribution (unique, non-unique, null, empty values) | ✅ |
| Min, max values | ✅ |
| Average | ✅ |
| Variance | ✅ |
| Standard deviation | ✅ |
| Min-max span | ✅ |
| Number of distinct values | ✅ |
| Top 10/100/1000 values | ✅ |
| 10 random values | ✅ |
Read more about profiling in a Data Profiling documentation.
Data Lineage
| Source | Method |
|---|---|
| Views - object level | From dependencies |
| Views - object level | From SQL parsing |
| Views - column level | From SQL parsing |
Known issues and limitations
- Because packages are parsed to get functions and procedures from them, import may take a long time when the application has many of them
- Some tables may be imported twice with different schemas. Currently, we are unable to determine which one is correct.