Skip Headers

Agile Product Lifecycle Management Agile PLM and EDQP Integration White Paper Release 9.3.3 E49241-01 |
| Contents

Previous |

# Agile Product Lifecycle Management

Agile PLM and EDQP Integration White Paper

Release 9.3.3

E49241-01

October 2013

Note: This document is a supplement to the release Readme file and all other manuals, such as the ”Capacity Planning Guide”. It is intended to be an overview of the other documentation and does not replace the need to read and understand those documents. Errors contained in this document are not commitments to provide features.

## 1 Overview

Poor data quality creates issues for an Agile Product Lifecycle Management (PLM) system. People enter information into Agile PLM in an inconsistent manner with incorrect and missing information. For example from an NEMI study, 40-80% of BOMs arrive at Procurement/Manufacturing with 'problems' including:

- Inconsistent content (missing and cryptic information)

- Inconsistent formats (including languages)

- Invalid or missing supplier information

- Invalid or missing part information

- Obsolete parts

Resolution of these inconsistencies can add days or weeks to new product introduction (and creates additional cost). The solution is to enforce consistent standards on all parts and BOMs using an integrated Data Quality solution. Since 70% of product costs are locked-in early, it's important to deliver integrated data quality from the beginning of the process.

In Agile PLM 9.3.3, Enterprise Data Quality for Product (EDQP) has access to Agile PLM administrative information, as well as the entire Item data set through direct database access. This access allows EDQP to be integrated to Agile PLM in a high performance manner that enables use cases for data validation and enrichment. This integration enables processes to manage the cleanliness of the Agile PLM enterprise product record.

## 2 Configuring EDQP with the Agile PLM Database

The integration of the EDQP solution with Agile PLM 9.3.3 provides an integrated set of capabilities to categorize, standardize, match, govern, validate, correct, and repurpose both existing Agile PLM items and product data being introduced from any source system(s). It provides a set of ready-to-use integration templates, in addition to a robust set of interface functions and procedures that can be leveraged to create customized process flows. These process flows cleanse, standardize, and extract attributes for existing Agile PLM data and provide the foundation for additional data quality capabilities.

The included templates allow a quick path to performing the following functions:

- Metadata Synchronization Template:

  - Automatically generate semantic models from the subclass metadata, including page three attributes with their associated valid values and UOMs if available.

- Create Production Batch to cleanse and revalidate existing items in your Agile PLM system.

- Run cleansing process to cleanse data, standardized attributes, and create standardized description for Agile Production batches.

- Create a standard import Excel worksheet ready for import into Agile PLM.

The Agile PLM integration includes the necessary tables to enable customization of the EDQP process to perform the following functions:

- Develop a full remediation process to enable business users to correct and complete item records.

- Cleanse, extract, and standardize external data for import into Agile PLM.

- Create Semantic Key Cache for duplicate detection and resolution for existing items and externally loaded items,

- Cleanse and standardize Manufacturer Names and Manufacturer Part Numbers.

### 2.1 Getting Started

You must ensure that you have completed the following actions before using the Agile PLM Connector:

1. Install the Agile PLM 9.3.3 server (including the latest patch sets)

2. Ensure that the Oracle database support the Agile PLM 9.3.3 server is setup correctly using the Agile Product Lifecycle Management Database Installation Guide v9.3.3

   - Enable Oracle JVM on the Agile PLM database.

   Note: The connector uses Java stored procedures, pdqplm_plsql.jar, included with Agile database utilities.

   - Run the agile9pdq_setup file

1. Install and configure an EDQP 11g Oracle DataLens Server. For instructions, see Oracle Enterprise Data Quality for Product Data Oracle DataLens Server Installation Guide.

2. Install and configure the EDQP Services for Excel 11g. For instructions, see Oracle Enterprise Data Quality for Product Data Services for Excel Reference Guide.

### 2.2 Installing EDQP templates and configuring the Agile PLM integration

The following section describes the configuration and sample template installation for the EDQP and Agile PLM integration.

### 2.3 Creating the PLM_CONNECTOR Database Connection

