Issues we are facing
Data type is the objects that are often under change during the implementation of XI / PI project due to the business requirement change or technical design change. If the changes are just based on the existing structure then they can be easily handled. For example, add additional elements or sub-elements. However I came cross chances that I need to add additional level in root, or insert additional level in the middle of the structure, in this case, my previous data type can not be re-used, I have to build new data type from scratch. If the data type contains 100 elements or more, we can imagine the time wasted on rebuilding them: each element added into data type need performing a couple of steps.
As we all know the data type we defined is nothing but a XSD schema file. So I just wonder if there is new way to build the schema file. As we stick to MS application all the time and familiar with its editing features like drag & drop, copy & paste etc. Can we just take advantage of those MS features? The idea encouraged me to explore the possibility, eventually, I came up a MS Excel based tool which I called it " Data Type Generator".
Data Type Generator Overview
Now let us take a look at how Data Type Generator look like, and how it can be used to generate data type xsd file.
Maintaining datum based on certain rules is important for error-free data type generating process. Let us exam how the datum are maintained in "Data Type Generator" by taking an example of employee data type. Suppose I would like to create a data type to reflect a structure shown in Fig 1 below:
Fig.1 Data Type Generator Over View
- Cell B1: for Name Space, the name space has to match the name space in your integration repository.
- Cell B2: for Data Type Name. this is the data type name which you are going to create.
- Cell C1: specify generated schema file location in your file system. Make sure the user has write permission for the directory the file is going to be created.
- Row 3: are just header information, the contents in this row are not used to generate data type.
- Row 4 to end of the sheet: the datum in this area are maintained in following ways, let us look at them column by column:
- Sequence Number:Just used to index the row number, this allow user to quickly locate the row when exception occurs.
- Structure: This is most import column since all the nodes name will be maintained in this column. The Root node left justified. Child elements need to have four leading spaces.
- Category: Either "Element" or "Attribute", in this version, only "Element" is supported.
- Type: specify xsd type for the child most elements. If the node is not child most, just leave this column content empty.
- Occurrence: Here you specify minimum occurence and maximum occurence for the corresponding elment nodes, "0..unbounded" is represented in the format of "0..N".
There are three buttons on right side of the spread sheet,:
- "Check Readiness": Use to check if the datum maintained in the tool are in compliance with all the pre-defined rules, if incompliance are found, they will be reported in a pop up window, user can make proper correction based on the exception report. If there is no incompliance reported, there is the pop up window saying "Check Passed: Ready to Generate Data Type", see Fig.2.
Fig.2: Check Readiness Button:
- "Generate DT": Once the "Check Readiness" give you green signal, you can click this button to generate the xsd file, then the pop up window saying "Done", See Fig.3, then the xsd file is generated in your specified location, see Fig. 4
Fig 3. Generate DT Button
Fig 4. "Generate DT Button: xsd file is created
- "Read Me": pop up a document to guide user to use the tool properly. It just displays mini-version of this blog in pop up window.
Incompliance Reporting
Now let us take a look at how incompliances are reported by clicking "Readiness Check" button:
Incompliance 1, no xsd schema file specified
A pop up window will be shown by clicking "Check Readiness" button if there is no schema file name specified in Cell C1. In our case, we got following screenshot when we leave cell C1 blank. See Fig. 5.
Fig 5: Incompliance 1
Incompliance 2, the child node does not have 4 leading space
In such incompliance situation, by clicking "Check Readiness" button, you will see a incompliance report pop up window.
in our case, we have 5 leading space in element "Fname" comparing with its parent node "Employee". Plus incompliance 1, then we got following screenshot by clicking "Check Readiness" button. See Fig.6.
Fig.6: Incompliance 1 and 2
Incompliance 3, Node that has child nodes is typed.
In our case, Element "Employee" has child nodes, but there is "xsd:string" in the type column. Plus incompliance 1 and 2, therefore the incompliance reporting pop up window is demonstrated in Fig.7.
Fig.7 Incompliance 1, 2 and 3
Steps of Generating Data Type
- Maintaining the datum in the spreadsheet.
- Click "Check Readiness" button.
- Correct any incompliance, Click "Check Readiness" button again.
- Repeat Step 3, and 2 until you got message "Check Passed..."
- Click "Generate DT" button, xsd file is generated.
- Import to your data type editor in Integration Repository: you switch to "XSD" tab to import the xsd file, see Fig.8. Once the import process finished, switch to "Type Definition" type, you will see your data type has been created. See Fig.9.
Fig.8: Import xsd file
Fig. 9 Data Type created
Limitation
- Only support elements
- Need to enable macro when starting MS Excel.
- Can not set the MS Excel Macro security to high or very high.
Bluesky
