Handling export and import of relationship attributes in IBM Master Data Management Collaboration Server

IBM InfoSphere® MDM Collaboration Server provides a special attribute data type that allows for implementing a relationship between two items: the relationship data type. If a relationship data type has to be exported and imported, the default-generated export and import scripts are not sufficient. This article will guide you through the requirements to create custom export/import scripts to handle items with relationship attributes.

Share:

Uwe Weber (uwe.weber@de.ibm.com), IBM MDM PIM/CS L2 Support Support Engineer, EMEA, IBM Germany

Uwe Weber is an IT Specialist for Informix and DB2 UDB. He is located in Munich, Germany. Uwe's IT experience began 1997 where he started to work at Informix as an instructor for Informix products. After the acquisition of Informix by IBM in 2001 he changed to the technical presales department where he his working with customers in EMEA Central.



20 September 2012

Introduction

Since the first days of IBM InfoSphere MDM Collaboration Server, it was important to exchange data with other applications in enterprise environments. Typically, data is exchanged using exports and imports. MDM CS supports several standard formats that can be used to interact with other applications. For example, there is comma-separated value (CSV) format, fixed-length format, and even a spreadsheet format, which allows more complex export/import applications. Of course, MDM CS also provides functionality to create customized exports and imports for handling any kind of data exchange.

When working with a standard export/import generated by the MDM CS engine, items can be exported and imported without limitation. But there are a few data types that require special attention. If an item consists of relationship attributes, the relationship information is not exported in a format that can be imported in any other MDM CS instance. To understand why relationship attributes need attention when exporting/importing such data types, you have to understand how relationship attributes are stored within MDM CS: The reference to another item within an MDM CS instance is implemented by an ID. This item ID is an internal identifier, which is a unique number within an MDM CS instance. Depending on the export/import scenario, it is not guaranteed that a referenced item in the target system has the same item ID in the source system. So if a standard export would simply take this ID of the referenced item and use it for import, a completely different item could be referenced. This situation would cause a logical corruption, which is not acceptable.

In the following sections, you will find samples that will demonstrate how to create your own scripts to handle relationship attributes in exports and imports.


Data model for illustration: Employee and project

In this article, the use case is built on a classic relationship scenario. In this example, there are two catalogs: "Employees" and "Projects." There is a relationship between the "items" stored in these catalogs. An employee (which is technically an "item" in the Employees catalog) can be in relation to one or more projects (an item in the Projects catalog). Of course, a project item could have a relation to the Employees catalog, too. In a project, one or more employees can be involved. That means there is a many-to-many relationship between catalog Employees and catalog Projects.

Figure 1. Many-to-many relationship entity diagram
Diagram shows the many-to-many relationship between employee and project

The following script sections will create a sample company that consist of these two catalogs with all required objects and items. Please note that the script has been separated into several logical sections (listings). Please find the complete script in the Download section.

Creating primary specs

First of all, primary specs for catalog Employees and Projects are created. Both catalogs consists of a few attributes to store relevant information. But most importantly, both specs have a primary key attribute (mandatory) and, of course, a relationship attribute.

