SQL SERVER 2008 SSAS使用手册1 BI、OLAP、Analysis Services1.1 BI概念简介BI系统负责从多个数据源中搜集数据，并将这些数据进行必要的转换后存储到一个统一的存储介质中，并提供给使用者将这些数据转换为使用者所需信息的功能。一个BI系统通常包括5层：1. 数据源层（data source layer）：由每日的操作数据、文本数据、Excel表格、Access数据库、其他外部数据组成；2. 数据转换层（data transformation layer）：转换数据源层为统一的连续数据，并放入数据存储层；3. 数据存储和提取层（data storage and retrieval layer）：数据仓库；4. 分析层（analytical layer）：多维度的OLAP数据库，为决策者提供分析依据；5. 展示层（presentation layer）：报表和可视化工具。与目前的RDC系统对应，BSERP数据库便相当于一个数据源层，它提供实时的事务数据。一个由SSIS（SQL Server Integration Services）提供的ETL功能可以将业务数据库中的操作性数据通过一定的规则转换为统一的连续数据，它提供的便是一个数据转换层的功能。通过SSIS转换后的数据，存储到DW_RDC数据仓库中。DW_RDC是一个关系型的数据仓库，包含两种类型的表：维度表和事实表。它提供一个数据存储和提取的功能，但是这里的数据仍然不是多维数据，所以我们需要将这些数据通过SSAS(SQL Server Analysis Services)转换成多维数据并提供分析功能，这些多维数据，存储在BI_RDC中。最后，我们将BI_RDC的数据通过Analyzer展示工具进行多维可视化的展现。1.2 OLAP、Analysis Services由SSAS生成的BI_RDC是一个OLAP（On-Line Analysis Process）多维数据库。OLAP是与OLTP（On-Line Transaction Process）相对应的概念，OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事物处理；OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。一些在BI系统中的重要概念，也是从OLAP中的概念延伸过来的，比如：属性、层次结构、维度，度量值等。Integration Services、Analysis Services以及Reporting Services是SQL Server提供的BI工具，分别提供BI系统的数据转换层、分析层和展示层的功能。可以看到使用微软的SQL SERVER 产品可以完全实现BI系统中能够提供的所有功能。其中，Microsoft SQL Server 2005 Analysis Services 为商业智能应用程序提供了联机分析处理 (OLAP) 功能和数据挖掘功能。Analysis Services 支持 OLAP，能够设计、创建和管理包含从其他数据源（如，关系型数据库）聚合来的数据的多维结构。对于数据挖掘应用程序，Analysis Services 使您能够通过使用各种各样的业界标准数据挖掘算法，设计、创建和查看从其他数据源构造的数据挖掘模型。1.3 使用SSAS需要了解的概念1.3.1 Cube、Dimension和MeasureCube就像一个坐标系，每一个Dimension代表一个坐标轴，要想得到一个点，就必须在每一个坐标轴上取的一个值，而这个点就是Cube中的Cell。见下图（来源于http:/msdn2.microsoft.com/zh-cn/library/ms144884.aspx）：上图很好的说明了Cube、Dimension、Measure之间的关系。这里需要注意的是：其实Measure也属于一个维度，即Measures Dimension。所有的Measure构成了Measures Dimension，这个维度的只有一个Hierarchy，而且这个Hierarchy只有一个层次（Level）。1.3.2 Hierarchy、Level和Member在上节的图中，每个Dimension只有一个Hierarchy，而在实际的环境中，一个Dimension往往有很多Hierarchy。因此，上一小节中关于“Cube就象一个坐标系，每一个Dimension代表一个坐标轴”这句话其实不够准确，准确的说应该是每一个Hierarchy代表了一个坐标轴，而Hierarchy中每一个Member代表了坐标轴上的一个值。下图以时间维度为例展示了Dimension的内部结构。2 UDM统一维度模型 希望直接从诸如企业资源规划 (ERP) 数据库这样的数据源中检索信息的用户会面临几个重要挑战： 此类数据源的内容通常非常难于理解，因为它们的设计初衷是针对系统和开发人员，而不是用户。 用户所关心的信息通常分布在多个异类数据源中。即使只是使用其他关系数据库，用户也必须了解每个数据库的详细信息（例如，所用的 SQL 方言）。更糟糕的是，这些数据源的类型可能各不相同，不仅包括关系数据库，而且还包括文件和 Web 服务。 尽管许多数据源都倾向于包含大量事务级别的详细信息，但是，支持业务决策制订所需的查询经常涉及汇总信息和聚合信息。随着数据量的增加，最终用户为进行交互式分析而检索此类汇总值所需的时间也会过长。 业务规则通常并不封装在数据源中。用户需要自行理解数据。统一维度模型 (UDM) 的作用是在用户和数据源之间搭建一座桥梁。UDM 构造于一个或多个物理数据源之上。用户使用多种客户端工具（例如，Microsoft Excel）向 UDM 发出查询。即使 UDM 只是作为数据源上的瘦层来构造，对于最终用户而言也有益处：更简单、更容易理解的数据模型，与异构的后端数据源相隔离，并且汇总类型查询的性能也有所提高。在某些方案中，可以自动构造简单的 UDM。如果在构造 UDM 的过程中再增加一些投资，则可以从该模型提供的丰富元数据中获得其他收益。UDM 具有下列优点： 极大地丰富了用户模型。 提供了支持交互式分析的高性能查询，即使是数据量非常大也不例外。 捕获模型中的业务规则，以支持更丰富的分析。 支持“关闭循环”：允许用户按照所看到的数据进行操作。 2.1 基本的最终用户模型 现在考虑一个示例，在该示例中，用户希望比较不同时间段的销售额和配额。 销售额数据存储在主数据库“销售额和库存”，其中包含许多其他的表。甚至在标识出了相关表之后，该用户也可能发现单个实体（例如，产品）的数据分散在很多表中。由于引用完整性由应用程序逻辑强制实施，因此没有定义这些表之间的关系。销售配额存储在另一个应用程序的数据库中。这两个数据库都不会捕获任何业务规则，例如以下事实：为比较配额和实际销售额，必须使用订单发货日期，而不能使用与订单有关的其他日期（订单日期、订单到期日期、计划日期等）。2.1.1 直接访问数据源首先考虑用户直接访问数据源的情况。下图显示了一个使用示例工具构造的查询示例。到目前为止，用户已经完成了大量的工作。其中包括： 从大量名称隐晦的表中筛查出所需的表。 确定了应将哪些列用于联接表。 从很多包含大量针对系统的详细信息的表中，选择那些包含所需详细信息的列。例如，在存储了有关产品类别的详细信息的表中的 11 个列中，只有两个名称列与用户实际相关。 现在用户专注于定义应当在哪里使用“外部”联接与“内部”联接，以及如何对详细信息进行分组以提供所需的聚合。 然而，用户还要面对更艰巨的任务。例如，用户如何联接来自其他数据源的数据？即使其中的一个数据库支持分布式查询，大多数用户仍然无法构造所需查询，而且在此任务中工具可能无法向用户提供足够的支持。代码示例显示了一个查询外部数据的方法。SELECT Quotas.QuotaAmount, Quotas.EmployeeId, ?FROM OPENROWSET(SQLOLEDB,seattle1;Sales;MyPass,SELECT * FROM Forecasts.dbo.SalesQuota?) As Quotas如果使用其他数据源（如 Web 服务），则在确定如何执行正确的远程调用，然后又如何处理返回的XML以将其与其他数据合并时，用户将遇到另一个巨大的障碍。 最后还有一点：对一个查询执行此项工作之后，进行下一个查询时，此工作的很大一部分又将重复一遍。 2.1.2 使用 UDM 访问数据源与前面的情形相反，以下关系图例示了如何为访问某个基于这些数据源而构造的简单 UDM 的用户生成查询。 Microsoft SQL Server 2005 附带的开发工具提供了此示例中显示的设计界面。但可以使用支持 UDM 的任何接口，包括客户端工具，例如，Office Excel 或 Office Web 组件 (OWC)，或很多报表和分析工具中的一种。左边的树视图显示了 UDM 的内容。注意该示例中的以下几点：只为用户显示面向用户的相关项目。系统列（例如，行 GUID 或最后修改日期）是不可见的。所用的名称为友好名称，而没有使用基础数据库中采用的面向开发人员的命名约定。UDM 还将每个业务实体的所有属性分组为单独的“维度”，如产品或雇员。因此，客户端便可引用该示例中的“产品颜色”、“子类别”以及“类别”，而无需在所涉及的大量表之间显式执行联接。 这些表示事务值或度量值的列随后将显示为“度量值”。例如，用户通常都喜欢对销售量或销售配额之类的列进行聚合。这种将数据显示为“度量值”和“维度”的方法称为“维度建模”。 右边的关系图显示了当前查询中包含的元素。在这种情况下，为了请求“按产品类别分类的销售额和配额”，用户只需通过从树视图中将三个相关项目拖动到右侧设计界面中，即可定义查询。用户不必指定实际访问两个不同数据源时所需的详细信息，或在很多相关表之间执行正确的联接。 模型定义了简单的默认格式：例如，使用货币符号。还可以定义更复杂的格式，包括条件格式，例如，如果某个值低于特定的阈值，则以红色显示该值。 同一模型可支持多种查询。例如，只需通过拖动雇员维度中的一个属性便可按雇员对结果进行细分。2.2 扩展基本模型 虽然上面的示例阐释了即使简单的 UDM 也可以显著地简化基本的数据浏览。但是，当用户访问数据时，还会遇到更多的挑战。例如： 支持来自不同用户的众多不同类型查询的 UDM 的规模可能会变得非常庞大。如何才能确保处理特定任务的用户不会受到与之无关的信息的干扰？如何满足全球用户希望看到以其自己的母语显示的报表的要求？如何才能简化所有与时间相关的常见问题？例如，用户可能希望显示与上一年同期进行比较的销售额。 本部分探讨了上述部分问题，说明 UDM 如何支持对基本模型进行扩展以帮助用户实现更高级的数据浏览操作。 2.2.1 层次结构虽然将一个实体的所有属性合并为一个维度可以显著简化用户的模型，但是，还有一些属性关系是用简单列表所无法表达的。在前面的示例中，“类别”、“子类别”以及 SKU 定义了其中一种可用于组织产品的层次结构。由于用户经常希望基于此类层次结构执行分析，因此 UDM 允许定义此类层次结构。例如，按“类别”查看总计之后，用户可能要深入到“子类别”，然后深入到最低的 SKU 级别。每个层次结构都是序列属性，可以在查询中使用这些属性来简化这种向下深化和向上深化的情形。以下关系图示例显示了层次结构可能以何种形式出现在最终用户的界面中。该模型包含若干种不同的层次结构，产品便是按照这些