Hierarchy Denormalization Guide
Extensibility Pack 3.6
E50232-01
October 2013
Agile Product Lifecycle Management for Process
Copyright © 1995, 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.
Contents
Performance Estimation for Initial
Denormalization
Extending
Hierarchy Denormalization
Relationship
Context Definitions
Supported Relationships that are Denormalized in GSM
Supported
Relationships that are Denormalized
in SCRM
Understanding the Hierarchy Denormalization Data Model
Denormalization Results under Nested-Set
Model
This guide is intended for client programmers involved with integrating Oracle Agile Product Lifecycle Management for Process. Information about using Oracle Agile PLM for Process resides in application-specific user guides. Information about administering Oracle Agile PLM for Process resides in the Oracle Agile Product Lifecycle Management for Process Administrator User Guide.
Descriptions and illustrations of the Agile PLM for Process user interface included in this manual may not match your installation. The user interface of Agile PLM for Process applications and the features included can vary greatly depending on such variables as:
¡ì Which applications your organization has purchased and installed
¡ì Configuration settings that may turn features off or on
¡ì Customization specific to your organization
¡ì Security settings as they apply to the system and your user account
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.
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.
Oracle Software Delivery Cloud (OSDC) provides the latest copy of the core software. Note the core software does not include all patches and hot fixes. Access OSDC at:
This guide describes how to install, configure, and use the Hierarchy Denormalization solution pack.
Solution packs are designed to be pluggable modules that can
be added to the Agile PLM for Process application suite without modifying the
existing release code base.
Oracle Agile PLM
for Process stores objects, such as specifications, along with the
relationships to each other, in a normalized database schema, making inserts,
updates and deletes highly efficient while minimizing its size. The challenges with
having a normalized schema are that it can make custom SQL queries complex and possibly
not optimal for bulk data retrieval. For example, to construct a report that
returns the entire hierarchy of a trade specification, would require a deep
understanding of many relationship tables and would be extremely difficult to
do in SQL alone, due to the varying number of possible layers in the hierarchy.
A hierarchy of a specification is defined as that specification plus all
descendant specifications as well as other related objects. For example these
objects would be considered part of a trade hierarchy:
¡ì The main trade specification
¡ì All lower level trade
specifications
¡ì The material specification
directly associated to the trade specification
¡ì The formula to create the above material
¡ì All inputs and outputs to the
above formula
¡ì All formulas that create the
above inputs
¡ì All inputs and outputs to the
above formulas
¡ì Etc ¡
By continuing to
drill down into the formula and intermediate formulas that comprise a trade
specification, you will have what we are referring to as the Trade Hierarchy.
This hierarchy is not limited to the relationships defined above but covers
many of the relationships that are defined in PLM for Process.
Hierarchy
Denormalization provides a solution to this data access problem by storing the
object relationship information in a single table, allowing for simple and
performant hierarchy retrieval.
Many solutions can use this table to provide functionality such as hierarchical navigation and reporting.
Note: A prerequisite for installing Hierarchy Denormalization is Oracle Agile PLM for Process 6.1.1.1.0.
1. Unzip the contents of Extensibility Pack 3.6.
2. Unzip the contents of the Utilities\DenormServices\HierarchyDenorm\HierarchyDenorm.zip file and note the location.
1. Create a backup of the PLM for Process database.
2. Apply the database scripts.
a. SQL Server
i. Open a command prompt and navigate to the directory where you unzipped the solution pack.
ii. Change directories (cd) to the Database directory.
iii. Apply the scripts using the following calls to the ApplyScripts.exe utility:
ApplyScripts šCc "server=<database_server>;uid=<user>; password=<password>;database=<database>" šCf HierarchyDenorm.xml
iv. After the ApplyScripts call, you can confirm that the database scripts have been applied successfully when the system prompts you with the following message: "Complete šC with no errors".
b. Oracle
i. Open a command prompt and navigate to the directory where you unzipped the solution pack.
ii. Change directories (cd) to the Database directory.
iii. Apply the scripts using the following calls to the ApplyScripts.exe utility:
ApplyScripts šCc "User
Id=<user>;Password=<password>; Data Source=<datasource>"
šCdbvendor="orcl" šCf HierarchyDenorm-orcl.xml
iv. After the ApplyScripts call, you can confirm that the database scripts have been applied successfully when the system prompts you with the following message: "Complete šC with no errors".
1. Run the HierarchyDenormSetup.exe file from the location where you unzipped the solution pack and follow the onscreen instructions to install the necessary files to your specified PLM for Process directory.
A backup folder called ¡°SolutionPackBackup¡± is created during the installation to facilitate the uninstall process. Inside of that folder, a new folder called ¡°HierarchyDenorm¡± is created to hold the files that were changed during the installation.
2.
Restart RemotingContainer
Service.
Hierarchy
Denormalization is a background process running on the Remoting Container,
which processes updates as needed. After the initial startup of the Remoting
Container after installation, all the hierarchies must be denormalized. The
amount of time to perform this is dependent on some factors such as hardware
performance, number of specifications, depth of hierarchies and Hierarcy
Denormalization configuration settings.
For an
approximation of how long it will take to complete the initial processing,
tests were performed on three data sets. Below are the test details and
results.
Testing server (Virtual
Machine) information:
¡ì
APP server: Xeon 2.93G Dual, 8G RAM, 1000M Intranet, Windows 2008 R2
(64-bit) with IIS7
¡ì
DB server A: Xeon 2.93G Dual, 8G RAM, 1000M Intranet, Oracle 11g Release
2 for Windows (64-bit)
¡ì
DB server B: Xeon 2.93G Dual, 8G RAM, 1000M Intranet, Oracle 11g Release
2 for Linux
The following is a
snapshot of the configuration file ¡°HierarchyDenormConfig.xml¡± used.
Four database
samples with legacy data have been selected for the estimation. Their involved
object types were listed in the below table.
Scope |
Object |
Spec Type |
Table Name |
GSM |
PrintedPackaging |
2121 |
FinishedPackagingSpec |
Menu |
6500 |
FoodServiceMenuItem |
|
Formulation |
5816 |
formulationSpecification |
|
Material |
1004 |
MaterialSpec |
|
Trade |
2147 |
gsmBaseTradeSpec |
|
Product |
6501 |
FoodServiceProduct |
|
Delivered Material Packing |
1010 |
PackingSpec |
|
Label |
1006 |
LabelingSpec |
|
Packaging |
1009 |
PackagingSpec |
|
Equipment |
2280 |
gsmEquipmentSpecification |
|
Packing Configuration |
2076 |
PackingConfigurationSpec |
|
Activity |
2283 |
SpecActivitySpecification |
|
SCRM |
Company |
5002 |
scrmCompany |
Facility |
5001 |
scrmFacility |
|
SourcingApproval |
5012 |
scrmSourcingApproval |
|
NonSpecSourcingApproval |
5019 |
scrmSourcingApprovalNonSpec |
¡ì
Sample A - Oracle for Windows
Total: ~ 26,000 requests
Duration: ~ 173
minutes
Average: ~ 2.5 requests/second
¡ì
Sample B - Oracle for Linux
Total: ~ 26,000
requests
Duration: ~ 172
minutes
Average: ~ 2.5
requests/second
¡ì
Indicators on Oracle platform:
RemotingContainer
CPU usage:
Average: 6%
Peak: 12%
Valley: less than 1%
RemotingContainer
Memory usage:
150MB ~ 350MB
By default
Hierarchy Denormalization will execute without the need to update the
configuration settings. The configuration settings can be used to change how
often the denormalization process runs as well as what will be denormalized. To
understand when data will be denormalized, it¡¯s helpful to understand the
processes involved.
Hierarchy
Denormalization is designed as two endless processing services: Denormalization
Detector and Denormalization Processor service. The Detector service checks
whether the hierarchy was changed after the last time Denormalization Processor
was executed. If changes were detected, it will create denormalization
requests. The Processor service will orchestrate the execution of denormalizers
which does the actual denormalization work. Both the Detector and Processor service are designed to be triggered
in a configurable frequency as shown in the table below. This table also shows
the configuration for controlling what is denormalized.
The following are
the configurable properties located in the ¡°HierarchyDenormConfig/Settings¡± section
of the configuration file located at ¡°<PLM for
Process>\config\Extensions\HierarchyDenormConfig.xml¡±.
Property Name |
Acceptable values |
Default Value |
Description |
PollingIntervalInSeconds_Detector |
Number in seconds |
90 |
Interval of detector service
running frequency. |
PollingIntervalInSeconds_Processor |
Number in seconds |
100 |
Interval of processor service
running frequency. |
DenormMaxLevel |
Number |
12 |
Denormalized hierarchy
tree max depth limitation. |
DenormMaxLevel_BreakdownComponent |
Number |
3 |
Denormalized hierarchy branch
max depth limitation specific for a Breakdown Component. If current branch has a node
whose relationship context is ¡°BreakdownComponent¡± it would perform a level-limited
denormalization. The default value indicates the parent will at most have
3-levels children. |
DenormMaxLevel_AlternateOutput |
Number |
2 |
Denormalized hierarchy
branch max depth limitation specific for an Alternate Output. If current branch has a
node whose relationship context is ¡°AlternateOutput¡± it would perform a
level-limited denormalization. The default value indicates the parent will at
most have 2-levels children. |
DenormMode_AssociatedSpec |
¡°TargetOnly¡±; |
¡°TargetOnly¡± |
Determines which type of
sub-relationships should be resolved. |
DenormMode_RequestBatchCount |
Number |
200 |
Determinate how many
valid requests will be resolved in a denormalization period. |
LoggingLevel |
¡°ErrorOnly¡±; ¡°WithWarning¡±; ¡°WithWarningAndInfo¡± |
¡°ErrorOnly¡± |
Determinate
how many types of logs should be written. |
LoggingWhiteList_SkipWarning |
TypeID string separated by comma |
¡°5816,1004,2147,1009,2280,2121,1006,2076,1010,6500,6501,5002,5001,5012,5019¡± |
The listed
spec types would always skip writing any denormalization warning no matter
what LoggingLevel is. |
Hierarchy
Denormalization is designed as a pluggable architecture that enables customers
to extend the out of the box functionality by adding other relationships to the
denormalized table. The two components necessary to make this possible are
Detectors and Denormalizors.
Detectors are components that determine what and when a relationship
should be denormalized. Denormalizors are components that do the actual work of
populating the denormalized table. These are both pluggable components written
in C#.
A full reference of
how to extend Hierarchy Denormalization can be found below in the Extensibility
References section.
The purpose of
detectors is to find objects that have been modified since the denormalizers
last ran. To determine this it compares last updated dates of the objects to
the last run dates of the denormalizers. Each object type will have a specific
detector that is responsible for performing this operation.
Detector settings were
organized in ¡°HierarchyDenormConfig/Detectors¡± section of the ¡°<PLM for
Process>\config\Extensions\HierarchyDenormConfig.xml¡± file. It can accept a
sequence of ¡°Detector¡± nodes within the section.
Each ¡°detector¡±
should have an ¡°objectURL¡± and ¡°id¡± attribute. The value of ¡°id¡± attribute
should be unique across the section. ¡°objectURL¡± is configured as the full
class name of the detector producing factory.
The user can either
add new detectors or customize the existing ones. (See more at Extensibility
References). Example of Detector settings:
<Detectors
configChildKey="id">
<Detector
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.Detectors.RequestArchiveDetector,HDGSMLib"
id="RequestArchiveDetector"/>
<Detector
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.Detectors.ActivityDetector,HDGSMLib"
id="ActivityDetector"/>
¡
</Detectors>
The purpose of
denormalizers is to actually populate the denormalized table.
Denormalizer
settings were organized in ¡°HierarchyDenormConfig/Denormalizers¡± section of the
¡°<PLM for Process>\config\Extensions\HierarchyDenormConfig.xml¡± file. It
can accept a sequence of ¡°Denormalizer¡± nodes within the section.
Each ¡°Denormalizer¡±
should have an ¡°objectURL¡±,¡°type¡± and ¡°dataTable¡± attribute. The ¡°type¡±
attribute will represent the type id defined in PLM for Process. ¡°objectURL¡± is
configured as the full class name of the denormalizer producing factory. And ¡°dataTable¡±
determinates the table in which the corresponding denormalization results
should be saved. Note the available data tables must have been pre-defined in installer
scripts. Currently, this solution pack supports the following spec type and
repository mappings:
ClassName |
Type |
DataTable/Repository |
IngredientSpecification |
1004 |
DENORM_HD_HIERARCHY_GSM |
LabelingSpecification |
1006 |
DENORM_HD_HIERARCHY_GSM |
PackagingSpecification |
1009 |
DENORM_HD_HIERARCHY_GSM |
PackingSpecification |
1010 |
DENORM_HD_HIERARCHY_GSM |
PackingConfigurationSpecification |
2076 |
DENORM_HD_HIERARCHY_GSM |
FinishedPackagingSpecification |
2121 |
DENORM_HD_HIERARCHY_GSM |
GSMTradeSpecDO |
2147 |
DENORM_HD_HIERARCHY_GSM |
EquipmentSpecification |
2280 |
DENORM_HD_HIERARCHY_GSM |
FormulationSpecification |
5816 |
DENORM_HD_HIERARCHY_GSM |
FoodServiceMenuItemDO |
6500 |
DENORM_HD_HIERARCHY_GSM |
FoodServiceProductDO |
6501 |
DENORM_HD_HIERARCHY_GSM |
SCRMFacilityDO |
5001 |
DENORM_HD_HIERARCHY_SCRM |
SCRMCompanyDO |
5002 |
DENORM_HD_HIERARCHY_SCRM |
SourcingApproval |
5012 |
DENORM_HD_HIERARCHY_SCRM |
SourcingApprovalNonSpec |
5019 |
DENORM_HD_HIERARCHY_SCRM |
For example, when a
formulation specification, type 5816 is denormalized, the resulting
denormalized data will be stored in the ¡°DENORM_HD_HIERARCHY_GSM¡± table.
For each ¡°Denormalizer¡±,
the relationships that should be denormalized should also be defined. The
relationships also have attributes of ¡°objectURL¡± and ¡°id¡±. An expected ¡°RelationshipContext¡±
should be bound with a relationship resolver by adding its unique name to the
end of the ¡°objectURL¡± (Refer to ¡°Relationship Context Definitions¡±).
Customers can
create new denormalizers or customize the existing ones. (See more at Extensibility
References).
Example of Denormalizer
settings:
<Denormalizer
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.Denormalizers.FormulationDenormalizer,HDGSMLib"
type="5816" dataTable="DENORM_HD_HIERARCHY_GSM">
<Relationships
configChildKey="id">
<Relationship
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.DescendentRelationships.ComActivities,HDGSMLib$PrimaryActivity"
id="ComActivities"/>
<Relationship
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.DescendentRelationships.ComMasterSpec,HDGSMLib$ExplicitMaster"
id="ComMasterSpec"/>
¡
</Relationships>
</Denormalizer>
Specifications can
be related to each other in different ways. For instance a formulation can have
an input BOM item or it can have an alternate BOM item. If that formulation was
part of a trade specification then the relationship between the trade and the
BOM items would also have certain relationship types. Hierarchy Denormalization
captures these relationships types in 2 columns in the DENORM_HD_HIERARCHY_XXXX
table, one for the Parent and one for the Ancestor, ¡°fkParentRelationshipContext¡±
and ¡°fkAncestorRelationshipContext¡±.
These fields are foreign keys to the ¡°DENORM_HD_RELATIONSHIP_CTX¡± table
and ¡°DENORM_HD_RELATIONSHIP_CTX_ML¡± table, which both store the readable
relationship type, the latter stores the multilingual types. These tables
should not be modified.
Example:
SELECT PARENT.Context,ANCESTOR.Context,H.* FROM DENORM_HD_HIERARCHY_XXXX
H
LEFT JOIN
DENORM_HD_RELATIONSHIP_CTX PARENT ON H.fkParentRelationshipContext=PARENT.PKID
LEFT JOIN
DENORM_HD_RELATIONSHIP_CTX ANCESTOR ON H.fkParentRelationshipContext=ANCESTOR.PKID
WHERE H.fkAncestor='<AncestorPKID>'
Column |
Data type |
Description |
PKID |
Number |
Identifier |
Context |
Varchar |
Relationship context unique name |
MaxLevelLimit |
Number |
Indicate the
max allowable level under current relationship type |
IsAlternate |
Bool |
IsAlternate flag |
And Table ¡°DENORM_HD_RELATIONSHIP_CTX_ML¡±
is the corresponding translations. Link it for multi-language support.
Column |
Data type |
Description |
PKID |
Number |
Relationship context identifier |
LangID |
Number |
Language ID. 0: English |
Context |
Varchar |
Relationship context translation. |
The processor is a
background service running in RemotingContainer. The responsibility of this
process is to orchestrate the execution of the denormalizers. Each denormalizer
is responsible for updating one level of the hierarchy, so the generation of
the complete hierarchy will take many denormalizers.
For example, this
is an expected tree:
For example, in
order to denormalize the above hierarchy, a denormalizer would be executed for
each node, according to spec type.
The sequence of execution would be similar to the below diagram.
Each denormalizer
contains multiple child processes called relationship-resolvers, which are
responsible for updating the relationships specific to an object type. For
instance if a formulation specification contains a master specification, primary
Inputs and an alternate output, within the Formulation Denormalizer, three
relationship-resolvers will be executed; the Master Specification
relationship-resolver will update the relationships to the master
specification, the Primary Input relationship-resolver will update the
relationships to the primary inputs and so on.
Customers wanting
to add new types of relationships to the denormalized output can add their own
relationship resolvers with the runtime context parameters given by the relationship
resolver interface.
The input parameter
of denormalizer is taking the denormalization context transferred from the
previous denormalizer. It includes the parent PKID, the relationship context,
some referenced resource entries, and so on. These data ensures the denormalizer
finish its work as designed.
Similarly, the
output parameter is taking the specific denormalization context that should be
transferred to the next denormalizer. Actually, a parent output parameter can
be rapidly converted to a child input parameter directly.
This is depicted in
the below diagram.
Finally, every
denormalizer and its related relationship-resolvers are configurable. The
corresponding configuration node for the above FormulationDenormalizer is:
<Denormalizer
type="5816"
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.Denormalizers.FormulationDenormalizer,HDGSMLib"
dataTable="DENORM_HD_HIERARCHY_GSM">
<Relationships configChildKey="id">
<Relationship id="ComMasterSpec"
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.DescendentRelationships.ComMasterSpec,HDGSMLib$ExplicitMaster"/>
<Relationship id="FrmInput"
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.DescendentRelationships.FrmInput,HDGSMLib$Input"/>
<Relationship id="FrmAlternateOutput"
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDGSMLib.DescendentRelationships.FrmAlternateOutput,HDGSMLib$AlternateOutput"/>
<Relationship ¡ />
</Relationships>
</Denormalizer>
You can find the full
official mappings in the configuration file located in the following path ¡°<PLM
for Process>\config\Extensions\HierarchyDenormConfig.xml¡±.
Attention: Each relationship-resolver ObjectURL is
taking an additional parameter separated by ¡°$¡± character. That means this
relationship-resolver is binding with a ¡°Relationship Context¡± so that the
resolver¡¯s outputs can also take the context as its ¡°ParentRelationshipContext¡±
property. Please refer to Relationship Context Definitions section.
Object (Parent) |
Related
Object (Child) |
Relationship |
Resolver
Name in HDGSMLib |
Trade |
Trade Specifications |
Primary |
TrdNextLowerLevelItems |
Primary Packaging Specifications |
Primary |
TrdPackagingMaterials |
|
Alternate Packaging Specifications |
Alternate |
TrdAlternatePackaging |
|
Material |
Primary |
TrdRelatedMaterial |
|
Formulation that produces associated Material |
FormulationContext |
MatFormulationContext |
|
Breakdown Materials of the associated Material |
BreakdownComponent |
ComBreakdown |
|
Nutrient Profiles |
Primary |
ComNutrientProfile |
|
Sourcing Approval |
Primary |
ComSourcingApproval |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Master Specs |
ExplicitMaster |
ComMasterSpec |
|
Formulation |
Materials |
Input, Output |
FrmInput, FrmOutput |
Alternate Materials |
Alternate, AlternateOutput |
FrmAlternateInput,
FrmAlternateOutput |
|
Packaging |
Input |
FrmInput |
|
Alternate Packaging |
Alternate |
FrmAlternateInput |
|
Formulation context |
FormulationContext,
AlternateFormulationContext |
MatFormulationContext,
MatAlternateFormulationContext |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Master Specs |
ExplicitMaster |
ComMasterSpec |
|
Menu |
Product/Menu |
Primary |
MenuMenuItemBuild |
Packaging |
Primary |
MenuPackagingMaterial |
|
Alternate Pkg |
MenuAlternatePackaging |
||
Global/Regional Standard |
- |
- |
|
Alternate Standards |
AlternateStandards |
ComAlternateStandards |
|
Nutrient Profile |
Primary |
ComNutrientProfile |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Master Specs |
ExplicitMaster |
ComMasterSpec |
|
Product |
Breakdown Materials |
BreakdownComponent |
ComBreakdown |
Global/Regional Standard |
- |
- |
|
Alternate Starndards |
AlternateStandards |
ComAlternateStandards |
|
Packing config |
Primary |
ComPackingConfigurationSpec |
|
Sourcing Approval |
Primary |
ComSourcingApproval |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Master Specs |
ExplicitMaster |
ComMasterSpec |
|
Material |
Breakdown Materials |
BreakdownComponent |
ComBreakdown |
Substitute Material |
Substitute |
ComSubstituteMaterial |
|
Packing config |
Primary |
ComPackingConfigurationSpec |
|
Produced By Formulation |
Primary |
MatProducedBy |
|
LIO Profile |
- |
- |
|
Sourcing Approval |
Primary |
ComSourcingApproval |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
ExplicitMaster |
ComMasterSpec |
||
Packaging |
Sub Components |
SubComponent |
PkgSubComponents |
Packing Config |
Primary |
ComPackingConfigurationSpec |
|
Equipment |
Primary |
PkgEquipmentSpec |
|
Substitute Material |
Substitute |
ComSubstituteMaterial |
|
Sourcing Approval |
Primary |
ComSourcingApproval |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Master Specs |
ExplicitMaster |
ComMasterSpec |
|
Equipment |
Sub Components |
SubComponent |
EquSubComponent |
Sourcing Approval |
Primary |
ComSourcingApproval |
|
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Master Specs |
ExplicitMaster |
ComMasterSpec |
|
Delivered Material |
Labeling |
Primary |
DmatLabelingSpec |
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Packing Config |
Delivered Material |
Primary |
PcfgDeliveredMaterialPackingSpec |
Associated Specs |
Associated |
ComAssociatedSpec |
|
Activities |
PrimaryActivity |
ComActivities |
|
Labeling |
Associated Specs |
Associated |
ComAssociatedSpec |
Activities |
PrimaryActivity |
ComActivities |
Object (Parent) |
Related Object (Child) |
Relationship |
Resolver Name in HDSCRMLib |
Company |
Company |
Primary |
CompChildCompany |
Facility |
Primary |
CompFacility |
|
Facility |
Sourcing Approval |
Primary |
FacNonSAC, FacSAC |
Sourcing Approval |
Specification |
Primary |
SACSpec |
Facility |
Primary |
ComReceivingFacility |
|
Non Sourcing Approval |
Facility |
Primary |
ComReceivingFacility |
All hierarchies
will be stored in the denormalized table, with each node represented by one
row. Each row will contain information such as a reference to the parent
object, level in the hierarchy and the type of relationship. These nodes are
tied together by a column, fkAncestor, - that references the relative top node
of the hierarchy. By using this column as the query criteria all the nodes for
a specific hierarchy can be returned.
For Example:
Retrieve the single
tree with this script:
select * from DENORM_HD_HIERARCHY_GSM
where fkAncestor='21475f5fc62c-1ccb-4067-80f8-4f5810806fb5';
And following is
the sample data table from above script:
PKID |
fkAncestor |
fkDescendent |
fkDescendentParent |
CurrentLevel |
fkParentRelationshipContext |
|||||
2 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
|
0 |
1 |
|||||
3 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
10044dd5a3b8-0bae-4c93-b711-ecaf8f7b4e6a |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
1 |
6 |
|||||
4 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
10044209f6dc-05d8-4c82-9e11-a54a7bb443c5 |
10044dd5a3b8-0bae-4c93-b711-ecaf8f7b4e6a |
2 |
14 |
|||||
5 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
100499340adc-87ec-4fd2-adf6-7c93718e5568 |
10044dd5a3b8-0bae-4c93-b711-ecaf8f7b4e6a |
2 |
14 |
|||||
6 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
581603d28db4-0ce2-4b13-be70-ae22f6480079 |
10044dd5a3b8-0bae-4c93-b711-ecaf8f7b4e6a |
2 |
13 |
|||||
7 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
1005a6fd6f82-31a9-4ad2-92da-332c504b5f80 |
10044209f6dc-05d8-4c82-9e11-a54a7bb443c5 |
3 |
8 |
|||||
8 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
10044209f6dc-05d8-4c82-9e11-a54a7bb443c5 |
581603d28db4-0ce2-4b13-be70-ae22f6480079 |
3 |
9 |
|||||
9 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
100499340adc-87ec-4fd2-adf6-7c93718e5568 |
581603d28db4-0ce2-4b13-be70-ae22f6480079 |
3 |
9 |
|||||
10 |
21475f5fc62c-1ccb-4067-80f8-4f5810806fb5 |
1005a6fd6f82-31a9-4ad2-92da-332c504b5f80 |
10044209f6dc-05d8-4c82-9e11-a54a7bb443c5 |
4 |
8 |
|||||
fkAncestorRelationshipContext |
ObjectType |
fkObjectSubType |
fkObjectSubTypeEx |
fkRelationshipIdentifier |
BoxLft |
BoxRgt |
||||
1 |
2147 |
|
|
|
1 |
18 |
||||
6 |
1004 |
2210083d0660-b2a6-4256-ad98-3d3dcc2d5d43 |
|
5826d5ba6106-6df9-4f97-8dab-8c59a0ed48c3 |
2 |
17 |
||||
6 |
1004 |
2210e2704850-c6e1-4c3f-8bd4-7e11bbe862b0 |
|
101323a10b8c-45d8-4eb8-8dbf-9f09d138ae24 |
3 |
6 |
||||
6 |
1004 |
2210e2704850-c6e1-4c3f-8bd4-7e11bbe862b0 |
|
1013fa756721-35a9-4f34-9f68-4a1319b3be7b |
7 |
8 |
||||
6 |
5816 |
|
|
5826d5ba6106-6df9-4f97-8dab-8c59a0ed48c3 |
9 |
16 |
||||
6 |
1005 |
|
|
104058d05bf4-afbe-40ef-bb7e-373aa23217b1 |
4 |
5 |
||||
6 |
1004 |
2210e2704850-c6e1-4c3f-8bd4-7e11bbe862b0 |
|
5817c375fbf5-b990-42ba-8d7b-b10bfc917303 |
10 |
13 |
||||
6 |
1004 |
2210e2704850-c6e1-4c3f-8bd4-7e11bbe862b0 |
|
5817d503e570-aa02-4f60-9b58-e67bf550bb1a |
14 |
15 |
||||
6 |
1005 |
|
|
104058d05bf4-afbe-40ef-bb7e-373aa23217b1 |
11 |
12 |
||||
The structure
definition of the table is shown in below. The current node¡¯s primary key is
stored in the fkDescendent column.
Column |
Data type |
Description |
PKID |
Number |
Primary key |
fkAncestor |
Char |
PKID of the top node of the hierarchy. |
fkDescendent |
Char |
PKID of the
object for the current node of the hierarchy. |
fkDescendentParent |
Char |
PKID of the parent object of the current
node. |
CurrentLevel |
Number |
Level of the hierarchy for the current node. Top node is 0, first
level down is 1 and so on. |
fkParentRelationshipContext |
Number |
The relationship type between current node and its parent node. (Refer
to ¡°Relationship Context Definitions¡±) |
fkAncestorRelationshipContext |
Number |
The relationship type between current node and the ancestor node. (Refer
to Relationship
Context Definitions). |
ObjectType |
Number |
The 4 digit object type ID of current node. |
fkObjectSubType |
Char |
The first item type ID for current
node if it is a Trade, a Material or a Packaging. |
fkObjectSubTypeEx |
Char |
The second item type ID for current
node if it is a
Material. |
fkRelationshipIdentifier |
Char |
This is the row PKID when a node exists as one row within in a
collection. For example when multiple Materials exist within a Formula as BOM
items, this represents the ID that can be tied back to retrieve more row
detail such as Quantitiy. |
LastEdit |
Datetime |
Last edit date of the object represented by this node. |
Remark |
Varchar |
(For internal
use). |
MaxLevel |
Number |
(For internal
use). Indicate the max allowable level
for current node¡¯s children. |
ContextOwner |
Char |
(For internal
use). Keep the
formulation context from parent denormalizer. |
BoxLft |
Number |
Nested-set mode LEFT value. Help to identify the tree
branch. |
BoxRgt |
Number |
Nested-set model RIGHT value. Help to identify the tree
branch. |
The nested-set
model is a high efficient model for storing hierarchical data or trees. A
metaphor used to describe this model is that each parent node is a box, and all
its children are also boxes inside the parent box. For hierarchies greater than
two levels there will be boxes inside of boxes. Each box will have 2 numbers,
one for each side, box-left and box-right. These numbers facilitate the
retrieval and reconstruction of the hierarchy. The solution guarantees that each
box will have two serial numbers (Box-Left and Box-Right). They help to identify
each node and facilitate thee retrieval of all parents or all children nodes for
a selected node. Example:
The visual
nested-set model of the picture is like below:
And this is the
corresponding data table:
Object |
Level |
BoxLft |
BoxRgt |
Trade 01 |
0 |
1 |
12 |
Material 01 |
1 |
2 |
7 |
Formulation 01 |
2 |
3 |
4 |
Material 02 |
2 |
5 |
6 |
Trade 02 |
1 |
8 |
11 |
Trade 03 |
2 |
9 |
10 |
As above shown, those
unique numbers (BoxLft and BoxRgt) could help to search or traverse the whole
result tree. Assuming there is a node named ¡°X¡±. Here are some tips:
a)
When ¡°X.BoxRgt šC X.BoxLft == 1¡±, it must be a leaf node.
b)
If ¡°{NODE}.BoxLft < X.BoxLft && {NODE}.BoxRgt > X.BoxRgt¡±,
{NODE} must be X¡¯s parent.
c)
If ¡°{NODE}.BoxLft > X.BoxLft && {NODE}.BoxRgt < X.BoxRgt¡±,
{NODE} must be X¡¯s child.
d)
With b) and c), it¡¯s convenient to get the direct parent, the direct
children or the whole branch members in the tree as well.
Below is a complete
implementation of how to extend Hierarchy Denormalization by identifying a new
relationship to denormalized, creating a Detector to determine when a
relationship has changed and create a request for processing and creating a
Denormalizer which will process the request by adding the appropriate data in
the denormalization table.
In this example we
will use the PQM Issue relationship with its supplier and affected items (material
specification).
1. Create Visual Studio Project
Open Visual Studio 2010 (or above version) and create a class library.
For this example it
is called ¡°HDPQMLib¡± under .NET Framework 3.5. Normally, it¡¯s strongly recommended
to create sub-directories for better organization.
2. Create Detector
Create a Detector for PQM Issue object so that the Denormalization service would
know if an Issue has been changed for any reason.
The ¡°PQMIssueDetector¡±
is responsibility to detect the edit event in the UI and deliver the related
object PKID(s) to Denormalization request queue.
To add the item to
the queue it is possible to use a SQL insert statement to write to the ¡°DENORM_HD_REQUEST¡±
table directly or by using pre-defined HierarchyDenormRequest data object.
Below is an example
of the SQL solution.
The most critical
point is the new Detector must inherit from ¡°Oracle.PLM4P.SolutionPack.HierarchyDenorm.DetectorService.DetectorBase¡±
and implement the ¡°Process¡± method. The delivery work should be done in the designated
method.
In this case, the Detector makes
a scan on the ¡°commonLifecycleEventLog¡± table in which many events are captured
and then inserts the appropriate PKIDs in the ¡°DENORM_HD_REQUEST¡± table.To help
understand the ¡°CommonLifecycleEventLog¡± better, below are some of the details
of what this table captures.
Action Category |
eventType |
eventSource |
Create |
1 |
GSM.CreateFromTemplate |
1 |
GSM.Editor |
|
1 |
GSM.SmartIssue |
|
1 |
SCRM.Clone |
|
Update |
2 |
GSM.Editor |
2 |
GSM.GlobalSuccession |
|
2 |
GSM.Workflow.Resolve |
|
2 |
SCRM.Editor |
|
2 |
SCRM.Features.Facility.CompanyChange |
|
2 |
SCRM.Workflow.Resolve |
|
Workflow |
3 |
GSM.Workflow.Resolve |
3 |
GSM.Workflow.Transition |
|
3 |
SCRM.Workflow.Resolve |
|
Copy |
4 |
GSM.Clone |
4 |
GSM.CreateFromTemplate |
|
4 |
GSM.NewIssue |
|
4 |
GSM.SmartIssue |
|
4 |
SCRM.Clone |
|
Get Latest Revision |
5 |
Revision |
Substitute |
6 |
GSM.Substitute |
Alternatively, the
following code shows how to create a request by using the data object.
3. Register the Detector
In order for the
RemotingContainer to recognize the new Detector it must be registered properly.
To do this, open the ¡°<PLM for Process>\config\Extensions\HierarchyDenormConfig.xml¡±
file with any text editor. Add the following node to the ¡°/HierarchyDenormConfig/Detectors¡±
section.
<Detector
id="PQMIssueDetector"
objectURL="Class:YourCompany.HDPQMLib.Detectors.PQMIssueDetector,HDPQMLib"/>
4. Create Denormalizer
Find the specification type ID for the main object, in this example it is the
PQM Issue, 7002. This can be done by querying the ¡°ORClassMetaInfo¡± table.
select * from orclassmetainfo where classname = 'PQMIssueDO'
Normally, there is no need to
create a new specific denormalizer for each object type. Use ¡°Oracle.PLM4P.SolutionPack.HierarchyDenorm.ProcessorService.DenormalizerBase¡±
instead if the current target object doesn¡¯t have to overwrite some denormalization
runtime Context.
In current release
version, only FormulationSpecification uses this feature. This screenshot helps
to explain the concept.
As a result, this configuration
node should be added to ¡°/HierarchyDenormConfig/Denormalizers¡± section.
<Denormalizer
type="7002"
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.ProcessorService.DenormalizerBase,HierarchyDenormProcessorService"
dataTable="DENORM_HD_HIERARCHY_PQM">
<Relationships
configChildKey="id">
</Relationships>
</Denormalizer>
Please note the marked ¡°dataTable¡±
property here. This property determines the repository of the data generated by
this Denormalizer. The direct table
name should be given there; however, the user must ensure the target data table
exists. If not, use following scripts to create a new repository for the new
customized denormalization.
-- SQL Server
IF EXISTS(SELECT 1 FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'DENORM_HD_HIERARCHY_PQM')
DROP TABLE DENORM_HD_HIERARCHY_PQM
GO
CREATE TABLE DENORM_HD_HIERARCHY_PQM(
PKID int NOT NULL,
fkAncestor char(40) NOT NULL,
fkDescendent char(40) NOT NULL,
fkDescendentParent char(40)
NULL,
CurrentLevel int NOT NULL,
fkParentRelationshipContext int
NOT NULL,
fkAncestorRelationshipContext
int NOT NULL,
ObjectType int NOT NULL,
fkObjectSubType char(40) NULL,
fkObjectSubTypeEx char(40) NULL,
fkRelationshipIdentifier
char(40) NULL,
LastEdit datetime NOT NULL
CONSTRAINT DF_DENORM_HD_HIERARCHY_PQM_LastEdit DEFAULT (getdate()),
Remark nvarchar(512) NULL,
MaxLevel int NOT NULL,
ContextOwner char(40) NULL,
BoxLft int NOT NULL,
BoxRgt int NOT NULL,
CONSTRAINT PK_DENORM_HD_HIERARCHY_PQM PRIMARY KEY CLUSTERED
(
PKID
ASC
)
)
GO
-- Oracle
DECLARE cnt NUMBER;
BEGIN
cnt:=0;
Select count(*) into cnt from
user_tables where table_name = upper('DENORM_HD_HIERARCHY_PQM');
If(cnt>0) then
execute
immediate ('DROP TABLE DENORM_HD_HIERARCHY_PQM');
End if;
execute immediate ('
CREATE TABLE DENORM_HD_HIERARCHY_PQM(
PKID number(10,0) NOT NULL,
fkAncestor
char(40) NOT NULL,
fkDescendent
char(40) NOT NULL,
fkDescendentParent
char(40) NULL,
CurrentLevel
number(10,0) NOT NULL,
fkParentRelationshipContext
number(10,0) NOT NULL,
fkAncestorRelationshipContext
number(10,0) NOT NULL,
ObjectType
number(10,0) NOT NULL,
fkObjectSubType
char(40) NULL,
fkObjectSubTypeEx
char(40) NULL,
fkRelationshipIdentifier
char(40) NULL,
LastEdit
timestamp DEFAULT CURRENT_TIMESTAMP,
Remark
nvarchar2(512) NULL,
MaxLevel
number(10,0) NOT NULL,
ContextOwner
char(40) NULL,
BoxLft
number(10,0) NOT NULL,
BoxRgt
number(10,0) NOT NULL,
CONSTRAINT
PK_DENORM_HD_HIERARCHY_PQM PRIMARY KEY
(
PKID
)
)
');
END;
5. Create Relationship Resolvers
Next create two relationship resolvers for ¡°Suppliers¡± and ¡°AffectedItems¡±.
This will be similar
to the Detector development. The user should create two classes named ¡°IssueAffectedItem¡±
and ¡°IssueSupplier¡± under the DescendentRelationships directory of the project.
Both of them must inherit from ¡°Oracle.PLM4P.SolutionPack.HierarchyDenorm.ProcessorService.DescendentRelationshipBase¡±
and implement the ¡°GetDescendents¡± method. In the implementation of the method,
with the input parameter (packaging the denormalization context of current scenario),
¡°IssueAffectedItem¡± resolver should retrieve the AffectedItem relationship
children of current PQM Issue whose PKID was being assigned in ¡°input.fkDescendentParent¡±.
And ¡°IssueSupplier¡± resolver should retrieve the Supplier relationship children
of current PQM Issue specification whose PKID was being assigned in ¡°input.fkDescendentParent¡±
as well. In fact, the input parameter should have provided whatever the task
needs.
This is the demo
code:
The solution relies
on the reflection feature provided by PLM for Process core. It will be helpful
to make a basic understanding of the PLM for Process objects.
Then the object property
retrievers provided by this solution pack would help to get the direct PKIDs,
and then wrap them as an output for next phase. The actual code is not complex.
6. Register the Resolvers
The new resolvers should be registered in configuration.
Add the two nodes to ¡°/HierarchyDenormConfig/Denormalizers/Denormalizer/Relationships¡±
section. Put them under the ¡°7002¡± one.
<Relationship
id="ComAffectedItem"
objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDPQMLib.DescendentRelationships.IssueAffectedItem,HDPQMLib$PrimaryXApp"/>
<Relationship
id="ComCompany" objectURL="Class:Oracle.PLM4P.SolutionPack.HierarchyDenorm.HDPQMLib.DescendentRelationships.IssueSupplier,HDPQMLib$PrimaryXApp"/>
Meanwhile, the expected
relationship context for current resolver will be assigned here. Refer to Relationship Context Definitions for more detail about the additional
parameter at the end of ¡°objectURL¡± value.
7. Create DLL
Finally, compile the new project to be a DLL file and put it to ¡°<PLM for
Process>\RemotingContainer\dependentAssemblies¡±.