Listing 1. Creating primary specs
 16 //
 17 // create employee spec
 18 var emp_spec_node = null;
 19 var emp_spec_name = "emp_spec";
 20 var emp_display_node = null;
 21 var emp_spec = new Spec(emp_spec_name, "PRIMARY_SPEC");
 22 
 23 emp_spec_node = new SpecNode(emp_spec, emp_spec_name + "/emp_id", 1);
 24 emp_spec_node.setAttribute("TYPE", "STRING");
 25 emp_spec_node.setAttribute("MAXLENGTH", "32");
 26 
 27 emp_spec_node = new SpecNode(emp_spec, emp_spec_name + "/emp_name", 2);
 28 emp_spec_node.setAttribute("TYPE", "STRING");
 29 emp_spec_node.setAttribute("MAXLENGTH", "64");
 30 emp_spec_node.setNodeIndexed(true);
 31 emp_display_node = emp_spec_node;
 32 
 33 emp_spec_node = new SpecNode(emp_spec, emp_spec_name + "/emp_department", 3);
 34 emp_spec_node.setAttribute("TYPE", "STRING");
 35 emp_spec_node.setAttribute("MAXLENGTH", "64");
 36 
 37 emp_spec_node = new SpecNode(emp_spec, emp_spec_name + "/emp_projects", 4);
 38 emp_spec_node.setAttribute("TYPE", "RELATIONSHIP");
 39 emp_spec_node.setAttribute("MAXLENGTH", "256");
 40 emp_spec_node.setAttribute("MIN_OCCURRENCE", "0");
 41 emp_spec_node.setAttribute("MAX_OCCURRENCE", "4");
 42 
 43 emp_spec.setPrimaryKeyPath(emp_spec_name + "/emp_id");
 44 emp_spec.saveSpec();
 45 
 46 //
 47 // create project spec
 48 var proj_spec_node = null;
 49 var proj_spec_name = "proj_spec";
 50 var proj_display_node = null;
 51 var proj_spec = new Spec(proj_spec_name, "PRIMARY_SPEC");
 52 
 53 proj_spec_node = new SpecNode(proj_spec, proj_spec_name + "/proj_code", 1);
 54 proj_spec_node.setAttribute("TYPE", "STRING"); 
 55 proj_spec_node.setAttribute("MAXLENGTH", "3");
 56 
 57 proj_spec_node = new SpecNode(proj_spec, proj_spec_name + "/proj_name", 2);
 58 proj_spec_node.setAttribute("TYPE", "STRING");
 59 proj_spec_node.setAttribute("MAXLENGTH", "64");
 60 proj_spec_node.setNodeIndexed(true);
 61 proj_display_node = proj_spec_node;
 62 
 63 proj_spec_node = new SpecNode(proj_spec, proj_spec_name + "/proj_description", 3);
 64 proj_spec_node.setAttribute("TYPE", "STRING");
 65 proj_spec_node.setAttribute("MAXLENGTH", "256");
 66 
 67 proj_spec_node = new SpecNode(proj_spec, proj_spec_name + "/proj_employees", 4);
 68 proj_spec_node.setAttribute("TYPE", "RELATIONSHIP");
 69 proj_spec_node.setAttribute("MAXLENGTH", "256");
 70 proj_spec_node.setAttribute("MIN_OCCURRENCE", "0");
 71 proj_spec_node.setAttribute("MAX_OCCURRENCE", "8");
 72 
 73 proj_spec.setPrimaryKeyPath(proj_spec_name + "/proj_code");
 74 proj_spec.saveSpec();

These specs contain two additional rules/constraints: An employee cannot participate on more than four projects (max-occ=4), and a project cannot be handled by more than eight employees (max-occ=8). But this is just a rule that could be defined by design and will not have any effect regarding further steps when creating exports/imports.

Creating attribute collections

Before creating the catalogs, two attribute collections have to be created that can be used as a core attribute group for the catalogs. The attribute collections attrcol_emp_spec and attrcol_proj_spec will contain all attributes from specs emp_spec and proj_spec.

Listing 2. Creating attribute collections
 76 //
 77 // create attribute collection attrcol_emp_spec
 78 var attrcol_emp_spec_name = "attrcol_" + emp_spec_name;
 79 var attrcol_emp_spec = new AttrGroup(attrcol_emp_spec_name, "GENERAL");
 80 
 81 attrcol_emp_spec.addSpecToAttrGroup(emp_spec, true);
 82 
 83 //
 84 // create attribute collection attrcol_proj_spec
 85 var attrcol_proj_spec_name = "attrcol_" + proj_spec_name;
 86 var attrcol_proj_spec = new AttrGroup(attrcol_proj_spec_name, "GENERAL");
 87 
 88 attrcol_proj_spec.addSpecToAttrGroup(proj_spec, true);

Creating Employees and Projects catalogs

Using the specs and attribute collections created above catalog Employees and Projects can now be created. For catalog Employees, the built-in Default Organization Hierarchy can be chosen, and for catalog Projects and Default Lookup Table Hierarchy, which is also built-in.

Listing 3. Creating catalogs
 90 //
 91 // create catalog "Employees"
 92 var catalog_attributes = [];
 93 var hm_catalog_args = [];
 94 hm_catalog_args["displayAttribute"] = emp_display_node;
 95 
 96 var emp_catalog_name = "Employees";
 97 var org_hierarchy = getCategoryTreeByName("Default Organization Hierarchy");
 98 var emp_catalog = new Catalog(emp_spec, emp_catalog_name, 
 99                               org_hierarchy, hm_catalog_args);
