Custom Section
Denormalization Guide
Extensibility Pack 2.7
E37241-02
September 2012
Agile Product Lifecycle Management for Process
Copyright © 1995, 2012 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 software or related documentation 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 RIGHTS Programs, software, databases, and
related documentation and technical data delivered to U.S. Government customers
are "commercial computer software" or "commercial technical
data" pursuant to the applicable Federal Acquisition Regulation and
agency-specific supplemental regulations. As such, the use, duplication, disclosure,
modification, and adaptation shall be subject to the restrictions and license
terms set forth in the applicable Government contract, and, to the extent
applicable by the terms of the Government contract, the additional rights set
forth in FAR 52.227-19, Commercial Computer Software License (December 2007).
Oracle USA, Inc.,
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 Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
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.
Denormalization Process Overview
Custom
Section Denormalization Approaches
Direct-Map Approach: Custom Sections Map Directly to a
Table
Limitation: Extended attribute data types must match
for all rows in a column
Pivot Approach: Custom Sections Pivot onto Table
Limitation: Extended attribute data types must match
for all columns in a row
Limitation: Repeatable rows cannot be included for
denormalization for a Pivot approach
Application
Support for Denormalization
Custom Section Data Admin User
Interface Enhancements
Changes to Denormalization Metadata
Enabling Custom Section Denorm in the Application
Table Creation Script (DENORM_GEN_DDL)
Data Denormalization Script (DENORM_PROC_MAPPING)
Leveraging CS Denorm for Oracle Product Lifecycle
Analytics v3.3.1
Using Denormalized Custom Sections
Custom Section Denormalization (CS Denorm) is a feature that provides the ability to convert the internal data storage of a Custom Section into data structures that are easier to understand and report against while providing improved query performance.
The CS Denorm process allows clients to indicate which Custom Sections (and which rows and columns) they want to denormalize and how the data should be denormalized. The CS Denorm process then reads this information, pulls the relevant Custom Section data from specifications (or other business objects), and populates that data into a newly created database table used solely for that Custom Section.
This approach provides customers with the following benefits:
· Performance: the denormalized data will be accessible via far fewer joins.
a. Without Denormalization, querying for Custom Section data could involve over 20 database tables just for the Custom Section data.
b. Using CS Denorm, simply querying the single new table provides most of that same data needed.
· Accessibility: The context for the data is surfaced to the table structure. This allows third party applications to represent and search the data in a meaningful way.
There are three main steps to the CS Denorm solution: Configuration, Table Creation, and Denormalization.
The Data Administration user interface for Custom Sections has been enhanced to allow users to specify which Custom Sections they want denormalized, which rows and columns should be included, the Denormalization approach, and other details. See Application Support for more details.
A new database script is available that reads the configuration data (populated by the user interface) and dynamically generates SQL code to create each denormalized Custom Section table flagged for denormalization. This is a manual process in which a user with proper database permissions runs the stored procedure and saves the result output as a script to generate the new Custom Section tables.
The script is then be used to generate the Denormalized Custom Section tables.
A new stored procedure is available that processes each Custom Section set up for Denormalization and extracts the relevant data from it (where it is saved on a Specification, Sourcing Approval, etc) and populates that data into the newly created Custom Section database table. A log entry may optionally be written to a new database table to record the execution results of each custom section denormalization.
This stored procedure may be run as an automated process on a predetermined interval (usually nightly) to extract data from Custom Sections and populated it into the new Custom Section tables.
The data from the denormalization metadata tables is combined with the various tables in PLM4P that contain Custom Section and Extended Attribute data and is then pushed into the single table created for the given Custom Section.
When designing each Custom Section and configuring it for denormalization, users must determine how the denormalized table should be created. This decision may ultimately affect the way the Custom Section is set up. In the next section, we discuss the two different denormalization approaches that can be used.
There are two different ways that a Custom Section can be denormalized: Direct-map and Pivot.
This approach converts a given Custom Section into a table structure that mimics the view of a Custom Section in the UI of the application.
Assuming the following Custom Section Template #10001:
|
Column1 |
Column2 |
Column3 |
Row1 |
Numeric
EA+UOM |
Range EA+NoUOM |
Text EA |
Row2 |
Numeric
EA+UOM |
Range EA+NoUOM |
Text EA |
And assuming it appears on a specification (PKID: 1004-32-232ef) like so:
|
Column1 |
Column2 |
Column3 |
Row1 |
5 g |
Min: 2.5 Target: 5 Max: 7.5 |
Hello |
Row2 |
10 g |
Min: 25 Target: 50 Max: 75 |
World |
A table named DENORM_CS_10001 will be created with the following structure:
Column |
Position |
Type |
Nullable? |
fkOwner |
1 |
CHAR (40) |
NO |
ROW_NAME |
2 |
VARCHAR(500) |
NO |
Column1 |
3 |
FLOAT |
YES |
Column1_UOM |
4 |
VARCHAR(32) |
YES |
Column2_Min |
5 |
FLOAT |
YES |
Column2_Target |
6 |
FLOAT |
YES |
Column2_Max |
7 |
FLOAT |
YES |
Column3 |
8 |
FLOAT |
YES |
The Custom Section appearing on the specification will be represented by the following two lines in the newly created denorm table:
fkOwner |
ROW_NAME |
Column1 |
Column1_ |
Column2_ |
Column2_ |
Column2_ |
Column3 |
1004-32-232ef… |
Row1 |
5 |
g |
2.5 |
5 |
7.5 |
Hello |
1004-32-232ef… |
Row2 |
10 |
g |
25 |
50 |
75 |
World |
Note that the name of the rows and columns for the denormalized table can be configured in the Data Admin UI.
Custom Sections allow customers to setup a different Extended Attribute (EA) types in each cell. This means a column could have different data types as you go further down in the column. A Custom Section that contains different EA Type base data types in the same column cannot be de-normalized using this approach as the resulting table would have a column of unknown type.
Given the following Custom Section #1002:
|
Column1 |
Row1 |
Numeric EA+NoUOM |
Row2 |
Text EA |
A table named DENORM_CS_10002 cannot be created as the data type of Column1 is unknown:
Column |
Position |
Type |
Nullable? |
fkOwner |
1 |
CHAR (40) |
NO |
ROW_NAME |
2 |
NVARCHAR(500) |
NO |
Column1 |
3 |
NVARCHAR(max) OR FLOAT |
YES |
NOTE: Some different Extended Attributes types have the same base data type; for instance, a Text and a Qualitative Extended Attribute both have a base type of String, and therefore can be denormalized together. The Data Admin user interface will inform users when there are data type conflicts.
This approach “pivots” a given Custom Section into a table structure where each row turns into a column and each column is represented by a row.
Assuming the following Custom Section Template #10003:
|
Column1 |
Column2 |
Column3 |
Row1 |
Text EA |
Text EA |
Text EA |
Row2 |
Numeric
EA+UOM |
Numeric
EA+UOM |
Numeric
EA+UOM |
And assuming it appears on a specification (PKID: 1004-32-232ee) like so:
|
Column1 |
Column2 |
Column3 |
Row1 |
Hi |
Hello |
Hiyas |
Row2 |
10 g |
15 mg |
20 kg |
A table named DENORM_CS_10003 will be created with the following structure:
Column |
Position |
Type |
Nullable? |
fkOwner |
1 |
CHAR (40) |
NO |
COLUMN_NAME |
2 |
NVARCHAR(500) |
NO |
Row1 |
3 |
NVARCHAR(max) |
YES |
Row2 |
4 |
FLOAT |
YES |
Row2_UOM |
5 |
VARCHAR(32) |
YES |
The data on the Custom Section will be represented thus:
fkOwner |
COLUMN_NAME |
Row1 |
Row2 |
Row2_UOM |
1004-32-232ee |
Column1 |
Hi |
10 |
g |
1004-32-232ee |
Column2 |
Hello |
15 |
mg |
1004-32-232ee |
Column3 |
Hiyas |
20 |
kg |
A Custom Section that contains different EA Types in the same row cannot be de-normalized using this approach as the resulting table would have a column of unknown type.
Given the following Custom section #1002:
|
Column1 |
Column2 |
Row1 |
Numeric EA+NoUOM |
Text EA |
A table named DENORM_CS_10002 cannot be created as the data type of Row1 is unknown:
Column |
Position |
Type |
Nullable? |
fkOwner |
1 |
CHAR (40) |
NO |
COLUMN_NAME |
2 |
NVARCHAR(500) |
NO |
Row1 |
3 |
NVARCHAR(max) OR FLOAT? |
YES |
NOTE: Some different Extended Attributes types have the same base data type; for instance, a Text and a Qualitative Extended Attribute both have a base type of String, and therefore can be denormalized together. The Data Admin user interface will inform users when there are data type conflicts.
A Custom Section that contains repeatable rows cannot be de-normalized using the Pivot approach. When Pivoting, a row turns into a column in the created DB table; since all columns must be defined to generate the CREATE TABLE syntax, repeatable rows would break that schema.
The following Extended Attribute types are considered to have the same base data type (text), and therefore can exist in the same denormalized column:
All other Extended Attribute types are considered to have a unique base data type and therefore cannot be combined in the same denormalization column.
This section describes how the Custom Section data is denormalized for different Extended Attribute types and values
Text, Qualitative, and Qualitative Lookup Extended Attributes are denormalized using the English only values. Multi-select values are listed as one record, in comma delimited format.
When executing the CS Denorm process, there is an option to include a conversion to the base Unit of Measure for an Extended Attribute. When adding numeric data for a Custom Section in the user interface, users may choose different UOM values.
Given the following example in the UI:
|
Column1 |
Row1 |
0.5 g |
Row2 |
1000 mg |
There are two ways of denormalizing the data:
1)
Not
including base UOM values – this would result in a table similar to the UI,
in which the UOM column would keep the UI values:
|
Column1 |
Column1_UOM |
Row1 |
0.5 |
g |
Row2 |
1000 |
mg |
2)
Including
the base UOM values – this would result in a table like above, but also
adds columns for Base UOM support:
|
Column1 |
Column1_UOM |
Column1_BASE |
Column1_UOM_BASE |
Row1 |
0.5 |
g |
.5 |
g |
Row2 |
1000 |
mg |
1 |
g |
Likewise, Quantitative Range and Quantitative Tolerance Extended Attributes also can include Base UOM information for their Min, Max, and Target values.
See the Execution section for details.
When configuring a Qualitative Lookup in Data Admin, an internal PLM4P category, such as Allergens, can be selected. However, the Custom Section Denorm process currently only supports denormalization of the following internal lookup categories:
Alternatively, external lookup categories, as available via Custom Lookups are fully supported.
The CS Denorm process does not include custom section data from questionnaires in EQ. Once a questionnaire is imported into a GSM Specification, however, the custom section data from the spec does get included.
A new Feature Configuration entry has been added to enable the new CS Denorm features:
Portal.DataAdmin.CustomSection.De-normalizationProcess.Enabled
Setting this configuration entry value to true will display
the new denormalization sections in Data Admin. At least one row and one column
will need to be marked for denormalization in the custom section.
The Data Administration of Custom Sections has been enhanced
to allow users to specify which Custom Sections they want denormalized, which
rows and columns should be included, the Denormalization approach, and other
details. The end result of specifying the denormalization details for a Custom
Section is the creation of a new database table. Therefore, new user interface
options allow users to specify the name of the database table and the names of
the database columns that will be created.
A new section titled “De-normalization Process” has been added to the Custom Section data administration screen.
This checkbox marks a Custom Section to be included in the denormalization process, once the Custom Section is made Active.
Un-checking an
already denormalized Custom Section
If a Custom Section has already been denormalized, and then this checkbox is unchecked, then the generated denormalized Custom Section table will no longer be included in the Data Denormalization process. Additionally, the next time the Table Creation process is performed this denormalized Custom Section table will be deleted from the database.
Selects the type of denormalization used for this Custom Section:
This read-only field displays the Custom Section’s current denormalization process status. The numeric value in the parenthesis corresponds to the metadata table DENORM_CS_TABLE status value for reference purposes.
The User Interface uses the Include in De-normalization checkbox and the Custom Section’s status to workflow the denormalization status. That is, the denormalization status depends on the Custom Section status, as follows:
Denorm Checkbox status |
Custom Section Status |
Denorm Status |
Notes |
Unchecked |
any status |
-1: Not for Denormalization |
The Custom Section will not be denormalized |
Checked |
New |
0: Not ready for
denormalization |
Denorm meta data (alias, etc) is stored, but the Custom
Section is ignored by the denorm scripts. Once the Custom Section is made
Active, this status is promoted to a 1. |
Checked |
Active, Archived, Inactive |
1: Ready for Table
generation |
A Denormalized table can now be created for the Custom
Section. Once the table generation scripts are executed, the status is moved
to 2: Ready for denormalization |
Checked |
Active, Archived, Inactive |
2: Ready for denormalization |
Custom Sections in this status will now be picked up in the
Data Denormalization process |
This field determines the table name that will be used for the denormalized Custom Section table. The value must be unique across all Custom Sections, and must be no longer than a configurable number of characters (default is 30). The configuration setting, AliasMaxLength, is in the ExtendedAttributeSectionBuilderService configuration node in the CustomerSettings.config file. See the Configuration Settings section for details.
Section Alias
rules
The section alias value must adhere to database vendor specific rules. For instance, Oracle DB requires table names to be 30 characters or less, while SQL Server does not have such a restriction; certain reserved words cannot be used; etc.
Note that while table names for an Oracle database must be 30 characters or fewer, the CS Denorm process actually creates a version of the table to process the denorm data, which is named “STG_<table alias>”. Therefore, when using an Oracle database, be sure to restrict the Section Alias to 26 characters or fewer. This may be done in two ways: (1) Setting the AliasMaxLength configuration value. However, this would also enforce the alias length restriction on row and column aliases. (2) Adding a Custom Validation for the length of the section alias value.
Custom validation using the Validation Framework can be added to enforce any desired restrictions. See the Validation section below.
Users should take care to avoid name collisions with existing PLM4P internal tables. The table generation stored procedure will indicate if a conflict exists.
Clicking the Section Alias link will copy the Custom Section name into the field, replace any spaces with underscores (‘_’) and trim the name to the configurable max length.
Custom Section cells that are selected for de-normalization will display one of two new icons, at the left of the cell.
·
The “Include in De-normalization” icon will be displayed if both the column and row
for that cell are marked as Include In Denorm, but their statuses are not Ready
For Denormalization (meaning those columns and rows have not been created in
the new denorm table.)
·
The “Denormalized”
icon will display when both the column and row have
been created in the denormalized table.
The legend at the bottom of the grid describes the two new icons.
A new button is now available that performs validation of the Custom Section data and validation of the Denormalization data. This allows users to check the setup of their Custom Sections prior to saving. Warning messages appear in the Warnings grid at the top of the page.
The Validate button also calls to the Validation Framework to see if there are any additional custom validation rules to evaluate. Customers can add their own validation rules for Custom Sections and for the Custom Section Denorm information.
· Validation for Custom Section Data uses rule of type “1020”, and is available for save and validate events in the ValidationSettings.xml file.
· Validation for Custom Section Denorm Data uses rule of type “denorm1020”, and is available for save and validate events in the ValidationSettings.xml file.
· See the Validation Training documentation for more details.
IMPORTANT:
Once a Custom Section has been denormalized, all changes to it are
locked down. See the Security Roles section below for details.
A new section titled “De-normalization Process” has been added to the Row and Column Header Information data admin screen. The Row and Column Popups now include two new fields: Include in De-normalization and De-normalization Alias.
This checkbox marks the Row or Column to be included in the denormalization process, once the Custom Section is made Active.
Un-checking an
already denormalized Row or Column
If a Custom Section has already been denormalized, and then this checkbox is unchecked, then this Row or Column will no longer be included in the Data Denormalization process. The Custom Section’s denormalization status is also workflowed back to Ready for Table Creation so that the next time the Table Generation process executes, the denormalized table will be recreated without this Row or Column.
When the denormalization approach is Direct Map, the De-normalization alias for the Column pop is restricted and must be no longer than a configurable number of characters (default is 30). When the denormalization approach is Pivot, the De-normalization alias for the Row pop (which will get pivotted to a database column) is restricted in the same way.The configuration setting, AliasMaxLength, is in the ExtendedAttributeSectionBuilderService configuration node in the CustomerSettings.config file. See the Configuration Settings section for details.
Row and Column
Alias rules
Note that while column names for an Oracle database must be 30 characters or fewer, the CS Denorm process actually can create additional columns for a given extended attribute type. For instance, a numeric extended attribute that has a UOM will have an additional UOM column added to the resulting table. The name of the column is the denorm alias name followed by one of the following, depending on the EA Type:
<alias>_UOM
– for Unit of Measure
<alias>_TGT – Target
<alias>_MIN – Min
<alias>_MAX – Max
<alias>_TOL – Tolerance
When choosing to denormalized and include BaseUOM values, the denorm alias becomes even longer:
<alias>_UOM_BASE
– for Unit of Measure
<alias>_TGT_BASE – Target
<alias>_MIN_BASE – Min
<alias>_MAX_BASE – Max
Therefore, when using an Oracle database, be sure to restrict the Column or Row Alias to 26 characters or fewer, or 21 characters or fewer, if including BaseUOM values. This may be done in two ways: (1) Setting the AliasMaxLength configuration value. However, this would also enforce the alias length restriction on section, and row/column aliases, so this may be too restrictive. (2) Adding a Custom Validation for the length of the row/column alias value.
Custom validation using the Validation Framework can be added to enforce any desired restrictions. See the Validation section above.
Clicking the De-normalization Alias link will copy the Row or Column name into the field. When the denormalization approach is Direct Map, the copied Column name will replace any spaces with underscores (‘_’) and trim the name to the configurable max length. When the denormalization approach is Pivot, the copied Row name will replace any spaces with underscores (‘_’) and trim the name to the configurable max length.
Note that when using the Pivot
denormalization approach, the name of the
row will become the column name used in the denormalized table, and vice
versa.
Once a custom section has had its denormalized table created and is in a status of Ready for Denormalization, changes to it are locked down (See Security Roles below). If changes to the denormalization configuration are made, the status of the Custom Section denormalization will be reverted, and that Custom Section will stop being included in the denormalization process.
Changes to any of the following fields will revert the denormalization status:
Two new security roles have been added to support the Custom Section Denormalization feature:
The import and export process for Custom Section data now includes any CS Denorm information configured.
When importing CS Denorm data, the Denorm status may be automatically adjusted for the Target environment.
For example, if the Denorm status of a Custom Section is “Ready for denormalization (2)” in the Source Environment, it means that the denormalized table has been generated and the Custom Section’s denormalized table is ready for denormalization data. In the Target environment, however, this may not be the case (e.g., the Custom Section didn’t exist there, and therefore the denormalized table hasn’t been created, etc.).
Therefore, the import process may revert the Denorm Status value back from Ready for denormalization (2) to Ready for Table generation (1), and add notification for that Custom Section to the email message generated on import.
Configuring de-normalization types is controlled in the CustomerSettings.config file, using the ExtendedAttributeSectionBuilderService settings. Permission for each de-normalization approach type can be set using the following options:
· All – All users have access to the de-normalization type
· None – No users have access to the de-normalization type
·
A UGM
Group PKID list, separated by a pipe character (‘|’) – only users in the
specified group(s) have access to the de-normalization type. For example:
<envvar
name="DenormType_NONE_Groups" value="20550b7c241e-ff3c-4cf8-b2bc-2a814195833c|
2055c8000cde-5312-4ef8-910d-f60c4e849874
"/>
will only allow users in the groups identified by these PKIDs to select
None.
Additionally, the maximum length for the Denorm aliases (for the section, columns, and rows) can be declared here using the AliasMaxLength setting.
See the configuration example below.
<ExtendedAttributeSectionBuilderService configChildKey="name">
<envvar
name="MaximumNumberOfRowsThroughCopy"
value="250"/>
<envvar
name="DenormType_PIVOT_Groups"
value="All"/>
<envvar
name="DenormType_NONE_Groups" value="None"/>
<envvar
name="AliasMaxLength"
value="30"/>
</ExtendedAttributeSectionBuilderService>
Note that the denormalization approach of Direct Map is always on, and therefore cannot be configured.
Changes to the CustomerSettings.config file require an IIS reset.
Several database scripts are provided as part of the Extensibility Pack release. All of these scripts must be executed in order to add the various stored procedures and functions to the database.
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 CustomSectionMapping folder within.
All of the scripts in the CustomSectionMapping folder must be compiled, in the order of the folder names.
See the Execution section for details on which scripts must be called to execute the denormalization process.
Add the [Custom_Section_Denorm_Enabler] role to the relevant group(s) and assign that group to the relevant user(s).
Set the feature configuration setting,
Portal.DataAdmin.CustomSection.De-normalizationProcess.Enabled, to true in the
CustomerSettings.config file, in the FeatureConfig node:
<add
key="Portal.DataAdmin.CustomSection.De-normalizationProcess.Enabled"
value="true" configDescription="Enables
the configuration of custom section denormalization" />
Changes to the CustomerSettings.config file require an IISReset.
Once the database scripts have been added to the database, and the Feature Configuration entry has been set to true, the application can be restarted and Custom Sections can be configured for Denormalization in the UI, as described earlier.
When the Custom Section denormalization UI configuration is completed and the Custom Section is made Active, it can be assigned to GSM Specification, SCRM Sourcing Approvals, etc. This custom section data is now available for denormalization, and, at this point, the CS Denorm process can be run to yield results.
Executing CS Denorm involves two functional processes: Table Creation and Data Denormalization. The following section describes these processes by their related database stored procedures.
There are two main stored procedures that are used to execute the CS Denorm process: DENORM_GEN_DDL and DENORM_PROC_MAPPING.
(These stored procedures, in turn, call several other included stored procedures and functions, which are not detailed here as clients should not be calling them directly.)
Executing the DENORM_GEN_DDL stored procedure generates SQL code for creating the denormalized tables for all Custom Section templates that have a status of “Ready for Table generation”. Note that executing this stored procedure does not actually create any tables, but rather generates the SQL code required to generate the tables.
The results of the DENORM_GEN_DDL should be saved and reviewed for any errors that may have been raised. Prior to executing the results of the DENORM_GEN_DDL, the SQL produced should be parsed (in SQL Server, the checkmark button next to the Execute button.) or compiled.
The text returned by the stored procedure must be executed by someone that has CREATE TABLE and DROP TABLE privileges in the target database.
Please note that this stored procedure does not create any indexes on the table being generated as that is dependent on data (amount, type, variance, etc) and searching (frequency, type of operators, etc) characteristics which are highly specific to a customer’s environment.
The stored procedure has one optional input parameter: a Boolean value indicating if the table generation should include BASE_UOM columns.
For example:
EXEC
DENORM_GEN_DDL 0
Generates the SQL to create the denormalized tables but will not include columns for BASE_UOM values.
EXEC
DENORM_GEN_DDL 1
Generates the SQL to create the denormalized tables and will include columns for BASE_UOM values
** SQL Server Users – be
sure to generate the results of this stored procedure to text (not grid) and
increase the setting for “Maximum number of characters displayed in each
column” to 8192. This setting is found in the Tools > Options popup window,
under Query Results-> SQL Server -> Results to Text.
The DENORM_GEN_DDL stored procedure produces SQL code that is used to create two tables for each denormalized custom section:
1. The target denormalization table, as specified by the section alias field in the UI
2. A copy of that target table with an “STG_” prefix. This “STG_” table is later used in the denormalization process to store and process updated records prior to inserting those records into the final target table.
The table name is evaluated to determine if it conflicts with any of the Agile PLM for Process internal tables. If a conflict exists, the process terminates and an error is raised:
***ERROR*** Denormalization Table Name <table name> is
an Internal PLM4P table, and is not allowed! Table skipped.
When this error occurs, the table generation process is terminated. Additionally, and error is logged to the DENORM_LOG table with a value of “CS Denorm Table Generation ERROR” in the MODULE column.
See the DENORM_PROC_MAPPING Implementation Details section for more.
Customers can perform additional validations on the table names (or any property of the CS Denorm custom section information), if desired, by using the Validation Framework. This would provide the user configuring the custom section immediate feedback in the UI.
This section describes some of the technical details of
the table generation process, in order to provide some transparency and insight
into the process. This level of detail is not required to run the CS Denorm
process, but may be useful to better understand performance characteristics or to
troubleshoot the process if any errors occur.
The table generation process works as follows:
Determines which custom sections to process
The DENORM_GEN_DDL stored procedure first retrieves all custom section entries with a status of 1 (Ready for table generation) from the DENORM_CS_TABLE table, and then produces SQL code to generate CREATE TABLE commands for each Custom Section table, one at a time.
For each Custom Section table, it does the following:
Ensures the table name is not an Agile PLM4P Table (see the Table Name Conflicts section above for details)
Performs a last stage validation
Evaluates the rows and columns for the current custom section to ensure the custom section rows and columns are using data type consistency, as performed in the UI.
Constructs the CREATE TABLE SQL statements
The columns for direct map or rows for pivot (from the denorm_cs_column and denorm_cs_row tables) are examined to create the CREATE TABLE SQL command, used to create the denormalized custom section table and the individual columns. All rows and columns with a denormalization status of Ready for Table Generation or Ready for Denormalization are included. The CREATE TABLE command is preceded by a DROP TABLE which will be called if the custom section table already exists.
Updates the denormalization status
The SQL code generated also includes statements to update the denormalization status of the custom section, rows, and columns, to the status of Ready for Denormalization (2)
Includes Base UOM columns
If the INCLUDE_BASE_UOM input parameter is set to 1 (true), then the table creation scripts also include columns for the base value(s) and base UOM entries.
Prints the resulting SQL commands
Once all custom sections that had a “Ready for Table Generation” denormalization status have been processed, the table generation process:
Constructs DROP TABLE statements
Previously denormalized custom sections that are have generated their denormalization table but are now marked with a Not for Denormalization (-1) status are included as DROP TABLE commands.
Executing the DENORM_PROC_MAPPING stored procedure will perform the denormalization of Custom Section data from the various internal PLM4P tables into the newly created denorm tables created by the DENORM_GEN_DDL results. The stored procedure processes any Custom Section templates that have a status of “Ready for Denormalization”, and includes all of that Custom Section’s Rows and Columns that have that same denorm status.
The stored procedure will perform a “delta” denormalization, retaining the existing records and adding any records that may have been updated since the last denormalization time.
The stored procedure has three optional input parameters:
0:
(default) No debug statements are printed.
1: some minor debug
statements are printed indicate general status messages.
2: very detailed debug statements are printed that includes the SQL that is being created and executed to denormalize the data. This setting should only be used if unexpected errors occur and their output will be helpful to Customer Support.
0:
(default) No log entries are added.
1: will log each Custom Section
being denormalized
2:
same as 1, but adds a log message for any skipped Custom Sections. Custom
sections in which none of its owning objects (specification, sourcing approval,
etc) have been updated since the last denorm execution will be skipped.
The number of rows written to this table will depend on the number of custom
sections being denormalized, and the frequency of denormalization.
0:
(default) Base UOM data not is
denormalized.
1: Base UOM data is
denormalized.
For example:
EXEC
DENORM_PROC_MAPPING 2,2,0
will run the CS Denormalization for all Custom Section templates (in a Ready for Denorm status), printing out all debug statements, adding all logging statements to the DENORM_LOG table, and will ignore Base UOM data.
The DENORM_PROC_MAPPING stored procedure can be run manually, or be scheduled to run on a recurring basis using the Database server tools (such as SQL Server Agent for SQL Server).
Note that performance
can be greatly improved by increasing the number of processors and RAM
available to the database server.
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 very significant impact on runtime performance
of the CS Denorm process.
Additionally, the first run of denormalization for a Custom Section will
take the longest time, but subsequent denormalization runs only pull in the
changes since the last denormalization run.
Running the Denorm process manually for a limited number of Custom Sections is recommended to monitor the process initially.
This section describes some of the technical details of
the denormalization process, in order to provide some transparency and insight
into the process. This level of detail is not required to run the CS Denorm
process, but may be useful to better understand performance characteristics or to
troubleshoot the process if any errors occur.
The denormalization process works as follows:
Determines which custom sections to process
The DENORM_PROC_MAPPING stored procedure first retrieves all entries with a status of 2 (Ready for denormalization) from the DENORM_CS_TABLE table, and then denormalizes each Custom Section table, one at a time.
For each Custom Section table, it does the following:
Removes any relevant denormalized Custom Section data
If Custom Section data for a business object has previously been denormalized, but that Custom Section is then deleted from the business object, the CS Denorm process removes the relevant records from the target table.
Determines which business objects to process
Populates the temporary tables
A temporary table is created for each column grouping in the denormalized table:
Data from the relevant business objects for the column grouping is then pulled into these temporary tables. A significant process of the denormalization process can occurs at this stage.
Combines data from the temporary tables into the staging
table
Once all temporary tables have been created and populated for a custom section, any old data is deleted from the staging table. Next, data from the temporary tables is joined and copied into the denormalized staging (“STG_<alias>”) table.
Processing time of this step will be based on the number of denormalized columns and the amount of data.
The temporary tables are then deleted.
Copies Denormalized data into the final table
At this stage, the denormalization processing is complete, and the updates from the Staging table are ready to be copied into the final denormalization table. First, however, data is deleted from the final denormalization table for business objects that have been modified since the last run time. Then the data from the Staging table is inserted into the final denormalization table.
The next custom section that is ready for denormalization is then processed.
*Modification Dates: A modification to a business object may occur from various changes, some of which may have nothing to do with the actual custom section data. However, the CS Denorm process considers any change to a business object as one that requires the Custom Section denormalization to be regenerated.
The DENORM_LOG table is populated with logging messages from the denormalization process. This table can therefore be monitored for status.
When an error is encountered during de-normalization of a Custom Section template, no changes for that particular Custom Section template will be applied and a row will be written to the DENORM_LOG table. Any errors that occur will be logged with a value of “CS Denorm ERROR” in the MODULE column, and the error details in the MSG column.
Customers that require other notification channels (e.g., email) may enable them programmatically by attaching an INSERT trigger to the DENORM_LOG table, as needed.
This section describes how to customize and extend the OPLA PLM4P solution to contain data from CS-Denorm.
Audience: This document is intended for data warehouse administrators who are familiar with Agile PLM4P or Agile PLM4P administrators who have database warehousing skills.
The Oracle Product Lifecycle Analytics product comes with many pre-configured OBIEE Answers and Dashboards. It also has provisions for selectively extending those solutions with Agile PLM4P Custom Sections.
The general solution for all areas of extension is characterized by an initial setup which is then used by the overall ETL process every time data is transferred. Specifically that initial setup is:
Custom-data mappings are created to specific pre-defined user-data columns in the staging schema tables
OBIEE Administrator is used to:
Once Custom Sections have been denormalized via the CS Denorm process, the denormalized tables can be imported into the OPLA solution as follows:
ETL: The process by which the data is moved from the source system to the target system. This data includes any optional CS denormalized data. For this to work:
ETL Setup: The Scripts/Oracle/BISupport/DM_
OPLA setup: The manual configuration via OBIEE Administrator of: