A Critique of Data warehousing in Enterprise Resource Planning Systems

A Critique of Data warehousing in Enterprise Resource Planning Systems INTRODUCTION General Background There are different ways in which companies have collected and accessed the data in order to support and enhance the business. Since 1990s, with the emergence of the concept of business data warehouse, companies have been instituting data warehousing for data mining, data analysis, reporting and other business intelligence purpose (Matthias et al., 2003). Bill Inmon in 1990, defined data warehousing as à ¢Ã¢â€š ¬Ã…“a subject-oriented, integrated, non-volatile, and time-variant collection of data in support of managements decisions. He also stated that the à ¢Ã¢â€š ¬Ã…“data warehouse contains a very useful source of data for the explorer and data miner. The data found in the data warehouse is cleansed, integrated, organized. And the data is historicalà ¢Ã¢â€š ¬Ã‚  (Inmon W. H, 2002). Data warehouse is also defined as the à ¢Ã¢â€š ¬Ã…“architecture used to maintain critical historical data that has been extracted from operational data storage and transformed into formats acce ssible to the organizations analytical communityà ¢Ã¢â€š ¬Ã‚  (Anne Marie, 2009). In the same decade, with the success of Material Requirements Planning 2 (MRP II) and its evolution to Enterprise resource planning (ERP), various companies implemented ERP software as à ¢Ã¢â€š ¬Ã…“integrated suitesà ¢Ã¢â€š ¬Ã‚  that automate core corporate activities and helps the corporate managers to coordinate the common functions of an enterprise (Gibson et al., 1999). ERP can be defined as à ¢Ã¢â€š ¬Ã…“techniques and concepts for integrated management of business as a whole from the viewpoint of the effective use of management resources to improve the efficiency of enterprise management. ERP packages are integrated (covering all business functions) software packages that support these ERP conceptsà ¢Ã¢â€š ¬Ã‚  (Alexis Leon, 2008). For every critical business decision taken, information is the foundation. To facilitate this, all functional areas of the organization are integrated using ERP (Chou, 2005). Most ERP vendors have an integrated business suite containing busi ness intelligence (BI) tools to access their data modules directly. However, data warehousing in ERP system is a complicated task that requires the use of various types of inputs like the historical data, and the information that are external to the ERP system (Peng and Nunes, 2008; Chaudhuri et al., 1997). Although ERP systems can integrate all business transaction data into their master databases for organizational planning, it may not be a solution for data analysis and decision support process. Selection of ERP, implementation and integration with BI is the costly and risky processes in the companys life span (Baki et al, 2005). This paper reviews the value of data warehousing in ERP systems. It identifies the power and the capabilities ERP and Data Warehousing. And, reviews the claims made by ERP vendors about their integrated BI solution. The conclusion is provided in the last section. Research Objectives The paper presents the study of features and claims by ERP vendors on its ERPs efficiency of the data warehousing in ERP system. This study attempts to critically review and question the claims by ERP vendors on their efficiency of Data warehousing in ERP systems. Research objective is also to identify those issues that occur in Data warehousing in ERP systems, and then map them in the research framework, perhaps with more detail related to the dimensions that are found. The issues are defined with the viewpoints of vendors and consultants. This paper will provide an overview of the issues and challenges that the intersection of these two IS concepts are creating. Research Design An overview of the importance of the information technology sector and a synopsis on enterprise resource planning systems are presented first, followed by a discussion on the research problem and the academic and practical motivations for undertaking the present study. The study is a review of literature, and claims made by prominent ERP vendors on the data warehousing in ERP system. Critical Literature Review The research design of this study consists of theoretical risk ontology through a critical literature review. A critical literature review was conducted by first searching for the appropriate literature. Initial phase of the literature research attempted to search and retrieve the secondary literature sources like journals, books and newspapers that are directly related to data warehousing in ERP, and data mining. In this process it was identified that current research studies on data warehousing in ERP system focus mainly on ERP selection, implementation, integration with data warehouse, and business intelligence (Chou et al, 2005; Shehab et al, 2004; Davenport, 1998; Themistocleous et al, 2006). The process involved a search of prominent Publisher of journals in information services like ACM Portal, Emerald, Wiley Interscience and Web search engine Google Scholar and IEE Explore. Journals and databases were searched by generating key words and search terms with initial reading and brainstorming. I decided to focus my study on articles that discuss the ERP and particularly the integration with BI. This paper presents the critical literature review about the data warehousing in ERP systems. ERP SYSTEMS Definition of ERP ERP system is a software package that integrates the flow information through the company, including financial, accounting, human resources, supply chain, and customer information. Yen et al (2002) defined ERP system as à ¢Ã¢â€š ¬Ã…“a business management system that integrates all facets of the business, including planning, marketing and manufacturingà ¢Ã¢â€š ¬Ã‚ . An integrated ERP system can cover wide range of functionalities like reporting, planning, budgeting, forecasting, strategy management, scorecards, and risk management (SAP, 2009) and integrate them into one unified database. It automates core corporate activities by incorporating best practices to facilitate rapid decision making, cost reduction, and greater managerial control (Holland et al, 1999). For example, functional modules such as manufacturing, warehouse management, human resources, finance, customer relations management, supply chain management were all once stand alone software applications, typically having its own database and network (tech-faq, 2009). Best practices are incorporated as a result of the long development history of the ERPs. ERP market is led by companies like SAP AG, Oracle Corporation, Sage Group, Microsoft Corporation and Infor Global Solutions (Wikipedia, 2009). Importance of ERP An important reason for implementing ERP is that, it can help companies re-engineer their business process and compete in the market. Davenport (1998) says that à ¢Ã¢â€š ¬Ã…“for managers who have struggled, at great expense and with great frustration, with incompatible information systems and inconsistent operating practices, the promise of an off-the-shelf solution to the problem of business integration is enticingà ¢Ã¢â€š ¬Ã‚ . Following are the benefits of ERP systems over the distributed stand alone departmental systems (Yen et al, 2002): * Business process automation a unified enterprise view of the business that encompasses all functions and departments. Improvement in the supply chain via the use of e-communication and E-commerce. * Timely access to management information an enterprise database where all business transactions are entered, recorded, processed, monitored and reported There are many reasons why organizations find ERP system very attractive. The primary reasons focus on the frustrations in using the existing stand alone systems. Convincing reasons for a purchasing ERP system may include (Chen, 2001): * Efficiency of the current system Inability of the existing stand alone systems to support organizational needs * Failure in the distributed system The use of multiple points of input using multiple application which leads in duplicated effort of capturing and storing the data in existing system * Maintenance overhead in the current system The requirement of extensive resources (man and machine) for maintenance and support of the system. * Competition Competition in the global market and the desire to reengineer its business process * Company growth The growth of the enterprise and subsequent incompatibility of several legacy information system * E-commerce Inability of employees to respond easily to questions or information requested by key customer or suppliers ERP systems provide a common platform and business practices across the enterprise that allows the real-time access. According to Davenport (1998), ERP solutions are designed to solve the fragmentation of information in large business organisations, and integrate all the information flowing within a company. ERP failures ERP system implementation can either reap huge benefits for successful companies or it can be disastrous for organizations that fail to manage the implementation process (Holland et al, 1999). The selection and acquisition of ERP software is a risky and challenging task. And a wrong purchase may adversely affect the organization. Themistocleous states many reason for the failure of ERP system. For example, * Resistance from the employs against the change in the system * Differences between organisations and consultants as a result of cost overruns and projects delays. * Non-flexibility in ERP software forces organisation to abandon their way of doing business * Conflict with the business strategy of the organization Selecting ERP and implementation In-house software system development is generally expensive, time consuming and often covered by uncertainties and integration of various incompatible software systems may not function well with each other. If different software packages are being used, data may not be consistent. On the other hand purchasing off-the-shelf ERP software packages can solve problem. Holland et al (1999) says that à ¢Ã¢â€š ¬Ã…“the companies are radically changing their information technology strategies by purchasing pre-packaged software instead of developing IT systems in-houseà ¢Ã¢â€š ¬Ã‚ . There are different strategic approaches to ERP software implementation. It can be implemented with either a minimum deviation from the standard settings that the ERP vendor provides or with the customization of a system to suit local requirements (tech-faq, 2009). As discussed by Yusuf et al (2004) in the case study about the implementation of ERP in Rolls-Royce in partnership with Electronic Data Services (EDS), ERP implementation is a complicated task. The project implementation problems faced while implementing are * Cultural Problems Some of the functions and processes of the new system did not receive full appreciation from the employee. So, the implementation team had to resolve this by illustrating the improvements made to the company as a whole. Also extensive trainings were provided to the employees of Rolls-Royce. * Business Problems Because of the rigidity in the business structure of SAP R/3 ERP, employees of Rolls-Royce adjusted their working practices in order to fit SAP. * Technical Problems As the system required the retrieval of old data from legacy system which were in de-normalized form, Rolls-Royce had to run legacy system in parallel with the ERP until the expensive process of extracting the old data from legacy system was normalized, screened and stored in a sensible data format in the new database. Implementation of ERP and planning of the resources required to run the enterprise is not the end of the road for ERP. Organization will realize the full potential of ERP when it is used and properly managed (Yusuf et al, 2004). One of the main difficulties experienced by ERP implementations have been the costly development of additional software to summarize and retrieve the information for generating the reports (Themistocleous et al, 2001). A company that plans to invest into ERP needs to have a good strategy and a clear idea about the cost of ERP system. Implementation slowdowns the routine works within an organization. Customization is costly and time consuming (Yen et al, 2002). As outlined by Peng and Nunes (2009), reasons like insufficient user training, loss of in-house IT experts, bankruptcy of system vendor and barriers like inefficient communication between functional divisions can cause ERP post implementation failures Analytical and forecasting functions of ERP: Business managers will have different information needs for planning and decision making (Peng and Nunes, 2009). Decision support system can reduce the time, cost and improve efficiencies. Analytical and forecasting functions are the skills, processes used to support decision making and forecasting. Analytical and forecasting features of ERP can be accessed by managers using an interface such as web-based or graphical interface via the internet or intranet (Marnewick, 2005). If an organization does not take advantage of decision support systems, it cannot take complete advantage of the data and may lose its competitive edge. Most ERP systems today have highly integrated databases and business intelligence (BI) tools to access their data modules directly (Chou, 2005). ERP vendors, data warehousers, and third-party tool vendors have numerous products and solutions for using the ERP data. There are 3 major solutions for ERP data (searchSAP, 2009): 1. Solutions from third-party vendors that analyze data within ERP systems 2. ERP-based solutions that analyze data within ERP systems 3. ERP-based solutions that build data warehouses outside their ERP systems An ERP-based data warehouse is a classical, external data warehouse or data mart built with tools offered by an ERP vendor (Russom, 2007). ERP reports are generated using the existing ERP schema as the foundation for building the standard reports. Integrated business intelligence system pulls the data from ERP systems to a data warehouse and enables to perform data analysis and deliver superior reporting for making timely and accurate decision (Chou et al, 2005). Closer integration of corporate wide data warehousing data with ERP data potentially enhances companies return on their ERP and data warehouse investments (Wiley, 2009). ERP contains a set of analytical tools to facilitate sales planning. Yen et al (2002) says that à ¢Ã¢â€š ¬Ã…“many companies deploy data warehouses for facilitating the data analysis in ERP. They will buy packaged analytic applications that include a data warehouse, analytical tools, and predefined data models to accelerate the data analysis in ERPà ¢Ã¢â€š ¬Ã‚ . But, in spite of deploying ERP and an integrated data warehousing and BI, there is no guarantee that the forecast generated is up to the accuracy. As discussed by Peng and Nunes, one of the reasons for inaccurate forecasting is due to inherent difficulties in predicting the fluid market. This results in significant impact in companies. ERP systems are usually designed to record business transactions data, make changes to existing data, reconcile data, keep track of business transactions, run predefined business reports, and manage business transactions. In contrast, analytical systems are designed to examine large volumes of data and then to generate essential information for decision-making. There are five major software vendors offering ERP solutions to business worldwide. According to reports from Gartner Dataquest, quoted by destinationcrm (destinationcrm, 2006) SAP is the market share leader in ERP, followed by Oracle, Sage, Microsoft Dynamics and SSA Global Technologies. DATA WAREHOUSING AND DATA MINING Data Warehousing Bill Inmon (2002) says that the à ¢Ã¢â€š ¬Ã…“data warehouse contains a very useful source of data for the explorer and data miner. The data found in the data warehouse is cleansed, integrated, organized. And the data is historicalà ¢Ã¢â€š ¬Ã‚ . To help managers and decision makers retrieve information they need from tremendous amount of data reside in database, many enterprises have built system environments focusing on data warehousing technology, deployed that as an integral part of a decision support systems (DSS). Data warehouse is responsible for providing information needed for supporting executive decision making. As a result, data warehousing technology has been integrated into ERP systems (Zhang et al, 2006). Yusuf et al (2004) defines Data warehouse as à ¢Ã¢â€š ¬Ã…“an integrated collection of data. The data is stored centrally and is extracted from operational, historical and external databasesà ¢Ã¢â€š ¬Ã‚ . Data warehouses are used for decision support. Historical, summarized and consolidated data is more important than detailed, individual records. Data Mining Data mining is the study and extraction of patterns from a large set of data. It can be defined as the process of analyzing data from different viewpoints and summarizing it into useful information for planning and increase revenue. It allows users to analyze data from many different dimensions or angles, categorize it, and summarize the relationships identified (Anderson, 2009). Data mining can also be defined as the à ¢Ã¢â€š ¬Ã…“practice of automatically searching large stores of data to discover patterns and trends that go beyond simple analysisà ¢Ã¢â€š ¬Ã‚  (Oracle, 2009). Data mining uses sophisticated mathematical algorithms to slice the data and evaluate the probability of future events. The key properties of data mining are (thearling, 2009): 1. Automatic discovery of patterns 2. Prediction of likely outcomes 3. Creation of actionable information Data Mining is widely used in applications such as product analysis, demand and supply analysis, understanding consumer research marketing, investment trend in stocks real estates, telecommunications, e-commerce and so on (Chou et al, 2005). However, a database which is new and which has only a current piece of information is not suitable for data mining as it can never detect trends and long term patterns of behaviour. Historical data is very essential for data mining as historical data contains valuable chunk of information hidden in it. Mature data is crucial for understanding the seasonality of business and the larger cycles of business to which every corporation is subject (Inmon, 1996). Data mining uses data from data source in order to provide users with meaningful indicators. Data from ERP systems is used as data source. Modern ERP systems provide advanced BI tools out of the box, avoiding the hassle of connecting a stand-alone BI system, and lowering the cost which is a critical capability to consider ERP for midsize companies, with limited staff and resources to maintain multiple systems (Newcomer, 2009). After implementing the ERP system in organizations, they tend to concentrate more on the return on investment (ROI). Chou et al (2005) says that à ¢Ã¢â€š ¬Ã…“In order to justify their return-on-investment (ROI), more and more organizations are turning to BI tools that make data collected by ERP, customer relationship management (CRM), and other data-intensive applications meaningfulà ¢Ã¢â€š ¬Ã‚ . Since a BI system includes technologies for reporting, analysis, and sharing information, many ERP vendors have integrated these solutions with ERP systems to truly maximize the ROI of ERP. The integration of BI and ERP systems can strengthen corporate decision-making capability through utilizing the analytical capability of BI system and data managerial capability of ERP system (Chou et al, 2005). Business Intelligence (BI) can help in competition analysis, market research, economical trends, consume behaviour, industry research, and geographical information analysis and so on. Business Intelligence using data mining helps in decision-making (Naxton, 2006). ERP VENDOR CLAIMS Modern ERP systems may provide advanced BI tools, avoiding the hassle of connecting a stand-alone BI system, and lowering the cost. Integrated business intelligence contains a broad category of analytical applications that help companies in making decision based on the data in their ERP systems (Moller, 2005). Oracle and SAP are currently the only major ERP vendors with such offerings. Analytical applications can be broadly classified as follows: Financial Analytics Financial analysis refers to an assessment of the viability, stability and profitability of a business, sub-business or project (Wikipedia, 2009). It is concerned with optimising the profitability of the business. When used effectively it can provide a competitive differentiator. Financial analytics helps the business focus on the most important customers and the most profitable products and services (Brook, 2009). It helps them to (Schroeck, 2001): * Understand the overall performance of the organization * Identify ways to measure and maximize the value of intangible assets (eg. Services) * Effectively manage enterprise-wide investments and reduce operating costs * Forecast variations in the marketplace, * Optimize the capabilities of information systems, and * Business processes improvement. Integrated analytics allow organizations with an ERP infrastructure to facilitate reporting and tools required for decision-makers. Oracle E-Business Suit (EBS) is one suite of applications that contains ERP and integrated BI. Oracle says that à ¢Ã¢â€š ¬Ã…“Oracle Financial Analytics helps front-line managers improve financial performance with complete, up-to-the-minute information on their departments expenses and revenue contributionsà ¢Ã¢â€š ¬Ã‚ . SAP Business Suite is a range of software modules with an integrated Business Intelligence. SAP states that à ¢Ã¢â€š ¬Ã…“SAP ERP provides powerful analytic software that enables powerful financial analysis to help you analyze your business, develop business plans and budgets, and track performance during execution.à ¢Ã¢â€š ¬Ã‚  (SAP AG, 2009). Few of the features and functions that support financial analytics as stated by SAP are * Financial and management reporting Providing a set of tools to meet the financial and management reporting needs. * Planning, budgeting, and forecasting Support traditional budgeting, rolling forecasts, and collaborative planning, such as cost center planning. * Working capital and cash flow management Optimize cash flow, including cash flow calculations and middle- and long-term planning. Sales Analytics Sales analytics is a procedure involving the gathering, classifying, comparing, and studying of company sales data. It may simply involve the comparison of total company sales in two different time periods. Or it may entail subjecting thousands of component sales (or sales-related) s to a variety of comparisons, like comparison with s for earlier periods of time (Wikipedia, 2009). SAP says that the SAP sales analytic help the organization to obtain the data necessary to proactively address trends and measure success and revenue shortfalls. Oracle states that analytics solutions provided by its E-business suite dramatically improve the effectiveness of sales people by providing real-time, actionable insight into every sales opportunity at the point of customer contact. With more accurate sales forecasts and enhanced identification of potential problems and opportunities, Oracle Sales Analytics helps close business faster and increase overall sales revenue. It lists the following benefits: * Resource allocation Identifying critical opportunities so that executives can assign the appropriate resources to increase the chance of winning * Sales forecasts Analyzing pipeline opportunities to determine actions required to meet sales targets. Provide the information about sales documents, such as opportunities, sales orders and sales contracts. Thus, help in future revenue forecasting. Integrated sales planning and analysis enables sales managers to understand the financial status and overall effectiveness of the sales organization quickly and easily. These scenarios help users obtain the data necessary to proactively address trends, measure customer retention and revenue shortfalls, and assess future opportunities (SAP, 2009). Operational Analytics Operational analytics is a process that facilitates delivery of the in-depth and focused analysis of the performance of each key operational area of the business. Operational Analytics try to provide comprehensive and focused analysis of every aspect of the operational area of a company (Information Management, 2007). Oracle says that à ¢Ã¢â€š ¬Ã…“Oracles Business Intelligence Suite delivers real-time operational analytics that enable you to make better business decisions fasterà ¢Ã¢â€š ¬Ã‚ . Operational analytics is also a part of SAP business suite. SAP says that à ¢Ã¢â€š ¬Ã…“SAP ERP provides features and functions for operational analysis to help you optimize the entire supply chain, improve revenues, and increase customer satisfactionà ¢Ã¢â€š ¬Ã‚ . Few of the features and functions that support financial analytics as stated by SAP are: * Manufacturing reporting Provides various standard reports and analyses detailing production-related information. * Customer service analysis Used for monitoring financial trends, costs, and revenues per customer, as well as service contracts and operations. * Sales planning Used for opportunity planning and analysis and partner planning. * Sales analysis Provides an accurate overview of current sales performance and an overview of sales force effectiveness. Workforce Analytics Workforce Analytics is a powerful decision-making platform using business intelligence tools that offer to the management at every level the right and timely information at point of decision making process for a better visibility and accountability in regards to workforce-related issues (Information Management, 2007). Workforce Analytics is used by HR professionals, and line managers. It provides an analysis option that gives real-time insight into your workforce. They can identify trends at an early stage and make well-informed decisions, enabling you to manage your human capital more effectively, predict human-capital investment demands, and track workforce costs and the ROI associated with HR projects (Wikipedia, 2009). The focus is to analyse current and historical employee data to identify key relationships among variables and use this to provide insight into the workforce they need for the future. Oracle says that Oracle workforce analytics in the e-business suite à ¢Ã¢â€š ¬Ã…“provides the strategy management and performance tracking needed to measure the effectiveness of HR initiatives. It helps to evaluate and communicate company performance, staffing, turnover, HR readiness, compensation, and competencies.à ¢Ã¢â€š ¬Ã‚  Managers need information that will help guide your strategic decisions. Implementing an Enterprise Resource System (ERP) that integrates all the information and processes into one coherent environment is a first and major step towards improved decision-making. But capturing and processing data is not sufficient to give the insight into the business that decision makers need today. Only when coupled with a business intelligence system can your ERP software enable users analyse and act on that data quickly and effectively. IT industrial leader, Microsoft quotes that à ¢Ã¢â€š ¬Ã…“Forecaster for Microsoft Dynamics ERP helps you manage financial performance through accurate budgeting and planningà ¢Ã¢â€š ¬Ã‚  (Microsoft, 2009). A CRITIQUE OF VENDOR CLAIMS Data Warehousing In todays ever-competitive business climate, the ability to understand business conditions and gain timely insight into business performance is essential for survival. Business users have long faced the challenge of being unable to easily analyze business data in their enterprise resource planning (ERP) environment. Oftentimes, the reporting tools available are too complex for business users to utilize effectively, and IT experts do not have the business background to sufficiently understand business users analytical needs. The delay in IT departments turnaround time can quickly render information irrelevant and outdated by the time it is available to business users. ERPs serve as transaction engines in many organizations. It provides mission-critical operational workflow but do not support decision support systems (DSS) directly (Inmon, 2000). Therefore, the need to source a data warehouse from the ERP system and other legacy systems is obvious. Many organisations are now discovering that the solution to leveraging investment decisions in and retrieving useful data from, an ERP system is to undertake a Data Warehousing initiative in conjunction with the implemented ERP system. But, the harsh reality of ERP systems implementation, to the expense of those organisations that invested resources in the initiative, is that ERP only gets data into the system, it does not prepare data for use and analysis (Inmon, 2000). ERP systems lack certain functionality and reporting capabilities. It has been realised that ERP systems are good for storing, accessing and executing data used in daily transactions, but it is not good at providing the information needed for long term planning and decision making (Radding, 2000) as ERP systems are not designed to know how the data is to be used once it is gathered (Inmon, 2000). Consequently, in the post-implementation phase organisations are often dismayed to find that they havent improved their an alytical and decision support capabilities (Inmon, 2000; Radding, 2000) as ERP systems do not provide an environment for decision support activities such as analysing historical trends, drawing conclusions, scenario building and planning. Business Intelligence using Data Warehouse built on ERP System Analytical and forecasting features are provided by the business intelligence tools that are linked to the data warehouse. Some of the common functions of Business Intelligence technologies are reporting, analytics, data mining and benchmarking (Wikipedia, 2009). Integration of ERP and BI can provide a consolidated analysis of the data and user-friendly reporting capabilities and help users make and correct decisions and gain advantages over their competitors. Financial analytics, sales analytics, operational analytics and workforce analytical, may provide the facility to analyze relationships and understand trends that ultimately support business decision. However, few of the challenges faced by data warehousing in ERP systems are in its capability of providing a valuable and accurate reporting service, data analysis and forecasting. Chou et al says (2005) says à ¢Ã¢â€š ¬Ã…“Organizations recognize the wealth of information within ERP systems, the challenge lies in the ways of min ing themà ¢Ã¢â€š ¬Ã‚ . The lack of historical transaction data in the database containing the data from ERP is the most significant obstacle in successfully implementing a BI on ERP system. One of the key elements in accurate forecasting like trend reporting is the need for historical data. Most of the ERP vendors claim that the reports and forecasts generated by ERP or a BI that is integrated with ERP environment are of high accuracy. Zhang et al (2006) says that à ¢Ã¢â€š ¬Ã…“although ERP system is powerful, a serious challenge is how to make use of previous experiences and knowledge to support managerial decision makingà ¢Ã¢â€š ¬Ã‚ . Still the research has to be carried out to know the accuracy of the reports as ERP system does not contain the historical data in the enterprises data warehouse. Traditionally, the enterprise data warehouse needs historical data. When a large amount of historical data starts to stack up in the ERP environment, the ERP environment is usually purged, or the data is archived to a remote storage facility. When an enterprise data warehouse needs to go back in time and bring in historical data that has not been previously colle