100 
101 catalog_attributes["USER_DEFINED_CORE_ATTRIBUTE_GROUP"] = attrcol_emp_spec;
102 emp_catalog.setContainerProperties(catalog_attributes);
103 emp_catalog.saveCatalog();
104 
105 //
106 // create catalog "Projects"
107 var proj_catalog_name = "Projects";
108 hm_catalog_args["displayAttribute"] = proj_display_node;
109 var lkp_hierarchy = getCategoryTreeByName("Default Lookup Table Hierarchy");
110 var proj_catalog = new Catalog(proj_spec, proj_catalog_name, 
111                                lkp_hierarchy, hm_catalog_args);
112 
113 catalog_attributes["USER_DEFINED_CORE_ATTRIBUTE_GROUP"] = attrcol_proj_spec;
114 proj_catalog.setContainerProperties(catalog_attributes);
115 proj_catalog.saveCatalog();

For both catalogs, various categories can now be added.

Listing 4. Creating categories
117 //
118 // create categories in catalog "Employees"
119 org_hierarchy.buildCategory("Default Organization/AP", "/", "AP");
120 org_hierarchy.buildCategory("Default Organization/AP/AU", "/", "Australia");
121 org_hierarchy.buildCategory("Default Organization/AP/CN", "/", "China");
122 org_hierarchy.buildCategory("Default Organization/EMEA", "/", "EMEA");
123 org_hierarchy.buildCategory("Default Organization/EMEA/FR", "/", "France");
124 org_hierarchy.buildCategory("Default Organization/EMEA/DE", "/", "Germany");
125 org_hierarchy.buildCategory("Default Organization/EMEA/UK", "/", "United Kingdom");
126 org_hierarchy.buildCategory("Default Organization/NA", "/", "NA");
127 org_hierarchy.buildCategory("Default Organization/NA/USA", "/", "USA");
128 org_hierarchy.buildCategory("Default Organization/NA/CA", "/", "Canada");
129 org_hierarchy.saveCategoryTree();

Adding items to catalogs

New items for the catalogs are now created. First, new projects are created. Because there are no employees in the Employees catalog yet, the relationship attribute proj_spec/proj_employees is left empty.

Listing 5. Creating project items
131 //
132 // create categories in catalog "Projects"
133 lkp_hierarchy.buildCategory("Administration", "/", "Administration");
134 lkp_hierarchy.buildCategory("IT", "/", "IT");
135 lkp_hierarchy.buildCategory("Sales", "/", "Sales");
136 lkp_hierarchy.saveCategoryTree();
137 
138 //
139 // create projects
140 var proj = null;
141 
142 proj = new CtgItem(proj_catalog);
143 proj.setCtgItemAttrib(proj_spec_name + "/proj_code", "PRT");
144 proj.setCtgItemAttrib(proj_spec_name + "/proj_name", "WLAN Printer");
145 proj.setCtgItemAttrib(proj_spec_name + "/proj_description", 
146                       "Installation of new printer in new offices.");
147 proj.mapCtgItemToCategory(lkp_hierarchy.getCategoryByPath("IT", "/"));
148 proj.saveCtgItem();
149 
150 proj = new CtgItem(proj_catalog);
151 proj.setCtgItemAttrib(proj_spec_name + "/proj_code", "CRE");
152 proj.setCtgItemAttrib(proj_spec_name + "/proj_name", "Credit Definition");
153 proj.setCtgItemAttrib(proj_spec_name + "/proj_description", 
154                       "Credits needs to be redefined for new customers.");
155 proj.mapCtgItemToCategory(lkp_hierarchy.getCategoryByPath("Sales", "/"));
156 proj.saveCtgItem();
157 
158 proj = new CtgItem(proj_catalog);
159 proj.setCtgItemAttrib(proj_spec_name + "/proj_code", "MOB");
160 proj.setCtgItemAttrib(proj_spec_name + "/proj_name", "Mobile Devices");
161 proj.setCtgItemAttrib(proj_spec_name + "/proj_description",
162                       "Mobile device delivery for employees.");
163 proj.mapCtgItemToCategory(lkp_hierarchy.getCategoryByPath("IT", "/"));
164 proj.saveCtgItem();

After a set of projects has been added to catalog Projects, new employees are created in catalog Employees. Four out of five of these employees will be assigned to projects.

