Copyrights and Trademarks

Agile Product Lifecycle Management for Process

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 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 which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software 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 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.

Contents

Overview... 5

Denormalization Process Overview.. 5

Near Real-Time Denormalization. 5

Batch Denormalization. 5

Available Extended Attributes for Denormalization. 6

Extended Attribute Denormalization Tables.. 6

Denormalization Metadata. 6

Denormalization Data Tables. 7

Numeric and Calculated Numeric EA Types. 7

Free Text, Qualitative, and Qualitative Lookup EA Types. 8

Date EA Types. 9

Boolean EA Types. 9

Quantitative Range EA Types. 10

Quantitative Tolerance EA Types. 10

Denormalization Logging.. 11

Near Real Time Denorm Status. 11

Denormalized Data Formats.. 11

Numeric and Date Nulls. 11

Text Based Values. 12

Base Unit of Measure Values. 12

Qualitative Lookup Limitation. 12

Installation.. 12

Installing the Scripts. 12

Near Real Time Denorm Configuration and Installation. 13

Execution.. 16

Execution Script. 16

Performance Considerations. 17

Overview

Extended Attribute Denormalization (EA Denorm) is a feature that provides the ability to convert the internal data storage of Extended Attributes into data structures that are easier to understand and report against while providing improved query performance. The EA Denorm process is required when using Oracle Product Lifecycle Analytics (OPLA).

The EA Denorm process pulls data for all activated (Active, Archive, and Inactive) Extended Attributes from Specifications (or other business objects, such as Sourcing Approvals, NPD Projects, etc), and populates that data into a new set of denormalization tables. These denormalization tables include additional information such as attribute IDs, custom section IDs, etc, that make the data easier to query against for reporting purposes.

Denormalization Process Overview

Near Real-Time Denormalization

When a business object (such as a GSM Specification) is saved, the Near Real-Time Denormalization (NRTD) process will determine if any of the extended attributes in the simple EA listing, or any distinct extended attributes within custom sections on that item have been added, deleted, or modified. If so, a extended attribute denormalization request is added to a queue, which is then processed by a Service on the Remoting Container application. This Service will trigger denormalization for each request in the queue, which processes only that specific business object. Since the denormalization is only occurring for small sets of data, the NRTD Service can run on an ongoing basis, keeping the denormalized data very closely in synch with the live data.

The frequency of the NRTD Service can be configured, along with options for logging, email notifications if any errors occur, and more.

Batch Denormalization

Prior to the Near Real Time Denormalization, denormalization was processed in a scheduled batch manner; all denormalized extended attributes for all business objects that have been updated since the last batch denormalization were processed together. While this process should no longer be required if using NRTD, there may be one-off instances where clients still wish to use this. As such, it is still available for use.

The batch EA Denorm process consists of running a provided stored procedure on a recurring basis, which processes each Extended Attribute set up for Denormalization, extracts the relevant data from it (where it is saved on a Specification, Sourcing Approval, etc) and populates that data into the relevant Extended Attribute denormalization table(s). This stored procedure may be run as an automated process on a predetermined interval (usually nightly) to extract data from Extended Attributes and Custom Sections, and populate it into the new denormalization tables. A log entry may optionally be written to a new database table to record the execution results of the Extended Attribute denormalization process.

Available Extended Attributes for Denormalization

Extended Attributes (EAs) are available for Denormalization if the Extended Attribute Template has been activated (Status is Active, Archived, or Inactive) and the Class is marked as:

1. Simple , or

2. Custom Section –EA Templates within a Custom Section must be marked as Distinct to be included in the EA Denorm. Non-distinct EAs in a Custom Section will not be included.

Extended Attribute Denormalization Tables

The Denorm_EA_Templates metadata table will be used to control which extended attributes will be denormalized and drive the denormalization process. This table will be populated automatically by the main stored procedure included in the extended attribute denorm package.

Individual Denormalization tables will be created for the various data types supported by the existing Extended Attribute types.

Denormalization Metadata

Extended Attribute denormalization metadata is stored in the DENORM_EA_TEMPLATES table. This table is used by the denorm process and is not needed when querying against the detail tables.

Column	DataType	Comments
ID	varchar(36)	Unique GUID
FKEATEMPLATE	char(40)	Foreign key to the EA Template (CommonExtendedAttributeType table)
STATUS	int	Denorm status -1, 0, or 1
LASTDENORMTIMESTAMP	datetime	Time of last Denormalization of this EA Template (used by the Batch Denorm process)
AttributeType	varchar(40)	extended attribute type
IsDistinct	bit
UOMCategory	char(2)
AttributeID	varchar(24)
DexVersion	float

Extended Attribute Types that are to be denormalized will be automatically added to this table using the provided SQL scripts. The LastDenormTimestamp value will be set for each EA when the denormalization process runs.

