学生成绩分析OLAP数据仓库的设计与实现

摘要：针对存储在事务型数据库中大量学生成绩数据的彼此分散、不一致及各自独立等现状，利用Sql Server 2008 BI商业智能平台及三层建模方式建立了面向分析型学生成绩数据仓库。通过多维数据集的创建、OLAP分析以及EXCEL 2007前台工具中的数据透视表和数据透视图对OLAP分析结果的展示，为教学管理者在决策分析、教学工作趋势的预测及管理措施的制定等方面提供了有力支持。

关键词：成绩数据仓库；OLAP；多维数据集；数据透视表；数据透视图

中图分类号：TP391文献标识码：A文章编号：1009-3044(2012)13-2944-05

The Design and Implementation of OLAP Data Warehouse of Student Score Analysis

HUANG Yu-da1,2，LI Xiang-qian2

(1.College of Computer Science and Technology，Southwest University of Science and Technology，Mianyang 621010，China；2. Information and Engineering Department，Zhoukou Vocational and Technical College, Zhoukou 466000, China)

Abstract: In response to the situation that a lot of student achievement data Stored in a transactional database are scattered, inconsistent, and independent,using Sql Server 2008 BI Business Intelligence platform and three-tier modeling approach to build an analysis-oriented student achievement data warehouse.The creating of multidimensional datasets，OLAP analysis ,and OLAP analysis results’showing by means of the pivottable and pivotchart of EXCEL 2007 front tool Provide strong support for Educational administrators in Decision analysis, the forecasting of teaching trend and the formulation of management measures.

Key words: score data warehouse; OLAP; multidimensional datasets; pivottable; pivotchart

目前，无论是与学生成绩直接相关的教务管理系统还是与学生成绩间接相关的学籍管理系统（存储学生基本信息）和人事管理系统（存储教师基本信息）等都已经积累了大量数据。但对这些数据的使用平时基本上都限于一些单方面分析且难以被再次利用，不仅缺乏一些相应技术对它们加以综合分析[1]，而且这些系统由于相应业务不同导致相应的平台、开发工具、数据库系统等都不尽相同，可以说互相之间各自为政且缺乏科学、统一的协调和规划。然而各院校为提高教学质量，对大量教学决策信息的需求显得更为迫切。另外，数据仓库是从历史和发展的角度来组织和存储数据[2]，是面向主题且为决策分析提供服务，而且学生成绩数据也比较符合数据仓库一些特征，如面向主题（成绩分析）、相对稳定（每学期一次考试）、随时间变化（学年学期）等。因此将与学生成绩相关的一些数据库系统中大量历史数据加以预

处理后载入数据仓库，可充分利用其中的多维数据分析技术（如OLAP）及数据挖掘工具等对仓库中数据加以智能化分析处理，从而更好地为教学管理者提供决策服务。

本文采用微软公司的Microsoft Visual Studio 2008中的Sql Server Management Studio平台来构建学生成绩仓库，采用Microsoft Sql Server 2008中的Sql Server Business Intelligence Development Studio平台工具来建立BI项目并实现多维数据集的创建、OLAP分析功能。

1成绩数据仓库的三层建模

本文采用通用的自顶向下、逐步细化的仓库建模方式即三层建模，依次为：概念模型设计、逻辑模型设计、物理模型设计[3]。

1.1概念模型设计

概念模型设计是一种面向全局的、较高抽象层次上的设计，该阶段主要任务是确定系统中主要主题域及其内容[4]。考虑到学生成绩特点及影响因素，该文把学生成绩分析作为主题来建立成绩数据仓库，仓库中包含学生学籍信息、课程信息、教师信息、班级信息、专业信息、系别信息、学年学期信息和学生成绩信息共八个方面的数据，将学生成绩作为关键性能指标并将除学生成绩信息以外的另外七个方面的信息作为维度数据，所有数据都分别来自学生学籍管理系统、教务管理系统和人事管理系统。

1.2逻辑模型设计

由于本文需建立的成绩仓库的维度数并不是太多而且数据量并非很大即不会占用较多存储空间，所以针对关系模式的定义以及较高查询效率的需求，这里决定采用星型逻辑模型，如图1所示。

图1成绩数据仓库星型逻辑模型

1.3物理模型设计

目前由于大部分数据仓库都是基于关系型数据库而且数据的最终管理及存放都是由相应数据库系统来处理的，所以这里的物理模型设计就主要考虑关于物理数据库的一些模型设计，如数据的索引、存储及其结构等[5]。

本文设计的数据仓库的核心数据库是微软的SQL SERVER 2008企业版关系型数据库管理系统。对实体表的主外键均建立索引以提高响应速度。

2数据ETL过程实现

本文采用笔者所在工作单位—周口职业技术学院几年来的学生成绩相关数据作为数据源。考虑到2007年以前尚未全面启用教务管理系统、时间略显仓促及数据收集和预处理需花费大量时间、精力，所以数据源范围暂先定于信息工程系、机电系和财经系三个系的07级、08级、09级学生成绩数据。

2.1数据的抽取及清洗

在确定了成绩仓库中事实表及各个维度表的具体结果模型之后，就需要以这些结构模型为重要参考依据分别从各自对应的业务型数据库系统中进一步抽取相关数据并经过预处理后再载入成绩仓库。

数据的清洗主要是对数据中的杂质、噪声、不一致、不规范、遗漏等情况加以处理。一般应视各自具体情况区别对待，不要一律删除。比如对于某名学生，若有少量课程成绩遗漏或出现0-100范围外的个别噪声数据则应考虑取平均成绩替代；若遗漏课程成绩门数较多（4门以上）或因学籍异动造成其数据无法有效参与比较，为保证分析的可靠性则应考虑删除。另外对于一些关于教师、课程、班级、专业维度信息数据的缺失或出现不一致的情况则进一步核实后加以填充。如有重修或补考成绩则均采用正考即首次成绩。