You must create a database connection for use by the EDQP DSAs to process data. This database connection is created in the EDQP Oracle DataLens Administration Server using the Oracle DataLens Administration Server web page. For more information, see Oracle Enterprise Data Quality for Product Data Oracle DataLens Server Administration Guide.

1. Browse to the Oracle DataLens Administration Server web page:

   http://hostname:port/datalens

   where hostname is the DNS name or IP address of the Administration Server and port is the listen port on which the Administration Server is listening for requests (port 2229 by default).

2. Log in.

3. Click the Administration tab.

4. From the Configuration section of the menu, click Database Connections.

5. Click the New Connection button.

6. In the Connection Name field, enter APLM_CONNECTOR and Description

   The Connection Name must be this exact name or the process will not work correctly; the DSAs rely on the presence of this exactly named connection.

7. Select Standard as the Connection Type. The form is then populated for you with default information.

8. Select Oracle as the Database Type. The form is then populated for you with default information.

9. Complete the form:

   a. Enter a description for this database connection.

   b. In the Connection field, edit the machine name, Oracle Database Port, and Database SID to match your environment.

   In the following example, these values are set to:

   scl34099.us.oracle.com:1521:tf932b47

   The Oracle Database Port default value is 1521.

   c. Enter the Db User name.

   d. Enter the DB User password.

10. Click the Test button to verify that your EDQP Named Development Database Connection is working properly. The following message should be displayed:

    Test Output Successful

    If the connection does not test successfully, review and revise the options for accuracy, and then retest.

11. Click Save.

The connection information is saved and it is added to the list of database connections to which you are returned.

## 3 Installing EDQP Agile PLM sample files

The Agile PLM Connector Application Server sample files are a collection of DSAs that are delivered with EDQP Agile PLM Connector Patch. You can download the patch from the Oracle support site. The patch number is 16485419. The patch title is "AGILE PLM 9.3.3 AND EDQP INTEGRATION SAMPLE FILES".

You can install them on your Oracle DataLens Server as an example of how you can process your data using the Agile PLM sample data. This sample includes a set of DSAs, each of which is described in this section.

Install the Agile PLM Connector application server sample files:

1. On your Oracle DataLens Administration Server system, log in using the administrator user you established when installing the server in preparation to copy files.

   Note: This step does not refer to the Oracle DataLens Administration Server web page

2. Copy the contents of the patch to the autodeploy directory located on your Oracle DataLens Administration Server WebLogic dls_domain. The DSAs end with a .XML file extension and should be placed in the dsa subdirectory while the data lenses end with a .project file extension and should be placed in the lens subdirectory. By default, these directories are:

   On Linux and UNIX: MW_HOME/user_projects/domains/dls_domain/opdq/autodeploy/dsa

   MW_HOME/user_projects/domains/dls_domain/opdq/autodeploy/lens

   On Windows: MW_HOME\user_projects\domains\dls_domain\opdq\autodeploy\dsa

   MW_HOME\user_projects\domains\dls_domain\opdq\autodeploy\lens

   Note: The preceding directories are the defaults when installing your Oracle DataLens Server; your installation directory may vary.

   This automatically deploys (autodeploys) the DSA sample files to your Oracle DataLens Server. The Oracle DataLens Administration Server polls the autodeploy directory every 10 minutes and attempts to autodeploy the DSA sample files that are placed in the autodeploy parent directory.

3. Log out of the Oracle DataLens Administration Server system.

4. Wait 10 minutes to ensure that the server samples have been autodeployed.

5. Log into the Oracle DataLens Server Administration web page.

   For more information about these web pages, see Oracle Enterprise Data Quality for Product Data Oracle DataLens Server Administration Guide.

6. Verify that the APLM_NAME DSAs have been autodeployed.

## 4 Updating Excel Services

The EDQP Services for Excel has the capability to normalize the view of attribute output name / value column pairs by changing them into a single column with the attribute name as the header value suitable for import into the Agile PLM system. To turn on this capability, the configuration file, dlsforexcel_cfg.xml file must be updated with one line.

1. Close any open Excel workbooks.

2. Navigate to the following directory %AppData%\DataLens\config\.

3. Edit the dlsforexcel_cfg.xml file.

4. Add the following line at the end of the file, and then save it:

<data property="app.ui.feature.groupintoworksheets.mergeattributenamevalue" value="true" />

