谈Excel 与OLAP整合应用doc 14页正式版Word格式.docx

1、关于透过OLAP数据来源建立数据透视表在线分析处理 （OLAP） 是一种组织大型企业数据库的方法。OLAP 数据是由数据库管理员负责组织的，以符合用户分析和管理数据的方法，其好处是只需花较少的时间和精力就可建立所需的报表。OLAP数据库会按明细数据层次组织数据，它会使用与您在分析数据时所使用的同一种类别。比方说，某公司产品在全世界销售信息的数据库可能是由多个单独的字段组成，分别标明国家、地区、县/市和销售地点。在OLAP数据库中，这种地理信息可以按明细数据层次从低到高排列如下：这种组织方式使 Excel 更容易显示较高层次的销售汇总资料 （例如，整个国家或地区的总销售额），以及显示出销售情况好

2、或坏的不同地点的明细数据。在数据透视表或枢纽分析图中，您可以显示不同层次的汇总数据，并且在需要检视更详细的数据时，可以只显示感兴趣的项目。至于维度和cube又是什么意思呢？关于数据特定特征的一组层次（例如：地理区域）称为维度。这样，有关销售时间的信息就可以组织在具有年、季、月和日层次的时间维度中。OLAP数据库称为cube，因为它们既包含了汇总数据（例如：销售或库存资料）也包含了多个维度（例如：时间、地点和产品）。OLAP 数据库的设计目的，是为了加快撷取数据的速度。因为在建立或变更数据透视表或枢纽分析图时，OLAP 服务器 （而不是 Excel） 会计算汇总值，这样就只有较少数据送到Exce

3、l中。此方法使您可以处理的来源数据比使用非OLAP数据库组织的数据还要多，因为对于非OLAP数据库而言，Excel必须先撷取每一笔记录，然后再计算汇总值。欲于Excel 2000中连接至OLAP数据来源与连接至其它外部数据库是一样的，您也可以使用由 Microsoft SQL Server OLAP Services（Microsoft OLAP 服务器产品）所建立的数据库来进行工作。Excel 还可以使用那些提供了与OLE-DB for OLAP 兼容的资料来源驱动程序的协力厂商OLAP产品。Excel 2000只能将您从OLAP资料来源中所撷取的数据显示为数据透视表或枢纽分析图，而不能将O

4、LAP数据显示成外部数据范围。您可以将OLAP数据透视表和枢纽分析图储存在报表模板中，还可以替OLAP查询建立查询档案。OLAP查询档的扩展名为.oqy。在开启.oqy档案时，Excel会显示一个空白的数据透视表，以供您进行版面配置。透过关系型数据库建立cubeExcel 2000提供了OLAP cube 精灵，以协助您在OLAP cube中组织关系型数据库（例如：Microsoft SQL Server）中的资料。透过关系型数据库查询来建立cube，能让您可以在数据透视表或枢纽分析图中处理以前所不能处理的大量数据，以及加速数据的撷取。建立和使用这种类型的OLAP cube并不需要有OLAP服

5、务器产品。如果要了解和使用Microsoft Excel的这种特性，应熟悉如何透过 Microsoft Query管理数据库和存取数据透视表或枢纽分析图的外部数据。如果透过查询建立了OLAP cube，则可以将一组简单的记录转化为结构化的层次或 cube，这样，报表就能着重强调所需层次的明细数据。也可以预先定义报表的汇总值，从而加快报表的计算。如果要在报表中处理大量外部数据（尤其是在系统资源的范围内，如果记录的数量超过了Excel所能分析的数量时），则可建立OLAP cube。由于cube允许Excel仅取出目前报表检视所需的数据，因此对于大量数据，透过cube建立和更新报表比取得数据库的整笔

