This is the forth and final blog in a series about Agile 9.3.2’s integration with Oracle’s Enterprise Data Quality for Product Data (EDQP), which walks through the Agile PLM 9.3.2 and EDQP Integration White Paper to explore the integration’s setup and capabilities. This edition will focus on section 4 of the whitepaper
Section 4: Enriching the Agile PLM Dataset with EDQP
The main objectives of section 4 are to:
- Validate the necessary attributes exist in the database
- Set up the semantic model
- Use AutoBuild to create a data lens & evaluate in Knowledge Studio
- Enrich the Agile data
- Import the data back into Agile
Prior to starting the series, Agile PLM 9.3.2 and EDQP’s Admin and Transform servers were installed, so these will not be covered. An entire blog series could be written on each product and it is beyond the intended scope of the series.
Validate the necessary attributes exist in the database
This blog, and the whitepaper, are based on the demo database provided to Oracle business partners. Prior to writing this blog I downloaded the demo database, upgraded it to 9.3.2 and reran the SQL scripts as outlined in the prior blog. This step verifies that the content in the database is as expected:
- Log into the Agile JavaClient
- Navigate to Admin tab| Settings | Data Settings | Classes and open the Capacitors subclass
- Click the User Interface tab
- Double click the Capacitor Attributes (page 3) row
- Click the Attributes: Page Three tab
- Verify the following attributes are defined: Material, Package, Temperature Characteristics, Tolerance, Capacitanance and Voltage.
To eliminate possible issues, I changed the name on a couple of my attributes to ensure they aligned with the documentation as they contained additional text. The picture below shows what I started with and unlike the whitepaper, I only had 52 capacitors – but that should be enough to test with.
If you do not have the demo databae the whitepaper has a screenshot that shows the APIName and type so you should be able to reproduce the subclass structure without any issues.
Set up the semantic model
Earlier in the series, we installed the Excel add-in for EDQP; now its time to use it.
- Start Excel. By default a new workbook should show but if for some reason yours does not, create a new workbook
- Navigate to the Add-Ins tab | DataLens Tools dropdown | Set Transform Server
- Log into EDQP and accept the server group
- Select Job Options from the DataLens Tools dropdown
- Select the APLM_CREATE_SEMANTIC_MODEL from the DSA list, click ok.
- Run the Job and enter Capacitor in the popup dialog to select all the objects that are of the type Capacitor. At this point I received errors stating that a database connection could not be established. The documentation was actually incorrect. The database connection name set up prior should be APLM_CONNECTOR, not PLM_CONNECTOR!
At this point, the Agile data will be populated in Excel and is ready for AutoBuild
Create a DataLens using AutoBuild & evaluation it in Knowledge Studio
AutoBuild is a great feature to speed the creation of a DataLens. It can build out terms, phrases and put together some classification rules. The usage and capability of the Knowledge Studio is outside of the scope of the blog but it is a very interesting tool and AutoBuild helps one get started.
- Next to the DataLens Tools dropdown in Excel is a button for AutoBuild. With results above loaded in the workbook, click the button
- A series of dialog boxes will show. In this exercise, we only care about the first and last. On the first dialog, select the “Generate a new DataLens” radio button and click Next to transition through the dialogs until a name can be entered for the data lens. Give the lens a name – it looks like you must select a name from the dropdown, but it is a combo box that will accept user input. Click finish
- In Knowledge Studio, you will notice some terms in orange and some in white. The terms in white are recognized and the ones in orange are not.
- Watch the video and see how to add an alias to CER so that ceramic becomes a recognized term. There is a lot of capability built into Knowledge Studio and recognizing terms and phrases is just the beginning. After come the standardization – it is a very powerful tool!
The exercise is to help understand how one could start manipulating Agile data. The example data lens APLM_Capacitors was deployed in the earlier blogs, so this particular lens will not serve a purpose.
Enrich the Agile data
There are several steps to enriching the Agile data: extract the data, update it, and prepare it for import. Here are the steps:
- Extracting the data
- Open Job Options
- Select the APLM_CREATE_PRODUCTION_BATCH DSA, click ok
- You are prompted for the subclass(es) and the last update date. I entered “Capacitor|200-JAN-01 00:00:00” and clicked ok
- Run the DSA and Excel will extract Agile data and create a tab called 20-Batch_Creation_Details
- Make a note of the value in the Job Id column, you will need this for the next step
- Updating the data
- With the newly extracted data selected (tab 20-Batch_Creation_Details), open Job Obtions
- Select the APLM_CLEANSE_PRODUCTION_BATCH DSA
- Run the job and enter the Id from step 1.5 above. This is where the custom data lens gets called to transform the data. The gap in information in the whitepaper is that a mature version of the AutoBuild lens created above could eventually make its way to be called in this step rather than the supplied lens (see picture below)
- The result of the job are some new tabs in the worksheet. If you look at the new content you will see that it is comprised of name-value pairs in the form of adjacent columns rather than having an attribute name as the column and its value as the cell value. Luckily there is a utility to correct this.
- Preparing for import
- After transformation, the last step is to prepare the data to be reimported into Agile. Click on the new worksheet created by the cleansing DSA
- Notice that around column F is where attributes start being displayed as name-value pairs – this is where we will have the Add-In start its correction
- Right click on any cell and select DataLens Services | Group Records into Worksheets
- Enter the column F
- Select a filename for the new content
- Open the new spreadsheet and notice that attribute names are now column headings. This data should be ready for import!
Import the data back into Agile
I decided not to go into Agile import because it is such a common thing to do, but at this stage, the hard part is done. Refer to the Agile Import/Export guide if you have questions on this step.
Final Thoughts on the Integration
Overall I really like what I have seen of EDQP. It is a niche in and of itself and even though I have worked with it before, I am sure I have only scratched the surface of its capabilities.
When I titled this blog “Discovery” I truely meant it. I did not know where the series would lead and did very little homework first. After going through the document I am left feeling satisfied but a little empty inside. I really had hopes for a tighter coupling with EDQP, such as leveraging the extension framework for real-time validation or updating of attributes, but the capability isn’t there. Instead we are left to either write our own solution or use batch processing. In the mean time, I am thankful for the tools that Oracle did provide and am still hopeful that future releases will have a tighter integration.
What are your thoughts?
Here is the video for section 4 of the whitepaper:
- Agile documentation: http://docs.oracle.com/cd/E28664_10/otn/docset.html
- EDQP documentation: http://docs.oracle.com/cd/E35636_01/index.htm
- Agile website: http://www.oracle.com/us/products/applications/agile/overview/index.html
- EDQ website: http://www.oracle.com/us/products/middleware/data-integration/enterprise-data-quality/overview/index.html