question Re: Need help on importing Business Capability in Enterprise Architecture forum

Need help on importing Business Capability

shaik_irfan — Tue, 11 May 2021 19:48:00 GMT

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

Re: Need help on importing Business Capability

mcastoe — Wed, 12 May 2021 16:17:11 GMT

Hi,

I have used two different method for dealing with APQC capabilities.

You will want to use the APQC Hierarchy IDs rather than the system generated ID. See this link in docs to disable ID generation: Create business capability and relate the capability with an application.

First is what i would classify as Excel manipulation + "layered" imports. I take the "Combined" sheet in the APQC data and using a couple formulas in columns to right of the core APQC columns, parse the APQC Hierarchy ID to determine the level of the capability (0-4) and the Hierarchy ID of the capabilities parent capability. Once you have these, separate each level "set" into separate excel workbooks per level with a single sheet with Capability Hierarchy ID, Name and Parent as column. Note that level 0 capabilities will have no parent. Create an import set and import one set at a time starting with level 0. Level 0, you can ignore the Parent column. For subsequent levels, use a script in the transform map to locate the parent by Hierarchy ID.

Another option I've used is to import the whole Combined sheet into a custom table, then write a fix script to parse the table, the data coming in from Excel is properly sorted and so for each row that has a Hierarchy ID of the pattern ##.0, you create a level 0 business capability, for others, you locate the parent, which should already exist and set the parent appropriately. This method is actually quicker once you have it coded but of course you need the admin level permissions to create a custom table and the fix script. I've done it locally in a PDI and then exported and imported via Excel.

I hope this helps,
Mark

Re: Need help on importing Business Capability

shaik_irfan — Wed, 12 May 2021 21:11:24 GMT

Thank you very much @mcastoe . I think I can do the second option as playing with excel formulas is tricky and since I need to show to client first at my PDI instance I can create a table as I am admin. If you please don't mind can you share me the fix script you talking. It would be more helpful for me

Re: Need help on importing Business Capability

mcastoe — Thu, 13 May 2021 12:30:27 GMT

Hi,

unfortunately that old PDI has retired. I will see if i can recreate. Another thing i remember is that i remember is that i added a column to the sheet before import named "order" and it was a simply a sequence from 1 - however many to insure i got the right sorting as you cannot sort on Hierarchy ID.

Mark

Re: Need help on importing Business Capability

shaik_irfan — Thu, 13 May 2021 12:35:17 GMT

Thank You @mcastoe ,

Yes i did imported the sheet in my PDI and had a order column added 🙂 But its little difficult to understand the hierarchy terminology as i am not so mature on APM i hope you understand

I will wait for your reply 🙂

Re: Need help on importing Business Capability

markbodman — Thu, 13 May 2021 13:01:15 GMT

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

Re: Need help on importing Business Capability

shaik_irfan — Thu, 13 May 2021 14:47:46 GMT

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 ?

Re: Need help on importing Business Capability

mcastoe — Fri, 14 May 2021 13:54:09 GMT

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

Re: Need help on importing Business Capability

shaik_irfan — Fri, 14 May 2021 20:28:23 GMT

@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 &apos;1.00&apos; for key &apos;a_num_10&apos;][sanitized=java.sql.BatchUpdateException: (conn&#61;6059) Duplicate entry &#39;1.00&#39; for key &#39;a_num_10&#39;]
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 ?

Re: Need help on importing Business Capability

mcastoe — Mon, 17 May 2021 13:49:42 GMT

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.

Re: Need help on importing Business Capability

shaik_irfan — Mon, 17 May 2021 17:43:16 GMT

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 ?

Re: Need help on importing Business Capability

mcastoe — Mon, 17 May 2021 19:05:26 GMT

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

Re: Need help on importing Business Capability

shaik_irfan — Mon, 17 May 2021 19:22:33 GMT

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