Listing 6. Creating employee items
166 //
167 // create employees
168 var emp = null;
169 
170 emp = new CtgItem(emp_catalog);
171 emp.setCtgItemAttrib(emp_spec_name + "/emp_id", "1");
172 emp.setCtgItemAttrib(emp_spec_name + "/emp_name", "Brown");
173 emp.setCtgItemAttrib(emp_spec_name + "/emp_department", "Development");
174 emp.setCtgItemRelationshipAttrib(emp_spec_name + "/emp_projects#0", 
175                                  proj_catalog, "MOB");
176 emp.setCtgItemRelationshipAttrib(emp_spec_name + "/emp_projects#1", 
177                                  proj_catalog, "PRT");
178 emp.mapCtgItemToCategory(org_hierarchy.getCategoryByPath(
179     "Default Organization/NA/USA", "/"));
180 emp.saveCtgItem();
181 
182 emp = new CtgItem(emp_catalog);
183 emp.setCtgItemAttrib(emp_spec_name + "/emp_id", "2");
184 emp.setCtgItemAttrib(emp_spec_name + "/emp_name", "Chevalier");
185 emp.setCtgItemAttrib(emp_spec_name + "/emp_department", "Operations");
186 emp.setCtgItemRelationshipAttrib(emp_spec_name + "/emp_projects#0", 
187                                  proj_catalog, "CRE");
188 emp.mapCtgItemToCategory(org_hierarchy.getCategoryByPath(
189      "Default Organization/EMEA/FR", "/"));
190 emp.saveCtgItem();
191 
192 emp = new CtgItem(emp_catalog);
193 emp.setCtgItemAttrib(emp_spec_name + "/emp_id", "3");
194 emp.setCtgItemAttrib(emp_spec_name + "/emp_name", "Hinz");
195 emp.setCtgItemAttrib(emp_spec_name + "/emp_department", "Marketing");
196 emp.setCtgItemRelationshipAttrib(emp_spec_name + "/emp_projects#0", 
197                                  proj_catalog, "CRE");
198 emp.mapCtgItemToCategory(org_hierarchy.getCategoryByPath(
199     "Default Organization/EMEA/DE", "/"));
200 emp.saveCtgItem();
201 
202 emp = new CtgItem(emp_catalog);
203 emp.setCtgItemAttrib(emp_spec_name + "/emp_id", "4");
204 emp.setCtgItemAttrib(emp_spec_name + "/emp_name", "Wang");
205 emp.setCtgItemAttrib(emp_spec_name + "/emp_department", "Q&A");
206 emp.setCtgItemRelationshipAttrib(emp_spec_name + "/emp_projects#0", 
207                                  proj_catalog, "MOB");
208 emp.setCtgItemRelationshipAttrib(emp_spec_name + "/emp_projects#1", 
209                                  proj_catalog, "PRT");
210 emp.mapCtgItemToCategory(org_hierarchy.getCategoryByPath(
211     "Default Organization/AP/CN", "/"));
212 emp.saveCtgItem();
213 
214 emp = new CtgItem(emp_catalog);
215 emp.setCtgItemAttrib(emp_spec_name + "/emp_id", "5");
216 emp.setCtgItemAttrib(emp_spec_name + "/emp_name", "Steinman");
217 emp.setCtgItemAttrib(emp_spec_name + "/emp_department", "Operations");
218 emp.mapCtgItemToCategory(org_hierarchy.getCategoryByPath(
219     "Default Organization/NA/CA", "/"));
220 emp.saveCtgItem();

Because employees have been created, the missing relationships from catalog Projects to Employees can be defined.

Listing 7. Creating relationship
222 //
223 // add employees to projects
224 proj = proj_catalog.getCtgItemByPrimaryKey("PRT");
225 proj.setCtgItemRelationshipAttrib(proj_spec_name + "/proj_employees#0",
226                                   emp_catalog, "1");
227 proj.setCtgItemRelationshipAttrib(proj_spec_name + "/proj_employees#1",
228                                   emp_catalog, "4");
229 proj.saveCtgItem();
230 
231 proj = proj_catalog.getCtgItemByPrimaryKey("CRE");
232 proj.setCtgItemRelationshipAttrib(proj_spec_name + "/proj_employees#0",
233                                   emp_catalog, "2");
234 proj.setCtgItemRelationshipAttrib(proj_spec_name + "/proj_employees#1",
235                                   emp_catalog, "3");
236 proj.saveCtgItem();
237 
238 proj = proj_catalog.getCtgItemByPrimaryKey("MOB");
239 proj.setCtgItemRelationshipAttrib(proj_spec_name + "/proj_employees#0",
240                                   emp_catalog, "1");
241 proj.setCtgItemRelationshipAttrib(proj_spec_name + "/proj_employees#1",
242                                   emp_catalog, "4");
243 proj.saveCtgItem();
244 
245 out.writeln("Sample company created.");

After the script above has executed, a new company has been set up containing two catalogs. The following figures show the catalogs and their contents in the UI after creation

Figure 2. Relations between employee and project items
Figure describes what employee items are related to project items and vice versa
Figure 3. Relations between employee and project items
Figure describes what employee items are related to project items and vice versa

The figure below depicts the relations of items in catalogs Employees and Projects.

Figure 4. Relations between employee and project items
Figure that describes what employee items are related to project items and vice versa

The next sections describe how to export and import items from this company and what pitfalls to consider.


Standard export functionality

Before writing our own code to export items from the Employees catalog, we should look at what a "standard export" can provide us. When creating a standard export, a few settings and configurations have to be defined. All necessary definitions can be done when creating an export using Collaboration Manager > Exports > New Export. An export consists of:

  1. Export Name: A unique name for the export.
  2. Destination Specification: A (destination) spec that describes what attributes can be found in the export (-file).
  3. Catalog: What catalog to export.
  4. Version: Which version of an catalog to export.
  5. Group of items: Which items to export.
  6. Hierarchy: Items of which hierarchy shall be exported.
  7. Syndication Type: Which items to export.
  8. Catalog to Destination mapping: Maps the attributes of the items to the attributes in the export file.
  9. Charset: Encoding used for the export file.
  10. Distribution group: Distribution group.
  11. Catalog Export Script: Script doing the export.
  12. Approving Authority: Approver.

Keep in mind that relationship attributes have to be exported; step 2 (destination specification) and step 8 (catalog to destination mapping) become important. The destination spec in combination with the mapping specifies what attributes of an item will be exported. Attribute emp_projects is of data type relationship; it references another item. What data type has to be selected for this attribute when creating the destination spec? The drop-down box for available data types does not include "relationship."

As mentioned in the introduction, the referenced item in a relationship attribute is specified by an ID, so you can select Integer or String as the data type for the relationship attribute in the destination spec. Both data types can represent an ID.

Figure 5. Selecting appropriate data type for relationship in destination spec
Figure describes what employee items are related to project items and vice versa

When creating an export using the Export Console with the parameters discussed above, a script will be created that is exporting the items from the catalog (see step 11 above). Such a standard export script looks like Listing 8.

Listing 8. Generated standard export script
var item;
forEachCtgItem(item)
{
    var itemAttrs = item.getCtgItemMappedAttribs();
	var itemValidatedAttrs = validateMappedAttribs(itemAttrs);
	if (null != itemValidatedAttrs)
	{
		out.writeln(buildCSV(
		    checkString(itemValidatedAttrs["emp exp destspec/id"], ""),
		    checkString(itemValidatedAttrs["emp exp destspec/name"], ""),
		    checkString(itemValidatedAttrs["emp exp destspec/department"], ""),
		    checkString(itemValidatedAttrs["emp exp destspec/projects"], "")));
	}
}

In the generated script, there is loop iterating over all items in a catalog (i.e., Employees) and writing all attributes into a file in CSV format. Attribute projects is a reference to another item. To get an idea what output for this attribute is generated, we can run that export from Export Console. After the export executes, the exported items can be found in catalog.out in the docstore:

1,Brown,Development,2620
2,Chevalier,Operations,2619
3,Hinz,Marketing,2619
4,Wang,Q&A,2620
5,Steinman,Operations,

All employees were exported. When having a closer look at each of these employees (items) we can see the ID as last attribute value. Actually, this is our relationship attribute that references projects (items). But here, we only have a number. This number represents the internal item ID. Please review the exported lines for employee Brown and Steinman.