The file should look similar to the following:

<?xml version="1.0" encoding="UTF-8"?>

<dlsforexcel version="1.0" datetime="11/8/2012 1:53:19 PM">

<properties>

<data property="app.locale" value="en_us" />

<data property="app.servergroup" value="Admin" />

<data property="app.transformserver" value="localhost" />

<data property="app.transformserver.port" value="2229" />

<data property="app.username" value="dlsadmin" />

<data property="app.password" value="xxxxxxxx" />

<data property="app.adminserver" value="localhost" />

<data property="app.adminserver.port" value="2229" />

<data property="app.rememberpassword" value="True" />

<data property="app.usehttps" value="False" />

<data property="app.session.folder.path" value="C:\Users\username\Desktop" />

<data property="app.ui.feature.groupintoworksheets.mergeattributenamevalue" value="true" />

</properties>

</dlsforexcel>

### 4.1 Enterprise DQ for Product DSA Sample Files Overview

There are three main categories of samples DSAs that illustrate the overall integration capabilities between the EDQP and Agile PLM.

- Metadata Import that creates a Semantic Model from Agile PLM metadata extract.

- Creating a new batch from a query of production data.

- Cleansing the batch of production data to produce an importable spreadsheet for import into the Agile PLM server.

APLM_CREATE_SEMANTIC_MODEL

This DSA retrieves the semantic model metadata for a given sub-class from the Agile PLM system. EDQP AutoBuild (an application within Services for Excel) uses this semantic model metadata to build a data lens that is used as a starting point for the cleansing and enrichment process. This DSA is typically run using the Services for Excel to create the data needed by the AutoBuild application. This data includes attributes and sample descriptions.

APLM_CREATE_PRODUCTION_BATCH

This DSA is run to create a new batch of data from the Agile PLM Production tables. This batch is then processed by the Oracle DataLens Server and imported back into the Agile PLM system to update the item records with the cleansed and enriched data.

APLM_CLEANSE_PRODUCTION_BATCH

This DSA is run as the main DSA. It processes an Agile PLM Production batch to cleanse, standardize and extract attributes from the description and the original concatenated attribute values.

APLM_Capacitors

This data lens is used in the APLM_CLEANSE_PRODUCTION_BATCH DSA to cleanse, standardize and extract attributes from the description and the original concatenated attribute values.

Default Entries in DSAs

All DSAs shipped as part of the EDQP Agile PLM Connector have default entries that allow quick configuration.

Default Database Connection

The default database connection name for all DSAs is 'APLM_CONNECTOR'. This database connectionpoints to a fictitious server name with a fictitious user and password that must be configured as part of the initial server configuration, see.

### 4.2 Enriching the Agile PLM Dataset with EDQP

Governance is import to any data quality initiative where standards are put into place that considers the organizational needs to establish the necessary attributes for the business process. Although EDQP can apply standards to any type of data value, it is especially important for form, fit and function attributes on Page 3. The Agile PLM system has page 3 attribute definitions per category (sub class).

### 4.3 Validating Attributes

The first step in the data quality process is to analyze the existing set of parts then to define and set up the Page 3 attributes to enable the standardization, extraction and enrichment of those attributes from the existing part description and attribute values.

Following is an example of the setup for the Page 3 attributes for a Capacitor in a demonstration Agile PLM system:

Notice that there are six attributes: Material, Package, Temperature Characteristics, Tolerance, Capacitance and Voltage. You should validate that they are setup correctly and represent the entire set of attributes required for that part.

There are currently 81 Capacitors in this demo Agile PLM system. The Agile EDQP connector retrieves these items and uses them as sample data when creating a Semantic Model.

### 4.4 Create Semantic Model Input

Once the page three attributes are validated and setup for all of the relevant subclasses, you run the metadata export process called Create Semantic model to download the structure and data into an Excel worksheet using the Agile EDQP Connector integration.

In Excel, you use the Services for Excel Add-In to run the Create Semantic Model process as follows:

1. Start Excel and open a new workbook.

2. Select the Add-Ins tab

3. Select Set Transform Server… from the DataLens Tools Menu to connect to the EDQP DataLens server.

