Need help on importing Business Capability

shaik_irfan · ‎05-11-2021

Hello Experts,

I am really need of some expert suggestions, i have some bunch of data provided by customer from APQC to import as Business Capabilities and have a data mapped correctly with hierarchy and dependencies.

I have more than 20+K records, i really do not any idea how to import this. I am attaching some sample data similar to what i have can anyone please help me how to get this done ?

@mark_b Can you please help me in loading the data with mapping

mcastoe · ‎05-14-2021

Hi,

Hierarchies are not an APM thing; it's just a data concept or really a life concept. So as far as the hierarchy, the way it works is essentially a parent-child organization of data. The top capability 1.0 has children 1.1, 1.2, 1.3, and 1.1 has 1.1.1, 1.1.2 and so forth. Each child has its parent attribute set to the parent. Hope that helps to explain the data concept.

the following script is an example of what you might do.

/**
APQC Capability Loader
1. Prepare you Excel by combining all the categories and their children in hierarchy into a single sheet. this script depends on the capabilities being in a depth-wise ordering
2. add a row for ordering 1-n
3. create a custom table to hold the APQC data, APQC_TBL in the variable below
4. import the apqc data into your custom table
5. follow the instruction at the following to allow custom hierarchy ID on capabilities: https://docs.servicenow.com/bundle/quebec-it-business-management/page/product/application-portfolio-management/task/create-a-business-capability.html
6. use this script as a model to build you own import script from the custom table to capability.

*** NOTE: this script is for educational purpose only and no guarantee or warranty is implied or given

**/

var APQC_TBL = 'u_apqc_capability';
var BUS_CAP_TBL = 'cmdb_ci_business_capability';

var apqc_gr = new GlideRecord(APQC_TBL);
// capabilities are ordered in hierarchy form starting with the first root and its children and their children etc
apqc_gr.orderBy('u_order');
apqc_gr.query();

// for each apqc row, create a capability
gs.info('[APQC IMPORT] Begin APQC Capability Import');
var cnt = 0;
while(apqc_gr.next()) {
cnt++;
//gs.info('[APQC IMPORT] Capability = {0} {1}, count is {2}', [apqc_gr.getValue('u_name'),apqc_gr.getValue('u_hierarchy_id'), cnt]);
var h_id = apqc_gr.getValue('u_hierarchy_id');

// if its not a root capability, get the parent
var p_id = '';
var p_sys_id = null;
var ordr = null;
var istop = h_id.indexOf('.0');
if(istop < 0) {

var lastnum = h_id.lastIndexOf('.');
p_id = h_id.substring(0,lastnum);

// fix root level parents
if(p_id.indexOf('.') < 0){
p_id = p_id + '.0';
}
p_sys_id = getParentCapabilitySysIdByHID(p_id);
} else {
ordr = h_id.substring(0,h_id.lastIndexOf('.'));
}

var newcid = createCapability(apqc_gr.getValue('u_name'),h_id,ordr,p_sys_id);
if(!newcid) {
break; // something went wrong so bail and investigate
}
if((cnt % 100) == 0 ){
gs.info('[APQC IMPORT] - {0} APQC Capabilities imported', [cnt]);
}
}
gs.info('[APQC IMPORT] APQC Capability Import complete, {0} loaded ', [cnt]);

// create capability
function createCapability(name, hier_id, order, parent){
if(!name || !hier_id){
gs.info('[APQC IMPORT] ERROR, found a null name or hierarchy id, that cant be allowed...');
return null;
}

var c_gr = new GlideRecord(BUS_CAP_TBL);
c_gr.initialize();
c_gr.setValue('name',name);
c_gr.setValue('hierarchy_id',hier_id);
c_gr.setValue('correlation_id',hier_id);
if(parent) {
c_gr.setValue('parent',parent);
}
if (order) {
c_gr.setValue('order',order);
}
var newid = c_gr.insert('create new capability record');
//gs.info('[APQC IMPORT] New Capability Created: {0} {1} {2}',[name, hier_id, newid]);
c_gr = null;
return newid;
}
// get capability parent by hierarchy id
function getParentCapabilitySysIdByHID (capId) {
// note, when importing and referencing data it is always good to use the correlation id instead of other attributes
var gr = new GlideRecord(BUS_CAP_TBL);
gr.addQuery('correlation_id', capId);
gr.query();
if(gr.next()){
return gr.sys_id;
} else {
return null;
}
}

View solution in original post

shaik_irfan · ‎05-17-2021

Awesome, i think i can do that. Let me try to rephrase the code shared by you and update you accordingly

Mark Bodman · ‎05-13-2021

Yes, Mark captured the advice I would give, import in seperate layers, starting from the top then each subsequent layer under that.

shaik_irfan · ‎05-13-2021

Thank you @mark_b for your reply.

As per Mark Castoe we need to run a fix script to align the parent business capabilities and level 0, by any chance do you have that fix script with you or any blog which i can refer ?