Migrate Planning Hierarchies to Lens Structure on upgrading to new Alignment Planner Workspace.

[Syed Azam]

This article is meant only for the customers who are running the below plugins in SN instance:

· “Alignment Planner Workspace” v4.1.1 or earlier version

· “Alignment Planner Workspace with PPM, Agile 2.0 and SAFe” v3.01 or earlier versions.

and would like to upgrade to “Alignment Planner Workspace” v5.0.1 or later version and “Alignment Planner Workspace with PPM, Agile 2.0 and SAFe” v4.0.0 or later version.

Why do I need data migration ?

The concept of Planning Hierarchy and Planning organization are obsoleted from “Alignment Planner Workspace” v5.0.1 onwards and the new concept of Lens structure has been introduced. Now, users will be able to use any tables (example: business_unit, dmn_department, pm_portfolio, pm_program or any platform table) as planning organization in Lens structure. If you have been maintaining data in planning organization tables (example: sn_align_core_business_unit, sn_align_core_department etc.) and also in platform tables, then this data migration process will help you to migrate the data and consolidate all data into targeted table. So, that any desired tables can be used in the Lens structure.

Who can perform data migration 

As this migration involves executing the script in background, only users with admin role can perform this migration. 

When to perform this process ?

The below steps are recommend to be completed before performing the data migration:

· Ensure that “Alignment Planner Workspace” app and all its depended app is upgraded successfully.

· Ensure that required modification has been done in the Lens structures(if required) and Lens structures are finalized. (Refer the OOTB lenses and product documentation to understand the Lens structure)

· Ensure that all Lenses are validated and are marked as active.

· Now, the instance is ready for data migration process. Note that, this data migration process is performed one Planning organization type at a time.

How to perform data migration?

Data migration evolves around two things. 1. Migrating Planning organization to lens entity records and 2. Mapping Planning items to bottom entity of the Lens.

1. Migration of Planning organization to Lens Entity records.
   

   Based on the Lens Structure you selected or created/modified. Verify if you have the required data already in place on the lens entities tables and also verify if the relationship among those lens entities are well maintained between the lens entity records. 

   For ex: If you selected the Organization Hierarchy (Company <- Business Unit <- Department)

   If the records already exists as per the lens entity structure, skip this step and jump to step 2 (Mapping Planning items to bottom entity of the lens)
   Data can be migrated from planning organization table to desired tables through any of the below options

   - Manually migrate (create) records in the lens entity table.

   - Run a background script to create (clone) a record in lens entity table for each of the planning organisation records.
2. Mapping Planning Items to bottom entity of the lens
   Once the planning organizations and their relationships are migrated to the Lens entities, Map the planning items to the Bottom entity of the lens. This can also be done either manually populating the bottom entity references or via background script (Probably making use of the Planning org references to now point to the leaf entity of the lens).
   Note : In the sample script below, only allowed planning item type records (typically Project/ Demand and Epic) are updated to refer to bottom entity. Other planning items Initiative , Big Rock are not included as they are referred to as lens entities.
   Refer PlanningItemAPIImpl script include -> getAvailablePlanningItemTypes() for more.
3. (For Goals module users only) Migrating the Planning Organization to the targeted tables for Strategic priority and Goal tables
   This step is applicable only for the customers who defined goals using the Goals module in the APW or Enterprise Goal Management module. After the Planning Hierarchies data is migrated to the Lens structure and the Planning items are mapped to the bottom entity of the lens, migrate the Planning Organization to the targeted tables for Strategic priority and Goal tables. For the script and detailed instructions, see Goals migration KB
   
   

Do the dependencies and Milestones need to be migrated as well?

All the existing dependencies among planning items are retained as is on upgrade and do not required any migration.

Planning item level milestones are intact , while Roadmap level milestones from AdHoc Roadmap are migrated to Free-form Roadmap.

Only Planning Org level milestones from legacy planning hierarchy are skipped on upgrade.

For reference, please find below sample script, to migrate the records from "sn_align_core_department" table to "cmn_department" table and it also updates the parent entity reference (business_unit in cmn_department) and planning item references to bottom entity (department on planning item). Please do not use this script as is, modify it based on your existing Hierarchy and new Lens structure to meet your need. This migration of records should be verified in Pre production environments thoroughly before executing in product instance. 

Recommendation

Data migration should be done one planning organization at a time. We would recommend you to start the data migration with the top-level entities before the child entities, so that their relationships are also retained. For example: If the lens structure is following: Company <- Business Unit <- Department. In this case, data migration should be done in following order: Company, Business Unit and then Department.