Denormalization Data Tables

New Denormalization tables are created for the various data types supported by the existing Extended Attribute types. Different EA Types with a common base data type, such as Text, will be stored in the same table. EA Types that contain a Unit of Measure field (UOM) will be denormalized to also include the Base UOM and base numeric value(s).

All Denorm tables will contain the following columns:

Column	Comments
ID	Unique GUID
fkOwner	Foreign key to the application object that owns this Extended Attribute instance (e.g., GSM Specifications, Sourcing Approval, etc.)
fkExtendedAttributeTemplateID	Foreign key to the EA TemplateID - same as the Denorm_EA_Templates
fkExtendedAttributeInstanceID	Foreign key to the Individual Extended Attribute source table entry
AttributeID	The attributeID value from the CommonExtendedAttributeType table
fkSectionTemplateID	Foreign key to the Custom Section Template (commonEASectionTemplate), if populated from a Custom Section
fkSectionInstanceID	Foreign key to the Custom Section instance (commonEASectionInstance), if populated from a Custom Section
SectionID	The Custom Section’s ID value from the commonEASectionTemplate table, if populated from a Custom Section
IsDistinct	Boolean identifier indicating if this EA has the IsDistinct tag

The following denormalization tables will be used based on the extended attribute type:

Numeric and Calculated Numeric EA Types

DENORM_EA_NUMERIC

Column	DataType	Length
ID	varchar	36
FKOWNER	varchar	40
FKEXTENDEDATTRIBUTETEMPLATEID	varchar	40
FKEXTENDEDATTRIBUTEINSTANCEID	varchar	40
ATTRIBUTEID	varchar	24
FKSECTIONTEMPLATEID	varchar	40
FKSECTIONINSTANCEID	varchar	40
SECTIONID	varchar	24
ISDISTINCT	bit	1
VALUE	float
UOM	varchar	10
VALUEBASE	float
UOMBASE	varchar	10
ISCALCULATED	bit
PRECISION	int

Free Text, Qualitative, and Qualitative Lookup EA Types

Free Text, Qualitative, Qualitative Lookup, and Calculated Text EAs will be denormalized into the following table. Note that multi-select items will be denormalized in both of the following ways:

1. As a comma-delimited list into the Value column

2. As individual rows into the Denorm_EA_Text_Multi table

DENORM_EA_TEXT

Column	DataType	Length
ID	varchar	36
FKOWNER	varchar	40
FKEXTENDEDATTRIBUTETEMPLATEID	varchar	40
FKEXTENDEDATTRIBUTEINSTANCEID	varchar	40
ATTRIBUTEID	varchar	24
FKSECTIONTEMPLATEID	varchar	40
FKSECTIONINSTANCEID	varchar	40
SECTIONID	varchar	24
ISDISTINCT	bit	1
ATTRIBUTETYPE	varchar	40
ISMULTI	bit	1
VALUE	varchar	8000

DENORM_EA_TEXT_MULTI

Column	DataType	Length
ID	varchar	36
FKDENORM_EA_TEXT_ID	varchar	36
VALUE	varchar	500
EXTERNALID	varchar	80
SORTORDER	int

Date EA Types

Denorm_EA_Date

Column	DataType	Length
ID	varchar	36
FKOWNER	varchar	40
FKEXTENDEDATTRIBUTETEMPLATEID	varchar	40
FKEXTENDEDATTRIBUTEINSTANCEID	varchar	40
ATTRIBUTEID	varchar	24
FKSECTIONTEMPLATEID	varchar	40
FKSECTIONINSTANCEID	varchar	40
SECTIONID	varchar	24
ISDISTINCT	bit	1
VALUE	datetime

Boolean EA Types

Boolean and Calculated Boolean EA Types will be denormalized into the following tables

DENORM_EA_BOOLEAN

Column	DataType	Length
ID	varchar	36
FKOWNER	varchar	40
FKEXTENDEDATTRIBUTETEMPLATEID	varchar	40
FKEXTENDEDATTRIBUTEINSTANCEID	varchar	40
ATTRIBUTEID	varchar	24
FKSECTIONTEMPLATEID	varchar	40
FKSECTIONINSTANCEID	varchar	40
SECTIONID	varchar	24
ISDISTINCT	bit	1
VALUE	bit	1	1=true, 0=false, NULL = not set

Quantitative Range EA Types

DENORM_EA_QUANTITATIVERANGE

