成本分析：数据仓库的设计、创建与数据转换

1 引 言

    Microsoft SQL server 2000 Analysis Service是联机分析处理和数据挖掘的中间层服务器。它具有分析处理和数据挖掘等方面的强大功能，专业开发人员可以通过使用Analysis Manager提供的各种管理工具，进行操作完成分析服务管理任务。同时，它也提供了DSO决策对象模型、PivotTable Service和MDX语言等API接口工具，使得开发人员能够面向特定领域的应用进行二次开发，开发出满足用户特殊需要的分析服务工具。作者在承担山东省教育厅资助的“生产成本管理智能决策支持系统生成器的开发研究”科研课题时，利用Microsoft SQL server 2000 Analysis Service所提供的API接口工具，开发了用于生产成本预测、控制和分析考核的生产成本管理分析服务系统工具。本文主要论述了该工具中的生产成本数据仓库的设计、创建与数据转换技术。

2 数据仓库的设计

    在此，数据仓库是指为了满足分析服务需要而在SQL Server数据库服务器上创建的数据库。它是Analysis Servers分析服务器上数据库对象和数据库对象所属的多维数据集等对象的数据源。数据仓库中数据表(事实表和维度表)的结构及相互关系必须满足在分析服务器中创建多维数据集和数据挖掘模型的需要，例如：在维度表中，字段之间应该存在某种层次关系或记录之间存在父子关系；维度表和事实表之间应该存在主键与外键之间的约束关系等。根据生产成本预测、分析考核的内容，我们设计了多种星型数据仓库模式，预置在系统中供用户选择组合。具体模式见表1。下面对主要维度表和事实表说明如下：

表1 生产成本分析服务——数据仓库模式

    (1)Department(部门)维度表：必须包括代码、名称和上级部门代码字段。部门上至整个企业，下至生产者个人。这祥，部门维度表就产生一个父子维度，并且可能是一个不均衡层次结构维度。因为有的部门成本考核到个人，有的部门成本考核到班组。

    (2)Product(产品)维度表：必须包括代码和名称字段。产品维度表与部门维度表不同，因为某个产品的下级产品成本之和并不等于该产品的成本，产品成本之间不存在简单的层次关系或父子关系。所以，在产品维度中，如果仅考虑最终产品的成本，则可以包含“All”级别，否则只能仅有产品一个级别，无论是最终产品还是中间产品，都是并列关系，并且不能包含“All”级别。

    (3)CostItem(费用项目)维度表：必须包括代码、费用项目名称和父项目代码。费用项目维度表也产生一个父子维度，并且是一个不均衡层次结构维度。直接人工费用一般没有子项，而直接材料和制造费用往往有子项。

    (4)Process(工序)维度表：必须包括代码、工序名称和父工序代码。工序之间的父子关系不是指上一道工序与下一道工序的关系，而是指工序分类中，大类与子类之间的工序关系。显然，工序维度表也产生一个具有不均衡层次结构的父子维度。

    (5)Material(材料)维度表：包括代码、材料大类名称(例如：主材料和辅助材料及包装材料等)和材料子类名称(例如：钢材、塑料等)及材料名称。显然，材料维度表产生一个常规维度。

    (6)Time(时间)维度表：包括时间代码、年、季和月。在月内，是否再进行划分，如何划分，则根据用户要求进行选择。

    (7)RealProductionCost(实际生产成本)事实表：因和它关联的维表不同，而具有不同的结构，即分为1表、2表、3表和4表。每个表都具有不同的关键字段的组合和具有不同含义度量值字段。例如：在RealProductionCost_2表中，关键字段由：Department_id、Product_id 和Time_id组成，度量值是所指定部门在给定时间内，生产该产品的单位成本和总成本；在RealProductionCost_3表中，关键字段由：Department_id、P roduct_id、CostItem_id和Time_id组成，度量值是所指定部门在给定时间内，生产该产品的某项成本项目的单位成本和总成本。

    (8)ProductionCostQuota(生产成本定额)事实表：同样因和它关联的维表不同，分为1表、2表、3表和4表及5表。每个表都具有不同的关键字段的组合和具有不同含义的度量值字段。PlanningProductionCost(计划生产成本)事实表也有与此类似的结构。

    (9)ProductionCostState(生产成本性态)事实表：按生产成本性态可将产品成本划分为变动成本和固定成本。因此，该表中除了Product_id和Time_id作为关键字段外，度量值字段有：计划单位变动成本、实际单位变动成本、计划固定成本总额和实际固定成本总额。

    (10)IncomeFromSalesAndCost(销售收入与成本费用)事实表：因为在成本分析中，有时需要进行成本效益分析，用到与产品销售有关的数据。因此，该表中除了维表的标识字段外，度量值字段有：产品销售成本、产品销售收入、期间费用等。

    (11)ProductOutput(产品产量)事实表：包括关键字段Department_id，P roduct_id和Time_id，度量值字段：计划产量和实际产量。

