OLAP和数据挖掘技术在QAD产品审计中的应用与研究.docx

OLAP和数据挖掘技术在QAD产品审计中的应用与研究OLAP和数据挖掘技术在QAD产品审计中的应用与研究摘要随着时代的发展，如今的企业已大多进入了“无纸化”的办公时代。原有的手工信息输入与分析已无法适应如今日益增多的信息数据。可以说，企业每天都面临着大量的商业信息，而如何利用、分析好这些数据从而为企业的发展提供指导就显得尤其重要。对于QAD公司而言，它是一家专门为制造业提供企业解决方案的软件供应商，它在全球范围内的九十多个国家拥有超过六千多个客户。每一年，它都会对每个客户使用公司软件产品的情况进行审计。在每个客户审计的过程中，自然会产生大量的数据。对于公司而言，所有客户的审计数据将是百万级的。面对如此庞大的数据，如何从这些数据中获取公司所需的信息，分析出审计的结果，并得出一定的指导性结论就显得尤其重要。为此，本文提出了一种基于联机分析处理（OLAP）和数据挖掘技术的审计信息分析的设计。OLAP和数据挖掘技术是近年来数据库领域和人工智能领域研究的热点，它通过对大量数据进行分析和处理，得到隐含在这些数据背后有用的信息和知识。本项目实现了基于SQL Server 2008分析服务（SSAS）在审计信息立方体之上的OLAP多维数据分析和MDX多维数据查询，并利用决策树、神经网络等数据挖掘算法对审计信息数据进行挖掘，得出有用的知识。为实现上述目标，首先需要决定存储分析后的审计信息的数据库版本与类型以及最终生成审计结果报表所需使用的报表生成工具。为此，针对数据库的选择提出了以下几种可行的解决方案：Progress数据库、MySQL数据库、Access数据库和SQL Server数据库。根据实际需求，分别比较了以上四种数据库的优缺点，最终权衡之后选择了SQL Server数据库作为本项目的关系数据库服务器。同样，针对报表生成工具，也提出了以下几种可行的解决方案：QAD公司自己实现的报表生成框架、微软的Access工具和微软的Excel工具。分析了以上几种工具使用的便捷性以及代价考虑，最终选择了大家比较常用且比较轻量型的Excel作为我们最终的报表生成工具。在选定了数据库和报表生成工具的解决方案之后，便要对历史审计数据进行一定的整理、分析以及数据提取和存储工作。审计数据是由公司产品所提供的功能菜单运行后自动生成的，客户在对这些多种多样的报表整理时，可能根据自身的习惯进行不同的打包方式。因此公司从客户手上得到的审计数据结构则是复杂繁多的，没有一定的规律性可循，这对我们进行历史审计信息的提取造成了一定的困难与阻碍。经过对历史审计数据的详细分析之后，找到了所需要的两个审计文件：应用使用详细报告和许可权使用情况报告。其他诸如数据库使用报告等日志文件目前对我们来说并没有太大的意义。因此我们的目标只是在每个客户的审计数据文件夹下找到这两个文件，分析提取数据，并存入数据库中。在实践中，发现如果不对历史数据进行处理，直接运行数据分析提取程序，则效率十分低下。究其原因是因为程序每次都需要遍历每个文件来确认该文件是否为所需的那两个报告文件，因此要花费大量的时间。为解决这一问题，就需要在程序之上再加上一层数据预处理的程序，即将不需要的历史数据文件过滤，按照原有的目录结构，只留下所需的那两个文件。这样，在数据预处理的基础上，程序运行的效率将大大提高。解决了这一问题之后，便是要将分析出的数据存入数据库中。根据历史审计数据信息及相关的外部数据信息，按照需求，共设计了六张数据表。该数据库将为后面的OLAP提供高效的数据源。有了关系数据库源，便可利用SSAS对审计数据进行维度建模。本文重点论述了审计信息数据的概念模型设计和逻辑模型设计，包括度量、维度和粒度的设计，事实表和维表的设计，同时采用了雪花模型构造了逻辑视图，最终生成了审计信息的多维立方体，供最终生成审计结果报表和数据挖掘提供了多维数据源，完成了OLAP在QAD产品审计中的应用。在生成审计结果报表时，使用Excel中的透视表，建立数据库连接，选择建立的多维数据源，便可读取多维数据中的内容。为了方便客户审阅，特别定义了十多个报表模板，可供客户选择，基本涵盖了所有的审计结果，且客户可以自主选择想要查看的内容，对数据进行一定的钻取等，十分方便。最后，便是使用数据挖掘技术对审计数据进行一定的挖掘任务研究。传统的数据挖掘过程往往基于关系数据库。本文探讨了基于OLAP的数据挖掘技术在产品审计中的应用，分别使用了决策树算法和神经网络算法对同一个挖掘模型进行了挖掘，并利用提升图比较了两个算法的挖掘准确性。本文所建立的挖掘模型为分析客户选择QAD公司不同产品类型组合的因素，意在发现一个行业领域内使用QAD公司产品组合的最佳实践，为不同的客户在选择公司产品组合时提供一定的建议。由于时间及精力的有限，只是粗略探索了数据挖掘在公司审计中应用的可能性。相信可以利用该技术挖掘出更多对公司经营决策有指导性意义的知识。本文的研究和结果表明，OLAP和数据挖掘技术在QAD公司产品审计中的应用是可行的，而且是便捷高效的。它不仅从企业管理的角度为公司决策层与销售人员提供了更为智能的分析方法和途径，还为审计分析人员提供了新的手段和视角，来挖掘出更多数据背后隐藏的有用的知识。关键词：数据挖掘，OLAP，多维数据分析，SQL Server 2008分析服务，产品审计THE APPLICATION AND RESEARCH OF OLAP AND DATA MINING TECHNOLOGY IN QAD PRODUCT AUDITABSTRACTWith the development of the society, science and technology, most of the enterprises have now entered the paperless office time. The original manual input and analysis couldnt deal with the increasing information and data any more. Every day, enterprises are faced with lots of business information, and be aware of know how to use these data to analysis, to provide guidance for development of the enterprise is especially important. For QAD which is a software supplier company who provides solutions to specialized manufacturing enterprises, has more than 6,000 multiple clients within 90 countries worldwide. Every year, all clients using software products conditions will be audited by the company. In every customer audit process, large amounts of data will be produced. For QAD, the number of all customers audit data gathered together will be millions. Facing such large numbers of data, it is especially important to know how to get the information the company need, to analysis the audit results, and some guidance conclusions from this huge and messy data.Therefore, this paper proposed a multi-dimensional analysis of QAD product auditing design based on OLAP and data mining technology. OLAP and data mining technology have becoming a research hot spot of database and the field of artificial intelligence during recent years. It through the large data analysis and processing, implicates useful information and knowledge behind these data. This project realized OLAP multi-dimensional data analysis and MDX multi-dimensional data query on the basis of SQL Server Analysis Service 2008 in the audit information cube, and achieved audit information data mining using decision trees and the neural network data mining algorithm for audit information to draw useful knowledge for the company.To achieve this goal, we first need to decide the database version and type to choose, which is used to store the audit information after the analysis and which reporting framework tools to choose to generate final audit result report. Therefore, several feasible solutions of databases based on demand were put forward. They are Progress database, MySQL database, Access database and SQL Server database. According to actual needs, and after comparing each advantages and disadvantages of the above four database servers, the SQL server database was chosen as the relational database server finally. Similarly, for reporting tools, the following feasible solutions were also put forward: QAD reporting framework realized by the company, Microsoft Access tool and Microsoft Excel tools. Excel was chosen as the reporting tool finally because most of the users are very familiar with it and it is powerful for generating various reports though it is very lightweight.After finished selection of database and report generation tool, the next step was to deal with the audit data. We need to sort out, analysis and extract data and finally make these useful information be stored in database correctly. Audit data is automatic generated by using the product function menu. Customer will work on these various reports according to their own habit of different packaging. So the company obtained from the customer on the audit data structure is complex range, no certain regularity, this to our historical audit information extraction has caused some difficulties and obstacles. Through analyzing the historical data after detailed analysis of audit, we found out that only two kinds of report are needed, including Application Detail Usage Profile Report and Licensed Application Report. Other files such as database log files are not so important to us for now. So our goal is to find out these two files in each customer audit data folder and to analysis, extract data from these two files and store the extracted information in database. In practice, we found that if we run data processing procedure directly instead of pre-processing the historical data first, then the data processing procedures efficiency is very low. The reason it that every time the procedure is run, it need to traversal every file in the appointed folders to see whether this file is useful for us or not, that is to say the procedure will open each file to see whether this file is the Application Detail Usage Profile Report and Licensed Application Report or not, thus a lot of time is spent. To solve this problem, it need to plus a layer of data pretreatment process before the above processing procedures. That is to say, after running the data pretreatment process, some historical audit files which are not needed will be filtered, only two kinds of report mentioned above will be remained according to the original directory structure. So, on the basis of data pretreatment, the program will greatly improve the efficiency of the operation. After solving this problem successfully, it is time to deposit the analyzed data into database. According to the historical data information and related auditing data information stored in external database, in accordance with the requirement, and six tables were designed. This database filling with audit information data will provide efficient data source for OLAP mentioned below.Since we already have a relational database source now, we can use SQL Server Analysis Service to build dimensional modeling for audit data. This paper detailed discusses the concept of audit data model design and logical model design, including the measure, dimension and granularity design, fact table and dimension tables design, also adopted the logic structure model of snowflakes, generating a view of the audit information and eventually generated multidimensional cube of audit information, which provide a multidimensional data source for audit results statements and data mining. So the application of OLAP in QAD product audit has been brought into effect. In order to generate audit results report, we can use Excel, establish the pivot table, and choose to build database connection of multidimensional data source. Then we can read the contents of the multidimensional data. In order to facilitate customer review, more than ten report templates were defined, which can be provided for customer to choose, basically covers the entire audit results, also customers can choose to have a check on the content and the data of drilling, very convenient at all.Finally, the data mining technology should be used to do some mining task study on audit data. The traditional data mining is often based on relational database. However, in this paper, it details the application of data mining technology based on OLAP in product audit, using decision trees algorithm and the neural network algorithm respectively for the same mining model, also a lift chart was build to compare the two algorithms mining accuracy. This paper established a mining model to find out the key factors which influence the customers choice of different types of QAD product combination, in order to find a best practice for using a combination of products which QAD provides in a specula industry field, which can be used for different types of customers in choosing products combination provide some suggestions. Due to the limited time and energy of mine, I only make a rough exploration of the possibility of the application of data mining in company product audit. According to the existing results, it is believed that the data mining technology can be used to excavate more guiding significance of knowledge for operation decisions.This research and results show that the application of OLAP and data mining technology in QAD product audit is feasible and it is high efficient and more convenient also. It not only provide more intelligent analysis of the ways and means form the perspective of business management for company administrators and sales personnel, but also provided new means and methods form the perspective of audit research for the company audit researchers, to create more useful knowledge hiding behind the audit data for company development.Key words: data mining, Online Analytical Processing, multi-dimensional data analysis, SQL Server 2008 Analysis Services, product audit目 录第一章 绪论-11.1 QAD产品审计问题简述及分析-11.2 研究目的与意义-11.3 国内外研究现状-21.4 论文研究内容-2第二章 解决方案比较与选定-42.1 数据库的选择-42.1.1 Progress数据库-42.1.2 MySQL数据库-42.1.3 Access数据库-42.1.4 SQL Server数据库-42.2 报表工具的选择-52.2.1 QAD报表生成框架-52.2.2 微软Access报表-52.2.3 微软Excel报表-52.3 本章小结-5第三章 数据处理与存储-63.1 数据的分析与处理-63.1.1 历史数据结构与内容概述-63.1.2 历史数据处理-73.2 数据的存储-83.3 本章小结-11第四章 OLAP的应用-124.1 OLAP技术概述-124.2 维度、度量及立方体的设计-124.2.1 维度的设计-124.2.2 度量的设计-134.2.3 事实表和维表的设计-144.3 建立审计多维数据模型-154.4 审计信息MDX多维查询分析-174.5 审计信息统计报表生成-184.6 本章小结-20第五章 数据挖掘的应用-215.1 数据挖掘技术概述-215.2 基于决策树算法的数据挖掘应用-225.2.1 决策树算法的基本原理-225.2.2 使用决策树算法-235.3 基于神经网络算法的数据挖掘应用-255.3.1 神经网络算法的基本原理-255.3.2 使用神经网络算法-265.4 本章小结-27第六章 结论-286.1 论文总结-286.2 展望-28参考文献-30谢辞-