Column	DataType	Length
ID	varchar	36
FKOWNER	varchar	40
FKEXTENDEDATTRIBUTETEMPLATEID	varchar	40
FKEXTENDEDATTRIBUTEINSTANCEID	varchar	40
ATTRIBUTEID	varchar	24
FKSECTIONTEMPLATEID	varchar	40
FKSECTIONINSTANCEID	varchar	40
SECTIONID	varchar	24
ISDISTINCT	bit	1
TARGET	float
MIN	float
MAX	float
UOM	varchar	32
TARGETBASE	float
MINBASE	float
MAXBASE	float
UOMBASE	varchar	32
TARGETPRECISION	int
MINPRECISION	int
MAXPRECISION	int

Quantitative Tolerance EA Types

Denorm_EA_QuantitativeTolerance

Column	DataType	Length
ID	varchar	36
FKOWNER	varchar	40
FKEXTENDEDATTRIBUTETEMPLATEID	varchar	40
FKEXTENDEDATTRIBUTEINSTANCEID	varchar	40
ATTRIBUTEID	varchar	24
FKSECTIONTEMPLATEID	varchar	40
FKSECTIONINSTANCEID	varchar	40
SECTIONID	varchar	24
ISDISTINCT	bit	1
VALUE	float
TOLERANCE	float
UOM	varchar	32
VALUEBASE	float
UOMBASE	varchar	32
VALUEPRECISION	int
TOLERANCEPRECISION	int

Denormalization Logging

Extended Attribute denormalization results may be logged to the DENORM_EA_LOG table. Each Extended Attribute Type is denormalized separately, and an entry indicating the number of records updated and inserted will be stored in this log table, along with a timestamp.

Any errors that occur will be logged with a value of “EA Denorm Error” in the MODULE column.

Near Real Time Denorm Status

The Real Time Denorm process will log basic informational messages, as well as error messages, to a rolling file log in the Denorm_ExtendedAttributes directory within the default Logs directory. A new log file will be generated each day.

The MaxLogFilesToKeep_EA configuration setting controls the number of daily log files that will be kept. To disable this file logging you must set the MaxLogFilesToKeep_EA value to "0" (zero). Note that errors will still be emailed and an entry in the ProdikaCommon Event Log will also be added. See the readme.txt file in Utilities\DenormServices for more details.

Denormalized Data Formats

This section describes how the Extended Attribute data is denormalized for different Extended Attribute types and values

Numeric and Date Nulls

There are two different ways that non-existent data is stored for numeric values and date-based extended attribute values.

NULL: A database NULL is stored when a custom section cell (row and column combination) has not been added to a Custom Section.
A PLM4P internal representation for a null value is added when an extended attribute has been added to a business object or a custom section cell has been added to a Custom section, but no data is entered.

The null representation for numeric values is -1234567890
The null representation for Date fields is ‘9999-12-31 00:00:00.000’

The EA Denorm process treats both scenarios as a null value, and therefore denormalizes the data as NULL values in the DENORM_EA_* tables.

Text Based Values

Text, Qualitative, and Qualitative Lookup Extended Attributes are denormalized using the English only values.

Multi-select values are denormalized as:

· One record, in comma delimited format.

· Individual rows into the Denorm_EA_Text_Multi table

Base Unit of Measure Values

When executing the EA Denorm process, the numeric values entered in the UI are denormalized, as are the conversion to the base Unit of Measure values. Likewise, Quantitative Range and Quantitative Tolerance Extended Attributes also can include Base UOM information for their Min, Max, and Target values.

Qualitative Lookup Limitation

When configuring a Qualitative Lookup in Data Admin, an internal PLM4P category, such as Allergens, can be selected. However, the Extended Attribute Denorm process currently only supports denormalization of the following internal lookup categories:

Countries
Additives
Allergens
Intolerances
Complies With
NPD Brands

Alternatively, external lookup categories, as available via Custom Lookups are fully supported.

Installation

Several database scripts are provided as part of the Extensibility Pack release. The files include a script to generate the new tables used in the denormalization process, and individual stored procedures that handle the denormalization process.

All of these scripts must be executed in order to add the various stored procedures and functions to the database.

Installing the Scripts

Locate the Scripts directory in the release package. There are two different folders: Oracle and SqlServer. Open the folder that corresponds to the database provider you are using, then open the Denormalization folder, and the ExtendedData folder within.

All of the scripts in the ExtendedData folder must be compiled.

Note that an extension point has been added which allows clients to add custom tasks to the denorm process. This EA Denorm process calls the stored procedure named sp_After_Internal_Denorm_EA after it processes the core EA Denormalization. Clients may modify this stored procedure to add their own functionality if needed.

See the Execution section for details of the denormalization process.

Near Real Time Denorm Configuration and Installation

The Near Real Time Denormalization process is service that runs on the Remoting Container to process Extended Attribute denormalization requests in near real time. Follow the instructions below for installation:

1. Stop IIS.

2. Stop the Remoting Container.