4. Log in to the EDQP server with the correct User ID, Password, Server Name and Port.

5. Accept the default of the Server Group, Admin, by clicking OK.

6. Select Job Options… from the DataLens Tools Menu to select the DSA to process the job on the EDQP DataLens server.

7. Select APLM_CREATE_SEMANTIC_MODEL from the DSA list and click OK.

8. Click the run button to initiate the job processing.

9. Enter the subclass (category) name(s) with a pipe separator between them if more than one of the item (for example Capacitor) to configure the DSA to retrieve the correct subclass names from the Agile PLM system using the Agile EDQP Connector then click OK.

10. After the results are retrieved, click OK to return to the worksheet to see the results.

The data is in a format that is ready for the AutoBuild process to run to automatically create the data lens that contains the data quality rules.

### 4.5 Using Services for Excel AutoBuild to Create a Data Lens

AutoBuild constructs a data lens by examining the available product metadata and sample data. Given sufficient information, AutoBuild can accomplish the following.

· Construct a full Item Definition hierarchy, complete with required, scoring, and optional attributes.

- Construct rich term and phrase recognition rules.

- Provide an initial set of classification rules.

AutoBuild offers a familiar, easy-to-use graphical wizard interface that guides you through the process from start to finish.

The Autobuild process extracts data from the APLM schema and uses it to create a data lens that will cleanse, standardize, classify and extract attributes from the Agile PLM data.

1. Click the AutoBuild button to initiate the AutoBuild processing to create the data lens from the Agile PLM data.

2. Select Generate a new DataLens radio button in Step 1 of the AutoBuild wizard. It automatically populates all of the values for this step and the remainder of the wizard steps because the columns are a special format that AutoBuild is designed to recognize this is built using a connector template.

3. Click on Next twice to move to the last step (Step 4)

4. Name the data lens (for example Agile_Capacitor), and then click Finish to create the data lens.

   The process runs and a message is disaplayed that it successfully created the data lens.

5. Click OK to exit the dialogue. Your initial AutoBuild lens is complete and you're ready to review and refine the data lens.

### 4.6 Reviewing and Refining the Data Lens

Review the data lens to confirm proper creation. The data lens should have the correct Item Definitions, Attributes, Phrases, and Terminology as defined by the AutoBuild process.

You should review the data lens and add any undefined terminology to complete the process. For example, the following terms "CERAMIC" AND "SMD" not being recognized by the "Capacitor" Item Definition thus they are "Unknown".

"CERAMIC" is not the recognized as a variant of Agile standard "CER". You would have to update the data lens and add the variant to the term "CER".

In the case of "SMD", it was not setup on page 3 originally. If you wanted to include this attribute, you would go back to the Agile PLM to setup the page 3 attributes for Mounting (SMD) for Surface Mount then return to step 4.2 and either recreate or update the data lens using AutoBuild to reflect the changes.

After making the adjustments, the data lens will recognize the terms. The example data lens, APLM_Capacitors, included with the templates, is a finalized data lens as in the following.

The update of data lens to include updates to new knowledge rules and the best practices of creating knowledge are outside the scope of this white paper. For more information on how to work with data lenses, please see Oracle Enterprise Data Quality for Product Data Knowledge Studio Reference Guide

### 4.7 Cleansing and Enriching Agile Production Data

Once you have created the data lens using one or more subclasses, you can now use the data lens to cleanse, extract, and standardize Agile PLM data.

### 4.8 Create Production Batch

The first step in the process is to create a production batch to process. The production batch runs database queries to retrieve data for particular categories (subclasses) and combines them into a batch for processing by the EDQP software. Since the Agile PLM categories are flat, the connector allows multiple categories to be retrieved in a single batch using a semi-colon (;) as a separator. For example "Capacitor;Resistor".

1. Start Excel and go to the Add-Ins tab.

2. Select the Transform server as shown in step 4.2.

3. Select Job Options… from the DataLens Tools Menu to select theDSA to process the job on the EDQP Oracle DataLens Server.

4. Select APLM_CREATE_PRODUCTION_BATCH from the DSA list, and then click OK.

5. Click the run button to initiate the job processing.