6、记录集要快。OLAP cube 中的数据只能作为数据透视表和枢纽分析图传回到 Excel，这一点与查询中的关系数据库记录不同，关系数据库中的记录还可作为工作表上的外部数据范围传回到 Excel。欲建立OLAP cube，首先应在Microsoft Query中建立查询，在建立了包含OLAP cube所需所有字段的查询后，就可执行OLAP cube 精灵建立cube 。在以下的操作步骤中，我们要实际示范如何透过关系型数据库查询来建立OLAP cube ：附注：Query 对于Microsoft Excel是一个预设的选项功能，它在第一次会自动地安装在您的计算机上，让其您可以使用指令或功能来撷取外

7、部数据。如果 Microsoft Office或Excel安装您的计算机上，而Query不却不能使用适用，则请您先安装Microsoft Query。 1. 于Excel 2000中，从数据菜单中选取 取得外部数据指令，再选取新增数据库查询指令。2. 当选择数据来源对话框出现时，使用鼠标按一下【在线分析处理】 （OLAP） Cubes索引卷标以便切换至图表2所示的页面。图表23. 使用鼠标连按两下选项。4. 于标示为1的文字方块中键入一个用来辨识数据来源的名称。5. 从标示为2的下拉式清单方块中，选取数据来源所使用的数据库驱动程序。如果您没有看到想要的数据库驱动程序，您可能需要替您的数据来源安

8、装 ODBC 驱动程序或数据来源驱动程序。6. 使用鼠标按一下连接按钮来开启Multi_dimensional Connection对话框。由于我们要连接至数据库服务器，因此请如图表3所示，选取选项钮 OLAP Server、键入供服务器的名称、然后按一下Next按钮。图表37. 从图表4中Database清单方块选取您所要连结的数据库，然后按一下Finish按钮。图表48. 如果您不想要每次使用数据来源时，都要输入登入名称与密码，请选取复选框 将我的使用者代号及密码储存在数据来源定义中（S）。9. 确认您的建立新数据来源对话框类似图表5所示后，按下确定按钮。图表510. 至此您已完成建立所需

9、的OLAP Cube，而此一OLAP Cube也会类似图表6所示显示在清单方块中，请按下确定按钮。图表611. 接下来会出现图表7所示的画面，询问您要将数据透视表放在哪里，并允许您进行相关设定。完成各项设定后，请按一下完成按钮。图表712. 现在您的Excel画面应如图表8所示，您必须决定数据透视表组织数据的方式，以便制作出数据来源为OLAP Cube的数据透视表（类似图表9所示）。图表8图表9您可以将 OLAP 数据透视表和枢纽分析图储存在报表模板中，还可以为 OLAP 查询建立查询档案。OLAP 查询档的扩展名为 .oqy。在开启 .oqy 档案时，Excel 会显示一个空白的数据透视表，

10、以供您进行版面配置。建立离线时使用的cube档案您也可以使用离线cube精灵来建立具有OLAP cube中数据子集的档案。离线cube档案能让您在未连接上网络的情况下使用OLAP数据。因此，当您不要对其他使用者提供存取原始数据库的权限时，您可以使用这些档案，以便使OLAP数据能在网络或Web上使用。欲建立离线cube档案，必须拥有支持此特性的OLAP Provider，例如 Microsoft SQL Server OLAP Services。如果数据透视表或枢纽分析图的资料来源是服务器上的OLAP数据库，则可以将来源数据复制到本机磁盘个别的档案中，此档案就称为离线cube档案（.cub）。透

11、过使用该档案，使用者可以在网络中断连接时继续变更报表，或在OLAP服务器无法使用时继续进行动作。利用此档案，也可以使OLAP数据库中的数据在网络分享目录中得以使用，从而让其它使用者可以透过该档案建立报表。离线cube档案的另外一个好处是，如果需要经常变更报表，则使用离线cube档案可以加快变更的速度（尤其是当OLAP服务器的网络联机十分缓慢时）。但是，通常OLAP数据库都十分巨大，因此要慎重选择离线cube档案中的数据。第一次储存大型离线cube档案时通常会需要较长时间。是否可以建立和使用离线cube档案取决于所使用的OLAP Provider是否支持该特性。在Excel对Microsoft

