Encompass Agile/ODM DB Schema Connector

Overview:

Data can be extracted multiple ways from Oracle's Agile PLM application. One way for data to be extracted is a direct connection to the underlying agile database or an operational data mart (ODM) provided by Oracle's Product Lifecycle Analytics (OPLA).  This connector utilizes the standard database schema provided by Oracle for these applications.  The Direct Database Connector is a full batch extractor. It provides high-performance extraction of Agile PLM parts, Docs links, BOMs, changes including back revisions. The connector is designed to provide high performance for one-time initial extracts or for periodic nightly refreshes. The connector is particularly useful for very large agile implementations or implementations where Agile is managing a large number of changing items. The connect is also useful for environments where Application server loading is a problem, as the connector will not interact with the Agile Application server. In Particular large extracts that require interaction with the BOM structures are known to cause heavy loading on the Agile application server. The Direct Database Connector in conjunction with Encompass understand the the Agile data model, in particular the BOM data models. This allows the connector to execute a direct Table Export for relevant information, requiring minimal decode and joining logic.

Prerequisites

  • Perception Software Encompass 2.x +
  • Encompass supporting features for Parts, Docs, BOMs and changes 
  • Oracle Agile Agile 9.3.2 – 9.3.3
  • Oracle Agile OPLA module
  • Local disk space to accommodate temporary storage during extraction
  • Groovy and Java 1.6+ installed and in the system class-path
  • Encompass Ruby/Rake based ETL installed and configured.

Obtaining the Connector

Please contact Perception Software or your sales representative to determine if the connector is appropriate fit for your organization, and how it must be licensed for your your Encompass solution.

The connector feature is a capability to the existing Agile PLM Webservice based connector, if you already own the Agile PLM Webservice connector this capability is included as part of your maintenance contract with Perception Software.

Installing or Updating the connector

For a new installation or upgrading an existing solution the following steps are required to install the connector.

1) Obtain the installation archive from Perception Software – this generally will be provided as a download site or provided by as part of the installation services.

2)  Extract the connector deliverables into the encompass ETL directory – it is recommended the connector assets be placed here, it recommended to place the connector into the "ETL/Pipes" directory of your implementation. the exact location may vary based on deployment. This will result in a standard directory called "agile_database_connector"

$ENCOMPASS_HOME/ETL/pipes/agile_database_connector 

When upgrading a connector it is only required that you maintain the agile-config.xml, and follow any provided instructions to modify the config for new features and changes.

General Operation:

The connector process consists of three separate processes: Query Generation, Extraction, and Post Processing.  Once configuration has been properly completed, the connector will generate a set of SQL queries to execute during the extraction process. This provides an input compatible with the Encompass ETL process. 

Configuring the connector

The connector comes with a preset configuration to extract item, bom, manufacturer part and change objects from the Agile database schema. Simple modifications to this configuration can be made to tailor the connector to a specific Agile instance (multilist fields, flex fields, set data tracing values, etc.).  This is also where the database connection details reside as well.

To Update this configuration edit the following file assuming installation location mentioned above:

$ENCOMPASS_HOME/ETL/pipes/agile_database_connector/config/agile-config.xml

you may utilize the liked sample_config.xml as a starting point for this file.

Critical items to configure:

Database-properties: in this section enter database connection string credentials for the ODM that the connector will attach to,

<connectionDetails>
   <username>odm_test</username>
   <password>odm_test</password>
   <connectionUrl>jdbc:oracle:thin:@host:port:sid</connectionUrl>
<connectionType>Oracle</connectionType>
</connectionDetails>

Under normal circumstances, the <TemplateQueries> section will not need edited. These map to the standard Oracle Agile/OPLA schema.  Please contact Perception Software for changes to this section as this typically indicates a non-standard Agile/OPLA implementation.

At a high level, the configured connector reads Agile's data dictionary, attribute, and metadata tables to determine important information such as subclasses, active attributes, and unique attribute names. Once the information is gathered, the connector constructs the specific extraction queries necessary for the given Agile instance. Attached is a sample agile-config.xml file.

Configuring MultiLists:

It is required to inform the connector of fields that are to use multi-lists. For each subclass  simply list the agile API name for the multilists being used.

<multilistFields id="doc">
   <field>ATTRIBUTE1</field>
   <field>ATTRIBUTE2</field>
   <field>ATTRIBUTE3</field>
   <field>ATTRIBUTE4</field>
</multilistFields>
<multilistFields id="part">
   <field>ATTRIBUTE1</field> 
   <field>ATTRIBUTE2</field>
   <field>ATTRIBUTE3</field>
   <field>ATTRIBUTE4</field>
</multilistFields>

Extraction:

The extraction process is the most simple of the three. It simply executes the generated queries and writes their results to their respective output files.  The standard connector ships with a convenience script as template, that may be integrated into the ETL process.  This script "run.sh" or "run.bat" located in the "pipes" directory, will generate all of the required SQL queries, attach to the database and extract all the required data. This is is only an example, as  this file script is integrated into the site specific ETL flow. The output of the extraction process will create and maintain a "results" directory, located in the ENCOMPASS_HOME/ETL/pipes/agile_database_connector/ location. The resulting output will be the intermediary CSV, of the SQL queries. The connector takes care of managing the contents of this directory.

Information on the Post Processing:

No user actions or configuration are required for the post processing this is integrated into the connector. However for clarity Post processing maps and consolidates the extracted data. Also multilist fields are mapped to their respective values. Mapping the multilist fields during a post processing step is more efficient than doing it in SQL, as well as takes the load off of the database that others may be using. The result of the post processing is a consolidated "agile_data.xml", located in the $ENCOMPASS_HOME/ETL/pipes/agile_database_connector/work. The connector fully manages the work directory.

Incorporation into existing ETL process

The Agile_data.xml, is the input to the next stage of the ETL processing which is assumed to be configured. This stage typically is a the "preferential_merge" transformation stage in most encompass deployments, however this process can be highly site specific. 

Logging and trouble shooting:

The connector utilizes the existing ETL logging, please review the ETL output logs for troubleshooting.   Errors and warnings will be logged against the specific ETL task (dependent on your deployment). Common issues:

Database connection errors (jdbc): most likely your connection strings in the agile-config.xml is incorrect, please verify  your db credentials.

Tables do not exist errors: This would indicate a non-standard OPLA ODM configuration. Please contact Perception Software for assistance.

Low-Temp-Space: For very large agile implementations Oracle may be limited on temp-space. This will need to be increased by your DBA.

Execution Time: Its not possible to fully predict execution time as it varies based on oracle infrastructure, databases etc. However the connector on "large" agile database generally can complete in a few hours. Excessive extraction time may require further database tuning with your DBA.

 

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.
Powered by Zendesk