Figure No 1: entity-relationship model of the database
Source: Own Elaboration
Design of an information system of indicators logistics
(*) González Pérez, Aliesky; (**) García Pérez, Michel
(*)Facultad de Ingeniería Industrial y Turismo
Universidad Central “Marta Abreu” de Las Villas
Santa Clara, Villa Clara. Cuba
aliesky@uclv.edu.cu
(**) Facultad de Ingeniería Industrial y Turismo
Universidad Central “Marta Abreu” de Las Villas
Santa Clara, Villa Clara. Cuba
michelgp@uclv.edu.cu
Reception Date: 11/25/2015 – Approval Date: 02/23/2016
ABSTRACT
Information has become a resource that provides competitive advantage to organizations; which is why it takes booming development and improvement of information systems for related potential exploitation of Information Technology and Communication (ICT) management. In this context, the Wholesale Distributor of Villa Clara, is immersed in improving its management based on the use of information systems so this research proposes an information system for management of logistics indicators that influence in raising the level of customer service. For this, a theoretical framework for the study is constructed; the entity under study is described, developing a system of indicators taking into account those used in the company and other proposed following the investigation and, finally, the proposed information system is exposed using the platform Pentaho Open Source Business Intelligence to show the performance of the main indicators that assess the level of customer service that the company provides the basis for improving the management of its supply chain.
KEYWORDS: Information systems, Logistics indicators, Customer service.
INTRODUCTION
Management information may constitute the basis for success in the performance of organizations, depending on the role they play is for that organization. So it is necessary to consider and analyze concepts, definitions, concerning communication, information and information systems that integrate, considering the system approach to organizational management.
In this regard it is stated that computer science has been able to infiltrate all spheres of life and business management and has been one of the areas where they are most used (Blanco Encinoso, 2011).
In the information age, one of the main problems is its excess, you need to invest much time in it because, among other things, the release of existing regulatory mechanisms on publications, mainly as a result of the emergence and development from Internet; for this reason, it is common to find a significant number of publications redundant and low quality mixed with other important and solid, hard to find between the noisy information that wraps. There are multiple definitions of the same concept, for example, there are a multitude of definitions, which together are quite disparate and in many cases, ambiguous. This is because, on the one hand, the approach that the concept (cultural, organizational or technological) is defined, and secondly, there are different approaches, depending on their specific real cases application (Garcia Robles, 2001) .
With the emergence of the theory of organization, the importance of information deepened. An organization is made up of people, material resources and information system. The latter determines the order and chaos (Currás, 1996) among individuals, resources and the interrelation resource persons. For this reason, organizations should be considered as information systems. The impact of economic, political, cultural, technological and others has originated a revolution in information management in organizations (Ponjuán dante, 1997) changes the rules, concepts, procedures, behavior is then transformed and as products and services; a new attitude allows the daily life of the projection and development of information activities; unquestionably the new management model has the indispensable basis of knowledge management (Suarez Zarabozo, 1997).
Currently software tools for the organization and integration of content and make it viable facilitate greatly the development of the bank develop. In this context, the Comercializadora ITH Wholesaler (for its acronym in English. International Trading House) Villa Clara, has great deficiency because the logistics indicators of customer service that has defined manually analyze which implies delays in information flow.
In the entity redundancy and duplication observed in control information by different logistic areas; delay in access to analysis and behavior of key indicators, which greatly limits good performance and behavior of the entity. Meanwhile the existing system for handling information is not adapted to include the development needs of the entity in terms of growth of entities to supply, while its design does not meet the key result areas and checking compliance of the fundamental objectives of the entity.
To reach the excellent services, the Distributor Wholesaler ITH Villa Clara new strategies is plotted as the analysis of logistics service indicators clientele are manually analyzed, this precludes the proper functioning of the entity in terms of its supply chain, which implies poor management of these indicators at the strategic level of the organization. Po which sets itself the task of research Designing an information system that allows for analysis of the behavior of logistics indicators customer service using web technology in managing the supply chain of the Distributor Wholesaler ITH Villa Clara.
The research solves not only the need for an information system for the control of different logistics indicators of customer service in the Comercializadora ITH Wholesaler Villa Clara to improve the management of the facility would materialize in their results.
DEVELOPMENT
1. Methodology for the development of an information system
There are several methodologies for the development of an information system among them we can name the following:
Camacho (2014) defines which are methods that indicate how to develop information systems more efficient. To do so usually structured in phases life of such systems in order to facilitate the planning, development and maintenance. The systems development methodologies should define: objectives, phases, tasks, responsible products and necessary for the successful completion of the process and its monitoring. The main objectives of a development methodology are:
To ensure the consistency and quality of both development and the system itself.
Meeting the needs of system users.
Achieve a higher level of performance and efficiency of the staff assigned to development.
Sticking to deadlines and costs incurred in planning.
Generate properly associated documentation systems.
To facilitate subsequent maintenance of the systems.
According to Castellanos (2009) structured is considered a classic, because the procedures of the methodology set out in stages, which should be followed under a sequential approach to analysis, design and development and is the most widely applied in the development of Software . Among its features are:
It is managed as project
Large volume of data and transactions
It covers several organizational areas of the company
Long development time
Requires that all steps are met, to meet the following (linear and sequential progression from one phase to the other)
Andrea and Martinez (2004) consider that the design of information systems is an activity based on the relationship between a designer, or group thereof, and a group of people that will be affected by the system; positively or negatively, depending on the case. During the development of this activity, it is common that even though this relationship has been adequate and has given rise to a correct definition of requirements, the designer gets to make decisions that, for one reason or another, are not consistent with that required by the affected group of people, resulting in the development of an information system that does not meet fully the requirements of the human group, and that in the worst case would be useless.
For Roman (2003) methodology, for detailed system model information in making management decisions design, you should include the following steps:
Identified strategic goals covering all areas according to the chosen frame work, the critical factors consistent with each of the goals identified success are identified. For example, if the strategic goal is patient-centered care, an associate FCE can be to achieve patient satisfaction, which requires a qualitative definition.
In the company under study key areas that influence the customer pays the same analyzing their main characteristics and indicators that have implemented the service right now they are identified. For this, the commercial area of the company is analyzed as this is responsible for carrying out transactions with the client; also the area of transportation and distribution which is responsible for complying with the order delivery is analyzed according to the time and place established, to achieve full customer satisfaction and finally the storage area being the area that deals with conservation in good condition of all stored products, so that the requested orders arrive fully fit for consumption by customers.
2. The pentaho platform for information management
There are several platforms or libraries of information management that help us work when graphed some information for displaying this. Among these tools we have: Pentaho, jQuery and Rgraph among the best known, of which we will address below according to the above order.
Pentaho is a Business Intelligence tool developed under the philosophy of free software for management and business decision making. It is a composite of different programs that meet the requirements of BI platform. Offering solutions for the management and analysis of information, including multidimensional OLAP analysis, reporting, data mining and creating dashboards for the user. The platform has been developed under the Java programming language and has an implementation environment also based on Java, thus making it a very flexible Pentaho covering a high-end business needs (Bustillo, 2013) solution.
Pentaho defined itself as a BI platform solution-oriented and focused on processes that includes all major components required to implement process-based solutions and has been designed from the outset to be based on processes. Pentaho Solutions that aims to offer consist primarily of infrastructure analysis tools and integrated workflow engine business process reports. The platform will be able to run the business rules necessary, expressed in the form of processes and activities and to present and deliver the right information at the right time.
The platform Pentaho Open Source Business Intelligence covers very broad needs of Data Analysis and business reports. Pentaho solutions are written in Java and have a deployment environment based on Java also. That makes Pentaho is a very flexible solution to meet a wide range of business needs - both typical and sophisticated and specific to the business.
3. Information system design
3.1 Characterization of the information system of the company
Wholesaler Comercializadora ITH in Villa Clara, the SI is present in the area that has to do with the logistics indicators in customer service is not automated, making it difficult to support decision-making of the company. The existing IF inputs reflected: defining the general data of customers and suppliers, general product data and invoices; processes: for the calculation of these indicators, the commercial staff performed manually; in the storage area: catalog customers and suppliers invoices issued and received; and outputs: orders received and orders urgent.
When the SI of the company is automated on the part of the logistics indicators in customer service, this brings about a considerable improvement in supporting decision making of the company, savings in internal costs the same as a consequence labor. Moreover, achieving competitive advantage through its implementation and use.
In the company there are different systems to achieve the desired performance of the entity for the different areas, among which are the following:
The Payroll system is a system responsible for incident reports, capturing incidents, performs the processing of prenómina, calculation and reporting. It is also responsible for payment and foreign exchange result.
Accounting System (Acountmate) is responsible for keeping records of the company.
Inventory System (Sistinve), billing, receipt and reports is done, it is also responsible for bringing the predespachos and prefectures.
ZunAft system, ZunUtiles is an administrative system that is responsible for linking these systems to get the most significant of these systems data.
Financial Accounting system (Zunacc), it is analyzing accounting regarding accounts receivable and accounts payable.
Consolidated Inventory (OSCE), is responsible for analyzing inventory reports.
Encoder system (Coditerr), carries the codes of the products, which are exported to the (Sistinve) system and analyzes the reports.
Mistral Haulage is a system for the area of transportation, which is responsible for parameterization, making letter bearing and waybills, invoices performs management and accounting of transport.
Maresol is a system that is responsible for the preparation and submission of applications, controls authorized signatures and maintenance.
Soe is an administrative system that links all these systems.
Commercial margin, is a system that works in Microsoft Excel by computer company in order to analyze the reports issued.
Of these existing computer systems not all provide information to the information system for the analysis of logistics customer service indicators, only they contribute: the Sistinve, Ecoe, Coditerr, Mistral freight and the Maresol.
The information is handled by computers that serve to archive information and process it with different types of software, which generate a series of data are analyzed in the monthly meetings of the company, revealing the direction that is taking the company in the same all responsible for the areas set out their concerns and suggestions for better functioning of the entity. Each area of the body has a biweekly meeting where the situation is updated in the areas, on the other hand, the commercial director and the chief of the stores have a monthly meeting to place orders for the missing products in the company or products requested by customers who are not stored in the body.
3.2 Principles and tools of SI
The system is intended for operational planning, with accurate, reliable and easy to obtain, mainly with internal information in correspondence with the variables of logistics indicators proposed above. Suitability for decision-making from the outlets is based on the training, experience and information held by the management.
The system aims to: facilitate decision-making and make it more effective from information on the actual behavior of the different logistics indicators to increase administrative productivity.
In the design of SI for the Comercializadora ITH Wholesaler of Villa Clara a set of general principles which must be supported is the same, such as discussed:
Systemic Character: The system should encourage interaction in the analysis of different indicators giving a comprehensive view of the behavior of the organization.
Flexibility: Ability to assume changes and redesigns in correspondence with the feedback and the changing needs of the organization.
Organizational Adaptation: Given the possibility of application in the organization under study.
Continuous improvement: From the feedback should promote the possibility of improvement and adaptation to changing environmental conditions.
Interactive Character: The computer tool to promote interaction with users of the indicators being really useful for decision-making.
Implication: The system must promote systematic participation in the authentications due.
SI for the development of various tools such as Pentaho, MySQL and Microsoft Access were used. The system is made on the platform Pentaho Open Source Business Intelligence, which covers very broad needs of data analysis and business reports. Pentaho solutions are written in Java and have a deployment environment based on Java also. That makes Pentaho is a very flexible solution to meet a wide range of business needs, both typical and sophisticated and specific to the business. Make designs flexible reporting, it is easy to understand and does not consume many resources.
It is also used in information processing for making cubes MySQL, being a management system relational database, is an open source system, has great portability between systems, ie, you can work on different platforms and operating systems. It can support many types of data, you have a flexible system password and user management, with a very good level of data security. Low cost requirements for the development of database, since due to its low power consumption can be run on a machine with limited resources without any problem.
For the preparation of the database that supports the primary information used Microsoft Access is a data manager that collects information on a particular subject or purpose, this is proper office manager package in any of its versions. It is an easy to use tool that allows you to quickly create database applications based on the browser that will help take the company. Data is automatically stored in a SQL database, so they are better protected.
3.3 Design database
In the Distributor Wholesaler ITH Villa Clara, to know the behavior of logistics indicators in customer service creating a database (performed in Microsoft Access), in which the existing data will be displayed in the company referred it became necessary to: those in food stores products, customer orders for these products and dispatches them.
Product name, the stored quantity, weight in kg, the unit cost of storage and the level of stocks of these products is known. Orders the application date, the date of delivery, the delivery, the amount of claimed products, the quantity of products urgent shipments, the quantity of product requested by the customer and the quantity of products that were delivered known no damage. Besides the office it is known about the quantity dispensed and the number of offices that is just the time to store products according to the amount shipped are issued. Then the entity relationship model (Figure 1) and the relational model is presented:
Products (prod_id, name, Cant_almacenada, Weight (kg) Unit Cost Storage, stock levels)
Orders (Id_ped, Fecha_solicitud, Fecha_entrega, Plazo_entrega, Cant_reclamada, Cant_urgente, Cant_solicitada, Cant_entregados_bien)
Offices (prod_id, Id_ped, Cant_despachada, No_despacho)
Figure No 1: entity-relationship model of the database
Source: Own Elaboration
After the establishment of the database must be transformed to propitiate the same information as predesigned in order that the SI run faster facilitating data for decision making are in time.
3.4 Star Model SI
Kimball methodology, called Dimensional Model English Dimensional Modeling is based on what is called Dimensional Life Cycle Business English Business Dimensional Lifecycle. This methodology is considered one of the favorite techniques when building a Data Warehouse. In the Dimensional Model models of tables and relationships in order to optimize decision-making, based on queries made in a relational database that are linked to the measurement or set of measurements of the results of the processes they are of business.
The Dimensional Model is a logical design technique that aims to present data within a framework of standard and intuitive work, to allow access to high performance. Each Dimensional Model consists of a table with a combination wrench, called fact table, and a set of smaller tables called dimension tables. The elements of these tables can be defined as follows:
The problem query to solve is: Show the behavior of logistics indicators in customer service in May 2014 in the Comercializadora ITH Wholesaler Villa Clara.
According to the method of Kimball defined as follows:
Being as shown in Figure 2 star pattern that governs the operation of the information system is proposed.
Figure No 2: Model SI star
Source: Own Elaboration
3.5 Extraction, cleaning and data transformation using the tool Pentaho Kettle
The ETL process involves an analysis of operational data of any entity, which performs an extraction of them where it performs the extraction process, filter, transform, integrate, classify, aggregate and summarize operational data for subsequent storage.
Data cleansing is responsible for filling missing values, identify values outside limit and eliminate noise data. Also it corrects inconsistencies in the data and performs an integration of the same.
The transformation of the data includes semantic reconcile inequalities, may include multi-table joins data, sorting and summarization of information and concludes with the definition of a relational view of the tables in the data sources.
For the above-defined dimensions in the flagship model should turn the tables in the database in this case and ordering the Product table as shown in Figure 3.
Figure No 3: Transformation of the tables in the database
Source: Own Elaboration
First it is extracted from the database in the product Access table. Is selected from the table values (prod_id, name and cant_almacenada) that according to the terms of calculation of indicators of the fact table are the fields that are needed and defined output connection to the data warehouse to define the Product dimension. As above it is extracted from the Access database, in this case, the order table. It is selected from the table order values (Id_Ped, Fecha_entrega, Plazo_entrega, Cant_solicitada, Cant_reclamada, Cant_urgente, Cant_entregados_bien) and output connection is defined in the data warehouse to define the dimension Order.
For the dimension table date the necessary transformations were performed, as shown in Figure 4.
Figure No 4: Transformation for the Dimension Date
Source: Own Elaboration
In this case it is taken from the Internet, where 110000 is generated and determines dates year, month, day, semester and day of the week. Defined in Table Output connection to the database to define the Time dimension.
For the fact table the necessary transformations were performed, as shown in Figure 5.
Figure No 5: Table transformation to obtain Facts
Source: Own Elaboration
To make the fact table the following steps are followed:
• It is extracted from the database in Microsoft Access on the table Office data (prod_id, Code, Cant_despachada)
• The data is organized by Id_Ped
• It is extracted from the database in Microsoft Access made the Order table data (Code Fecha_entrega, Plazo_entrega, Cant_solicitada, Cant_reclamada)
• The data is organized by Code
• a union by key is done with the Code field
• The data is organized by prod_id
• It is extracted from the database in Microsoft Access performed Table Product data (prod_id, cant_almacenada, Costo_unitario_almacenamiento, Nivel_de_Stocks, Weight (kg))
• The data is organized by prod_id
• a union by key is done with the field prod_id
• The data is organized by prod_id
• The data is extracted from the product dimension
• The data is organized by prod_id
• a union by key is done with the field prod_id
• The data is organized by Code
• I was removed from the order dimension data
• The data is organized by Code
• a union by key is done with the Code field
• I was removed from the order dimension data
• The data is organized by Fecha_Entrega
• dimension is extracted date data
• The data is organized by Date
• a union by key is performed with Fecha_Entrega field and Date
• Cant_despachada values, No_Desp, Fecha_entrega, Plazo_entrega, Cant_solicitada, Cant_entregados_bien, Cant_reclamada, Cant_urgente, Cant_almacenada fields is selected
• values of product quality indicators, missing inventory, time delivery, unplanned Shipping and rotation is estimated stocks also Precalculus Precalculus 1 and 2
• values Product Quality fields, missing inventory, time delivery, unplanned Shipping and rotation is selected stocks
• connection to the database is defined in the output table to define the fact table Indicators.
With the data warehouse made must then prepare the data cube facilitating connection to the database to complete publishing the information to be displayed, in this case, the behavior during the month of May the different logistics indicators of the company.
3.6 Realization Schema Workbench as a tool of Pentaho
First for this scheme is necessary to make a connection to the database. In addition, it places the jdbc driver in the drivers directory located at the root of the installation of the Schema Workbench.
Once the connection to the database we proceed to the creation of the data cube, initiating the process with the creation of a new scheme. a cube containing all the data from the fact table, followed by a table that correspond to the fact table to be displayed is added is added. Then existing in the star pattern dimensions are inserted, then the hierarchy of the dimensions are added, levels that is just the elements needed and relevant tables are created. Later measurements of the fact table that would be the analysis values, in this case would be the logistics indicators in the most significant service to the customer entity are made, this is evidenced in Figure 6.
Figure No 6: Data cube
Source: Own Elaboration
Then the output that gives us the program used, putting in evidence the accuracy and ease of understanding of them is presented. Appreciating in Figure 7 the results of the indicators analyzed in the entity under study, where the values of the indicators are displayed by date, by product and by orders of these.
Figure No 7: Results of the indicators analyzed
Source: Own Elaboration
To define the behavior of the indicators were asked the experts to give their assessment on the matter based on the best practices of corporate performance, such evaluations are summarized in Table 1.
Table No 1: Behavior of logistics customer service indicators
Source: Own Elaboration
Generally the company has an acceptable behavior indicators, as product quality was of 95.73%; the time deliveries were at most 2 days earlier; unplanned shipments behaves quite low as only a 16.83% is reached, this shows an area that the company should strengthen such behavior is particularly well embodied by the little availability of transport equipment; the missing inventory behaves negative (-66.68%), demonstrating the storage capacity of the company to always have the products necessary to meet their clients; and rotation of stocks was a 48.39% that has great relationship with the previous indicator as most products in the analyzed period did not circulate completely in the store.
The main weaknesses were that orders Refreshment tukola (Lata) were below the target in the quality of the product reaching a 78.0% indicator, there was a request Refresher Coracan suffering a delay of a day, there was missing inventory Helmets product grapefruit syrup daring 10.0 percentage units. Moreover, only Refreshment tukola (Lata) had a rotation completeness or store more than 100% (142.5%) and in the case of shipments unplanned only presented in 4 products (biscuits soda La Paloma, Juice TROPICAL ISLAND Mango, guava jam the cockerel and Refreshment Amaro) being in all cases, over 30% (80%, 40%, 50% and 66.66% respectively).
CONCLUSION
The design of business information systems is conditioned by the demands of the organization under study, the platform used depends on the features expected of SI, in this case the platform Open Source Pentaho Business Intelligence is used because it has a large data analysis and report good results.
The proposed information system enables the analysis of indicators showing the performance of all or any specific, depending on the granularity user-by dates, products or orders established; also facilitating the graphical display of the same.
Selected for analysis in the company under study had a behavior indicators as the product of 95.73% with a product below 90 was Refreshment tukola (Lata) (78.0%); time deliveries were made with a maximum variation of having an order of Refreshment CORACAN a day late; there were only four products had not planned shipments; there was only missing inventory in the product Cascos grapefruit syrup The Daring; and stock rotation was a 48.39%.
BIBLIOGRAPHICAL ABSTRACT
Please refer to articles Spanish Bibliographical Abstract
REFERENCES
Please refer to articles in Spanish References