6. Enter the subclass (category) name(s) (for Example Capacitor;Resistor) with a semi-colon separator between them followed by a pipe separator and enter the last update date in the following format:

   YYYY-MON-DD HH24:MI:SS e.g. (2011-JAN-01 00:00:00)

   This date parameter enables you to do incremental processing.

7. The input values let the DSA know which ones it should pull down from the Agile PLM system using the Agile EDQP Connector then click OK.

8. After the results are retrieved, click OK to return to the worksheet to see the results.

### 4.9 Cleanse Production Batch

1. Select the 20_Batch_Creation_Details worksheet created in the previous step to begin the process of cleansing the production batch.

2. Select Job Options… from the DataLens Tools Menu to select the Data Service Application (DSA) to process the job on the EDQP DataLens server.

3. Select APLM_CLEANSE_PRODUCTION_BATCH from the DSA drop down and click OK. The APLM_CLEANSE_PRODUCTION_BATCH DSA includes an example data lenses called APLM_Capacitors, which is fully refined version.

4. Click the run button to initiate the job processing.

5. Enter the Job_id (for Example 303) from the results of APLM_CREATE_PRODUCTION_BATCH worksheet run then click OK.

6. After the results are retrieved, click OK to return to theworksheet to see the results.

   The attribute results are in name value pairs, which are not in the correct format for import into Agile.

7. Right-click on a cell in the spreadsheet, select DataLens Services… and then Group Records into Worksheets to normalize the attribute name / value column pairs.

8. Enter the column letter (. F) that represents the beginning of the attribute information.

9. Enter a filename and designate a location for the new spreadsheet then click Save. A message will display letting you know the spreadsheet has been created,

10. Click OK. The resulting normalized worksheet will have attribute headers and row values.

### 4.10 Importing Cleansed Data back into Agile

Once the Excel worksheet is created, the standard Agile PLM Import process should be used to load the results back into Agile PLM. You must develop an overall data cleansing processes that takes into account their unique requirements.

### 4.11 Basic Import Process

Following is an example of the process to import the content into Agile PLM without any additional ECO process.

1. Start the Import Wizard and load the Excel file.

2. Select the rightworksheet.

3. Import the Item data.

4. Select the import without ECO and Map option on the next screen.

5. Map the attributes to be imported.

6. Complete the import.

This sequence is illustrated in the following images:

### 4.12 4.6.2 Additional process considerations

When designing the overall cleansing process, user privileges and attribute configuration will determine who can perform the import and when it can be done. For example, if some of the data is in Change Controlled Attributes, then a Change Order or Manufacturer Order will be required to import the data and then complete the changes. Only a user who has modify rights to all the data will be able to successfully complete an import.

A company may also want to prenotify key constituent groups before completing an import that changes a large amount of data. With this type of communication, users will not be surprised to see new or altered attributes. If you are updating descriptions across 3,000 capacitors, all users will need to be informed so that they can perform searches and other tasks without becoming frustrated with the data changes.

Another issue is synchronization. When changes are made in Agile PLM, they also need to be communicated to other enterprise systems, like ERP. How this communication occurs will be influenced by the number of changes being made and the nature of the changes. If Agile only data is changed, like Tolerance, then there will not need to be any communication. However, if 50,000 descriptions are updated, then the communication method of that scope of change should be considered. Can the direct ERP integration process a change of that many descriptions? Should ERP also be updated at the same time as Agile?

As with any process that impacts your production data, it is always wise to test and validate the process in a Development or QA system before proceeding to a large production import. Once the overall process is stable and well understood, then proceed to resolve data issues in Agile PLM as an ongoing practice.

In summary, spend time planning the overall data change process, including technical details, communication, integration, and testing, as you implement this new capability to leverage EDQP with Agile PLM.

Copyright 2013, Oracle and/or its affiliates. All Rights Reserved.

This document is provided for information purposes only, and the contents hereof are subjected to change without notice. This document is not warranted to be error-free, nor is it subjected to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document; and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose without prior written permission from Oracle.

### 4.13 Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at `http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc`.

Access to Oracle Support

Oracle customers have access to electronic support through My Oracle Support. For information, visit `http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info` or visit `http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs` if you are hearing impaired.

Copyright Page

E49241-01

Copyright © 2013 Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.