3 数据仓库的创建

    3.1 初始化

    当开发用户在生产成本分析服务工具中，选择创建新的数据仓库时，系统在已经注册的服务器创建数据库和SysTableList及SysFieldList数据表，并且将表1中的事实表和维表的结构及其相互关系的描述存到SysTableList和SysFieldList数据表中，为创建星型模式的向导运行做准备。SysTableList和SysFieldList表的关系模式如下：

    SysTableList(FactTableName，DimensionTtable).
    SysFieldList(TableName，FieldName，FieldType，FieldWidth，WhetherPrimaryKey，WhetherAllowNull，WhetherAllowUnique，DefalutValue，CheckConstraint).

    3.2 创建星型模式

    为了使开发用户能够快速地创建满足一般用户需要的星型多维数据模式，我们开发了创建星型模式的向导，开发用户在向导的引导下，能够对表1中的各种结构模式进行组合，选择事实表和维表及表中的字段。向导则根据开发用户的每一步选择设置，创建表的结构，生成星型多维数据模式。

    用户利用创建星型模式向导创建星型模式的过程是一个反复的过程，系统需要跟踪记录用户的每一步操作结果。在此，将用户的每一步操作结果都保存到自定义结构类型的数组中。自定义结构类型的VB语句如下：

    Public Type IndexProperty ‘保存字段索引属性的结构
    IndexName As String ‘索引名称
    IndexFlag As Integer ‘字段是否被创建索引 0没有建索引 1建索引
    IndexRepeat As Integer ‘是否允许重复建
    End Type
    Public Type StarSchemaProperty ‘保存星型模式属性
    FactTableName As String ‘事实表名称
    DimensionTableName As String ‘维表名称
    KeyFieldName ‘建立关联的字段名称
    End Type
    Public Type IDProperty  ‘保存字段标识属性的结构
    IsID As Boolean ‘该字段是否是标识字段
    IDSeed As Integer ‘标识种子
    IDIncrement As Integer ‘标识增加量
    End Type
    Public Type FieldProperty ‘字段属性结构
    TableName As String ‘包含该字段的表名称
    Name As String
    Type As String
    Caption As String
    Width As Integer
    IsNull As String
    IsUnique As String
    Default As String
    Check As String
    IsPrimarykey As Boolean
    ID As ID Property
    Index As IndexProperty
    End Type
    Public FieldArray () As FieldProperty ‘定义为动态数组保存字段属性
    Public StarSchema (1 to 6) As StarSchemaProperty ‘保存星型模式属性，假设一个事实表最多可以有6个维表

    当开发用户运行向导，单击“完成”命令按钮时，向导首先检查用户设置的合法性，然后，根据保存在FieldArray数组的内容，按顺序创建事实表和维表。再根据保存在StarSchema数组的内容，生成创建关系的SQL命令，完成星型多维数据模式的创建。
