Go to primary content
Oracle Product Lifecycle Analytics Configurator and Data Mapping Guide
Release 3.5
E70277-01
  Go To Table Of Contents
Contents

Previous
Previous
 
Next
Next
 

4 Extending the Agile PLM for Process Data

4.1 General Solution

For all areas of extension the general solution is characterized by an initial setup. This setup is then used by the overall ETL process every time data is transferred. The initial setup comprises of the following:

  • In the staging schema tables, custom-data mappings are created to specific predefined user-data columns.

  • Use the OBIEE Administrator to:

    • Customize and add the MDS user-defined columns to the already existing Physical Layer tables, Logical/Business Layer objects, and Presentation Layer objects within a given Subject Area.

    • Customize the names of these columns and fields.

If there are any errors in the data in the mapping tables (for example, non-existent NPD Project Metrics, non-existent Extended Attributes, and/or misnamed target DB tables or columns, and so on.) they will cause the overall ETL run to fail. Error messages are logged in the TLOG table.


Important:

You must conduct a FULL extract run if you make any content changes to the P4P_CONFIG_EA table or to the P4P_METRIC_CONFIG_MAP table. A CDC (incremental) run does not pick up any changes made to the tables.

4.2 What's Extensible in Agile PLM for Process

There are three key data elements from Agile PLM for Process to the Presentation layer:

  1. Core Attributes (Not Configurable): Are attributes available from the Agile PLM for Process out-of-the-box solution. In Oracle Product Lifecycle Analytics (Oracle PLA) only the relevant attributes are brought out as measures and dimensions.

  2. Extended Attributes (Configurable): In order to capture additional information - beyond core attributes, Agile PLM for Process allows you to add extra (extended) attributes. Oracle PLA provides configuration and customization steps that bring out dimension and measure fields.

    Figure 4-1 Extended Attributes

    Surrounding text describes Figure 4-1 .
  3. Project Metrics (Configurable): Project metrics are definable in Agile PLM for Process. Project metrics allow you to capture information related to a project (for example, cost, revenue forecasts, and so on). Based on whether the metrics have basis, financial year, or category information, the measures are simple or complex. OPLA provides configuration and customization steps to bring out the fields as measures (but not as dimensions).

    Figure 4-2 Project Metrics

    Surrounding text describes Figure 4-2 .

Important:

OPLA does not support Custom Sections.

4.3 NPD Project Metrics


Important:

Any changes to the content of the P4P_CONFIG_EA table or the P4P_METRIC_CONFIG_MAP table must be followed by a FULL extract run. A CDC run will not pick up these changes.

Oracle Product Lifecycle Analytics (OPLA) delivers two out-of-the-box metrics that correspond to Project Metrics in Agile PLM for Process:

  • Three year NPV

  • Annualized case volume

These metrics flow through the prebuilt ETL and mappings all the way to the presentation layer, provided certain naming conventions are adhered to. Even if the naming convention followed is different, you can replace names of these metrics via a custom script.

Simple Metrics are Project Metrics that have no phase, no basis, no category, and no fiscal year. These measures have to be mapped to the Project Summary fact. This mapping can be created using the custom script between Source to Staging. From Staging to MDS, prebuilt mapping & ETL exist. You need to customize the OBIEE RPD to expose the metric as a measure in the presentation layer.

Complex metrics are Project Metrics that are dimensionalized and have a phase, basis, category, or fiscal year. These measures have to be mapped to the Project Metrics fact (unlike Simple metrics). Other than that the process of customization/ configuration is similar to that of simple metrics.


Important:

The Project Metrics cannot be exposed as a dimension in the Presentation layer. The only dimensions that are supported for Complex metrics are Phase, Basis, Category and Fiscal Year.

4.3.1 Mapping the NPD Project Metrics

There are three basic types of NPD Project Metrics mappings:

  1. System-Defined replacements

  2. Simple (dimensionless) User-Defined additions

  3. Dimensionalized User-Defined additions

