What are the Issues that the Developers find it difficult to Design Data Warehouse for ERP Systems?
There is a fierce competition in the business environment in the modern world. To achieve high efficiency and effectiveness through delivering value added products and services in a shortest possible time, many enterprises tend to move from functional to process-based Enterprise Recourse Planning (ERP). ERP systems integrate information and information based processes within and across functional areas of in an organization. ERP systems can be used to manage the main set of activities in an industry. For the business requirements it is very important to have systems to provide key performance required for meeting commercial purposes. ERP system is the most widely used IT solutions in many large enterprises. ERP software applications can be used to deal with product planning, parts purchasing, inventories, interacting with suppliers, providing customer service, and tracking orders. It shows that the benefits of ERP systems do not only include increased decision making speed, improved control of operations and costs, and cost reductions but also improved enterprise wide information dissemination.
In general, many companies have been established ERP software to bring together the common functions of an enterprise. ERP software generally has a central database as its core, allowing applications to share and reuse data more efficiently than formerly acceptable by separate applications. This paper explores the critical issues faced by the developers in designing data warehouse for the implementation of ERP systems in business enterprises. Considering the needs for a data warehouse database, data is taken as most reliable or constant for develop the data warehouse. It is very curtail to identify the correct source system is necessary for any data warehouse development for an ERP systems. Traditionally ERP systems data warehouse cooperate with historical data and ERP technology does not store historical data. Considering all this paper will discuss how developers find it difficult to designing a data warehouse and try give some solutions to develop good data warehouse for an ERP system.
2. Research Question
In the past most of the developers who design data warehouse face some critical issues. In this paper I am focusing on the research question; what are the Issues that the Developers find it difficult to Design Data Warehouse for ERP Systems? My overall idea is to identify and examine the critical issues that data warehouse developers faced when designing data warehouse to the ERP systems. At the moment for a company designing data warehouse can be very interfering. As in the readings, I found that some companies spent lots of the money and the resources for design data warehouse. And they find it as failure at the end as it takes longer time and costs than expected. Some of the companies gain success from the beginning and they fail because of weak planning or implementation process. There are also having lots of security facts also. This is a very vast area and companies need to plan for its future and to be up to date. Generally, the designing of data warehouse for ERP systems is mired by the large investment that it required, schedule overruns, resistance to business process change and unavailability of adequate skills.
2.1. Main objectives of research question
There are many generic design issues such as performance issue and use friendliness expected by the decision makers of the cooperate environment. In an ERP system data warehouse must be satisfied the following requirements.
- Convey a great user practice and user receiving measures by it success
- Purpose without interfering with Online Transaction Processing (OLTP) systems
- Supply a mid storehouse of consistence data
- Quick answers for the complex queries
- Make available selection of powerful systematic tolls such as Online Analytical Processing (OLAP) and data mining.
In an ERP system most successful data warehouses that meet these requirements have following common characteristics. (Mark Moorman - Paper 118-25)
- Are based on a dimensional model
- Include historical data
- Include both detailed and summarized data
- Combine dissimilar data from several sources while maintaining consistency
- Keep an eye on a single subject, such as sales, inventory, or finance
As I mention earlier success of data warehouse measured by its acceptance by users. Without users historical data might as well be archived to magnetic tape and stored in the basement. Successful data warehouse design establish with accepting the users and their requirements.
3. Data Warehouse developing
Data warehouse development process includes several phases.
- Requirement Analysis
- Designing the dimensional model
- Developing the architecture
- Designing the relational database and OLAP cubes
- Developing the data maintenance applications
- Developing analysis applications
- Test and deploy the system
3.1. Requirement Analysis
This phase involves identifying & gathering the requirements. Identifying a sponsor is one of the activities done in this phase. It is crucial to identify a sponsor who understand & support the business values of the project.
The next activity is to interview or work with users & understand the business and turn their needs in to project requirements. Although the users point out their requirements it is the developer's responsibility to determine what data is necessary to provide information. These discussions with users must be continuing periodically in the progress of requirement definition.
3.2. Designing the dimensional model
User requirements and data realities form the dimensional model. It must address business needs, speck of detail, and what dimensions and facts to include. It must be designed in order to provide ease of use for the end users by going well with requirements. Also the model must be designed for easy maintenance and future adoptions. Most of the developers uses star or snowflake design incase those designs easily understands business requirements and provide greater query performances with minimal table joins.
3.3. Developing the Architecture
Data warehouse architecture must be developed for updates and spreading out. That is it must put up with ongoing data updates and to allow for future expansion with minimum impact on existing design. The chronological nature of data warehouses funds that records almost never have to be deleted from tables except to correct errors. So errors in source data must be detected in extraction and transformation processes in the staging area and must be corrected before loading data in to warehouse database.
3.4. Designing the relational database and OLAP cubes
In this phase the schema is created in relational database. The developer must determine the structure that provides better performances. The keys and relationships must be identified and should be established in database schema. Composite primary keys are expensive to maintain, so the developer must eliminate them and should use clustered primary keys which provides excellent query performances.
When dealing with indexes dimension tables must be indexed on their primary keys, the fact tables with unique indexes. The developer can use an index tuner, but must not rely on it. Considering views; should be created for end users with direct access to data in data warehouse. Developer can grant access to views with using requirement analysis. For better performances developers uses indexed views. The view definitions should be clear and consistent & column names must be readable. OLAP cube design requirements are the outcomes of dimensional model.
3.5. Developing the data maintenance applications
The data maintenance applications, including extraction, transformation, and loading processes, must be automated, often by specialized custom applications. It is the developer who must select the powerful tools for that purposes.
3.6. Developing analysis applications
This phase includes developing the application that supports data analysis by the data warehouse users. There are several kinds of analysis services such as data mining, predefined reports, web sites & digital dashboards. Specialized third-party analysis tools are also attained and implemented or installed.
3.7. Test and deploy the system
For this phase it is crucial the users participation. Not only the developers & test groups test the system; but the users who use the system afterwards must load the queries & use the system for testing purpose. Through that kind of testing inconsistencies can be found and corrected, Users become familiar with the system, Index tuning can be performed. Afterwards additional tuning must be provided for satisfactory performance of the system before the system launched online. The developer must identify the required tuning according to the system & there the final product of fine tuned, well developed data warehouse with satisfactory performances.
4. What are the research tools and techniques?
When doing research there are two type of research approaches called deductive and inductive. Deductive approach means top down approach it is more generic and more specific. It goes from theory -> hypothesis -> observation -> confirmation. Inductive approach means bottom up approach it is other way around, moving from specific observation to broader generalizations and theories. It goes from observation -> pattern -> tentative hypothesis -> theory.( Meritorious Prof. Dr. S. M. Aqil Burney(06tthMarch 2008))
In this research I decided to use inductive approach because I have to identify the goals. The issues that the developers find it difficult to design data warehouse for ERP systems are generic. There are lots of qualitative data can be collect from the theories what is hard to implement and another set of quantitative data gave clear idea to fulfill the requirements to build data warehouse in an ERP system. The approach to the project at large can be classified as inductive since the research will be based on the experiments on the selected enterprises and analyzing the results. The methodological choices have been influenced by the variety of issues in designing the data warehouse for ERP systems and differences within those enterprises. The action research aims to explore the requirements of a successful data warehouse design of ERP systems. For the purpose of the project I analyze the qualitative data collected from some enterprises. By exploring the common characteristics of a good ERP system, mainly through a set of questionnaires, it was possible to outline the work with issues related to data warehouse designing. it would need to insert some qualitative questions as well as the quantitative questions.
5. Proposed outcomes of the research
Gaining the knowledge from the questionnaires and feedbacks from the developers I would try develop stepwise document including the needs for a developer to help any situation the felt it difficult to manage. In the data warehouse developing process developers have to exchange their ideas to develop good data warehouse then I try to generate report after every bit they done.
After that I would like to identify what are weaknesses of the methods they following and what are the solutions for them. There also I would like to give some help tips where it is difficult.
Another outcome of my research is to identify benefits can be shared among the developers and try develop communication links through the ERP systems. In there I mainly focus on developers from outside and how they share the things without affect to the security of the data. Security is very important because companies can be lost if data has been leaked through in the system. This is the most risky part of research and I will try my best to do it.
Nevertheless I have to gain knowledge how to design data warehouse within an ERP system and required to identify the essential parts of the design and how to come up with a good clear picture to developers develop the data warehouse easily.
Involving the security factor when I find in readings it is really interesting topic to collaborate with the developers and companies they hide they lots of facts according to the failures.
Finally I would like to come up with a good experimental result involving variety of companies who using ERP systems and different type of developers in the industry.
The designing of data warehouse for ERP systems are believed to be affected by the general trends of globalization and the dominance of Information technology. The general aim of the project is to gain insight of the factors that affect to a successful designing and implementation of data warehouse for ERP systems. The research indicates that rapidly growing business environment; data transactions and communication must be expanded with larger amount of data processing. In that situation data warehouse play an appreciative role in the data manipulation sector. Data warehouses collect, merge, organize, and summarize for the use of decision making. The developers act a specialized role in data warehousing. They focus on the users, determine what data is needed, locate sources for the data, and organize the data in a dimensional model that represents the business needs. In these phases the developers come across several issues that are crucial for the data warehouse performances.
Anne Marie Smith (April 1, 2002) " Data Warehousing & ERP - A Combination of Forces ". The Data Administration Newsletter - TDAN.com. Last accessed 29 Apr, 2010 at: http://www.tdan.com/view-articles/4994
Dave Browning and Joy Mundy (December 2001) "Data Warehouse Design Considerations". MicrosoftSQL2000TechnicalArticles - MSDN.com. Last accessed 27 Apr, 2010 at : http://msdn.microsoft.com/en-us/library/aa902672(SQL.80).aspx
Meritorious Prof. Dr. S. M. Aqil Burney(06tthMarch 2008) "INDUCTIVE & DEDUCTIVE RESEARCH APPROACH". - www.drburney.net . Last accessed 1 may, 2010 at: http://www.drburney.net/INDUCTIVE%20&%20DEDUCTIVE%20RESEARCH%20APPROACH%2006032008.pdf