4 数据仓库的数据转换

    生产成本数据仓库的数据转换就是将会计信息系统中各子系统和其它事务处理信息系统中的数据提取到生产成本的数据仓库中。数据转换服务Data Transformation Services(DTS)是SQL Server数据库服务器中，专门用于完成数据转换任务的工具。SQL Server提供了使用DTS的两种途径：一是在SQL Server企业管理器中，利用DTS向导和编辑器创建DTS数据转换服务包，再调度服务包完成数据转换任务；二是在Visual Basic、Visual C++等开发工具中，创建DTS的Package对象体系结构中的有关对象，再通过调用这些对象的属性和方法，创建和调度DTS包。另外，在SQL Server企业管理器中，创建的DTS包，可以按结构化存储文件格式(文件扩展名：.dts)保存，在VB、VC++等开发工具中调度运行。无论采用那种途径使用DTS，只有掌握了数据库和数据仓库相关知识的专业开发人员，才能胜任DTS包的创建工作。笔者开发的生产成本分析服务系统工具是面向两类不同的用户：一类是专业开发人员(开发用户)；另一类是熟悉生产成本管理但仅具有计算机操作基本知识的企业管理人员(一般用户)。开发用户利用该工具针对企业的具体需求，进行生产成本分析服务的初始化和进行各项设置生成。一般用户利用该工具完成生产成本的日常预测、分析等任务。

    根据上述情况，已开发的生产成本分析服务系统工具中，采用的数据转换方法步骤如下：

    (1)当开发用户完成数据仓库的创建后，首先在SQL Server企业管理器中，创建DTS包，并以结构化存储文件格式保存到磁盘上。

    (2)在生产成本分析服务系统工具中，启动“注册数据转换服务包”功能，将DTS包在生产成本分析服务系统工具中注册。注册内容包括：DTS包的文件名称(包括路径)、包密码、包描述、执行包频率、执行时间。

    (3)在生产成本分析服务系统工具中，自动启动“数据转换服务包调度监视器”功能，在规定的时间间隔内，对“数据转换服务包”注册表进行搜索，比较DTS包规定的执行时间和最近一次执行时间，如果应该执行某个DTS包，则系统提醒一般用户执行该DTS包(例如：“请将八月份的生产成本转到本系统中”)，并记录执行时间。

    (4)当一般用户启动“数据转换”功能时，系统执行DTS包的VB程序段如下：

    Private Sub RunDTSPackage ()
    Dim objDTSPackage As DTS.Package2
    Dim objDTSStep As DTS.Step
    Dim objDTSTask As DTS.Task
    Dim objDTSExecPkg As DTS.ExecutePackageTask
    Set objDTSPackage＝New DTS.Package
    objDTSPackage.FailOnError＝True
    Set objDTSStep=objDTSPackage.Steps.New
    Set objDTSTask＝objDTSPackage.Tasks.New (“DTSExecutePackageTask”)
    Set objDTSExecPkg＝objDTSTask.CustomTask With objDTSExecPkg
    .PackagePassword＝GetPackagepwd () ‘获得包密码的函数过程
    .FileName＝GetPackageName () ‘获得包名称的函数过程
    .Name＝“ExecPkgTask”
    End W  ith
    With objDTSStep
    .TaskName＝objDTSExecPkg.Name
    .Name＝“ExecPkgStep”
    .ExecutelnMainThread＝True
    End With
    objDTSPackage.Steps.Add objDTSStep
    objDTSPackage.Tasks.Add objDTSTask
    objDTSPackage.Execute
    Set objDTSExecPkg＝Nothing
    Set objDTSTask＝Nothing
    Set objDTSStep＝Nothing
    objDTSPackage.Unlnitialize
    End Sub

5 结束语

    本文针对企业生产成本管理的特点，探讨了生产成本数据仓库的设计、创建与数据转换技术，拓宽了数据仓库技术在企业管理中的应用范围。为数据仓库技术在企业其它管理领域的推广应用提供了一个范例。此外，笔者准备开发一个面向企业生产成本管理这一特定领域的，创建DTS包的向导，以弥补本文要求开发用户在SQL Server企业管理器中，创建DTS包的不足。

转载：感谢您对网站平台的认可，以及对我们原创作品以及文章的青睐，非常欢迎各位朋友分享到个人站长或者朋友圈，但转载请说明文章出处。