var apw_core_po_table = 'sn_align_core_department'; //Source table to migrate data from
var migrate_to_table = 'cmn_department'; //Destination/Target table to migrate data to
var planning_item_table_column = 'department'; //Reference field on planning item table , which refers the new Target table, Typically when the target table is the leaf node in Lens.
var mappingFields = { //Provide the field level mappings between source and target table, Ensure tht field types match for the field mappings.
	//Key : apw_core planning organization (or its extension) field name
	//Value : Migrate to table field name
    'name': 'name',
    'description': 'description'
};
var parent_filed_in_migrate_to_table = 'business_unit'; //Reference field from target table to its parent. (As per the new lens defined), this can be null if the source planning org is root entity.

var alignmentPlanningOrgGr = new GlideRecord(apw_core_po_table);
alignmentPlanningOrgGr.query();

while (alignmentPlanningOrgGr.next()) {//Iterate over all records in apw_core_po_table table
    var execution_entity_sysId = alignmentPlanningOrgGr.getValue('execution_entity_item');
    if (gs.nil(execution_entity_sysId)) { //Check if this is ever integrated/synched to execution layer (or any other platform table)
		//If record is not found to be synched with other table, synch with Execution table
        execution_entity_sysId = _createPORecordsOnExecutionSide(alignmentPlanningOrgGr, migrate_to_table, mappingFields);
        if (gs.nil(execution_entity_sysId)) {
            continue;
        } else {
			//If synch to execution table is successfull, update the reference on source record (i.e: alignmentPlanningOrgGr)
            alignmentPlanningOrgGr.setValue('execution_entity_item', execution_entity_sysId);
            alignmentPlanningOrgGr.update();
        }
    }
	
	//If the source planning org record has a parent populated, synch the corresponding parent's clone with the execution record (clone of planning org)
    if (!gs.nil(parent_filed_in_migrate_to_table) && !gs.nil(alignmentPlanningOrgGr.parent)) {
        var parentOrg = alignmentPlanningOrgGr.parent.getRefRecord();
        if (!gs.nil(parentOrg) && parentOrg.isValidRecord()) {
            var executionPOEquivalentRecrord = new GlideRecord(migrate_to_table);
            executionPOEquivalentRecrord.get(execution_entity_sysId); //Fetch the target execution record
            executionPOEquivalentRecrord.setValue(parent_filed_in_migrate_to_table, parentOrg.getValue('execution_entity_item'));//Set the mapped parent on the execution record.
            executionPOEquivalentRecrord.update();
        }
    }
	
	//If the reference field from planning item table to new target table is provided, link the planning item to the execution record (clone of planning org)
    if (!gs.nil(planning_item_table_column)) {
        _updatePlanningItemRecordsForPO(alignmentPlanningOrgGr, planning_item_table_column, execution_entity_sysId);
    }
}

//Fetches all the allowed Planning item records (usually Project/ Demand and Epic OOB) from the source Planning org and associates them to the execution record (clone of planning org)
function _updatePlanningItemRecordsForPO(alignmentPlanningOrgGr, planning_item_table_column, execution_entity_sysId) {
    var PlanningItemAPIImpl = new sn_align_core.PlanningItemAPIImpl();
    var planningItemsList = PlanningItemAPIImpl.getAvailablePlanningItemTypes();
    var planningItemGr = new GlideRecord('sn_align_core_planning_item');
    planningItemGr.addQuery('planning_organization', alignmentPlanningOrgGr.getUniqueValue());
    planningItemGr.addQuery('sys_class_name', 'IN', planningItemsList);
    planningItemGr.query();
    planningItemGr.setValue(planning_item_table_column, execution_entity_sysId);
    planningItemGr.updateMultiple();
}

//Create the record clone of alignmentPlanningOrgGr in the migrate_to_table using mappingFields between the 2 tables, modify accordingly as per the need.
function _createPORecordsOnExecutionSide(alignmentPlanningOrgGr, migrate_to_table, mappingFields) {
    var newExecutionPOEquivalentRecrord = new GlideRecord(migrate_to_table);
    newExecutionPOEquivalentRecrord.initialize(); //New record in target table to synch with Planning Org record
    if (!JSUtil.isEmpty(mappingFields)) {
        for (var key in mappingFields) {
            var executionFieldValue = alignmentPlanningOrgGr.getValue(key);
            var executionFieldName = mappingFields[key];
            newExecutionPOEquivalentRecrord.setValue(executionFieldName, executionFieldValue);
        }
        var newRecordSysid = newExecutionPOEquivalentRecrord.insert();
        return newRecordSysid;
    }
    return null;
}