As shown in the relationship diagram above, Brown is participating in two projects. But here, only one (the project's internal item ID 2620) has been exported. This is because attribute "project" in spec "emp_spec" is a multi-occurrence attribute. Whenever accessing an occurrence of such an attribute, the index of the particular occurrence has to be specified. For example, when accessing the second occurrence of a multi-occurrence attribute, the following pattern has to be used: specname/path/multioccattrib#1.

If the index for the multi-occurrence attribute is not specified, the first occurrence is accessed. In other words: specname/path/multioccattrib is identical to specname/path/multioccattrib#0. Therefore, the standard export script only exports the first occurrence of attribute "projects." For employee Steinman, no internal item ID has been exported. Of course, this is because this employee is not participating on any project.

Why can't we use the item ID to export and import items?

The item ID is a unique number/identifier for an item within an MDM CS instance. Now, some would argue that as long as the target system is an identical copy of the source system, everything is fine, and it should be possible to import these items on the target system. In an identical copy, referenced items (projects) should have the same item ID as we have on the source system. So it should be enough to transfer this ID from one system to another. But this would require a kind of "read-only" MDM CS instance, which guarantees that items and their IDs do not change. Such a read-only mode is not provided by the MDM CS engine, so we cannot assume that item ID of a source and target systems are always identical.

Another reason why the item ID cannot be used for an import is because of a data type mismatch. The target attribute is expecting a value of data type relationship. But here, we have only a number (date type Integer, Float, or String).

Finally, please consider that the export/import job could also be performed within a single MDM CS instance but different companies. Because an item ID is unique within an MDM CS instance, it is not possible to export an item ID from a company A and import it in a company B, which is in the same MDM CS instance.

To summarize: A standard generated export script cannot provide a CSV file that can be used to import items that contain relationship attributes. Customized code is required.

How is a relationship attribute created?

Before thinking about how an export/import script has to look, we have to make ourselves aware of how a relationship attribute is created in WPC script language. When satisfying an attribute with a value script, function setCtgItemAttrib() is used. But when setting a value for an attribute of type relationship, a different function has to be used: setCtgItemRelationshipAttrib().

Please review the syntax for this function:

void Item::setCtgItemRelationshipAttrib(
        String sAttribPath,
        Catalog relatedItemCtg,
        String sRelatedItemPrimaryKey)

The first argument for this function is the spec name and attribute path for the relationship attribute. The second attribute is a catalog object. And finally, the third argument specifies the primary key value of the item you would like to reference.

That means these values/information required for function setCtgItemRelationshipAttrib() has to be included for each item in the export file. So the exported item for employee 1 (name: Brown) could look like this:

1,Brown,Development,emp_spec/emp_projects#0,Projects,MOB,emp_spec/
emp_projects#1,Projects,PRT

As shown in the item relationship diagram, employee Brown is participating in two projects. The project information we need to import later can be found as additional columns/values in a CSV line:

emp_spec/emp_projects#0,Projects,MOB,emp_spec/emp_projects#1,Projects,PRT
|            project 1              |             project 2             |

Using custom scripts to export data from the relationship attributes

In the export script, we have to define which items of a catalog to export. This can be done using a query string formulated in WQL (select item from catalog('Employees')). In this scenario, we are fetching all employees. But of course, the query can have any format as you require.

Listing 9. Customized export script
// export file
var emp_export = createOtherOut("employee.csv");

// query: what items/employees shall be exported
var emp_query_string = "select item from catalog('Employees')";
var emp_query = new SearchQuery(emp_query_string);

// result set containing employee items
var emp_rs = emp_query.execute();

// outer loop to get all relevant employee items
while (emp_rs.next()) {
    // get an employee/item from result set
    var emp = emp_rs.getItem(1);
	
    // isolate attributes from employee
    var emp_cat = emp.getCtgItemCategoryPaths("/")[0];
    var emp_id = emp.getCtgItemAttrib("emp_spec/emp_id");
    var emp_name = emp.getCtgItemAttrib("emp_spec/emp_name");
    var emp_dept = emp.getCtgItemAttrib("emp_spec/emp_department");

    // projects will be determined in next step
    var emp_projects = "";
	
    // check projects (max_occ = 4)
    for (var p = 0; p < 4; p++) {
        // get relationship attribute (String[])m, where
        // relproj[0] = related item's catalog's name
        // relproj[1] = related item's primary key
        var path = "emp_spec/emp_projects#" + p;
        var relproj = emp.getEntryRelationshipAttrib(path);
    
        if (relproj != null) {
            var catalog_name = relproj[0];
            var primkey = relproj[1];
            emp_projects = emp_projects + "emp_spec/emp_projects#" + p + "," +
                           catalog_name + "," + primkey + ",";
        }
    } // for

// write item to export file
emp_export.writeln(emp_cat + "," + emp_id + "," + emp_name + "," + 
                   emp_dept + "," + emp_projects);
				   
out.writeln("Employee '" + emp_name + "' exported.");
} // while
emp_export.save("employee.csv");

For testing, this script can be executed in the Script Sandbox, which creates following output:

Employee 'Brown' exported.
Employee 'Chevalier' exported.
Employee 'Hinz' exported.
Employee 'Wang' exported.
Employee 'Steinman' exported.

The export file (employee.csv) can be found in the document store in the root directory with the following content:

  • DefaultOrganization/NA/USA,1,Brown,Development,emp_spec/
    emp_projects#0,Projects,MOB,emp_spec/emp_projects#1,Projects,PRT,
  • Default Organization/EMEA/FR,2,Chevalier,Operations,emp_spec/
    emp_projects#0,Projects,CRE,
  • Default Organization/EMEA/DE,3,Hinz,Marketing,emp_spec/emp_projects#0,Projects,CRE,
  • Default Organization/AP/CN,4,Wang,Q&A,emp_spec/
    emp_projects#0,Projects,MOB,emp_spec/emp_projects#1,Projects,PRT,
  • Default Organization/NA/CA,5,Steinman,Operations,

These lines contain enough data to perform an import on the target system.

NOTE: The first value in a CSV line is the category where an item has been mapped. Of course, this information has to also be stored in that export file. Otherwise, we won't be able to map an item into the right category when importing these items later.


Create a customized export

To use this script as "real" export script, you have to pass it as script when the export is created in the Export Console or create it in the Scripts Console, selecting Catalog Export Script. If the script is created as Catalog Export Script, you can select it from a drop-down box when the export is created. Beware: When the export is created, you have to specify the destination spec and the map even if the script is not making use of these objects. The script will handle the the sequence of attributes on its own, and there is no need to specify this information using a destination spec and map. So it is OK to provide a dummy spec and dummy map here to satisfy the requirements when creating an export.

Figure 6. Selecting customized export script for export
Screenshot shows the selection of a script in new export console

Standard import functionality

Similar to what we have in the export, we also cannot use a standard import script to import the data as shown in the export file above. In this file, we have a "special" data structure that has to be recognized by the import script. But as you will see, it is also not too complicated to write a customized import script.

Basically, as a template, we can use the standard import script generated when a standard catalog import is create via Collaboration Manager > Imports > New Import. When creating an import this way, similar configuration steps have to be done as already seen when creating an standard export. We also have to define a spec and map that determines that values in an input line (from the file to be imported) has to be mapped to an attribute of an item in the catalog. The spec in this sample is called "file spec" (it describes the values in the import file) and consists of four attributes because an employee item also consists of four items.

Figure 7. Filespec required for import">File spec required for import
Screenshot shows the details of the file spec required for import

The script making use of this file spec looks like the listing below.

Listing 10. Standard import script
var parser = newCSVParser(in);
var bDone = false;
while(!bDone) 
{
    var attrs = parser.splitLine();
    bDone = (null == attrs);
    if (!bDone)
    {
        var itemAttrs = [];
        itemAttrs["file spec/c01"] = attrs[0];
        itemAttrs["file spec/c02"] = attrs[1];
        itemAttrs["file spec/c03"] = attrs[2];
        itemAttrs["file spec/c04"] = attrs[3];
        var itemValidatedAttrs = validateMappedAttribs(itemAttrs);
        if (null != itemValidatedAttrs)
        {
            var item = buildCtgItem();
            item.setCtgItemMappedAttribs(itemValidatedAttrs);
            item.saveCtgItem();
        }
    }
}

This script provides a parser that reads the file to be imported (reference by variable in). Because this file has a CSV format, a CSVParser is used. This parser provides an array ("attrs"), where each array element represents a value of the CSV line read. After a line from the import file has been read and stored in the array, the array elements will be mapped to attributes of a new item to be created. This mapping has been previously defined by the "map," while the import has been created. The advantage of using the array and the mapping is that we assign all values to their corresponding item attributes in one operation (setCtgItemMappedAttribs()). There is no need to assign each value to its attribute one by one.


Create a customized import

Unfortunately, this standard import script does not fulfill our requirements. The export file created by our customized export script creates a different format, which does not meet the standard import script expectations. The first value of each line in the export defines the category of an item. More importantly, the number of values in a line of the file to be imported is not constant. Depending on the number of projects an employee is working on, the number of values for a line is between four (no projects) and eight (four projects). This makes it a bit difficult to use a static mapping between number of values per line in the import file and the number of attributes defined in the spec and map.

To import the exported data as shown above, we have to create our own import script.

Listing 11. Customized import script
var parser = newCSVParser(in);
var bDone = false;

var org_hierarchy = getCategoryTreeByName("Default Organization Hierarchy");

// read until end of file
while(!bDone) {
    var attrs = parser.splitLine();
    
	bDone = (null == attrs);
    if (!bDone) {
        var item = new CtgItem("Employees", false, false);
        item.setCtgItemAttrib("emp_spec/emp_id", attrs[1]);
        item.setCtgItemAttrib("emp_spec/emp_name", attrs[2]);
        item.setCtgItemAttrib("emp_spec/emp_department", attrs[3]);

        var num_projects = (attrs.size() - 5) / 3;
        var offset = 3;

        for (var p = 1; p <= num_projects; p++) {
            var specpath = attrs[p*offset+1]; // emp_spec/<project>#n
            var xctg = getCtgByName(attrs[p*offset+2]); // catalog name
            var pk = attrs[p*offset+3]; // primary key
            item.setCtgItemRelationshipAttrib(specpath, xctg, pk);
        }
		item.mapCtgItemToCategory(org_hierarchy.getCategoryByPath(attrs[0], "/"));
        item.saveCtgItem();
    } // if (!bDone)
} // while (!bDone)

The good news is that the import script is compact and does not contain a huge amount of source code. But please be aware that the import script above does not contain any sanity checks regarding the items/employees to be imported or any other error checking. For example, item.saveCtgItem() can return validation errors that should be handled. As shown in the standard import script, we are using a CSV parser, which provides the functionality to split the line into attribute values. These values can be used to create the new item (employee) as parameter for method setCtgItemAttribute(). The remarkable part of the import is the way the relationship attribute is satisfied. Because of the design of the import file, we have to isolate all related projects from an input line. Please review the format of an input line with or without projects.

Figure 8. Item/employee with no assigned projects
Image shows CSV file structure of employee with no projects
Figure 9. Item/employee with one assigned project
Image shows CSV file structure of employee with one project
Figure 10. Item/employee with two assigned projects
Image shows CSV file structure of employee with two projects

The first four elements in the array "attrs" always belongs to the employee item: category, emp_id, emp_name, and emp_department. Further elements have to be handled in packages of three elements; these packages are describing a project on which this employee is participating (spec path, catalog (Projects) and the primary key of a project). The for loop is looking for such packages and creates the relationship attribute for the item/employee. Finally, the employee item is saved within the catalog.

This script can now be used for an import created via Import Console. In the screenshot below, you can find all required information to complete the import definition.

Figure 11. After new import
Image shows all relevant settings to define a new import

Steps 5, 7, 10, and 11 require special attention:

  • Step 5: This step defines how the data to import will be provided. Here, Upload via Web browser is selected. This means the CSV file is uploaded into docstore via the browser, and the import script will read that file from docstore.
  • Step 7: As mentioned, you have to provide a file spec for import. The file spec defines what kinds of columns are stored in the CSV file. Because the import script is managing the structure of the CSV file on its own, a dummy spec can be provided (in this case, the spec consists of only one attribute).
  • Step 10: Same as in step 7. A map defines the mapping between the sequence/order of attributes in a file (file spec) and the sequence/order of attributes in a spec used for an item. The import script is managing his mapping so a dummy map is sufficient here.
  • Step 11: In this step, you can provide the script. Just copy/paste it into the script editor.

After the import has been created, you should find it in the Import Console. Upload the CSV file that has been created by the export and run the import. You should find your import in the Import Console as depicted below.

Figure 12. Import Console
Image shows the import console

If you don't want to create another company for testing, you can simply remove all items in catalog Employees and run the import. Then run the import and all employee items should be restored.

Figure 13. Result of import
Image shows the import result

To verify that the import was successful, you should open an employee participating in a project (e.g., employee Hinz in EMEA > Germany). If the import was successful, it should look like the figure below.

Figure 14. Employee item after import
Screenshot shows employees after successful import

Considerations

The export and import scripts represent a solution for an non-complex use case. Even for this easy use case, several solutions and implementations are possible. For example, in the scenario described, a custom pattern for multi-occurrence attributes has been used. By default, when using a standard export, MDM CS is using a different pattern to identify multi-occurrence attributes in an export file. Maybe you would like to compare this solution with the standard export format of multi-occurrences provided by the MDM CE engine to get inspired by other solutions.

Whenever customizations are created, you should be aware that changes in the data model can also affect your customization. For example, if the employee spec is changing for any reason, you may also have to change your export and import script because the references to those specs are hard-coded within the script, and changes will not automatically applied to the script. This will increase the maintenance efforts. But on the other hand, for relationship attributes, there is currently no alternative, which makes these efforts necessary when relationship attributes have to be exported and imported.


Download

DescriptionNameSize
WPC scriptssample_cmp_scripts.zip4KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=835817
ArticleTitle=Handling export and import of relationship attributes in IBM Master Data Management Collaboration Server
publish-date=09202012