2.2数据的转换

对于考查课中的“优”、“良”、“中”、“差”四个层次，为了便于统一分析，应向百分制转换。这里对应关系为：“优”-90、“良”-80、“中”-70、“差”-50。此外，对于源数据表中各属性名、类型及长度都要统一设置并与成绩仓库中对应的维度表及事实表保持一致。

2.3数据的加载

上述各环节工作处理完毕后，接下来就是最后一步即数据的加载。首先在Sql Server 2008 Management Studio环境下建立一个名为“StudentMA”的数据库，然后利用Sql Server中的数据导入功能将已整理好的8个数据表导入到新建的数据库中，如图2所示。在载入目标成绩数据仓库数据库后，还需根据星型逻辑结构和物理结构的具体设计来设置好各表主/外键并为其建立相应索引。

3多维数据集的创建及OLAP技术的应用

3.1多维数据集的创建

进入Sql Server 2008 Business Intelligence Development Studio集成环境后，新建一个名为“学生成绩多维分析”的Analysis Service即SSAS项目，然后使用“解决方案资源管理器”来分别创建项目的“数据源”、“数据源视图”、“多维数据集”。其中“数据源”所对应的连接字符串代码为：

Provider=SQLNCLI10.1；Data Source=SWUST-573110AE0；

图2预处理后数据源导入SQL SERVER 2008

Integrated Security=SSPI；Initial Catalog=studentMA

另外，为了满足决策分析的需要，还需要再通过添加“平均成绩”计算成员来新增度量值，新建的“平均成绩”计算变量（avg? Score）所对应的MDX语句为：

CREATE MEMBER CURRENTCUBE.[Measures].avgScore AS round([Measures].[Score]/[Measures].[Score计数],0), VISIBLE = 1；

创建后的多维数据集视图如图3所示。

图3成功创建后的多维数据集

最后，对SSAS项目“学生成绩多维分析”加以部署和处理操作，部署并处理成功的结果如图4所示。

3.2多维分析及OLAP技术的应用实现

首先可利用SSAS自带的“浏览器”功能进行多维分析和OLAP操作[6]。在“浏览器”界面中，可根据用户的OLAP处理要求来对创建的CUBE加以自由组合其中的维度，只需将左侧窗口界面中的目标对象如维度、维度成员、度量值成员等直接拖动到窗口右侧的显示区域相应位置后即可查看OLAP处理结果。如果要进行数据筛选则需要将筛选条件所涉及到的相应维度名或其成员值拖放到右侧窗口上方的筛选字段指定区域。如图5所示。

另外，由于Microsoft Excel 2007全面支持Sql Server2008的SSAS服务，目前其作为微软前端展现工具中的一个重要组件已在商业智能方面得到了较为广泛的应用[7]。Excel 2007中的数据透视表和数据透视图可直接访问SSAS项目中对应多维数据集，这样就不必开发应用程序即可快速而灵活地生成各种图表及报表。因此可利用该组件来进行多维分析并展现分析结果。

下面通过Excel 2007中的数据透视表来进行多维分析。可在透视表窗口任意组合一些度量值和维度，直接拖动到EXCEL表格模型的相应行列中即可自动生成所需表格数据。如果想以数据透视图方式来展示数据则只需点击工具栏中的柱状小图标即可立即生成对应的透视图。在某些情况下，透视图的显示效果要比透视表会更加直观，如图6、7、8所示。

其中图6为教师、课程、班级所有学年学期信息的汇总显示，细节区域内容为班级人数和平均分；图7为信息工程系建筑设计技术专业在2009年第二学期教师、课程、班级及平均分信息汇总显示；图8为信息工程系道路桥梁工程技术专业在所有学年学期的教师、课程、班级及平均分信息汇总显示。

图4项目成功处理示意图

图5多维分析结果展示

图6利用PivotTables功能在EXCEL前台展示多维分析结果

图7利用EXCEL前台透视图功能展示多维分析结果一

图8利用EXCEL前台透视图功能展示多维分析结果二

4结束语

本文利用Sql Server 2008 Management Studio及Sql Server 2008 BI平台成功构建了学生成绩数据仓库并建立了数据集市，然后进行了OLAP即联机分析处理，最后利用Microsoft Excel 2007前台工具中的数据透视表及数据透视图组件对OLAP分析结果进行了直观展示，为管理者进行科学决策分析提供了重要决策依据。另外，增加数据仓库中的成绩数据量以及对成绩仓库中的数据进行深入挖掘分析将是笔者下一步主要研究内容。

参考文献：

[1]魏丽,王雁苓.高校学生成绩分析数据仓库的建立[J].吉林省教育学院学报,2010(6):42-43.

[2] Jiawei Han,Micheline Kamber.范明,等,译.数据挖掘概念与技术[M].北京:机械工业出版社,2007.

[3]罗跃国.高校教务系统数据仓库的建模及应用[J].长江大学学报:自然科学版,2009(3):235-237.

[4]王丽珍,周丽华.数据仓库与数据挖掘原理及应用[M].北京:科学出版社,2005.

[5]陈文伟.数据仓库与数据挖掘教程[M].北京:清华大学出版社,2006.

[6]黄兴荣,李昌领.基于SQL Server 2005的数据挖掘的研究[J].计算机与现代化,2010(5):195-198.

[7]王欣,徐腾飞,唐连章,等.SQL Server 2005数据挖掘实例分析[M].北京:中国水利水电出版社,2008.