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

mcastoe · ‎05-12-2021

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

shaik_irfan · ‎05-12-2021

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

mcastoe · ‎05-13-2021

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

shaik_irfan · ‎05-13-2021

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 🙂