All types are mapped via inserting the name of the metric and the table and column to map it to into the meta-data table named: P4P_METRIC_CONFIG_MAP. Data is then transferred to the targeted table at ETL time for each NPD Project that contains that given named metric. Projects that do not have a value for that metric will have that column left "null".

The table below defines the P4P_METRIC_CONFIG_MAP metrics mapping database table.

COLUMN DATA TYPE DESCRIPTION
METRIC_NAME varchar2(200) The NPD project metric ID.
TABLE_NAME varchar2(40) The destination table name. One of the following:

P4P_PRJ_SUM_FS

P4P_PRJ_DET_FS

P4P_PRJ_METRICS_FS

P4P_PROJECT_DS

COLUMN_NAME varchar2(100) The destination column name (for example, PRJ_USRDEF_DATE2, PRJ_USRDEF_LIST8, etc.)

The TABLE_NAME is dependent upon the preferred BI Subject Area and the metric's data type. Use the table below to select the TABLE_NAME.

TABLE NAME BI SUBJECT AREA DATA TYPE EXPLANATION
P4P_PRJ_SUM_FS Project Summary Numeric The available Project Summary FACT table column for numeric metrics.
P4P_PRJ_DET_FS Project Detail Numeric The available Project Detail FACT table column for numeric metrics.
P4P_PRJ_METRICS_FS Project Metrics Numeric The available Project Metrics FACT table column for numeric metrics.
P4P_PROJECT_DS ALL of the above Text The available Project DIMENSION table column for text metrics.

The COLUMN_NAME is dependent upon the TABLE_NAME you choose.

If you chose P4P_PRJ_SUM_FS as your TABLE_NAME, then use the table below to select the COLUMN_NAME.

COLUMN_NAME DATA TYPE DESCRIPTION
THREE_YR_NPV, ANNUAL_CASE_VOL NUMBER(22, 7) Only available in the P4P_PRJ_SUM_FS table.

Initially mapped to the simple (fully-qualified metric names) of THREE_YR_NPV_NOB_NOPH_NOFY and ANNUALIZED_VOLUME_CASES_NOB_NOPH_NOFY. However you can also override them as well. See examples.

These metrics are available already in the OBIEE Answers list under Project Summary subject area, Agile PLM for Process Metrics Measures as "3 Year NPV (No Basis, No Phase, No Year)" and "Annualized Case Volume (No Basis, No Phase, No Year)", so they will not have to be enabled via the Administrator like the USRDEF columns.

PRJ_USRDEF_COST0 - PRJ_USRDEF_COST NUMBER(22, 7) 10 user-defined decimal fields
PRJ_USRDEF_DATE0 - PRJ_USRDEF_DATE4 NUMBER(10) 5 user-defined date columns, formatted as: YYYYMMDD
PRJ_USRDEF_NUMBER0 - PRJ_USRDEF_NUMBER9 NUMBER(10) 10 user-defined integer fields

If you chose P4P_PRJ_DET_FS as your TABLE_NAME, then use the table below to select the COLUMN_NAME.

COLUMN_NAME DATA TYPE DESCRIPTION
PRJ_USRDEF_COST0 - PRJ_USRDEF_COST NUMBER(22, 7) 10 user-defined decimal fields
PRJ_USRDEF_DATE0 - PRJ_USRDEF_DATE4 NUMBER(10) 5 user-defined date columns, formatted as: YYYYMMDD
PRJ_USRDEF_NUMBER0 - PRJ_USRDEF_NUMBER9 NUMBER(10) 10 user-defined integer fields

If you chose P4P_PRJ_METRICS_FS as your TABLE_NAME, then use the table below to select the COLUMN_NAME.