3. Enable Real Time Denorm by enabling the feature configuration entries:
Add the following entries to the config\Custom\CustomerSettings.config file, in the FeatureConfig node:

4. Add the following entry to the environmentvariables.config file, in the # Port Numbers section, replacing 8111 with a valid open/unused port:

Prodika.CSDenormService.Port = 8111

5. Customize the Real Time Denorm process.
The following configuration entries allow for customization of the Real Time Denormalization process:

<add key="PollingIntervalInSeconds_ExtendedAttributeRequests" value="90" />

The frequency (in seconds) that Extended Attribute Denorm requests are processed.

<add key="AllowCustomSectionEAsInEADenorm" value="true" />

Extended Attribute Denorm includes *distinct* Extended Attributes that are in Custom Sections. To exclude these EAs, change this value to "false"

<add key="ErrorNotifyFromAddress" value="@@VAR:Prodika.From.EmailAddress@@" />

<add key="ErrorNotifyToAddress" value="@@VAR:Prodika.To.EmailAddress@@"/>

Errors in Real Time Denorm process will generate an email containing error details, along with additional useful information. These configuration settings are used to indicate the sender and the recipient of these emails. The default setting will use the values entered in environmentvariables.config, but can be modified here to use different values.

<add key="MaxLogFilesToKeep_EA" value="30"/> 

To completely disable file logging, set the MaxLogFilesToKeep_EA value to "0" (zero). Note that errors will still be emailed (as discussed above) and an entry in the ProdikaCommon Event Log will also be added.

<add key="DaysForProcessedEARequestExpiration" value="15"/>

These settings are used to clean up (remove) old, successfully processed denormalization requests (from the denorm_ea_request table) after the configured number of days. Set to zero to never remove the denorm request entries.

CUSTOMIZATIONS:

To make any customizations to the above Real Time Denorm configurations, copy the following and add it to the CustomerSettings.config file, in the CustomerSettings/Core node.

Then copy the desired entries from above into the AppSettings node and modify the settings as needed.

6. Enable Real Time Denorm processing for each object type.

Update the \config\Extensions\CustomPluginExtensions config file to enable Real Time Denorm processing for each object type, inserting the following entries into the ValidatePlugins node.

7. Copy the DenormConfig.xml file to the RemotingContainer\bin directory.

8. Add the following entry to EnvironmentSettings.config in the EnvironmentSettings/RemotingContainer/RemoteServices node, making sure to set the isActive attribute to true.

9. Make a backup of the existing RemotingContainer.exe.config file in Apps\RemotingContainer\bin and then replace the current version with the one in this directory.

10. Copy the DenormServices.dll file from the SharedLibs directory and place it in the following folders:

· RemotingContainer\dependentAssemblies

· web\gsm\bin

· web\pqm\bin

· web\scrm\bin

· web\npd\bin

11. Restart IIS.

12. Restart the Remoting Container.

To verify the service is running, you can check the Logs\(Denorm_CustomSections and Logs\Denorm_ExtendedAttributes directories, or the ProdikaCommon Event log. Once the Remoting Container is started, the denorm requests get processed after the configured number of seconds specified in the config.

Execution

Once the database scripts have been added to the database, the EA Denorm process is available for use.

Execution Script

The stored procedure that is used to execute the Batch EA Denorm process is called sp_Denorm_EA. This stored procedure can be run manually, or it can be scheduled to run on a recurring basis using the Database server tools (such as SQL Server Agent for SQL Server).

Executing the sp_Denorm_EA stored procedure is not required when using the Near Real Time Denorm process. However, it should be called for an initial run, to start the denormalization for existing data.

An optional parameter, @log_level, is used to indicate if logging to the DENORM_EA_LOG table should be enabled; a value of 1 will enable logging, 0 will disable logging.

The sp_Denorm_EA stored procedure does the following:

Populates the denorm_ea_templates table with any Active, Archived, or Inactive extended attribute types
Denormalizes each extended attribute type into its corresponding denormalization table and logs the results into the DENORM_EA_LOG table (if enabled).

Booleans and Calculated Booleans
Dates
Numerics and Calculated Numerics
Quantitative Ranges
Quantitative Tolerances
Texts and Calculated Texts
Qualitative Lookups
Qualitatives

Deletes any denormalized records which no longer exist on the business object (Spec, Sourcing Approval, etc.)
Calls the sp_After_Internal_Denorm_EA stored procedure, which can be used by clients for any additional processing.

Performance Considerations

Before setting up denormalization, DBAs must understand the runtime characteristics of their routine. At a minimum, they need to understand how long the routine will run and what impact it will have on users. Database server hardware makes a significant impact on runtime performance of the EA Denorm process.

The first run of denormalization for the Extended Attributes will take the longest time, but subsequent denormalization runs only pull in the changes since the last denormalization run.