Need help on importing Business Capability

[shaik_irfan]

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 · Accepted solution]

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;
}
}

[shaik_irfan]

@mcastoe 

Thank you so very much for the script and explanation.

I followed your steps and i am able to import Combined sheet along with order in the new custom table

I copied your script and added in the fix script before running i disable the custom use_business_capability_custom_hierarchy_id property to true as suggested in the docs

Later when i run the i am getting an error sir, i tried understanding the error but i think this is occurring because of duplicate order then i inactivate the BR 'Check for duplicate order' when i run the fix script i see the error again 😞

*** Script: [APQC IMPORT] Begin APQC Capability Import
GlideSession message was modified by sanitization. [message=java.sql.BatchUpdateException: (conn=6059) Duplicate entry '1.00' for key 'a_num_10'][sanitized=java.sql.BatchUpdateException: (conn=6059) Duplicate entry '1.00' for key 'a_num_10']
FAILED TRYING TO EXECUTE ON CONNECTION glide.8 (connpid=6059): INSERT INTO cmdb (`a_str_14`,`skip_sync`,`operational_status`,`sys_updated_on`,`can_print`,`sys_class_name`,`sys_id`,`sys_updated_by`,`sys_class_path`,`sys_created_on`,`sys_domain`,`sys_created_by`,`a_num_10`,`a_bln_2`,`sys_mod_count`,`a_bln_1`,`monitor`,`sys_domain_path`,`a_bln_3`,`cost_cc`,`install_status`,`name`,`attested`,`unverified`,`correlation_id`,`fault_count`,`a_int_1`) VALUES('1.0',0,1,'2021-05-14 20:23:00',0,'cmdb_ci_business_capability','281badbe2f4870108bea56f62799b687','admin','/!!/#+','2021-05-14 20:23:00','global','admin',1,1,0,1,0,'/',1,'USD',1,'Develop Vision and Strategy',0,0,'1.0',0,0),INSERT INTO cmdb$par1 (`operational_status`,`sys_updated_on`,`sys_class_name`,`sys_id`,`sys_updated_by`,`sys_class_path`,`sys_created_on`,`sys_domain`,`sys_created_by`,`sys_mod_count`,`sys_domain_path`,`install_status`,`name`) VALUES(1,'2021-05-14 20:23:00','cmdb_ci_business_capability','281badbe2f4870108bea56f62799b687','admin','/!!/#+','2021-05-14 20:23:00','global','admin',0,'/',1,'Develop Vision and Strategy')
java.sql.BatchUpdateException: (conn=6059) Duplicate entry '1.00' for key 'a_num_10'
: java.sql.SQLException: java.sql.BatchUpdateException: (conn=6059) Duplicate entry '1.00' for key 'a_num_10': com.glide.db.StatementBatcher.getSQLException(StatementBatcher.java:643)
com.glide.db.StatementBatcher.commitBatch(StatementBatcher.java:595)
com.glide.db.StatementBatcher.commitAllOrNothing(StatementBatcher.java:469)
com.glide.db.DBCompositeAction.executeAsBatch(DBCompositeAction.java:228)
com.glide.db.DBCompositeAction.executeChunk(DBCompositeAction.java:171)
com.glide.db.DBCompositeAction.executeAsResultSet0(DBCompositeAction.java:108)
com.glide.db.DBAction.executeAndReturnTable(DBAction.java:267)
com.glide.db.DBAction.executeNormal(DBAction.java:256)
com.glide.db.DBAction.executeAndReturnException(DBAction.java:210)
com.glide.script.GlideRecordITable.insert(GlideRecordITable.java:158)
com.glide.script.GlideRecord.insert(GlideRecord.java:4971)
com.glide.script.GlideRecord.insert(GlideRecord.java:4881)
com.glide.script.GlideRecord.jsFunction_insert(GlideRecord.java:5425)
sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
org.mozilla.javascript.MemberBox.invoke(MemberBox.java:138)
org.mozilla.javascript.FunctionObject.doInvoke(FunctionObject.java:670)
org.mozilla.javascript.FunctionObject.call(FunctionObject.java:614)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2609)
org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime.java:32)
org.mozilla.javascript.gen._refname__4026._c_createCapability_1(<refname>:68)
org.mozilla.javascript.gen._refname__4026.call(<refname>)
org.mozilla.javascript.ScriptRuntime.doCall2(ScriptRuntime.java:2678)
org.mozilla.javascript.ScriptRuntime.doCall(ScriptRuntime.java:2617)
org.mozilla.javascript.optimizer.OptRuntime.callName(OptRuntime.java:63)
org.mozilla.javascript.gen._refname__4026._c_script_0(<refname>:38)
org.mozilla.javascript.gen._refname__4026.call(<refname>)
org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:563)
org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3459)
org.mozilla.javascript.gen._refname__4026.call(<refname>)
org.mozilla.javascript.gen._refname__4026.exec(<refname>)
com.glide.script.ScriptEvaluator.execute(ScriptEvaluator.java:279)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:118)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:82)
com.glide.script.ScriptEvaluator.evaluateString(ScriptEvaluator.java:73)
com.glide.script.Evaluator.evaluateString(Evaluator.java:96)
com.glide.update.UpdateManager2.testFixScript(UpdateManager2.java:750)
com.snc.apps.ScriptFixXMLHttpProcessor$ScriptFixWorker.runFixScript(ScriptFixXMLHttpProcessor.java:173)
com.snc.apps.ScriptFixXMLHttpProcessor$ScriptFixWorker.evaluateScript(ScriptFixXMLHttpProcessor.java:151)
com.snc.apps.ScriptFixXMLHttpProcessor$ScriptFixWorker.startWork(ScriptFixXMLHttpProcessor.java:126)
com.glide.worker.AbstractProgressWorker.startAndWait(AbstractProgressWorker.java:126)
com.glide.worker.ProgressWorker.startAndWait(ProgressWorker.java:52)
com.glide.worker.AbstractProgressWorker.start(AbstractProgressWorker.java:101)
com.snc.apps.ScriptFixXMLHttpProcessor.process(ScriptFixXMLHttpProcessor.java:87)
com.glide.processors.XMLHttpProcessor.processJavaAJAX(XMLHttpProcessor.java:168)
com.glide.processors.XMLHttpProcessor.process(XMLHttpProcessor.java:121)
com.glide.processors.AProcessor.runProcessor(AProcessor.java:576)
com.glide.processors.AProcessor.processTransaction(AProcessor.java:264)
com.glide.processors.ProcessorRegistry.process0(ProcessorRegistry.java:181)
com.glide.processors.ProcessorRegistry.process(ProcessorRegistry.java:169)
com.glide.ui.GlideServletTransaction.process(GlideServletTransaction.java:44)
com.glide.sys.Transaction.run(Transaction.java:2228)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
java.lang.Thread.run(Thread.java:748)

*** Script: [APQC IMPORT] APQC Capability Import complete, 1.0 loaded 
[0:00:00.167] Total Time

Do i need to inactive any Business rules or i think some of Business Capability have the same name like "Develop Vision and Strategy" was this causing the issue to run the scirpt ?

[mcastoe]

Hello,

this is not a business rule issue.   It is complaining about a duplicate entry in a key field (a field that does not allow duplicates) named a_num_10.  Do you already have records in the cmdb_ci_business_capability table?  If so, delete them all before running the script.

[shaik_irfan]

Hi @macastoe 

Thank you so very much for your help, i think i am good and when i delete in my PDI reimport the data i can see all are perfectly working 🙂

One last question before i mark the response as correct, sorry for bearing me please do for one last time. 

What if we already having records on Business capability table, which are not available in sheet then we can do this integration ? is there a way we can import all the records without deleting existing records in the table ?

[mcastoe]

Hi,

so, in order to be able to use this import technique where there are already records you will have to query the cmdb_ci_business_capability table and insure that your new capability has a unique correlation id, order and hierarchy id values.  If it does not, you either skip that record or you create a new unique value.

mark