WIXLIB

International Journal of Computer Trends and Technology (IJCTT) – Volume 56 Number 1- February 2018Data Warehouse Design for ElectronicManufacturing CompanyFauziyah1, Metty Mustikasari2,Irwan Bastian3Department of Information Systems, Faculty of Computer Science and Technology,Gunadarma University, IndonesiaAbstract– Recently, managing data inelectronic manufacturing company has become achallenge.This paper presents the design of a datawarehouse based on user needs forelectronicmanufacturing company. The data warehouse isdesigned using Kimball’s Method. The purpose ofthe proposed design is to help decision makers inperforming data processing and data analysis overthe data stored in the warehouse. The datawarehouse design based on user needs whichprovides a data source to support corporateleaders for enhance the decisionmaking process.Database Management System used is OracleDatabase XE. This StudyincludesIntegrity checkprocess and User Acceptance Test.This studyreflects that user find the data warehouse fulfill theuser needs.Keywords– Data Warehouse, Kimball method,Dimensional Model, Integrity Check.I.INTRODUCTIONData warehouse is a subject-oriented,integrated, time-variant, and nonvolatile collectionof data in support of management’s decisionmaking process [1]. Currently thedata warehousesystemsupports not onlyreports nalanalysisandpredictionsthat used for decisionmaking process.A data warehouse is a collection of datafrom several major sources, such as usercan easily analyze the data and createreports [2].Managing data within a manufacturingcompany to be challengeas a result of differences inuser needs, such as presents the total production,total payments or horizontally customers. Managethe data in electronic manufacturing company is achallenge of the different users needs.The idea ofa data warehouseis basedonOnline Analytical Processing(OLAP), ata providequick accessto get informationfor analysisand creating reports.This paper proposes thedata warehousedesignfor Electronic Manufacturing Companybased on user needs which provides a data sourceto help corporate leaders for enhance decisionmaking process. To fulfill users needs, the datawarehouse design used the Spiral modelandKimball method. This paper is divided into fiveISSN: 2231-2803sections. Section Ipresents introduction, section IIpresents related work, section III presentsproposedmethod, section IV presentsdata warehouse design,section V presents result and section VI presentsconclusion.II.RELATED WORKThis research was conducted through afew references as support in research datawarehouse design electronic manufacturingcompany. Several references were made in supportof this research are:Research conducted by Alpa R Patel andJayesh M Patel, 2012 examines the data warehousemodel using model Entity-Relationship and datamodels dimensional. This study describescomparison of two data model. The conclusion isthe Entity-Relationship in the design of datawarehouse specifies the dependency relationshipbetween the data without fact tables. Dimensionaldata model is preferable to design a data warehousefor company. The dimensional model has goodqueries performance to support OLAP functions[3].Oketunji and Omodara, 2011, conducted tosupport the management of retail companies inmake better decisions using historical data inavailable at the organization. Business users(decision makers) do not have the ability to accessdata easily when needed. In an effort to addressthese shortcomings, several departments withinretail companies find their own resources usingavailable data and hire a consultant to solve thedata individually of their short-term needs. Thedata handled by the organization's operationalneeds of online transaction processing (OLTP)systems are essential for the daily running of thebusiness. However, they are not very suitable tosustain demand for decision support or questionsbusiness managers typically need to address. Thequestion involves analysis including aggregation,search and slicing / dicing of data, which is bestsupported by analytical processing (OLAP)systems online. Data warehouse support OLAPapplications to store and maintain data in amultidimensional format. Data is extracted in awarehouse OLAP and OLTP taken from differentsources of data (including DB2, Oracle, SQLServer and flat files) using the Extract, Transferand Load (ETL) tools.Business activity has beenoperational data store long enough, and theycontinue to collect large amounts of data at a greathttp://www.ijcttjournal.orgPage 47

