Solving Planning Problems using Dimensional Modeling

Abstract

Today Egyptian industry has been affected by many problems that caused industry obstacles that have significant impact on the Egyptian economy. One of basic common • problems to all manufactures is planning. Planning types contain long-range planning, medium-range planning, and short-range planning; each of them is complex by nature and need to be decomposed into smaller sub problems that arless complex, more structured and easier to be solved. Capacity Requirements Planning (CRP) is one of the medium-range planning problems. CRP is a detailed comparison of the capacity required by production plans versus the available production capacity and it determines how much labor and machine resources are required to accomplish the production plans. If adequate capacity cannot be established, corrective actions should be taken. When available capacity is insufficient despite corrective actions, the production planner reviews relative priorities and, working with marketing and production responsible, makes the difficult decisions required in revising the schedule. • This research aims at providing a tool that uses computerized algorithms, information technology, decision functions, what - if analysis and scenarios to help manufacturers in solving one of planning problems (CRP problem). A decision support tool is developed to _, support decision maker with alternative policies for optimize the use of your production resources by highlighting bottlenecks and letting user simulate changes in production plans anresources. To put a practical solution, the researcher studied the planning procedure in one of the biggest industrial companies in Egypt: Maadi Company for Engineering Industries (MCEI). The researcher captured the MCEI data from several internal and external sources to create DSS database. Internal data includes product tree, routing file, machine status, machine failure, machine required time and machine state. External data includes production plan, extra orders from customer and working hours. He also uses tactical models, operational models, and other quantitative mathematical models that provide the analysis capabilities in a Decision Support System (DSS). A data mart is designed to represent the problem from all possible views. OLAP cubes are built to provide multidimensional view of data, complex CRP calculations and different scenarios. Different types of interfaces are implemented such as windows interface, and pivot table interface. These interfaces facilitate data visualization through the use of cube browser interface, minimize the user movement between screens, and facilitate user input through the use of graphical user interface. If the available capacity is sufficient, the proposed solution allows production planners to add extra orders to the production plan.