Research And Application Of Performance Optimization Methods In Sql Server Data Warehouse

With the explosive growth of information, the amount of data that we achieved is growing exponentially. Now, it’s more and more difficult to obtain useful information quickly from the mass of data to support decision-making. Business intelligence (BI) emerged to meet this requirement. The performance of the BI system plays a more and more important role due to its direct influence to the final user experiences.In this thesis we built a data warehouse and related CUBE based on the sales data from Ronghua Co. Ltd. The performance-tuning support in SQL Server for ETL procedures and analysis processes are extensively studied to improve the final performance of the data warehouse. The major work of this paper is as follows:First, we studied the optimal cube design for the BI decision making. We analyzed the design methods of the existing system from the perspective of flexible analysis of multidimensional data. According to the characteristics of the RongHua data we optimized the designing of a multi-dimensional data structure on the aspect of dimension table and fact table.Second, the methods for system performance improvement by SQL Server Integration Services (SSIS) parameter setting were studied. We studied the system architecture of SSIS, and analyze the inadequacy of the current system. Some improvements were proposed and tested by simulation. Third, we give several methods in improving SQL Server Analysis Services (SSAS) settings to support optimization. As query optimization and processing performance tuning in SSAS are fairly broad subjects, this thesis focuses SSAS performance tuning techniques on the following three aspects:query optimization, processing performance enhancement, server resources allocation.