COLUMN_NAME DATA TYPE DESCRIPTION
THREE_YR_NPV OR ANNUAL_CASE_VOLUME NUMBER(22, 7) These are not initially pre-mapped to any metrics. However they are available already in the OBIEE Answers list under the Project Summary subject area, P4P Metrics Measures as "3 Year NPV" and "Annualized Case Volume", so they will not have to be enabled via the OBIEE Administrator like the USRDEF columns.
USRDEF_COST0 - USRDEF_COST9 NUMBER(22, 7) 10 user-defined decimal fields
USRDEF_DATE0 - USRDEF_DATE4 NUMBER(10) 5 user-defined date columns, formatted as: YYYYMMDD
USRDEF_NUMBER0 - USRDEF_NUMBER9 NUMBER(10) 10 user-defined integer fields


Note:

The P4P_PRJ_METRICS_FS table is only for the mapping of Base-Metric-Name metrics.

If you chose P4P_PROJECT_DS as your TABLE_NAME, then use the table below to select the COLUMN_NAME.

COLUMN_NAME DATA TYPE DESCRIPTION
PRJ_USRDEF_LIST0 - PRJ_USRDEF_LIST14 VARCHAR2(1000 CHAR) 15 user-defined text fields (for example. for multi-value comma-delimited text lists)
PRJ_USRDEF_TEXT0 - PRJ_USRDEF_TEXT9 VARCHAR2(450 CHAR) 10 user-defined text fields

4.3.2 System-Defined NPD Project Metric Replacements

A System-Defined NPD Project Metric does NOT require any use of the OBIEE Administrator tool. System-Defined NPD Project Metrics are metrics where the predefined BI analytics mapping (from physical to logical to presentation) have been rationalized in the Project Summary Subject Area of the OBIEE Answers section.

There are two System-Defined NPD Project Metrics:

  1. 3 Year NPV (pre-mapped metric ID: THREE_YR_NPV_NOB_NOPH_NOFY)

  2. Annualized Case Volume (pre-mapped metric ID: ANNUALIZED_VOLUME_CASES_NOB_NOPH_NOFY)

The concepts for the above System-Defined NPD Project Metrics are common, but their names are not standard across customers.

For this reason, Oracle allows you to remap (replace) these metrics. For example, replace THREE_YR_NPV_NOB_NOPH_NOFY with THREE_YR_NPV_ABS_PREL_FY10.

Example: SQL script for replacing the 3 Year NPV metric

insert into P4P_METRIC_CONFIG_MAP (metric_name, table_name, column_name) values ('THREE_YR_NPV_ABS_PREL_FY10', 'P4P_PRJ_SUM_FS', 'THREE_YR_NPV');
(Ea_Denorm_Table_Name, Ea_Denorm_Column_Name, Ea_Select_Value, Attribute_Id, Owning_Object_Type, Dest_Table_Name, Dest_Column_Name)
insert into P4P_METRIC_CONFIG_MAP (metric_name, table_name, column_name) values ('BIGCORP_METRIC_AN_CS_VOL', 'P4P_PRJ_SUM_FS', 'ANNUAL_CASE_VOL');

Example: SQL script for replacing the Annualized Case Volume metric

4.3.3 Simple (dimension-less) User-Defined NPD Project Metric Additions

Simple (dimension-less) user-defined NPD project metric additions are simple metrics for which no previous BI Analytics exists.

Example SQL script for adding the "Project Cost" metric to an available user-defined cost column in the Project Detail Subject Area:

insert into P4P_METRIC_CONFIG_MAP (metric_name, table_name, column_name) values ('PROJECT_COST_NOB_POST_FY09', 'P4P_PRJ_DET_FS', 'PRJ_USRDEF_COST1');

Note:

To view the data in the Answers section, in OBIEE Administrator, enable the PRJ_USRDEF_COST1 column from the Project Detail Subject Area.

4.3.4 Dimensionalized User-Defined NPD Project Metric-Set Additions

Dimensionalized User-Defined NPD Project Metric-Set Additions are metrics that allow you to analyze its different values (based on the intersection of the different dimensions that have defined it).

The different possible dimensions are:

  • Fiscal Year

  • Basis

  • Phase

  • Category