12、SQL Server OLAP Services数据库的支持中包含了支持建立离线cube档案，但对于其它OLAP Provider则可能不支持该特性。如果所使用的OLAP Provider不允许使用者建立离线cube档案，则数据透视表工具列之数据透视表菜单中的主从架构设定指令将在根据其OLAP 数据库所建立的报表中无法使用。欲替来源数据为OLAP数据库的报表建立离线cube档案，请依下列步骤进行：1. 请注意，由于如果要执行离线cube精灵，首先必须先建立来源数据为OLAP数据库的数据透视表或枢纽分析图，然后透过报表建立离线cube档案。因此现在请您先自行依前一小节所述的方式去建立一个来源数据

13、为OLAP数据库的数据透视表或枢纽分析图。2. 请使用鼠标按一下要替其建立离线cube档案的数据透视表。对于枢纽分析图，请使用鼠标按一下相关的数据透视表。3. 请如图表10所示，从数据透视表工具列的按一下数据透视表菜单中的选取主从架构设定指令。图表10图表114. 当您见到图表11所示的主从架构设定对话框时，请执行下列操作之一： 如果以前没有替报表建立过离线cube档案，则请按一下建立本机数据文件按钮。 如果报表已存在离线cube档案，则请选取选项钮 本机数据文件，然后按一下编辑本机数据文件按钮。此处我们假设您尚未替报表建立过离线cube档案，因此请按一下建立本机数据文件按钮。此举将开启图表1

14、2所示的离线Cube精灵，请按一下Next按钮。图表12图表135. 如图表13所示，离线Cube精灵显示了服务器cube中所有可用的维度与层次来让您选择。就选择维度而言，您可以使用鼠标按一下维度旁的，以便按层次顺序从最高层到最低阶层检视层次。如果不希望在档案中包含某个维度，则可清除该维度左侧的复选框。选择了维度之后，必须选择维度中的层次（如图表14所示）。在每个维度中，可以透过选择或清除层次所对应到的多重标记来指定要包含于档案中的明细数据层次。包含的维度和层次越多，离线cube档案的规模也会越大，尤其当包含了较低层次时更是如此，因为该层次的明细数据量可能大大超过较高层次的数据量。可以忽略较低

15、层次，但不能跳至维度中的层次。例如，如果地理维度中包含国家或地区、 地区和 县/市层次，且已包含了县/市层次，则不能忽略地区或国家或地区层次。在来源数据为离线cube档案的报表中，如果需要，可以只强调县/市层次而隐藏地区层次的数据。选取维度与层次之后，请按一下Next按钮。图表14图表156. 如图表15所示，您现在必须从每个维度的最更多别中选择所需的数据项。按一下维度旁的可检视维度中最高层次的所有项目。例如，如果读者居住地维度的最高层次为读者居住城市，则会看到 台北市、台北县 、 高雄市等。如果要在档案中忽略某一项，则请清除该数据项左侧的复选框。图表167. 如图表16所示，请指定离线cub

16、e档案的存放位置与文件名称。于Microsoft Excel中，离线cube档案的预设扩展名为.cub。当开启数据来源为离线cube 档案的报表时，Excel会认为离线cube档案系位于此步骤中所指定的存放位置。如果离线cube档案不位于该位置，Excel会启动使用者浏览来当助您寻找该档案。储存离线cube档案可能需要较长时间，而且在存盘期间不能于Excel中进行其它工作。如果您想要放弃存盘，请按一下Create Cube Progress对话框中的Stop按钮（如图表17所示）。图表17结语经过本文的说明，相信您已非常了解如何整合使用Excel 2000与Microsoft SQL Server OLAP Services，并利用此特性来大幅强化企业的决策支持分析能力。如果您希望对Microsoft SQL Server OLAP Services，请参阅相关书籍、期刊、白皮书、与网站，我们下次再见。