The following topics could be interesting for future research:
The implementation of a central master data database to ensure consistency and create a clear and structured interface and master data management tool.
More research is needed to ensure accuracy of master data. Apart from the obvious outliers and errors, all data fields should be able to be tested for accuracy.
41
BIBLIOGRAPHY
Ballou, D. P. & Pazer, H., 1985. Modeling data and process quality in input multi-output information systems. Managment Science, Vol. 31(2), pp. 150-62.
Batini, C., Cappiello, C., Francalanci, C. & Maurino, A., 2009. Methodologies for Data Quality Assessment and Improvement. ACM Computing Survey, Issue Vol. 41(3).
Blumberg, B., Cooper, D. R. & Schindler, P. S., 2011. Business Research Methods. Third European Edition ed. Maidenhead(Berkshire): McGraw-Hill Education.
Bovee, M., Srivastava, R. P. & Mak, B., 2001. A conceptual framework and belief-function approach to assessing overall information quality. Proceedings of the 6th International Conference on Information Quality.
Jarke, M., Lenzerini, M., Vassiliou, Y. & Vassiliadis, P., 1995. Fundamentals of Data Warehouses. 2nd ed. s.l.:Springer Verlag.
Loshin, D., 2008. Master data management. Burlington(Massachusetts): Morgan Kaufmann.
Naumann, F., 2002. Quality-driven query answering for integrated information systems.
Lecture Notes in Computer Science, Issue Vol. 2261.
Ofner, M. H., Straub, K., Otto, B. & Oesterle, H., 2013. Management of the master data lifecycle: a framework for analysis. Journal of Enterprise Information Management, Vol.
26(4), pp. 472-491.
Redman, T. C., 1997. Data quality for the information age. 1st ed. Norwood: Artech House.
Redman, T. C., 1998. The impact of poor data quality on the typical enterprise.
Communications of the ACM, Vol. 41(2), pp. 79-82.
Sidi, F. et al., 2012. Data Quality: A Survey of Data Quality Dimensions. International Conference on Information Retrieval & Knowledge Management.
Smith, H. A. & McKeen, J. D., 2008. Developments in practice XXX: master data
management: salvation or snake oil?. Communications of the Association for Information Systems, Vol. 23(4), pp. 63-72.
Tayi, G. K. & Ballou, D. P., 1998. Examining data quality. Communications of the ACM, Vol. 41(2), pp. 54-57.
van Aken, J. E., Berends, H. & van der Bij, H., 2012. Problem solving in organizations.
Cambridge: Cambridge University Press.
Wand, Y. & Wang, R. Y., 1996. Anchoring data quality dimensions in ontological foundations. Communications of the ACM, Issue Vol. 39(11), pp. 86-95.
42 Wang, R. Y., 1998. A product perspective on total data quality management.
Communications of the AIS, Vol. 41(2), pp. 58-65.
Wang, R. Y., Lee, Y. W., Pipino, L. L. & Strong, D. M., 1998. Manage your information as a product. Sloan Management Review, Vol. 39(4), pp. 95-105.
Wang, R. Y. & Strong, D. M., 1996. Beyond accuracy: what data quality means to consumers. Journal of Management Information Systems, Vol. 12(4), pp. 5-34.
43
APPENDIX I
In the table below it is visible what different departments in the company indicated as problems regarding (master) data management.
Information
System Maintenance Communication Capacity Leadership Knowledge
Sales X X X
Planning X X X X X
Purchasing X X X
Supply
Chain X X
Accountant X X X
Corporate X X X
44
APPENDIX II
Below the interview is shown that is used in the meetings to investigate the as-is situation.
The interviews were held in a semi-structured way, so during the meeting new questions or different perspectives on the problem arose. The following questions were prepared and asked during the interview:
1. Do you work with (master) data during your normal tasks?
2. Do you ever check this data on its accuracy?
3. Are there checks or tests in place that can be used to test the data quality?
4. What requirements would such a test have in your opinion?
5. For what data would it be useful to check the quality?
6. What errors do you expect to find in that data?
If there are tests in place:
7. How often do you run the test?
8. What data is checked in the test?
9. What checks are done within the test?
10. What is done with the results of the test?
45
APPENDIX III
Data quality checks that sales implemented for their data
46
APPENDIX IV
List of data variables used in tool
47
APPENDIX V
Data sample for validation
Item NumberDemand 6 MonthsDemand 12 MonthsPlannerFull Cumulative Order Lead TimeEP-ReviewerInitial BuyerItem Type[Descr.]Agreed Lead TimeEconomic Order Qty. by BPInbound Lead Time WarehouseOrder Quantity Increment WarehouseMinimum Order Quantity WarehouseFixed Order Quantity WarehouseEconomic Order Quantity WarehouseSafety Stock WarehouseLead Time WarehouseCost Price(Estimated) Excess StockES AmountOn OrderOn Order AmountOrder Quantity IncrementMinimum Order QuantityFixed Order QuantityEconomic Order QuantitySafety StockEconomic Order Qty Sales41100038216025015PU13708215PL005815PU2110Purchased0,029544513,1275001010110141100065811311315PU19104415PL001815PU2110Purchased4,2832136,96000011014110012111105154915PU19102415PL005815PU1210Purchased3010,8669-1084-939,72000011014110012423315PU19102415PL005815PU1210Purchased2,7218616,33080000110141100127346674915PL0088215PP0115Purchased3011,55-534-827,700001101411001723595101515PU13705915PL005815PU2110Purchased1510,088213011,4660000110141100253910410415PL0038215PP0207Purchased0,4257-4-1,702800001101411002546151515PU13707415PL003815PU2110Purchased0,40593514,206500001101411002577696915PL0028415PP0207Purchased0,1945-52-10,1140000110141100258412512515PU13707415PL003815PU2110Purchased0,2314-86-19,900400001101411002591707015PU13708415PL003815PU2110Purchased0,57451810,3410000110141100296640440415PL0038215PP0205Purchased0,39-150-58,517267,0800110141100319226026015PU13703415PL001815PU2110Purchased0,042978119,1200001101411003239101015PU19103415PL001815PU0910Purchased0,38131,1430000110141100453313013015PU13706915PL001815PU2110Purchased0,251197299,25000011014110048239374937415PU13705915PL003815PU2110Purchased0,0197621,221400001101411004830113931139315PU13708415PL003815PU2110Purchased0,0351130,4563000011014110049773222322215PU13706415PL003815PU2110Purchased0,04181687,022400001101411005202121215PL0088215PP0110Purchased0,26-6-1,560000110141100522662162115PU13705415PL003815PU2110Purchased0,09562264216,4384000011014110065138815PU19103415PL008815PU2110Purchased0,11623627,37600001101411006704131315PU19105415PL001815PU2110Purchased0,1611674108,5814000011014110068722110523015PU19104415PL005815PU2110Purchased0,2341-1564-366,13200001101411006988373715PU19105915PL005815PU2110Purchased0,239-8-1,9120000110141100700828046015PU19704415PL008815PU2110Purchased5610,28-135-37,80000110141100701546765215PU19103915PL008815PU2110Purchased0,2898-59-17,0982464134,467200110141100702251775715PU19703915PL008815PU2110Purchased0,345-17-5,865000011014110073674415PU10101915PL008815PU1210Purchased0,2276715,20900001101411007459686815PU10101915PL008815PU1210Purchased0,14914020,8600001101411007466171715PU10101915PL008815PU1210Purchased0,217922348,5917000011014110077566615PU10101915PL008815PU1210Purchased0,138723232,178400001101411008043909015PU10101915PL008815PU1210Purchased0,3607103,60700001101411008098788115PU10101915PL008815PU1210Purchased0,5747-14-8,04580000110141100830216116115PU13707415PL008815PU2110Purchased1,29-115-148,3500001101411008319202015PU137011915PL005815PU2110Purchased1511,341621,442634,8400110141100832616121115PL0088215PP0110Purchased1,3-156-202,80000110141100833358892415PL0058215PP0110Purchased1,542-621-957,582198305,31600110141100834016528515PU13705915PL008815PU2110Purchased1,46-139-202,940000110141100836418218215PU13707415PL008815PU2110Purchased1,522233,440000110141100918714014015PU13109415PL003815PU2110Purchased0,5550011262,160011014110092483444470815PL0028215PP0205Purchased0,0598-3007-179,81952931,6342001101411009613808015PL0038415PP0207Purchased0,04863230,3360000110141100983543643615PU19703915PL005815PU2110Purchased99910,09-65-5,85000011014110098736615PU10101715PL006815PU1210Purchased0,31054513,9725000011014110101458815PU131011915PL007815PU2110Purchased0,87073732,215900001101411010152737415PU131014915PL007815PU2110Purchased0,3631,08000011014110106951890262515PU13706915PL005815PU2110Purchased99911,6731-1110-1857,1412802141,568001101411010701746131715PU13704915PL005815PU2110Purchased1,8315-589-1078,7500001101411012606131615PU10101915PL006815PU1210Purchased0,99882019,97600001101411012668303315PU10101915PL007815PU1210Purchased0,3997-68-27,179600001101420441773603169215PU1310100115PU2110Purchased211110100100110142044195712121001Purchased211110100100110142044197112121001Purchased211110100100110142044198812121001Purchased211110100100110142044199512121001Purchased211110100100110142044201512121001Purchased21111010010011016100188012215PU11102415PL006815PU1610Purchased4111302435,9233135,9233001113016100189011115PU11102715PU1610Purchased21111010011111016100190011115PU11102715PU1610Purchased21111010011111016100192016615PU11102915PL007815PU1610Purchased4111302972,21-5-361,0500111301610019301141415PU11102915PL007815PU1610Purchased4111302924001228811110016100194017715PU02106915PL007815PU1210Purchased4111306975,45006452,7131501611030151171715PU18103315PL005815PU0610Purchased31115033111150161103030111111115PU11104915PL008815PU1610Purchased41111504938,5333-31-1194,53762928,5311111501611030551151515PU18101815PL008815PU0610Purchased3111501818,74-10-187,400111501611030651151515PU18101815PL008815PU0610Purchased3111501818,84-11-207,240011150161103100110010015PU14109715PL002815PU1210Purchased753021113009719,45000011130030611031401427015PU02104415PL002815PU1210Purchased411114044111140161103210112621015PU02104415PL002815PU1210Purchased4,54940,86001114201611033251607515PU18102115PL008815PU0610Purchased31111502114,25-60-85500111150161103330140040015PU02105415PL002815PU1210Purchased604041114005433,66702356,20011170040611033952313115PU18103815PL008815PU0610Purchased31115538276451382020552801111501611034051609015PU18102115PL008815PU0610Purchased31111502116,88-59-995,9229489,5211115016110343028711715PU11102915PL008815PU1610Purchased41111502916,278-39-634,842631025,5141111501611034402649915PU11103915PL006815PU1610Purchased41111503921,1813-29-614,258561186,1531111501611034501649915PU11103915PL006815PU1610Purchased41111503917,8-29-516,252925,611115016110348027200960015PU11104415PL003815PU1610Purchased41111200767442,29-6000-13740480010992111120001611099502489315PU17106915PL005815PU0610Purchased9110411115469124,4897-73-9087,75151867,34611110010611100351909015PU17106915PL008815PU0610Purchased9154111506911150561110055118632115PU17109915PL006815PU0610Purchased13330411115799119,3549-169-2017114317067,7511115015611100651285015PU17109815PL005815PU0610Purchased126541115098140,3992-10-1403,99304211,97611150561110095113323815PU17105915PL005815PU0610Purchased77041111505911115015611101051909015PU17107415PL005815PU0610Purchased98541115074111505611101151909015PU17106915PL008815PU0610Purchased9154111506966,373700775110,775111505611101251909015PU17106915PL008815PU0610Purchased9154111506977,257800604635,468111505611101351757515PU17106915PL008815PU0610Purchased9154111506993,613500605616,81111505611101451909015PU17107415PL006815PU0610Purchased98541113074111505611101501909015PU17105915PL008815PU0610Purchased775411150591115056111017019915PU171018415PL007815PU0610Purchased845411130184335,75760041343,031113016111018011215PU171018415PL007815PU0610Purchased411130184111501611101901131315PU171018415PL007815PU0610Purchased411130184334,77281334,7728134352,0461113016111023016615PU171011915PL005815PU0610Purchased140541115011911150161110315330030015PL003818915PP0203Manufactured189100215011034169,506-502-8509225042376,5111100010061110375248990515PL005816415PP0112Manufactured706021060110121116006061110380259089015PU18102115PL005815PU0610Purchased3111603217,31-740-5409,40011160016111039011115PU18101815PL008815PU0610Purchased3111501822,43489,72001113016111043011115PU03109515PL006815PU1510Purchased4111309517,6318317,3410176,311130161110460314623015PL00888815PP0111Manufactured119521511272176,956-186-4049142758777,811130056111046031115PL00888815PP0111Manufactured119521511272176,956-186-4049142758777,81113005
48
APPENDIX VI
In this Appendix the user manual for the master data quality tool is given. The user manual is structured with the use of a process model. Every section covers a process step and describes the necessary actions to be taken during that step.
Gather data
Before even starting the tool, relevant data needs to be gathered from the ERP-system.
Within the ERP-system, several reports need to be extracted which will subsequently be loaded into the tool. Table 3 shows all the relevant reports that should be extracted with the right format.
Table 3 Relevant reports and their format
Format
Tmp .xlsx
Cprpd .csv
Tcibd .csv
Tdisa .xlsx Tdsls .xlsb
Whwmd .csv
Production .xlsx Stock .xlsx
These files can be stored anywhere, it is however recommended to store them in a map on the dashboard because they need to be easy to access.
49 Check data
From this step onwards the master data quality tool will be used. After opening the tool, the user navigates to the ‘Dashboard’ sheet to start the master data quality check. To start the check, the big button ‘Check Master Data’ needs to be pressed, as can be seen in Figure 15.
Figure 15 Dashboard Master Data quality tool
The tool then asks the user to upload the different reports one by one, as seen in Figure 16.
Figure 16 Loading screen of reports into tool
These reports are automatically filtered and stored in a sheet in the tool. After all reports are loaded in the different quality checks are automatically started. Depending on the speed of the computer the tool will run for thirty minutes to an hour. When all the checks are executed, the performance of the master data is visible on the ‘Dashboard’ sheet. The amount of errors on the different checks are described. By clicking the buttons to the right of the performance indicators the data rows with errors are visible in more detail. A list of all variables can be accessed by clicking the ‘Show List of Variables’ button.
Type of Error #Errors Percentage
Amount of Items with >50% HR Delta 637 25%
Amount of Items with >20% HR Delta 1095 43%
Amount of Items with <-20% HR Delta 692 27%
Amount of Items with <-50% HR Delta 375 15%
Amount of wrong dates 37 0%
Amount of wrong Planned Delivery Dates 2731 8%
Amount of Orders below Minimum Order Quantity 1694 7%
Amount of Orders not in right increments 1232 5%
Amount of orders where actual time is longer than Full COLT 793 37%
Amount of orders not ordered in right increments 4 0%
Amount of orders not fully delivered 169 7%
Amount of orders delivered too late 169 7%
Amount of empty cells 3333 23%
Amount of zero values in dataset 931 93%
Amount of zero values in dataset 14375 100%
Master Data Quality Check
50 Communicate errors
By clicking one of the buttons next to the percentage column, all errors of that type are visible. For instance, by clicking ‘Show Planned’, the rows with errors will be visible like in Figure 17. These rows can then be copied into another workbook and send to stakeholders that can fix these errors in the ERP-system.
Figure 17 Example of visible error rows
51 List of business rules
Zero demand Description
Logic If
The demand in the coming six or twelve months is zero Then
The data fields are not relevant at this moment and filtered out Else
None
Planned delivery date should be equal to standard delivery date Description
Logic If
The planned delivery date is not equal to standard delivery date Then
Planned delivery date is not valid Else
None
Planned delivery date should be equal to requested delivery date Description
Logic If
The planned delivery date is not equal to requested delivery date Then
Planned delivery date is not valid Else
None
Amount ordered should be equal or higher than minimum order quantity Description
Logic If
The amount ordered is bigger than MOQ Then
MOQ is not valid Else
None
Amount ordered should be equal or multiples of the order quantity increment Description
Logic If
The amount ordered is not equal or multiples of order quantity increment Then
Order quantity increment is not valid Else
None
52 Minimum order quantity should be equal or bigger than order quantity increment
Description Logic If
MOQ is lower than order quantity increment Then
If historic data about Lead Time does not match with the full cumulative order lead time Then
Full COLT is not valid Else
None
Amount of items delivered should be equal to ordered items Description
Logic If
Amount of items delivered is not equal to ordered items Then
Order number should be checked for completion Else
None
Confirmed completion date should be equal to actual completion date Description
Logic If
Confirmed completion date is not equal to actual completion date Then
Confirmed completion date is not valid Else
None
The difference between predicted production time and actual production time Description
Logic If
The percentage difference between the predicted production time and actual production time exceeds 20%, 50% or is lower than -20% or -50%
Then
Predicted production time is not valid Else
None
53 Empty data fields
Description Logic If
The data field is empty Then
The data field is not valid and should be filled Else
None Filter on item numbers
Description Logic If
The item number is below 4000000 or over 8000000 Then
The data of that item number is filtered out Else
None Default zero values
Description Logic If
Value of data field is zero Then
Value of variable is not valid because it is the default value Else
None