When you specify the METRIC_NAME of this type, you should only specify the base or prefix of the ID. For example, a metric with a base name of CONTRIBUTION; Fiscal Year values of FY08, FY09, and FY10; Phase values of Pre-Launch, Launch and Post-Launch; and Basis value of Absolute; it would have the following nine intersections of its dimensions:

  • CONTRIBUTION_ABS_PREL_FY08

  • CONTRIBUTION_ABS_PREL_FY09

  • CONTRIBUTION_ABS_PREL_FY10

  • CONTRIBUTION_ABS_LNCH_FY08

  • CONTRIBUTION_ABS_LNCH_FY09

  • CONTRIBUTION_ABS_LNCH_FY10

  • CONTRIBUTION_ABS_POST_FY08

  • CONTRIBUTION_ABS_POST_FY09

  • CONTRIBUTION_ABS_POST_FY10

Example SQL script for adding this CONTRIBUTION metric-set to the Project Metrics Fact group:

insert into P4P_METRIC_CONFIG_MAP (metric_name, table_name, column_name) values ('CONTRIBUTION', 'P4P_PRJ_METRICS_FS', 'USRDEF_COST4');

4.4 Extended Attributes

Extended Attributes (EAs) are optional custom-data feature available in Agile PLM for Process. EAs exist on both GSM Specifications and NPD Projects.


Warning:

If you install the optional Extended Attributes Denormalization feature on the PLM4P database AFTER you have already installed OPLA, then you need to rerun the appropriate db-type PLM4P source views script (that is, AllinOneViews.sql for SQL-SERVER source DB, and OracleAllInOneViews.sql for Oracle source DB) for any P4P_CONFIG_EA mappings to work.

Only distinct EAs can be denormalized, and therefore are the only candidates for mapping into BI. Extended Attributes may require prebuilt content, mapping, and customization.

Prebuilt mappings and ETL exist between:

  1. Source to Staging

  2. Staging to MDS

After you have identified the Extended Attributes to bring in to OPLA for analysis, you must map them between the Source to Staging. This is done by using a custom mapping script. After you have created the mapping and run the ETL this data flows from Source to Staging. The data then moves to MDS using prebuilt mappings between Staging to MDS. You must customize OBIEE RPD in order for the extended attributes to be exposed as a measure or a dimension in the presentation layer.

4.4.1 EAs Process Steps

The steps for getting an EA into the BI solution are as follows:

  1. EA Denormalization: the automated process where EAs are extracted into denormalization tables, for example, DENORM_EA_NUMERIC and DENORM_EA_TEXT.

  2. Mapping: The manual configuration and mapping of which EAs to extract from the denormalized data into specific BI Subject Areas.

  3. ETL: The automated process by which the data is moved from the source system to the target system. This data includes any optional EA denormalized data. This process also transfers specific EAs per the above Mapping step into the desired location(s).

  4. Enabling: The manual configuration via OBIEE Administrator of the enabling of the targeted tables and columns in the desired Subject Area in the Answers section.

