| Oracle Product Lifecycle Analytics Configurator and Data Mapping Guide Release 3.5 E70277-01 |
|
 Previous |
 Next |
| Oracle Product Lifecycle Analytics Configurator and Data Mapping Guide Release 3.5 E70277-01 |
|
Previous |
Next |
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. |
There are three key data elements from Agile PLM for Process to the Presentation layer:
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.
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.
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).
|
Important: OPLA does not support Custom Sections. |
|
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. |
There are three basic types of NPD Project Metrics mappings:
System-Defined replacements
Simple (dimensionless) User-Defined additions
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 |
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:
3 Year NPV (pre-mapped metric ID: THREE_YR_NPV_NOB_NOPH_NOFY)
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
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. |
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');
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:
Source to Staging
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.
The steps for getting an EA into the BI solution are as follows:
EA Denormalization: the automated process where EAs are extracted into denormalization tables, for example, DENORM_EA_NUMERIC and DENORM_EA_TEXT.
Mapping: The manual configuration and mapping of which EAs to extract from the denormalized data into specific BI Subject Areas.
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).
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.
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. 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 |
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 |
The COLUMN_NAME to choose depends on the TABLE_NAME chosen as follows:
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 |
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 |
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 |
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 |
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');
