Graphical Using Representations and Historical Data of Like Items to Assist In Root Cause Analysis
Introduction Root cause analysis of data integrity issues within manufacturing systems is a vital aspect of continued system performance. Currently root cause is first hypothesized after a collection of data from manufacturing reports, and reporting tools that display information in a spreadsheet. When identifying issues with large data sets this method allows for a large amount of oversight and a great deal of user strain. It is also possible to misinterpret the data at hand due to an overabundance of data or a lack of pertinent data. Current reporting tools require the user to write queries or string commands to pull the requested information from the manufacturing database. The ability to create and execute these queries is a learned skill requiring specialized training, and is therefore unavailable to the company as a whole. The reports that are created by way of these queries are large spreadsheets with Y/N or coded entries signifying the status of an item. This presents another issue in analysis, one must first understand the function of a particular field, or the coded entry, before analyzing the data within.
Item Status/ Type Attempting to compensate for the above issues with a small data set would merely require an increased user understanding. The issue to be addressed is that of a world wide corporation with one million plus items. These items are broken down into item status. Prototype used for the initial build and testing of an item Active used for items that are actively being sold and transacted
MFG Active Represents items that are not sold but are used to manufacture other items. Obsolete Represents items that are no longer purchased, or manufactured for sale but must still be sold or transacted to alleviate inventory Inactive Items are no longer purchased, manufactured, sold, or transacted. These items are historical records.
The above item statuss must also have an affiliated item type. They are as follows: Free Stock Items which fall under a two dollar value and are not tracked in inventory Purchased Items which are purchased for sale from an outside vendor Make Items that are manufactured in house Finished Goods Items that have been made and are packaged to sell. Kit An item representing a bundle of specific finished goods.
The item status and type will restrict certain items from being transacted in certain ways and allow transaction for others. For this paper the transactions to be considered are: Purchasable, Stockable, Shipable, Sellable, BOM (Bill of materials) Allowed. In Figure 1 the relationships between the type and transaction are outlined. As shown not all types of products can be transacted in certain ways.
Type
Free Stock
Transaction
Purchasable
Purchased
Stockable
Make
Shipable
Finished Good
Sellable
Kit
BOM Allowed
Figure 1 Type, Transaction relations
In Figure 2 the relation ship between status and type is outlined. As illustrated some statuss cannot be assigned to certain types of products.
Status
Prototype
Type
Free Stock
Active
Purchased
MFG Active
Make
Obsolete
Finished Good
Inactive
Kit
Figure 2 Status, Type relations
In Figure 3 the relationship between status and transaction is illustrated. In order to have proper data alignment there must be a connection between the type and the status, the type and the transaction, as well as the status and the transaction.
Status
Prototype
Transaction
Purchasable
Active
Stockable
MFG Active
Shipable
Obsolete
Sellable
Inactive
BOM Allowed
Figure 3 Status, Transaction relations
Data integrity issues arise when a status is assigned to a type of product that it should not be assigned to or a transaction is being allowed for a type of product that it should not be allowed for. For example a Prototype Status can not be assigned to a free stock item type, a free stock item type can not be sellable, and an inactive item can not be sold.
Data Integrity and Root Cause Analysis The issue of data integrity is two fold. First if an item is set up improperly it may not perform as needed, second the item may perform in way that it is not meant to. Currently weekly reports are generated into spreadsheets in order to find data inconsistencies. Figure 4 is an extraction from a current data integrity report.
83 83 83 83 83 83 83 83 83 83 10947 10982 11001 11010 10777 10783 10794 10796 10797 10802 DIO VVC BB505B 20PF@-3V DO35 CLAMP RIBBON CABLE 1"WIDE PLAS IC CMOS 4066 QUAD ANALOG SWCH RES NET KOHM 10 COM 6SIP CAP CE PF 820 CAP CE PF 100 5% 2A CG 10% 2A RD Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive MP MP MP MP MP MP MP MP MP MP N N N N N N N N N N Y Y Y Y Y Y Y Y Y Y
RES MF KOHM 82.5 1% 1/8W IC CMOS 4069 HEX INV UNBUF RES MF OHM 475 1% 1/8W
IC CMOS 74HCT 00 Q 2I NAND DIP
Figure 4 Data integrity report
This format of data representation does not allow for any historical information on the items, nor does it represent the items relationships to themselves. The proposed design will utilize the hover technique to display the number of items that are exhibiting the same error as well as the percentage of items within any given selection that have data integrity issues. The later will improve on current methods of comparing integrity issues with all available items. This approach will show fifteen items out of one as million an extrodinarily small fractional error. To display this in context would be to show the percentage of like items which have data integrity issues. For example there are 1000 obsolete kits. The true error rate for obsolete kits is therefore 1.5%. The integrity standard for data is 99.5%, therefore obsolete kits are in violation of that standard and should be given priority on a clean up. This application could be further aided by a transition to a graphical representation of the percentage of error. The user could then see with a glance potential issues pertaining to integrity.
Proposed Display The first step in the proposed display would be a selection step, where a user could drag and drop the attributes (type, status, transaction) to be reported on, into a reporting field. The attribute sections would be color coded to alert the user to their difference. Modifying the query tool to allow for drag and drop queries will eliminate the need to train users on the writing and use of string commands to pull information. This will increase the user base by increasing the ease of use.
Status
Type
Transaction
Prototype Active MFG Active Obsolete Inactive
Free Stock Purchased Make Finished Good Kit Sellable Stockable
Purchasable
Shipable BOM Allowed
Report
Inactive Kit Purchasable
xY
N
Figure 5 Proposed query display
In the above query, the user has selected to report on inactive kits that have are allowing a purchasing transaction. Running this query will bring the user to the query report screen.
Query1: Inactive Kits Purchasable Y Total # Like Items: 1000 Total # Like Errors: 45
4.5% error
Back New Query Add Query Revise Query
6
Items
Item 4895 5509 3345 7634 1689 9371 1569 0863 4598 2354 7854 Status Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive Inactive Type Kit Kit Kit Kit Kit Kit Kit Kit Kit Kit Kit Purchasable Y Y Y Y Y Y Y Y Y Y Y
5 4 3 2 1 0
05 06 06 06 05 05 06 05 05 06 1/ 1/ 06 /1/ 11 05 /1/ 1/ 1/ 1/ 1/ 1/ 1/ 1/ 1/ 1/ 1/ 07
7/
1/
3/
5/
9/
1/
3/
5/
7/
Item: 4895 Previous item update: 1/25/07 Reoccurrence of error: 36 Last error date: 1/28/07
Figure Data display
11
9/
The display in Figure 6 represents the comparison of the requested items to the entire like data set and also supplies the user with a chart representing the percentage of the whole that is incorrect. This will help in the prioritization of the data correction efforts. The display also provides hyperlinks to return to the previous screen, to perform a new query, to add a query to the existing query, and allows the user to revise the current query. The items list at the bottom of the display can be selected to populate the information at its right. By selecting the first item in the list a chart of errors in the past twenty four months populates to allow the user to see if there are certain months or time of year that this item has been edited to the incorrect transaction type. Finally in the lower right hand corner the user can view the item number selected, the last time the item was updated, how many times since its creation it has appeared on an integrity list, and the date of the last error. The last item update, its occurrence rate, and date of last error are all v...