Date: 23-Jul-08
ITC Infotech India Ltd #18 Banaswadi Main Road Bangalore, India - 560005 +91-80-22988601 www.itcinfotech.com
Windchill – Excel Office Document Data Integration
Author: JGS.Raj
Contents
Executive Summary 3
Business Challenge 3
I3L – ISC Solution 3
Technical Specifications 4
Benefits 7
Target Market 8
Executive Summary
People at various responsibilities in an organization are highly
comfortable with Microsoft office products to capture relevant
information / data. Excel document stands very popular as tool to capture all business / general data. This could be quicker solution on enterprise data handling but
accumulation of data on such passion would post huge risk to an
organization. Following are the risks foreseen
• Loss of data
• Machine and people dependency
• Delay on data availability for other groups
• As no control on data, possibility of loosing uniqueness in market
To avoid all risks related to
uncontrolled data, every organization are using enterprise systems for their data management. These enterprise systems capable of accommodating all data collated from various groups of the organization on a neutral format. Windchill is one of such enterprise system capable of
managing both complete engineering data of products from an
organization and their related business information. Windchill provides very rigid access control, well defined workflows and efficient reporting mechanism for effective handling of enterprise data.
Business Challenge
A global networking solution major, faced a challenge with data at their business.
All of their ROI reports existing in Excel document right from their inception. This posted huge over head on handling these documents as they are huge in number
For better control, they loaded their entire ROI report documents as content into Windchill system. This resolved safe storage of data and ease of control, but was not able to generate read only reports from the existing data.
Quicker report is essential as CFA (Cross functional Agreement – Concept Commit Gate) report is very essential to take business decision.
I3L – ISC Solution
PTC GSO and ISC jointly proposed the following solution approach for the challenge customer undergone
Load all excel documents into Windchill system as primary content of WTDocument object
Define another Document sub type object with soft attributes to hold all business data
Customize workflow, such a way, data from content of WTDocument (excel document) object would be read and set the values to relevant attributes defined in Document sub type object
Use this Document sub type object for Windchill based report
generation (Cognos based, Html based, etc)
This solution would achieve following, better control on all existing organizational /business data and superior reporting from relevant data.
Technical Specifications
This section describes the solution in detail.
Data Loading: PTC supported, load from file method would be the best way to load all pre existing Excel document data. One can configure the document object name while loading the data. All excel files would become primary content of WTDocument object.
Document Object Type & Soft Type Attribute definition: Creation of soft type object and soft attributes
Customization: The work flow attached for WTDocument object housing excel file as primary content would be customized to trigger automatic data transfer from excel file to the designated document type object
1. Workflow Change: Include one expression robot in approval process flow. Inside robot, call two methods, one to construct document type object and another one to execute the data transfer.
2. Constructing Document type object: Get the document type information from the source business object, and either query from data base, if it exists, or create and persist new one if it doesn’t exists. All these logic would go either inside
expression robot or a method called “constructObjectType()” which would be implemented as part of Windchill Module.
3. Data Transfer: The solution is visualized as component based approach and provides high scalablity. Data transfer module could be sub divided into three sub modules such as Windchill Module, Excel Handling Module and Configuration Module. The logical decomposition is shown as below
Data Transfer Module Workflow Windchill Module Excel Module Configurati on Module 3rd Party Plug In
3.1 Windchill Module: This module
accommodates all logic related to Windchill side operation such as obtaining primary content, setting & persisting IBA values. This module would have two new classes created and implemented. See the graphical representation of the classes and subsequent solution approach
Following are details of solution approach
“processExcelValues” would be a public method and having two arguments as (WTDocument source, WTDocument target)
This method would be called from expression robot passing by two document objects
Inside above method, call “getContentStream” to get primary content as stream object
getContentStream is a private method to fetch primary content from source object and return as InputStream object, this would also verify file type and throw exception Pass this Excel file input
stream object into Excel Handling module for data processing
Call private method
“getWorkingCopyOfWTDoc ument” to get the working copy of the target object for further update
getWorkingCopyOfWTDocu ment method validate and return working copy of the target object.
Get IBA attribute details from Configuration module, get the appropriate value from Excel Handling Module
Create object of IBAUtility class by passing target object as argument. IBAUtility class would have custom
constructor taking IBAHolder object as argument. Inside the constructor, call setIBAHolder method
“setIBAHolder” is private method to set target object as the target
IBAHolder for further update “setValues” method would set value
on to the IBAHolder against the IBA attribute.
“updateIBAHolder” method is to update the IBAHolder object after setting the values against all IBA attributes.
Both “setValues” and
“updateIBAHolder” methods needs to be called from
“processExcelValue” method Implement loop to set all IBA
attribute values
3.2 Excel Handling Module: Excel handling module going to have one class named “ExcelUtility”. This would also need to get service from a third party jar called “jxl.jar” +ExcelUtility() +loadExcelFile() +getWorkSheet() +getExcelValue() +validateCell() +processValue() +isNum() ExcelUtility
This class would have one custom constructor and six new methods implemented to achieve the objective. Following are the details about approach
The custom constructor would have two arguments such as InputStream object and PropertiesUtility object.
PropertiesUtility is a custom class created for
Configuration module. “loadExcelFile” method would be called from the constructor
loadExcelFile is private method taking InputStream object and PropertiesUtility object as arguments. This would load the excel file (InputStream form) into the system and calls
getWorkSheet to construct the exact work sheet for process.
getWorkSheet is a private method taking InputStream object and PropertiesUtility object as arguments to construct exact worksheet from the entire excel file for further data handling. The work sheet name would be obtained from
PropertiesUtility object getExcelValue method is a
public one and would be called from
“processExcelValue” method by passing row number, column id and data type. getExcelValue method would
intern call following methods and returns the value.
o validateCell o processValue
validateCell method is private. This would validate whether the data type
expected and the data type of the cell is equivalent,
otherwise would throw exception to user. processValue is private
method. This processes the
formatting of number values. This in-turn calls isNum method.
isNum method is private, it validates whether passed in value is number or not.
3.3 Configuration Module: This module consists of two elements. Four properties files to hold all configuration details and a new class PropertiesUtility to load all configuration details into run time environment. +PropertiesUtility() +loadAllProperties() +loadParameterfromProperties() +loadParameterRowNumberfromProperties() +loadParameterColumnIDfromProperties() +loadIBAMap() PropertiesUtility
See below the details description of the approach
ParametersConfig.properties would hold all parameters that could be processed from Excel file
ParamatersRowNoConfig.properties is a properties file which would have a row number map against individual parameters listed in
ParametersConfig.properties ParametersColIdConfig.properties would hold column id information. This would also have the data type information and worksheet name. ParametersIBAMap.properties would contain the map between the value of
individual cells against IBA attribute name
PropertiesUtility class constructor would call “loadAllProperties” method “loadAllProperties” method would call other four methods listed in the class diagram.
Private method
“loadParameterfromPropertie s” would load all entries from ParametersConfig.properties into a hash table.
loadParameterRowNumberfr omProperties is private method to load all row numbers map against the parameters into a hash table loadParameterColumnIDfrom Properties, this method would read values from
ParametersColIdConfig.prop erties file and load hash table with column ids, hash table with data type and a string variable with worksheet name.
loadIBAMap would load all values from
ParametersIBAMap.propertie s into a properties object. PropertiesUtility class would also be implemented with more get methods to return hash tables, properties and string values from this class object.
Benefits
The solution described in this document posts many benefits to the customer. See some of them below
1. Data Security and Ease of Handling: • This solution, Windchill as
enterprise system provides central repository for all organizational business data accumulated right from inception of the company in the form of Excel document. This provide more security to the business related data as Windchill is having
sophisticated ACL implementation • As data in Excel document is read
and stored as Windchill IBA values, critical resources in the organization would easily access those data in the form of simple reports generated from Windchill system, hence it would help them to take business decision quickly.
2. Correctness of Data:
• As this solution suggest having custom workflow, which would insist the responsible people to validate the data before it is getting approved for data transfer into Windchill IBAs.
3. Flexible and Scalable Solution: • Data transfer module is provided
with Configuration module. This configuration module provides better flexibility to the customer that they can define from which work sheet the data needs to be retrieved, how many parameters are expected to fetch from Excel file for IBA update. • They can also change the IBA
attribute name in Windchill system, and then modifying the IBAMap properties file would enable them to
complete the Data transfer successful.
• The solution envisaged for data transfer module is such a way, there is no external dependency on this solution. And this application a simple java application and would work for any version of Windchill implementation. This gives greater scalability to the customer, when they are thinking of moving to higher version of Windchill implementation.
Target Market
Due to the scalability of this solution and common use of Excel document for data gathering in various
industries, this solution could be applied for lot of Windchill customers.
Following is the sample list of potential beneficiaries of this solution
Project team in any industry that uses Excel to calculate ROI of project / program Engineering industry people
who manages their
engineering data calculations in Excel document
Quality control organization holding their tracking
summery in Excel document
Summary
The solution described in this document would add following benefits.
Central repository and better control to the business data
Data validation and correctness Ease of data access and quicker
business decision More flexible Best scalability