4.4.2 EA Mapping Table and Column Choices

  1. The extended attribute mapping table (P4P_CONFIG_EA) is defined as follows:

    COLUMN NAME DB-TYPE DESCRIPTION
    EA_DENORM_TABLE_NAME VARCHAR2(256 CHAR) Source EA denorm table name
    EA_DENORM_COLUMN_NAME VARCHAR2(256 CHAR) Source EA denorm column name
    ATTRIBUTE_ID VARCHAR2(80 CHAR) Source EA ID
    OWNING_OBJECT_TYPE VARCHAR2(256 CHAR) Source Owning object type (that is, GSM or NPD)
    DEST_TABLE_NAME VARCHAR2(256 CHAR) Destination table name
    DEST_COLUMN_NAME VARCHAR2(256 CHAR) Destination column name

  2. 2. The EA_DENORM_TABLE_NAME and EA_DENORM_COLUMN_NAME depend on the type of the EA data. Specifically:

    EA Data-Type DENORM_TABLE_NAME DENORM_COLUMN_NAME
    Boolean P4P_DENORM_EA_BOOLEAN VALUE
    Quantitative Range P4P_DENORM_EA_QUANTITATIVERANGE MIN

    TARGET

    MAX

    MINBASE

    TARGETBASE

    MAXBASE

    UOM

    UOMBASE

    MINPRECISION

    TARGETPRECISION

    MAXPRECISION

    Quantitative Tolerance P4P_DENORM_EA_QUANTTOLERANCE VALUE

    TOLERANCE

    UOM

    VALUEBASE

    UOMBASE

    VALUEPRECISION

    TOLERANCEPRECISION

    Date P4P_DENORM_EA_DATE VALUE
    Text P4P_DENORM_EA_TEXT VALUE
    Number P4P_DENORM_EA_NUMERIC VALUE

    UOM

    VALUEBASE

    UOMBASE

    PRECISION


  3. The destination table name (DEST_TABLE_NAME) to choose depends on the desired owning object type (OWNING_OBJECT_TYPE), Subject Area, and EA data type. Specifically:

    OBJECT TYPE SUBJECT AREA & DATA TYPE TABLE NAME
    GSM Specification numeric P4P_SPEC_FS
    Specification text P4P_SPEC_DS
    NPD Project Summary numeric P4P_PRJ_SUM_FS
    Project Detail numeric P4P_PRJ_DET_FS
    Project text P4P_PROJECT_DS

  4. The COLUMN_NAME to choose depends on the TABLE_NAME chosen as follows:

    1. P4P_SPEC_FS (the available GSM Specification FACT table columns for numeric EAs)

      COLUMN_NAME DB-TYPE DESCRIPTION
      SPEC_USRDEF_COST0 to SPEC_USRDEF_COST9 NUMBER(22, 7) 10 user-defined decimal fields
      SPEC _USRDEF_DATE0 to SPEC _USRDEF_DATE4 NUMBER(10) 5 user-defined date columns, formatted as: YYYYMMDD
      SPEC _USRDEF_NUMBER0 to SPEC _USRDEF_NUMBER9 NUMBER(10) 10 user-defined integer fields

    2. P4P_SPEC_DS (the available GSM Specification DIMENSION table columns for text EAs)

      COLUMN_NAME DB-TYPE DESCRIPTION
      SPEC_USRDEF_TEXT0 to SPEC_USRDEF_TEXT9 VARCHAR2(450 CHAR) 10 user-defined text fields

    3. P4P_PRJ_SUM_FS, P4P_PRJ_DET_FS (the available NPD Project Summary and Project Detail FACT table columns for numeric EAs)

      COLUMN_NAME DB-TYPE DESCRIPTION
      PRJ_USRDEF_COST0 to PRJ_USRDEF_COST9 NUMBER(22, 7) 10 user-defined decimal fields
      PRJ_USRDEF_DATE0 to PRJ_USRDEF_DATE4 NUMBER(10) 5 user-defined date columns, formatted as: YYYYMMDD
      PRJ_USRDEF_NUMBER0 to PRJ_USRDEF_NUMBER9 NUMBER(10) 10 user-defined integer fields

    4. P4P_PROJECT_DS (the available NPD Project DIMENSION table columns for text EAs)

      COLUMN_NAME DB-TYPE DESCRIPTION
      PRJ_USRDEF_LIST0 to PRJ_USRDEF_LIST14 VARCHAR2(1000 CHAR) 15 user-defined text fields (for multi-value comma-delimited text lists)
      PRJ_USRDEF_TEXT0 to PRJ_USRDEF_TEXT9 VARCHAR2(450 CHAR) 10 user-defined text fields

4.4.3 EA Example

Example SQL script for adding the distinct numeric EA named FPCalciumOverride to an available user-defined cost column in the Specification Subject Area:

insert into P4P_CONFIG_EA
Values ( 'P4P_DENORM_EA_NUMERIC', 'VALUE', 'VALUE', 'FPCalciumOverride', 'GSM Spec', 'P4P_SPEC_FS', 'SPEC_USRDEF_NUMBER3');