11 Environment Propagation

One of the important features of Oracle Product Lifecycle Analytics is the Configurator, which supports mapping of source fields to Multi-Dimensional Schema tables and columns to enable report relevant data. These mapping details or metadata changes have to be propagated from one environment to another environment during deployment to ensure the configurations are the same on both of the source PLM systems.

If the Agile PLM configuration is the same in both environments, the propagation of source column mappings to the MDS schema from the first environment to the second environment is supported. An ACP-migrated Agile PLM configuration is not supported.

11.1 Environment Propagation Process

The steps to propagate the environment are as follows:

Create the Agile PLM configuration in environment 1.
Run ODM ETL, perform the configuration mappings, then run MDS ETL in environment 1.
Verify that the Agile PLM configurations are the same in both environment's source systems.
Follow the ODM Propagation steps to propagate the Flexcols metadata.
Run ODM ETL in environment 2.
Follow the MDS Propagation on page 77 steps to propagate the Configurator mappings.
Run MDS ETL in environment 2.

11.2 ODM Propagation

To propagate the ODM configurations:

Connect the ODM user in environment 1 and run the following SQL statements:

CREATE TABLE ETL_PARAMETER_BACKUP AS SELECT stage, MDS FULL_LOAD, PQM, PC, PPM, PCM, PGC, FISCAL_START_DATE, fiscal_year_offset, snapshot_freq, snapshot_freq_type, calendar_type, eco_wkly_aggr,since FROM ETL_PARAMETER; CREATE TABLE ODM_CLASS_METADATA_BACKUP AS SELECT sc.subclass_id, sc.subclass, dd.tgt_table p3_view FROM odm_class_metadata sc, (SELECT DISTINCT tgt_table, subclass_id FROM odm_data_dictionary WHERE tgt_table LIKE '%_P3' ) dd WHERE sc-subclass_id=dd.subclass_id;
Export the following tables:

a. ETL_PARAMETER_BACKUP

b. ODM_FLEXCOLS_METADATA (Export as INSERT statements)

c. ODM_CLASS_METADATA_BACKUP
Connect the ODM user in environment 2, then perform the following steps:

a. Import the table ETL_PARAMETER_BACKUP and run the following SQL statement:

MERGE INTO ETL_PARAMETER p2 USING ETL_PARAMETER_BACKUP p1 ON (1=1) WHEN MATCHED THEN UPDATE SET p2.stage =p1.stage, p2.MDS =p1.MDS, p2.FULL_LOAD =p1.FULL_LOAD, p2.PQM =p1.PQM, p2.PC =p1,PC, p2.PPM =p1.PPM, p2.PCM =p1.PCM, p2.PGC =p1.PGC, p2.FISCAL_START_DATE =p1.FISCAL_START_DATE, p2.fiscal_year_offset =p1.fiscal_year_offset, p2.snapshot_freq =p1.snapshot_freq_type, p2.calendar_type =p1.calendar_type, p2.eco_wkly_aggr_since =p1.eco_wkly_aggr_since;

b. Truncate the ODM_FLEXCOLS_METADATA table.

c. Run the INSERT statements generated from ODM_FLEXCOLS_METADATA in environment 1.

11.3 MDS Propagation

To propagate the configurator mappings:

Connect the MDS user in environment 1 and run the following SQL statements:

CREATE TABLE BI_DATA_DICTIONARY_BACKUP AS SELECT * FRP, BI_DATA_DICTIONARY WHERE is_conf=1 AND SRC_TABLE IS NOT NULL AND SRC_COL IS NOT NULL;
Export the following tables:

Ÿ PPM_ACTIVITY_DOMAINS (Export as INSERT statements)

Ÿ BI_DATA_DICTIONARY_BACKUP
Connect the MDS user in environment 2 and perform the following steps:

a. Import the ODM_CLASS_METADATA_BACKUP table.

b. Import the BI_DATA_DICTIONARY_BACKUP table.

c. Truncate the PPM_ACTIVITY_DOMAINS table.

d. Run the INSERT statements generated from PPM_ACTIVITY_DOMAINS in environment 1.
Run the following SQL statements to update the Configured Columns:

MERGE INTO BI_DATA_DICTIONARY B2
USING BI_DATA_DICTIONARY_BACKUP B1
ON (b2.is_conf=1 AND b2.tgt_table=b1.tgt_table
AND b2.tgt_col=b1.tgt_col)
WHEN matched THEN
UPDATE
set
b2.att_id =b1.att_id,
B2.SRC_TABLE =B1.SRC_TABLE,
b2.src_col =b1.src_col,
B2.TGT_DIM_TABLE =B1.TGT_DIM_TABLE,
B2.TGT_DIM_COL_NAME =B1.TGT_DIM_COL_NAME,
B2.TGT_MAP_TABLE =B1.TGT_MAP_TABLE,
B2.TGT_MAP_DIM_COL_NAME =B1.TGT_MAP_DIM_COL_NAME,
B2.LIST_ID =B1.LIST_ID,
B2.MASTER_LIST_ID =B1.MASTER_LIST_ID,
b2.class_id =b1.class_id;�
Run the following script to update the List ID in the BI_DATA_DICTIONARY table:

UPDATE BI_DATA_DICTIONARY A
SET list_id=
(SELECT SELECTION_ID
FROM ODM_ATTR_METADATA
WHERE CUST_ATT_ID=A.ATT_ID
AND class_id =a.class_id
)
WHERE IS_CONF=1 ;�
Run the following script to update the PPM_ACTIVITY_DOMAINS table with the updated subclass ID:

UPDATE PPM_ACTIVITY_DOMAINS ACT SET subclass_wid= (SELECT SUBCLASS_ID FROM ODM_CLASS_METADATA WHERE SUBCLASS= act.subclass_name);
Run the following script to create a Subclass Views lookup table:

CREATE TABLE bi_subclass_view_lkp AS SELECT sc.subclass, acp.subclass_id AS old_subclass_id, sc.subclass_id AS new_subclass_id, acp.p3_view AS p3_view_old, dd.tgt_table AS p3_view_new FROM ODM_CLASS_METADATA_BACKUP acp, odm_class_metadata sc, (SELECT DISTINCT tgt_table, subclass_id FROM odm_data_dictionary WHERE tgt_table LIKE '%_P3' ) dd WHERE acp.subclass = sc.subclass AND sc.subclass_id = dd.subclass_id;
Run the following script to update the P3 View in the BI_DATA_DICTIONARY table:

UPDATE bi_data_dictionary bi SET (bi.src_table) = (SELECT lkp.p3_view_new FROM bi_subclass_view_lkp lkp WHERE lkp.p3_view_old = bi.src_table ) WHERE EXISTS (SELECT 1 FROM bi_subclass_view_lkp lkp WHERE lkp.p3_view_old = bi.src_table );
Commit the changes.

Commit;

11.3.1 PPM Snapshot Data

Perform the following steps to propagate PPM snapshot data to your new environment:

Export the data of PPM_SNAPSHOT_F as insert scripts in your old environment.
Import the data into your new environment.

Note:
The snapshot parameters (Snapshot frequency type and Snapshot frequency) of the old and new environments should be the same for a successful migration.