International Journal of Computer Trends and Technology (IJCTT) – Volume 56 Number 1- February 2018rate as the operational database becomes morevaluable, helping to grow the rate at whichbusinesses succeed. This paper shown that the datawarehouse collect, consolidate, organize, andsummarize this structured data so that this data canbe used to inform business decisions [4].Güzin, 2007, provide decision supportsystem that has a query that is taken from theexisting student information system and canproduce reports as output to assist in decisionmaking in the University AdministrationAtilimmaster programs. The method used by Güzin is thespiral model life cycle and Kimball. Kimballmethod uses a Dimensional Modelcan be usedagainst multiple fact tables so can get varies onuser needs and also the results of research GüzinTurkmen IALA retrieval history data quickly andsave time [5].Mohammed, 2014, used a structureddatabase for a trading company that has manybranches. The author provides a method of datawarehouse that can be implemented by companieswith high accuracy, this study applies two schemesnamely star schema and snowflake schema with theconcept of a multidimensional database. Where theauthor compares the two schemes and conclude starschema has a central fact (the fact table) that can bechanged while the center (table fact) on asnowflake schema cannot be changed [6].Leonard,2011,involvingtechnicaldescription of data warehouse, design, needs, andchallenges regarding the data cleansing andconversion of existing data, as well as otherchallenges associated with transactional database.This study also includes a discussion of databaserequirements and the technology used to create andrefresh the data warehouse. This study discusseshow data from other databases and data warehouseto be integrated. In addition, there is discussion ofspecific data marts in the data warehouse to meetspecial needs. The study also covers the topic ofhow the system architecture of data from otherdatabase and data warehouse from variousdepartments can integrate. As a prototypedeveloped Enterprise Data Warehouse databaseshows how different pair of experienced Extract,Transform and Load (ETL) process and loaded intothe set is actually a star schema and then makereporting easier [7].To support the decision-making style adata warehouse can accommodate the use of dataand decision-making. Connolly and Begg(2015)stated data warehouse is integrated view ofcorporate. Disparate data is drawn from theoperational data sources and a range of end-useraccess tools capable of supporting simple to highlycomplex queries to support decision making [1].System Development Life Cycle is used inthis study is Spiral Model which consistsofrequirement gathering, requirement analysis,requirement modelling, designand evaluation.The data warehouse design with four step ofKimball’s Dimensional Lifecyclewhich be capableof supporting the information requirements ofparticular group of users. According to Kimballthere are four steps process:Select BusinessProcess, Declare Grain, Choose DimensionsandIdentify Fact [9].Integrity Constraint is a concept that aims toprevent the entry of invalid data into the base tabledatabasewhich can define boundaries to reinforcethe business rules to associate the information onthe database [1]. According to Connolly and Begg,Integrity Constraint consists of NULL, traint.IV.DATA WAREHOUSEDESIGNA. Kimball’s MethodThe fourth section designed datawarehouse used a four steps process of Kimball’sMethod, with details of the process as follows:First step is select business processes. Ascanbe seen in Fig. 1, business processes start from theprocurement staff made an order to purchasematerial to the supplier. After ordering, supplierwill send material to the company that will receiveby the warehouse. In the warehouse the materialchecked by staff. If the material defects, it will berefund to supplier in return material process, whilematerials passed from checked will be sent forassembly into products. After the production thegoods delivered to warehouse before delivered tocustomers. If any goods are defective, it will returnin return goods process. Payment for materials andgoods by finance company.III.PROPOSED METHODAccording toSauter(2010), the formation, qualityinformation, and the amount ofchoiceandsuitabilityofmodeling.Thegooddecisionis obtaininformation that hhasthepurposeamong others: Completingthe sistancetodecisionnonstructuralandmanaging knowledgethatexist in the company [8].ISSN: 2231-2803http://www.ijcttjournal.orgPage 48

International Journal of Computer Trends and Technology (IJCTT) – Volume 56 Number 1- February 2018Fig 1:Business Process OfElectronicsManufacturingCompanyThe user needs related to the process thathas been described previouslydisplayed in themodel Entity Relationship in Fig. 2. Where darkentities representing the fact of the electronicsmanufacturing company.Second step is declare grain. The grain in anattribute that fill in the fact tables. Identifies thegrain for this study, namely:total material, sub totalmaterial, total return material, total pay material,total production, total material production, totalgoods, sub total goods, total return goods and totalpay goods.Fig 2 : Business Process Model Of Electronics Manufacturing CompanyThe third step is choose dimensions.Dimensions set context is needed later in the facttable. The dimensions identified namely:material,request material, supplier, goods, request goods,customer and production.The last step isidentify facts. Grain from thefact table determine the facts that can be used in thedimensional model. The list of the fact tablesarepurchase material table, returnsmaterial table,paymentmaterial table, production goods table,material production table, purchase goods table,returns itemstable and payment items table.B. Entity Relationship ModelEntity-Relationship model design for datawarehouse manufacturing company can be seen inFig. 3. The design of model Entity-Relationshipconsists of 7 entities that are interconnected.Entity Relationshipmodel design in figure 3consists of 7 entities: request material entity,material returns entity, payments material entity,request goods entity, returns goods entity,payments goods entity and production entity.C. Dimensional ModelDimensional models in the design of ElectronicManufacturing Data Warehouse use Star Schemabased Kimball Dimensional Lifecycle. Thestarschema can be seen in Fig. 4.ISSN: 2231-2803http://www.ijcttjournal.orgPage 49