基于启明ERP的一个数据仓库原型的实现
|
摘要
本文的主要思路是首先介绍数据仓库、数据仓库技术及它同ERP的联系(第2章)。然后以具体一个数据仓库需求为例,展示这个数据仓库的目标和需求特点(第3章),详细介绍了建设数据仓库的全部过程(第4章)。这个过程包括从业务需求入手、搭建开发使用环境、确定策略及技术方案到逻辑和物理设计,直到最后的数据展现。其中4.4小节又是第4章的重点,它包含了大部分我们在实际搭建数据库环境时在框架上所做出的努力,这小节对实际工作有一定的参考价值。然后从实际情况出发,展示了几个数据仓库原型的实现中的细节(第5章)。在第6章以图片形式展示了开发的数据仓库原型的使用实例。在最后一章(第7章)对笔者在整个过程中的收获进行了总结。
ERP systems and Data Warehousing are the hot spots in recent years, and they have become more closer: Data Warehouse can fit further demand of ERP systems - Data mining and analysis; and ERP systems can provide the most comprehensive data sources.
The main purpose of this article is a study in building Data Warehouse based on ERP systems, and making a prototype. It contains the following:
1. First part including introduce the concept of Data Warehouse.And also its history and current situation. And then the logic and physical components of Data Warehouse are described. And what is more how they work togerther. Then introduced two of the technologies and applications of Data Warehouse. Then point out the advantage of building Data Warehouse based on ERP combine the characteristics of ERP.
2. Introducing the target of the prototype and the characteristics of the data source. Business requirements include: to meet with general Data Warehouse decision support functions; general data are phased into the Data Warehouse, data that is not real-time, but static, history. Of the Data Warehouse to meet the requirements of real-time demand; in the Data Warehouse to save the historical details of the ERP system data for operational staff took place not long history of providing data to support the inquiry, that is to have the history of the inquiry; Because the system is operational for automotive manufacturing industry, it has more detailed data, and the Data Warehouse will include Finance, Sales, Production, Cost, Inventory, HR and the development of several modules, we are faced with data from the data source and type of very large scale;The whole process from the ERP system to the final design of the physical model is very complex; A large amount of data.
3. Listed Data Warehouse development process in general and giving the planning of my team to develop the Data Warehouse. Including the general demand for business process analysis, the choice of development tools, the overall structure of the specific development, deployment, maintenance and application.
Analysis of the needs of business is to collect business requirements, including individual interviews, the promotion of collective talks and a similar case law inferred.
Choice of development tools including Select database tools, ETL tools and options to choose to display the data tools.
At the technical level, we treat the Data Warehouse as a set of data storage layer, data transfer layer, and the data application layer.The data of the data storage layer come from collention.Most of them will be used directly.Data transfer layer provide the data source for data storage layer. Application logic layer, including the main data analysis functions which including the WEB application system and the system of MicroStrategy 8 product. It is used to achieve a comprehensive inquiry, KPI indicators, OLAP analysis, dynamic dashboard functions.
The main methord of Data Warehouse development is dimension modeling. Dimension modeling is a logic design technology, which attempts to adopt some kind of visual the framework of the standard structure to performance data, and allows for high-performance access. Dimension modeling consisted of the definition of the dimension table,view and the relationship between them. Modeling time to achieve the physical design phase of the design and structure of the table data dump. The final step is to develop the use of data showing the development of tools to display, where the use of MSTR's Desktop tool.
4. In addition to the level of achievement of the program, this article gives the achieving process of the technical details . Which contains more specific we have demonstrated for the development of the written part of the financial documents, including the objective and target of the financial analysis, and also the realization . What is more is the data conversion process from ODS to ERP, the data preparation area, materialized view log and the Stream technology. Finally,including agents key technologies, conversion of ODS to Data mart ,metadata management and data backup .
5. Show the result on a setp-to-step to make readers know Data Warehouse more directly.
6. At last ,sum up my harvest in the project and giving my prospect. Experience, including the right choise of logic of division, choosing the right software and keep the data quality at any tome. Concluded: ERP system can provide the most comprehensive and detailed data for data analysis and data mining ,and Data Warehouse should be build on systems like that; Data modeling has not yet standardized , And look forward to a way of modeling in theoretical circles agree that such data in the warehouse, If ERP able to meet its own demand at the same time, the need of Data Warehouse, I believe that the Data Warehouse can be faster and better on the combined ERP.
ERP systems and Data Warehousing are the hot spots in recent years, and they have become more closer: Data Warehouse can fit further demand of ERP systems - Data mining and analysis; and ERP systems can provide the most comprehensive data sources.
The main purpose of this article is a study in building Data Warehouse based on ERP systems, and making a prototype. It contains the following:
1. First part including introduce the concept of Data Warehouse.And also its history and current situation. And then the logic and physical components of Data Warehouse are described. And what is more how they work togerther. Then introduced two of the technologies and applications of Data Warehouse. Then point out the advantage of building Data Warehouse based on ERP combine the characteristics of ERP.
2. Introducing the target of the prototype and the characteristics of the data source. Business requirements include: to meet with general Data Warehouse decision support functions; general data are phased into the Data Warehouse, data that is not real-time, but static, history. Of the Data Warehouse to meet the requirements of real-time demand; in the Data Warehouse to save the historical details of the ERP system data for operational staff took place not long history of providing data to support the inquiry, that is to have the history of the inquiry; Because the system is operational for automotive manufacturing industry, it has more detailed data, and the Data Warehouse will include Finance, Sales, Production, Cost, Inventory, HR and the development of several modules, we are faced with data from the data source and type of very large scale;The whole process from the ERP system to the final design of the physical model is very complex; A large amount of data.
3. Listed Data Warehouse development process in general and giving the planning of my team to develop the Data Warehouse. Including the general demand for business process analysis, the choice of development tools, the overall structure of the specific development, deployment, maintenance and application.
Analysis of the needs of business is to collect business requirements, including individual interviews, the promotion of collective talks and a similar case law inferred.
Choice of development tools including Select database tools, ETL tools and options to choose to display the data tools.
At the technical level, we treat the Data Warehouse as a set of data storage layer, data transfer layer, and the data application layer.The data of the data storage layer come from collention.Most of them will be used directly.Data transfer layer provide the data source for data storage layer. Application logic layer, including the main data analysis functions which including the WEB application system and the system of MicroStrategy 8 product. It is used to achieve a comprehensive inquiry, KPI indicators, OLAP analysis, dynamic dashboard functions.
The main methord of Data Warehouse development is dimension modeling. Dimension modeling is a logic design technology, which attempts to adopt some kind of visual the framework of the standard structure to performance data, and allows for high-performance access. Dimension modeling consisted of the definition of the dimension table,view and the relationship between them. Modeling time to achieve the physical design phase of the design and structure of the table data dump. The final step is to develop the use of data showing the development of tools to display, where the use of MSTR's Desktop tool.
4. In addition to the level of achievement of the program, this article gives the achieving process of the technical details . Which contains more specific we have demonstrated for the development of the written part of the financial documents, including the objective and target of the financial analysis, and also the realization . What is more is the data conversion process from ODS to ERP, the data preparation area, materialized view log and the Stream technology. Finally,including agents key technologies, conversion of ODS to Data mart ,metadata management and data backup .
5. Show the result on a setp-to-step to make readers know Data Warehouse more directly.
6. At last ,sum up my harvest in the project and giving my prospect. Experience, including the right choise of logic of division, choosing the right software and keep the data quality at any tome. Concluded: ERP system can provide the most comprehensive and detailed data for data analysis and data mining ,and Data Warehouse should be build on systems like that; Data modeling has not yet standardized , And look forward to a way of modeling in theoretical circles agree that such data in the warehouse, If ERP able to meet its own demand at the same time, the need of Data Warehouse, I believe that the Data Warehouse can be faster and better on the combined ERP.
引文
[1][美] W. H. Inmon.数据仓库(Building the Data Warehouse).北京:机械工业出版社,2000
[2][美]Kimball,R等著.数据仓库生命周期工具箱—设计、开发和部署数据仓库的专家方法.北京:电子工业出版社,2004
[3]ETL.http://www.sta8.cn/baike/shenmeshi-ETL-a/
[4]OLAP.http://www.itisedu.com/phrase/200604232107315.html
[5]ODS. http://www.dwway.com/html/28/n-2228.html
[6]ODS用途. http://ks.cn.yahoo.com/question/?qid=1307021408231
[7]ERP.http://zidao.baidu.com/question/1844861.html?si=5
[8]决策支持系统. http://www.itisedu.com/phrase/200603011653185.html
[9]宋迎亮.数据仓库设计实施,内部文档,2008
[10]数据仓库实施的6种策略.http://dev.21tx.com/2005/03/05/11462.html
[11]MSTR管理员手册,内部文档
[12][美]Kimball,R.数据仓库工具箱:维度建模的完全指南.北京:电子工业出版社,2003
[13]李春阳.数据仓库建设方案,内部文档,2008
[2][美]Kimball,R等著.数据仓库生命周期工具箱—设计、开发和部署数据仓库的专家方法.北京:电子工业出版社,2004
[3]ETL.http://www.sta8.cn/baike/shenmeshi-ETL-a/
[4]OLAP.http://www.itisedu.com/phrase/200604232107315.html
[5]ODS. http://www.dwway.com/html/28/n-2228.html
[6]ODS用途. http://ks.cn.yahoo.com/question/?qid=1307021408231
[7]ERP.http://zidao.baidu.com/question/1844861.html?si=5
[8]决策支持系统. http://www.itisedu.com/phrase/200603011653185.html
[9]宋迎亮.数据仓库设计实施,内部文档,2008
[10]数据仓库实施的6种策略.http://dev.21tx.com/2005/03/05/11462.html
[11]MSTR管理员手册,内部文档
[12][美]Kimball,R.数据仓库工具箱:维度建模的完全指南.北京:电子工业出版社,2003
[13]李春阳.数据仓库建设方案,内部文档,2008